Индексы в SQL: как ускорить запросы
Индексы — главный инструмент оптимизации SQL-запросов. Без них база данных читает все строки таблицы (seq scan). Правильный индекс ускоряет запрос в сотни раз. На собеседованиях часто спрашивают: «Как бы вы ускорили этот запрос?»
Содержание
Как работает индекс
Индекс — отдельная структура данных (обычно B-дерево), хранящая значения колонки в отсортированном виде со ссылками на строки таблицы.
Без индекса: PostgreSQL читает все строки таблицы (Sequential Scan) — O(n). С индексом: бинарный поиск по B-дереву — O(log n), потом прямой переход к нужным строкам.
-- Создать индекс на колонку user_id в таблице orders
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Посмотреть план запроса до и после
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;Когда индекс используется
Индекс помогает при: - Фильтрации: WHERE user_id = 42 - JOIN: ON o.user_id = u.id - Сортировке: ORDER BY created_at (если индекс на created_at) - Проверке уникальности: UNIQUE индекс
Индекс НЕ помогает при: - Функциях над колонкой: WHERE LOWER(email) = '...' (нужен функциональный индекс) - Операции LIKE с % в начале: LIKE '%text' - Низкой селективности (булевы поля): сканировать половину таблицы выгоднее через seq scan
Составной индекс
Индекс по нескольким колонкам. Работает слева направо — запрос должен использовать колонки в том же порядке, что и индекс.
Правило: ставь в начало колонку с наибольшим количеством уникальных значений (высокая кардинальность).
-- Индекс для запросов вида: WHERE user_id = ? AND status = ?
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Этот запрос использует индекс:
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';
-- Этот тоже (только по user_id — частичное использование):
SELECT * FROM orders WHERE user_id = 42;
-- Этот НЕ использует (нет user_id в условии):
SELECT * FROM orders WHERE status = 'paid';Покрывающий индекс
Если индекс содержит все колонки, которые нужны запросу — база вообще не обращается к таблице (Index Only Scan). Это максимально быстро.
-- Запрос нуждается в user_id, amount, created_at
-- Покрывающий индекс — включаем все три колонки
CREATE INDEX idx_orders_covering ON orders(user_id, created_at, amount);
-- Index Only Scan — данные берутся только из индекса
SELECT created_at, amount
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;Минусы индексов
Индексы — не бесплатны: - Замедляют INSERT/UPDATE/DELETE (нужно обновить индекс) - Занимают место на диске - Неиспользуемые индексы — просто балласт
Правило: создавай индексы под конкретные медленные запросы, а не «на всякий случай». Используй EXPLAIN ANALYZE чтобы проверить что индекс реально работает.
-- Найти неиспользуемые индексы в PostgreSQL
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE 'pg_%';Закрепи знания на практике
Решай реальные задачи с собеседований прямо в браузере — без установки.
Попробовать EXPLAIN ANALYZE →