Миф: индекс на каждое поле ускорит выборки

MAATRIX

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

Почему миф так живуч: индекс кажется бесплатным ускорителем

Миф держится на честном наблюдении: один конкретный SELECT с WHERE email = ... на таблице без индекса делает полный скан (Seq Scan) — читает все строки подряд, чтобы найти нужные. Добавили индекс — вместо полного скана база спускается по B-дереву за логарифмическое число шагов, и время ответа падает с секунд до долей миллисекунды на больших таблицах. Эффект настолько нагляден, что напрашивается вывод: «раз один индекс так помог, десять индексов помогут ещё больше».

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

Второй источник мифа — ORM и генераторы миграций, которые по умолчанию индексируют внешние ключи и часто предлагают проиндексировать вообще всё, что можно сравнивать. Разработчик соглашается не глядя, потому что «индекс же не может навредить» — и это единственная ложная предпосылка, из которой вырастает всё остальное.

Оборотная сторона: как индекс замедляет запись

Индекс — это отдельная структура данных (в большинстве СУБД — B-дерево), которая должна оставаться синхронизированной с таблицей в любой момент времени. Это значит: каждый INSERT, каждый UPDATE затронутого столбца и каждый DELETE обязаны обновить не только саму строку, но и запись в каждом индексе, который эту строку затрагивает.

Возьмём таблицу с пятью индексами. Один INSERT — это уже не одна операция записи, а минимум шесть: вставка строки в таблицу плюс вставка ключа в каждое из пяти B-деревьев, с возможной перебалансировкой страниц дерева, если она переполнилась. UPDATE, меняющий проиндексированное поле, устроен ещё дороже: старое значение нужно удалить из индекса, новое — вставить, и в PostgreSQL это ещё и напрямую связано с HOT-обновлениями (Heap-Only Tuples) — если вы обновляете столбец, участвующий хотя бы в одном индексе, HOT-оптимизация для этой строки не сработает, и обновление станет «полным», с записью новой версии строки во все индексы таблицы, а не только в изменённые.

-- пример: таблица с индексами на каждое поле
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    status TEXT,
    total NUMERIC,
    created_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    warehouse_id INT,
    notes TEXT
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_total ON orders(total);
CREATE INDEX idx_orders_created_at ON orders(created_at);
CREATE INDEX idx_orders_updated_at ON orders(updated_at);
CREATE INDEX idx_orders_warehouse_id ON orders(warehouse_id);
CREATE INDEX idx_orders_notes ON orders(notes);

Каждое изменение статуса заказа (частая операция в любом интернет-магазине) теперь трогает как минимум два индекса — по status и по updated_at, если это поле тоже обновляется автоматически триггером. При высокой частоте обновлений это ощутимо увеличивает время отклика на запись и создаёт дополнительную нагрузку на WAL (write-ahead log) — каждое изменение индекса тоже журналируется, а значит растёт объём записи на диск и, как следствие, трафик репликации на реплики.

Отдельно страдают массовые операции — COPY, bulk INSERT, миграции данных: база параллельно поддерживает семь B-деревьев вместо одного, и загрузка растягивается заметно дольше. Стандартная практика при массовой первоначальной загрузке — временно удалить неключевые индексы, залить данные, а потом создать индексы заново одной операцией CREATE INDEX, которая работает эффективнее, чем миллион point-insert в существующее дерево.

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

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

Проверить реальный вклад индексов в размер таблицы можно так:

SELECT
    relname AS table_name,
    pg_size_pretty(pg_table_size(oid)) AS table_size,
    pg_size_pretty(pg_indexes_size(oid)) AS indexes_size,
    pg_size_pretty(pg_total_relation_size(oid)) AS total_size
FROM pg_class
WHERE relkind = 'r' AND relname = 'orders';

Место на диске — не самая болезненная часть. Хуже то, что происходит с памятью. У PostgreSQL и MySQL есть буферный пул (shared_buffers и innodb_buffer_pool_size соответственно), который старается держать в оперативной памяти «горячие» страницы данных и индексов. Каждый лишний индекс — это дополнительные страницы, конкурирующие за то же ограниченное место в буферном пуле с действительно нужными данными и действительно используемыми индексами. О том, почему буферный пул вообще определяет производительность базы сильнее, чем скорость диска, подробно разобрано в статье «Буферный пул базы: почему он важнее диска» — неиспользуемые индексы напрямую вытесняют из этого пула то, что действительно читается на каждый запрос, увеличивая долю обращений к диску (cache miss) там, где раньше их не было.

На сервере с ограниченной памятью (например, VPS с 2–4 ГБ RAM под небольшой проект) эффект заметен особенно быстро: индексы нескольких таблиц в сумме перестают помещаться в буферный пул целиком, начинается вытеснение и рост числа обращений к диску на операциях, которые раньше отрабатывали из кеша.

Как планировщик решает, использовать ли индекс — и почему лишние только мешают

