RussianLDP Рейтинг@Mail.ru
WebMoney: 
WMZ Z294115950220 
WMR R409981405661 
WME E134003968233 		 Visa 
4274 3200 2453 6495 

Small. Fast. Reliable.
Choose any three.
EXPLAIN QUERY PLAN
►Оглавление

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

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

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

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

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

Можно также установить CLI в автоматический режим EXPLAIN QUERY PLAN, используя ".eqp on":

sqlite> .eqp on

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

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 by union" (также известную как оптимизация 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. Вторым подзапросом, однако, является "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 результатов этих двух подзапросов. Альтернативный метод вычисления комплекса должен управлять каждым подзапросом как функцией, принять меры, чтобы их вывод появился в сортированном порядке и слить результаты вместе. Когда планировщик запроса выбирает этот последний подход, вывод 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