EXPLAIN и планы запросов: смотри, что база данных делает на самом деле

Разбирай с помощью EXPLAIN QUERY PLAN тот план, который планировщик SQLite действительно выбирает для твоих запросов.

10 мин · 3 вопросов

SQL - язык декларативный: ты описываешь результат, а не способ его получить. Решает, как читать данные, планировщик запросов. Чтобы посмотреть на его работу, SQLite предлагает две команды: EXPLAIN QUERY PLAN выдаёт читаемый план высокого уровня, а один только EXPLAIN печатает байткод виртуальной машины VDBE. Установи консольный инструмент sqlite3 или открой sqliteonline.com в браузере и повтори каждый пример, который идёт дальше.

SQL

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  department_id INTEGER,
  salary INTEGER NOT NULL
);

INSERT INTO departments (id, name) VALUES
  (1, 'Engineering'),
  (2, 'Sales');

INSERT INTO employees
  (id, name, department_id, salary)
VALUES
  (1, 'Ada',    1, 65000),
  (2, 'Grace',  1, 72000),
  (3, 'Alan',   2, 58000);

Набор данных, который нужно создать в первую очередь.

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

SQL

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- Result: SCAN employees
-- (no index: the whole table is read)

Без индекса планировщик просматривает всю таблицу.

Создай индекс по столбцу, по которому идёт фильтрация, и запусти ровно тот же запрос ещё раз: план сменится с SCAN на SEARCH ... USING INDEX. Другие пометки тоже полезны: USE TEMP B-TREE выдаёт сортировку или группировку, материализованную в памяти (часто это признак того, что не хватает индекса для упорядочивания), а USING COVERING INDEX сообщает, что таблицу даже не открывали.

SQL

CREATE INDEX idx_emp_dept
  ON employees (department_id);

EXPLAIN QUERY PLAN
SELECT * FROM employees
WHERE department_id = 1;
-- SEARCH employees USING INDEX
-- idx_emp_dept (department_id=?)

Тот же SELECT, но теперь его ведёт индекс.

Планировщик опирается на статистику. Выполни ANALYZE: SQLite заполнит внутреннюю таблицу sqlite_stat1 распределением значений, и это поможет ему выбрать правильный индекс, когда кандидатов несколько. Без статистики он применяет эвристики по умолчанию и на больших таблицах может ошибиться.

EXPLAIN QUERY PLAN не выполняет запрос и не замеряет никакой длительности: он показывает *предполагаемый* план. Этот план может измениться по мере роста данных, после ANALYZE или если ты добавишь индекс. Всегда проверяй план на реалистичном объёме, а не на трёх тестовых строках.

Проверка знаний

Убедись, что запомнил ключевые моменты этого урока.

Что в SQLite возвращает EXPLAIN QUERY PLAN по сравнению с одним только EXPLAIN?
- Байткод VDBE, инструкция за инструкцией
- План высокого уровня (SCAN, SEARCH, временная сортировка)
- Реально замеренное время выполнения запроса
- Список существующих индексов таблицы
Что означает строка SCAN employees в плане SQLite?
- Таблица просматривается целиком, строка за строкой
- Используется покрывающий индекс
- Выполняется точечный поиск по индексу
- Таблица загружается в кэш памяти
Для чего нужна команда ANALYZE?
- Она перестраивает все индексы базы данных
- Она собирает статистику в sqlite_stat1, чтобы направлять планировщик
- Она выполняет запрос и замеряет его длительность
- Она блокирует таблицу на время анализа