EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite

Материал основан на разборе sqlite.org. Ниже — главное и выводы, которые стоит учитывать в SEO и маркетинге.

Команда EXPLAIN QUERY PLAN и план выполнения запроса

Команда EXPLAIN QUERY PLAN используется для получения высокоуровневого описания стратегии или плана выполнения конкретного SQL-запроса в SQLite. Основная задача — показать, как запрос использует индексы базы данных для оптимизации выполнения.

Внимание! Данные, возвращаемые командой EXPLAIN QUERY PLAN, предназначены только для интерактивной отладки. Формат вывода может меняться между версиями SQLite.

Так, формат вывода существенно изменился в версии 3.24.0 (июнь 2018 года) и получил дополнительные изменения в версии 3.36.0 (июнь 2021 года). Поэтому приложения не должны полагаться на неизменность формата.

План запроса представлен в виде дерева, где каждый узел имеет четыре поля: идентификатор узла, идентификатор родителя, дополнительное поле (пока не используемое) и описание узла. Командная оболочка SQLite обычно отображает этот план в удобной ASCII-графике.

Режим командной оболочки для автоматического отображения EXPLAIN QUERY PLAN включается командой .eqp on и выключается с помощью .eqp off.

На практике EXPLAIN QUERY PLAN полезен не только при ручной оптимизации медленного SELECT, но и перед выпуском новой версии приложения. Если вы добавили индекс, переписали WHERE, перенесли фильтрацию в подзапрос или изменили порядок полей в composite index, этот инструмент быстро показывает, сработало ли изменение.

В реальном проекте им удобно проверять три вещи: ушёл ли полный SCAN, появился ли ожидаемый SEARCH, и не возникла ли лишняя сортировка через TEMP B-TREE.

Полезный рабочий ритуал выглядит так: сначала зафиксируйте исходный запрос и его план, затем внесите одно изменение, после чего снова снимите план. Если поменять сразу и индекс, и текст запроса, и порядок соединений, вы не поймёте, что именно дало эффект. Для командной работы это особенно важно: в code review проще обсуждать конкретный переход из SCAN t1 в SEARCH t1 USING INDEX ..., чем спорить на уровне общих предположений.

Ещё один важный момент: EXPLAIN QUERY PLAN не заменяет профилирование по времени, но отлично подсказывает, куда смотреть дальше. Если план выглядит хорошо, а запрос всё равно медленный, значит проблема может быть в размере выборки, количестве возвращаемых столбцов, блокировках на запись или в том, что условие недостаточно селективно.

То есть план нужен как быстрый диагностический слой между «запрос работает медленно» и более глубокой проверкой схемы, индексов и фактических данных.

Сканирование таблиц и индексов: SCAN и SEARCH в плане запроса

При выполнении SELECT-запроса SQLite может извлекать данные из таблиц различными способами: полный перебор всех записей, сканирование по индексам или их комбинация.

Для каждой таблицы, вовлеченной в запрос, EXPLAIN QUERY PLAN выводит запись с полем "detail", начинающимся на "SCAN" (сканирование) или "SEARCH" (поиск). SCAN означает полный перебор, включая перебор по индексовому порядку, а SEARCH — выборочную выборку строк по индексу.

В записи содержится информация о таблице, использовании индексов (включая автоматические индексы), применении покрывающих индексов и о том, какие части условия WHERE используются для индексации.

Пример:

EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
-- вывод:
`--SCAN t1

Если возможно использовать индекс, запись будет содержать имя индекса и условия поиска, например:

CREATE INDEX i1 ON t1(a);
EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
-- вывод:
`--SEARCH t1 USING INDEX i1 (a=?)

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

CREATE INDEX i2 ON t1(a, b);
EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
-- вывод:
`--SEARCH t1 USING COVERING INDEX i2 (a=?)

При соединениях SQLite использует вложенные циклы. EXPLAIN QUERY PLAN выводит отдельную запись SCAN/SEARCH для каждого уровня вложенного цикла.

Пример:

EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
-- вывод:
|--SEARCH t1 USING INDEX i2 (a=? AND b>?)
`--SCAN t2

Порядок в выводе соответствует вложенности: первая запись — внешний цикл, последняя — внутренний.

Если в условии встречается OR, может использоваться стратегия "OR by union" с двумя записями для каждого условия индекса:

CREATE INDEX i3 ON t1(b);
EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
-- вывод:
`--MULTI-INDEX OR
 |--SEARCH t1 USING COVERING INDEX i2 (a=?)
 `--SEARCH t1 USING INDEX i3 (b=?)

1.2. Временные B-деревья для сортировки

Если SELECT содержит ORDER BY, GROUP BY или DISTINCT, SQLite может использовать временную B-структуру для сортировки.

Использование индекса — почти всегда эффективнее, чем сортировка.

Если временное B-дерево требуется, EXPLAIN QUERY PLAN добавляет запись с "USE TEMP B-TREE FOR xxx" (xxx — ORDER BY, GROUP BY или DISTINCT).

Пример:

EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
-- вывод:
|--SCAN t2
`--USE TEMP B-TREE FOR ORDER BY

Чтобы избежать этого, можно создать индекс:

CREATE INDEX i4 ON t2(c);
EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
-- вывод:
`--SCAN t2 USING INDEX i4

В реальном проекте эта строка особенно полезна на списках с пагинацией, в отчётах и в административных экранах, где пользователь постоянно сортирует данные по дате, статусу или имени. Если вы видите USE TEMP B-TREE FOR ORDER BY, это почти всегда сигнал проверить, совпадает ли индекс с фактическим порядком сортировки. Для ORDER BY created_at DESC индекс только по status не поможет: SQLite всё равно будет отдельно упорядочивать результат.

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

