Индексы SQL

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

B-tree

Наиболее распространенный индекс поддерживает равенство, диапазоны и упорядоченный проход:

CREATE INDEX idx_orders_user_created
    ON orders (user_id, created_at DESC);

Он подходит для запроса:

SELECT id, total, created_at
FROM orders
WHERE user_id = 42
  AND created_at >= TIMESTAMP '2026-01-01 00:00:00'
ORDER BY created_at DESC;

В составном B-tree важен порядок столбцов. Упрощенное правило «левого префикса» полезно, но не абсолютно: оптимизатор может использовать skip scan, bitmap scan и другие возможности конкретной СУБД.

Другие виды

• hash — равенство, если поддерживается реализацией;
• inverted/GiST/GIN-подобные структуры — массивы, документы, полнотекстовый поиск, геоданные;
• bitmap — аналитические сценарии и низкая кардинальность в некоторых СУБД;
• clustered/организация таблицы — физическая близость строк, но терминология отличается.

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

Если все нужные запросу данные доступны из индекса, СУБД иногда избегает чтения таблицы. Это зависит от видимости версий и реализации.

CREATE INDEX idx_orders_state_created
    ON orders (state, created_at)
    INCLUDE (id, total);

INCLUDE поддерживается не везде. Широкий индекс занимает больше места и дороже обновляется.

Частичный и функциональный индекс

CREATE INDEX idx_orders_new_created
    ON orders (created_at)
    WHERE state = 'NEW';
 
CREATE INDEX idx_users_lower_email
    ON users (LOWER(email));

Это мощные, но диалект-зависимые инструменты. Предикат запроса должен соответствовать определению индекса.

Почему индекс не используют

• запрос возвращает большую долю таблицы;
• статистика устарела или неверно оценивает корреляцию;
• типы приводятся неудачно;
• над столбцом применена функция;
• ведущий столбец составного индекса не ограничен;
• последовательное чтение дешевле случайных обращений.

Цена индекса

• дополнительный I/O и CPU на INSERT/UPDATE/DELETE;
• блокировки и время создания;
• место на диске и в кэше;
• фрагментация/разрастание;
• риск дублирующих почти одинаковых индексов.

Связано: Селективность SQL, SQL оценка производительности, Оператор ORDER BY, Оператор LIKE.

Вопросы

Почему индекс не гарантирует ускорение? Оптимизатор выбирает самый дешевый по оценке план; чтение большой доли таблицы может быть быстрее scan.

Имеет ли значение порядок столбцов? Да: он определяет доступные префиксы, диапазон и порядок результата.

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

Почему не индексировать каждый столбец? Запись, хранение и обслуживание подорожают, а многие индексы не будут полезны.

Помогает ли индекс для %text%? Обычный B-tree обычно нет; нужен специализированный индекс или полнотекстовый поиск.