Медленный SELECT, который раньше отрабатывал за 20 миллисекунд, вдруг начинает виснуть на 12 секунд. База выросла с 50 тысяч строк до 5 миллионов, и каждый запрос превратился в лотерею. Знакомо?

Индексы, штука, о которой все слышали, но мало кто настраивает осознанно. Добавил пару штук, вроде ускорилось, и забыл. А через полгода INSERT тормозит сильнее, чем SELECT без индекса, потому что каждая вставка перестраивает пять ненужных B-деревьев.

Разберём, как индексы работают на самом деле, какие типы бывают и, главное, по каким правилам их строить, чтобы не наломать дров в production.

💡 Быстрый обзор:

• Разобраться в механике B-tree, hash и составных индексов, без этого выбрать правильный тип невозможно
• Освоить 7 ключевых практик: от индексирования внешних ключей до удаления неиспользуемых индексов
• Научиться читать EXPLAIN и отличать рабочий индекс от бесполезного

Что такое индекс базы данных

Индекс базы данных, это отдельная структура, которая хранит отсортированные значения одного или нескольких столбцов таблицы вместе с указателями на строки. Когда вы выполняете SELECT ... WHERE category_id = 42, сервер без индекса сканирует таблицу строка за строкой (full table scan). С индексом находит нужные записи за O(log n), как в телефонном справочнике.

Создаётся индекс через CREATE INDEX:

1
-- Обычный индекс
2
CREATE INDEX idx_category
3
ON products (category_id);
4
 
5
-- Уникальный индекс
6
CREATE UNIQUE INDEX idx_email
7
ON users (email);

Но плата за скорость чтения, замедление записи. Каждый INSERT, UPDATE и DELETE вынужден обновлять не только таблицу, но и все связанные индексы. Три индекса на таблице с миллионом строк, и массовая вставка проседает на порядок. Баланс между скоростью чтения и записи, центральный вопрос проектирования индексов.

Подробный разбор темы от Hussein Nasser: 497 тысяч подписчиков, инженерный подход без воды. На примере PostgreSQL показана внутренняя механика индексов, почему один CREATE INDEX ускоряет запрос в 100 раз, а другой не даёт ничего.

Типы индексов: какой когда применять

Выбор типа индекса определяет, насколько эффективно база обработает ваш запрос. Разные СУБД реализуют их по-своему, но принципы едины.

B-tree (сбалансированное дерево)

Стандартный тип в большинстве реляционных СУБД, PostgreSQL, MySQL, Oracle и SQL Server используют B-tree как индекс по умолчанию. Хранит ключи в отсортированном порядке, поддерживает операции сравнения, диапазоны (BETWEEN), префиксный поиск (LIKE 'prefix%') и сортировку, лучший выбор для подавляющего большинства случаев.

Hash-индекс

Работает только для точного сравнения =. Молниеносный на точечных запросах, но бесполезен для диапазонов и сортировки. В PostgreSQL hash-индексы стали production-ready с версии 10; в MySQL их нет в InnoDB (только в MEMORY).

Кластерный индекс

Определяет физический порядок строк на диске. В MySQL InnoDB первичный ключ всегда кластерный, строки хранятся в порядке PRIMARY KEY. В SQL Server, один кластерный индекс на таблицу. Правильный выбор кластерного ключа (монотонно возрастающий: BIGSERIAL, AUTO_INCREMENT или UNIQUEIDENTIFIER с NEWSEQUENTIALID()) даёт выигрыш на диапазонных запросах и предотвращает фрагментацию страниц.

Составной индекс

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

1
CREATE INDEX idx_order_date_status
2
ON orders (order_date, status);

Порядок столбцов важен: первым ставят столбец с наивысшей селективностью, используемый в WHERE. Принцип самого левого префикса: индекс (A, B, C) работает для WHERE A и WHERE A AND B, но не для WHERE B AND C.

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

Содержит все столбцы, нужные запросу, и для фильтрации, и для вывода. База берёт всё из индекса, не заходя в таблицу. В PostgreSQL это INCLUDE-столбцы, в MySQL InnoDB, неявное покрытие через кластерный индекс.

7 ключевых практик индексирования

1. Индексируйте столбцы из WHERE

Первое правило: каждый столбец, регулярно используемый в WHERE, JOIN ... ON и HAVING, должен быть проиндексирован. Именно эти операции выигрывают от индексов больше всего.

Перед созданием индекса проверьте селективность колонки. Если в столбце status всего три значения ('new', 'processing', 'done'), а строк, 2 миллиона, индекс по status почти бесполезен: планировщик выберет full table scan как более дешёвый. Индекс имеет смысл, когда количество уникальных значений достаточно велико относительно размера таблицы.

2. Индексируйте столбцы сортировки

ORDER BY без индекса, это filesort (MySQL) или explicit sort (PostgreSQL): сервер собирает все строки и сортирует в памяти (или на диске, если work_mem/sort_buffer_size малы). Индекс по тем же столбцам, что и ORDER BY, бесплатен, данные уже отсортированы в B-tree.

1
-- Без индекса по created_at: filesort на миллионах строк
2
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20;
3
 
4
-- Индекс решает проблему
5
CREATE INDEX idx_posts_created ON posts (created_at);

3. Индексируйте GROUP BY и агрегатные столбцы

Группировка без индекса требует полного сканирования и построения хеш-таблицы. Индекс по столбцам GROUP BY превращает операцию в потоковую агрегацию: строки уже сгруппированы в порядке ключа.

4. Индексируйте все внешние ключи

