大型SELECT查询优化

32 浏览
0 Comments

大型SELECT查询优化

我在Python中使用SQLite进行大文件管理系统。我有一个大文件（1亿行），我想使用3个列的值（这些值是整数）对其进行排序，以便我可以迭代并进行一些计算。

我使用带有一个列索引的大SELECT ... ORDER BY来使用SQLite。由于这个大的SELECT需要太多内存，所以我需要多次调用它（使用OFFSET和LIMIT）。

我可以使用Linux sort，但我希望它是平台独立的。它可以正常工作（只要正确设置了正确的PRAGMA），但速度慢。如何优化这个问题？

命令如下：

PRAGMA journal_mode = OFF
PRAGMA synchronous = 0
PRAGMA locking_mode = EXCLUSIVE
PRAGMA count_change = OFF
PRAGMA temp_store = 2
CREATE TABLE tmpTranscripts_arm_3R_transcripts (id INTEGER PRIMARY KEY, name varchar(255), chromosome varchar(255), start int(11), end int(11), direction tinyint(4), tags varchar(1023), bin int(11), exons varchar(10000))
CREATE INDEX 'iTranscript_arm_3R_14943' ON 'tmpTranscripts_arm_3R_transcripts' (start, end, direction)
INSERT INTO tmpTranscripts_arm_3R_transcripts (name, chromosome, start, end, direction, tags, bin, exons) VALUES ('SRR060644.1', 'arm_3R', 11450314, 11450337, -1, 'feature=transcript;bestRegion=(self);nbGaps=0;nbMismatches=0;ID=SRR060644.1;identity=100.0', 300011450, '')
(this, more than 10 millions times)
SELECT * FROM tmpTranscripts_arm_3R_transcripts ORDER BY start, end, direction LIMIT 0, 10000
(this, as much as needed)

admin 更改状态以发布 2023年5月25日
0

1 答案

0 Comments

我写了一些样例脚本来创建你的数据库并且扫描所有的元素，看起来比你在评论中写的要快。你确定数据库访问是瓶颈吗？也许在你的脚本中还做了其他事情，这会花费更多时间。

我检查了两个拥有500万条目的数据库，SQLite和MongoDB。对于SQLite，插入所有行大约需要1200秒，并且选择大约需要300秒。对于MongoDB，插入只需要约400秒，而选择不到100秒。

请使用我的样例检查你的代码并检查你的选择是否相似。我使用游标而不是LIMIT/OFFSET。如果这还不能帮助你，那么我认为MongoDB是值得一试的。它有一个缺点-需要64位操作系统以支持大型数据库（像你的）。如果你以前从未使用过它，那么这是Windows的最短安装指南：

这是我用Python 3.x测试SQLite的脚本

import sqlite3
from time import time
conn = sqlite3.connect('test.dbase')
c = conn.cursor()
c.execute("""PRAGMA journal_mode = OFF""")
c.execute("""PRAGMA synchronous = 0""")
c.execute("""PRAGMA locking_mode = EXCLUSIVE""")
c.execute("""PRAGMA count_change = OFF""")
c.execute("""PRAGMA temp_store = 2""")
c.execute("""CREATE TABLE tmpTranscripts_arm_3R_transcripts (id INTEGER PRIMARY KEY, name varchar(255), chromosome varchar(255), start int(11), end int(11), direction tinyint(4), tags varchar(1023), bin int(11), exons varchar(10000))""")
c.execute("""CREATE INDEX 'iTranscript_arm_3R_14943' ON 'tmpTranscripts_arm_3R_transcripts' (start, end, direction)""")
t1 = time()
for i in range(0, 5000000):
    c.execute("""INSERT INTO tmpTranscripts_arm_3R_transcripts (name, chromosome, start, end, direction, tags, bin, exons) VALUES ('SRR060644.1', 'arm_3R', %d, %d, %d, 'feature=transcript;bestRegion=(self);nbGaps=0;nbMismatches=0;ID=SRR060644.1;identity=100.0', 300011450, '')""" % ((i+123)%352, (i+523)%422, (i+866)%536))
    if(not i%10000):
        print("Insert:", i)
t2 = time()
print("Insert time", t2-t1)
conn.commit()
t1 = time()
c.execute("""SELECT * FROM tmpTranscripts_arm_3R_transcripts ORDER BY start, end, direction""")
i = 0
for row in c:
    a = row[0]
    if(not i%10000):
        print("Get:", i, row)
    i+=1
t2 = time()
print("Sort time", t2-t1)
c.close()

以及测试MongoDB的脚本

from pymongo import Connection
from pymongo import ASCENDING, DESCENDING
from time import time
connection = Connection()
connection = Connection('localhost', 27017)
db = connection['test-database']
collection = db['test-collection']
posts = db.posts
posts.create_index([("start", ASCENDING), ("end", ASCENDING), ("direction", ASCENDING)])
t1 = time()
for i in range(0, 5000000):
    post = { "name": 'SRR060644.1',
            "chromosome": 'arm_3R',
            "start": (i+123)%352,
            "end": (i+523)%422,
            "direction": (i+866)%536,
            "tags": 'feature=transcript;bestRegion=(self);nbGaps=0;nbMismatches=0;ID=SRR060644.1;identity=100.0',
            "bin": 300011450,
            "exons": ''}
    posts.insert(post)
    if(not i%10000):
        print("Insert:", i)
t2 = time()
print("Insert time", t2-t1)
t1 = time()
i = 0
for post in posts.find().sort([("start", ASCENDING), ("end", ASCENDING), ("direction", ASCENDING)]):
    if(not i%10000):
        print("Get:", i, post)
    i+=1
t2 = time()
print("Sort time", t2-t1)

0