sqlite - 大SELECT优化
我在Python中使用SQLite做一个大的文件管理系统,我有一个大的平面文件(1亿行),我想用3列(整数)的值进行排序,这样我就可以进行迭代和计算。我有一个大的平面文件(1亿行),我想用3列(是整数)的值进行排序,这样我就可以进行迭代和计算。
我使用了SQLite与一个大的 SELECT ... ORDER BY
(在一列上有一个索引)。由于这个大 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)
【回答】:
我已经写了一些示例脚本,创建你的数据库,并通过它的所有元素。它看起来比你在评论中写的快得多。你确定数据库访问是一个瓶颈吗?也许在你的脚本中,你做了更多的事情,这需要这么多的时间。
我检查了2个数据库,SQLite和MongoDB,有500万个项目,对于SQLite来说,插入所有的行需要大约1200秒,选择它们大约300秒,MongoDB更快,插入需要大约400秒,而选择不到100秒。
请用我的样本检查你的代码,并检查你的选择是否类似。我使用了光标而不是LIMITOFFSET.如果这仍然没有帮助,那么我认为MongoDB值得一试。它有一个缺点--它需要64位的操作系统来支持大型数据库(比如你的)。如果你以前使用过它,那么这里是最短的Windows安装指南。
- 下载并解压MongoDB for windows 64-bit,来自 http:/www.mongodb.orgdownloads
- 运行 "mongod.exe --dbpath ."
- 下载python 2.x的模块 http:/pypi.python.orgpypipymongo。 或对python 3.x的 http:/pypi.python.orgpypipymongo3。
- 运行我的脚本
这里是我的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)