Я рекомендую ознакомиться с Функции JSON в SQLite: полный справочник и JSONB, так как правильный выбор методов работы с JSON в запросах может значительно улучшить общую производительность вашей базы данных.
ANALYZE собирает данные о таблицах и индексах, храня их во внутренних таблицах базы данных для оптимизатора запросов. Если аргументы не указаны, команда анализирует основную базу данных и все присоединённые к ней базы данных. Указание имени схемы подразумевает анализ всех таблиц и индексов в этой базе, а имя таблицы ведёт к анализу самой таблицы и её индексов. Указание имени индекса означает анализ только этого индекса.
Когда и как запускать ANALYZE: рекомендуемые шаблоны
Хотя использование ANALYZE не обязательно, его запуск перед сложными запросами, имеющими много вариантов выполнения, позволяет оптимизатору запросов выбрать наилучший вариант, что может существенно улучшить производительность.
Ниже описаны два рекомендуемых подхода к тому, когда и как запускать ANALYZE — в порядке предпочтения.
Как использовать PRAGMA optimize для автоматического запуска ANALYZE
Команда PRAGMA optimize будет автоматически запускать ANALYZE при необходимости. Рекомендуемое использование:
Приложения с кратковременными соединениями с базой данных должны выполнять PRAGMA optimize; один раз, непосредственно перед закрытием каждого соединения с базой данных.
Приложения, использующие долгоживущие соединения с базой данных, должны выполнять PRAGMA optimize=0x10002; при первом открытии соединения, а затем также периодически выполнять PRAGMA optimize; — например, раз в день или чаще, если база данных активно изменяется.
Все приложения должны выполнять PRAGMA optimize; после изменения схемы, особенно после одного или нескольких операторов CREATE INDEX.
Команда PRAGMA optimize, как правило, ничего не делает, но иногда запускает одну или несколько операций ANALYZE для отдельных таблиц базы данных, если это окажется полезным для оптимизатора запросов. С версии SQLite 3.46.0 (2024-05-23) команда PRAGMA optimize быстро завершает выполнение даже на больших базах данных, ограничивая область действия операций ANALYZE для отдельных таблиц.
PRAGMA optimize обычно запускает ANALYZE только для тех таблиц, к которым ранее обращалось текущее соединение с базой данных, или для таблиц без записей в sqlite_stat1. Если в аргумент добавлен бит 0x10000, PRAGMA optimize проверяет все таблицы на необходимость ANALYZE, что полезно при новом соединении с базой данных, когда история запросов отсутствует.
Дополнительную информацию см. в разделах «Автоматический запуск ANALYZE» и «Приближённый ANALYZE для больших баз данных» ниже.
Фиксированные результаты ANALYZE
Запуск ANALYZE может влиять на выбор плана выполнения запросов, что чаще всего приводит к улучшению их эффективности. Тем не менее, в некоторых редких случаях ANALYZE может вызвать замедление работы отдельных запросов.
Разработчики иногда хотят, чтобы после фиксации схемы и логики приложения SQLite использовал те же планы запросов, что и во время разработки и тестирования. Это позволяет им быть уверенными в том, что все копии приложения, разосланные пользователям, будут придерживаться одинаковых планов запросов, независимо от внесённых клиентами данных, что помогает в воспроизводстве жалоб на проблемы с производительностью.
Для обеспечения согласованности запросов не стоит запускать полный ANALYZE в приложении. Вместо этого стоит выполнять ANALYZE на тестовой базе данных и сохранять результаты в SQL-скрипте, который будет использован для инициализации новых экземпляров базы данных.
.mode list
SELECT 'ANALYZE sqlite_schema;' || 'DELETE FROM sqlite_stat1;' || 'INSERT INTO sqlite_stat1(tbl,idx,stat)VALUES' || (SELECT group_concat(format('(%Q,%Q,%Q)',tbl,idx,stat),',') FROM sqlite_stat1) || ';ANALYZE sqlite_schema;';
При создании нового экземпляра базы данных в развернутых копиях приложения или при каждом запуске выполняйте команды, сгенерированные предыдущим скриптом. Это заполнит sqlite_stat1 так же, как было на этапе разработки, обеспечивая схожесть выбранных планов запросов в рабочей среде и лаборатории.
sqlite3_exec(db, zStat1Init, 0, 0, 0);
Возможно, также стоит добавить BEGIN; в начало строковой константы и COMMIT; в конец — в зависимости от контекста, в котором выполняется скрипт.
Дополнительную информацию см. в разделе «гарантия стабильности оптимизатора запросов».
Подробности реализации
Реализация по умолчанию хранит всю статистику в единственной таблице с именем sqlite_stat1. Если SQLite скомпилирован с параметром SQLITE_ENABLE_STAT4, дополнительные данные гистограммы собираются и сохраняются в sqlite_stat4.
Более старые версии SQLite использовали таблицу sqlite_stat2 или sqlite_stat3 при компиляции с SQLITE_ENABLE_STAT2 или SQLITE_ENABLE_STAT3, однако все последние версии SQLite игнорируют таблицы sqlite_stat2 и sqlite_stat3. Будущие улучшения могут создавать дополнительные внутренние таблицы с тем же шаблоном именования, но с конечной цифрой больше «4». Все эти таблицы в совокупности называются «таблицами статистики» (stat tables).
Содержимое таблиц статистики можно запрашивать с помощью SELECT и изменять с помощью команд DELETE, INSERT и UPDATE. Команда DROP TABLE работает с таблицами статистики начиная с версии SQLite 3.7.9 (2011-11-01). Команда ALTER TABLE не работает с таблицами статистики.
При изменении содержимого таблиц статистики следует проявлять надлежащую осторожность, поскольку некорректное содержимое может привести к тому, что SQLite будет выбирать неэффективные планы запросов. Как правило, не следует изменять содержимое таблиц статистики никаким иным способом, кроме вызова команды ANALYZE.
Дополнительную информацию см. в разделе «Ручное управление планами запросов с помощью таблиц SQLITE_STAT».
Статистика, собранная ANALYZE, не обновляется по мере изменения содержимого базы данных. Если содержимое базы данных существенно изменилось или изменилась схема базы данных, следует рассмотреть возможность повторного запуска команды ANALYZE для обновления статистики.
Оптимизатор запросов загружает содержимое таблиц статистики в память при чтении схемы. Поэтому, когда приложение напрямую изменяет таблицы статистики, SQLite не сразу замечает эти изменения. Приложение может принудить оптимизатор запросов перечитать таблицы статистики, выполнив ANALYZE sqlite_schema.
Автоматический запуск ANALYZE
Команда PRAGMA optimize автоматически запускает ANALYZE для отдельных таблиц по мере необходимости. Рекомендуемая практика — вызывать оператор PRAGMA optimize непосредственно перед закрытием каждого соединения с базой данных.
Или, если приложение держит одно соединение с базой данных открытым в течение длительного времени, при первом открытии соединения следует выполнить PRAGMA optimize=0x10002, а затем периодически запускать PRAGMA optimize; — например, раз в день или даже раз в час.
Каждое соединение SQLite с базой данных фиксирует случаи, когда оптимизатор запросов мог бы извлечь пользу из наличия актуальных результатов ANALYZE. Эти записи хранятся в памяти и накапливаются на протяжении всего времени жизни соединения с базой данных.
Команда PRAGMA optimize анализирует эти записи и запускает ANALYZE только для тех таблиц, для которых новые или обновлённые данные ANALYZE, по всей видимости, окажутся полезными. В большинстве случаев PRAGMA optimize не запускает ANALYZE, однако иногда делает это — либо для таблиц, которые никогда ранее не анализировались, либо для таблиц, значительно выросших с момента последнего анализа.
Поскольку действия PRAGMA optimize в определённой мере определяются предшествующими запросами, выполненными в рамках того же соединения с базой данных, рекомендуется откладывать PRAGMA optimize до момента закрытия соединения — когда оно успело накопить как можно больше информации об использовании.
Также разумно установить таймер для запуска PRAGMA optimize каждые несколько часов или дней для соединений, остающихся открытыми длительное время. При запуске PRAGMA optimize сразу после открытия соединения с базой данных можно добавить бит 0x10000 к аргументу-битовой маске (тем самым команда принимает вид PRAGMA optimize=0x10002), что приводит к проверке всех таблиц, включая те, к которым не обращались в рамках текущего соединения.
Команда PRAGMA optimize была впервые введена в SQLite 3.18.0 (2017-03-28) и является холостой операцией для всех более ранних версий SQLite. Команда PRAGMA optimize была существенно улучшена в SQLite 3.46.0 (2024-05-23), и рекомендации в данной документации основаны на этих улучшениях.
Приложениям, использующим более ранние версии SQLite, следует обратиться к соответствующей документации за более актуальными советами по оптимальному использованию PRAGMA optimize.
Приближённый ANALYZE для больших баз данных SQLite
По умолчанию ANALYZE выполняет полное сканирование каждого индекса. Для больших баз данных это может быть медленным. Поэтому начиная с версии SQLite 3.32.0 (2020-05-22) команда PRAGMA analysis_limit может использоваться для ограничения объёма сканирования, выполняемого ANALYZE, и тем самым ускорить его работу даже на очень больших файлах баз данных. Такой режим работы называется «приближённым ANALYZE» (approximate ANALYZE).
Рекомендуемый шаблон использования pragma analysis_limit выглядит следующим образом:
PRAGMA analysis_limit=1000;
Эта pragma указывает команде ANALYZE начать полное сканирование индекса в обычном режиме. Но когда количество просмотренных строк достигает 1000 (или иного лимита, заданного pragma), команда ANALYZE начинает предпринимать действия для остановки сканирования. Если крайний левый столбец индекса изменился хотя бы один раз за предыдущие 1000 шагов, анализ останавливается немедленно.
Но если крайний левый столбец всегда оставался одинаковым, ANALYZE пропускает вперёд до первой записи с другим крайним левым столбцом и считывает дополнительные 1000 строк перед завершением.
Детали эффектов лимита анализа, описанные в предыдущем абзаце, могут измениться в будущих версиях SQLite. Однако основная идея останется прежней: лимит анализа N будет стремиться ограничить количество просматриваемых строк в каждом индексе приблизительно до N.
Рекомендуются значения N от 100 до 1000. Чтобы отключить лимит анализа и заставить ANALYZE выполнять полное сканирование каждого индекса, установите лимит анализа равным 0. Значение по умолчанию для лимита анализа равно 0 из соображений обратной совместимости.
Значения, помещаемые в таблицу sqlite_stat1 приближённым ANALYZE, не совпадают в точности с теми, что были бы вычислены при неограниченном анализе. Но, как правило, они достаточно близки.
Статистика индексов в таблице sqlite_stat1 в любом случае является приближённой, поэтому тот факт, что результаты приближённого ANALYZE несколько отличаются от результатов традиционного ANALYZE с полным сканированием, имеет незначительное практическое значение.
Можно построить патологический случай, при котором приближённый ANALYZE заметно уступает ANALYZE с полным сканированием, однако такие случаи редко встречаются в реальных задачах.
Хорошее практическое правило, которым я руководствуюсь сам, — всегда устанавливать PRAGMA analysis_limit=N для N от 100 до 1000 перед запуском ANALYZE. Раньше это также рекомендовалось делать перед запуском PRAGMA optimize, однако начиная с версии 3.46.0 (2024-05-23) это происходит автоматически.
Результаты при использовании PRAGMA analysis_limit не столь точны, но достаточно точны, а то, что они вычисляются значительно быстрее, означает, что разработчики с большей вероятностью будут их вычислять. Приближённый ANALYZE лучше, чем полное отсутствие ANALYZE.
Ограничения приближённого ANALYZE
Содержимое таблицы sqlite_stat4 не может быть вычислено без полного сканирования. Поэтому, если задан ненулевой лимит анализа, таблица sqlite_stat4 не вычисляется.
Типичные ошибки при работе с ANALYZE
На практике я замечаю несколько повторяющихся ошибок, которые стоит держать в голове. Мой совет — пройтись по этому списку перед тем, как интегрировать ANALYZE в рабочий процесс.
Запуск ANALYZE без учёта размера базы данных. Если база данных большая, а лимит анализа не задан, полное сканирование всех индексов может занять значительное время и заблокировать другие операции. Используйте PRAGMA analysis_limit для контроля над этим поведением.
Изменение таблиц статистики вручную без последующего вызова ANALYZE sqlite_schema. Оптимизатор запросов не замечает прямых изменений в sqlite_stat1 до тех пор, пока схема не будет перечитана. Без явного вызова ANALYZE sqlite_schema новые данные статистики не вступят в силу в рамках текущего соединения.
Отказ от PRAGMA optimize в пользу ручного ANALYZE на каждом соединении. Ручной ANALYZE всегда выполняет полное сканирование (если не задан analysis_limit), тогда как PRAGMA optimize принимает взвешенное решение о том, нужен ли анализ вообще. Для большинства приложений PRAGMA optimize — более экономичный выбор.
Игнорирование устаревшей статистики. Статистика, собранная ANALYZE, не обновляется автоматически при изменении данных. Если таблица существенно выросла или изменилась её схема, старые данные в sqlite_stat1 могут привести к выбору неоптимальных планов запросов.
Использование sqlite_stat2 и sqlite_stat3 в современных версиях SQLite. Эти таблицы игнорируются начиная с актуальных версий SQLite. Если в базе данных присутствуют данные в этих таблицах, они не оказывают никакого влияния на оптимизатор запросов.
Часто задаваемые вопросы об ANALYZE в SQLite
Нужно ли запускать ANALYZE при каждом запуске приложения? Нет. Рекомендуется использовать PRAGMA optimize; перед закрытием соединения — эта команда сама решает, нужен ли ANALYZE, и запускает его только при необходимости.
Что произойдёт, если не запускать ANALYZE вообще? SQLite будет строить планы запросов без статистики о распределении данных. Для простых запросов это несущественно, но для сложных запросов с несколькими возможными планами выполнения отсутствие статистики может привести к выбору неоптимального плана и заметному замедлению.
Чем приближённый ANALYZE отличается от полного? Приближённый ANALYZE ограничивает количество просматриваемых строк в каждом индексе значением, заданным через PRAGMA analysis_limit. Результаты немного менее точны, но вычисляются значительно быстрее. Таблица sqlite_stat4 при этом не заполняется.
Можно ли вручную редактировать таблицу sqlite_stat1? Технически — да, с помощью INSERT, UPDATE и DELETE. Однако некорректные значения могут привести к выбору неэффективных планов запросов. После ручного изменения необходимо выполнить ANALYZE sqlite_schema, чтобы оптимизатор запросов перечитал статистику.
Когда стоит использовать фиксированные результаты ANALYZE вместо динамического запуска? Когда важна предсказуемость планов запросов во всех развёрнутых экземплярах приложения. В этом случае ANALYZE запускается один раз на тестовой базе данных, результаты фиксируются в виде SQL-скрипта и воспроизводятся при инициализации каждого нового экземпляра базы данных.
