Индексирование в T-SQL

75 Исходник базы данных

Тестовые данные

В большинстве примеров в этой и следующих статьях, посвященных оптимизации, используются таблицы Accounts и Transactions, которые содержат информацию о банковских счетах и транзакциях на этих счетах. При пополнении счета сумма транзакции положительная, а при снятии средств со счета — отрицательная. Выполните следующий код, чтобы создать таблицы Accounts и Transactions в тестовой базе данных TSQL2012:

SET NOCOUNT ON;
USE TSQL2012;

IF OBJECT_ID('dbo.Transactions', 'U') IS NOT NULL DROP TABLE dbo.Transactions;
IF OBJECT_ID('dbo.Accounts', 'U') IS NOT NULL DROP TABLE dbo.Accounts;

CREATE TABLE dbo.Accounts
(
  actid   INT         NOT NULL,
  actname VARCHAR(50) NOT NULL,
  CONSTRAINT PK_Accounts PRIMARY KEY(actid)
);

CREATE TABLE dbo.Transactions
(
  actid  INT   NOT NULL,
  tranid INT   NOT NULL,
  val    MONEY NOT NULL,
  CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid),
  CONSTRAINT FK_Transactions_Accounts
    FOREIGN KEY(actid)
    REFERENCES dbo.Accounts(actid)
);

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

INSERT INTO dbo.Accounts(actid, actname) VALUES
  (1,  'account 1'),
  (2,  'account 2'),
  (3,  'account 3');

INSERT INTO dbo.Transactions(actid, tranid, val) VALUES
  (1,  1,  4.00),
  (1,  2, -2.00),
  (1,  3,  5.00),
  (1,  4,  2.00),
  (1,  5,  1.00),
  (1,  6,  3.00),
  (1,  7, -4.00),
  (1,  8, -1.00),
  (1,  9, -2.00),
  (1, 10, -3.00),
  (2,  1,  2.00),
  (2,  2,  1.00),
  (2,  3,  5.00),
  (2,  4,  1.00),
  (2,  5, -5.00),
  (2,  6,  4.00),
  (2,  7,  2.00),
  (2,  8, -4.00),
  (2,  9, -5.00),
  (2, 10,  4.00),
  (3,  1, -3.00),
  (3,  2,  3.00),
  (3,  3, -2.00),
  (3,  4,  1.00),
  (3,  5,  4.00),
  (3,  6, -1.00),
  (3,  7,  5.00),
  (3,  8,  3.00),
  (3,  9,  5.00),
  (3, 10, -3.00);
  

Что касается создания объемных тестовых данных, сначала выполните следующий код, чтобы создать вспомогательную функцию по имени GetNums, которая генерирует последовательность целых чисел из заданного диапазона:

IF OBJECT_ID('dbo.GetNums', 'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
    L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
    L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
    L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
    L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
    L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
    L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
    Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5)
  SELECT @low + rownum - 1 AS n
  FROM Nums
  ORDER BY rownum
  OFFSET 0 ROWS FETCH FIRST @high - @low + 1 ROWS ONLY;

А затем следующим кодом создайте в таблице Accounts 100 счетов, а в таблице Transactions 20 тыс. транзакций на каждом счете — всего 2 млн транзакций:

DECLARE
  @num_partitions     AS INT = 100,
  @rows_per_partition AS INT = 20000;

TRUNCATE TABLE dbo.Transactions;
DELETE FROM dbo.Accounts;

INSERT INTO dbo.Accounts WITH (TABLOCK) (actid, actname)
  SELECT n AS actid, 'account ' + CAST(n AS VARCHAR(10)) AS actname
  FROM dbo.GetNums(1, @num_partitions) AS P;

INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val)
  SELECT NP.n, RPP.n,
    (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5))
  FROM dbo.GetNums(1, @num_partitions) AS NP
    CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;

Вы можете поменять число секций (счетов) и строк на секцию (транзакций на счет), но учтите: именно такие цифры я использую в качестве входных данных в своих тестах.

Рекомендации по индексированию

