XML 索引 (SQL Server)XML Indexes (SQL Server)

適用於: 是SQL Server否Azure SQL Database否Azure Synapse Analytics (SQL DW)否平行處理資料倉儲APPLIES TO: YesSQL Server NoAzure SQL Database NoAzure Synapse Analytics (SQL DW) NoParallel Data Warehouse

可以在 xml 資料類型資料行上建立 XML 索引。XML indexes can be created on xml data type columns. 它們會在資料行中為整個 XML 執行個體的所有標記、值和路徑編制索引,進而提高查詢效能。They index all tags, values and paths over the XML instances in the column and benefit query performance. 在下列情況下,您的應用程式可從 XML 索引獲益:Your application may benefit from an XML index in the following situations:

  • 在您的工作負載中,經常會查詢 XML 資料行。Queries on XML columns are common in your workload. 必須將資料修改期間的 XML 索引維護成本納入考量。XML index maintenance cost during data modification must be considered.

  • 您的 XML 值相對較大,而所擷取的部份相對較小。Your XML values are relatively large and the retrieved parts are relatively small. 建立索引可避免在執行階段剖析整份資料,並有助於索引查閱,增進查詢處理的效率。Building the index avoids parsing the whole data at run time and benefits index lookups for efficient query processing.

XML 索引可分成下列類別:XML indexes fall into the following categories:

  • 主要 XML 索引Primary XML index

  • 次要 XML 索引Secondary XML index

xml 類型資料行上的第一個索引必須是主要 XML 索引。The first index on the xml type column must be the primary XML index. 使用主要 XML 索引時,可支援下列次要索引類型:PATH、VALUE 及 PROPERTY。Using the primary XML index, the following types of secondary indexes are supported: PATH, VALUE, and PROPERTY. 視查詢類型而定,這些次要索引可協助改善查詢效能。Depending on the type of queries, these secondary indexes might help improve query performance.


除非您已正確設定資料庫選項使其可以處理 xml 資料類型,否則無法建立或修改 XML 索引。You cannot create or modify an XML index unless the database options are set correctly for working with the xml data type. 如需詳細資訊,請參閱 使用 XML 資料行進行全文檢索搜尋For more information, see Use Full-Text Search with XML Columns.

