我的数据库有大约 6 亿条我想查询的条目（）。这个本地 dbSNP 仅包含 rsID 和基因组位置。我使用了：

import sqlite3
import gzip
import csv

rsid_db = sqlite3.connect('rsid.db')

rsid_cursor = rsid_db.cursor()

rsid_cursor.execute(
"""
CREATE TABLE rsids (
rsid TEXT,
chrom TEXT,
pos INTEGER,
ref TEXT,
alt TEXT
)
"""
)

with gzip.open('00-All.vcf.gz', 'rt') as vcf: # from https://ftp.ncbi.nih.gov/snp/organisms/human_9606/VCF/00-All.vcf.gz
    reader = csv.reader(vcf, delimiter="\t")
    i = 0
    for row in reader:
        if not ''.join(row).startswith('#'):
            rsid_cursor.execute(
            f"""
            INSERT INTO rsids (rsid, chrom, pos, ref, alt)
            VALUES ('{row[2]}', '{row[0]}', '{row[1]}', '{row[3]}', '{row[4]}');
            """
            )
            i += 1
            if i % 1000000 == 0:
                print(f'{i} entries written')
                rsid_db.commit()

rsid_db.commit()

rsid_db.close()

我想查询多个 rsID 并获取它们的基因组位置和改变（查询并rsid获取chrom、pos、ref和）。一个条目如下所示：altrsid

安全标识符 铬 位置 参考 替代
rs537152180 1 4002401 格 A、C

我查询使用：

import sqlite3
import pandas as pd


def query_rsid(rsid_list,
               rsid_db_path='rsid.db'):

    with sqlite3.connect(rsid_db_path) as rsid_db:

        rsid_cursor = rsid_db.cursor()
        rsid_cursor.execute(
        f"""
        SELECT * FROM rsids
        WHERE rsid IN ('{"', '".join(rsid_list)}');
        """
        )

        query = rsid_cursor.fetchall()

    return query

无论输入多少条，大约需要 1.5 分钟。有没有什么方法可以加快速度？

17

  • 7
    您可能想要在该字段上创建索引rsid。


    – 


  • 如果你只想要 Id 和基因组位置，为什么要使用SELECT *？为什么要选择所有字段？


    – 

  • 2
    假设 600Mio 意味着 6 亿行，我不禁想到 SQLite 可能不是理想的数据库。此外，rsid_list中有多少个值？正如 @KlausD. 所建议的那样， rsid列上的索引可能会有所帮助


    – 


  • @KlausD.：如果 rsid 完全未排序，索引是否也会有帮助，或者索引是否也会处理内部排序？


    – 

  • 2
    正确索引的 SQLite 数据库应该可以解决这个问题。不过，您可能想尝试一下；它应该能够直接提取经过 gzip 压缩的 TSV。


    – 


最佳答案
2

其他人建议将你的rsid列定义为主键，或者在其上创建唯一索引。这是一个好主意。

另一件事： rsid IN ('dirty','great','list','of',items')可以使用所谓的来获取结果。如果你的rsid_list数据非常大，或者它提取的值在词汇上相差很大，那么将列表中的项目放入临时表中，然后执行以下操作可能会有所帮助

SELECT rsids.*
  FROM rsids
  JOIN temp_rsids_list ON rsids.rsid = temp_rsids_list.rsids

以获得更有效的查找。

我会像这样声明表格：

CREATE TABLE rsids (
  rsid TEXT PRIMARY KEY COLLATE BINARY,
  chrom TEXT,
  pos INTEGER,
  ref TEXT,
  alt TEXT
) WITHOUT ROWID

COLLATE BINARY是默认设置。但显示它仍然很有帮助，因为您事先知道您不希望在该列上使用不区分大小写的匹配。这会提醒您未来的自己和您的同事这一重要的优化。

WITHOUT ROWID告诉 SQLite 将表组织为所谓的“聚集索引”，其中其他值与易于搜索的主键一起存储。

如果您可以将主键变为 INTEGER，那么从性能角度来说这是一个好主意。

1

  • 好的，主键确实做到了。查询现在需要 0.0 秒（在 .ipynb 内测量，我知道它实际上不是 0.0 秒）。我COLLATE BINARY还没有添加，也没有删除它们rs。谢谢大家的帮助 :)。


    – 

您可以在列上创建索引rsID：

CREATE UNIQUE INDEX idx_rsid
ON rsids(rsid);

仅当值唯一UNIQUE时才可使用关键字。正如您所述，这是您的主键，因此根据定义它是唯一的。rsidrsid

2

  • 1
    rsid 应该是唯一的： /… 。我目前正在尝试将其作为 PRIMARY KEY。


    – 


  • 1
    我没有生成这些数据。这是一个现有的大型公开数据库。因此，我不确定它是否是主键，但我认为是的。


    –