Что такое индексы в базе данных и зачем они нужны?

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

Аналогия — предметный указатель в книге. Без него, чтобы найти упоминание термина, придётся пролистать все страницы; с ним вы открываете нужную сразу. Разница в том, что указатель занимает место и его приходится обновлять при каждом изменении текста — с индексами то же самое, и именно поэтому их не ставят на все столбцы подряд.

Разберём, как индекс устроен внутри, когда он ускоряет запрос, а когда бесполезен, и как проверить, что база им действительно пользуется.

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

По умолчанию в PostgreSQL, MySQL и большинстве других СУБД создаётся индекс типа B-tree (точнее, B+-дерево). Это сбалансированное дерево, где ключи отсортированы, а данные лежат в листьях.

Поиск идёт сверху вниз: на каждом уровне база сравнивает искомое значение с ключами узла и спускается в нужную ветку. Глубина дерева растёт логарифмически, поэтому в таблице на миллион строк до нужной записи будет 3–4 обращения к страницам вместо миллиона сравнений.

Из устройства следуют два практических вывода:

1) B-tree работает не только на точное совпадение. Раз ключи отсортированы, индекс годится для диапазонов (WHERE created_at > '2026-01-01'), сортировки (ORDER BY) и поиска по началу строки (LIKE 'abc%'). А вот LIKE '%abc' он ускорить не может — начало значения неизвестно, и спуститься по дереву не получится.

2) Листья связаны в список. Поэтому после нахождения первой подходящей записи база читает следующие подряд, что делает диапазонные запросы дешёвыми.

Другие типы индексов

• Hash — только точное равенство, зато без накладных расходов на упорядочивание. В PostgreSQL полноценно работает с версии 10; на практике B-tree чаще оказывается не хуже.
• GIN — для составных значений: полнотекстовый поиск, jsonb, массивы. Индексирует не значение целиком, а его элементы.
• GiST и SP-GiST — геоданные, диапазоны, нестандартные типы.
• BRIN — для очень больших таблиц, где данные физически лежат в порядке значения (например, логи по времени). Хранит минимум и максимум на блок, поэтому занимает копейки, но и работает только при такой «естественной» упорядоченности.
• Полнотекстовые индексы в MySQL — поиск по словам в тексте, а не по точному совпадению строки.

Начинать почти всегда стоит с B-tree и переходить к специализированным типам только под конкретную задачу.

Селективность: почему индекс иногда бесполезен

Ключевая характеристика столбца для индексирования — селективность, то есть насколько значения различаются между собой.

Индекс по email в таблице пользователей отличный: значение уникально, по нему находится ровно одна строка. Индекс по столбцу is_active со значениями true/false почти бесполезен: если активны 90% пользователей, база всё равно прочитает почти всю таблицу, и планировщик разумно решит, что последовательное сканирование дешевле — обращение к индексу, а затем к самой строке стоит дороже, чем просто прочитать страницы подряд.

Отсюда правило: чем больше уникальных значений в столбце относительно числа строк, тем полезнее индекс. Столбцы-флаги и поля с двумя-тремя вариантами значений индексируют только в составе составного индекса или частичного.

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

Индекс можно построить по нескольким столбцам сразу — и порядок в нём принципиален. Индекс (user_id, created_at) умеет обслуживать:

• условие только по user_id;
• условие по user_id вместе с created_at;
• сортировку по created_at внутри одного user_id.

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

Покрывающий индекс — частный случай, когда в индекс включены все столбцы, нужные запросу. Тогда база отвечает, не заглядывая в саму таблицу: в PostgreSQL это INCLUDE, в MySQL — просто перечисление столбцов в индексе. Такой запрос называется index-only scan и работает заметно быстрее обычного.

Частичный индекс индексирует не всю таблицу, а строки по условию: CREATE INDEX ... WHERE status = 'pending'. Если приложение постоянно ищет необработанные задачи, а их 1% от таблицы, индекс получится крошечным и очень быстрым.

Чем приходится платить

Индексы не бесплатны, и это главная причина, почему «проиндексировать всё» — плохая стратегия.

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

Практический ориентир: индексируйте столбцы, по которым реально фильтруете и соединяете таблицы в частых запросах, а не «на всякий случай». Неиспользуемые индексы стоит находить и удалять — в PostgreSQL их видно в pg_stat_user_indexes по нулевому idx_scan.

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

Частая ситуация: индекс создан, а база его игнорирует. Типичные причины:

1) Функция или приведение типа над столбцом. WHERE lower(email) = '...' не использует обычный индекс по email — нужен функциональный индекс по lower(email). То же с несовпадением типов: сравнение varchar-столбца с числом заставит базу приводить каждую строку.

2) Поиск по подстроке. LIKE '%текст%' не ускоряется B-tree — здесь нужен полнотекстовый поиск или триграммный индекс (pg_trgm).

3) Низкая селективность условия. База посчитала, что проще прочитать таблицу целиком, и, скорее всего, была права.

4) Устаревшая статистика. Планировщик опирается на статистику распределения значений; после массовой загрузки данных её стоит обновить (ANALYZE).

5) Слишком много строк в результате. Индекс выгоден, когда выбирается небольшая доля таблицы. При выборке половины строк выигрыша не будет.

Как проверить, что индекс работает

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

В PostgreSQL это EXPLAIN ANALYZE, в MySQL — EXPLAIN. В плане важно увидеть Index Scan или Index Only Scan вместо Seq Scan, а также сравнить ожидаемое число строк с фактическим: сильное расхождение обычно означает устаревшую статистику.

Отдельно стоит смотреть на медленные запросы в продакшене: в PostgreSQL их собирает расширение pg_stat_statements, в MySQL — slow query log. Это честнее, чем оптимизировать наугад: обычно выясняется, что почти вся нагрузка приходится на два-три запроса.

Практические примеры создания индексов, синтаксис и типовые рецепты — в отдельной статье про индексы в SQL. Как индексы влияют на производительность в бою и что за ними наблюдать, разбираем в материале про мониторинг PostgreSQL и MySQL, а про частую ошибку «индекс есть, а быстрее не стало» — в статье почему индекс не гарантирует быстрый запрос.

FAQ

Сколько индексов можно создать на таблицу? Технических ограничений почти нет, но каждый замедляет запись. Для большинства OLTP-таблиц разумный ориентир — до пяти-семи индексов, включая первичный ключ.

Нужен ли индекс на первичный ключ? Он создаётся автоматически: первичный ключ уже подразумевает уникальный индекс.

Индексировать ли внешние ключи? Да, почти всегда. Соединения идут именно по ним, а в PostgreSQL отсутствие индекса на внешнем ключе к тому же замедляет удаление строк из родительской таблицы.

Ускоряет ли индекс SELECT * без условий? Нет. Если запрос читает всю таблицу без фильтра, индекс только мешает — база просто пройдёт таблицу последовательно.