Итераторы планов, которые вычисляют результат оконной функции, будут описаны чуть позже. А сейчас достаточно сказать, что им нужно, чтобы входные строки хранились с разбиением по столбцам секционирования (если имеется предложение секционирования), а затем по столбцам упорядочения (при условии, что есть предложение упорядочения окна). Если нет индекса, содержащего информацию в нужном порядке, потребуется выполнить сортировку, прежде чем оконные итераторы смогут приступить к работе.

POC-индекс

Общие рекомендации по поддержке оконных функций укладываются в концепцию, которую я называю POC — по начальным буквам слов partitioning (секционирование), ordering (упорядочение) и covering (покрытие). Иногда ее называют POCo. Ключами POC-индекса должны быть столбцы оконных секций, за которыми следуют столбцы упорядочения, в конце индекс должен включать остальные столбцы, которые используются в запросах. Это включение можно выполнить с помощью предложения явного включения INCLUDE в некластеризованном индексе или средствами кластеризации самого индекса — в последнем случае в конечные строки нужно включить все столбцы дерева.

В отсутствие POC-индекса в плане появляется итератор Sort, который может создавать серьезную нагрузку, если объем входных данных велик. Сложность сортировки пропорциональна произведению N * LOG(N), которое растет быстрее линейной функции. Это означает, что с ростом числа строк каждая последующая строка стоит дороже, чем предыдущая. Возьмем для примера значения 1000 * LOG(1000) = 3000 и 10000 * LOG(10000) = 40000. Это означает, что при увеличении числа строк на порядок объем работы увеличивается в 13 раз и ситуация ухудшается с увеличением числа строк. В качестве примера посмотрите на следующий запрос:

SELECT actid, tranid, val,
  ROW_NUMBER() OVER(PARTITION BY actid ORDER BY val) AS rownum
FROM dbo.Transactions;

План этого запроса показан на рисунке:

План запроса с итератором Sort

В данный момент никакого POC-индекса нет. Просмотр кластеризованного индекса выполняется без требования упорядочения (свойство Ordered равно False), после чего для сортировки данных используется дорогой итератор Sort. На моей системе при наличии «горячего» кеша на выполнение этого запроса ушло четыре секунды, при этом результаты отбрасывались. (Чтобы отбросить результаты, в контекстном меню Query Options выберите Grid в разделе Results и установите флажок Discard Results After Execution.) Затем создайте POC-индекс таким кодом:

CREATE INDEX idx_actid_val_i_tranid
  ON dbo.Transactions(actid /* P */, val /* O */)
  INCLUDE(tranid /* C */);

Как видите, первая часть содержит столбцы оконных секций (в нашем случае actid), за которыми следуют столбцы упорядочения окна (в нашем случае val), а затем указаны остальные столбцы, используемые в запросе (в данном случае tranid). Повторно выполните следующий запрос:

SELECT actid, tranid, val,
  ROW_NUMBER() OVER(PARTITION BY actid ORDER BY val) AS rownum
FROM dbo.Transactions;

План этого запроса показан на рисунке:

План запроса без итератора Sort

Итератор Sort исчез. План выполняет упорядоченный просмотр POC-индекса, решая задачу упорядочения для итераторов, которые вычисляют результат оконной функции. На этот раз запрос выполнялся две секунды, несмотря на то, что использовался последовательный план, что отличается от предыдущего параллельного плана с сортировкой. В больших наборах разница больше.

Если в запросе присутствуют фильтры равенства, например WHERE col1 = 5 AND col2 = 'ABC' все потребности в фильтрации можно удовлетворить с помощью того же индекса, разместив фильтруемые столбцы первыми в списке ключей индекса. Это можно назвать FPOC-индексом, где FPO соответствует списку ключей, а C — списку включения.

Если в запросе много оконных функций, то если у них одинаковое определение окна, они могут использовать одни и те же упорядоченные данные без необходимости каждый раз добавлять итератор Sort. Заметьте также, что при указании множественных оконных функций с разным упорядочением окон (и, возможно, упорядочением представления), порядок их указания в списке SELECT может влиять на число сортировок, выполняемых в плане.

