Skip to content

Индексирование и оконные функции

Пересказ статьи Kevin Feasel. Indexing and Window Functions


POC: правило большого пальца


Если вы знакомы с оконными функциями, то, вероятно, слышали о стратегии индексирования POC: Partition by, Order by, Covering (покрытие). Другими словами, при работе с запросом следует сфокусироваться на столбцах в предложении Partition by (в указанном порядке!), затем на столбцах в предложении Order by (опять в заданном порядке!), и, наконец, на остальных столбцах в предложении SELECT, чтобы сделать индекс покрывающим (не по порядку!, хотя это и не повредит!).
Предположим, что у вас есть реально крутой запрос типа такого:

WITH records AS
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY i.CustomerID
ORDER BY i.OrderID) AS rownum
FROM Sales.Invoices i
)
SELECT *
FROM records r
WHERE
r.rownum = 0;
GO

Вы можете спросить себя, выполняется ли фильтрация по значению rownum = 0, если минимальное значение rownum должно быть 1? Вот почему! На самом деле для того, чтобы мы не учитывали время на выписывание строк. Так или иначе, вот план запроса:


Простой план

Этот запрос выполняется за 58 миллисекунд, включая 304 миллисекунды времени процессора, но при этом было выполнено 103384 операций ввода/вывода.

Давайте добавим индекс, начиная с ID заказчика и включая ID заказа в соответствии с нашими столбцами в partition и order.

IF NOT EXISTS
(
SELECT 1
FROM sys.indexes i
WHERE
i.name = N'IX_Sales_Invoices_WindowFunction'
)
BEGIN
CREATE INDEX [IX_Sales_Invoices_WindowFunction] ON Sales.Invoices
(
CustomerID,
OrderID
);
END
GO

Теперь снова проверим план запроса.

Выглядит неплохо

Мы по-прежнему имеем сканирование индекса, но план выглядит проще, чем раньше. Нам не требуется выполнять сортировку. Это не вполне следует из самого плана, но преимущества довольно большие: понижение длительности до 35 мс для однопоточности (поэтому 35 мс времени процессора) и только 1497 чтений.

Но будьте осторожны: порядок имеет значение. Для этого запроса мы бы ожидали в точности такой же вывод. Кроме того, индексы имеют двойное связывание, поэтому я могу читать его в любом направлении.

WITH records AS
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY i.CustomerID
ORDER BY i.OrderID DESC) AS rownum
FROM Sales.Invoices i
)
SELECT *
FROM records r
WHERE
r.rownum = 0;
GO


Одна печальная сортировка в конце

Оказывается, что когда вы создаете индекс для поддержки оконной функции, вам действительно нужно придерживаться порядка.

WPOC: почему правило большого пальца не работает


Это большое исключение из правила большого пальца POC: если вы имеете сильный фильтр, поместите ваши фильтры в начале. В примере выше мы сканировали весь набор счетов-фактур. Предположим, однако, что нам нужны только конкретные счета-фактуры.

WITH records AS
(
SELECT
ROW_NUMBER() OVER (
PARTITION BY i.CustomerID
ORDER BY i.OrderID) AS rownum
FROM Sales.Invoices i
WHERE
i.TotalDryItems > 3
)
SELECT *
FROM records r
WHERE
r.rownum = 0;
GO


Ну, это шаг назад

Мы вернулись к 103384 чтениям и сканированию кластеризованного индекса. В идеале мы сначала фильтруем по предложению WHERE и обрезаем число необходимых строк. Если подобный запрос выполняется множество раз, нам следует модифицировать наш индекс. Вот наша первая попытка:

CREATE INDEX [IX_Sales_Invoices_WindowFunction] ON Sales.Invoices
(
TotalDryItems,
CustomerID,
OrderID
) WITH(DROP_EXISTING = ON);


Это уже лучше

План запроса не обязательно выглядит хорошо, но имеются улучшения: продолжительность 8мс, 35мс времени процессора и 472 чтения. Если предполагается, что запрос должен включать как минимум 3 сухих элемента, у нас может быть даже вариант использования фильтрованного индекса!

CREATE INDEX [IX_Sales_Invoices_WindowFunction] ON Sales.Invoices
(
CustomerID,
OrderID
)
WHERE TotalDryItems > 3
WITH(DROP_EXISTING = ON);


Теперь поговорим

Продолжительность 41мс для непараллельного выполнения запроса и всего 369 чтений.

Что насчет нескольких окон?


ОК, мистер Умный Парень, что случится, когда мы имеем несколько окон? Ну... вам придется с этим жить. Вы получите один индекс для извлечения данных (здесь даже более важно для индексирования сделать лидирующим предложение WHERE), а затем вы получите множество сортировок и операций с окнами. Если вам нужны все эти окна в единственном запросе, тут мало что вы могли бы сделать. Если производительность действительно плоха, сбросьте результаты в временную таблицу и попробуйте использовать более простые запросы.

Заключение


Если вам необходимо сканировать всю таблицу, то создавайте индексы на основе шаблона POC. Но как только вам нужно сузить ваши запросы, выбор за WPOC.

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

No comments

The author does not allow comments to this entry

Add Comment

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

Submitted comments will be subject to moderation before being displayed.