Структуры хранения #5 – XML-индексы

Автор: Hugo Kornelis, Storage structures 5 – XML indexes;

После рассмотрения дискового строчного хранения, индексов columnstore, оптимизированных для памяти структур и оптимизированных для памяти columnstore, пришло время обратить внимание на структуры хранения, которые используются только для определённых типов данных. Первой «жертвой» станет XML-индекс.

Когда вам нужно хранить XML-данные в базе данных SQL Server, вы можете выбрать между двумя типами данных, каждый со своими плюсами и минусами. Вы можете хранить XML-данные как nvarchar(max). Это сохраняет точное содержимое XML в том виде, в котором вы его получили, что может потребоваться по юридическим причинам. Однако любой запрос, которому важно конкретное содержание XML-данных, будет вынужден прибегать к очень сложным строковым выражениям.

Тип данных xml выполняет «разборку» (shredding) XML и хранит содержимое во внутреннем формате, который позволяет SQL Server работать, например, с выражениями XQuery или XPath. Этот формат также экономит место по сравнению с альтернативой nvarchar(max). Однако при запросе данных содержимое будет тем же, но форматирование и пробелы могут отличаться.

Вы можете использовать XQuery и XPath для любых данных, имеющих тип xml. Но для часто фильтруемых столбцов это может быть медленно. Чтобы ускорить такие запросы, вы можете создавать XML-индексы.

Этот пост не описывает внутреннее устройство хранения типа данных xml. Он сосредоточен только на XML-индексах, которые могут быть определены поверх столбца типа xml в таблице.

Четыре типа

SQL Server поддерживает не менее четырёх типов XML-индексов: первичный XML-индекс и три типа вторичных XML-индексов (PATH, VALUE и PROPERTY). Вторичный XML-индекс может быть создан только на XML-индексе, для которого уже существует первичный XML-индекс.

Также возможно создание выборочных XML-индексов (selective XML indexes). Они содержат только подмножество содержимого индексируемых XML-данных. Однако фактическая структура хранения данных при этом не меняется.

Первичный XML-индекс

Первичный XML-индекс также называется таблицей узлов (node table). Этот индекс хранит копию всех данных в индексируемом XML-столбце вместе со ссылкой на строку, из которой эти данные происходят.

Давайте посмотрим на детали. В образце базы данных AdventureWorks уже есть несколько XML-индексов, но давайте создадим свой собственный, просто потому что можем. Столбец Resume в таблице HumanResources.JobCandidate имеет тип xml, но не индексирован. Единственный тип XML-индекса, который мы можем создать, — это первичный XML-индекс:

CREATE PRIMARY XML INDEX PXML_Jobcandidate_Resume
ON HumanResources.JobCandidate (Resume);

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

SELECT     o.object_id               AS [Object ID],
           SCHEMA_NAME (o.schema_id) AS [Schema name],
           o.name                    AS [Object name],
           o.parent_object_id        AS [Parent object ID],
           o.type_desc               AS [Object type],
           i.name                    AS [Index name],
           i.type_desc               AS [Index type]
FROM       sys.indexes AS i
INNER JOIN sys.objects AS o
   ON      o.object_id  = i.object_id
WHERE      i.name       = N'PXML_Jobcandidate_Resume';

Когда вы выполните этот запрос, вы можете удивиться, увидев в результатах не одну, а две строки!

Первая строка — та, которую мы ожидали. Индекс с типом XML, определённый на объекте HumanResources.JobCandidate. Однако есть и вторая строка. Этот индекс имеет то же имя, указан как кластерный индекс и определён на объекте, классифицированном как «внутренняя таблица» (internal table) с именем xml_index_nodes, за которым следуют идентификатор объекта таблицы JobCandidate и число, которое, насколько я могу судить, начинается с 256000 и увеличивается для каждого дополнительного первичного XML-индекса в той же таблице. У этой внутренней таблицы также ненулевое значение в столбце Parent object ID. Значение здесь точно соответствует идентификатору объекта таблицы JobCandidate.

Эта внутренняя таблица и есть таблица узлов. В этой таблице все узлы из XML-содержимого JobCandidate хранятся в другом формате. Поскольку это внутренняя таблица, мы не можем выполнять к ней запросы. Однако мы можем заглянуть в её структуру:

SELECT     c.name         AS ColumnName,
           t.name         AS Datatype,
           CASE
               WHEN c.max_length = -1 THEN
                   'MAX'
               ELSE
                   CAST (c.max_length AS varchar(30))
           END            AS MaxLength,
           ic.key_ordinal AS [Clustered index column]
FROM       sys.indexes       AS i
INNER JOIN sys.columns       AS c
   ON      c.object_id    = i.object_id
INNER JOIN sys.types         AS t
   ON      t.user_type_id = c.user_type_id