Обратный просмотр

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

Первая особенность заключается в том, что в прямых просмотрах для повышения эффективности может использоваться параллелизм, а в обратных - нет. В настоящий момент обратные просмотры просто не реализованы в ядре базы данных.

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

Посмотрите на следующий запрос, который мы уже использовали в предыдущем примере:

SELECT actid, tranid, val,
  ROW_NUMBER() OVER(PARTITION BY actid ORDER BY val) AS rownum
FROM dbo.Transactions;

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

SELECT actid, tranid, val,
  ROW_NUMBER() OVER(PARTITION BY actid ORDER BY val DESC) AS rownum
FROM dbo.Transactions;

План этого запроса приведен на рисунке, и в нем есть итератор Sort:

План с итератором Sort при упорядочении по убыванию

Индекс из предыдущего примера используется и здесь, но упорядочение здесь не используется. В этом можно убедиться, посмотрев на свойство Ordered итератора Index Scan — его значение равно False, а в предыдущем случае значение Ordered было равно True. Это недостаток оптимизации. Порядок, в котором просматриваются конкретные столбцы секционирования, не должен иметь значения. А важно здесь то, что значения в каждой секции должны сканироваться точно в порядке, заданном в предложении упорядочения окна. Поэтому чтение индекса в обратном порядке должно предоставить значения для оконной функции в правильном порядке. Но, к сожалению, оптимизатор этого не понимает.

Есть два индекса, которые могут избавить от необходимости сортировки: один содержит список ключей (actid, val DESC), а другой точные указания по обратному просмотру (actid DESC, val), причем у обоих одинаковый список включения (tranid). В первом случае будет использоваться упорядоченный прямой, а во втором — упорядоченный обратный просмотр.

Но намного интереснее то, что происходит, если добавить предложение представления, которое требует упорядочить строки по столбцу упорядочения в порядке убывания. Вдруг итераторы, вычисляющие оконную функцию, хотят получать секционированные значения в порядке убывания и для этого используют упорядочение индекса. Поэтому простое добавление предложения представления ORDER BY с DESC в наш последний запрос устраняет необходимость в итераторе Sort. Вот скорректированный запрос:

SELECT actid, tranid, val,
  ROW_NUMBER() OVER(PARTITION BY actid ORDER BY val DESC) AS rownum
FROM dbo.Transactions
ORDER BY actid DESC;

План этого запроса показан на рисунке:

План запроса без итератора Sort при упорядочении по убыванию

Заметьте, что итератор Sort исчез. План выполняет упорядоченный обратный просмотр индекса. Как вы помните, в отличие от прямого просмотра, обратный просмотр не поддается распараллеливанию. Тем не менее, это замечательно узнать, что добавление в запрос предложения представления ORDER BY позволяет повысить производительность!

Индексы columnstore

Индексы columnstore впервые появились в SQL Server 2012. Они группируют и хранят данные по столбцам (в отличие от строк, которые используются в обычных индексах). В них удается добиться высокого уровня сжатия за счет использования технологии, которая называется VertiPaq. В запросах определенного типа, особенно в хранилищах данных, индексы columnstore позволяют значительно повысить производительность по сравнению с обычными индексами. Повышение производительности достигается за счет сжатия (сокращение ввода/вывода) и новой пакетной обработки данных, в которой нет в традиционных вычислениях с использованием строк.

Индексы columnstore позволяют повысить эффективность запросов, в которых используется фильтрация, группировка и соединения типа «звезда». Однако индексы columnstore не обеспечивают ускорение вычисления оконных функций. Некоторые запросы с оконными функциями могут работать быстрее, например, из-за обусловленного сжатием сокращения числа операций ввода/вывода), однако вычисление итераторов, которые используются в оконных функциях, все равно выполняется построчно. Иначе говоря, чтобы добиться высокой производительности оконных функций, лучше сосредоточиться на создании традиционных POC-индексов, которые позволят избежать сортировки данных.

Пройди тесты
Лучший чат для C# программистов