Индексирование

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

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

Как это выглядит

-- SQL
CREATE INDEX idx_orders_client ON orders (client_id);

EXPLAIN SELECT * FROM orders WHERE client_id = 42;

Первая команда создает индекс по полю «клиент», вторая показывает план выполнения запроса. До создания индекса план покажет полный просмотр таблицы (Seq Scan), после — поиск по индексу (Index Scan). Команда EXPLAIN — главный инструмент, чтобы понять, почему запрос медленный.

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

• B-дерево — основной тип. Хранит значения упорядоченными, поэтому подходит для поиска по точному значению, диапазону («заказы за март») и сортировки. Устройство таких деревьев описано в термине «Структуры данных».
• Хеш-индекс — только для точного совпадения.
• GIN — для поиска внутри составных значений: слов в тексте, ключей в JSON, элементов массива.
• GiST и другие — для геоданных, диапазонов и близости. Для поиска похожих векторов используют специальные индексы векторных баз данных.

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

Индекс можно построить по нескольким полям, например (client_id, created_at). Он ускорит поиск «заказы клиента 42 за март», а также просто «заказы клиента 42», но почти не поможет искать «все заказы за март»: работает правило левого префикса, как в телефонном справочнике, отсортированном по фамилии, а потом по имени.

Цена индексов

• Медленнее запись. Каждая вставка и изменение обновляет все индексы таблицы.
• Место на диске — индексы бывают сравнимы по размеру с самой таблицей.
• Неиспользуемые индексы только тратят ресурсы, поэтому их находят по статистике и удаляют.

На практике

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