SQL: индексы, транзакции, EXPLAIN, N+1¶

TL;DR¶

Индекс — структура (B-tree обычно), ускоряет поиск, замедляет запись.
Не работает при функциях над колонкой: WHERE LOWER(email) = ... → нужен functional index.
Транзакция: ACID. Уровни изоляции: RU < RC < RR < SERIALIZABLE.
PostgreSQL дефолт — READ COMMITTED.
EXPLAIN ANALYZE — план + реальные тайминги.
N+1 — главная боль ORM, лечится JOIN'ом или IN(...).

Индексы¶

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_lower_email ON users(LOWER(email));   -- functional
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC); -- composite

Когда индекс не работает:

-- НЕТ индекса по lower(email):
WHERE LOWER(email) = 'foo@bar.com'

-- Преобразование типа:
WHERE user_id::text = '123'

-- Leading wildcard:
WHERE email LIKE '%@gmail.com'

-- OR между разными колонками:
WHERE user_id = 1 OR email = 'x@y'   -- (можно UNION разделить)

Транзакции и ACID¶

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

ACID: - Atomicity — всё или ничего. - Consistency — invariants после commit'а. - Isolation — уровень параллельности. - Durability — после commit данные не потеряются (WAL).

Уровни изоляции¶

Уровень	Dirty read	Non-repeatable	Phantom
READ UNCOMMITTED	возможен	возможен	возможен
READ COMMITTED (PG default)	нет	возможен	возможен
REPEATABLE READ	нет	нет	нет*
SERIALIZABLE	нет	нет	нет

*В PostgreSQL REPEATABLE READ защищает от phantom read через snapshot isolation, что строго сильнее чем стандарт SQL.

EXPLAIN ANALYZE¶

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 100 AND status = 'paid';

На что смотрим в плане:

Seq Scan — последовательное чтение, плохо при больших таблицах.
Index Scan / Bitmap Index Scan — использует индекс.
rows — оценка vs actual rows — большой разрыв = плохая статистика (запусти ANALYZE table_name).
cost vs actual time — если actual >> cost, тоже плохо.

N+1 problem¶

-- N+1: один запрос за пользователями + N запросов за их заказами:
SELECT * FROM users LIMIT 10;
-- для каждого user_id:
SELECT * FROM orders WHERE user_id = ?;

Решения:

-- 1. JOIN:
SELECT u.id, u.name, o.id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (1, 2, 3, ...);

-- 2. WHERE IN:
SELECT * FROM orders WHERE user_id = ANY($1);  -- $1 = array of ids

-- 3. lateral subquery (если нужно top-N per group):
SELECT u.*, recent.* FROM users u
LEFT JOIN LATERAL (
    SELECT * FROM orders WHERE user_id = u.id
    ORDER BY created_at DESC LIMIT 5
) recent ON true;

Pagination¶

-- LIMIT/OFFSET — простой, но медленный на больших offset'ах:
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000;

-- Keyset pagination — быстрее, требует уникального cursor:
SELECT * FROM orders
WHERE created_at < $1 OR (created_at = $1 AND id < $2)
ORDER BY created_at DESC, id DESC
LIMIT 20;

SELECT FOR UPDATE¶

Lock на конкретные строки внутри транзакции:

BEGIN;
SELECT * FROM accounts WHERE id = $1 FOR UPDATE;
-- никто другой не сможет UPDATE/DELETE этой строки до commit
UPDATE accounts SET balance = ... WHERE id = $1;
COMMIT;

FOR NO KEY UPDATE слабее (не блокирует foreign key проверки) — обычно лучше для FK-родителей.

Что чаще всего спрашивают на собесе¶

Чем READ COMMITTED отличается от REPEATABLE READ?
Что такое phantom read?
Почему индекс на email не работает при WHERE LOWER(email) = ...?
Как починить N+1?
Чем EXPLAIN отличается от EXPLAIN ANALYZE?
Что делает SELECT FOR UPDATE и зачем?
Что такое WAL и зачем?