LEFT JOIN  sys.index_columns AS ic
  ON       ic.object_id   = i.object_id
  AND      ic.index_id    = i.index_id
  AND      ic.column_id   = c.column_id
WHERE      i.name = N'PXML_Jobcandidate_Resume'
AND        i.type_desc   = N'CLUSTERED'
ORDER BY   c.column_id;

Это даёт следующий результат:

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

Теперь, когда я ранее сказал, что мы не можем выполнять запросы к внутренней таблице, я немного соврал. Обычно мы не можем этого делать. Но есть исключение. Подключения, установленные через выделенное административное соединение (DAC), могут выполнять запросы к внутренним таблицам!

Итак, я подключился через DAC и выполнил этот запрос (вам может потребоваться изменить точное имя таблицы узлов):

SELECT *
FROM   sys.xml_index_nodes_722101613_256000;

Это возвращает 1 046 строк. Это довольно впечатляет, учитывая, что в таблице JobCandidate всего 13 строк! Причина в том, что каждое из 13 XML-значений в таблице «разбирается» на составные части. Каждый элемент, атрибут и значение становятся строкой в таблице узлов.

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

Столбец	Предполагаемое значение
id	Похоже, это двоичное кодирование пути к узлу, расширенное дополнительным значением для добавленных атрибутов.
nid	Похоже, это идентификатор элемента. Если элемент повторяется, это значение не меняется.
tagname	Неизвестно, до сих пор я наблюдал только значения NULL в этом столбце.
taguri	Неизвестно, до сих пор я наблюдал только значения NULL в этом столбце.
tid	Для элементов в типизированном XML это ссылка на xml_component_id в sys.xml_schema_types. Для атрибутов в типизированном XML это, по-видимому, индикатор типа данных. В нетипизированном XML всегда NULL.
value	Хранит значение атрибута или содержимое элемента.
lvalue	То же, что и value, но только для значений, которые являются длинными строками.
lvaluebin	Вероятно, то же, что и value, но для BLOB-значений.
hid	Это закодированное представление пути к узлу.
xsinil	Исходя из названия, я ожидал, что это будет True для элементов, использующих xsi:nil="true". Однако в моих тестах они сохранялись как атрибут с именем xsi:nil со значением "true", а столбец xsinil оставался равным 0. Я не наблюдал значений 1 в этом столбце.
xsitype	Исходя из названия, я ожидал, что это будет True для элементов, использующих атрибут xsi:type. Однако в моих тестах они сохранялись как атрибут с именем xsi:type с указанным типом в качестве значения, а столбец xsitype оставался равным 0. Я не наблюдал значений 1 в этом столбце.
pkn	Один или несколько столбцов со значениями первичного ключа соответствующей строки данных.

Я понимаю, что приведённая выше таблица содержит много предположений и пробелов. Пожалуйста, свяжитесь со мной, если вы можете предоставить дополнительную информацию!

Суть в том, что первичный XML-индекс — это просто обычный кластерный индекс на таблице, которая представляет все узлы, элементы и атрибуты из XML-содержимого в 11 столбцах плюс один или несколько столбцов для связи обратно с первичным ключом таблицы. Ключевые столбцы кластерного индекса — это столбцы первичного ключа плюс столбец id, представляющий путь каждого узла, элемента или атрибута.

Вторичный XML-индекс

Чтобы более подробно рассмотреть вторичные XML-индексы, я не буду создавать свой собственный, а вместо этого использую существующую таблицу в AdventureWorks — Person.Person. В этой таблице есть два XML-столбца: AdditionalContactInfo и Demographics. Оба имеют первичный XML-индекс, но столбец Demographics также имеет все три типа вторичных XML-индексов.

SELECT     o.object_id               AS [Object ID],
           SCHEMA_NAME (o.schema_id) AS [Schema name],
           o.name                    AS [Object name],
           o.parent_object_id        AS [Parent object ID],
           o.type_desc               AS [Object type],
           i.name                    AS [Index name],
           i.type_desc               AS [Index type]
FROM       sys.indexes AS i
INNER JOIN sys.objects AS o
   ON      o.object_id = i.object_id
WHERE      o.parent_object_id = OBJECT_ID (N'Person.Person')
OR         o.object_id               = OBJECT_ID (N'Person.Person');

Результаты показывают три ожидаемых обычных индекса и пять XML-индексов. Но мы также снова видим пять индексов на внутренних таблицах. Если посмотреть на столбец Object ID, можно увидеть, что они принадлежат всего двум внутренним таблицам.

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

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

SELECT name,
       xi.index_id,
       xi.type_desc,
       xi.xml_index_type_description,
       xi.using_xml_index_id,
       xi.secondary_type_desc
FROM   sys.xml_indexes AS xi
WHERE  xi.object_id = OBJECT_ID (N'Person.Person');

