Индексы в базе: какие ставить и когда они не спасают

Как индексы работают: компромисс между скоростью и местом

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

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

Почему порядок колонок в индексе важен

Составной индекс — это индекс по нескольким колонкам. Порядок колонок в нём критически важен, потому что движок может использовать индекс только в том случае, если условие запроса начинается с первых колонок индекса.

Допустим, у нас есть индекс (country, city, street). Он будет использован для запросов:

• WHERE country = 'Russia'
• WHERE country = 'Russia' AND city = 'Moscow'
• WHERE country = 'Russia' AND city = 'Moscow' AND street = 'Tverskaya'

Но не будет использован для:

• WHERE city = 'Moscow' (первая колонка индекса не задействована)
• WHERE street = 'Tverskaya' (первая и вторая колонки не задействованы)
• WHERE country > 'Russia' AND city = 'Moscow' (после диапазонного условия на country колонка city уже не может быть использована для точного поиска)

Если в запросах часто используется условие по city, но редко по country, то индекс (city, country, street) будет полезнее. Поэтому порядок колонок в индексе должен отражать реальные паттерны запросов: сначала те колонки, которые чаще всего используются в условиях и имеют высокую избирательность.

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

Даже если индекс создан, движок может его не использовать. Вот основные причины, почему индекс может простаивать:

1. Функция над колонкой. Если в условии запроса используется функция над колонкой, индекс не будет использован. Например, WHERE LOWER(name) = 'alice' не возьмёт индекс по name, потому что движок не может применить индекс к результату функции. Решение — либо создать индекс по выражению (CREATE INDEX idx_name_lower ON users (LOWER(name))), либо изменить запрос, чтобы он не использовал функцию.

2. Приведение типов. Если тип колонки и тип значения в условии не совпадают, движок может не использовать индекс. Например, WHERE id = '123' (где id — целое число) может не взять индекс по id, потому что движок должен привести строку '123' к числу. Решение — привести значение к типу колонки в запросе: WHERE id = 123.

3. Неподходящий порядок колонок. Если условие не начинается с первых колонок индекса, индекс не будет использован. Решение — создать индекс с правильным порядком колонок или изменить запрос.

4. Низкая избирательность. Если индекс покрывает слишком много строк, движок может решить, что полное сканирование таблицы будет быстрее, чем использование индекса. Например, индекс по булевой колонке (is_active) или по колонке с небольшим количеством уникальных значений (status) может не использоваться, если условие покрывает больше половины таблицы. Решение — либо не создавать такой индекс, либо использовать его только в комбинации с другими колонками.

5. Слишком сложные условия. Если условие запроса содержит OR, NOT или подзапросы, движок может не использовать индекс. Например, WHERE a = 1 OR b = 2 может не взять индексы по a и b, потому что движок не может объединить результаты двух индексных сканирований. Решение — переписать запрос или создать составной индекс, который покрывает оба условия.

Как понять, что индекс нужен

Прежде чем создавать индекс, стоит проверить, действительно ли он нужен и будет ли использоваться. Вот как это сделать:

1. Анализ медленных запросов. Посмотрите на запросы, которые выполняются дольше всего. В PostgreSQL для этого можно использовать pg_stat_statements или логи медленных запросов. Если запрос часто выполняется и использует условие по колонке, индекс может помочь.

2. EXPLAIN ANALYZE. Используйте EXPLAIN ANALYZE для запроса, чтобы понять, как движок его выполняет. Если видите Seq Scan (полное сканирование таблицы) и запрос медленный, индекс может ускорить его. Если видите Index Scan или Index Only Scan, индекс уже используется.

3. Избирательность. Посчитайте, сколько строк покрывает условие запроса. Если избирательность низкая (например, условие покрывает больше половины таблицы), индекс может не помочь. Для этого можно использовать EXPLAIN без ANALYZE: EXPLAIN SELECT * FROM users WHERE is_active = true. Если в выводе видите rows=500000 (половина таблицы), индекс по is_active не поможет.

После создания индекса снова выполните EXPLAIN ANALYZE, чтобы убедиться, что индекс используется. Если видите Index Scan или Index Only Scan, индекс работает. Если видите Seq Scan, индекс не используется — ищите причину.

Какие индексы стоит удалить

Не все индексы полезны. Вот какие индексы стоит удалить, чтобы ускорить запись и освободить место на диске:

1. Дубликаты. Если у вас есть индекс (a, b) и индекс (a), то индекс (a) избыточен, потому что (a, b) уже покрывает условия по a. Удаление дубликатов ускорит запись и освободит место.

2. Неиспользуемые индексы. Проверьте статистику использования индексов с помощью pg_stat_user_indexes. Если индекс не используется (idx_scan = 0), его можно удалить. Но будьте осторожны: индекс может использоваться редко, но критически важно для какого-то важного запроса.

3. Покрытые более широким индексом. Если у вас есть индекс (a, b, c) и индекс (a, b), то второй индекс избыточен, потому что первый покрывает все условия, которые может покрыть второй. Удаление таких индексов ускорит запись.

4. Индексы с низкой избирательностью. Если индекс покрывает слишком много строк (например, индекс по булевой колонке), он может не использоваться и только замедлять запись. Такие индексы стоит удалить.

Тупик: индекс на каждую колонку

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

Во-первых, каждый индекс замедляет запись. Если у вас 10 индексов, каждая вставка или обновление будет в 10 раз медленнее, чем без индексов. В таблице с высокой нагрузкой на запись это может стать критическим узким местом.

Во-вторых, индексы занимают место на диске. Если таблица большая, индексы могут занять столько же места, сколько сама таблица, или даже больше. Например, таблица на 100 миллионов строк с индексом по текстовой колонке может занимать столько же места, сколько сама таблица. А если индексов несколько, место на диске может закончиться.

В-третьих, движок не может использовать несколько индексов для одного запроса. Если запрос использует условия по нескольким колонкам, движок возьмёт только один индекс (если он подходит) или не возьмёт ни одного. Например, для запроса WHERE a = 1 AND b = 2 движок может использовать индекс по a или по b, но не оба сразу. Поэтому составные индексы часто полезнее, чем отдельные индексы на каждую колонку.

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

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

Как проверить, что индекс используется

После создания индекса важно убедиться, что он действительно используется. Вот как это сделать:

1. EXPLAIN ANALYZE. Выполните EXPLAIN ANALYZE для запроса, который должен использовать индекс. Если видите Index Scan или Index Only Scan, индекс используется. Если видите Seq Scan, индекс не используется — ищите причину.

2. Статистика использования индексов. В PostgreSQL можно посмотреть статистику использования индексов с помощью pg_stat_user_indexes. Поле idx_scan показывает, сколько раз индекс был использован. Если idx_scan = 0, индекс не используется.

3. Проверка избирательности. Если индекс используется, но запрос всё равно медленный, проверьте избирательность индекса. Возможно, индекс покрывает слишком много строк, и движок предпочитает полное сканирование таблицы.

Позиция редакции

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

Эта позиция неверна, если:

• у вас очень маленькая таблица, где индексы не дают заметного ускорения, потому что полное сканирование и так быстрое;
• у вас только запись, и чтение почти не происходит — в этом случае индексы только замедляют работу;
• вы используете базу данных, которая не поддерживает B-деревья или имеет другие механизмы индексации, например, колоночные базы или базы с индексами по хешам.