XML 執行個體是以大型二進位物件 (BLOB) 儲存在 xml 類型資料行中。XML instances are stored in xml type columns as large binary objects (BLOBs). 這些 XML 執行個體可以是大型的,而且所儲存之 xml 資料類型執行個體的二進位表示法最多可達 2 GB。These XML instances can be large, and the stored binary representation of xml data type instances can be up to 2 GB. 如果沒有索引,這些二進位大型物件就會在執行階段切割,以便評估查詢。Without an index, these binary large objects are shredded at run time to evaluate a query. 這項切割作業可能會很費時。This shredding can be time-consuming. 例如,請考慮下列查詢:For example, consider the following query:

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")  
SELECT CatalogDescription.query('  
') as Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1  

為了選取符合 WHERE 子句中條件的 XML 執行個體,在執行階段會切割 Production.ProductModel 資料表之每個資料列中的 XML 二進位大型物件 (BLOB)。To select the XML instances that satisfy the condition in the WHERE clause, the XML binary large object (BLOB) in each row of table Production.ProductModel is shredded at run time. 然後,便評估 (/PD:ProductDescription/@ProductModelID[.="19"]方法中的運算式 exist() )。Then, the expression (/PD:ProductDescription/@ProductModelID[.="19"]) in the exist() method is evaluated. 此執行階段的切割可能會非常費時,端視資料行中所儲存的執行個體之大小與數目而定。This run-time shredding can be costly, depending on the size and number of instances stored in the column.

如果查詢 XML 二進位大型物件 (BLOB) 常在您的應用程式環境中發生,它將可協助索引 xml 類型資料行。If querying XML binary large objects (BLOBs) is common in your application environment, it helps to index the xml type columns. 但是,在修改資料期間會有維護索引的相關成本。However, there is a cost associated with maintaining the index during data modification.

主要 XML 索引Primary XML Index

主要 XML 索引會在 XML 資料行中檢索 XML 執行個體內的所有標記、值與路徑。The primary XML index indexes all tags, values, and paths within the XML instances in an XML column. 若要建立主要 XML 索引,出現 XML 資料行之資料表必須在資料表的主索引鍵上,具有叢集索引。To create a primary XML index, the table in which the XML column occurs must have a clustered index on the primary key of the table. SQL ServerSQL Server 使用此主索引鍵,以將主要 XML 索引中的資料列與內含 XML 資料行之資料表中的資料列相互關聯。uses this primary key to correlate rows in the primary XML index with rows in the table that contains the XML column.

主要 XML 索引是 xml 資料類型資料行中,XML BLOB 的切割和保存的表示法。The primary XML index is a shredded and persisted representation of the XML BLOBs in the xml data type column. 對於資料行中的每個 XML 二進位大型物件 (BLOB) 而言,索引可建立幾個資料列。For each XML binary large object (BLOB) in the column, the index creates several rows of data. 索引中的資料列數目大約等於 XML 二進位大型物件中的節點數目。The number of rows in the index is approximately equal to the number of nodes in the XML binary large object. 當查詢擷取完整 XML 執行個體時, SQL ServerSQL Server 會從 XML 資料行提供此執行個體。When a query retrieves the full XML instance, SQL ServerSQL Server provides the instance from the XML column. XML 執行個體中的查詢會使用主要 XML 索引,並使用索引本身來傳回純量值或 XML 子樹。Queries within XML instances use the primary XML index, and can return scalar values or XML subtrees by using the index itself.

每個資料列都會儲存下列節點資訊:Each row stores the following node information:

  • 類似元素或屬性名稱的標記名稱。Tag name such as an element or attribute name.

  • 節點值。Node value.

  • 如元素節點、屬性節點或文字節點等節點類型。Node type such as an element node, attribute node, or text node.

  • 文件順序資訊,以內部節點識別碼表示。Document order information, represented by an internal node identifier.

  • 從每個節點至 XML 樹狀結構根節點的路徑。Path from each node to the root of the XML tree. 在查詢中會為路徑運算式搜尋資料行。This column is searched for path expressions in the query.

  • 基底資料表的主索引鍵。Primary key of the base table. 基底資料表的主索引鍵會在主要 XML 索引中重複,以利向後聯結基底資料表,而基底資料表主索引鍵中資料行的最大數目是限定為 15。The primary key of the base table is duplicated in the primary XML index for a back join with the base table, and the maximum number of columns in the primary key of the base table is limited to 15.

此節點資訊是用以評估和建構指定查詢的 XML 結果。This node information is used to evaluate and construct XML results for a specified query. 為了達到最佳化,標記名稱與節點類型資訊將會編碼成整數值,而 Path 資料行則會使用相同的編碼。For optimization purposes, the tag name and the node type information are encoded as integer values, and the Path column uses the same encoding. 另外,當只知道路徑後置詞時,會以相反順序儲存路徑以允許比對路徑。Also, paths are stored in reverse order to allow matching paths when only the path suffix is known. 例如:For example:

  • //ContactRecord/PhoneNumber 只知道最後兩個步驟//ContactRecord/PhoneNumber where only the last two steps are known


  • /Book/*/Title 於運算式的中間指定了萬用字元 (*)。/Book/*/Title where the wildcard character (*) is specified in the middle of the expression.

查詢處理器會使用主要 XML 索引來進行包含 xml 資料類型方法 的查詢,並從主要索引本身傳回純量值或 XML 子樹。The query processor uses the primary XML index for queries that involve xml Data Type Methods and returns either scalar values or the XML subtrees from the primary index itself. (這個索引會儲存重新建構 XML 執行個體的所有必要資訊)。(This index stores all the necessary information to reconstruct the XML instance.)

例如,下列查詢會傳回儲存在 CatalogDescriptionxml 類型資料行 ProductModel 資料表中的摘要資訊。For example, the following query returns summary information stored in the CatalogDescriptionxml type column in the ProductModel table. 此查詢只會針對目錄描述也儲存 <Summary> 描述的產品型號傳回其 <Features> 資訊。The query returns <Summary> information only for product models whose catalog description also stores the <Features> description.

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 二進位大型物件相對應的資料列。With regard to the primary XML index, instead of shredding each XML binary large object instance in the base table, the rows in the index that correspond to each XML binary large object are searched sequentially for the expression specified in the exist() method. 如果在索引中的 Path 資料行找到了路徑,就會從主要 XML 索引擷取 <Summary> 元素及其子樹,並將其轉換為 XML 二進位大型物件,當做 query() 方法的結果。If the path is found in the Path column in the index, the <Summary> element together with its subtrees is retrieved from the primary XML index and converted into an XML binary large object as the result of the query() method.

請注意,在擷取完整 XML 執行個體時不會使用主要 XML 索引。Note that the primary XML index is not used when retrieving a full XML instance. 例如,下列查詢會從資料表擷取整個 XML 執行個體,該執行個體描述了特定產品型號的製造指示。For example, the following query retrieves from the table the whole XML instance that describes the manufacturing instructions for a specific product model.

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

次要 XML 索引Secondary XML Indexes

若要增強搜尋效能,您可以建立次要的 XML 索引。To enhance search performance, you can create secondary XML indexes. 在建立次要索引之前,必須先有主要 XML 索引存在。A primary XML index must first exist before you can create secondary indexes. 以下為其類型:These are the types:

  • PATH 次要 XML 索引PATH secondary XML index

  • VALUE 次要 XML 索引VALUE secondary XML index

  • PROPERTY 次要 XML 索引PROPERTY secondary XML index

以下是建立一或多個次要索引的一些指導方針:Following are some guidelines for creating one or more secondary indexes:

  • 如果您的工作負載在 XML 資料行上大量使用路徑運算式,PATH 次要 XML 索引就可能會加速您的工作負載。If your workload uses path expressions significantly on XML columns, the PATH secondary XML index is likely to speed up your workload. 最常見的情況是將 exist() 方法使用在 Transact-SQL 的 WHERE 子句中的 XML 資料行上。The most common case is the use of the exist() method on XML columns in the WHERE clause of Transact-SQL.

  • 如果您的工作負載使用路徑運算式,從個別的 XML 執行個體中擷取多個值,則在 PROPERTY 索引中將路徑叢集在每個 XML 執行個體中,可能會有所幫助。If your workload retrieves multiple values from individual XML instances by using path expressions, clustering paths within each XML instance in the PROPERTY index may be helpful. 當物件的屬性被提取,且已知其主索引鍵值時,此案例通常會發生在屬性包案例中。This scenario typically occurs in a property bag scenario when properties of an object are fetched and its primary key value is known.

  • 如果您的工作負載需要查詢 XML 執行個體中的值,但您不知道含有那些值的元素或屬性名稱,您可能會想要建立 VALUE 索引。If your workload involves querying for values within XML instances without knowing the element or attribute names that contain those values, you may want to create the VALUE index. 這通常會發生在子系座標軸查閱,例如 //author[last-name="Howard"],其中 <author> 項目可出現在階層中的任何層級。This typically occurs with descendant axes lookups, such as //author[last-name="Howard"], where <author> elements can occur at any level of the hierarchy. 這也會發生在萬用字元查詢中,例如 /book [@* = "novel"],此查詢是要尋找屬性中具有 "novel" 值的 <book> 項目。It also occurs in wildcard queries, such as /book [@* = "novel"], where the query looks for <book> elements that have some attribute having the value "novel".

PATH 次要 XML 索引PATH Secondary XML Index

如果您的查詢通常會在 xml 類型資料行上指定路徑運算式,則使用 PATH 次要索引將可使搜尋速度變快。If your queries generally specify path expressions on xml type columns, a PATH secondary index may be able to speed up the search. 如本主題前面所述,當您的查詢在 WHERE 子句中指定 exist() 方法時,主索引就非常有用。As described earlier in this topic, the primary index is helpful when you have queries that specify exist() method in the WHERE clause. 如果您加入 PATH 次要索引,也可以改善這類查詢的搜尋效能。If you add a PATH secondary index, you may also improve the search performance in such queries.

雖然主要 XML 索引可避免必須在執行階段切割 XML 二進位大型物件,但是它可能無法為以路徑運算式為基礎的查詢提供最佳的效能。Although a primary XML index avoids having to shred the XML binary large objects at run time, it may not provide the best performance for queries based on path expressions. 由於會針對大型 XML 執行個體,循序搜尋與 XML 二進位大型物件相對應的主要 XML 索引中的所有資料列,因此循序搜尋可能會很慢。Because all rows in the primary XML index corresponding to an XML binary large object are searched sequentially for large XML instances, the sequential search may be slow. 在此情況下,將次要索引建立在主要索引的路徑值與節點值上,將可大幅增加索引搜尋的速度。In this case, having a secondary index built on the path values and node values in the primary index can significantly speed up the index search. 在 PATH 次要索引中,路徑與節點值都是索引鍵資料行,可在搜尋路徑時進行更有效率的搜尋。In the PATH secondary index, the path and node values are key columns that allow for more efficient seeks when searching for paths. 查詢最佳化工具可以針對如下列所示的運算式使用 PATH 索引:The query optimizer may use the PATH index for expressions such as those shown in the following:

  • /root/Location 其僅指定路徑/root/Location which specify only a path


  • /root/Location/@LocationID[.="10"] 其中同時指定了路徑與節點值。/root/Location/@LocationID[.="10"] where both the path and the node value are specified.

下列查詢顯示 PATH 索引非常有用:The following query shows where the PATH index is helpful:

WITH XMLNAMESPACES ('https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS "PD")  
SELECT CatalogDescription.query('  
') AS Result  
FROM Production.ProductModel  
WHERE CatalogDescription.exist ('/PD:ProductDescription/@ProductModelID[.="19"]') = 1  

在查詢中, /PD:ProductDescription/@ProductModelID 方法中的路徑運算式 "19"exist() 值會對應至 PATH 索引的索引鍵欄位。In the query, the path expression /PD:ProductDescription/@ProductModelID and value "19" in the exist() method correspond to the key fields of the PATH index. 這允許在 PATH 索引中進行直接搜尋,並對主索引中的路徑值提供比循序搜尋更佳的搜尋效能。This allows for direct seek in the PATH index and provides better search performance than the sequential search for path values in the primary index.

VALUE 次要 XML 索引VALUE Secondary XML Index

如果查詢是以值為基礎,例如, /Root/ProductDescription/@*[. = "Mountain Bike"]//ProductDescription[@Name = "Mountain Bike"],且並未完整指定路徑,或是路徑中包含萬用字元,您可以透過在主要 XML 索引的節點值上建立次要 XML 索引,以獲得更快的結果。If queries are value based, for example, /Root/ProductDescription/@*[. = "Mountain Bike"] or //ProductDescription[@Name = "Mountain Bike"], and the path is not fully specified or it includes a wildcard, you might obtain faster results by building a secondary XML index that is built on node values in the primary XML index.

VALUE 索引的索引鍵資料行是主要 XML 索引的節點值與路徑。The key columns of the VALUE index are (node value and path) of the primary XML index. 如果您的工作負載需要在不知道包含值的元素或屬性名稱的情況下,從 XML 執行個體查詢值,VALUE 索引將會非常有用。If your workload involves querying for values from XML instances without knowing the element or attribute names that contain the values, a VALUE index may be useful. 例如,下列運算式將可從擁有 VALUE 索引而獲益:For example, the following expression will benefit from having a VALUE index:

  • //author[LastName="someName"],其中您知道 <LastName> 元素的值,但是 <author> 父系可存在於任何位置。//author[LastName="someName"] where you know the value of the <LastName> element, but the <author> parent can occur anywhere.

  • /book[@* = "someValue"] 中,查詢會尋找某些屬性中包含值 book 的 <"someValue"> 元素。/book[@* = "someValue"] where the query looks for the <book> element that has some attribute having the value "someValue".

下列查詢會從 ContactID 資料表傳回 ContactThe following query returns ContactID from the Contact table. WHERE 子句可指定篩選,在 AdditionalContactInfoxml 類型資料行中尋找值。The WHERE clause specifies a filter that looks for values in the AdditionalContactInfoxml type column. 如果對應的其他連絡資訊 XML 二進位大型物件包含特定的電話號碼,就會傳回連絡識別碼。The contact IDs are returned only if the corresponding additional contact information XML binary large object includes a specific telephone number. 因為 <telephoneNumber> 元素有可能出現在 XML 的任何位置,所以路徑運算式會指定 descendent-or-self 軸。Because the <telephoneNumber> element may appear anywhere in the XML, the path expression specifies the descendent-or-self axis.

  '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 執行個體中的任何位置。In this situation, the search value for <number> is known, but it can appear anywhere in the XML instance as a child of the <telephoneNumber> element. 此類的查詢可從以特定值為基礎的索引查閱獲益。This kind of query might benefit from an index lookup based on a specific value.

PROPERTY 次要索引PROPERTY Secondary Index

從個別 XML 執行個體擷取一或多個值的查詢可從 PROPERTY 索引獲益。Queries that retrieve one or more values from individual XML instances might benefit from a PROPERTY index. 當您使用 xml 類型的 value() 方法擷取物件屬性,且物件的主索引鍵值為已知時,就會發生此情況。This scenario occurs when you retrieve object properties by using the value() method of the xml type and when the primary key value of the object is known.

PROPERTY 索引是建立在主要 XML 索引的資料行 (PK、Path 以及節點值) 上,在主要 XML 索引中 PK 是基底資料表的主索引鍵。The PROPERTY index is built on columns (PK, Path and node value) of the primary XML index where PK is the primary key of the base table.

例如,若為產品型號 19,下列查詢就會使用 ProductModelID 方法來擷取 ProductModelNamevalue() 屬性值。For example, for product model 19, the following query retrieves the ProductModelID and ProductModelName attribute values using the value() method. PROPERTY 索引可提供比使用主要 XML 索引或其他次要 XML 索引更快的執行。Instead of using the primary XML index or the other secondary XML indexes, the PROPERTY index may provide faster execution.

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 類型資料行上建立索引類似。Except for the differences described later in this topic, creating an XML index on anxml type column is similar to creating an index on a non-xml type column. 下列 Transact-SQLTransact-SQL DDL 陳述式可用以建立及管理 XML 索引:The following Transact-SQLTransact-SQL DDL statements can be used to create and manage XML indexes:

取得有關 XML 索引的資訊Getting Information about XML Indexes

XML 索引項目會出現在目錄檢視 sys.indexes 中,且具有索引 "type" 3。XML index entries appear in the catalog view, sys.indexes, with the index "type" 3. 名稱資料行包含此 XML 索引的名稱。The name column contains the name of the XML index.

XML 索引也會記錄在目錄檢視 sys.xml_indexes 中。XML indexes are also recorded in the catalog view, sys.xml_indexes. 其中包含 sys.indexes 的所有資料行,以及對 XML 索引有助益的一些特定資料行。This contains all the columns of sys.indexes and some specific ones that are useful for XML indexes. 資料行 secondary_type 中的 NULL 值是指主要 XML 索引;'P'、'R' 和 'V' 這三個值分別代表 PATH、PROPERTY 和 VALUE 次要 XML 索引。The value NULL in the column, secondary_type, indicates a primary XML index; the values 'P', 'R' and 'V' stand for PATH, PROPERTY, and VALUE secondary XML indexes, respectively.

XML 索引使用的空間可以在資料表值函式 sys.dm_db_index_physical_stats中找到。The space use of XML indexes can be found in the table-valued function sys.dm_db_index_physical_stats. 其提供所有索引類型的資訊,例如:所佔用的磁碟分頁數量、平均資料列大小 (位元組),以及記錄的數量。It provides information, such as the number of disk pages occupied, average row size in bytes, and number of records, for all index types.. 另外還包含 XML 索引。This also includes XML indexes. 每個資料庫分割區都有此資訊。This information is available for each database partition. XML 索引使用相同的基底資料表分割區配置及分割區功能。XML indexes use the same partitioning scheme and partitioning function of the base table.

另請參閱See Also

sys.dm_db_index_physical_stats (Transact-SQL) sys.dm_db_index_physical_stats (Transact-SQL)
XML 資料 (SQL Server)XML Data (SQL Server)