Skip to content

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 на использование другого плана переписыванием запроса.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Нет комментариев.

Автор не разрешил комментировать эту запись

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

Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

Form options

Добавленные комментарии должны будут пройти модерацию прежде, чем будут показаны.