Stage 6. Индексы: почему один запрос 5 мс, а другой 5 секунд
Индекс в БД — это отдельная структура данных, которая помогает быстро найти нужные строки, не читая всю таблицу. Без индекса СУБД часто делает full scan: проходит по всем строкам и проверяет условие построчно. На таблице из 20 строк это нормально. На таблице с миллионами заказов, пользователей или логов такой подход может превратить простой запрос в медленную операцию.
Простая аналогия: индекс в книге. Вы не читаете книгу полностью, чтобы найти одну тему — открываете указатель, находите термин и переходите к нужной странице. Индекс базы данных работает похоже: он хранит значения индексируемых колонок вместе со ссылками на строки таблицы, где эти значения находятся.
| Ситуация | Без индекса | С индексом |
|---|---|---|
Поиск по email | Проверка многих строк | Быстрый точечный поиск |
JOIN по user_id | Большой объем чтения | Быстрое соединение |
Сортировка по created_at | Часто отдельная дорогая сортировка | Может использовать порядок индекса |
Что хранит индекс
Таблица хранит полные строки: все выбранные колонки, бизнес-данные, даты, статусы и другие значения. Индекс хранит только те значения, которые нужны для быстрого поиска, плюс ссылку на соответствующую строку. Например, индекс по users(email) содержит email-адреса в структуре, удобной для поиска. Когда запрос ищет один email, база сначала ищет значение в индексе, получает ссылку на строку и затем читает нужную строку из таблицы.
Индексы полезны для:
- частых фильтров в
WHERE; - соединений, где одна таблица ссылается на другую по ключу;
- сортировки через
ORDER BY; - диапазонных условий по датам или числам;
- правил уникальности, например
UNIQUE(email).
Индекс обычно хранится отдельно от данных таблицы. Он может кэшироваться в памяти, но все равно занимает место и требует поддержки. Когда строка добавляется, удаляется или обновляется, СУБД должна обновить все затронутые индексы. Это главный компромисс: индексы ускоряют чтение, но добавляют стоимость записи и хранения.
Как база использует индекс
Разберем запрос, который загружает последние заказы одного пользователя.
Пример полезного индекса:
CREATE INDEX idx_orders_user_id_created_at
ON orders (user_id, created_at DESC);
Кому поможет такой индекс:
- запросам “покажи последние заказы пользователя”;
- соединениям
orders.user_id = users.id; - выборкам с фильтром по
user_idи сортировкой по времени.
Пример запроса:
SELECT id, amount, status, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;
Ожидаемый эффект: БД быстро находит нужный диапазон по индексу, а не сканирует все заказы.
Без подходящего индекса база может просканировать все строки orders, оставить только строки с user_id = 42, отсортировать их по created_at и вернуть первые 20. С индексом (user_id, created_at DESC) база может сразу перейти к части индекса для user_id = 42 и читать самые новые строки первыми. Так можно убрать и полный просмотр таблицы, и дорогую сортировку.
Конкретное поведение зависит от СУБД, размера таблицы, распределения данных и плана выполнения. Поэтому важен EXPLAIN: он показывает, использует ли база индекс на самом деле или выбирает другой путь.
B-tree: первый тип индекса, который нужно понять
Самый распространенный тип индекса в реляционных базах — B-tree. Чтобы писать SQL, не нужно реализовывать B-деревья самостоятельно, но полезно понимать практическую модель. B-tree хранит ключи в отсортированном сбалансированном дереве. База начинает поиск ближе к корню, сравнивает искомое значение с ключами, переходит по нужной ветке и доходит до листовой части, где лежат ссылки на строки.
B-tree хорошо подходит для равенства, диапазонов и сортировки:
WHERE email = '[email protected]'
WHERE created_at >= '2026-05-01'
ORDER BY created_at DESC
Такие индексы часто используют для строк, чисел, дат, primary key, foreign key и составных индексов. B-tree остается сбалансированным при изменениях данных, поэтому поиск остается эффективным даже при росте таблицы. Цена — обслуживание структуры: вставки, удаления и обновления иногда требуют перестройки частей дерева.
Другие типы индексов на уровне новичка
Разные СУБД поддерживают разные типы индексов. Новичку не нужно запоминать все детали реализации; важнее понять, почему типов несколько.
| Тип индекса | Для чего подходит | Важное ограничение |
|---|---|---|
| B-tree | Обычные фильтры по равенству, диапазоны, join и сортировка | Не всегда лучший для текстового поиска и специальных данных |
| Hash index | Точное совпадение вроде id = 42 | Плохо подходит для диапазонов и сортировки |
| Full-text index | Поиск внутри больших текстовых полей | Нужны правила языка и токенизации |
| Bitmap index | Колонки с малым числом разных значений в аналитике | Часто плохой выбор для таблиц с частыми записями |
| GiST / пространственные индексы | Геометрия, координаты, диапазоны и специальные операторы | Более узкая и сложная область применения |
Hash index пропускает значение через хэш-функцию и попадает в определенную корзину. Это может быть очень быстро для точного поиска, но такой индекс не сохраняет порядок. Поэтому он не помогает для ORDER BY created_at или WHERE amount BETWEEN 100 AND 500.
Full-text index разбивает текст на токены, может убирать частые служебные слова, нормализовать формы слов и находить строки с релевантными терминами. Он нужен для поиска по статьям, описаниям товаров, комментариям и документам. Это не то же самое, что обычный индекс по целой колонке TEXT.
Bitmap index представляет значения через компактные битовые карты. Он может быть эффективен в аналитических нагрузках, особенно для колонок с низкой кардинальностью: status, gender, is_active. Для таблиц, куда постоянно идут вставки и обновления, это обычно плохой вариант по умолчанию, потому что поддержка bitmap-структур может быть дорогой.
GiST и похожие обобщенные индексы применяются для более сложных данных и операторов: географические координаты, диапазоны, специализированный поиск. Обычно с GiST не начинают, если задача — ускорить простой запрос по users.email или orders.created_at.
Селективность и порядок колонок
Селективность показывает, насколько сильно фильтр сужает результат. Условие email = '[email protected]' обычно очень селективное, потому что возвращает одну строку. Условие status = 'PAID' может быть менее селективным, если половина заказов оплачена. Чем селективнее фильтр, тем чаще индекс дает заметную пользу.
Порядок полей в составном индексе важен. Индекс (status, created_at) хорош для фильтра по status и сортировки по created_at:
WHERE status = 'PAID'
ORDER BY created_at DESC
Но для запроса только по created_at он может помочь слабо, потому что первая колонка в индексе — status. Хороший составной индекс начинается с колонок, которые соответствуют частому паттерну запроса. Если основной запрос — “последние оплаченные заказы пользователя”, то (user_id, status, created_at DESC) может быть полезнее, чем три отдельных индекса по одному столбцу.
Стоимость и частые ошибки
Индексы — не бесплатны. Каждый INSERT/UPDATE/DELETE должен обновлять и таблицу, и затронутые индексы. Слишком много индексов означает медленнее запись и больше места на диске.
Частые ошибки:
- Индексируют каждую колонку “на всякий случай”.
- Не учитывают порядок полей в составном индексе.
- Не проверяют план запроса через
EXPLAIN. - Добавляют индекс ради редкого запроса, но замедляют частые записи.
- Ожидают, что один тип индекса решит все виды поиска.
Как работать правильно:
- Сначала посмотреть медленные запросы.
- Понять, где фильтр и сортировка.
- Добавить индекс под конкретный паттерн.
- Проверить
EXPLAINдо/после. - Оставить только те индексы, что реально используются.
Мини-таблица принятия решений:
| Вопрос | Если “да” |
|---|---|
| Запрос очень частый? | Рассматривайте индекс |
| Фильтр селективный? | Индекс чаще полезен |
| Таблица большая? | Индекс обычно критичен |
| Записей много в секунду? | С осторожностью, избегать лишних индексов |
Итог: индекс — это точечный инструмент под реальную нагрузку. Хороший индекс ускоряет ключевые запросы, плохой или лишний только добавляет стоимость записи. Начинайте с запросов, которые приложение выполняет чаще всего, смотрите, по каким колонкам они фильтруют, соединяют и сортируют данные, затем создавайте минимальный полезный индекс под этот паттерн.
Практика перед следующим уроком
Попробуйте руками повторить примеры из статьи на маленьком наборе данных из 5-10 строк. Это важный шаг: когда вы сами запускаете SQL и видите конкретный результат таблицей, материал перестает быть абстрактным. Сначала выполните запрос без оптимизаций, затем внесите одно изменение и посмотрите, как меняется результат или план выполнения. Если что-то не сходится, зафиксируйте вопрос и проверьте: корректны ли фильтры, правильно ли выбраны поля, не потерялись ли строки из-за условий соединения, не появился ли NULL там, где вы его не ожидали.
Мини-проверка понимания:
- Могу объяснить тему урока одним простым предложением.
- Могу написать базовый SQL-пример без подсказки.
- Могу прочитать результат запроса и объяснить каждую колонку.
- Могу назвать минимум одну частую ошибку и как ее избежать.
После добавления индекса проверяйте и сторону записи. Если задержка INSERT или UPDATE выросла слишком сильно, пересмотрите набор индексов вместо того, чтобы оставлять каждый экспериментальный индекс.
Дополнительный сценарий: индекс под частый endpoint
Предположим, самый горячий endpoint в системе: “последние оплаченные заказы пользователя”. Если этот запрос вызывается сотни раз в минуту, индекс под такой паттерн окупается быстро.
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);
Перед добавлением такого индекса полезно проверить, что endpoint реально массовый, а фильтр по status действительно используется. После добавления сравните план и latency. Если ускорения нет, возможно, порядок полей нужно поменять.