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

Я задал два связанных вопроса ( как я могу ускорить получение результатов после запуска SQL-запроса?) И нормально ли, что sqlite.fetchall () так медленно? ). Я изменил некоторые вещи и получил некоторое ускорение, но для завершения операции выбора все еще требуется более часа.

У меня есть feature таблицы, которая содержит значения rtMin , rtMax , mzMin и mzMax . Эти значения вместе являются углами прямоугольника (если вы читаете мои старые вопросы, я сохраняю эти значения отдельно, вместо того, чтобы получать min () и max () из таблицы convexhull , работает быстрее).
И я получил табличный spectrum с rt и значением mz . У меня есть таблица, которая связывает функции с спектрами, когда значения rt и mz спектра находятся в прямоугольнике признака.

Для этого я использую следующий код sql и python для извлечения идентификаторов спектра и функции:

 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) 

Я приурочил время выполнения, fetchall и insert и получил следующее:

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

Таким образом, этот запрос занимает около полутора часов, большую часть времени делает fetchall (). Как я могу ускорить это? Должен ли я выполнять сравнение rt и mz в коде python?


Обновить:

Чтобы показать, какие индексы я получил, вот инструкции 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 msruns. Это приводит к 10822 матчам.


обновление 3:

Используя новый индекс (CREATE INDEX fk_spectrum_msrun1_2 ON spectrum ( msrun_msrun_id , base_peak_mz );) и между base_peak_mz около 20 секунд: запрос занял: 76.4599349499 секунд 5864.15418601 секунд после fetchall


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

Печать с помощью EXPLAIN QUERY PLAN:

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

Вы сопоставляете две большие таблицы. Некоторая быстрая математика: 300k x 20k = 6 миллиардов строк. Если бы речь шла только о возврате всех этих строк, вы наверняка были бы привязаны к вводу / выводу (но на самом деле только на стороне вывода ( O )). Тем не менее, ваше предложение where отфильтровывает почти все, так как у вас есть только 10k строк, поэтому вы наверняка находитесь здесь.

SQLite не может использовать более одного индекса за раз, за ​​исключением того, что называется « оптимизациями OR ». Кроме того, вы не получаете прироста производительности от внутренних объединений, поскольку они « преобразуются в дополнительные условия предложения WHERE ».

Суть в том, что SQLite не сможет выполнить ваш запрос так же эффективно, как и postgresql et al.

Я играл со своим сценарием, поскольку мне было любопытно узнать, насколько оптимизирован ваш запрос. В конечном счете, кажется, что лучшая оптимизация – удалить все явные индексы (!). Похоже, что 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 строк, а spectrum 20k (код python, который делает это где-то ниже). Явного указателя нет, только неявные из- за определения INTEGER PRIMARY KEY :

INTEGER PRIMARY KEY, как ограничения UNIQUE, так и PRIMARY KEY реализуются путем создания индекса в базе данных (так же, как и оператор CREATE UNIQUE INDEX). Такой индекс используется как любой другой индекс в базе данных для оптимизации запросов. В результате часто нет преимущества (но значительных накладных расходов) при создании индекса в наборе столбцов, которые уже коллективно подчиняются ограничению UNIQUE или PRIMARY KEY.

Используя вышеприведенную схему, 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 если бы вы были вами; -) … Обратите внимание, что вы можете разделить работу в потоках и потенциально разделить время на выполнение запроса по количеству потоков, которые будут выполняться одновременно (т.е. количество доступных ЦП).

В любом случае, вот код, который я использовал. Можете ли вы запустить его самостоятельно и сообщить, как быстро запрос выполнялся в вашей среде. Обратите внимание, что я использую 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 

Сделайте это лучше на части sql.

Одним словом, используйте ИНДЕКСЫ !

Используйте вместо> = и <= для дальномерного сравнения.

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

Вы можете создать некластеризованный индекс в параметрах spect.scan_start_time, feature.rtMin, feature.rtMax, spectrum.base_peak_mz, m feature.mzMin и feature.mzMax.

В нормальной РСУБД следует делать хеш-соединение между таблицами spectrum и features . Если вы можете заставить его сделать хеш-соединение, запрос должен лететь.

Однако вы можете попробовать один запрос?

 self.cursor.execute("INSERT INTO `spectrum_has_feature` " + "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") 

Я запустил сценарий Ludo, и он сообщил о 1451 секунде в моей системе. Затем я добавил следующий индекс, который сократил время до 875 секунд (уменьшение на 40%):

 CREATE INDEX idx1 ON feature (lnk_feature, mzMin, mzMax, rtMin, rtMax); 

Все еще не ослепительно быстро, но лучше. Вот результат EXPLAIN QUERY PLAN:

 0|0|0|SCAN TABLE spectrum (~20000 rows) 0|1|1|SEARCH TABLE feature USING COVERING INDEX idx1 (lnk_feature=? AND mzMin<?) (~7 rows) 

Обратите внимание, что это индекс покрытия, что означает, что SQLite может считывать все поля, которые ему нужны из индекса, и поэтому их никогда не должно читать из таблицы функций. Он также может использовать индекс для двух из пяти условий в предложении WHERE (вместо одного).

Изменить это

 CREATE INDEX `fk_spectrum_msrun1` ON `spectrum` (`msrun_msrun_id` ASC); 

к одному из них (в зависимости от того, что более избирательно)

 CREATE INDEX `fk_spectrum_msrun1_1` ON `spectrum` (`msrun_msrun_id`, `scan_start_time`); CREATE INDEX `fk_spectrum_msrun1_2` ON `spectrum` (`msrun_msrun_id`, `base_peak_mz`); 

Первая из них может ускорить сравнение на scan_start_time а во втором – сравнения на base_peak_mz . Поскольку это сравнение неравенств, индекс в обоих столбцах не является полезным.

1. Используйте вместо <= или =>.

2.Add index на scan_start_time и base_peak_mz