EXPLAIN QUERY PLAN

1. Команда EXPLAIN QUERY PLAN

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

Внимание: Как уже предупреждалось выше, формат вывода EXPLAIN QUERY PLAN существенно изменился в выпуске версии 3.24.0 (2018-06-04). Дополнительные небольшие изменения произошли в версии 3.36.0 (2021-06-18). В последующих выпусках возможны дальнейшие изменения.

Команда SQL EXPLAIN QUERY PLAN используется для получения высокоуровневого описания стратегии или плана, который SQLite использует для выполнения определённого SQL-запроса. В частности, EXPLAIN QUERY PLAN сообщает, как запрос использует индексы базы данных. Этот документ поможет понять и интерпретировать вывод EXPLAIN QUERY PLAN. Дополнительные сведения доступны в отдельных документах:

• Введение в принципы работы SQLite.
• Примечания об оптимизаторе запросов.
• Как работает индексирование.
• Планировщик запросов нового поколения.

План запроса представлен в виде дерева. В исходном виде, возвращаемом функцией sqlite3_step(), каждый узел дерева состоит из четырёх полей: целочисленного идентификатора узла, целочисленного идентификатора родительского узла, вспомогательного целочисленного поля, которое в настоящее время не используется, и описания узла. Таким образом, всё дерево представляет собой таблицу из четырёх столбцов и нуля или более строк. Командная оболочка обычно перехватывает эту таблицу и отображает её в виде ASCII-графики для более удобного просмотра. Чтобы отключить автоматическое отображение графики в оболочке и выводить результат EXPLAIN QUERY PLAN в виде таблицы, выполните команду ".explain off", чтобы выключить «режим форматирования EXPLAIN». Чтобы снова включить автоматическое отображение графики, выполните команду ".explain auto". Текущую настройку «режима форматирования EXPLAIN» можно посмотреть с помощью команды ".show".

Также можно включить в CLI автоматический режим EXPLAIN QUERY PLAN с помощью команды ".eqp on":

sqlite> .eqp on

В автоматическом режиме EXPLAIN QUERY PLAN оболочка автоматически выполняет отдельный запрос EXPLAIN QUERY PLAN для каждого введённого оператора и отображает результат до фактического выполнения запроса. Чтобы отключить автоматический режим EXPLAIN QUERY PLAN, выполните команду ".eqp off".

EXPLAIN QUERY PLAN наиболее полезен для оператора SELECT, но может применяться и с другими операторами, которые читают данные из таблиц базы данных (например, UPDATE, DELETE, INSERT INTO ... SELECT).

1.1. Сканирование таблиц и индексов

При обработке оператора SELECT (или другого оператора) SQLite может извлекать данные из таблиц базы данных разными способами. Он может просматривать все записи таблицы (полное сканирование таблицы), просматривать непрерывную часть записей таблицы на основе индекса rowid, просматривать непрерывную часть элементов индекса базы данных или сочетать несколько перечисленных стратегий в одном сканировании. Различные способы извлечения SQLite данных из таблицы или индекса подробно описаны здесь.

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

• Имя таблицы, представления или подзапроса, из которого читаются данные.
• Используется ли индекс или автоматический индекс.
• Применяется ли оптимизация покрывающего индекса.
• Какие условия предложения WHERE используются для индексирования.

Например, следующая команда EXPLAIN QUERY PLAN выполняет анализ оператора SELECT, реализованного полным сканированием таблицы t1:

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SCAN t1

Если бы запрос мог использовать индекс, запись SCAN/SEARCH содержала бы имя индекса и, для записи SEARCH, указание на то, как определяется посещаемая часть строк. Например:

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
QUERY PLAN
`--SEARCH t1 USING INDEX i1 (a=?)

В предыдущем примере SQLite использует индекс "i1" для оптимизации условия WHERE вида (a=?) — в данном случае "a=1". В предыдущем примере нельзя было использовать покрывающий индекс, однако в следующем примере это возможно, и данный факт отражён в выводе:

sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
QUERY PLAN
`--SEARCH t1 USING COVERING INDEX i2 (a=?)

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

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

Порядок записей указывает порядок вложенности. В данном случае сканирование таблицы t1 с использованием индекса i2 — внешний цикл (поскольку оно указано первым), а полное сканирование таблицы t2 — внутренний цикл (поскольку оно указано последним). В следующем примере таблицы t1 и t2 расположены в предложении FROM оператора SELECT в обратном порядке. Стратегия выполнения запроса остаётся прежней. Вывод EXPLAIN QUERY PLAN показывает, как запрос выполняется на самом деле, а не как он задан в операторе SQL.

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

