АНАЛИЗ

1. Обзор

analyze-stmt: скрыть

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

2. Рекомендуемые шаблоны использования

Использование АНАЛИЗА никогда не является обязательным. Однако, если приложение выполняет сложные запросы с множеством возможных планов запросов, оптимизатор запросов сможет выбрать лучший план, если был выполнен АНАЛИЗ. Это может привести к значительному улучшению производительности для некоторых запросов.

Два рекомендуемых подхода к тому, когда и как выполнять АНАЛИЗ, описаны в следующих подразделах в порядке предпочтения.

2.1. Периодически выполняйте "PRAGMA optimize"

Команда PRAGMA optimize автоматически выполнит АНАЛИЗ при необходимости. Рекомендуемое использование:

1. Приложения с кратковременными подключениями к базе данных должны выполнить "PRAGMA optimize;" один раз непосредственно перед закрытием каждого подключения к базе данных.

2. Приложения с долговременными подключениями к базе данных должны выполнить "PRAGMA optimize=0x10002;" при первом открытии подключения, а затем периодически выполнять "PRAGMA optimize;" — например, один раз в день, или чаще, если база данных быстро развивается.

3. Все приложения должны выполнить "PRAGMA optimize;" после изменения схемы, особенно после одной или нескольких инструкций CREATE INDEX.

Команда PRAGMA optimize обычно является пустой операцией, но иногда она выполнит одну или несколько подкоманд АНАЛИЗА для отдельных таблиц базы данных, если это будет полезно для оптимизатора запросов. С версии SQLite 3.46.0 (2024-05-23) команда "PRAGMA optimize" автоматически ограничивает область действия подкоманд АНАЛИЗА, чтобы команда "PRAGMA optimize" завершалась быстро даже на огромных базах данных. Нет необходимости использовать PRAGMA analysis_limit. Это рекомендованный способ выполнения АНАЛИЗА.

Команда PRAGMA optimize обычно рассматривает только таблицы, которые были ранее запрошены тем же подключением к базе данных, или которые не содержат записей в таблице sqlite_stat1. Однако, если к аргументу добавлено значение 0x10000, PRAGMA optimize проверит все таблицы, чтобы определить, могут ли они извлечь выгоду из АНАЛИЗА, а не только те, которые недавно были запрошены. При первом открытии подключения к базе данных истории запросов нет, поэтому добавление бита 0x10000 рекомендуется при выполнении PRAGMA optimize для нового подключения к базе данных.

См. разделы Автоматическое выполнение АНАЛИЗА и Приблизительный АНАЛИЗ для больших баз данных ниже для получения дополнительной информации.

2.2. Зафиксированные результаты АНАЛИЗА

Выполнение АНАЛИЗА может привести к тому, что SQLite выберет другие планы запросов для последующих запросов. Это почти всегда положительный момент, так как планы запросов, выбранные после АНАЛИЗА, практически во всех случаях будут лучше, чем планы запросов, выбранные до АНАЛИЗА. В этом и заключается смысл АНАЛИЗА. Но нет гарантии, что выполнение АНАЛИЗА всегда будет выгодно. Можно привести патологические примеры, когда выполнение АНАЛИЗА может замедлить некоторые последующие запросы.

Некоторые разработчики предпочитают, чтобы после того, как дизайн приложения заморожен, SQLite всегда выбирал те же планы запросов, что и во время разработки и тестирования. Тогда, если миллионы копий приложения отправлены клиентам, разработчики уверены, что все эти миллионы копий используют одни и те же планы запросов независимо от данных, которые отдельные клиенты вставляют в свои базы данных. Это может помочь в воспроизведении жалоб на проблемы производительности, поступающих от пользователей.

Чтобы добиться этого, никогда не выполняйте полный АНАЛИЗ и не используйте команду "PRAGMA optimize" в приложении. Выполняйте АНАЛИЗ только во время разработки вручную, используя командную строку или аналогичным образом, на тестовой базе данных, по размеру и содержанию похожей на реальные базы данных. Затем зафиксируйте результат этого однократного АНАЛИЗА, используя скрипт, подобный следующему:

.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;" в конце, в зависимости от контекста, в котором выполняется скрипт.

См. гарантию стабильности оптимизатора запросов для получения дополнительной информации.

3. Детали

По умолчанию вся статистика хранится в одной таблице под названием "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 3.7.9. (2011-11-01) Команда ALTER TABLE не работает с таблицами статистики. Следует проявлять осторожность при изменении содержимого таблиц статистики, поскольку некорректное содержимое может привести к тому, что SQLite выберет неэффективные планы запросов. В целом, не следует изменять содержимое таблиц статистики каким-либо механизмом, кроме вызова команды ANALYZE. Для получения дополнительной информации см. "Ручное управление планами запросов с помощью таблиц SQLITE_STAT".

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

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

4. Автоматическое выполнение 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.

5. Приблизительный ANALYZE для больших баз данных

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

Рекомендуемый шаблон использования параметра analysis_limit выглядит следующим образом:

PRAGMA analysis_limit=1000;

Этот параметр сообщает команде ANALYZE начать полный поиск по индексу, как обычно. Но когда число посещенных строк достигнет 1000 (или любого другого предела, заданного параметром), команда 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.

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

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

Эта страница была в последний раз изменена 05.05.2024 15:23:53 UTC