Как определить, какой столбец нужно индексировать в SQL

Зачем индексировать столбцы в SQL?

Представьте: ваш эндпоинт, который работал мгновенно на маленькой базе, вдруг начал тайм-аутить в продакшене. Знакомая ситуация? Чаще всего проблема в отсутствии индекса. Я покажу, как по опыту находить столбцы, которые действительно нужно индексировать, как доказать, что индекс помог, и как не переплачивать за лишние.

Какие столбцы нуждаются в индексе

Кандидаты — столбцы, которые вы фильтруете, сортируете или используете в JOIN. В терминах SQL — всё, что стоит в WHERE, ORDER BY, JOIN или GROUP BY на большой или растущей таблице.

Первоочередные кандидаты

• Внешние ключи — например, user_id и order_id. В Rails они не получают индекс автоматически, если не указать опцию, а по ним постоянно идут ассоциации и JOIN. Это самый частый пропущенный индекс.
• Столбцы для поиска — email, slug, token. Обычно они должны иметь уникальный индекс.
• Столбцы для фильтрации и сортировки — status, created_at — то, что используется в дашбордах и пагинации.

Всё это неважно на маленьком объёме. Последовательное сканирование 500 строк — мгновенно. Проблемы начинаются на нескольких миллионах строк, когда эндпоинт, работавший в разработке, начинает тайм-аутить в продакшене.

Как измерить, помог ли индекс

Основной инструмент — EXPLAIN ANALYZE. Пример запроса:

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

Что читать в выводе:

• Seq Scan на большой таблице — красный флаг: Postgres читает каждую строку.
• После добавления индекса должно появиться Index Scan или Bitmap Index Scan.

Сравнивайте фактическое время выполнения до и после. Также проверяйте оценённое количество строк против реального — если они сильно расходятся, планировщик работает на устаревшей статистике. Иногда простой ANALYZE исправляет запрос без индекса.

Локальный план — не всё. Чтобы найти, что стоит оптимизировать, смотрите на продакшен: pg_stat_statements для реально тяжёлых запросов, APM (New Relic, Skylight, Datadog) и тайминги запросов в логах. Легко увлечься оптимизацией запроса, который выполняется дважды в день, игнорируя тот, что летает 10 тысяч раз в час.

Индексы не бесплатны

Каждый индекс занимает место на диске и замедляет запись: каждый INSERT, UPDATE и DELETE должен обновлять индекс. На write-heavy таблицах это быстро накапливается.

Поэтому не индексируйте наугад. Индексируйте столбцы, которые реально используются в запросах, и удаляйте неиспользуемые индексы. Неиспользуемый индекс — чистый убыток: замедляет запись и не даёт ничего взамен.

Практический пример: составной индекс

Рассмотрим запрос:

Order.where(user_id: id, status: 'paid')

Добавим составной индекс на [:user_id, :status]:

add_index :orders, [:user_id, :status]

Порядок столбцов решает всё. user_id идёт первым, потому что это селективный и всегда присутствующий фильтр равенства. B-tree сужает поиск до заказов одного пользователя, затем status фильтрует в пределах этого набора.

Бонус: так как user_id — левый столбец, этот же индекс покрывает запросы с фильтром только по user_id, не нужен отдельный индекс.

Обратный порядок [:status, :user_id] был бы хуже. У status всего несколько значений, поэтому он почти не сужает поиск перед тем, как дойти до user_id.

Практический вывод

Прямо сейчас откройте свою базу данных и найдите таблицы с внешними ключами без индексов. Используйте EXPLAIN ANALYZE на частых запросах и добавьте недостающие индексы. Затем проверьте, не осталось ли неиспользуемых индексов — удалите их. Это сэкономит ресурсы и ускорит запись.