ПЛАН ВЫПОЛНЕНИЯ ЗАПРОСА

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

Приведённый выше пример показывает, что SQLite выбирает полное сканирование таблицы, которое посетит все строки в таблице. Если бы запрос смог использовать индекс, запись 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. Например:

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. Однако второй подзапрос является "СВЯЗАННЫМ". Значение второго подзапроса меняется в зависимости от значений в текущей строке внешнего запроса. Следовательно, второй подзапрос должен выполняться один раз для каждой строки вывода во внешнем операторе 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-дерева. Альтернативный метод вычисления составного запроса — выполнение каждого подзапроса как сопроцедуры, организация их выходов в отсортированном порядке и объединение результатов. Если планировщик запросов выбирает этот последний подход, вывод 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

Эта страница была в последний раз изменена 08.01.2022 05:02:57 UTC