Исполняемые доки SQLite: EXPLAIN QUERY PLAN

Q: Чем SCAN отличается от SEARCH в выводе?

SCAN означает, что SQLite читает все строки таблицы или индекса подряд — для маленьких таблиц это нормально, для больших уже дорого. SEARCH — это прямой переход к нужным строкам через индекс или первичный ключ. На крупных таблицах для колонок из WHERE практически всегда хочется видеть именно SEARCH.

Q: Как понять, использует ли запрос индекс?

Запустите EXPLAIN QUERY PLAN и поищите в выводе строки USING INDEX или USING COVERING INDEX . Если там только SCAN без упоминания индекса — запрос идёт по всей таблице, и тут почти наверняка поможет добавить индекс.

Q: В чём разница между EXPLAIN и EXPLAIN QUERY PLAN?

EXPLAIN выдаёт низкоуровневый байт-код виртуальной машины SQLite — это полезно, если вы копаетесь во внутренностях движка, но для тюнинга запросов почти бесполезно. EXPLAIN QUERY PLAN показывает понятную человеку сводку: какие таблицы как читаются и какие индексы задействованы. Для оптимизации производительности нужен именно он.

EXPLAIN QUERY PLAN показывает, как именно выполнится запрос

Прежде чем оптимизировать тормозящий запрос, нужно понять, что вообще делает SQLite. Команда EXPLAIN QUERY PLAN выводит краткую сводку стратегии, которую выбрал планировщик: к каким таблицам он обращается, в каком порядке и какие индексы при этом задействует (если задействует вообще). Сам запрос не выполняется — вы получаете только план.

Просто допишите ключевые слова перед любым запросом:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    country TEXT
);

INSERT INTO users (email, country) VALUES
    ('a@x.com', 'US'),
    ('b@x.com', 'UK'),
    ('c@x.com', 'US');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE email = 'a@x.com';

На выходе получаем примерно такое:

