Индексы в базах данных: как они ускоряют запросы
Огромная доля проблем вида «почему наша база данных тормозит» сводится к одному отсутствующему элементу — индексу. Это одна из самых полезных вещей, которые стоит понимать о базах данных, и она проще, чем кажется по большинству объяснений.
Аналогия с библиотекой
Представьте библиотеку без каталога и без какой-либо системы — каждая книга стоит там, где нашлось место на полке. Чтобы найти конкретную книгу, придётся обойти все ряды и проверить каждую. Так выглядит таблица без индекса: чтобы найти строку, база данных проверяет все строки по одной, пока не найдёт нужную. Это называется полным сканированием таблицы, и на маленькой таблице это нормально, а на таблице с миллионами строк — мучительно медленно.
Индекс — это и есть каталог. Отдельная упорядоченная структура, которая сообщает: «строки, где этот столбец равен этому значению, находятся вот здесь», — и база данных может сразу перейти туда, не перебирая всё подряд.
Как это устроено внутри
Большинство баз данных реализуют индексы с помощью структуры данных под названием B-дерево — сбалансированного дерева, которое хранит значения упорядоченно и позволяет сужать поиск, примерно вдвое отсекая оставшиеся варианты на каждом шаге, — похоже на то, как вы ищете слово в бумажном словаре. Вместо того чтобы проверять миллион строк по одной, база может проверить десяток узлов дерева и сразу попасть на ответ.
Поэтому правильный индекс может превратить запрос, который выполнялся несколько секунд, в запрос на несколько миллисекунд: вы не заставляете базу работать усерднее, а даёте ей гораздо более короткий путь к тому же ответу.
Что стоит индексировать
- Столбцы из условий
WHERE— всё, по чему вы часто фильтруете, — сильные кандидаты на индекс. - Внешние ключи — столбцы, ссылающиеся на другую таблицу, ведь на них во многом держатся объединения.
- Столбцы из
ORDER BY— индекс, который уже хранит данные в нужном порядке, позволяет полностью пропустить сортировку. - Столбцы в условиях
JOIN— объединение больших таблиц без индексов по столбцам связи — одна из самых частых причин медленных запросов.
Компромисс, о котором молчат, пока он не ударит
Индексы не бесплатны. Каждый индекс приходится обновлять при каждой вставке, изменении или удалении строки, а значит, с ростом числа индексов операции записи замедляются. Кроме того, они занимают ощутимое место на диске, что важно при больших объёмах. Ошибка, которой стоит избегать, — не абстрактные «слишком мало» или «слишком много индексов», а индексы по столбцам, к которым почти никогда не обращаются: они замедляют каждую запись ради выгоды, которую вы так и не получите.
Хорошее практическое правило: индексируйте столбцы, по которым ваши реальные запросы фильтруют, объединяют и сортируют данные (загляните в журнал медленных запросов, если он есть), и не поддавайтесь соблазну индексировать всё «на всякий случай».
Определить, какие индексы действительно нужны реальной продакшен-базе, а какие незаметно отнимают ресурсы при каждой записи, — обычная часть нашей работы над инфраструктурой. Нередко это самое эффективное изменение, доступное системе, которая начала подтормаживать под реальным трафиком.