Запрос SQLite3 выполняется почти мгновенно в некоторых версиях базы данных, но у других на несколько порядков дольше

Question

Я старомоден и уже несколько лет использую Banshee в качестве основного музыкального проигрывателя. К счастью, я в основном доволен этим, за исключением некоторой проблемы, которую я отлаживал в течение последних нескольких дней, когда при перетасовке на новую дорожку в плейлисте он зависает и имеет высокую загрузку ЦП для значительного количества время (пропорционально размеру плейлиста). Например, около 16 секунд для списка воспроизведения из 512 дорожек или более 3 минут для списка воспроизведения из 6000 дорожек. Я также использую Banshee на своем рабочем компьютере (хотя и с несколько меньшей библиотекой), и проблема там полностью отсутствует; перетасовка не занимает заметного времени при воспроизведении любого размера, даже 6000-трекового.

Как я уже сказал, я некоторое время интенсивно изучаю эту проблему. Сначала я попытался изменить файл библиотеки Banshee различными способами, чтобы посмотреть, смогу ли я создать его с тем же базовым содержимым, что и мой исходный, но без проблемы медленной перетасовки. В конце концов я обнаружил, что удаление большинства моих плейлистов "решило" проблему. Сравнивая библиотеки с проблемой и без нее, в процессе, приближающемся к бинарному поиску, мне удалось создать библиотечный файл, который обнаружил проблему медленного перемешивания, но мог быть преобразован в тот, который не возник, запустив одну команду обновления:

UPDATE sqlite_stat1 SET stat='18800 1447 1' WHERE rowid=18;

Изучив, что такое таблица sqlite_stat1, я понял, что это не ошибка в Banshee, а серьезная проблема с SQLite3 и тем, как она оптимизирует запросы. Выполнение команды ANALYZE для обновления таблицы sqlite_stat1 решает проблему с файлом библиотеки, который находится всего в одной команде от работы, но не для моей "настоящей" библиотеки; Я не уверен почему.

Затем я заметил, что у Banshee есть аргумент --debug-sql, который позволяет мне получить фактический запрос, который занимал так много времени. Он запускается после нескольких сотен других команд установки (таких как создание и заполнение временной таблицы, CoreCache), чтобы преобразовать базу данных во временное состояние, в котором этот запрос выполняется либо мгновенно, либо мучительно медленно:

SELECT CoreTracks.Rating,CoreTracks.LastStreamError,CoreTracks.TrackID,
CoreTracks.PrimarySourceID,CoreTracks.ArtistID,CoreTracks.AlbumID,CoreTracks.TagSetID,
CoreTracks.MusicBrainzID,CoreTracks.MimeType,CoreTracks.FileSize,
CoreTracks.FileModifiedStamp,CoreTracks.LastSyncedStamp,CoreTracks.Attributes,
CoreTracks.Title,CoreTracks.TitleSort,CoreTracks.TrackNumber,CoreTracks.TrackCount,
CoreTracks.Disc,CoreTracks.DiscCount,CoreTracks.Duration,CoreTracks.Year,
CoreTracks.Genre,CoreTracks.Composer,CoreTracks.Conductor,CoreTracks.Grouping,
CoreTracks.Copyright,CoreTracks.LicenseUri,CoreTracks.Comment,CoreTracks.BPM,
CoreTracks.BitRate,CoreTracks.SampleRate,CoreTracks.BitsPerSample,CoreTracks.Score,
CoreTracks.PlayCount,CoreTracks.SkipCount,CoreTracks.ExternalID,
CoreTracks.LastPlayedStamp,CoreTracks.LastSkippedStamp,CoreTracks.DateAddedStamp,
CoreTracks.DateUpdatedStamp,CoreTracks.Uri,CoreArtists.Name,CoreArtists.NameSort,
CoreAlbums.Title,CoreAlbums.TitleSort,CoreAlbums.ArtistName,CoreAlbums.ArtistNameSort,
CoreAlbums.IsCompilation,CoreAlbums.MusicBrainzID,CoreArtists.MusicBrainzID
, OrderID, CoreCache.ItemID
FROM CoreTracks,CoreArtists,CoreAlbums
INNER JOIN CorePlaylistEntries
    ON CoreTracks.TrackID = CorePlaylistEntries.TrackID
INNER JOIN CoreCache
    ON CorePlaylistEntries.EntryID = CoreCache.ItemID 
WHERE
    CoreCache.ModelID = 188 AND
    CoreArtists.ArtistID = CoreTracks.ArtistID AND
    CoreAlbums.AlbumID = CoreTracks.AlbumID 
AND 1=1
AND LastStreamError = 0
AND (LastPlayedStamp < 1518483204 OR LastPlayedStamp IS NULL)
AND (LastSkippedStamp < 1518483204 OR LastSkippedStamp IS NULL)
ORDER BY RANDOM ()
LIMIT 1;

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

Выполнение EXPLAIN QUERY PLAN для запроса в базе данных, где возникает проблема, дает следующий план:

0|0|0|SEARCH TABLE CoreTracks USING AUTOMATIC COVERING INDEX (LastStreamError=?)
0|1|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|3|4|SEARCH TABLE CoreCache USING AUTOMATIC COVERING INDEX (ModelID=?)
0|4|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

И запуск его в базе данных, где проблема не возникает, дает такой другой план:

0|0|4|SCAN TABLE CoreCache
0|1|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|2|0|SEARCH TABLE CoreTracks USING INTEGER PRIMARY KEY (rowid=?)
0|3|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|4|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

Даже после всей информации о проблеме, которую я собрал, у меня много вопросов. Почему SQLite использует другой, более медленный план запросов? Почему простое обновление кэшированной информации о CorePlaylistEntriesIndex иногда решает проблему, но не всегда? Какова основная причина того, что этот запрос занимает столь разное время для выполнения почти идентичных баз данных? (Я предполагаю некоторую оптимизацию, которая используется или не используется)

Для справки, я использую SQLite версии 3.8.2 и (когда я запускаю SQLite через Python) я использую Python 3.4.2. Я попытался запустить SQL, чтобы вызвать проблему в текущей версии SQLite 3.22, и обнаружил, что некоторые из команд установки (INSERT с вложенными SELECT) теперь занимают чрезмерно много времени. Я кратко попытался пропатчить исполняемый файл SQLite 3.22 в моей системе и запустить его с помощью Banshee, но проблема медленного перемешивания не изменилась.

Answer 1

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

Чтобы оценить селективность предложения WHERE, SQLite использует информацию, собранную ANALYZE. Но когда вы не запустили ANALYZE, или когда столбец не проиндексирован, база данных не имеет этой информации, и она просто предполагает, что любое предложение WHERE вида column = value в значительной степени подходит.

Выражение LastStreamError = 0 ищет базу данных так, как будто она может отфильтровать много строк, но на практике это, вероятно, нет.

Чтобы ускорить запрос, попробуйте добавить индекс на LastStreamError а затем запустить анализ.

Если возможно, обновите версию SQLite; оптимизатор запросов постоянно совершенствуется.