Погружение в уникальные индексы

Рассмотрим следующий пример:

CREATE TABLE T (PK INT PRIMARY KEY, A INT, B INT)
CREATE INDEX TA ON T(A)
CREATE UNIQUE INDEX TB ON T(B)
INSERT T VALUES (0, 0, 0)
INSERT T VALUES (1, 1, 1)

Теперь предположим, что мы выполним обновление:

UPDATE T SET A = 1 – A

Это изменение влияет на кластерный индекс (PK__T__15502E78) и на некластерный индекс TA. План в значительной степени такой, какой мы ожидали:

  ^{|--Clustered Index Update(OBJECT:([T].[PK__T__15502E78]), OBJECT:([T].[TA]), SET:([T].[A] = [Expr1003]))
       |--Compute Scalar(DEFINE:([Expr1016]=[Expr1016]))
            |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1004] THEN (1) ELSE (0) END))
                 |--Compute Scalar(DEFINE:([Expr1003]=(1)-[T].[A], [Expr1004]=CASE WHEN [T].[A] = ((1)-[T].[A]) THEN (1) ELSE (0) END))
                      |--Top(ROWCOUNT est 0)
                           |--Clustered Index Scan(OBJECT:([T].[PK__T__15502E78]))}

Это типичный «узкий» план обновления. В одном операторе обновления затрагиваются кластерный и некластерный индексы. План содержит Compute Scalar, которые определяют, нужно ли изменять соответствующую строку некластерного индекса. О подобных планах я писал в этой статье.

Теперь предположим, что мы запускаем тот же оператор изменения, но на этот раз мы изменяем столбец B:

UPDATE T SET B = 1 – B

Внезапно план становится заметно сложнее:

_{Rows   Executes
2      1        |--Index Update(OBJECT:([T].[TB]), SET:([PK1022] = [T].[PK],[B1023] = [T].[B]))
2      1             |--Collapse(GROUP BY:([T].[B]))
4      1                  |--Sort(ORDER BY:([T].[B] ASC, [Act1021] ASC))
4      1                       |--Filter(WHERE:(NOT [Expr1019]))
4      1                            |--Split
2      1                                 |--Clustered Index Update(OBJECT:([T].[PK__T__15502E78]), SET:([T].[B] = [Expr1003]))
2      1                                      |--Compute Scalar(DEFINE:([Expr1019]=[Expr1019]))
0      0                                           |--Compute Scalar(DEFINE:([Expr1019]=CASE WHEN [Expr1004] THEN (1) ELSE (0) END))
0      0                                                |--Compute Scalar(DEFINE:([Expr1003]=(1)-[T].[B], [Expr1004]=CASE WHEN [T].[B] = ((1)-[T].[B]) THEN (1) ELSE (0) END))
2      1                                                     |--Top(ROWCOUNT est 0)
2      1                                                          |--Clustered Index Scan(OBJECT:([T].[PK__T__15502E78]))}

Что происходит? На этот раз изменениям был подвержен уникальный индекс. Механизмы хранения в SQL Server постоянно заботятся об обеспечении уникальности такого индекса, не позволяя вставлять в индекс дубликаты строк. С другой стороны, процессор запросов должен гарантировать, что оператор UPDATE завершится успешно, если его работа не приведёт к нарушению ограничения уникальности. В соответствии с этим правилом показанная выше операция изменения должна пройти успешно.

Давайте посмотрим что произойдет, если SQL Server будет вносить изменения в уникальный некластерный индекс TB, используя тот же простой план, который он использовал при изменениях по столбцу A. Чтобы применился этот план, сервер должен просматривать строки таблицы и изменять их по одной. Предположим, сервер решил сначала изменить строку с PK=0. В этом случае он попытается изменить значение B с 0 на 1. Но в индексе уже есть строка с B=1. Механизм хранения обеспечит действие ограничения уникальности индекса, и это изменение завершится ошибкой. По той же причине изменение не пройдёт, если сервер будет сначала изменять строку с PK=1. Казалось бы, нет возможности выполнить этот UPDATE!

К счастью, у SQL Server есть решение для таких случаев. Основная идея проста. Вместо обновления столбцов ключа уникального индекса, что может привести к «фиктивным» нарушениям уникальности, процессор запросов реорганизует UPDATE таким образом, что вместо этого будут изменяться не входящие в этот ключ столбцы. Эта реорганизация реализуется операторами разделения, сортировки и свертывания. Давайте подробнее рассмотрим, как это работает.

В нашем примере мы начинаем с изменения двух строк:

PK	B_old	B_new
0	0	1
1	1	0

Оператор SPLIT преобразует изменение в удаление, за которыми строки вставляются с новыми значениями:

Action	PK	B
Delete	0	0
Insert	0	1
Delete	1	1
Insert	1	0

Обратите внимание что STATISTICS PROFILE показывает что было затронуто 4 строки, как продемонстрировано в таблице выше.

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

Action	PK	B
Delete	0	0
Insert	1	0
Delete	1	1
Insert	0	1

Оператор свёртки объединяет соседние пары удаления и вставки, которые имеют одно и то же значение ключа, в одно изменение:

Action	PK_old	PK_new	B
Update	0	1	0
Update	1	0	1

В этом примере 4 строки сворачиваются обратно в 2 строки, оставляя только изменения. Обратите внимание, что UPDATE больше не изменяет столбец B (что может привести к фиктивному нарушению уникальности), а изменяет столбец PK, который является ключом не уникального индекса TB и не даёт сбой при изменении из‑за нарушения уникальности. Также обратите внимание, что в общем случае не обязательно все удаления и вставки превращаются в UPDATE. Результаты оператора свертывания могут включать любую комбинацию вставок, изменений и удалений.

После всего этого, оператор изменения индекса выполняет операции UPDATE, обобщённые оператором свертывания.

То, что план запроса включает операторы разделения, сортировки и свертывания, не означает, что это может привести к невозможности фактического нарушения уникальности. Это просто гарантирует что не будет фиктивных нарушений уникальности. Кроме того, SQL Server везде создает планы с операторами разделения, сортировки и свертывания, где существует риск фиктивного нарушения уникальности, несмотря на то, что такого может и не случиться. Например, следующий UPDATE, который для текущего набора данных не приведет к фиктивному нарушению уникальности, генерирует почти такой же план:

UPDATE T SET B = B + 10

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

UPDATE T SET B = B + 10 WHERE PK = 0
UPDATE TOP (1) T SET B = B + 10

_{Оперативно узнавать о новостях MS SQL Server можно в телеграмм-канале: MS SQL Server - дело тонкое...}