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

Статья
06/13/2017

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

Часто выполняются запросы 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.)

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

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")SELECT CatalogDescription.query('  /PD:ProductDescription/PD:Summary') as ResultFROM Production.ProductModelWHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

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

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

USE AdventureWorks2012;SELECT InstructionsFROM 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"], где запрос ищет <элементы книги> , имеющие атрибут со значением "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, выражение пути указывает ось потомков или я.

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. Этот сценарий происходит при извлечении свойств объекта с помощью метода xmlvalue() типа и при известном значении первичного ключа объекта.

Индекс PROPERTY строится по столбцам (PK, Path и значении узла) первичного 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

XML-индексы входят в представление каталога sys.indexes с индексом типа 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-индексы используют ту же схему секционирования и функцию секционирования базовой таблицы.

См. также:

sys.dm_db_index_physical_stats (Transact-SQL)
XML-данные (SQL Server)

Share via