IN или UNION ALL?

Пересказ статьи Bert Wagner. IN vs UNION ALL

Когда вам необходимо отфильтровать результаты запроса по множеству значений, вы, вероятно, используете оператор IN() или множество предикатов разделенных OR:

WHERE Col1 IN ('A','B','C')

или

WHERE Col1 = 'A' OR Col1 = 'B' OR Col1 = 'C'

В то время как SQL Server строит один и тот же план для каждого из вариантов, имеется другой способ, который вы можете попробовать и который при определенных условиях может улучшить производительность: UNION ALL.

Я буду использовать в примерах дамп данных StackOverflow 2014.

Lookups и Scans

Отметим, что мы имеем следующий индекс на нашей таблице dbo.Badges:

CREATE NONCLUSTERED INDEX [IX_Badges] ON [dbo].[Badges] ([Name]) INCLUDE ([UserId]);

Теперь давайте выполним эти два отдельных зароса:

/* Запрос 1 */
SELECT 
    Name, UserId, DATE 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Benefactor'
OPTION(MAXDOP 1)
/* Запрос 2 */
SELECT 
    Name, UserId, DATE 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Research Assistant'
OPTION(MAXDOP 1)

Замечание. Я использую здесь хинт MAXDOP 1, чтобы исключить разницу в производительности, связанную с параллелизмом.

Некластеризованный индекс не покрывает эти запросы — хотя SQL Server может выполнять поиск в индексе по предикату Name в предложении WHERE, он не может получить все столбцы в предложении SELECT только из одного лишь индекса. Это ставит SQL Server перед выбором:

  1. Сканировать целиком кластеризованный индекс, чтобы извлечь требуемые столбцы из полученных строк?
  2. Искать подходящие записи в некластеризованном индексе, а затем выполнять поиск ключа для получения оставшихся данных?

Итак, какой выбор сделает SQL Server?

Для запроса 1 SQL Server считает, что чтение всего кластеризованного индекса и возвращение только тех строк, для которых Name = ‘Benefactor’, является лучшим выбором.

Однако SQL Server применяет другой подход для запроса 2, используя непокрывающий некластеризованный индекс, с последующим применением поиска ключа (Key Lookup) для поиска значений Date в кластеризованном индексе.

Причиной выбора двух различных планов является то, что SQL Server считает, что будет быстрей вернуть небольшое число записей через Seek + Key Lookup (“Research Assistant”, 127 строк), и быстрей вернуть большое число записей посредством Scan (“Benefactor”, 17935 строк).

Объединение запросов с IN

А какой план генерирует SQL Server при объединении двух запросов в один?

SELECT 
    Name, UserId, DATE 
FROM 
    dbo.Badges 
WHERE 
    Name IN ('Benefactor','Research Assistant')
OPTION(MAXDOP 1)

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

Если мы посмотрим на чтение страниц (SET STATISTICS IO ON;), то увидим, что SQL Server должен прочитать 85500 страниц, чтобы вернуть требуемые данные:

(18062 rows affected)
Table 'Badges'. Scan count 2, logical reads 85500, physical reads 20, read-ahead reads 33103, ...

Есть ли способ без корректировки нашего индекса для включения столбца Date достичь лучшей производительности для получения тех же результатов?

UNION ALL

В этом случае возможно переписать наш запрос с использованием UNION ALL вместо IN/OR:

SELECT 
    Name,UserId,DATE 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Benefactor' 
UNION ALL
SELECT 
    Name,UserId,DATE 
FROM 
    dbo.Badges 
WHERE 
    Name = 'Research Assistant'
OPTION(MAXDOP 1)

Мы получаем те же результаты с помощью гибридного плана выполнения.

В данном случае наш план отражает то, что SQL Server делал при выполнении наших запросов порознь:

  • Строки, где Name = ‘Benefactor’, возвращаются сканированием кластеризованного индекса.
  • Поиск в некластеризованном индексе с поиском ключа в кластеризованном индексе для записей с Name = ‘Research Assistant’.

Посмотрим на статистику ввода/вывода для запроса с UNION ALL:

(18062 rows affected)
Table 'Badges'. Scan count 2, logical reads 50120, physical reads 6, read-ahead reads 49649, ...

Несмотря на то, что этот запрос читает весь кластеризованный индекс, чтобы получить строки Benefactor, общее число логических чтений все же меньше, чем видно в паттерне seek/key lookup в комбинированном запросе с IN(). Эта версия UNION ALL дает SQL Server возможность построить гибридный план выполнения, комбинируя два различных способа генерации плана с меньшим общим числом чтений.

IN или UNION ALL?

Не существует способа гарантировано утверждать без проверки каждого варианта.

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

Добавить комментарий