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

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureБаза данных SQL в Microsoft Fabric

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

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

• Значения XML довольно большие, а извлекаемые части довольно небольшие. Создание индекса позволяет избежать разбора всех данных во время выполнения и ускоряет поиск по индексу, обеспечивая эффективную обработку запросов.

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

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

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

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

Экземпляры XML хранятся в столбцах типа xml в виде больших двоичных объектов (BLOB). Эти XML-экземпляры могут быть большими, а сохраняемое двоичное представление экземпляров типа данных 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 . Для каждого XML-объекта большого двоичного типа (BLOB) в столбце индекс создает несколько строк данных. Количество строк в индексе приблизительно равно числу узлов в XML BLOB-объекте. Когда запрос извлекает полный экземпляр 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-индекс. Вот эти типы:

• вторичный XML-индекс PATH

• вторичный XML-индекс VALUE

• вторичный XML-индекс свойства

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

• Если в вашей рабочей нагрузке активно используются выражения пути в XML-столбцах, вторичный XML-индекс PATH, скорее всего, ускорит её выполнение. Чаще всего используется exist() метод для XML-столбцов в предложении WHERE Transact-SQL.

• Если рабочая нагрузка извлекает несколько значений из отдельных экземпляров XML с помощью выражений пути, может быть полезной кластеризация путей в пределах каждого экземпляра XML в индексе PROPERTY. Этот сценарий обычно имеет место при работе с наборами свойств, когда извлекаются свойства объекта и известно значение его первичного ключа.

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

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

Если обычно запросы задают выражения пути для столбцов типа данных 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 и при этом обеспечивает более высокую производительность, чем при последовательном переборе значений пути в первичном индексе.

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

Если запрос основан на значении, например /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-документа, в выражении пути используется ось descendant-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-индексов и управления ими:

• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)

Сжатие XML

Область применения: SQL Server 2022 (16.x) и более поздних версий, а также в базе данных SQL Azure, базе данных SQL в Microsoft Fabric и Управляемом экземпляре 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 с индексом type для 3. Столбец 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-индексы используют ту же схему секционирования и функцию секционирования базовой таблицы.

Следующие шаги

• Создание XML-индексов
• ALTER INDEX (Выборочные XML-индексы)
• sys.dm_db_index_physical_stats (Transact-SQL)
• XML-данные (SQL Server)