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

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

Что вообще такое индекс и зачем он нужен?

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

В базе данных то же самое: без индекса система вынуждена сканировать всю таблицу построчно, чтобы найти нужные записи. Это называется полным сканированием таблицы (full table scan). С индексом СУБД сразу переходит к нужным строкам, минуя миллионы ненужных.

Как устроен индекс технически?

Чаще всего индексы строятся на основе структуры данных, которая называется B-дерево (B-tree). Это сбалансированное дерево, в котором данные отсортированы так, что найти любую запись можно за считаное число шагов, даже если строк миллионы. Именно B-деревья лежат в основе индексов в большинстве популярных систем — MySQL, PostgreSQL, Oracle и других.

Есть и другие типы структур: хеш-индексы для точного поиска по равенству, битовые индексы для колонок с небольшим числом уникальных значений, полнотекстовые индексы для поиска по тексту. Выбор типа зависит от того, какие запросы чаще всего выполняются к таблице.

Почему нельзя просто проиндексировать всё подряд?

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

• Индекс занимает дополнительное место на диске.
• Каждая вставка или обновление строки требует времени на пересчёт индексов.
• Слишком много индексов может замедлить работу больше, чем их отсутствие.

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

Какие столбцы стоит индексировать в первую очередь?

В первую очередь имеет смысл индексировать столбцы, которые часто используются в условиях поиска (WHERE), сортировке (ORDER BY) и соединении таблиц (JOIN). Классический пример — идентификаторы, по которым связываются таблицы, или поля, по которым пользователи фильтруют данные в интерфейсе (дата, статус, категория).

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

Что такое составной индекс и когда он нужен?

Составной (или композитный) индекс строится сразу по нескольким столбцам. Он полезен, когда запросы регулярно фильтруют данные сразу по нескольким условиям одновременно — например, по дате и статусу заказа.

Важный нюанс: порядок столбцов в составном индексе имеет значение. Индекс по столбцам (дата, статус) хорошо работает для запросов, где фильтрация начинается с даты, но может быть бесполезен, если запрос фильтрует только по статусу. Это похоже на телефонную книгу, отсортированную сначала по фамилии, а потом по имени: искать по фамилии удобно, а вот найти всех Иванов Иванов среди случайных фамилий — нет.

Как понять, что запросу не хватает индекса?

Практически во всех современных СУБД есть встроенный инструмент — план выполнения запроса (execution plan). Он показывает, как именно база данных будет искать нужные данные: воспользуется ли она индексом или пойдёт на полное сканирование таблицы.

Если в плане видно полное сканирование большой таблицы там, где ожидалась быстрая выборка по индексу, — это сигнал, что либо индекса нет, либо он не подходит под структуру запроса, либо оптимизатор СУБД решил, что индекс использовать невыгодно (такое тоже случается при определённых объёмах и распределении данных).

Может ли индекс перестать работать со временем?

Да, и это довольно распространённая проблема. По мере роста и изменения таблицы индексы могут фрагментироваться — данные внутри структуры индекса располагаются не оптимально, и поиск по нему замедляется. Для многих СУБД предусмотрены операции переиндексации или обслуживания индексов, которые стоит выполнять периодически на больших нагруженных таблицах.

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

С чего лучше начать оптимизацию через индексы?

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

Тем, кто не хочет погружаться в тонкости настройки СУБД самостоятельно, помогает готовая инфраструктура с профессиональным администрированием. В «Клауд АйСи» такие задачи, как настройка и обслуживание баз данных под конкретную нагрузку, берёт на себя команда сервиса, а бизнес получает готовое быстрое решение без необходимости держать в штате отдельного DBA.