Подтвердить что ты не робот

Почему один и тот же запрос SQLite в 30 раз медленнее при получении всего в два раза больше результатов?

Я работаю над тем, чтобы ускорить запрос, который я использую примерно неделю, и задал здесь несколько вопросов (Как ускорить получение результатов после запуска sqlite query?, Нормально ли, что sqlite.fetchall() работает так медленно? Как использовать min() и max() эффективным образом?).

С очень полезной помощью из ответов, приведенных там, мне удалось получить время до запроса sqlite, взяв 100.95 секунд и fetchall: 1485.43. Этого было еще недостаточно, поэтому, попробовав несколько разных индексов, мне удалось получить время запроса до 0.08 секунд для одного образца, а время ввода - до 54.97 секунд. Поэтому мне показалось, что мне удалось ускорить все.

Затем запрос выполняется для следующего образца, принимая 0.58 секунды, а fetchall принимает 3952.80 секунды. Для третьего образца запрос занял 1.01 секунды и взял 1970.67 секунды для fetchall.

В первом примере выбраны строки 12951, второй образец - 24972 строки и третьи 6470 строк. Мне очень любопытно, почему первый образец был настолько быстрым, чтобы извлекать строки, когда в качестве второго примера он получал только половину суммы.

Код (spectrumFeature_inputValues равен (1,), (2,) и (3,) из трех использованных образцов.):

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

и результаты:

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

SQLite создает инструкции:

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

Как вы можете видеть, я сделал индексы из значений mz и rt как в спектре, так и в функции, потому что я решил, что большинство времени проводится, сравнивая эти числа вместе.

Итак, почему первый образец намного быстрее второго и третьего? И как время запроса относится к времени ввода? Самое главное, есть ли способ ускорить это?

Обновление 1:

После разговора с коллегой это, вероятно, связано с тем, что сравнение точки с 2-мерным измерением (rtMin, rtMax, mzMin, mzMax) займет n ^ 2 раза. Это примерно соответствует второму fetchall, принимающему бит более 60 ^ 2 секунд (приблизительное время, когда первый fetchall взял), и он получил немного меньше, чем в два раза больше количества строк. Однако это не отвечает ни на один из моих вопросов.

Обновление 2:

Я пробовал использовать дерево R *, как было сказано в комментариях. Я сделал новую таблицу:

CREATE VIRTUAL TABLE convexhull_edges USING rtree(
   feature_feature_table_id,             
   rtMin, rtMax,      
   mzMin, mzMax,       
);

и измените мой запрос на:

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)

Это дало следующие результаты:

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

Так немного быстрее, чем мой предыдущий способ, но все же не достаточно быстрый. Затем я собираюсь попробовать решение web_bod.

Обновление 3

Используя решение web_bod, я получил следующие моменты:

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

Третий печально не закончил из-за перезагрузки. Так что это немного быстрее, чем мое первое решение, но медленнее, чем использование R * Tree

Обновление 4

Работая над другим запросом, который шел невероятно медленно, я увидел, что он идет в непрерывный сон (см. этот вопрос). Поэтому я проверил top во время выполнения этого запроса и переключился между состояниями R и D, снизив загрузку процессора с 100 до 50%. Это может быть причиной того, что он работает так медленно со всеми предоставляемыми решениями.

Обновление 5

Я перешел на MySQL, но получаю те же результаты.

4b9b3361

ОТВЕТЫ

Ответ 1

Время выполнения геометрически пропорционально количеству строк в каждой таблице, а не арифметически, например

3 tables with 10 rows each => 1,000 comparision

3 tables with 10, 10 and 40 rows => 4,000 comparisons

3 tables with 20 rows each => 8,000 comparisons

Возможно, вы могли бы повторно подвергнуть сомнению запрос, чтобы избежать некоторых соединений/курсоров - когда вам нужен ответ?

Не могли бы вы сделать что-то вроде этого:

SELECT precursor_id, feature_table_id 
FROM MSMS_precursor
INNER JOIN 

    (
        SELECT mzMin, mzMax, rtMin, rtMax, spectrum_id, feature_table_id, msrun_msrun_id

        FROM spectrum
        INNER JOIN 

           (select feature_table_id, mzMin, mzMax, rtMin, rtMax, msrun_msrun_id
            from feature
            where feature.msrun_msrun_id = 'value'
           ) subquery 

        ON subquery.msrun_msrun_id = spectrum.msrun_msrun_id
        WHERE 
            spectrum.scan_start_time BETWEEN subquery.rtMin AND subquery.rtMax 
    ) subquery

    ON subquery.spectrum_id = MSMS_precursor.spectrum_spectrum_id 

WHERE 
    MSMS_precursor.ion_mz BETWEEN subquery.mzMin AND subquery.mzMax

Использование подзапроса позволяет сократить количество сравнений между таблицами - вы можете быстро отфильтровать нежелательные объекты, а затем несвязанные спектры перед поиском подходящих прекурсоров.

Я не использую SQLLite, но принцип все равно должен применяться.

ОБНОВЛЕНО: исправлена ​​ошибка SQL

Примечания:

Вам не нужно беспокоиться об AND, вы получите:

  • где feature.msrun_msrun_id = 'value'
  • для этих функций и где spectrum.scan_start_time МЕЖДУ subquery.rtMin И subquery.rtMax
  • для этих спектров и где MSMS_precursor.ion_mz BETWEEN subquery.mzMin AND subquery.mzMax

ОБНОВЛЕНИЕ 18 мая:

Это индексирование!!! у вас есть индексы в полях поиска, но не в полях, участвующих в объединениях - индексы внешнего ключа действительно повышают производительность:

CREATE INDEX `fk_msrun_msrun_id_feature` ON `feature` (`msrun_msrun_id` ASC); 
CREATE INDEX `fk_spectrum_spectrum_id_feature` ON `feature` (`msrun_msrun_id` ASC); 
CREATE INDEX `fk_spectrum_spectrum_id_MSMS_precursor` ON `MSMS_precursor` (`spectrum_spectrum_id` ASC);

Ответ 2

Я предлагаю вам попробовать использовать R * Tree index, они предназначены для эффективных запросов диапазона.

Я вообще не использовал R * Tree, просто прочитал документацию, но я думаю, что вы можете использовать ее неправильно. Вы можете попробовать изменить свой запрос, чтобы использовать

WHERE convexhull_edges.rtMin <= spectrum.scan_start_time AND convexhull_edges.rtMax >= spectrum.scan_start_time AND
convexhull_edges.mzMin <= MSMS_precursor.ion_mz AND convexhull_edges.mzMax >= MSMS_precursor.ion_mz

который должен быть эквивалентен вашему текущему запросу, но я думаю, что он должен быть быстрее (вы должны выбирать диапазон из дерева R *, а не сравнивать точку с диапазоном)

Ответ 3

Рассмотрите возможность использования покрытия индексов в таблицах, участвующих в вашем запросе.

Вы действительно получаете ограниченное количество столбцов в своем выражении select и соответствующих предложениях inner join и where. Используя индекс покрытия с хорошо упорядоченными столбцами, вы должны получить очень быстрый запрос, то есть вы удалите scan table в пользу search table с помощью covering index.

Попробуйте использовать эти индексы в своих таблицах:

CREATE INDEX `fk_covering_feature` ON `feature` (`msrun_msrun_id`,`mzMin`,`mzMax`,`rtMin`,`rtMax`,`feature_table_id`);
CREATE INDEX `fk_covering_spectrum` ON  `spectrum` (`msrun_msrun_id`,`scan_start_time`,`spectrum_id`);
CREATE INDEX `fk_covering_MSMS_precursor` ON  `MSMS_precursor` (`spectrum_spectrum_id`,`ion_mz`,`precursor_id`);

При переходе на скорость вы также должны намекать, что планировщик запросов понимает, что msrun_msrun_id является константой для проверки таблиц feature и spectrum. Добавьте постоянный тест в свой запрос, поставив этот дополнительный тест в конце запроса (и дважды пропустите spectrumFeature_InputValues):

"AND spectrum.msrun_msrun_id = ?"