Индексы для столбцов типа данных 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-индекса поддерживаются вторичные индексы трех типов: PATH, VALUE и PROPERTY. Эти вторичные индексы могут способствовать повышению производительности выполнения разных типов запросов.

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

Первичный XML-индекс — это разобранное и сохраненное представление XML-объектов BLOB, содержащихся в столбце типа данных xml. Для каждого большого двоичного объекта (BLOB) столбца типа данных 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 Result
FROM Production.ProductModel
WHERE CatalogDescription.exist ('/PD:ProductDescription/PD:Features') = 1

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

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

USE AdventureWorks;

SELECT Instructions
FROM Production.ProductModel 
WHERE ProductModelID=7;

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

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

  • Вторичный индекс PATH типа данных XML
  • Вторичный индекс VALUE типа данных XML
  • Вторичный индекс PROPERTY типа данных XML

Вторичный индекс 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

В этом запросе в методе exist() выражение пути /PD:ProductDescription/@ProductModelID и значение "19" соответствуют ключевым полям индекса 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, 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. Для создания XML-индексов и управления ими могут использоваться следующие DDL-инструкции Transact-SQL:

Создание первичного XML-индекса

Для создания первичного XML-индекса используется DDL-инструкция Transact-SQL CREATE PRIMARY XML INDEX. Для XML-индексов поддерживаются не все параметры, доступные для обычных индексов.

При создании XML-индекса следует учитывать следующее:

  • Для создания первичного XML-индекса таблица, содержащая индексируемый XML-столбец и называемая базовой таблицей, должна иметь кластеризованный индекс первичного ключа. Это гарантирует, что в случае секционирования базовой таблицы первичный XML-индекс может быть секционирован при использовании той же схемы и той же функции секционирования.
  • Если XML-индекс уже существует, кластеризованный первичный ключ таблицы не может быть изменен. Перед изменением первичного ключа необходимо удалить все XML-индексы, созданные для таблицы.
  • Первичный XML-индекс можно создать только для столбца типа данных xml. Никакой другой тип индекса, в котором столбец типа данных XML является ключевым, создать нельзя. Однако столбец типа xml может быть включен в обычный (не XML) индекс. Каждый столбец типа xml в таблице может иметь собственный первичный XML-индекс. Однако для столбца типа xml допустим только один первичный XML-индекс.
  • XML-индексы существуют в том же пространстве имен, что и обычные индексы. Поэтому для таблицы не могут быть определены обычный и XML-индекс с одинаковыми именами.
  • Параметры IGNORE_DUP_KEY и ONLINE для XML-индексов всегда должны устанавливаться в OFF. Можно указывать эти параметры со значением OFF.
  • Сведения о файловых группах и секционировании пользовательской таблицы применимы к XML-индексам. Однако пользователи не могут задавать их для XML-индекса отдельно.
  • Параметр DROP_EXISTING может использоваться для удаления и создания нового первичного XML-индекса или для удаления и создания нового вторичного XML-индекса. Однако нельзя удалить вторичный XML-индекс для создания первичного и наоборот.
  • На имена первичных XML-индексов накладываются те же ограничения, что и на имена представлений.

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

  • Для изменения столбца типа xml с помощью параметра ALTER TABLE ALTER COLUMN с нетипизированного на типизированный XML (или наоборот) для этого столбца не должно быть определено никаких XML-индексов. Если такой индекс существует, он должен быть сначала удален.
  • При создании XML-индекса параметр ARITHABORT должен быть установлен в значение ON. Для запроса, вставки, удаления или обновления значений в столбце XML методами типа данных XML этот параметр должен быть установлен для соединения. В противном случае методы типа данных XML будут завершаться ошибкой.
    ms191497.note(ru-ru,SQL.90).gifПримечание.
    Сведения об XML-индексе доступны через представления каталога, но процедура sp_helpindex для них не поддерживается. Ниже в этом подразделе содержатся примеры, которые демонстрируют, какие следует выполнить запросы к представлениям каталога, чтобы получить сведения об XML-индексе.

Создание вторичного XML-индекса

Для создания вторичного XML-индекса необходимого типа используется DDL-инструкция Transact-SQL CREATE XML INDEX.

При создании вторичных XML-индексов следует учитывать следующее:

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

