Оптимизация запросов: выражения в предложении WHERE, не допускающие поиска по индексу

Автор: Paul Randal, Adventures in query tuning: non-seekable WHERE clause expressions

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

Я подготовлю тестовый пример, чтобы показать, что я имею в виду.

Сначала создадим пример таблицы для отслеживания продаж и заполним её (на моём ноутбуке это заняло 3 минуты 45 секунд):

-- Создание примера таблицы
CREATE TABLE [BigTableLotsOfColumns] (
    [SalesID] BIGINT IDENTITY,
    [SalesDate] DATETIME,
    [Descr] VARCHAR (100),
    [CustomerID] INT,
    [ProductID] INT,
    [ModifyDate] DATETIME DEFAULT NULL,
    [PayDate] DATETIME DEFAULT NULL,
    [Quantity] INT,
    [Price] DECIMAL (6,2),
    [Discount] INT DEFAULT NULL,
    [WarehouseID] INT,
    [PickerID] INT,
    [ShipperID] INT);
GO
 
-- Заполнение таблицы
SET NOCOUNT ON;
 
DECLARE @Date DATETIME;
DECLARE @Loop INT;
 
SET @Loop = 1;
SET @Date = '01/01/2010';
 
-- Вставка 1500 продаж в день
WHILE @Loop < 547600
BEGIN
    INSERT INTO [BigTableLotsOfColumns] (
        [SalesDate], [Descr], [CustomerID], [ProductID], [Quantity],
        [Price], [WarehouseID], [PickerID], [ShipperID])
     VALUES (
        @Date,
        'A nice order from someone',
        CONVERT (INT, (RAND () * 1000)),
        CONVERT (INT, (RAND () * 1000)),
        CONVERT (INT, (RAND () * 10)),
        ROUND (RAND () * 1000, 2),
        1,
        CONVERT (INT, (RAND () * 4)),
        CONVERT (INT, (RAND () * 4)));
 
    IF @Loop % 1500 = 0 SET @Date = @Date + 1;
 
    SET @Loop = @Loop + 1;
END
GO
 
-- И кластерный индекс
CREATE CLUSTERED INDEX [IX_BigTableLotsOfColumns_Clustered] ON [BigTableLotsOfColumns] ([SalesID]);
GO

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

CREATE PROCEDURE [TotalSalesForDate] (@TargetDate DATETIME)
AS
    SELECT
        SUM ([Quantity] * [Price])
    FROM
        [BigTableLotsOfColumns]
    WHERE DATEDIFF (DAY, [SalesDate], @TargetDate) = 0
    OPTION (MAXDOP 1);
GO

Идея в том, что хранимая процедура должна выбрать все продажи за определённую дату, независимо от времени суток, когда произошла продажа. Я добавил OPTION (MAXDOP 1), чтобы имитировать систему, настроенную для OLTP-запросов (а также чтобы планы запросов лучше смотрелись в моём блоге :-).

Если я выполню хранимую процедуру, скажем, за 26 декабря 2010 года, это будет следующий запрос:

EXEC TotalSalesForDate '12/26/2010';
GO

И использует следующее время ЦП и ввод-вывод (вы можете получить эти данные, используя SET STATISTICS IO ON и SET STATISTICS TIME ON, но будьте осторожны — для больших процедур это может генерировать много вывода):

Table 'BigTableLotsOfColumns'. Scan count 1, logical reads 7229, physical reads 0, ...
 
SQL Server Execution Times:
CPU time = 125 ms,  elapsed time = 186 ms.

Я выполнил процедуру дважды: первый раз, чтобы она скомпилировалась, а второй — чтобы исключить время компиляции.

Совершенно очевидно, что я могу ускорить это, создав покрывающий некластерный индекс, и оптимизатор запросов подсказывает мне это с помощью предложения о пропущенном индексе. Я создаю покрывающий некластерный индекс:

CREATE NONCLUSTERED INDEX [IX_BigTableLotsOfColumns_Date] ON
    [BigTableLotsOfColumns] ([SalesDate]) INCLUDE ([Quantity], [Price]);

Теперь, когда я выполняю процедуру, я получаю план запроса получше и следующие показатели ЦП и ввода-вывода:

Table 'BigTableLotsOfColumns'. Scan count 1, logical reads 2113, physical reads 0, ...
 
SQL Server Execution Times:
CPU time = 78 ms,  elapsed time = 115 ms.

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

Причина в том, что функция DATEDIFF в процедуре заставляет вычислять значение каждого столбца SalesDate, отсюда и просмотр. Если я знаю, что процедура всегда вызывается с датой без временной части, я могу перестроить логику.

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

CREATE PROCEDURE [TotalSalesForDate] (@TargetDate DATETIME)
AS
    SELECT
        SUM ([Quantity] * [Price])
    FROM
        [BigTableLotsOfColumns]
    WHERE [SalesDate] >= @TargetDate AND [SalesDate] < DATEADD (DAY, 1, @TargetDate)
        OPTION (MAXDOP 1);
GO

И когда я выполняю его, я получаю наилучший план и следующие показатели ЦП и ввода-вывода:

Table 'BigTableLotsOfColumns'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 0, ...
 
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 64 ms.

Вау! В 200 раз меньше логических операций ввода-вывода и даже недостаточно времени ЦП, чтобы зарегистрировать 1 мс.

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