Самая распространённая ошибка индексирования в SQL Server (и как её исправить)

Автор: Luca Biondi, The Most Common SQL Server Indexing Mistake (And How to Fix It)

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

Настоящая проблема: слишком много индексов

Во многих базах данных можно найти таблицы с удивительным количеством индексов.

• Иногда десять.
• Иногда двадцать.
• Иногда даже больше.

Обычно это происходит потому, что новые индексы добавляются со временем, когда какой-либо запрос становится медленным. Но старые или избыточные индексы удаляются очень редко. Со временем это создаёт то, что можно назвать «джунглями индексов».

Перекрывающиеся индексы

Одна из самых распространённых ошибок индексирования — создание перекрывающихся индексов.

Например:

CREATE INDEX IX_Orders_CustomerID
ON Orders(CustomerID)

CREATE INDEX IX_Orders_CustomerID_OrderDate
ON Orders(CustomerID, OrderDate)

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

Накладные расходы на запись

Индексы улучшают производительность чтения. Но они также вносят накладные расходы на операции записи. При каждой вставке, обновлении или удалении строки SQL Server должен обновлять все связанные индексы. Если таблица имеет много индексов, операции записи становятся значительно медленнее. Это особенно важно для OLTP-систем с частыми изменениями данных. Слишком много индексов может фактически снизить общую производительность системы.

Неиспользуемые индексы

Ещё одна частая проблема — индексы, которые никогда не используются. Эти индексы потребляют дисковое пространство и замедляют операции записи, не улучшая производительность запросов. Во многих производственных базах данных можно найти индексы, которые не использовались месяцами. Удаление этих индексов иногда может дать немедленное улучшение производительности.

Использование динамических административных представлений (DMV) о недостающих индексах

SQL Server предоставляет динамические административные представления, которые предлагают потенциально полезные индексы.

Например:

• sys.dm_db_missing_index_details

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

Лучший подход

Лучшая стратегия индексирования включает:

• регулярный обзор существующих индексов
• удаление избыточных или неиспользуемых индексов
• проектирование индексов на основе реальных шаблонов запросов
• предпочтение покрывающих индексов (covering indexes) там, где это уместно

Цель — не максимизировать количество индексов, а максимизировать их эффективность.