Это возвращает следующие данные:

Детали по типу вторичного XML-индекса

Итак, мы знаем, что первичный XML-индекс — это таблица узлов с кластерным индексом, а все вторичные XML-индексы — это некластерные индексы на той же таблице узлов. Это означает, что остаётся только один вопрос: каковы индексированные столбцы в этих вторичных XML-индексах?

Мы можем узнать это, используя упрощённую версию запроса, который я использовал для проверки деталей первичного XML-индекса. Например, следующий запрос находит детали индекса PATH:

SELECT     c.name         AS ColumnName,
           ic.key_ordinal AS [Index column]
FROM       sys.indexes       AS i
INNER JOIN sys.columns       AS c
   ON      c.object_id    = i.object_id
INNER JOIN  sys.index_columns AS ic
  ON       ic.object_id   = i.object_id
  AND      ic.index_id    = i.index_id
  AND      ic.column_id   = c.column_id
WHERE      i.name = N'XMLPATH_Person_Demographics'
AND        i.type_desc   = N'NONCLUSTERED'
ORDER BY   ic.key_ordinal;

Это возвращает следующее:

Используя тот же запрос, я изучил каждый из трёх типов, чтобы определить, какие столбцы индексируются.

Индекс PATH

Как показано выше, индекс PATH реализован как некластерный индекс на столбцах hid и value. Поскольку hid является закодированным представлением пути к узлу, это делает его идеальным для таких запросов, как этот, которые фильтруют по наличию конкретного узла:

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)
SELECT BusinessEntityID,
       LastName,
       Demographics
FROM   Person.Person
WHERE  Demographics.exist ('/ns:IndividualSurvey/ns:NumberCarsOwned') = 0;

План выполнения показан ниже:

Интересная часть — это верхний вход в оператор Hash Match. Свойства Index Seek показывают, что именно здесь происходит:

Свойство Object показывает, что этот Index Seek использует вторичный индекс PATH. Свойство Seek Predicates показывает, что именно возвращается: все строки, у которых столбец hid равен некоторым странным непечатаемым символам. Если вы следили за мной и использовали выделенное административное соединение для запроса содержимого таблицы узлов, вы узнаете в этом те значения, которые находятся в столбце hid — закодированное представление пути. Таким образом, значение ̀ является закодированным представлением пути /ns:IndividualSurvey/ns:NumberCarsOwned. Из таблицы узлов, содержащей в данном случае более 300 000 строк, возвращаются только 18 484 узла NumberCarsOwned.

Нижний вход в Hash Match не вызывает удивления. Простое Clustered Index Scan по таблице Person плюс фильтр для удаления строк с NULL в столбце Demographics. Должен признать, что я не знаю, почему этот фильтр не был встроен в сканирование, но это оказывает лишь незначительное влияние на производительность.

Затем Hash Match соединяет эти два входа, используя Right Anti Semi Join в качестве логической операции. Это означает, что сохраняются только те строки из нижнего входа, для которых нет совпадения в верхнем входе. Это, конечно, отражает фильтр .exist = 0.

Индекс VALUE

Используя тот же метод, что и ранее, вы увидите, что индекс VALUE реализован как некластерный индекс на столбцах value и hid. Вы заметите, что это те же столбцы, которые используются в индексе PATH, но в обратном порядке. Это делает индекс VALUE идеальным инструментом для поиска конкретных значений в XML, как в запросе ниже:

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)
SELECT BusinessEntityID,
       LastName,
       Demographics
FROM   Person.Person
WHERE  Demographics.exist ('/ns:IndividualSurvey/ns:Education[.="Bachelors "]') = 1;

Это выполняется со следующим планом выполнения:

В этом случае выбран Nested Loops из-за более низких оценок количества строк. Верхний вход снова представляет собой Index Seek, но теперь по индексу VALUE.

Свойство Seek Predicates показывает, что передаются как значение (Bachelors с пробелом в конце), так и путь к узлу (опять закодированный, как ʀ). Это означает, что индекс PATH также мог быть использован. Я уверен, что есть лучшие примеры использования индекса VALUE, но мои знания XML ограничены, и я сдался после часа попыток.

Свойство Output List показывает, что возвращается только значение первичного ключа соответствующих строк в таблице Person. Затем Nested Loops выполняет свою нижнюю ветвь для каждого переданного значения, выполняя Clustered Index Seek для получения этой строки и снова фильтр для удаления строки, если столбец Demographics равен NULL. Поскольку передаются только значения первичного ключа, которые удовлетворяют условию наличия степени Bachelors в Demographics, этот фильтр фактически бесполезен.

Индекс PROPERTY

