回答此问题可获得 20 贡献值,回答如果被采纳可获得 50 分。
<p>我已经花了大约一周的时间来加速我正在使用的查询,并在这里提出了几个问题(<a href="https://stackoverflow.com/questions/10412604/how-can-i-speed-up-fetching-the-results-after-running-an-sqlite-query">How can I speed up fetching the results after running an sqlite query?</a>,<a href="https://stackoverflow.com/questions/10336492/is-it-normal-that-sqlite-fetchall-is-so-slow">Is it normal that sqlite.fetchall() is so slow?</a>,<a href="https://stackoverflow.com/questions/10334031/how-to-use-min-and-max-in-an-efficient-way">How to use min() and max() in an efficient way?</a>)。</p>
<p>在这里给出的答案的非常有用的帮助下,我成功地获得了sqlite查询所需的时间,它花费了<code>100.95</code>秒,而fetchall花费了:<code>1485.43</code>。这还不够,所以在尝试了一些不同的索引之后,我设法将一个样本的查询时间降到了<code>0.08</code>秒,而fetchall时间降到了<code>54.97</code>秒。所以我想我终于把事情搞得够快了。</p>
<p>然后查询运行到下一个示例,占用<code>0.58</code>秒,而fetchall占用<code>3952.80</code>秒。对于第三个示例,查询花费了<code>1.01</code>秒和<code>1970.67</code>秒来获取。</p>
<p>第一个样本提取12951行,第二个样本提取24972行,第三个样本提取6470行。
我很好奇为什么第一个样本提取行要快得多,而第二个样本只有大约一半的提取量。</p>
<hr/>
<p>代码(<code>spectrumFeature_inputValues</code>是(1,),(2,)和(3,),来自所使用的3个示例。)</p>
<pre><code>self.cursor.execute('begin')
self.cursor.execute("EXPLAIN QUERY PLAN "+
"SELECT precursor_id, feature_table_id "+
"FROM `MSMS_precursor` "+
"INNER JOIN `spectrum` ON spectrum.spectrum_id = MSMS_precursor.spectrum_spectrum_id "+
"INNER JOIN `feature` ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
"WHERE spectrum.scan_start_time BETWEEN feature.rtMin AND feature.rtMax "+
"AND MSMS_precursor.ion_mz BETWEEN feature.mzMin AND feature.mzMax "+
"AND feature.msrun_msrun_id = ?", spectrumFeature_InputValues)
print 'EXPLAIN QUERY PLAN: '
print self.cursor.fetchall()
import time
time0 = time.time()
self.cursor.execute("SELECT precursor_id, feature_table_id "+
"FROM `MSMS_precursor` "+
"INNER JOIN `spectrum` ON spectrum.spectrum_id = MSMS_precursor.spectrum_spectrum_id "+
"INNER JOIN `feature` ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
"WHERE spectrum.scan_start_time BETWEEN feature.rtMin AND feature.rtMax "+
"AND MSMS_precursor.ion_mz BETWEEN feature.mzMin AND feature.mzMax "+
"AND feature.msrun_msrun_id = ?", spectrumFeature_InputValues)
print 'query took:',time.time()-time0,'seconds'
time0 = time.time()
precursorFeatureIds = self.cursor.fetchall()
print 'it fetched:',len(precursorFeatureIds),'rows'
print 'fetchall took',time.time()-time0,'seconds'
time0 = time.time()
for precursorAndFeatureID in precursorFeatureIds:
feature_has_MSMS_precursor_inputValues = (precursorAndFeatureID[0], precursorAndFeatureID[1])
self.cursor.execute("INSERT INTO `feature_has_MSMS_precursor` VALUES(?,?)", feature_has_MSMS_precursor_inputValues)
print 'inserting took',time.time()-time0,'seconds'
self.connection.commit()
</code></pre>
<p>结果是:</p>
<pre><code>EXPLAIN QUERY PLAN:
[(0, 0, 2, u'SCAN TABLE feature (~100000 rows)'), (0, 1, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 2, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 0.0754859447479 seconds
it fetched: 12951 rows
fetchall took 54.2855291367 seconds
inserting took 0.602859973907 seconds
It took 54.9704811573 seconds
EXPLAIN QUERY PLAN:
[(0, 0, 2, u'SCAN TABLE feature (~100000 rows)'), (0, 1, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 2, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 0.579694032669 seconds
it fetched: 24972 rows
fetchall took 3950.08093309 seconds
inserting took 2.11575508118 seconds
It took 3952.80745602 seconds
EXPLAIN QUERY PLAN:
[(0, 0, 2, u'SCAN TABLE feature (~100000 rows)'), (0, 1, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 2, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 1.01185703278 seconds
it fetched: 6470 rows
fetchall took 1970.622962 seconds
inserting took 0.673867940903 seconds
It took 1972.31343699 seconds
</code></pre>
<hr/>
<p>SQLite create语句:</p>
<pre><code>-- -----------------------------------------------------
-- Table `feature`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feature` (
`feature_table_id` INT PRIMARY KEY NOT NULL ,
`feature_id` VARCHAR(40) NOT NULL ,
`intensity` DOUBLE NOT NULL ,
`overallquality` DOUBLE NOT NULL ,
`charge` INT NOT NULL ,
`content` VARCHAR(45) NOT NULL ,
`intensity_cutoff` DOUBLE NOT NULL,
`mzMin` DOUBLE NULL ,
`mzMax` DOUBLE NULL ,
`rtMin` DOUBLE NULL ,
`rtMax` DOUBLE NULL ,
`msrun_msrun_id` INT NOT NULL ,
CONSTRAINT `fk_feature_msrun1`
FOREIGN KEY (`msrun_msrun_id` )
REFERENCES `msrun` (`msrun_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE INDEX `fk_mzMin_feature` ON `feature` (`mzMin` ASC);
CREATE INDEX `fk_mzMax_feature` ON `feature` (`mzMax` ASC);
CREATE INDEX `fk_rtMin_feature` ON `feature` (`rtMin` ASC);
CREATE INDEX `fk_rtMax_feature` ON `feature` (`rtMax` ASC);
DROP TABLE IF EXISTS `spectrum`;
-- -----------------------------------------------------
-- Table `spectrum`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `spectrum` (
`spectrum_id` INT PRIMARY KEY NOT NULL ,
`spectrum_index` INT NOT NULL ,
`ms_level` INT NOT NULL ,
`base_peak_mz` DOUBLE NOT NULL ,
`base_peak_intensity` DOUBLE NOT NULL ,
`total_ion_current` DOUBLE NOT NULL ,
`lowest_observes_mz` DOUBLE NOT NULL ,
`highest_observed_mz` DOUBLE NOT NULL ,
`scan_start_time` DOUBLE NOT NULL ,
`ion_injection_time` DOUBLE,
`binary_data_mz` BLOB NOT NULL,
`binary_data_rt` BLOB NOT NULL,
`msrun_msrun_id` INT NOT NULL ,
CONSTRAINT `fk_spectrum_msrun1`
FOREIGN KEY (`msrun_msrun_id` )
REFERENCES `msrun` (`msrun_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE INDEX `fk_spectrum_spectrum_id_1` ON `spectrum` (`spectrum_id` ASC);
CREATE INDEX `fk_spectrum_scahn_start_time_1` ON `spectrum` (`scan_start_time` ASC);
DROP TABLE IF EXISTS `feature_has_MSMS_precursor`;
-- -----------------------------------------------------
-- Table `spectrum_has_feature`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `feature_has_MSMS_precursor` (
`MSMS_precursor_precursor_id` INT NOT NULL ,
`feature_feature_table_id` INT NOT NULL ,
CONSTRAINT `fk_spectrum_has_feature_spectrum1`
FOREIGN KEY (`MSMS_precursor_precursor_id` )
REFERENCES `MSMS_precursor` (`precursor_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_spectrum_has_feature_feature1`
FOREIGN KEY (`feature_feature_table_id` )
REFERENCES `feature` (`feature_table_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
CREATE INDEX `fk_feature_has_MSMS_precursor_feature1` ON `feature_has_MSMS_precursor` (`feature_feature_table_id` ASC);
CREATE INDEX `fk_feature_has_MSMS_precursor_precursor1` ON `feature_has_MSMS_precursor` (`MSMS_precursor_precursor_id` ASC);
</code></pre>
<p>如您所见,我已经从频谱和特性中的<code>mz</code>和<code>rt</code>值中创建了索引,因为我发现大部分时间都花在比较这些数字上。</p>
<p>为什么第一个样本比第二个和第三个样本快得多?查询时间与fetchall时间有什么关系?最重要的是,我有办法加快速度吗?</p>
<hr/>
<h2>更新1:</h2>
<p>在和同事交谈之后,可能是因为将一个点与二维空间(rtMin、rtMax、mzMin、mzMax)进行比较需要n^2次。这个<em>大致</em>对应于第二个fetchall占用的时间略大于60^2秒(第一个fetchall占用的时间约为该时间),并且它检索的行数略小于行数的两倍。但这并不能回答我的任何问题。</p>
<hr/>
<h2>更新2:</h2>
<p>我试着按照评论中的建议使用R*tree。我做了一张新桌子:</p>
<pre><code>CREATE VIRTUAL TABLE convexhull_edges USING rtree(
feature_feature_table_id,
rtMin, rtMax,
mzMin, mzMax,
);
</code></pre>
<p>并将我的查询更改为:</p>
<pre><code>self.cursor.execute("SELECT precursor_id, feature_table_id "+
"FROM `MSMS_precursor` "+
"INNER JOIN `spectrum` ON spectrum.spectrum_id = MSMS_precursor.spectrum_spectrum_id "+
"INNER JOIN `feature` ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
"INNER JOIN `convexhull_edges` ON convexhull_edges.feature_feature_table_id = feature.feature_table_id "
"WHERE spectrum.scan_start_time BETWEEN convexhull_edges.rtMin AND convexhull_edges.rtMax "+
"AND MSMS_precursor.ion_mz BETWEEN convexhull_edges.mzMin AND convexhull_edges.mzMax "+
"AND feature.msrun_msrun_id = ?", spectrumFeature_InputValues)
</code></pre>
<p>结果如下:</p>
<pre><code>EXPLAIN QUERY PLAN:
[(0, 0, 3, u'SCAN TABLE convexhull_edges VIRTUAL TABLE INDEX 2: (~0 rows)'), (0, 1, 2, u'SEARCH TABLE feature USING INDEX sqlite_autoindex_feature_1 (feature_table_id=?) (~1 rows)'), (0, 2, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 3, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 0.0572800636292 seconds
it fetched: 13140 rows
fetchall took 34.4445540905 seconds
EXPLAIN QUERY PLAN:
[(0, 0, 3, u'SCAN TABLE convexhull_edges VIRTUAL TABLE INDEX 2: (~0 rows)'), (0, 1, 2, u'SEARCH TABLE feature USING INDEX sqlite_autoindex_feature_1 (feature_table_id=?) (~1 rows)'), (0, 2, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 3, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 0.819370031357 seconds
it fetched: 25402 rows
fetchall took 3625.72873998 seconds
EXPLAIN QUERY PLAN:
[(0, 0, 3, u'SCAN TABLE convexhull_edges VIRTUAL TABLE INDEX 2: (~0 rows)'), (0, 1, 2, u'SEARCH TABLE feature USING INDEX sqlite_autoindex_feature_1 (feature_table_id=?) (~1 rows)'), (0, 2, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 3, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 0.878498077393 seconds
it fetched: 6761 rows
fetchall took 1419.34246588 seconds
inserting took 0.340960025787 seconds
It took 1420.56637716 seconds
</code></pre>
<p>所以比我以前的方法快了一点,但还是不够快。下一步我将尝试web-bod的解决方案。</p>
<hr/>
<h2>更新3</h2>
<p>使用WebBod的解决方案,我得到了以下时间:</p>
<pre><code>EXPLAIN QUERY PLAN:
[(0, 0, 2, u'SCAN TABLE feature (~100000 rows)'), (0, 1, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 2, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 0.0521960258484 seconds
it fetched: 13052 rows
fetchall took 90.5810132027 seconds
EXPLAIN QUERY PLAN:
[(0, 0, 2, u'SCAN TABLE feature (~100000 rows)'), (0, 1, 1, u'SEARCH TABLE spectrum USING INDEX fk_spectrum_scahn_start_time_1 (scan_start_time>? AND scan_start_time<?) (~3125 rows)'), (0, 2, 0, u'SEARCH TABLE MSMS_precursor USING INDEX fk_MSMS_precursor_spectrum_spectrum_id_1 (spectrum_spectrum_id=?) (~5 rows)')]
query took: 0.278959989548 seconds
it fetched: 25195 rows
fetchall took 4310.6012361 seconds
</code></pre>
<p>第三个遗憾的是因为重新启动而没有完成。所以这比我的第一个解决方案快一点,但是比使用R*Tree慢一点</p>
<hr/>
<h2>更新4</h2>
<p>在处理另一个异常缓慢的查询时,我看到它进入了一个不间断的睡眠状态(参见<a href="https://stackoverflow.com/questions/10854817/is-uninteruptable-sleep-the-cause-of-my-python-program-being-really-slow-and-if">this question</a>)。所以我在运行这个查询时检查了top,它在R和D状态之间切换,将CPU使用率从100%降低到50%。这可能就是为什么它在提供所有解决方案的情况下运行得如此缓慢的原因。</p>
<hr/>
<h2>更新5</h2>
<p>我迁移到MySQL,但得到的结果是一样的。</p>