XML-индексы (SQL Server)

Статья
08/04/2023

Применяется к:SQL Server Azure SQL Database Azure, управляемому экземпляру SQL Azure

Для столбцов типа xml можно создавать XML-индексы. При этом индексируются все теги, значения и пути хранимых в столбце экземпляров XML и повышается эффективность обработки запросов. Применение XML-индекса может дать преимущества в следующих ситуациях.

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

Начиная с SQL Server 2022 (16.x) и более поздних версий, а также в Базе данных SQL Azure и Управляемом экземпляре SQL Azure, можно использовать сжатие XML для сжатия данных XML вне строки как для столбцов XML, так и для индексов. Сжатие XML уменьшает требования к емкости хранилища данных.

XML-индексы разделяются на следующие категории.

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

Первым индексом, создаваемым для столбца типа данных xml , должен быть первичный XML-индекс. При наличии первичного XML-индекса поддерживаются вторичные индексы трех типов: PATH, VALUE и PROPERTY. Эти вторичные индексы могут способствовать повышению производительности выполнения разных типов запросов.

Заметка

Создание или изменение XML-индекса невозможно до тех пор, пока параметры базы данных не будут соответствующим образом настроены для работы с типом данных xml . Дополнительные сведения см. в разделе Использование полнотекстового поиска со столбцами XML.

Экземпляры XML хранятся в столбцах типа xml в виде больших двоичных объектов (BLOB). Размер экземпляров типа xml бывает достаточно велик и в двоичном представлении может достигать 2 ГБ. При отсутствии индекса эти большие двоичные объекты разбираются на этапе выполнения запроса, что может занять некоторое время. Рассмотрим, например, следующий запрос :

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1;

Чтобы выбрать экземпляры XML, удовлетворяющие условию предложения WHERE , большой двоичный объект типа данных XML (объект BLOB) в каждой строке таблицы Production.ProductModel разбирается во время выполнения запроса. Затем вычисляется выражение (/PD:ProductDescription/@ProductModelID[.="19"]) в методе exist() . В зависимости от размера и количества экземпляров, содержащихся в столбце, такой разбор на этапе выполнения запроса может потребовать значительных затрат.

Если приложение часто обращается к большим двоичным объектам XML (BLOB), индексирование столбцов типа xml поможет оптимизировать такие запросы. Однако обслуживание индекса при изменении данных связано с некоторыми затратами.

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

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

Первичный XML-индекс — это разобранное и сохраненное представление XML-объектов BLOB, содержащихся в столбце типа данных xml . Для каждого большого двоичного объекта (BLOB) столбца типа данных xml в индексе создается несколько строк данных, и их количество приблизительно равно числу узлов в большом двоичном объекте XML. При получении полного экземпляра XML SQL Server предоставляет экземпляр из XML-столбца. При обработке запросов в области экземпляров XML применяется первичный XML-индекс, и для возврата скалярных значений или поддеревьев XML может быть использован сам индекс.

В каждой строке для узла хранятся следующие сведения:

имя тега — элемента или атрибута;
значение узла;
тип узла: узел элемента, атрибута или текстовый узел;
сведения о положении в документе, представленные внутренним идентификатором узла;
путь от каждого узла до корня XML-дерева. По этому столбцу в запросе производится поиск выражений пути;
первичный ключ базовой таблицы. Дублируется в первичном XML-индексе для обратного соединения с базовой таблицей, а максимальное количество столбцов в первичном ключе базовой таблицы ограничено значением 15.

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

//ContactRecord/PhoneNumber , где известны только два последних элемента

ИЛИ

