Индексы в SQL — что это, зачем нужны и как работают

Коротко

Индекс в базе данных — это вспомогательная структура, которая ускоряет поиск строк по определённому столбцу. Без индекса СУБД перебирает всю таблицу целиком (Seq Scan). С индексом — находит нужные строки за логарифмическое время. На собеседованиях аналитиков тему индексов спрашивают реже, чем JOIN или GROUP BY, но на позициях middle+ она встречается регулярно.

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

Представьте учебник на 500 страниц. Чтобы найти раздел про «нормальное распределение», можно листать страницу за страницей — это Seq Scan. А можно открыть предметный указатель в конце книги, найти слово за секунду и перейти на нужную страницу — это индекс.

Индекс в базе данных устроен так же: он хранит отсортированные значения столбца и ссылки на строки таблицы, где эти значения лежат. СУБД сначала ищет в индексе, а потом точечно обращается к таблице за полными данными.

B-tree — основной тип индекса

По умолчанию PostgreSQL создаёт B-tree индекс. Это сбалансированное дерево, где данные отсортированы и поиск выполняется за O(log n). B-tree подходит для операций сравнения: =, <, >, <=, >=, BETWEEN, а также для ORDER BY и IS NULL.

CREATE INDEX idx_orders_user_id ON orders (user_id);

После создания этого индекса запрос SELECT * FROM orders WHERE user_id = 42 больше не сканирует всю таблицу — он находит нужные строки через дерево.

Типы индексов в PostgreSQL

Тип	Когда использовать	Пример
B-tree	Равенство, диапазоны, сортировка	WHERE created_at >= '2025-01-01'
Hash	Только точное равенство	WHERE status = 'active'
GIN	Полнотекстовый поиск, массивы, JSONB	WHERE tags @> '{sql}'
GiST	Геоданные, диапазоны, полнотекстовый поиск	WHERE location <-> point(55.75, 37.62) < 1
BRIN	Большие таблицы с естественной сортировкой (даты, ID)	WHERE created_at >= '2025-01-01' на таблице с 100M+ строк

Для аналитика в 90% случаев достаточно знать B-tree. Hash на практике почти не используется. GIN полезен, если вы работаете с JSONB-полями. BRIN стоит держать в голове для таблиц с событиями — он занимает мало места и быстр на столбцах, данные которых физически упорядочены (например, created_at в таблице логов).

Составные индексы и порядок столбцов

Составной (multi-column) индекс включает несколько столбцов. Порядок столбцов принципиально важен.

CREATE INDEX idx_events_user_date
ON training_events (user_id, created_at);

Этот индекс ускорит:

• WHERE user_id = 42 — используется первый столбец
• WHERE user_id = 42 AND created_at >= '2025-01-01' — оба столбца
• WHERE user_id = 42 ORDER BY created_at — поиск + сортировка

Но не ускорит:

• WHERE created_at >= '2025-01-01' — без первого столбца индекс бесполезен

Правило: столбец с условием равенства ставьте первым, столбец с диапазоном — вторым. Это называют «правило левого префикса».

Когда индексы помогают

• WHERE по конкретному значению: WHERE user_id = 42 — классический случай.
• JOIN по ключу: JOIN orders ON users.id = orders.user_id — индекс на orders.user_id ускоряет соединение.
• ORDER BY: если данные отсортированы в индексе, СУБД не тратит время на сортировку.
• Уникальность: CREATE UNIQUE INDEX — гарантирует, что значения не повторяются.

-- Аналитический запрос: активность пользователя за период
SELECT
    DATE_TRUNC('day', created_at) AS dt,
    COUNT(*) AS answers
FROM training_events
WHERE user_id = 42
  AND created_at >= '2025-01-01'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY dt;

С составным индексом (user_id, created_at) этот запрос выполняется за миллисекунды даже на таблице с миллионами строк.

Когда индексы НЕ помогают

• Маленькие таблицы (до нескольких тысяч строк) — Seq Scan быстрее, чем обращение к индексу.
• Низкая селективность — столбец с 2–3 уникальными значениями (например, gender) не имеет смысла индексировать. СУБД всё равно прочитает большую часть таблицы.
• Частые INSERT и UPDATE — каждый индекс замедляет запись, потому что СУБД обновляет и данные, и индекс. На таблице с 10 индексами вставка будет ощутимо медленнее.
• Функции над столбцом — WHERE LOWER(email) = 'test@mail.ru' не использует обычный индекс на email. Нужен функциональный индекс: CREATE INDEX idx_email_lower ON users (LOWER(email)).

EXPLAIN ANALYZE: проверяем, работает ли индекс

Единственный надёжный способ узнать, использует ли запрос индекс, — посмотреть план выполнения.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42;