Последний тип вторичного XML-индекса построен не на двух, а на трёх столбцах: pk1, hid, value. Если бы вы попробовали это на таблице с составным первичным ключом, то pk2, pk3 и т. д. также были бы индексированы после pk1, но перед hid.

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

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)
SELECT BusinessEntityID,
       LastName,
       Demographics,
       Demographics.value ('/ns:IndividualSurvey[1]/ns:Education[1]', 'varchar(30)')
FROM   Person.Person
WHERE  BusinessEntityID = 3206;

Графический план выполнения выглядит так:

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

Нижняя ветвь для первого Nested Loops начинается с Clustered Index Seek по первичному XML-индексу (вы не можете увидеть это в графическом плане выполнения, но это становится ясно при наведении курсора на оператор и проверке его свойства Object — хотя тот факт, что это Clustered Index Seek, также является индикатором). Свойство Seek Predicates показывает, что читаются только строки для указанного значения первичного ключа, с дополнительным предикатом фильтрации по столбцу hid, чтобы вернуть только запрошенный узел (в данном случае это только IndividualSurvey или À€ в закодированном формате).

Чтобы понять, почему используется Clustered Index Seek только по pk1 вместо Index Seek по комбинации pk1 плюс hid (первые два столбца этого индекса), нужно посмотреть на свойство Output List. Возвращаются столбцы id, hid и pk1. Но id не включён в индекс PROPERTY, поэтому потребовался бы поиск по ключу (Key Lookup). Оптимизатор, по-видимому, посчитал это слишком дорогим и решил использовать кластерный индекс. Свойство Actual Rows Read показывает, что ему пришлось просканировать всего 2 строки в таблице узлов, чтобы найти единственную строку, которую мы искали. Это действительно дешевле, чем Index Seek плюс Key Lookup.

Оператор Compute Scalar выглядит нелогичным. Он вычисляет новый столбец Expr1007 и устанавливает его в жестко заданное значение 0x58. Но этот столбец затем нигде не используется в остальной части плана выполнения. Поэтому я не знаю, зачем это делается.

Оператор Top реализует [1] в спецификации /ns:IndividualSurvey[1]. Если вы измените это на более высокое значение, вы увидите, что это заменяется комбинацией Segment и Sequence Project (для вычисления номера строки), за которой следует Filter, чтобы оставить только строки, где номер строки соответствует спецификации.

В конце этой ветви остаётся только значение id (как видно в Output List оператора Top). Это значение передаётся в Nested Loops, который затем использует Outer References для передачи этого значения в нижнюю ветвь.

В этой нижней ветви мы снова видим Clustered Index Seek по первичному XML-индексу. На этот раз Seek Predicates выполняет поиск по pk1, а также по столбцу id. Это поиск по диапазону, который возвращает все строки (в пределах указанного первичного ключа), у которых значение id находится между id узла IndividualSurvey[1] и значением, вычисленным внутренней скалярной функцией getdescendantlimit, которая возвращает наибольший дочерний узел того же узла. Таким образом, возвращаются все дочерние узлы IndividualSurvey[1]. Дополнительный фильтр (Predicate) затем сужает это до полного указанного пути (уже знакомое значение ʀ) и выполняет (избыточную, если я не ошибаюсь) проверку того, что внутренняя функция getancestor для этого узла возвращает id узла /ns:IndividualSurvey[1].

Остальная часть этой ветви — это те же Compute Scalar и Top, которые мы видели в другой ветви. Дальнейших пояснений не требуется.

Таким образом, хотя это был интересный план выполнения для обсуждения, чтобы показать, как в данном случае может использоваться первичный XML-индекс, он не демонстрирует использование индекса PROPERTY. Мне не удалось построить демонстрационный запрос, который бы его использовал. Опять же, скорее всего, это связано с моим очень ограниченным пониманием XML, XQuery и XPath. Ещё раз, все предложения приветствуются.

Заключение

Все запросы, показанные в этом посте, также могут быть использованы без предварительного создания каких-либо XML-индексов. Это приведёт к планам выполнения, использующим комбинацию операторов Table Valued Function и UDX для возврата запрошенных результатов. Но это медленно. Поэтому для часто запрашиваемых XML-столбцов имеет смысл создавать один или несколько XML-индексов.

Это не чёрная магия. Первичный XML-индекс — это просто таблица узлов, которая содержит по одной строке для каждого узла в XML-данных и организована как кластерный индекс. Любой вторичный XML-индекс затем является некластерным индексом на той же таблице узлов, где индексированные столбцы выбраны для оптимизации определённых типов запросов.

Оптимизатор анализирует все выражения XQuery и XPath в запросе, чтобы предложить (надеюсь) наилучший путь доступа, используя либо первичный XML-индекс, либо любой из доступных вторичных XML-индексов. Это приводит к плану выполнения, который показывает простые операторы поиска и сканирования по этим внутренним индексам.