大型表的 SQL Server 2008R2 XML 列索引策略？

December 2, 2014

我正在使用 SQL Server 2008 R2 和一個非常大的表中的一些 XML 列。我知道由於我們的 SQL Server 版本 < 2012，我不能使用選擇性 XML 索引。我對索引 XML 列的想法是全新的。
目標
我有兩個不同的場景需要查詢 XML：
在一種情況下，我需要查詢一個包含 300 萬行的表中是否存在嵌套值
例如columnName.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1'
在另一種情況下，我需要將 XML 中的兩個值提取到兩列中，以便在一個有 470 萬行的表中進行匹配。
例如SELECT columnName.value('Parent[1]/FilePath[1]', 'nvarchar(max)') as FilePath, columnName.value('Parent[1]/FileName[1]', 'nvarchar(255)') as FileName
題
考慮到這里索引的成本和固定的查詢類型，我可以應用哪些索引來獲得最大的影響，我應該注意哪些陷阱？

在 SQL Server 中調整 XML 查詢的方法有很多。物業促銷是個好東西，但我也經常使用以下內容：
XML 索引
最佳實踐語法
XML 模式集合
全文索引
XML 索引
XML 索引可以轉換 XML 查詢性能，但要付出一定的代價。在 SQL Server 2012 之前，它們有兩種類型，主要 XML 索引和輔助 XML 索引。您總是需要一個主 XML 索引，並且可以選擇添加 PATH、PROPERTY 或 VALUE 索引，它們的用途略有不同。對於您的特定查詢，輔助 PATH 索引在我下面的簡單裝備中提供了步進更改性能改進，例如：
CREATE PRIMARY XML INDEX xmlidx_largeTable ON dbo.largeTable ( yourXML )
GO

