Вопрос о количестве индексов на таблице часто становится предметом жарких обсуждений среди разработчиков и администраторов баз данных. Одни считают: чем больше индексов — тем лучше производительность. Другие предупреждают: избыточная индексация может замедлить запись и увеличить нагрузку.
Так где же золотая середина?
Далее предлагаем вашему вниманию перевод оригинальной статьи “How Many Indexes Is Too Many?”, который подготовила специалист «Автомакона». В статье рассматривается этот вопрос с практической стороны и даются полезные рекомендации.
Начнем с простого эксперимента.
Возьмём базу данных Stack Overflow (любого размера), удалим все индексы из таблицы Users и попытаемся удалить одну строку. Это поможет нам понять, насколько важно наличие нужных индексов и как сильно они влияют на производительность.
Выполним следующую команду, предварительно включив статистику ввода-вывода:
1 2 3 4 | SET STATISTICS IO ON; GO BEGIN TRAN DELETE dbo.Users WHERE DisplayName = N'Brent Ozar'; |
Удаление происходит внутри транзакции, чтобы мы могли в любой момент откатить изменения и повторить операцию.
Этот запрос удаляет пользователя с именем 'Brent Ozar'. Поскольку у нас нет индекса на столбец DisplayName, SQL Server вынужден просканировать всю таблицу, что приводит к значительным затратам ресурсов.
В частности, в копии базы данных Stack Overflow за июнь 2018 года SQL Server читает 143 670 страниц по 8 КБ каждая, чтобы найти нужную строку.
Нам нужен индекс по DisplayName.
Если мы хотим, чтобы операция удаления выполнялась быстрее, нам нужно быстро находить строки, где DisplayName = 'Brent Ozar'. Для этого отменим предыдущее удаление и создадим индекс:
1 2 | ROLLBACK CREATE INDEX DisplayName ON dbo.Users(DisplayName); |
Теперь повторим операцию удаления:
1 2 | BEGIN TRAN DELETE dbo.Users WHERE DisplayName = N'Brent Ozar'; |
Теперь, фактический план выполнения выглядит проще:
SQL Server начинает с индекса по DisplayName, моментально находит нужную строку, а затем удаляет её уже из кластерного индекса. Хотя визуально план может казаться простым, если навести курсор на оператор Clustered Index Delete, можно заметить, что фактически участвуют два объекта — PK_Users_Id (кластерный индекс) и DisplayName (некластерный индекс).
Если заглянуть в нижнюю часть всплывающей подсказки над оператором удаления, можно заметить, что SQL Server работает сразу с двумя объектами: это кластерный индекс PK_Users_Id и наш новый некластерный индекс DisplayName. Несмотря на это, объём работы теперь существенно меньше — и это хорошо видно по времени выполнения запроса и количеству логических чтений:
Объём работы значительно снижается: теперь вместо 143 670 логических чтений — всего 12. Даже если вы не аналитик — результат явно впечатляющий.
Отлично! Индекс помог. Может, добавим ещё?
Когда ко мне приходят с проблемами производительности, чаще всего всё сводится к одной из двух крайностей: либо люди вообще не знают, как работают хорошие некластерные индексы, либо увлеклись ими настолько, что налепили их везде, где только можно. Давайте добавим ещё несколько и снова попробуем удалить строку:
1 2 3 4 | CREATE INDEX Location ON dbo.Users(Location); CREATE INDEX LastAccessDate ON dbo.Users(LastAccessDate); CREATE INDEX Reputation ON dbo.Users(Reputation); CREATE INDEX WebsiteUrl ON dbo.Users(WebsiteUrl); |
На первый взгляд, план выполнения не изменился, но это обман.
Графический план скрывает важную деталь, и чтобы её увидеть, нужно навести курсор на оператор "Clustered Index Delete":
Обрати внимание на список объектов внизу всплывающего окна — несмотря на название «Clustered Index Delete», на самом деле SQL Server удаляет строку не только из кластерного индекса, но и из каждого некластерного. Это значит, что для выполнения операции требуется больше логических чтений — ведь нужно пройтись по всем индексам, где эта строка тоже присутствует:
Количество чтений выросло с 12 до 24. Звучит пугающе? На самом деле нет. Несколько дополнительных индексов — это нормально, особенно если они действительно нужны для ваших запросов.
Однако, чем больше индексов мы добавляем…
Давайте добавим еще несколько индексов, каждый из которых содержит несколько включенных столбцов:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE INDEX Age ON dbo.Users(Age) INCLUDE (DisplayName, Location, Reputation);
CREATE INDEX CreationDate ON dbo.Users(CreationDate) INCLUDE (DisplayName, Location, Reputation);
CREATE INDEX DownVotes ON dbo.Users(DownVotes) INCLUDE (DisplayName, Location, Reputation);
CREATE INDEX UpVotes ON dbo.Users(UpVotes) INCLUDE (DisplayName, Location, Reputation);
CREATE INDEX EmailHash ON dbo.Users(EmailHash) INCLUDE (DisplayName, Location, Reputation); |
И снова выполняем удаление. План остался прежним...
Но логических чтений стало ещё больше:
Это всё ещё не катастрофа — и в большинстве случаев ваша нагрузка и оборудование вполне справятся с 5, 10, а может, даже 15 или 20 индексами. Главное помнить, что это всё равно намного лучше, чем те 143 670 логических чтений, с которых мы начинали!
Так сколько индексов - это слишком много?
Нет «магического» числа индексов. Всё зависит от таких факторов, как:
Рабочая нагрузка в основном на чтение
Нет проблем с транзакциями или блокировками
Очень быстрые железо и запросы
Пользователи не так чувствительны к скорости вставок/обновлений/удалений
Вместо того чтобы искать конкретное число индексов, обратите внимание на признаки, что у вас их слишком много для вашей нагрузки и оборудования:
У ваших коллег возникают жалобы на замедление операций вставки, обновления или удаления данных.
Программы мониторинга сигнализируют о проблемах взаимоблокировки, либо подобные инциденты замечают сами пользователи.
Уже пробовали настроить уровни изоляции транзакций (RCSI или SI), но проблема осталась нерешённой.
При появлении перечисленных признаков настало время прибегнуть к бесплатному инструменту с открытым исходным кодом sp_BlitzIndex. Запустив его без аргументов прямо в нужной базе данных, вы получите чёткий отчёт о возможных проблемных местах:
Избыточных индексах-двойниках или близнецах.
Полностью неиспользуемых индексах, которые никак не задействуются системой.
Индексах с огромным объёмом данных, но редкими обращениями к ним.
Оптимизация индексов является ключевым аспектом эффективной работы любой базы данных. Как показало наше исследование, правильное использование индексов способно значительно повысить производительность приложения, сокращая время обработки запросов и снижая нагрузку на систему.
Однако важно помнить, что создание большого количества индексов также имеет свою цену: оно увеличивает накладные расходы на обслуживание, замедляет операции вставки и обновления данных, а также повышает вероятность возникновения конфликтов блокировки.
Следовательно, баланс между производительностью и поддерживаемостью — это задача, требующая тщательного анализа рабочих нагрузок, структуры данных и особенностей используемого оборудования. Регулярное тестирование и мониторинг позволяют выявить излишне созданные или неэффективные индексы, а инструменты вроде sp_BlitzIndex помогают упростить этот процесс.
Помните: правильный подбор индексов — залог стабильной и быстрой работы вашей базы данных.