xml 数据类型列的索引

  • 项目

XML 实例作为二进制大型对象 (BLOB) 存储在 xml 类型列中。这些 XML 实例可以很大，并且存储的 xml 数据类型实例的二进制表示形式最大可以为 2 GB。如果没有索引，则运行时将拆分这些二进制大型对象以计算查询。此拆分可能非常耗时。例如，请看以下查询：

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

为了选择满足 WHERE 子句中条件的 XML 实例，表 Production.ProductModel 的每行中的 XML 二进制大型对象 (BLOB) 将在运行时拆分。然后，计算 exist() 方法中的表达式 (/PD:ProductDescription/@ProductModelID[.="19"])。此运行时拆分有可能开销较大，这取决于存储在列中的实例的大小和数目。

如果在应用程序环境中经常查询 XML 二进制大型对象 (BLOB)，则对 xml 类型列创建索引很有用。但是，在数据修改过程中维护索引会带来开销。

XML 索引分为下列类别：

  • 主 XML 索引
  • 辅助 XML 索引

xml 类型列的第一个索引必须是主 XML 索引。使用主 XML 索引时，支持下列类型的辅助索引：PATH、VALUE 和 PROPERTY。根据查询类型的不同，这些辅助索引可能有助于改善查询性能。

主 XML 索引

主 XML 索引是 xml 数据类型列中的 XML BLOB 的已拆分和持久的表示形式。对于列中的每个 XML 二进制大型对象 (BLOB)，索引将创建几个数据行。该索引中的行数大约等于 XML 二进制大型对象中的节点数。

每行存储以下节点信息：

  • 标记名（如元素名称或属性名称）。
  • 节点值。
  • 节点类型（如元素节点、属性节点或文本节点）。
  • 文档顺序信息（由内部节点标识符表示）。
  • 从每个节点到 XML 树的根的路径。搜索此列可获得查询中的路径表达式。
  • 基表的主键。基表的主键复制到主 XML 索引中，用于向后和基表进行联接，并且基表的主键中的最大列数限制为 15。

此节点信息用于计算和构造指定查询的 XML 结果。出于优化的目的，标记名和节点类型信息的编码为整数值，而且 Path 列使用同样的编码。另外，路径以相反的顺序存储，以便在仅知道路径后缀的情况下能够匹配路径。例如：

  • //ContactRecord/PhoneNumber，其中只有最后两个步骤是已知的