Неиндексированный внешний ключ, бомба замедленного действия. DELETE FROM users WHERE id = 5 при наличии FOREIGN KEY (user_id) REFERENCES users(id) в таблице orders без индекса по user_id, это full table scan orders на каждое удаление. Все популярные СУБД требуют индекса на внешний ключ или создают его неявно (MySQL InnoDB, автоматически, PostgreSQL, нет).

5. Индексируйте уникальные столбцы и первичные ключи

Первичный ключ индексируется автоматически (часто кластерным индексом). Явный UNIQUE INDEX защищает от дубликатов и одновременно ускоряет поиск. Любой столбец с бизнес-ограничением уникальности (например email, slug или external_id) должен иметь уникальный индекс, и для целостности, и для производительности.

6. Используйте кластерный индекс осознанно

Для больших таблиц (десятки миллионов строк) правильный кластерный ключ критичен. Хороший выбор, монотонно возрастающее значение: AUTO_INCREMENT, BIGSERIAL или UUID v7. Случайный UUID в качестве кластерного ключа вызывает фрагментацию страниц, каждая вставка попадает в случайное место B-tree, разбивая заполненные страницы на две половинчатые.

7. Удаляйте неиспользуемые индексы

Индекс, который не используется в запросах, чистый убыток. Он замедляет запись, занимает место на диске и в буферном пуле, сбивает с толку планировщик запросов. В PostgreSQL список неиспользуемых индексов даёт системная таблица pg_stat_user_indexes:

1
SELECT schemaname, relname, indexrelname, idx_scan
2
FROM pg_stat_user_indexes
3
WHERE idx_scan = 0
4
ORDER BY relname;

В MySQL аналогичную информацию предоставляет sys.schema_unused_indexes (начиная с версии 5.7). Запланируйте ежемесячный аудит и удаляйте индексы, которые не использовались ни разу за отчётный период.

Как проверить, работают ли ваши индексы

Создали индекс, проверьте, что он действительно применяется. Команда EXPLAIN (или EXPLAIN ANALYZE) показывает план выполнения запроса и фактическое использование индексов:

1
EXPLAIN ANALYZE
2
SELECT * FROM orders
3
WHERE customer_id = 12345
4
ORDER BY order_date DESC;

В выводе ищите строки Index Scan или Index Only Scan (PostgreSQL) / Using index (MySQL). Если видите Seq Scan (PostgreSQL) или Using where; Using filesort (MySQL), индекс не используется. Причины: низкая селективность, неподходящий тип индекса, несовпадение порядка столбцов в составном индексе, устаревшая статистика (ANALYZE table_name;).

Регулярно мониторьте метрики: pg_stat_user_indexes.idx_scan в PostgreSQL, sys.schema_index_statistics в MySQL. Индекс с нулём сканирований за месяц, кандидат на удаление.

⁉️🤔 Частые вопросы

Сколько индексов должно быть у таблицы?

Оптимально, от 2 до 6 индексов на активно используемую таблицу. Меньше двух, почти наверняка есть неоптимальные запросы. Больше шести, внимательно проверьте, все ли реально нужны: каждый лишний индекс тормозит запись. Для таблиц-справочников (редкая запись, частое чтение) больше индексов оправдано. Для высоконагруженных операционных таблиц (много INSERT/UPDATE) держите минимум.

Чем составной индекс лучше нескольких одинарных?

Составной индекс (A, B), это ОДНА структура. Сервер проходит по ней один раз. Три отдельных индекса (A), (B) и (C) при WHERE A=1 AND B=2 заставляют сервер либо выбрать один индекс (и фильтровать остаток), либо делать bitmap index scan (слияние битовых карт). Составной индекс почти всегда эффективнее, если порядок столбцов соответствует запросам.

Когда индекс не помогает, а мешает?

Три типовых случая. Первый: таблица маленькая (до пары тысяч строк), full table scan быстрее, чем чтение индекса плюс добор строк. Второй: индекс на столбце с низкой селективностью (is_deleted, status с тремя значениями). Третий: массовые вставки в ETL/импорте, индексы перестраиваются на каждом батче, дропайте их перед загрузкой и создавайте заново после.

Нужно ли индексировать столбцы для JOIN?

Обязательно. Каждый JOIN без индекса на связующем столбце внешней таблицы, это nested loop с полным сканированием. Для LEFT JOIN orders ON users.id = orders.user_id индекс на orders.user_id превращает nested loop в index lookup. Индексируйте столбцы, по которым идут соединения, всегда.

B-tree или hash: что выбрать для точного поиска?

Для = hash быстрее: поиск по хешу занимает константное время, тогда как B-tree идёт по дереву за логарифмическое число шагов. Но hash не поддерживает диапазоны, сортировку и UNIQUE. На практике B-tree покрывает подавляющее большинство сценариев; hash, нишевый инструмент для точечных lookup-ов по ключу в высоконагруженных системах (сессии, кеш). В PostgreSQL hash-индексы с версии 10 production-ready и занимают меньше места, чем B-tree.

Стоит ли индексировать «на всякий случай»?

Нет. Каждый индекс, это компромисс. Он ускоряет чтение ценой замедления записи и дополнительного дискового пространства. Индексируйте не «на всякий случай», а под конкретные запросы, которые реально выполняются в приложении. Профилируйте медленные запросы (pg_stat_statements, slow_query_log), добавляйте индексы точечно и проверяйте EXPLAIN до и после.

Главный итог прост: индексы, инструмент, а не самоцель. Один правильный составной индекс может заменить три одинарных и сэкономить гигабайты диска. А один неиспользуемый индекс на таблице с интенсивной записью, замедлить всё приложение.

Хотите копнуть глубже, начните с официального руководства по дизайну индексов SQL Server и документации PostgreSQL по типам индексов. А если встретили запрос, который не лечится индексами, возможно, проблема в самой модели данных.