Как работает database indexing и почему «индекс есть» ≠ «запрос быстрый»

Практически каждый разработчик знает, что индексы ускоряют работу базы данных. Поэтому при медленных запросах первым делом часто советуют: «Добавь индекс».

Однако на практике наличие индекса вовсе не гарантирует высокую производительность. Бывает, что индекс существует, но запрос по-прежнему выполняется секунды или даже десятки секунд.

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

Что такое индекс

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

Представьте книгу с алфавитным указателем.

Если нужно найти главу про HTTP, вы открываете оглавление, находите номер страницы и сразу переходите к нужному месту.

Без оглавления пришлось бы листать книгу страницу за страницей.

Индекс в базе данных работает примерно так же.

Вместо последовательного чтения миллионов строк сервер сначала обращается к индексу, а уже затем получает нужные записи.

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

Большинство современных СУБД используют B-Tree.

Упрощённо его можно представить как дерево, в котором значения отсортированы по порядку.

Например:

A
├── B
├── D
├── G
└── M

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

При этом сами данные обычно лежат отдельно, а индекс содержит ссылки на соответствующие строки.

Когда индекс действительно помогает

Индекс особенно эффективен, если запрос выбирает небольшую часть таблицы.

Например:

SELECT *
FROM users
WHERE email = 'user@example.com';

Если поле email индексировано, база быстро найдёт нужную запись.

То же относится к:

• поиску по ID;
• фильтрации по уникальным значениям;
• диапазонам дат;
• сортировке по индексированному полю.

В таких случаях разница может составлять тысячи раз.

Почему индекс могут не использовать

Самый распространённый миф звучит так:

«На колонке есть индекс — значит запрос обязательно станет быстрее.»

Это неверно.

Оптимизатор всегда оценивает стоимость выполнения разных вариантов и выбирает наиболее выгодный.

Иногда полный просмотр таблицы оказывается быстрее обращения к индексу.

Например:

SELECT *
FROM users;

Индекс здесь бесполезен.

Если всё равно нужно прочитать каждую строку, дополнительное обращение к индексу только увеличит количество операций.

Низкая селективность

Предположим, есть поле:

status

Возможные значения:

• active
• inactive

Если 98% пользователей имеют статус active, запрос

WHERE status = 'active'

вернёт почти всю таблицу.

Использовать индекс здесь невыгодно.

Проще один раз прочитать таблицу целиком.

Поэтому индексы лучше работают для полей с большим количеством уникальных значений.

Функции могут отключить использование индекса

Ещё одна частая ошибка — применять функцию к индексируемому полю.

Например:

WHERE LOWER(email) = 'user@example.com'

Хотя индекс на email существует, база уже не может использовать его напрямую.

Такая же проблема возникает при использовании многих арифметических операций, преобразований типов и некоторых выражений.

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

Не каждый индекс подходит запросу

Пусть существует составной индекс:

(last_name, first_name)

Запрос

WHERE last_name = 'Ivanov'

сможет использовать индекс.

А вот

WHERE first_name = 'Ivan'

скорее всего нет.

Порядок полей в составном индексе имеет большое значение.

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

Индексы тоже имеют цену

Каждый индекс занимает место на диске.

Кроме того, при каждой операции:

• INSERT;
• UPDATE;
• DELETE;

индексы приходится обновлять.

Если создать слишком много индексов, чтение действительно может ускориться, но запись станет заметно медленнее.

Поэтому большое количество индексов — далеко не всегда преимущество.

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

Не стоит гадать.

Практически каждая современная СУБД умеет показывать план выполнения запроса.

Для этого используется команда:

EXPLAIN

или

EXPLAIN ANALYZE

Она позволяет увидеть:

• используется ли индекс;
• выполняется ли полный просмотр таблицы;
• сколько строк читает база;
• какие операции занимают больше всего времени.

Именно с анализа плана выполнения обычно начинается оптимизация медленных запросов.

Почему запрос всё равно может быть медленным

Даже если индекс используется, это ещё не означает высокую скорость.

Причин может быть много:

• возвращается слишком большое количество строк;
• выполняется сложный JOIN;
• данные читаются с медленного диска;
• отсутствуют подходящие составные индексы;
• оптимизатор выбрал неудачный план;
• сервер испытывает высокую нагрузку.

Поэтому индексы — лишь один из факторов производительности базы данных.

Типичные ошибки

При работе с индексами разработчики чаще всего допускают одни и те же ошибки:

• индексируют практически каждое поле;
• не проверяют план выполнения запроса;
• создают дублирующие индексы;
• используют функции в условиях поиска;
• забывают про порядок колонок в составных индексах;
• считают, что наличие индекса автоматически решает проблему производительности.

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

Итоги

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

Сам факт существования индекса не гарантирует быстрый запрос. Оптимизатор может отказаться от него, если полный просмотр таблицы окажется дешевле, если индекс плохо подходит под условие поиска или если запросу всё равно приходится читать большую часть данных.

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