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

Вы сопоставляете две большие таблицы. Некоторая быстрая математика: 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