Поэтому после появления TEMP B-TREE обычно делают одно из трёх действий: создают индекс под конкретный ORDER BY, переписывают запрос так, чтобы сортировка шла по уже индексированному выражению, или уменьшают размер промежуточной выборки до сортировки.

Практически удобно смотреть на это так: TEMP B-TREE не всегда баг, но это явный повод задать вопрос «можно ли обойтись без сортировки». Если ответ да, индекс почти всегда выигрывает по предсказуемости и снижает нагрузку на CPU при повторяющихся запросах.

1.3. Подзапросы

EXPLAIN QUERY PLAN отображает подзапросы как дочерние для внешнего SELECT.

Подзапросы могут быть константными (рассчитываются один раз и переиспользуются) и коррелированными (зависят от текущей строки внешнего запроса и выполняются для каждой строки).

Без оптимизации flattening подзапросы в FROM могут либо сохраняться во временной таблице, либо работать как корутина (при этом внешний запрос ожидает строку от подзапроса).

Пример с coroutine:

EXPLAIN QUERY PLAN SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq;
-- вывод:
|--CO-ROUTINE qqq
| `--SCAN t1 USING COVERING INDEX i2

Если применяется flattening, подзапрос в FROM сливается с внешним запросом, что отражается в EXPLAIN QUERY PLAN.

Для прикладной разработки здесь важнее всего различать дешёвый и дорогой подзапрос. Если подзапрос независим от внешней строки, SQLite может выполнить его один раз, сохранить результат и переиспользовать. Если он коррелированный, стоимость нередко растёт вместе с числом строк внешнего запроса. Поэтому при чтении плана полезно смотреть не только на наличие CO-ROUTINE, но и на то, сколько раз логически должен исполняться внутренний SELECT.

Типичный кейс: разработчик пишет запрос «для каждой категории посчитать последнее событие» через коррелированный подзапрос, потому что так текст выглядит короче. EXPLAIN QUERY PLAN помогает быстро заметить, что внутренний поиск привязан к каждой строке внешней таблицы и может выполняться сотни или тысячи раз.

После этого уже можно решать, что выгоднее: переписать запрос через JOIN и GROUP BY, вынести предварительную агрегацию в CTE или добавить индекс, который снизит цену каждого повторного обращения.

Практический критерий такой: если после добавления индекса план всё ещё показывает тяжёлую схему с повторными обращениями к подзапросу, проблема, скорее всего, в форме запроса, а не только в индексе. В этом случае EXPLAIN QUERY PLAN полезен именно как индикатор архитектурного решения: иногда дешевле переписать SQL, чем пытаться компенсировать неудачную конструкцию новыми индексами.

1.4. Составные запросы

Каждый компонент сложного запроса (UNION, UNION ALL, EXCEPT, INTERSECT) рассчитывается отдельно и получает собственную строку в выводе EXPLAIN QUERY PLAN.

Пример:

EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
-- вывод:
`--COMPOUND QUERY
 |--LEFT-MOST SUBQUERY
 | `--SCAN t1 USING COVERING INDEX i1
 `--UNION USING TEMP B-TREE
 `--SCAN t2 USING COVERING INDEX i4

Фраза "USING TEMP B-TREE" указывает на использование временной B-структуры для реализации UNION.

Альтернативный способ — coroutine с сортировкой и слиянием результатов из подзапросов.

С практической точки зрения здесь важно различать UNION и UNION ALL. Обычный UNION обязан удалять дубликаты, поэтому ему часто нужна дополнительная структура для сравнения и сортировки результатов. UNION ALL просто склеивает наборы строк и обычно дешевле, если дедупликация вам не нужна по бизнес-логике. EXPLAIN QUERY PLAN помогает подтвердить это на уровне движка: по плану видно, появилась ли промежуточная B-структура ради объединения.

Типовой сценарий из продакшена: один отчёт собирает строки из активной и архивной таблицы. Если разработчик по привычке пишет UNION, хотя строки и так взаимно исключаются, запрос получает лишние накладные расходы. После замены на UNION ALL план становится проще, а запрос предсказуемее под нагрузкой. Это хороший пример того, как инструмент помогает не «тюнить SQLite», а убирать лишнюю работу из самого SQL.

Проверяйте составные запросы по двум вопросам: нужен ли контроль дубликатов, и можно ли добиться того же результата без промежуточной сортировки. Если дубликаты бизнесу не важны, UNION ALL обычно предпочтительнее. Если важны, тогда TEMP B-TREE в плане будет ожидаемой ценой за корректность результата.

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

Что такое EXPLAIN QUERY PLAN? Команда для анализа плана выполнения SQL-запроса SQLite, показывающая высокоуровневое описание действий с индексами.

Зачем нужны временные B-деревья? Их используют для сортировки результатов запросов с ORDER BY, GROUP BY и DISTINCT, когда нельзя использовать индекс.

Что такое коррелированные подзапросы? Подзапросы, значение которых зависит от текущей строки внешнего запроса и которые выполняются для каждой такой строки.

Как использовать JOIN с EXPLAIN QUERY PLAN? SQLite применяет вложенные циклы (nested loop joins), и EXPLAIN QUERY PLAN показывает уровень вложенности.

Можно ли полагаться на формат вывода EXPLAIN QUERY PLAN? Формат вывода меняется между версиями SQLite, поэтому его нельзя считать обязательным для приложений.