/Book/*/Title, где в середине выражения указан подстановочный знак *.

Обработчик запросов использует первичный XML-индекс для запросов, задействующих xml Data Type Methods , и возвращает либо скалярные значения, либо XML-поддеревья из самого первичного индекса. (В этом индексе хранятся все необходимые данные для реконструкции экземпляра XML.)

Например, следующий запрос возвращает сводные данные, содержащиеся в столбце типа данных CatalogDescriptionxml таблицы ProductModel . Запрос возвращает информацию <Summary> только для тех моделей продуктов, описание каталога которых также содержит описание <Features>.

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('  /PD:ProductDescription/PD:Summary') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

При использовании первичного XML-индекса вместо того, чтобы разбирать каждый экземпляр большого двоичного объекта типа XML в базовой таблице, производится последовательный поиск выражения, указанного в методе exist(), в строках индекса, соответствующих данному объекту. Если в столбце Path индекса найден путь, элемент <Summary> вместе со своими поддеревьями извлекается из первичного XML-индекса и преобразуется в большой двоичный объект типа XML как результат метода query().

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

USE AdventureWorks2022;

SELECT Instructions
FROM   Production.ProductModel
WHERE  ProductModelID = 7;

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

Для повышения производительности поиска можно также создать вторичные XML-индексы. Перед созданием вторичных индексов должен существовать первичный XML-индекс. Существуют следующие типы вторичных индексов:

вторичный индекс PATH типа данных XML;
вторичный индекс VALUE типа данных XML;
вторичный индекс PROPERTY типа данных XML.

Ниже приведены некоторые рекомендации по созданию вторичных индексов.

Если при работе с XML-столбцами часто используются выражения пути, вторичный XML-индекс PATH, скорее всего, ускорит обработку данных. Чаще всего используется exist() метод для XML-столбцов в предложении WHERE Transact-SQL.
Если с использованием выражений пути извлекаются множественные значения из отдельных экземпляров XML, может принести пользу кластеризация путей в пределах каждого экземпляра XML в индекс PROPERTY. Этот сценарий обычно имеет место при работе с наборами свойств, когда извлекаются свойства объекта и известно значение его первичного ключа.
Если запрашиваются значения экземпляров XML, не зная имен элементов или атрибутов, содержащих эти значения, следует подумать о создании индекса VALUE. Обычно это случается при поисках по осям потомков, например //author[last-name="Howard"], где элементы <author> могут встретиться на любом уровне иерархии. Кроме того, такое случается при обработке запросов с подстановочными знаками, например /book [@* = "novel"], где в запросе выполняется поиск элементов <book>, имеющих некоторый атрибут со значением "novel".

вторичный индекс PATH типа данных XML;

Если обычно запросы задают выражения пути для столбцов типа данных xml , вторичный индекс PATH может ускорить их поиск. Как отмечалось ранее в этой статье, первичный индекс полезен в тех запросах, где метод exist() указан в предложении WHERE. Добавление вторичного индекса PATH может еще более повысить производительность поиска в таких запросах.

Хотя первичный XML-индекс позволяет избежать на стадии выполнения запроса разбора больших двоичных объектов типа данных XML, он не в состоянии обеспечить максимальную производительность запросов на основе выражений пути. Так как все строки первичного XML-индекса, соответствующие большому двоичному объекту XML, просматриваются последовательно, такой поиск работает довольно медленно. В таких случаях наличие вторичного индекса, построенного для значений путей и узлов первичного индекса, может существенно ускорить поиск в нем. Во вторичном индексе PATH значения пути и узлов являются ключевыми столбцами, позволяющими выполнять более эффективный поиск путей. Оптимизатор запросов может использовать индекс PATH, например для следующих выражений:

/root/Location , что задает только путь

ИЛИ

/root/Location/@LocationID[.="10"] , где заданы как значение пути, так и значение узла.

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

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.query('
  /PD:ProductDescription/PD:Summary
') AS Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1;

В этом запросе в методе /PD:ProductDescription/@ProductModelID выражение пути "19" и значение exist() соответствуют ключевым полям индекса PATH. Это позволяет выполнять поиск непосредственно в индексе PATH и при этом обеспечивает более высокую производительность, чем при последовательном переборе значений пути в первичном индексе.

вторичный индекс VALUE типа данных XML;

Если запрос основан на значении, например /Root/ProductDescription/@*[. = "Mountain Bike"] или //ProductDescription[@Name = "Mountain Bike"], а путь задан неполностью либо включает в себя подстановочный знак, скорость выполнения запросов можно повысить, построив вторичный XML-индекс на значениях узлов первичного XML-индекса.

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

//author[LastName="someName"], где вам известно значение элемента <LastName>, но родительский элемент <author> может встретиться где угодно.
/book[@* = "someValue"], где запрос выполняет поиск элемента <book> с каким-либо атрибутом, имеющим значение "someValue".

Следующий запрос возвращает столбец ContactID из таблицы Contact . Предложение WHERE задает фильтр, выполняющий поиск значений в столбце AdditionalContactInfo типа xml. Идентификаторы контактов возвращаются только тогда, когда соответствующий большой двоичный объект XML, содержащий дополнительные контактные данные, включает в себя определенный номер телефона. Поскольку элемент telephoneNumber может находиться в любом месте XML, выражение пути задает ось descendent-or-self.

;WITH XMLNAMESPACES (
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo' AS CI,
  'https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes' AS ACT
)

SELECT ContactID
FROM Person.Contact
WHERE AdditionalContactInfo.exist('//ACT:telephoneNumber/ACT:number[.="111-111-1111"]') = 1;

В этой ситуации искомое значение для <number> известно, но оно может находиться в любом месте экземпляра XML как дочерний элемент элемента telephoneNumber. Производительность запроса такого рода может повыситься при поиске указанного значения по индексу.

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

Производительность запросов, извлекающих одно или несколько значений из отдельных экземпляров XML, может повыситься при использовании индекса PROPERTY. Этот сценарий возникает при извлечении свойств объекта с помощью value() метода xml-типа и когда известно значение первичного ключа объекта.

Индекс PROPERTY строится на столбцах (PK, путь и значение узла) первичного XML-индекса, где PK — это первичный ключ базовой таблицы.

Например, для модели продукта 19следующий запрос извлекает значения атрибутов ProductModelID и ProductModelName при помощи метода value() . Если вместо первичного или вторичных XML-индексов использовать индекс PROPERTY, это может повысить скорость выполнения запросов.

;WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")

SELECT CatalogDescription.value('(/PD:ProductDescription/@ProductModelID)[1]', 'int') AS ModelID,
  CatalogDescription.value('(/PD:ProductDescription/@ProductModelName)[1]', 'varchar(30)') AS ModelName
FROM Production.ProductModel
WHERE ProductModelID = 19;

Создание XML-индекса для столбца с данными типа xml похоже на создание индекса для столбца с данными тип, отличного от xml, за исключением отличий, описанных ниже в этой статье. Следующие инструкции DDL Transact-SQL можно использовать для создания XML-индексов и управления ими:

Сжатие XML

Область применения: SQL Server 2022 (16.x) и более поздних версий, Базы данных SQL Azure и Управляемого экземпляра SQL Azure.

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

Сжатие XML влияет только на данные типа XML. XML-данные сжимаются с помощью алгоритма сжатия Xpress. Все существующие XML-индексы сжимаются через сжатие данных. Если сжатие XML разрешено, сжатие данных включено внутренне для XML-индексов.

Сжатие XML можно включить вместе со сжатием данных в тех же таблицах.

XML-индексы не наследуют свойство сжатия от таблицы. Для сжатия индексов необходимо явно разрешить сжатие XML для XML-индексов.

Вторичные XML-индексы не наследуют свойство сжатия первичного XML-индекса.

По умолчанию при создании XML-индексов параметр сжатия XML для них устанавливается на значение OFF.

Сведения об XML-индексах

Записи XML-индексов появляются в представлении каталога sys.indexes с индексом type3. Столбец name при этом содержит имя XML-индекса.

XML-индексы также записываются в представлении каталога sys.xml_indexes. Он содержит все столбцы sys.indexes и некоторые отдельные столбцы, полезные для работы с XML-индексами. Значение NULL в столбце secondary_type определяет первичный XML-индекс; значения P, R и V — это вторичные XML-индексы PATH, PROPERTY и VALUE соответственно.

Информацию о пространстве, занимаемом XML-индексами, можно получить при помощи функции с табличным значением sys.dm_db_index_physical_stats. Она предоставляет информацию об индексах всех типов, например число занимаемых ими страниц, средний размер строки в байтах и число записей. Эта функция поддерживает и XML-индексы. Получить такие данные можно для каждой секции базы данных. XML-индексы используют ту же схему секционирования и функцию секционирования базовой таблицы.