Индексы в базах данных: сколько индексов — перебор?

Вопрос о количестве индексов на таблице часто становится предметом жарких обсуждений среди разработчиков и администраторов баз данных. Одни считают: чем больше индексов — тем лучше производительность. Другие предупреждают: избыточная индексация может замедлить запись и увеличить нагрузку.
Так где же золотая середина?

Далее предлагаем вашему вниманию перевод оригинальной статьи “How Many Indexes Is Too Many?”, который подготовила специалист «Автомакона». В статье рассматривается этот вопрос с практической стороны и даются полезные рекомендации.

Начнем с простого эксперимента.
Возьмём базу данных Stack Overflow (любого размера), удалим все индексы из таблицы Users и попытаемся удалить одну строку. Это поможет нам понять, насколько важно наличие нужных индексов и как сильно они влияют на производительность.

Выполним следующую команду, предварительно включив статистику ввода-вывода:

SET STATISTICS IO ON;

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. Даже если вы не аналитик — результат явно впечатляющий.

Отлично! Индекс помог. Может, добавим ещё?

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

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. Звучит пугающе? На самом деле нет. Несколько дополнительных индексов — это нормально, особенно если они действительно нужны для ваших запросов.

Однако, чем больше индексов мы добавляем…

Давайте добавим еще несколько индексов, каждый из которых содержит несколько включенных столбцов:

CREATE INDEX Age ON dbo.Users(Age)