CREATE XML INDEX xpthidx_largeTable ON dbo.largeTable ( yourXML )
USING XML INDEX xmlidx_largeTable FOR PATH
GO
現在到成本。XML 索引（在選擇性 XML 索引之前）具有巨大的儲存影響。我已經看到桌子的大小增加到了 5 倍。在我下面的測試裝置中，具有 300 萬行和非常簡單的 XML 的表從 0.7GB 到 2GB，使用主要 XML 索引，然後是 2.7GB，使用 PATH 二級索引。SQL Server 2012 及更高版本中的選擇性 XML 索引可以大大改進這一點。
最佳實踐語法
當有多個級別的 XML 要從左到右向下鑽取時，我使用CROSS APPLY 。請參閱下面我的裝備中對 CROSS APPLY 的使用。此外，避免使用父軸 (..) 進行回溯。這可能會導致性能問題，尤其是對於較大的 XML 片段，如此處所述。
我也總是使用帶有無類型 XML 的 text() 訪問器，例如
SELECT
   p.c.value('(FilePath/text())[1]', 'nvarchar(max)') AS FilePath,
   p.c.value('(FileName/text())[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t
   CROSS APPLY t.yourXML.nodes('Parent') p(c)
這裡提到了這一點，我已經看到這種技術最多可以提高 15% 的性能。YMMV。將序數 ( [1]) 移動到表達式的末尾更有效，並且在語法上等同於Parent[1]/FilePath[1]/SomeOtherElement[1].
XML 模式集合
這些往往不會帶來性能改進，但是是一種很好的做法，就像約束一樣，它們迫使 XML 具有一定的結構。
全文索引
我偶爾將全文索引與 XML 結合起來效果很好，例如這裡。在此範例中可能不合適，因為您似乎沒有任何標準。
測試台
在我的簡單測試裝置中，我創建了一個包含 300 萬行的簡單表，每行包含一段簡單的 XML。然後我嘗試使用不同的語法和 XML 索引組合來查看差異：
USE tempdb
GO

SET NOCOUNT ON
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

-- Create a large table 
IF OBJECT_ID('dbo.largeTable') IS NOT NULL DROP TABLE dbo.largeTable
CREATE TABLE dbo.largeTable ( 
   rowId INT IDENTITY PRIMARY KEY, 
   someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
   dateAdded DATETIME DEFAULT GETDATE(), 
   addedBy VARCHAR(30) DEFAULT SUSER_NAME(),
   yourXML XML,
   ts ROWVERSION
   )
GO


-- Add 3 million rows to the table; with simple piece of XML in each row
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
   CROSS JOIN master.sys.columns c2
   CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.largeTable ( someData, yourXML )
SELECT NEWID(),
   (
   SELECT
       rn AS Child,
       'DifferentChild' + CAST( CASE WHEN rn % 9999 = 0 THEN rn % 33 ELSE NULL END AS VARCHAR(10) ) AS "Child/DifferentChild",
       'FilePath' + CAST( rn AS VARCHAR(10) ) AS FilePath,
       'FileName' + CAST( rn AS VARCHAR(10) ) AS [FileName]
   FOR XML PATH('Parent'), TYPE
   )
FROM cte
GO 3

-- Setup END
------------------------------------------------------------------------------------------------



------------------------------------------------------------------------------------------------
-- Queries START
------------------------------------------------------------------------------------------------

-- Query 1:
SELECT *
FROM dbo.largeTable t
WHERE t.yourXML.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1

-- Improve query
SELECT *
FROM dbo.largeTable t
WHERE t.yourXML.exist('(Parent/Child/DifferentChild)[1]') = 1
GO


-- Query 2:
SELECT
   yourXML.value('Parent[1]/FilePath[1]', 'nvarchar(max)') AS FilePath,
   yourXML.value('Parent[1]/FileName[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t


SELECT
   yourXML.value('(Parent/FilePath/text())[1]', 'nvarchar(max)') AS FilePath,
   yourXML.value('(Parent/FileName/text())[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t


SELECT
   p.c.value('(FilePath/text())[1]', 'nvarchar(max)') AS FilePath,
   p.c.value('(FileName/text())[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t
   CROSS APPLY t.yourXML.nodes('Parent') p(c)
GO

-- Queries END
------------------------------------------------------------------------------------------------


CREATE PRIMARY XML INDEX xmlidx_largeTable ON dbo.largeTable ( yourXML )
GO


------------------------------------------------------------------------------------------------
-- Queries START
------------------------------------------------------------------------------------------------

-- Query 1:
SELECT *
FROM dbo.largeTable t
WHERE t.yourXML.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1

-- Improve query
SELECT *
FROM dbo.largeTable t
WHERE t.yourXML.exist('(Parent/Child/DifferentChild)[1]') = 1
GO


-- Query 2:
SELECT
   yourXML.value('Parent[1]/FilePath[1]', 'nvarchar(max)') AS FilePath,
   yourXML.value('Parent[1]/FileName[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t


SELECT
   yourXML.value('(Parent/FilePath/text())[1]', 'nvarchar(max)') AS FilePath,
   yourXML.value('(Parent/FileName/text())[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t


SELECT
   p.c.value('(FilePath/text())[1]', 'nvarchar(max)') AS FilePath,
   p.c.value('(FileName/text())[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t
   CROSS APPLY t.yourXML.nodes('Parent') p(c)
GO

-- Queries END
------------------------------------------------------------------------------------------------


CREATE XML INDEX xpthidx_largeTable ON dbo.largeTable ( yourXML )
USING XML INDEX xmlidx_largeTable FOR PATH
GO



------------------------------------------------------------------------------------------------
-- Queries START
------------------------------------------------------------------------------------------------

-- Query 1:
SELECT *
FROM dbo.largeTable t
WHERE t.yourXML.exist('Parent[1]/Child[1]/DifferentChild[1]') = 1

-- Improve query
SELECT *
FROM dbo.largeTable t
WHERE t.yourXML.exist('(Parent/Child/DifferentChild)[1]') = 1
GO


-- Query 2:
SELECT
   yourXML.value('Parent[1]/FilePath[1]', 'nvarchar(max)') AS FilePath,
   yourXML.value('Parent[1]/FileName[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t


SELECT
   yourXML.value('(Parent/FilePath/text())[1]', 'nvarchar(max)') AS FilePath,
   yourXML.value('(Parent/FileName/text())[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t


SELECT
   p.c.value('(FilePath/text())[1]', 'nvarchar(max)') AS FilePath,
   p.c.value('(FileName/text())[1]', 'nvarchar(255)') AS [FileName]
FROM dbo.largeTable t
   CROSS APPLY t.yourXML.nodes('Parent') p(c)
GO

-- Queries END
------------------------------------------------------------------------------------------------
我的結果：
因此，總而言之，希望您能看到，您可以通過組合使用正確的特性來獲得 XML 查詢的階躍性能，但需要大量的儲存成本。
推薦閱讀
SQL Server 2005 中 XML 數據類型的性能優化
http://msdn.microsoft.com/en-us/library/ms345118.aspx
SQL Server 2005 中的 XML 索引
http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx

引用自：https://dba.stackexchange.com/questions/84025

大型表的 SQL Server 2008R2 XML 列索引策略？

目標

題

XML 索引

最佳實踐語法

XML 模式集合

全文索引

測試台

推薦閱讀

相關問答

查找和刪除重複索引

我可以信任數據庫引擎優化顧問的建議嗎？

索引列順序的 WHERE-JOIN-ORDER-(SELECT) 規則是否錯誤？

用許多值重建表中的索引

索引交點是否適用於包含的列？

如果索引不是最具選擇性的，如何提高性能？