Если предложение WHERE запроса содержит выражение OR, SQLite может использовать стратегию «OR посредством объединения» (также известную как оптимизация OR). В этом случае для поиска будет выведена одна запись верхнего уровня с двумя вложенными записями — по одной для каждого индекса:

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
QUERY PLAN
`--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 добавляется запись, в поле "detail" которой задаётся строковое значение вида "USE TEMP B-TREE FOR xxx", где xxx — одно из значений "ORDER BY", "GROUP BY" или "DISTINCT". Например:

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
QUERY PLAN
|--SCAN t2
`--USE TEMP B-TREE FOR ORDER BY

В этом случае использования временного B-дерева можно избежать, создав индекс по t2(c), как показано ниже:

sqlite> CREATE INDEX i4 ON t2(c);
sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
QUERY PLAN
`--SCAN t2 USING INDEX i4

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

Во всех приведённых выше примерах использовался только один оператор SELECT. Если запрос содержит вложенные запросы SELECT, они отображаются как дочерние узлы внешнего SELECT. Например:

sqlite> EXPLAIN QUERY PLAN SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
|--SCAN TABLE t2 USING COVERING INDEX i4
|--SCALAR SUBQUERY
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH t1 USING INDEX i3 (b=?)

Приведённый выше пример содержит два подзапроса "SCALAR". Подзапросы являются SCALAR в том смысле, что возвращают одно значение — таблицу с одной строкой и одним столбцом. Если фактический запрос возвращает больше данных, используется только первый столбец первой строки.

Первый подзапрос выше является константным относительно внешнего запроса. Значение первого подзапроса можно вычислить один раз, а затем повторно использовать для каждой строки внешнего SELECT. Второй подзапрос, однако, является "CORRELATED". Значение второго подзапроса меняется в зависимости от значений в текущей строке внешнего запроса. Поэтому второй подзапрос необходимо выполнять для каждой выходной строки внешнего SELECT.

Если не применяется оптимизация свёртки, то при появлении подзапроса в предложении FROM оператора SELECT SQLite может либо выполнить подзапрос и сохранить результаты во временной таблице, либо выполнить его в виде сопрограммы. Следующий запрос — пример второго варианта. Подзапрос выполняется сопрограммой. Внешний запрос приостанавливается всякий раз, когда ему требуется следующая входная строка из подзапроса. Управление передаётся сопрограмме, которая формирует нужную выходную строку, а затем возвращается основной программе, продолжающей обработку.

sqlite> EXPLAIN QUERY PLAN SELECT count(*)
      > FROM (SELECT max(b) AS x FROM t1 GROUP BY a) AS qqq
      > GROUP BY x;
QUERY PLAN
|--CO-ROUTINE qqq
|  `--SCAN t1 USING COVERING INDEX i2
|--SCAN qqqq
`--USE TEMP B-TREE FOR GROUP BY

Если для подзапроса в предложении FROM оператора SELECT применяется оптимизация свёртки, подзапрос фактически объединяется с внешним запросом. Это отражается в выводе EXPLAIN QUERY PLAN, как показано в следующем примере:

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1) AS t3, t1;
QUERY PLAN
|--SEARCH t2 USING INDEX i4 (c=?)
`--SCAN t1

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

sqlite> SELECT * FROM
      >   (SELECT * FROM t1 WHERE a=1 ORDER BY b LIMIT 2) AS x,
      >   (SELECT * FROM t2 WHERE c=1 ORDER BY d LIMIT 2) AS y;
QUERY PLAN
|--MATERIALIZE x
|  `--SEARCH t1 USING COVERING INDEX i2 (a=?)
|--MATERIALIZE y
|  |--SEARCH t2 USING INDEX i4 (c=?)
|  `--USE TEMP B-TREE FOR ORDER BY
|--SCAN x
`--SCAN y

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

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

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
QUERY PLAN
`--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 результатов двух подзапросов SELECT. Альтернативный способ вычисления составного запроса — выполнить каждый подзапрос в виде сопрограммы, упорядочить их выходные данные и объединить результаты. Когда планировщик запросов выбирает этот подход, вывод EXPLAIN QUERY PLAN выглядит так:

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
QUERY PLAN
`--MERGE (EXCEPT)
   |--LEFT
   |  `--SCAN t1 USING COVERING INDEX i1
   `--RIGHT
      |--SCAN t2
      `--USE TEMP B-TREE FOR ORDER BY

Последнее обновление этой страницы: 2025-05-31 13:08:22Z