Индексы в базах данных: как они ускоряют запросы и когда могут навредить

«Просто добавь индекс» — один из самых частых советов, которые дают, когда запрос к базе данных начинает работать медленно. Иногда это действительно решает проблему за одну строку миграции. А иногда индекс либо вообще не используется планировщиком, либо ускоряет один запрос ценой замедления десятка других. Чтобы решать, где индекс уместен, полезно понимать не только «индекс ускоряет чтение», но и то, как он устроен внутри и за что вы им платите.

Зачем нужен индекс и как он устроен

Без индекса база данных, чтобы найти нужные строки, вынуждена читать таблицу целиком — это называется full table scan, полное сканирование. При маленькой таблице это дёшево, но по мере роста количества строк время такого поиска растёт линейно: в десять раз больше строк — примерно в десять раз дольше поиск.

Индекс — это отдельная структура данных, которая хранит значения одного или нескольких столбцов вместе со ссылкой на строку в таблице, но уже в отсортированном и удобном для поиска виде. Чаще всего это B-дерево (B-tree): сбалансированная структура, в которой поиск, вставка и удаление занимают время, растущее не линейно, а логарифмически от размера данных. Именно поэтому индекс на таблице в миллион строк может найти нужную запись за считаные обращения к диску или странице в памяти, а не за проход по всей таблице.

Есть и другие виды индексов — хеш-индексы для точечного поиска по равенству, GIN и GiST для полнотекстового поиска и работы с массивами и JSON в PostgreSQL, специализированные пространственные индексы для геоданных. Но в подавляющем большинстве повседневных задач речь идёт именно о B-дереве, и дальше в статье мы говорим о нём.

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

Наличие индекса не гарантирует, что он будет применён. У любой современной реляционной базы есть планировщик запросов (query planner), который на основе статистики о данных — количестве строк, распределении значений, кардинальности — оценивает, какой путь выполнения запроса окажется дешевле: пройти по индексу или прочитать таблицу целиком.

Если индекс покрывает, скажем, всего 2% строк таблицы, планировщик почти наверняка им воспользуется — точечный поиск через дерево обойдётся заметно дешевле полного сканирования. А вот если запрос должен вернуть 60% строк таблицы, полное сканирование зачастую оказывается быстрее: чтение по индексу означает много случайных обращений к диску (по одной строке за раз), тогда как последовательное сканирование таблицы читается блоками и почти всегда работает эффективнее при таких объёмах выборки.

Отсюда практический вывод: индекс — это ставка на то, что запрос будет выбирать небольшую долю строк. Индекс на столбце с низкой кардинальностью (например, булево поле «активен/неактивен», где значений всего два) почти всегда бесполезен, потому что не помогает планировщику отсечь много строк.

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

Отдельная тема — индексы по нескольким столбцам сразу. Здесь важно, что порядок столбцов в определении индекса не произвольный, а определяет, для каких запросов индекс вообще будет полезен. Составной индекс по столбцам (a, b, c) устроен как единая отсортированная структура: сначала по a, затем внутри одинаковых a — по b, и так далее.

• Такой индекс отлично работает для запросов с условием по a, по a и b, или по всем трём столбцам.
• Он почти бесполезен для запроса, который фильтрует только по b или только по c, без условия на a — база не может «прыгнуть» в середину отсортированной структуры, не зная значения первого столбца.
• Столбец, по которому чаще всего фильтруют в реальных запросах и который имеет высокую кардинальность, обычно стоит ставить первым.
• Если запрос ещё и сортирует результат по какому-то столбцу, включение этого столбца в индекс может избавить базу от отдельного шага сортировки после выборки.

На практике это значит, что бездумно добавленные «индексы на всякий случай» по одному столбцу часто дублируют друг друга или вовсе не соответствуют реальным паттернам запросов приложения. Прежде чем добавлять индекс, стоит посмотреть на реальные запросы через EXPLAIN (или EXPLAIN ANALYZE) и увидеть, что база действительно делает сейчас — полное сканирование, поиск по неподходящему индексу или что-то ещё.

За что вы платите, добавляя индекс

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

Каждая вставка, обновление или удаление строки требует обновить не только саму таблицу, но и каждый индекс, который затрагивает изменённые столбцы. Чем больше индексов на таблице, тем дороже становится каждая операция записи. Для таблиц с интенсивной записью (логи событий, очереди, счётчики) это особенно ощутимо: добавление пятого «на всякий случай» индекса может заметно замедлить вставку данных, хотя ни один запрос на чтение не станет от этого быстрее.

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

Отсюда практическое правило: для таблиц-справочников, которые читают часто, а пишут редко (каталог товаров, список пользователей), индексов может быть много и это оправданно. Для таблиц с высокой частотой записи и редкими выборочными запросами стоит держать набор индексов минимальным и добавлять новый только под конкретный, измеренный запрос, а не «про запас».

Итог

Индекс — это инструмент, а не универсальное решение проблем с производительностью. Он помогает, когда запрос выбирает небольшую долю строк по столбцу с достаточно высокой кардинальностью, и почти не помогает или даже мешает в противоположных случаях. Прежде чем добавлять индекс, полезно посмотреть на план выполнения реального запроса, понять, какую долю данных он затрагивает, и оценить, как часто в эту таблицу пишут. Тогда решение «добавить индекс» превращается из интуитивного жеста в обоснованный шаг, за который не придётся потом расплачиваться на записи.