индекс базы данных

Индекс базы данных — вспомогательная структура данных, создаваемая для ускорения поиска строк в таблице по значению одного или нескольких столбцов. Без индекса база данных вынуждена последовательно просматривать каждую строку таблицы (full table scan) при каждом запросе с условием WHERE. Индекс базы данных работает аналогично предметному указателю книги: вместо перелистывания всех страниц вы сразу открываете нужную.

Как работает B-Tree индекс

Наиболее распространённый тип индекса — B-Tree (сбалансированное дерево). Он хранит значения индексируемого столбца в отсортированном дереве: каждый узел содержит несколько ключей и ссылки на дочерние узлы. Поиск конкретного значения занимает O(log n) операций — для таблицы с миллиардом строк это около 30 сравнений вместо миллиарда.

B-Tree эффективен для: точного поиска (= 42), диапазонов (BETWEEN, >, <), сортировки (ORDER BY), и поиска по префиксу строк (LIKE 'abc%'). Не эффективен для поиска по суффиксу (LIKE '%abc') и для нечёткого поиска.

Типы индексов в современных СУБД

• B-Tree — стандартный индекс, подходящий для большинства задач. Создаётся по умолчанию командой CREATE INDEX.
• Hash — хранит хэши значений. Эффективен только для точного сравнения (=). Не поддерживает диапазоны и сортировку.
• GiST (Generalized Search Tree) — расширяемая структура для нестандартных типов данных: геометрические объекты, диапазоны, полнотекстовые векторы (tsvector).
• GIN (Generalized Inverted Index) — инвертированный индекс для многозначных данных: массивы, JSONB-поля, tsvector (полнотекстовый поиск). Быстро ищет, медленно обновляется.
• BRIN (Block Range Index) — хранит диапазоны значений по физическим блокам таблицы. Очень маленький размер, подходит для больших таблиц с коррелированными данными (временные ряды, лог-таблицы).
• Partial Index — индекс, созданный только для части строк по условию WHERE. Меньше размер, быстрее обновление.
• Expression Index — индекс по выражению, а не по столбцу: CREATE INDEX ON users (lower(email)).
• Covering Index — включает все столбцы, нужные запросу (INCLUDE), позволяя обойтись без обращения к основной таблице (index-only scan).

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

Составной (composite) индекс покрывает несколько столбцов: CREATE INDEX ON orders (user_id, created_at). Порядок столбцов критичен: индекс (user_id, created_at) эффективен для запросов по user_id или по user_id AND created_at, но не по одному created_at. Правило: сначала столбец с наибольшей селективностью или тот, по которому есть условие точного равенства.

Как планировщик запросов использует индексы

Оптимизатор запросов (query planner) анализирует запрос и выбирает план выполнения на основе статистики (числа строк, распределения значений, размера таблицы). Команда EXPLAIN ANALYZE показывает реальный план выполнения с временем каждого шага. Оптимизатор может отказаться от индекса в пользу full scan, если считает его более эффективным (например, если запрос затрагивает >10–20% строк таблицы).

Цена индекса: влияние на запись

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

Мониторинг и обслуживание индексов

В PostgreSQL представление pg_stat_user_indexes показывает статистику использования индексов: количество сканирований, кортежей. Неиспользуемые индексы (0 scans) стоит удалить. Команда REINDEX перестраивает раздутый индекс. ANALYZE обновляет статистику планировщика — важно выполнять после массовых вставок. Параметр fillfactor влияет на резервирование места в страницах индекса для HOT-обновлений.

Практические рекомендации

Индексируйте столбцы, по которым фильтруете (WHERE), сортируете (ORDER BY) и группируете (GROUP BY). Всегда индексируйте внешние ключи — без этого DELETE и UPDATE в родительской таблице сканирует дочернюю. Используйте EXPLAIN ANALYZE перед и после создания индекса для проверки эффекта. В PostgreSQL создавайте индексы с CONCURRENTLY в продакшне, чтобы не блокировать таблицу.