<p>你正在关联两个大表。一些快速计算:30万x 20万=60亿行。如果这只是返回所有这些行的问题,那么您肯定会受到I/O的限制(但实际上仅限于(<strong>O</strong>)输出端)。但是,where子句过滤掉了几乎所有的内容,因为您只返回了10k行,所以您可以确定这里的CPU是有限的。</p>
<p>SQLite一次只能使用一个索引,但被称为“<a href="http://www.sqlite.org/optoverview.html#or_opt">OR optimizations</a>”的索引除外。此外,由于内部连接“<a href="http://www.sqlite.org/optoverview.html#joins">are converted into additional terms of the WHERE clause</a>”,因此不会从它们获得任何性能增益。</p>
<p>归根结底,SQLite将无法像say<code>postgresql</code>等人那样高效地执行查询。</p>
<p>当我好奇地想知道你的查询可以优化多少的时候,我对你的场景进行了反复的研究。最后,似乎最好的优化是删除所有显式索引(!)。似乎SQLite动态地创建了一些索引,这些索引比我尝试的不同方法有更好的性能。</p>
<p>作为演示,请考虑从您的模式派生的此模式:</p>
<pre><code>CREATE TABLE feature ( -- 300k
feature_id INTEGER PRIMARY KEY,
mzMin DOUBLE,
mzMax DOUBLE,
rtMin DOUBLE,
rtMax DOUBLE,
lnk_feature INT);
CREATE TABLE spectrum ( -- 20k
spectrum_id INTEGER PRIMARY KEY,
mz DOUBLE,
rt DOUBLE,
lnk_spectrum INT);
</code></pre>
<p><code>feature</code>有300k行,和<code>spectrum</code>20k(执行此操作的python代码在下面的某个地方)。由于定义<code>INTEGER PRIMARY KEY</code>,没有指定显式索引<a href="http://www.sqlite.org/lang_createtable.html#constraints">only implicit ones</a>:</p>
<blockquote>
<p>INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY
constraints are implemented by creating an index in the database (in
the same way as a "CREATE UNIQUE INDEX" statement would). Such an
index is used like any other index in the database to optimize
queries. As a result, there often no advantage (but significant
overhead) in creating an index on a set of columns that are already
collectively subject to a UNIQUE or PRIMARY KEY constraint.</p>
</blockquote>
<p>使用上面的模式,SQLite提到它将在查询的生命周期中创建一个索引<code>lnk_feature</code>:</p>
<pre><code>sqlite> EXPLAIN QUERY PLAN SELECT feature_id, spectrum_id FROM spectrum, feature
...> WHERE lnk_feature = lnk_spectrum
...> AND rt >= rtMin AND rt <= rtMax
...> AND mz >= mzMin AND mz <= mzMax;
0|0|0|SCAN TABLE spectrum (~20000 rows)
0|1|1|SEARCH TABLE feature USING AUTOMATIC COVERING INDEX (lnk_feature=?) (~7 rows)
</code></pre>
<p>即使我使用该列或其他列上的索引进行了测试,运行该查询的最快方式似乎是不使用任何这些索引。</p>
<p>我使用python运行上述查询的最快速度是20分钟。这包括完成<code>.fetchall()</code>。你提到在某个时刻你将拥有150倍的行。如果我是你,我会开始调查<code>postgresql</code>。。。请注意,您可以在线程中分割工作,并可能将完成查询的时间除以能够并发运行的线程数(即,除以可用的CPU数)。</p>
<p>无论如何,这是我使用的代码。您能自己运行它并报告查询在您的环境中运行的速度吗。请注意,我正在使用<code>apsw</code>,因此如果您不能使用它,则需要调整以使用自己的sqlite3模块。</p>
<pre><code>#!/usr/bin/python
import apsw, random as rand, time
def populate(cu):
cu.execute("""
CREATE TABLE feature ( -- 300k
feature_id INTEGER PRIMARY KEY,
mzMin DOUBLE, mzMax DOUBLE,
rtMin DOUBLE, rtMax DOUBLE,
lnk_feature INT);
CREATE TABLE spectrum ( -- 20k
spectrum_id INTEGER PRIMARY KEY,
mz DOUBLE, rt DOUBLE,
lnk_spectrum INT);""")
cu.execute("BEGIN")
for i in range(300000):
((mzMin, mzMax), (rtMin, rtMax)) = (get_min_max(), get_min_max())
cu.execute("INSERT INTO feature VALUES (NULL,%s,%s,%s,%s,%s)"
% (mzMin, mzMax, rtMin, rtMax, get_lnk()))
for i in range(20000):
cu.execute("INSERT INTO spectrum VALUES (NULL,%s,%s,%s)"
% (get_in_between(), get_in_between(), get_lnk()))
cu.execute("COMMIT")
cu.execute("ANALYZE")
def get_lnk():
return rand.randint(1, 2)
def get_min_max():
return sorted((rand.normalvariate(0.5, 0.004),
rand.normalvariate(0.5, 0.004)))
def get_in_between():
return rand.normalvariate(0.5, 0.49)
def select(cu):
sql = """
SELECT feature_id, spectrum_id FROM spectrum, feature
WHERE lnk_feature = lnk_spectrum
AND rt >= rtMin AND rt <= rtMax
AND mz >= mzMin AND mz <= mzMax"""
start = time.time()
cu.execute(sql)
print ("%s rows; %.2f seconds" % (len(cu.fetchall()), time.time() - start))
cu = apsw.Connection('foo.db').cursor()
populate(cu)
select(cu)
</code></pre>
<p>我得到的输出:</p>
<pre><code>54626 rows; 1210.96 seconds
</code></pre>