SQL запрос EXPLAIN и EXPLAIN QUERY PLAN в базах данных SQLite: оптимизация SQL запросов.

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223

Рубрика SQLite на сайте: http://zametkinapolyah.ru/zametki-o-mysql/sqlite/
Паблик блога в ВК: https://vk.com/zametkinapolyah
Тематическая группа в ВК: https://vk.com/zametki_bd

— SQL запрос EXPLAIN в базах данных SQLite: оптимизация SQL запросов.

— Эти ссылки помогут вам разобраться с оптимизацией запросов:
http://sqlite.org/lang_explain.html
http://sqlite.org/optoverview.html
http://sqlite.org/eqp.html
http://sqlite.org/queryplanner.html
http://sqlite.org/queryplanner-ng.html
http://sqlite.org/opcode.html

/*
Библиотека SQLite имеет две SQL команды, которые позволяют нам
сделать две важные вещи: посмотреть как выполняется запрос
и оценить насколько он эффективен.

Эти SQL команды стоит использовать только при отладке, но
никак не в рабочих проектах.

Команды, на самом деле очень просты: EXPLAIN и
EXPLAIN QUERY PLAN.

Информация, получаемая при помощи запросов EXPLAIN и
EXPLAIN QUERY PLAN может изменяться в зависимости от
версии SQLite.

Запрос EXPLAIN позволяет увидеть последовательность машинных
команд, которые бы совершила SQLite для выполнения того
или иного запроса.

У SQLite есть целый раздел документации, в котором
описывается особенность использования EXPLAIN QUERY PLAN,
там даже есть наглядные примеры и детальные пояснения.

*/

EXPLAIN SELECT * FROM invoices WHERE BillingCountry = “USA”;

/*
addr – номер команды или инструкции, всегда начинается
с нуля

P1, P2 P3 – 32-ух битные целые числа, зачастую эти операнды
ссылаются на регистры для получения инструкций по работе
с Б-деревом.
P3 – обычно используется для хранения промежуточных
результатов.
P1 – обычно хранит в себе номер указателя текущего узла
Б-дерева или курсор.
P2 – хранит информацию, которая помогает перемещаться между
узлами дерева, обычно это узел, в который нужно перейти.
P4 – может быть как 32-ух битным целым, так и 64-ех битным
целым или 64-ех битным с плавающей точкой, BLOB или
строковым значением обычно этот операнд является указателем
на функцию сравнения данных.
P5 – значение данного операнда является флагом, который
может тем или иным образом влиять не результаты выполнения
операций (всё зависит от opcode).
opcode – это код машинной операции, которая выполняется
на том или ином шагу. Не все операции задействуют одинаковое
число операндов, а назначение некоторых операндов зависит от
выполняемой операции.

*/

— EXPLAIN QUERY PLAN

— для тблицы без индекса
SELECT * FROM invoices WHERE BillingCountry = “USA”;
EXPLAIN QUERY PLAN SELECT * FROM invoices
WHERE BillingCountry = “USA”;

— для таблицы с индексом
SELECT * FROM invoices
WHERE BillingCountry = “USA”;
EXPLAIN QUERY PLAN SELECT * FROM invoices
WHERE BillingCountry = “USA”;

— поиск по индексному столбцу
SELECT BillingCountry FROM invoices
WHERE BillingCountry = “USA”;

EXPLAIN QUERY PLAN SELECT BillingCountry FROM invoices
WHERE BillingCountry = “USA”;

— поиск с группировкой
DROP INDEX i1;
SELECT max(invoicedate), BillingCountry FROM invoices
GROUP BY BillingCountry;
EXPLAIN QUERY PLAN SELECT max(invoicedate), BillingCountry
FROM invoices
GROUP BY BillingCountry;

/*

explain query plan
scan table — самый тупой поиск в базе даннных, полнотекстовый перебор значений;

search table using index — индекс в таблице существует,
но данные, которые мы ищем в него не включены;

search table using covering index — самый эффективный случай, искомые данные уже лежат в индексе;

use temp B-TREE — поиск с конструкциями типа group by,
order by, и по столбцу, в котором идет поиск, индекс не
создан, в этом случае SQLite делает примерно следующее:
выбирает все строки, которые удовлетворяют заданному
критерию, а затем строит в памяти Б-дерево, которое
использует для сортировки этих данных, всё это происходит
очень медленно. Поэтому столбцы, значения которых в часто
группируете или сортируете лучше проиндексировать.

*/