План запроса, SARGable-условия и оптимизация

EXPLAIN QUERY PLAN показывает, как БД выполнит запрос, а SARGable-условия позволяют ей использовать индекс.

План запроса — это пошаговый «маршрут», который выбрал оптимизатор: какие таблицы читать, в каком порядке, со сканом или через индекс. SARGable-условие — такое, под которое БД может задействовать индекс (колонка не спрятана за функцией).

Идея EXPLAIN QUERY PLAN

Прежде чем оптимизировать запрос, надо увидеть как он выполняется. Не угадывать, а смотреть план. В SQLite это EXPLAIN QUERY PLAN, в PostgreSQL/MySQL — EXPLAIN. Главное, что читают в плане: SCAN (полный проход — подозрительно на больших таблицах) против SEARCH ... USING INDEX (точечный доступ — хорошо).

SARGable: не прячьте колонку за функцией

Самая частая причина «индекс есть, а не используется» — функция или арифметика на индексированной колонке. Сравним два плана для одного индекса.

CREATE TABLE users(id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users(email) VALUES ('A@x'),('b@x');
CREATE INDEX idx_users_email ON users(email);

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE LOWER(email) = 'a@x';

Вывод:

QUERY PLAN
`--SCAN users

Несмотря на индекс — SCAN. Индекс хранит исходные значения (A@x), а БД ищет результат LOWER(email), которого в индексе нет, поэтому приходится посчитать LOWER для каждой строки. Это не-SARGable условие. Лекарство: хранить email уже в нижнем регистре или сравнивать без функции на колонке. Тот же запрос из прошлого урока без LOWER давал SEARCH ... USING INDEX.

Не-SARGable (плохо)	SARGable (хорошо)
`WHERE LOWER(email) = 'a@x'`	`WHERE email = 'a@x'`
`WHERE price * 1.2 > 100`	`WHERE price > 100 / 1.2`
`WHERE substr(code,1,3) = 'ABC'`	`WHERE code LIKE 'ABC%'`

Антипаттерн N+1

Это не про один SQL, а про то, как код общается с БД. Сначала запрос достаёт список (например, 100 заказов), а потом для каждого заказа делается отдельный запрос за клиентом: 1 + 100 = 101 поход в базу. Сеть и накладные расходы убивают производительность. Лекарство — один запрос с JOIN, который вернёт заказы и клиентов сразу.

-- N+1: так делает наивный ORM
SELECT * FROM orders;                       -- 1 запрос
SELECT * FROM customers WHERE id = ?;       -- ...и ещё по одному на заказ

-- Решение: один JOIN
SELECT o.*, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

Чек-лист оптимизации

Посмотрите план (EXPLAIN QUERY PLAN): нет ли SCAN там, где ждёте SEARCH.
Сделайте условия SARGable: уберите функции/арифметику с индексированной колонки.
Добавьте индексы на колонки из WHERE/JOIN; уберите неиспользуемые.
Не тащите лишнее: SELECT нужные_колонки вместо SELECT *, фильтруйте как можно раньше.
Убирайте N+1: один JOIN вместо запроса в цикле.
Замените тяжёлые коррелированные подзапросы на JOIN с агрегатом или оконную функцию.

Итог

EXPLAIN QUERY PLAN показывает маршрут запроса; ищите SCAN вместо SEARCH USING INDEX.
SARGable-условие не прячет колонку за функцией — только тогда индекс работает.
N+1 (запрос в цикле) лечится одним JOIN; тяжёлые подзапросы — оконными функциями.