Поделиться через

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

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

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

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

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

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

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

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

Замечание

Невозможно создать или изменить XML-индекс, если параметры базы данных не заданы правильно для работы с типом xml данных. Дополнительные сведения см. в разделе "Использование Full-Text поиска с xml-столбцами".

Экземпляры 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 предложении, в каждой строке таблицы Production.ProductModel XML-двоичный большой объект (BLOB) измельчается во время выполнения. Затем вычисляется выражение (/PD:ProductDescription/@ProductModelID[.="19"]) в методе exist() . Это разбиение данных во время выполнения программы может быть затратным в зависимости от размера данных и количества экземпляров, хранящихся в столбце.

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

Основной XML-индекс

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

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

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

  • Имя тега, например элемент или имя атрибута.

  • Значение узла.

  • Тип узла, например узел элемента, узел атрибута или текстовый узел.

  • Сведения о заказе документов, представленные внутренним идентификатором узла.

  • Путь от каждого узла к корню XML-дерева. Этот столбец выполняет поиск выражений пути в запросе.

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

Эта информация узла используется для оценки и создания xml-результатов для указанного запроса. В целях оптимизации имя тега и сведения о типе узла кодируются в виде целых значений, а столбец Path использует ту же кодировку. Кроме того, пути хранятся в обратном порядке, чтобы разрешить сопоставление путей, если известно только суффикс пути. Рассмотрим пример.

  • //ContactRecord/PhoneNumber где известны только последние два шага

ИЛИ

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

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

Например, следующий запрос возвращает сводную информацию, хранящуюся в CatalogDescription``xml столбце 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 ResultFROM Production.ProductModelWHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

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

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

USE AdventureWorks2012;SELECT InstructionsFROM Production.ProductModel WHERE ProductModelID=7;

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

Чтобы повысить производительность поиска, можно создать вторичные XML-индексы. Прежде чем создавать вторичные индексы, необходимо сначала создать первичный XML-индекс. Это типы:

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

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

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

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

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

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

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

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

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

Хотя первичный XML-индекс позволяет избежать разбиения больших двоичных объектов 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-индекса. Если рабочая нагрузка включает запросы к значениям из экземпляров XML, не зная имен элементов или атрибутов, содержащих значения, может оказаться полезным индекс VALUE. Например, следующее выражение будет использовать индекс 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. Этот сценарий возникает при извлечении свойств объекта с помощью метода value() типа и когда известно значение первичного xml ключа объекта.

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

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

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-индексов и управления ими можно использовать следующие Transact-SQL инструкции DDL:

Получение сведений о XML-индексах

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

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

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

См. также

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