Когда речь идет об оптимизации производительности запросов SQL Server, индексы играют важную роль. Они могут значительно улучшить скорость операций SELECT, UPDATE и DELETE, позволяя движку базы данных быстро находить необходимые данные. Однако не все индексы созданы равными, и важно понимать, как они работают, чтобы извлечь максимальную выгоду из них.
В недавнем производственном инциденте, с которым я столкнулся, был создан покрывающий индекс для оптимизации производительности между запросом UPDATE и SELECT. Индекс содержал столбцы, используемые в запросе SELECT и в предложении WHERE, и изначально казалось, что он работает хорошо. Однако во время пиковой активности все еще возникали небольшие блокировки и многочисленные взаимоблокировки.
При дальнейшем исследовании стало ясно, что не все столбцы в покрывающем индексе использовались эффективно. План выполнения SQL Server показывал поиск по индексу, но наведение курсора на узел показывало, что эффективно использовался только первый столбец в индексе. Остальные столбцы рассматривались как не-SARGable, что приводило к сбору и фильтрации дополнительных строк.
Чтобы лучше понять это поведение, рассмотрим пример. У нас есть таблица с названием SalesOrderDetail, и мы хотим создать покрывающий индекс для столбцов ModifiedDate, CarrierTrackingNumber и ProductID. Мы можем сделать это, выполнив следующие SQL-запросы:
USE AdventureWorks2012;
CREATE NONCLUSTERED INDEX NCI_Sales_Demo
ON Sales.SalesOrderDetail(ModifiedDate, CarrierTrackingNumber, ProductID);
Теперь предположим, у нас есть запрос, который фильтрует данные на основе этих столбцов:
DECLARE @ModifiedDate DATETIME = '1 Aug 2007';
DECLARE @ProductID INT = 712;
DECLARE @Carrier NVARCHAR(50) = 'FF50-4EB1-9E';
SELECT ModifiedDate, CarrierTrackingNumber, ProductID
FROM Sales.SalesOrderDetail
WHERE ModifiedDate = @ModifiedDate
AND CarrierTrackingNumber = ISNULL(@Carrier, CarrierTrackingNumber)
AND ProductID = @ProductID;
На первый взгляд все кажется хорошо. План выполнения показывает поиск по индексу, и нет поиска ключей, так как индекс покрывает все необходимые столбцы. Однако, если мы рассмотрим детали поиска по индексу, мы увидим, что эффективно используется только столбец ModifiedDate. Остальные столбцы рассматриваются как не-SARGable, что приводит к фильтрации дополнительных строк.
Для решения этой проблемы существует несколько возможных решений:
1. Сделать все столбцы SARGable: Если возможно, измените запрос, чтобы удалить любые условия, которые мешают использованию Seek Predicate в поиске по индексу. В нашем примере, удаление функции ISNULL позволит использовать Seek Predicate для всех трех столбцов.
2. Изменить порядок столбцов в индексе: Если изменение запроса не является вариантом, рассмотрите возможность изменения порядка столбцов в индексе. В нашем примере, так как столбцы ProductID и ModifiedDate являются SARGable, имеет смысл сначала выполнить поиск по индексу по ModifiedDate и ProductID, а затем проверить CarrierTrackingNumber.
3. Использовать включенные столбцы: Другой подход – сделать не-SARGable столбец включенным столбцом в индексе. Таким образом, индекс все равно может быть эффективно использован для фильтрации, даже если не-SARGable столбец не является частью ключа индекса.
Важно отметить, что количество логических чтений может не всегда изменяться при реализации этих решений. Количество логических чтений зависит от количества страниц данных, которые необходимо получить, и в некоторых случаях может потребоваться чтение того же набора страниц данных. Однако ключевое отличие заключается в более эффективной фильтрации, что приводит к улучшению производительности запроса.
В заключение, при работе с составными индексами в SQL Server важно убедиться, что все столбцы в индексе являются SARGable. То, что в плане выполнения используется поиск по индексу, не обязательно означает, что все столбцы в индексе используются эффективно. Понимая поведение индексов и внося необходимые корректировки, вы можете оптимизировать производительность ваших запросов SQL Server.
Спасибо за чтение, и я надеюсь, что этот статья дала вам ценные идеи по настройке производительности SQL.