Ключевой момент, который миф упускает полностью: наличие индекса не гарантирует, что планировщик запросов вообще станет его использовать. Планировщик выбирает план на основе статистики (собранной ANALYZE) и стоимостной модели — если он оценивает, что выборка по индексу обойдётся дороже, чем последовательное чтение таблицы (например, когда условие WHERE возвращает большую долю строк), он выберет Seq Scan, а индекс просто останется мёртвым грузом, который вы всё равно оплачиваете при каждой записи.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'completed';

Если completed — статус 80% заказов, индекс по status почти гарантированно не будет использован для такого запроса: читать 80% таблицы через индекс с последующими случайными обращениями к куче (heap) дороже, чем просто пройти таблицу подряд. При этом сам индекс продолжает исправно замедлять каждую вставку и обновление заказа. Разбор именно такой ситуации — есть индекс, но планировщик его игнорирует — в статье «Когда индекс перестаёт помогать: точка Seq Scan».

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

Правильный подход: индексируем по реальным паттернам запросов

Единственный рабочий критерий — не «какие столбцы существуют», а «какие столбцы реально участвуют в WHERE, JOIN и ORDER BY в запросах, которые выполняются часто и с задержкой, которая заметна». Практический процесс:

1. Собрать реальные запросы приложения — из логов медленных запросов или через pg_stat_statements в PostgreSQL (EXTENSION, показывает частоту и суммарное время каждого уникального запроса).
2. Для самых частых и самых дорогих запросов посмотреть EXPLAIN ANALYZE и найти операции Seq Scan на больших таблицах — это кандидаты на индекс.
3. Индексировать именно те столбцы, что стоят в условиях фильтрации (WHERE), соединения (JOIN ... ON) и сортировки (ORDER BY), причём в порядке, который реально нужен запросу, а не по алфавиту столбцов таблицы.

-- находим самые тяжёлые запросы за последнее время
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Составные (многоколоночные) индексы — отдельный и часто недооценённый инструмент. Если запрос фильтрует по user_id и status, а затем сортирует по created_at, один составной индекс (user_id, status, created_at) обычно эффективнее, чем три отдельных индекса на каждый столбец — планировщику не нужно пересекать несколько отдельных индексов (bitmap AND), он сразу проходит по одному дереву в нужном порядке:

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);

-- этот запрос использует индекс целиком: и фильтр, и сортировку
SELECT * FROM orders
WHERE user_id = 42 AND status = 'completed'
ORDER BY created_at DESC
LIMIT 20;

Порядок колонок в составном индексе важен: столбцы для равенства (=) ставятся первыми, столбец для диапазона или сортировки — последним, потому что B-дерево эффективно использует префикс индекса слева направо. Индекс (user_id, status, created_at) бесполезен для запроса, фильтрующего только по status без user_id — префикс не совпадает, и придётся создавать отдельный индекс под этот паттерн, если он тоже частый. Это ещё одна причина, почему «индекс на каждое поле» не заменяет продуманную схему: один хорошо спроектированный составной индекс под три реальных паттерна запросов часто эффективнее пяти узкоспециализированных индексов на отдельные столбцы, вместе взятых.

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

Регулярный аудит: как найти и убрать неиспользуемые индексы

Индексы, полезные в момент создания, со временем протухают: меняются паттерны запросов, переписывается код, добавляются составные индексы, которые перекрывают старые узкие. Без регулярного аудита база копит мёртвый груз — индексы, которые никто не читает, но которые продолжают замедлять каждую запись. В PostgreSQL реальную статистику использования каждого индекса показывает системное представление pg_stat_user_indexes:

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 на сервере, который проработал под реальной нагрузкой достаточно долго (минимум несколько недель, чтобы захватить редкие, но регулярные сценарии вроде отчётов на конец месяца), — надёжный сигнал, что индекс не используется вообще. Перед удалением стоит исключить первичные и уникальные ключи (они часто нужны не для чтения, а для целостности данных) и убедиться, что счётчик не сбрасывался недавно перезапуском сервера или командой pg_stat_reset() — иначе статистика будет обманчиво пустой для всех индексов сразу.

В MySQL похожую задачу решает sys.schema_unused_indexes (в MySQL 8) или ручной анализ через performance_schema.table_io_waits_summary_by_index_usage:

SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'your_database'
ORDER BY object_name;

Удалять найденные индексы стоит не разом всей пачкой, а по одному, с интервалом, чтобы успеть заметить регрессию, если статистика оказалась неполной — редкий, но регулярный запрос (месячный отчёт, годовая выгрузка) может использовать индекс раз в квартал и не попасть в недельное окно наблюдения. Разумная периодичность самого аудита — раз в один-два месяца для активно развивающегося проекта.

-- безопасное удаление: не блокирует таблицу на запись при удалении
DROP INDEX CONCURRENTLY idx_orders_notes;

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

Частые вопросы