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

Помощь проекту:
WMR: R288272666982
WMZ: Z293550531456
Яндекс.Деньги: 410011531129223
Рубрика SQLite на сайте: zametkinapolyah...
Паблик блога в ВК: zametki...
Тематическая группа в ВК: zametki_bd
-- SQL запрос EXPLAIN в базах данных SQLite: оптимизация SQL запросов.
-- Эти ссылки помогут вам разобраться с оптимизацией запросов:
sqlite.org/lang...
sqlite.org/opto...
sqlite.org/eqp....
sqlite.org/quer...
sqlite.org/quer...
sqlite.org/opco...
/*
Библиотека 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 делает примерно следующее:
выбирает все строки, которые удовлетворяют заданному
критерию, а затем строит в памяти Б-дерево, которое
использует для сортировки этих данных, всё это происходит
очень медленно. Поэтому столбцы, значения которых в часто
группируете или сортируете лучше проиндексировать.
*/