很慢的选择查询，我怎么能加快速度呢？

Question

我问了两个相关的问题（How can I speed up fetching the results after running an sqlite query?和Is it normal that sqlite.fetchall() is so slow?）。我已经改变了一些东西并加快了速度，但是select语句仍然需要一个多小时才能完成。

我有一个表feature，它包含一个rtMin、rtMax、mzMin和mzMax值。这些值一起是矩形的角（如果您阅读了我以前的问题，我会分别保存这些值，而不是从convexhull表中获取min（）和max（），这样会更快）。
我得到了一个表spectrum，它有一个rt和一个mz值。我有一个表，当光谱的rt和mz值在特征的矩形中时，它将特征链接到光谱。

为此，我使用以下sql和python代码来检索频谱和特性的id：

self.cursor.execute("SELECT spectrum_id, feature_table_id "+
                    "FROM `spectrum` "+
                    "INNER JOIN `feature` "+
                    "ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
                    "WHERE spectrum.scan_start_time >= feature.rtMin "+
                    "AND spectrum.scan_start_time <= feature.rtMax "+
                    "AND spectrum.base_peak_mz >= feature.mzMin "+
                    "AND spectrum.base_peak_mz <= feature.mzMax")        
spectrumAndFeature_ids = self.cursor.fetchall()

for spectrumAndFeature_id in spectrumAndFeature_ids:
        spectrum_has_feature_inputValues = (spectrumAndFeature_id[0], spectrumAndFeature_id[1])
        self.cursor.execute("INSERT INTO `spectrum_has_feature` VALUES (?,?)",spectrum_has_feature_inputValues)

我对执行、获取和插入时间进行了计时，得到了以下结果：

query took: 74.7989799976 seconds
5888.845541 seconds since fetchall
returned a length of: 10822
inserting all values took: 3.29669690132 seconds

所以这个查询大约需要一个半小时，大部分时间都在执行fetchall（）。我怎样才能加快速度？我应该在python代码中进行rt和mz比较吗？

---

更新：

为了显示我得到了哪些索引，下面是这些表的create语句：

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 UNIQUE INDEX `id_UNIQUE` ON `feature` (`feature_table_id` ASC);
  CREATE INDEX `fk_feature_msrun1` ON `feature` (`msrun_msrun_id` ASC);



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,
  `binaray_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_msrun1` ON `spectrum` (`msrun_msrun_id` ASC);



CREATE  TABLE IF NOT EXISTS `spectrum_has_feature` (
  `spectrum_spectrum_id` INT NOT NULL ,
  `feature_feature_table_id` INT NOT NULL ,
  CONSTRAINT `fk_spectrum_has_feature_spectrum1`
    FOREIGN KEY (`spectrum_spectrum_id` )
    REFERENCES `spectrum` (`spectrum_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_spectrum_has_feature_feature1` ON `spectrum_has_feature` (`feature_feature_table_id` ASC);
  CREATE INDEX `fk_spectrum_has_feature_spectrum1` ON `spectrum_has_feature` (`spectrum_spectrum_id` ASC);

---

更新2：

我有20938个光谱，305742个特征和2个msrun。结果是10822场比赛。

---

更新3：

使用新索引（在spectrum（msrun_msrun_id，base_peak_mz）上创建索引fk_spectrum_msrun1_2）并在两次之间节省大约20秒： 查询时间：76.4599349499秒 自fetchall后5864.15418601秒

---

更新4：

从解释查询计划打印：

(0, 0, 0, u'SCAN TABLE spectrum (~1000000 rows)'), (0, 1, 1, u'SEARCH TABLE feature USING INDEX fk_feature_msrun1 (msrun_msrun_id=?) (~2 rows)') 

Answer 1

你正在关联两个大表。一些快速计算：30万x 20万=60亿行。如果这只是返回所有这些行的问题，那么您肯定会受到I/O的限制（但实际上仅限于（O）输出端）。但是，where子句过滤掉了几乎所有的内容，因为您只返回了10k行，所以您可以确定这里的CPU是有限的。

SQLite一次只能使用一个索引，但被称为“OR optimizations”的索引除外。此外，由于内部连接“are converted into additional terms of the WHERE clause”，因此不会从它们获得任何性能增益。

归根结底，SQLite将无法像saypostgresql等人那样高效地执行查询。

当我好奇地想知道你的查询可以优化多少的时候，我对你的场景进行了反复的研究。最后，似乎最好的优化是删除所有显式索引（！）。似乎SQLite动态地创建了一些索引，这些索引比我尝试的不同方法有更好的性能。

作为演示，请考虑从您的模式派生的此模式：

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);

feature有300k行，和spectrum20k（执行此操作的python代码在下面的某个地方）。由于定义INTEGER PRIMARY KEY，没有指定显式索引only implicit ones：

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.

使用上面的模式，SQLite提到它将在查询的生命周期中创建一个索引lnk_feature：

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)

即使我使用该列或其他列上的索引进行了测试，运行该查询的最快方式似乎是不使用任何这些索引。

我使用python运行上述查询的最快速度是20分钟。这包括完成.fetchall()。你提到在某个时刻你将拥有150倍的行。如果我是你，我会开始调查postgresql。。。请注意，您可以在线程中分割工作，并可能将完成查询的时间除以能够并发运行的线程数（即，除以可用的CPU数）。

无论如何，这是我使用的代码。您能自己运行它并报告查询在您的环境中运行的速度吗。请注意，我正在使用apsw，因此如果您不能使用它，则需要调整以使用自己的sqlite3模块。

#!/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)

我得到的输出：

54626 rows; 1210.96 seconds

Answer 2

在sql方面做得更好。

总之，使用索引！

Answer 3

使用between而不是>；=和<；=进行范围比较。

self.cursor.execute("SELECT spectrum_id, feature_table_id "+
                        "FROM `spectrum` "+
                        "INNER JOIN `feature` "+
                        "ON feature.msrun_msrun_id = spectrum.msrun_msrun_id "+
                        "WHERE spectrum.scan_start_time between feature.rtMin " + 
                        "AND feature.rtMax "+
                        "AND spectrum.base_peak_mz between feature.mzMin "+
                        "AND feature.mzMax")   

可以在spectrum.scan_start_time、feature.rtMin、feature.rtMax、spectrum.base_peak_mz、m feature.mzMin和feature.mzMax字段上创建非聚集索引。