1. Обзор

analyze-stmt: hide

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

1.1. Рекомендуемый образец использования

Приложения с долговечными базами данных, которые используют сложные запросы, должны рассмотреть управление следующими командами только до закрытия каждого соединения с базой данных:

PRAGMA analysis_limit=400;
PRAGMA optimize;

optimize pragma обычно не делает ничего, но будет иногда управлять ANALYZE, если будет казаться, что выполнение этого будет полезно для планировщика запроса. analysis_limit pragma ограничивает объем любого ANALYZE, чтобы optimize pragma выполнялась так, чтобы это не потребляло слишком много циклов CPU. Константа "400" может быть приспособлена по мере необходимости. Значения от 100 до 1000 работают хорошо для большинства приложений.

2. Детали

Реализация по умолчанию хранит всю статистику в единственной таблице "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". Все эти таблицы коллективно упоминаются как "таблицы статистики".

Содержание таблиц статистики может быть запрошено, используя SELECT и может быть изменено, используя DELETE, INSERT и UPDATE. DROP TABLE работает с таблицами статистики с SQLite version 3.7.9. (2011-11-01) ALTER TABLE с ними не работает. Соответствующий уход должен использоваться, изменяя содержание таблиц статистики, поскольку недействительное содержание может заставить SQLite выбирать неэффективные планы запросов. Вообще говоря, не нужно изменять содержание таблиц статистики никаким механизмом кроме команды ANALYZE. См. "подробности".

Статистические данные, собранные ANALYZE, автоматически не обновляются как содержание изменений базы данных. Если содержание базы данных изменяется значительно, или если схема базы данных изменяется, то нужно рассмотреть запуск повторно команды ANALYZE, чтобы обновить статистику.

Планировщик вопроса загружает содержание таблиц статистики в память, когда схема прочитана. Следовательно, когда меняются таблицы статистики непосредственно, SQLite немедленно не заметит изменений. Приложение может вынудить планировщик запроса перечитать таблицы через ANALYZE sqlite_schema.

3. Автозапуск ANALYZE

PRAGMA optimize будет автоматически управлять ANALYZE на отдельных таблицах по мере необходимости. Рекомендуемая практика: вызвать PRAGMA optimize прежде, чем закрыть каждое соединение с базой данных.

Каждое SQLite соединение с базой данных делает запись случаев, когда планировщик запроса извлек бы выгоду из наличия точных результатов ANALYZE под рукой. Эти отчеты проводятся в памяти и накапливаются по жизни соединения с базой данных. PRAGMA optimize смотрит на те отчеты, и управляет ANALYZE только на тех таблицах, для которых новые или обновленные данные ANALYZE кажутся вероятно полезными. В большинстве случаев PRAGMA optimize не будет управлять ANALYZE, но он будет иногда делать так для таблиц, которые прежде никогда не анализировались, или для таблиц, которые выросли значительно после того, как они были в последний раз проанализированы.

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

Приложения, которые желают большего контроля, могут выполнить PRAGMA optimize(0x03), чтобы получить список команд ANALYZE, которые SQLite планировал, но не выполнил. Если возвращенный набор не пуст, приложение может тогда принять решение о том, управлять ли предложенными командами ANALYZE, возможно после запроса пользователя.

PRAGMA optimize доступна с SQLite 3.18.0 (2017-03-28).

4. ANALYZE для больших баз данных

По умолчанию ANALYZE делает полный просмотр каждого индекса. Это может быть медленно для больших баз данных. Так начиная с SQLite version 3.32.0 (2020-05-22), PRAGMA analysis_limit может использоваться, чтобы ограничить объем просмотра выполненного ANALYZE, и таким образом помочь ANALYZE работать быстрее даже на очень больших файлах базы данных. Мы называем это "приблизительным ANALYZE".

Рекомендуемый образец использования для 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". Результаты не совсем точные, но они достаточно точны, и то, что результаты вычисляются настолько быстрее, означает, что разработчики, более вероятно, вычислят их. Приблизительный ANALYZE лучше, чем никакого ANALYZE вообще.

4.1. Ограничения приблизительного ANALYZE

Содержание таблицы sqlite_stat4 не может быть вычислено чем-то меньшим, чем полным просмотром. Следовательно, если аналитический предел отличный от нуля определяется, таблица sqlite_stat4 не вычисляется.