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

Коротко о главном

Без индекса запрос вида WHERE email = 'a@b.com' заставляет базу прочитать все строки таблицы — это последовательное сканирование (Seq Scan). Индекс — дополнительная структура, где значения одной или нескольких колонок хранятся отсортированными вместе с указателями на строки. База ищет по индексу, за несколько шагов находит нужные указатели и читает только эти строки.

Компромисс простой: быстрее чтение, медленнее запись и больше места на диске.

Как устроен B-tree индекс

Большинство реляционных баз (PostgreSQL, MySQL, SQL Server) по умолчанию используют B-дерево. Представьте телефонный справочник, разбитый на уровни:

• корневая страница говорит: «А–К налево, Л–Я направо»;
• промежуточные страницы сужают диапазон;
• листовые страницы хранят отсортированные значения и указатели на строки таблицы.

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

• = и IN;
• диапазоны: >, <, BETWEEN;
• поиск по префиксу: LIKE 'abc%' (но не LIKE '%abc');
• ORDER BY по индексируемым колонкам — часто без отдельной сортировки.

Создание индексов

-- одна колонка
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- уникальный: ещё и правило целостности
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- составной: несколько колонок
CREATE INDEX idx_orders_customer_date ON orders (customer_id, created_at);

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

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

Составной индекс отсортирован сначала по первой колонке, затем по второй внутри каждого значения первой и так далее — как справочник, отсортированный по фамилии, а потом по имени.

Для индекса (customer_id, created_at):

Правило: сначала колонки, которые сравниваются на равенство, затем колонка диапазона или сортировки. Индекс по (a, b) покрывает и запросы только по a, так что отдельный индекс по a обычно лишний.

Цена на запись

Каждый индекс нужно обновлять при вставке строки, изменении проиндексированной колонки и удалении. Больше индексов — это:

• медленнее INSERT, UPDATE и DELETE;
• больше места на диске и в памяти под кэш;
• дольше бэкапы и восстановление.

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

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

Попросите у базы план запроса. В PostgreSQL:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 5;

На что смотреть:

• Index Scan или Index Only Scan — индекс используется.
• Bitmap Index Scan — индекс используется, чтобы собрать много подходящих строк.
• Seq Scan — читается вся таблица.

Seq Scan не всегда проблема. На маленькой таблице или когда запрос возвращает большую долю строк, прочитать всё дешевле, и планировщик выбирает это сознательно. В MySQL используйте EXPLAIN и смотрите колонку key.

Почему индекс игнорируется

• К колонке применена функция: для WHERE LOWER(email) = ... нужен индекс по LOWER(email).
• Несовпадение типов, например текстовая колонка сравнивается с числом.
• Шаблон с ведущим символом: LIKE '%term'.
• Запрос не использует первую колонку составного индекса.
• Устарела статистика — её обновляет ANALYZE.

Практический чек-лист

1. Сначала найдите медленные запросы (slow query log, pg_stat_statements или APM).
2. Индексируйте колонки из WHERE, JOIN и ORDER BY этих запросов.
3. Индексируйте колонки внешних ключей, по которым идут соединения.
4. Сравните план до и после.
5. Периодически удаляйте неиспользуемые и дублирующие индексы.