Для получения сведений об XML-индексе можно выполнить запрос к представлению каталога sys.xml_indexes. Обратите внимание, что столбец secondary_type_desc в представлении каталога sys.xml_indexes указывает тип вторичного индекса:

SELECT  * 
FROM    sys.xml_indexes

Столбец secondary_type_desc может возвращать значения NULL, PATH, VALUE или PROPERTY. Для первичного XML-индекса всегда возвращается значение NULL.

Изменение XML-индекса

Для изменения существующих XML-индексов и индексов других типов может применяться DDL-инструкция Transact-SQL ALTER INDEX. Однако к XML-индексам могут применяться не все параметры ALTER INDEX. В частности, недопустимо использование следующих параметров:

  • Параметр повторного создания и установки IGNORE_DUP_KEY для XML-индексов недопустим. Для вторичных XML-индексов параметр повторного создания ONLINE должен быть установлен в OFF. В инструкции ALTER INDEX недопустим параметр DROP_EXISTING. При перестроении индекса параметры соединения должны быть установлены так, как это описано в разделе Установка параметров (XML-индекс).
  • Изменения ограничения первичного ключа в пользовательской таблице на XML-индексы автоматически не распространяются. Пользователь должен вначале удалить, а затем вновь создать XML-индекс.
  • Инструкция ALTER INDEX ALL применяется как к обычным, так и к XML-индексам. Если заданы параметры индексирования, недопустимые для обоих типов индексов, то вся инструкция завершится ошибкой.

Удаление XML-индекса

Для удаления существующих первичных или вторичных XML-индексов и индексов других типов может использоваться инструкция Transact-SQL DROP INDEX. Однако к XML-индексам не применяется ни один из параметров DROP INDEX. Если удаляется первичный XML-индекс, то вместе с ним удаляются также и все имеющиеся вторичные индексы.

Синтаксис инструкции DROP вида TableName**.**IndexName постепенно вытесняется, и для XML-индексов не поддерживается.

Примеры

В следующих примерах демонстрируется создание, изменение и удаление XML-индексов.

А. Создание и удаление первичного XML-индекса

В следующем примере создается XML-индекс для столбца типа xml.

DROP TABLE T
GO
CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create Primary XML index 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Verify the index creation. 
-- Note index type is 3 for xml indexes.
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol' 
-- Drop the index.
DROP INDEX PIdx_T_XmlCol ON T

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

В следующем примере создается XML-индекс для столбца типа xml. Дополнительные сведения см. в разделе Типизированный и нетипизированный XML.

CREATE TABLE TestTable(
 Col1 int primary key, 
 Col2 xml (Production.ProductDescriptionSchemaCollection)) 
GO

Теперь можно создать первичный XML-индекс для столбца Co12.

CREATE PRIMARY XML INDEX PIdx_TestTable_Col2 
ON TestTable(Col2)
GO

Б. Создание вторичных XML-индексов

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

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary index.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Create secondary indexes (PATH, VALUE, PROPERTY).
CREATE XML INDEX PIdx_T_XmlCol_PATH ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PATH
GO
CREATE XML INDEX PIdx_T_XmlCol_VALUE ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR VALUE
GO
CREATE XML INDEX PIdx_T_XmlCol_PROPERTY ON T(XmlCol)
USING XML INDEX PIdx_T_XmlCol
FOR PROPERTY
GO

Для получения сведений об XML-индексах можно выполнить запрос к представлению каталога sys.xml_indexes. Столбец secondary_type_desc содержит тип вторичного индекса.

SELECT  * 
FROM    sys.xml_indexes

Для получения сведений об индексе можно также выполнить запрос к представлению каталога:

SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')

Можно добавить какие-либо данные, а затем просмотреть сведения об XML-индексе.

INSERT INTO T VALUES (1,
'<doc id="123">
<sections>
<section num="2">
<heading>Background</heading>
</section>
<section num="3">
<heading>Sort</heading>
</section>
<section num="4">
<heading>Search</heading>
</section>
</sections>
</doc>')
GO
-- Check XML index information.
SELECT *
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, NULL, 'DETAILED')
GO
-- Space usage of primary XML index
DECLARE @index_id int
SELECT  @index_id = i.index_id
FROM    sys.xml_indexes i 
WHERE   i.name = 'PIdx_T_XmlCol' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
--- Space usage of secondary XML index (for example PATH secondary index)  PIdx_T_XmlCol_PATH
DECLARE @index_id int
SELECT  @index_id = i.index_id 
FROM    sys.xml_indexes i 
WHERE  i.name = 'PIdx_T_XmlCol_PATH' and object_name(i.object_id) = 'T'
 