或

  • /Book/*/Title，其中通配符 (*) 是在表达式中间指定的。

查询处理器使用涉及 xml 数据类型方法的查询的主 XML 索引，并返回主索引自身中的标量值或 XML 子树。（此索引存储重新构造 XML 实例所需的所有信息。）

例如，以下查询将返回 ProductModel 表的 CatalogDescriptionxml 类型列中存储的摘要信息。只有当目录说明中还存储有 <Features> 说明，该查询才会返回此产品样式的 <Summary> 信息。

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 索引，为 exist() 方法中指定的表达式按顺序搜索与每个 XML 二进制大型对象相对应的索引中的行，而不是拆分基表中的每个 XML 二进制大型对象实例。如果路径是在索引中的 Path 列中找到的，则从主 XML 索引检索 <Summary> 元素及其子树，并将其转换为 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 辅助索引可以提高搜索的速度。如本主题前面所述，当查询在 WHERE 子句中指定 exist() 方法时主索引非常有用。如果添加 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

在该查询中，exist() 方法中的路径表达式 /PD:ProductDescription/@ProductModelID 和值 "19" 对应于 PATH 索引的键字段。这便允许在 PATH 索引中直接查找，并为主索引中的路径值提供比按顺序搜索更好的搜索性能。

VALUE 辅助 XML 索引

如果查询是基于值的查询，例如 /Root/ProductDescription/@*[. = "Mountain Bike"] 或 //ProductDescription[@Name = "Mountain Bike"]，且没有完全指定路径或路径包含有通配符，则生成基于主 XML 索引中的节点值所创建的辅助 XML 索引可以更快地获得结果。

VALUE 索引的键列是主 XML 索引的节点值和路径。如果您的工作负荷涉及到查询 XML 实例中的值，但不知道包含这些值的元素名称或属性名称，则 VALUE 索引非常有用。例如，以下表达式受益于 VALUE 索引：

  • //author[LastName="someName"]，其中 <LastName> 元素的值已知，但是 <author> 父级可以出现在任何地方。
  • /book[@* = "someValue"]，其中查询将查找包含值为 "someValue" 的属性的 <book> 元素。

以下查询从 Contact 表中返回 ContactID。WHERE 子句指定一个筛选器，该筛选器查找 AdditionalContactInfoxml 类型列中的值。只有当相应的其他联系信息 XML 二进制大型对象包含具体的电话号码时，才会返回联系 ID。由于 <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> 的搜索值是已知的，但是它可以作为 <telephoneNumber> 元素的子级在 XML 实例中的任意位置出现。这种查询特别适用基于特定值的索引查找。

PROPERTY 辅助索引

从单个 XML 实例检索一个或多个值的查询适用 PROPERTY 索引。当使用 xml 类型的 value() 方法检索对象属性并且知道对象的主键值时，会发生这种情况。

PROPERTY 索引是对主 XML 索引的列（PK、Path 和节点值）创建的，其中 PK 是基表的主键。

例如，对于产品样式 19，以下查询使用 value() 方法检索 ProductModelID 属性值和 ProductModelName 属性值。使用 PROPERTY 索引代替主 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 索引和对非 xml 类型列创建索引类似。可以使用下列 Transact-SQL DDL 语句创建和管理 XML 索引：

创建主 XML 索引

若要创建主 XML 索引，请使用 CREATE PRIMARY XML INDEX Transact-SQL DDL 语句。XML 索引不完全支持非 XML 索引的所有选项。

创建 XML 索引时注意下列事项：

  • 若要创建主 XML 索引，含有被索引的 XML 列的表（称为基表）必须具有主键的聚集索引。这就确保如果对基表进行了分区，便可以使用相同的分区架构和分区函数对主 XML 索引进行分区。
  • 如果存在 XML 索引，则不能修改基表的聚集主键。在修改主键之前，必须删除表的所有 XML 索引。
  • 您可以对单个 xml 类型列创建主 XML 索引。但不能为作为键列的 XML 类型列创建任何其他类型的索引。但是，可以将 xml L 类型列包含在非 XML 索引中。表中的每个 xml 类型列可以有自己的主 XML 索引。但是，一个 xml 类型列只允许有一个主 XML 索引。
  • XML 索引和非 XML 索引存在于相同的命名空间中。因此，同一表的 XML 索引和非 XML 索引不能具有相同的名称。
  • 对于 XML 索引，IGNORE_DUP_KEY 选项和 ONLINE 选项始终设置为 OFF。您可以将这些选项指定为 OFF。
  • 将用户表的文件组和分区信息应用于 XML 索引。用户无法为 XML 索引分别指定这些信息。
  • DROP_EXISTING 索引选项可以删除主 XML 索引并创建一个新的主 XML 索引，或者删除辅助 XML 索引并创建一个新的辅助 XML 索引。但是，此选项不能删除辅助 XML 索引来创建一个新的主 XML 索引，反之亦然。
  • 主 XML 索引名称与视图名称有相同的限制。

不能对视图中的 xml 类型列、xml 类型列的表值变量或 xml 类型变量创建 XML 索引。

  • 若要使用 ALTER TABLE ALTER COLUMN 选项将 xml 类型列从非类型化的 XML 更改为类型化的 XML，或者从类型化的 XML 更改为非类型化的 XML，则列不应存在 XML 索引。如果确实存在，则在尝试更改列类型之前必须删除该索引。
  • 创建 XML 索引时必须将选项 ARITHABORT 设置为 ON。若要使用 XML 数据类型方法查询、插入、删除或更新 XML 列中的值，则必须在连接上设置相同的选项。如果没有设置，则 XML 数据类型方法将会失败。
    ms191497.note(zh-cn,SQL.90).gif注意：
    有关 XML 索引的信息可以在目录视图中找到。但是，不支持 sp_helpindex。本主题后面给出的示例说明了如何查询目录视图来查找 XML 索引信息。

创建辅助 XML 索引

使用 CREATE XML INDEX Transact-SQL DDL 语句可创建辅助 XML 索引并指定所需的辅助 XML 索引类型。

创建辅助 XML 索引时注意下列事项：

  • 除了 IGNORE_DUP_KEY 和 ONLINE 之外，允许对辅助 XML 索引使用所有适用于非聚集索引的索引选项。对于辅助 XML 索引，这两个选项必须始终设置为 OFF。
  • 辅助索引的分区方式类似于主 XML 索引。
  • DROP_EXISTING 可以删除用户表的辅助索引并为用户表创建其他辅助索引。

可以查询 sys.xml_indexes 目录视图来检索 XML 索引信息。注意，sys.xml_indexes 目录视图的 secondary_type_desc 列中提供辅助索引的类型：

SELECT  * 
FROM    sys.xml_indexes

secondary_type_desc 列中返回的值可以是 NULL、PATH、VALUE 或 PROPERTY。对于主 XML 索引而言，返回的值为 NULL。

修改 XML 索引

ALTER INDEX Transact-SQL DDL 语句可用于修改现有的 XML 索引和非 XML 索引。但是，并非所有的 ALTER INDEX 选项都适用于 XML 索引。修改 XML 索引时以下选项不可用：

  • 对于 XML 索引，重新生成和设置选项 IGNORE_DUP_KEY 无效。对于辅助 XML 索引，重新生成选项 ONLINE 必须设置为 OFF。在 ALTER INDEX 语句中，不允许 DROP_EXISTING 选项。重新生成索引时，连接选项必须如设置选项（XML 索引）中所述进行设置。
  • 对用户表中的主键约束进行的修改不会自动传播到 XML 索引中。用户必须首先删除 XML 索引，然后再重新创建它们。
  • 如果指定了 ALTER INDEX ALL，则它将应用于非 XML 索引和 XML 索引。指定的索引选项可能对两种索引无效。在这种情况下，整个语句将失败。

删除 XML 索引

DROP INDEX Transact-SQL 语句可用于删除现有的主（或辅助）XML 索引和非 XML 索引。但是，任何 DROP INDEX 选项都不会应用于 XML 索引。如果删除主 XML 索引，则会删除任何现有的辅助索引。

以后将逐步停止使用带有 TableName**.**IndexName 的 DROP 语法，并且 XML 索引不支持该语法。

示例

以下示例说明了创建、修改以及删除的 XML 索引。

A. 创建和删除主 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

此时，可以对 Co12 创建主 XML 索引。

CREATE PRIMARY XML INDEX PIdx_TestTable_Col2 
ON TestTable(Col2)
GO

B. 创建辅助 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

您可以查询 sys.xml_indexes 来检索 XML 索引信息。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

C. 修改 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)

D. 禁用和启用 XML 索引

默认情况下，启用 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

E. 使用 DROP_EXISTING 索引选项创建 XML 索引

在以下示例中，XML 索引是针对列 (XmlColx) 创建的。然后，针对不同的列 (XmlColy) 创建同名的另一个 XML 索引。由于指定了 DROP_EXISTING 选项，因此将删除 (XmlColx) 现有的 XML 索引并创建新的 (XmlColy) XML 索引。

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 帮助