RiCo技術農場

設計上使用xml資料型別，其資料結構設計最好像關聯式資料庫，使用xml索引觀念和一般索引大同小異，由於所有資料皆存放在單一欄位中，從這角度來看，所有資料查詢只需相同xml索引即可涵蓋，這篇簡單筆記XML索引。

 

主要索引

類似clustered index，一個XML資料行只能存在一個主要索引。

--create primary xml index

CREATE PRIMARY XML INDEX [ixm1]

ON xmltest(xmldata)

GO

 

 

次要索引

以主要索引為基礎建立，由於主要索引大小會較大，次要索引可能可以提高效能及效率。

-- Create secondary indexes (PATH, VALUE, PROPERTY).

CREATE XML INDEX ixm1_PATH ON xmltest(xmldata)

USING XML INDEX ixm1

FOR PATH;

GO

 

 

選擇性索引

大部分情況下，建立選擇性索引會比一般索引來的更有效能和較佳儲存，但如果要查詢未知元素或大量節點則不適合使用。

CREATE SELECTIVE XML INDEX isxml_ProductKey

ON xmltest(xmldata)

FOR (

    pathProductKey   = '/row/ProductKey' as XQUERY 'xs:double' SINGLETON 

);

 

原始查詢

SELECT xmldata.value('(/row[1]/@TaxAmt[1])','money') AS TaxAmt

,xmldata.value('(/row[1]/@ProductKey[1])','int') AS ProductKey

FROM xmltest

WHERE xmldata.exist('/row/@ProductKey[.=212]') = 1

GO

(200 個資料列受到影響)

資料表 'xml_index_nodes_208055827_256000'。掃描計數 801，邏輯讀取 3682，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

資料表 'xmltest'。掃描計數 0，邏輯讀取 400，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

執行計畫

cost:0.7383

 

針對原始查詢建立XML主要索引後還是覺得效能不佳，是否還有其他方法提高效能呢?答案是有的

 

使用計算資料行

針對xpath兩個欄位，建立相對應純量函數

CREATE FUNCTION [dbo].[uf_XMLPERSISTEDCOLUMN_ProductKey](@xmlrecord XML)

RETURNS int

WITH SCHEMABINDING

BEGIN

RETURN @xmlrecord.value('(/row[1]/@ProductKey[1])','int')

END

GO

CREATE FUNCTION [dbo].[uf_XMLPERSISTEDCOLUMN_TaxAmt](@xmlrecord XML)

RETURNS money

WITH SCHEMABINDING

BEGIN

RETURN @xmlrecord.value('(/row[1]/@TaxAmt[1])','money')

END

GO

 

新增兩個計算資料行

ALTER TABLE xmltest

ADD ProductKey AS dbo.uf_XMLPERSISTEDCOLUMN_ProductKey(xmldata) PERSISTED

GO

 

ALTER TABLE xmltest

ADD TaxAmt AS dbo.uf_XMLPERSISTEDCOLUMN_TaxAmt(xmldata) PERSISTED

GO

建立索引

CREATE INDEX idx1 ON xmltest(ProductKey)

include(TaxAmt)

 

修改查詢如下

SELECT TaxAmt,ProductKey

FROM xmltest

WHERE ProductKey = 212

(200 個資料列受到影響)

資料表 'xmltest'。掃描計數 1，邏輯讀取 2，實體讀取 0，讀取前讀取 0，LOB 邏輯讀取 0，LOB 實體讀取 0，LOB 讀取前讀取 0。

執行計畫

cost:0.0035

整體查詢成本快211倍，I/O也大幅降低

 

參考

 

FOR XML 子句的基本語法

FOR XML (SQL Server)

CREATE SELECTIVE XML INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

Getting Started With XML Indexes

Performance tips of using XML data in SQL Server

Which one is better? PERSISTED Column or XML Indexes?

SQL Server XML Performance Tips

Precision Indexing: Basics of Selective XML Indexes in SQL Server 2012

XML indexes