SELECT *
FROM sys.dm_db_index_physical_stats (db_id(), object_id('T') , @index_id, DEFAULT, 'DETAILED')
go
 
-- Space usage of all secondary XML indexes for a particular table
SELECT i.name, object_name(i.object_id), stats.* 
FROM   sys.dm_db_index_physical_stats (db_id(), object_id('T'), NULL, DEFAULT, 'DETAILED') stats
JOIN sys.xml_indexes i ON (stats.object_id = i.object_id and stats.index_id = i.index_id)
WHERE secondary_type is not null
-- Drop secondary indexes.
DROP INDEX PIdx_T_XmlCol_PATH ON T
GO
DROP INDEX PIdx_T_XmlCol_VALUE ON T
GO
DROP INDEX PIdx_T_XmlCol_PROPERTY ON T
GO
-- Drop primary index.
DROP INDEX PIdx_T_XmlCol ON T
-- Drop table T.
DROP TABLE T
Go

В. Изменение XML-индекса

В следующем примере создается XML-индекс, после чего производится его изменение — установка параметра ALLOW_ROW_LOCKS в значение OFF. Когда параметр ALLOW_ROW_LOCKS установлен в значение OFF, строки не блокируются и доступ к указанным индексам осуществляется при помощи блокировок уровня страницы и таблицы.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
-- Create primary XML index. 
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlCol)
GO
-- Note the type 3 is index on XML type.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'

-- Modify and set an index option.
ALTER INDEX PIdx_T_XmlCol on T 
SET (ALLOW_ROW_LOCKS = OFF)

Г. Отключение и включение XML-индекса

По умолчанию XML-индекс включен. Если XML-индекс отключен, то запросы, выполняемые для XML-столбца, не пользуются им. Для включения XML-индекса используется инструкция ALTER INDEX с параметром REBUILD.

CREATE TABLE T (Col1 INT PRIMARY KEY, XmlCol XML)
GO
CREATE PRIMARY XML INDEX PIdx_T_XmlCol ON T(XmlCol)
GO
ALTER INDEX PIdx_T_XmlCol on T DISABLE
Go
-- Verify index is disabled.
SELECT *
FROM sys.xml_indexes
WHERE object_id = object_id('T')
AND name='PIdx_T_XmlCol'
-- Rebuild the index.
ALTER INDEX PIdx_T_XmlCol on T REBUILD
Go

Д. Создание XML-индекса с параметром DROP_EXISTING

В следующем примере XML-индекс создается для столбца XmlColx. Затем для другого столбца (XmlColy) создается другой XML-индекс с тем же именем. Так как задан параметр DROP_EXISTING, существующий XML-индекс для столбца (XmlColx) удаляется и вместо него создается новый XML-индекс для столбца (XmlColy).

DROP TABLE T
GO
CREATE TABLE T(Col1 int primary key, XmlColx xml, XmlColy xml)
GO
-- Create XML index on XmlColx.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColx)
GO
-- Create same name XML index on XmlColy.
CREATE PRIMARY XML INDEX PIdx_T_XmlCol 
ON T(XmlColy) 
WITH (DROP_EXISTING = ON)
-- Verify the index is created on XmlColy.d.
SELECT sc.name 
FROM   sys.xml_indexes si inner join sys.index_columns sic 
ON     sic.object_id=si.object_id and sic.index_id=si.index_id
INNER  join sys.columns sc on sc.object_id=sic.object_id 
AND    sc.column_id=sic.column_id
WHERE  si.name='PIdx_T_XmlCol' 
AND    si.object_id=object_id('T')

Запрос возвращает имя столбца, для которого создан XML-индекс.

См. также

Основные понятия

Тип данных xml
Образцы приложений XML

Другие ресурсы

sys.dm_db_index_physical_stats

Справка и поддержка

Получение помощи по SQL Server 2005