QUERY PLAN
`--SEARCH users USING INDEX sqlite_autoindex_users_1 (email=?)

Эта одна строчка говорит о многом: SQLite выполняет SEARCH (а не scan) по таблице users, используя автоматически созданный уникальный индекс для email, причём именно email служит ключом поиска. Ровно то, на что мы и рассчитывали.

SCAN или SEARCH: на что смотреть в первую очередь

Каждая строка плана запроса начинается со слова SCAN или SEARCH. Это различие — самый важный сигнал во всём выводе.

SCAN <table> — SQLite читает все строки таблицы (или все записи индекса) подряд. Стоимость растёт пропорционально размеру таблицы.
SEARCH <table> USING ... — SQLite сразу прыгает к нужным строкам через индекс или первичный ключ. Стоимость зависит от размера результата, а не от размера таблицы.

Сравним два случая бок о бок. У одного столбца индекс есть, у другого — нет:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    status TEXT
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'paid'), (2, 'pending'), (1, 'paid'), (3, 'shipped');

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'paid';

Первый план показывает SEARCH orders USING INDEX idx_orders_customer. Второй — SCAN orders: индекса по status нет, поэтому SQLite читает каждую строку. На маленькой таблице разница незаметна, а вот на таблице в миллион строк — это разница между миллисекундами и секундами.

SCAN сам по себе не приговор. Для крошечных справочников или запросов, которые и правда возвращают почти всё содержимое таблицы, скан — оптимальный вариант. Но если у вас большая таблица с селективным фильтром, SCAN — это сигнал, что пора добавить индекс.

Как проверить, что индекс используется в SQLite

Ключевая фраза, которую стоит искать, — USING INDEX <name> (или USING COVERING INDEX <name> — об этом чуть ниже). Если вы создали индекс и надеетесь, что планировщик его подхватит, проверяется это так:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    created_at TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id = 42;

Должно появиться SEARCH events USING INDEX idx_events_user (user_id=?). Если же в плане видно SCAN events, значит что-то мешает планировщику задействовать индекс. Самые частые причины: столбец обёрнут в функцию (WHERE lower(user_id) = ...), сравниваются значения разных типов или используется LIKE '%foo%' с подстановкой в начале.

Давайте быстро это проверим:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    kind TEXT
);

CREATE INDEX idx_events_user ON events(user_id);

EXPLAIN QUERY PLAN
SELECT * FROM events WHERE user_id + 0 = 42;

Это + 0 ломает индекс — план запроса откатывается к SCAN events. Любое выражение над индексированным столбцом даёт тот же эффект.

Покрывающие индексы выглядят иначе

Когда индекс содержит все столбцы, нужные запросу, SQLite может ответить, опираясь только на индекс — к самой таблице обращаться не приходится. В плане это отображается как USING COVERING INDEX:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    sku TEXT,
    name TEXT,
    price REAL
);

CREATE INDEX idx_products_sku_price ON products(sku, price);

EXPLAIN QUERY PLAN
SELECT price FROM products WHERE sku = 'ABC-123';

План получится такой: SEARCH products USING COVERING INDEX idx_products_sku_price (sku=?). Запрос просит price, а индекс уже хранит и sku, и price — значит, до самой таблицы SQLite даже не доходит. Покрывающий индекс (covering index) — самый быстрый план, который можно выжать из точечного поиска. Полезно держать это в голове, когда решаете, какие столбцы засунуть в один индекс.

Как читать план JOIN-запроса в SQLite

Самое интересное в планах запросов начинается на джойнах. Каждая строка плана — это одна таблица из соединения, а порядок строк показывает, в каком порядке SQLite к ним обращается. Первая таблица — внешняя (outer), к остальным движок ходит по одному разу на каждую строку из внешней.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total REAL
);

CREATE INDEX idx_orders_customer ON orders(customer_id);

EXPLAIN QUERY PLAN
SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE c.id = 7;

Типичный план:

QUERY PLAN
|--SEARCH c USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH o USING INDEX idx_orders_customer (customer_id=?)

Читай сверху вниз: SQLite находит нужного покупателя по первичному ключу, а затем подтягивает его заказы через индекс на customer_id. Обе строки — это SEARCH, никакого полного перебора, и это именно то, чего мы добивались.

Если бы во второй строке оказалось SCAN o, то на каждого покупателя SQLite пробегал бы всю таблицу orders целиком. На большой таблице это уже катастрофа. Лечится почти всегда одинаково — индексом по столбцу соединения.

Составные запросы и подзапросы

План запроса sqlite для UNION, EXCEPT и подзапросов получается вложенным. Каждая ветка отображается с отступом под своим родителем:

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT x FROM a
UNION ALL
SELECT x FROM b;

Под заголовком COMPOUND QUERY вы увидите две дочерние строки — по одной на каждую ветвь. Подзапросы и CTE отображаются по тому же принципу: у каждого свой узел плана с отступом, и читаются они через ту же призму SCAN vs SEARCH.

CREATE TABLE a (x INTEGER);
CREATE TABLE b (x INTEGER);

EXPLAIN QUERY PLAN
SELECT * FROM a
WHERE x IN (SELECT x FROM b WHERE x > 10);

Подзапрос превращается в отдельный узел плана (LIST SUBQUERY или что-то похожее) со своей стратегией доступа. Проверяйте такие узлы на каждом уровне вложенности по тем же правилам.

EXPLAIN vs EXPLAIN QUERY PLAN

Это две разные команды, и их постоянно путают.

CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT);

EXPLAIN
SELECT * FROM t WHERE id = 1;

EXPLAIN (без QUERY PLAN) выводит байт-код, который будет выполнять виртуальная машина SQLite — десятки низкоуровневых опкодов вроде OpenRead, SeekRowid, Column, ResultRow. Полезно, если вы отлаживаете сам движок. Для оптимизации запросов — почти бесполезно.

EXPLAIN QUERY PLAN — это та самая человекочитаемая сводка, которая вам и нужна. Если сомневаетесь, всегда берите EXPLAIN QUERY PLAN.

Как разбираться с медленными запросами

Когда запрос тормозит, схема действий примерно такая:

Прогоните его через EXPLAIN QUERY PLAN.
По каждой строке с таблицей задайте вопрос: это SCAN или SEARCH? На большой таблице SCAN — главный подозреваемый.
Если SCAN фильтрует по какой-то колонке, подумайте об индексе на эту колонку.
В джойнах убедитесь, что таблицы внутреннего цикла используют SEARCH USING INDEX по колонке соединения.
После добавления индекса снова запустите EXPLAIN QUERY PLAN. План должен измениться. Если нет — планировщик решил, что ваш индекс не стоит использовать. Обычно это значит, что таблица слишком маленькая или фильтр недостаточно избирательный.

Разберём шаг 5 на примере:

CREATE TABLE logs (
    id INTEGER PRIMARY KEY,
    level TEXT,
    message TEXT
);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SCAN logs

CREATE INDEX idx_logs_level ON logs(level);

EXPLAIN QUERY PLAN
SELECT * FROM logs WHERE level = 'ERROR';
-- SEARCH logs USING INDEX idx_logs_level (level=?)

План сменился с SCAN на SEARCH. Это и есть сигнал, что индекс действительно работает. (На свежей, почти пустой таблице планировщик может всё ещё выбирать полное сканирование — данных просто слишком мало, чтобы возиться с индексом. Наполните таблицу или выполните ANALYZE, и решение, как правило, поменяется.)

Чего план запроса вам не покажет

EXPLAIN QUERY PLAN описывает стратегию, а не стоимость. Он не скажет, что запрос выполнялся 800 мс и вернул 50 000 строк. Для этого нужен замер времени (.timer on в CLI) и подсчёт строк. План и тайминг дополняют друг друга: план объясняет, почему запрос медленный, а таймер — насколько он медленный на самом деле.

Ещё пара ограничений, о которых стоит помнить:

План меняется по мере роста данных. Запрос, который спокойно сканировал таблицу из 100 строк, потребует индекса, когда строк станет миллион. Проверяйте планы на данных боевого объёма, а не на тестовых фикстурах.
Планировщик опирается на статистику, собранную командой ANALYZE. Без неё он использует значения по умолчанию, и они далеко не всегда удачны. Устаревшая или отсутствующая статистика — частая причина странных планов.

Дальше: ANALYZE и VACUUM

Планировщик запросов принимает решения на основе статистики по таблицам и индексам. Если эта статистика устарела или её нет вовсе, даже идеально проиндексированная схема может выдать плохой план. ANALYZE помогает держать её в актуальном состоянии, а VACUUM — это парная команда для освобождения места и дефрагментации файла базы данных. Об этом — в следующей главе.

Часто задаваемые вопросы

Что вообще делает EXPLAIN QUERY PLAN в SQLite?

Эта команда показывает, как SQLite собирается выполнить запрос — но сам запрос при этом не запускается. В выводе видно, какие таблицы будут читаться, какие индексы пойдут в дело и в каком порядке выполняются JOIN-ы. Достаточно дописать EXPLAIN QUERY PLAN перед любым SELECT, INSERT, UPDATE или DELETE.

Чем SCAN отличается от SEARCH в выводе?

SCAN означает, что SQLite читает все строки таблицы или индекса подряд — для маленьких таблиц это нормально, для больших уже дорого. SEARCH — это прямой переход к нужным строкам через индекс или первичный ключ. На крупных таблицах для колонок из WHERE практически всегда хочется видеть именно SEARCH.

Как понять, использует ли запрос индекс?

Запустите EXPLAIN QUERY PLAN и поищите в выводе строки USING INDEX <имя> или USING COVERING INDEX <имя>. Если там только SCAN <таблица> без упоминания индекса — запрос идёт по всей таблице, и тут почти наверняка поможет добавить индекс.

В чём разница между EXPLAIN и EXPLAIN QUERY PLAN?

EXPLAIN выдаёт низкоуровневый байт-код виртуальной машины SQLite — это полезно, если вы копаетесь во внутренностях движка, но для тюнинга запросов почти бесполезно. EXPLAIN QUERY PLAN показывает понятную человеку сводку: какие таблицы как читаются и какие индексы задействованы. Для оптимизации производительности нужен именно он.

Связанные концепции

EXPLAIN QUERY PLAN в SQLite: как читать план запроса