[Перевод] Индексы в базах данных: сколько индексов — перебор?07.05.2025 13:01

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

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

Для начала давайте рассмотрим простой эксперимент. Возьмем популярную базу данных Stack Overflow любого размера, уберем все индексы из таблицы Users и запустим удаление одной строки командой DELETE. Это позволит нам наглядно увидеть влияние отсутствия индексов на выполнение данной операции и измерить количество физических операций ввода-вывода с помощью команды SET STATISTICS IO ON:

1

2

3

4

SET STATISTICS IO ON;

GO

BEGIN TRAN

DELETE dbo.Users WHERE DisplayName = N’Brent Ozar';

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

Мы читаем это справа налево. Первое, что должен сделать SQL Server, — это сканировать всю таблицу Users, чтобы найти строку (или строки) с DisplayName = 'Brent Ozar', потому что у нас нет индекса на DisplayName. После поиска он удалит их из кластеризованного индекса.

В копии Stack Overflow за июнь 2018 года SQL Server должен прочитать 143 670 страниц по 8 КБ каждая, чтобы найти нужные ему строки:

Нам нужен индекс на DisplayName.

Мы хотим, чтобы наше удаление работало быстрее, и нам нужно быстро находить строки, где DisplayName = 'Brent Ozar'. Для этого давайте отменим удаление и создадим индекс:

1 2	ROLLBACK CREATE INDEX DisplayName ON dbo.Users (DisplayName);

Then try our delete again:

1 2	BEGIN TRAN DELETE dbo.Users WHERE DisplayName = N’Brent Ozar';

Now, the actual execution plan is simpler:

Рассмотрим пошагово, как SQL Server обрабатывает запрос справа налево: сначала открывается индекс по полю DisplayName, затем точно определяется нужная строка, и только потом осуществляется её удаление непосредственно из основного кластерного индекса. Важно отметить, что визуализация плана выполнения слегка упрощает картину. Если внимательно изучить оператор удаления в кластерном индексе, можно заметить интересную деталь: внизу всплывающего окна отображаются сразу два объекта, а не один, как могло бы показаться изначально.

Внизу говорится: «Объект: PK_Users_Id» (это кластерный индекс) и «DisplayName» (это наш новый некластерный индекс). Тем не менее, теперь объем работы значительно меньше, что подтверждается временем выполнения запроса и логическими чтениями:

Мы сократили количество логических чтений с 143670 до всего лишь 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);

Now, our delete«s actual execution plan looks the same, but…

Графический план скрывает секрет, и чтобы раскрыть его, нам нужно навести указатель мыши на оператор «Удаление кластерного индекса», который является злостной ложью:

Видите этот объект внизу? Так называемое «Удаление кластерного индекса» фактически удаляет строку в каждом некластерном индексе тоже. Это также означает, что мы выполняем больше логических операций чтения для нахождения строк:

Мы перешли от 12 логических чтений до 24. Теперь, большое ли дело 24 чтения по 8 КБ? Совсем нет! Вы должны чувствовать себя абсолютно комфортно, добавляя горсть индексов к большинству таблиц, когда вашим рабочим нагрузкам этих запросов требуются эти индексы.

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

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE INDEX Age ON dbo.Users (Age)