Индексы в БД

Что такое индекс в БД

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

Без индекса запрос WHERE email = 'ivan@example.com' заставляет базу прочитать каждую строку таблицы и сравнить поле — это Sequential Scan. На 100 строках разницы нет, на 10 миллионах это единицы секунд вместо долей миллисекунды.

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

Как это работает

CREATE INDEX idx_users_email ON users (email);

Большинство индексов — сбалансированные B-деревья. Значения хранятся отсортированными, поиск идёт спуском по дереву: на каждом уровне отбрасывается большая часть данных. Практическое следствие — число обращений к диску растёт логарифмически: при росте таблицы с миллиона строк до миллиарда глубина дерева увеличивается всего на несколько уровней.

Поэтому B-tree годится не только для =, но и для <, >, BETWEEN, ORDER BY и LIKE 'префикс%' — данные уже отсортированы. А вот LIKE '%подстрока' индексом не пользуется: искать по концу строки в отсортированном по началу дереве нечем.

Проверка: EXPLAIN

Единственный способ узнать, работает ли индекс, — спросить у базы:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'ivan@example.com';

-- Без индекса:
-- Seq Scan on users  (cost=0.00..18334.00 rows=1 width=64)
--   Filter: (email = 'ivan@example.com')
--   Rows Removed by Filter: 999999          ← прочитал всё, выбросил всё

-- С индексом:
-- Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=64)
--   Index Cond: (email = 'ivan@example.com')

Seq Scan на большой таблице с узким фильтром — сигнал, что индекса нет или он не подходит. В MySQL то же самое смотрят через EXPLAIN: type: ALL — полный скан, type: ref или range — индекс используется.

Составной индекс и правило левого префикса

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

Такой индекс работает для запросов по:

• WHERE user_id = 42 — левый префикс, да
• WHERE user_id = 42 AND created_at > '2026-01-01' — да, обе колонки
• WHERE user_id = 42 ORDER BY created_at — да, сортировка бесплатна

И не работает для WHERE created_at > '2026-01-01' без user_id — это не левый префикс. Порядок колонок в составном индексе критичен, и (user_id, created_at) — не то же самое, что (created_at, user_id).

Общее правило: слева — колонки с условием на равенство, справа — диапазоны и сортировка.

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

Тип	Для чего	Пример
B-tree	Универсальный: =, <, >, сортировка	По умолчанию
GIN	Поиск внутри составных значений	jsonb, массивы, полнотекст
GiST	Геометрия, диапазоны	PostGIS, tsrange
BRIN	Огромные таблицы с естественным порядком	Логи по времени
Hash	Только =	Редко нужен, B-tree обычно не хуже

-- Поиск по содержимому jsonb
CREATE INDEX idx_events_payload ON events USING GIN (payload);

-- Логи на сотни миллионов строк: BRIN занимает считанные мегабайты
CREATE INDEX idx_logs_ts ON logs USING BRIN (created_at);

BRIN — недооценённый вариант: он хранит не значения, а границы диапазонов по блокам, поэтому в тысячи раз меньше B-tree. Работает только если физический порядок строк совпадает с порядком значений — что как раз верно для логов, которые всегда дописываются в конец.

Частичный и покрывающий

-- Частичный: индексируем только то, что ищем
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

-- Покрывающий: нужные колонки лежат в самом индексе
CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (total, status);

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

Покрывающий индекс даёт Index Only Scan — базе не нужно ходить в саму таблицу за остальными колонками, потому что всё требуемое уже в индексе.

Цена индексов

Индексы не бесплатны, и лишние вредят:

• Замедляют запись. Каждый INSERT, UPDATE и DELETE обновляет ещё и все индексы таблицы. Десять индексов — десятикратная работа на запись.
• Занимают место. Индексы легко перевешивают саму таблицу по объёму.
• Путают планировщик. Чем больше вариантов, тем выше шанс, что он выберет не лучший.

Найти бесполезные:

-- Индексы, которыми никто не пользовался
SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Практика

• Индексируйте то, что в WHERE, JOIN и ORDER BY, а не всё подряд.
• Внешние ключи — обязательно. PostgreSQL индексирует первичный ключ автоматически, а внешний — нет.
• Низкая кардинальность — обычно бесполезно. Индекс по колонке is_active с двумя значениями базе почти не поможет.
• Функция на колонке убивает индекс. WHERE lower(email) = ... не использует индекс по email — нужен индекс по выражению: CREATE INDEX ON users (lower(email)).
• На бою — CREATE INDEX CONCURRENTLY, иначе таблица блокируется на запись (про миграции).

Связанные термины

• Базы данных — где живут индексы
• SQL — запросы, которые индексы ускоряют
• Ключи в БД — первичные и внешние ключи и их индексы
• PostgreSQL — B-tree, GIN, GiST, BRIN
• MySQL / MariaDB — EXPLAIN и буферный пул
• Миграции БД — как добавить индекс без простоя