В плане ищите:

• Index Scan или Index Only Scan — индекс используется.
• Seq Scan — полный перебор таблицы, индекс проигнорирован.
• Bitmap Index Scan — СУБД сначала строит битовую карту по индексу, затем читает данные. Типично для условий с OR или диапазонами.

Если видите Seq Scan на большой таблице — проверьте, есть ли подходящий индекс и нет ли функции вокруг столбца.

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

Покрывающий индекс (covering index) содержит все столбцы, необходимые запросу. СУБД не обращается к таблице вообще — всё берёт из индекса (Index Only Scan).

CREATE INDEX idx_events_covering
ON training_events (user_id, created_at)
INCLUDE (is_correct);

Теперь запрос, который выбирает user_id, created_at и is_correct, вообще не читает таблицу.

Частичный индекс (partial index) индексирует только подмножество строк:

CREATE INDEX idx_active_subs
ON subscriptions (user_id)
WHERE status = 'active';

Индекс занимает меньше места и быстрее обновляется, потому что включает только активные подписки.

Типичные ошибки

Создавать индексы на каждый столбец. Каждый индекс — это дополнительное дисковое пространство и замедление записи. Индексируйте то, что реально используется в WHERE и JOIN.

Забывать про порядок столбцов в составном индексе. Индекс (created_at, user_id) бесполезен для запроса WHERE user_id = 42. Важен именно порядок.

Индексировать столбцы с низкой кардинальностью. Индекс на булево поле (is_deleted) обычно не даёт выигрыша. Исключение — частичный индекс: WHERE is_deleted = false.

Не проверять EXPLAIN. Наличие индекса не гарантирует его использование. Оптимизатор может решить, что Seq Scan эффективнее. Всегда проверяйте план запроса.

Вопросы с собеседований

— Что такое индекс в базе данных и как он работает? — Индекс — вспомогательная структура данных (обычно B-tree), которая хранит отсортированные значения столбца и указатели на строки таблицы. Позволяет находить данные за O(log n) вместо O(n). Аналогия — предметный указатель в книге.

— Когда индекс замедляет работу? — При частых INSERT, UPDATE и DELETE. Каждая такая операция обновляет не только таблицу, но и все связанные индексы. Если на таблице много индексов и высокая частота записи, накладные расходы могут быть существенными.

— Чем Index Scan отличается от Index Only Scan? — Index Scan находит строки через индекс, а затем обращается к таблице за полными данными. Index Only Scan берёт все нужные столбцы прямо из индекса, не трогая таблицу. Index Only Scan быстрее, но требует, чтобы все запрашиваемые столбцы были в индексе.

— Почему запрос не использует существующий индекс? — Возможные причины: столбец обёрнут в функцию (WHERE LOWER(name) = ...), низкая селективность (индекс вернул бы большую часть таблицы), устаревшая статистика (нужен ANALYZE), оптимизатор оценил Seq Scan дешевле.

— Что такое составной индекс и как работает правило левого префикса? — Составной индекс включает несколько столбцов. Он используется, если запрос фильтрует по первому столбцу индекса (или по первому + второму, и т.д.). Запрос, фильтрующий только по второму столбцу, индекс не использует. Поэтому столбцы с равенством ставят первыми, с диапазоном — последними.

---

Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.

FAQ

Сколько индексов может быть на одной таблице?

Технического ограничения почти нет — в PostgreSQL можно создать сотни индексов. Но на практике оптимальное число — 3–7 на таблицу. Каждый лишний индекс замедляет вставку и занимает место. Если индексов больше десятка, стоит проверить, все ли они реально используются: pg_stat_user_indexes покажет статистику обращений.

Нужно ли аналитику уметь создавать индексы?

На позицию junior — достаточно понимать, что индексы существуют и зачем нужны. На middle+ ожидают, что вы можете прочитать EXPLAIN, предложить индекс для медленного запроса и объяснить, почему запрос не использует существующий индекс. Создавать индексы в продакшене обычно задача DBA, но понимание механики — обязательно.

Удаляются ли индексы автоматически?

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

Как индексы связаны с первичным ключом?

PRIMARY KEY автоматически создаёт уникальный B-tree индекс. То же самое делает UNIQUE-ограничение. Поэтому создавать отдельный индекс на столбец, который уже является первичным ключом, — бессмысленно.

Как тренироваться

Индексы — тема, которую проще понять на практике. Создайте таблицу с миллионом строк, выполните запрос без индекса и с индексом, сравните планы через EXPLAIN ANALYZE. Разница будет наглядной.

В тренажёре Карьерник есть вопросы на индексы, планы выполнения и оптимизацию запросов. Больше примеров вопросов по всем темам — в разделе с примерами.