為什麼 SQL 使用相同的 XML 查詢結構進行索引掃描和查找？

我已經設置了一個測試來對 SQL Server 中的 xml 性能進行基準測試。

測試設置

• 一百萬行數據
• 帶有主鍵的 XML 定義的列
• 主 XML 索引
• 路徑上的二級 XML 索引
• XML 數據在格式上相似，但在每個文件中都有可變標籤名稱

表格和索引設計

CREATE TABLE [dbo].[xml_Test]
(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [GUID] [varchar](50) NULL,
   [JSON_Data] [varchar](max) NULL,
   [XML_Data] [xml] NULL,
   CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC)
);

ALTER TABLE [dbo].[xml_Test] ADD  CONSTRAINT [DF_xml_Test_GUID]  DEFAULT (newid()) FOR [GUID];
ALTER TABLE [dbo].[xml_Test] ADD  CONSTRAINT [PK_xml_Test] PRIMARY KEY CLUSTERED ([ID] ASC);

CREATE PRIMARY XML INDEX [PK_xml] ON [dbo].[xml_Test]
(   [XML_Data]);

CREATE XML INDEX [IX_xml_Path] ON [dbo].[xml_Test] 
(   [XML_Data]) 
USING XML INDEX [PK_xml] FOR PATH;

範例 XML 架構

<data>
 <id>3812</id>
 <guid>E3735046-1183-4A79-B8EE-806312B533D6",</guid>
 <firstName>John</firstName>
 <lastName>Doe</lastName>
 <tel>123-123-1234</tel>
 <city>Toronto</city>
 <prov>Ontario</prov>
 <Q.49.R.47>14325</Q.49.R.47>
 <Q.1>14326</Q.1>
 <Q.9>143257</Q.9>
 <Q.25>14328</Q.25>
 <Q.50>14329</Q.50>
 <Q.51>14330</Q.51>
 <Q.30>14331</Q.30>
 <Q.22>14332</Q.22>
 <Q.100>14333</Q.100>
 <Q.70.R.4>1</Q.70.R.4>
 <Q.43>14335</Q.43>
 <Q.3>14336</Q.3>
 <Q.84.R.21.L.19>1</Q.84.R.21.L.19>
 <done>1</done>
</data>

當我用兩個不同的值查詢表時，我會非常有效地尋找一個結果，並掃描另一個需要幾個數量級才能完成的結果。

我不確定為什麼第二個查詢無法對索引進行搜尋。我認為這可能與開始時不在索引中的元素有關，但即使那樣我也不知道為什麼需要掃描，除非索引確實沒有索引所有路徑。

通過粘貼計劃執行計劃

查詢 A

select ID, 'Query A'
from xml_test
where XML_Data.exist('(/data/Q.70.R.4)[1]')=1

查詢 B

select id, 'Query B'
from xml_test
where XML_Data.exist('(/data/Q.61.R.15)[1]')=1 

如您所見，執行查詢 A 需要 5 毫秒，而查詢 B 則需要將近 15 秒。

相同的查詢格式但具有不同的輸入會產生如此明顯低效的執行計劃的原因是什麼？

在旁邊

• 我還使用它來測試具有相同格式 JSON 數據的 JSON，我的結果顯示與 XML 查詢 A 相同的 JSON 查詢需要 22,344 毫秒（22 秒！）的 CPU 時間（3,419 毫秒時鐘時間）來執行。
• 如果 XML 索引可以通過查找操作可靠地工作，那麼與我在網上研究和閱讀的內容相反，它顯然是性能更高的讀取選項。
• 在查詢大量數據時，我不建議在 SQL 伺服器中使用 JSON，除了用於 blob 儲存或您有一個小數據集的地方，也就是說，不要在生產中使用它來搜尋應該是的數據索引。還有其他為此設計的優質服務。

SQL Server 中的 XML 索引被實現為一個內部表，它是節點表的持久版本，與 XML 粉碎函式產生的非常相似。

內部表中的一列稱為隱藏，該列包含用於查找路徑表達式的值。該值是一個ordpath值。創建路徑 xml 索引時，您將獲得一個以 hid 作為前導列的非聚集索引。

當 SQL Server 將 XML 數據插入表中時，會生成特定元素名稱的 ordpath。為了對路徑進行編碼，連接不同的 ordpath 值。

簡化後的路徑 X/Y/Z 可以表示為 1.2.3，Y/Z/Z 可以表示為 2.3.3。

SQL Server 必須有一種方法可以為相同的元素名稱生成相同的 ordpath 值。這可以通過某種字典或表來完成，該字典或表跟踪一個 XML 索引的所有生成的 ordpath 值。我在這裡有點模糊，因為我在任何地方都沒有找到任何說明它是如何完成的並且我沒有找到它的儲存位置。

使用此 XML 插入一行

<X>
 <Y>
   <Z />
 </Y>
</X>

將在內部表中為您提供此內容，並添加一個由我添加的額外列以顯示在 hid 中編碼的路徑表達式

id      hid     pk1
0x              1    
0x58          1     Z
0x5AC0  Á€À€    1     Y/Z
0x5AD6  €Á€À€  1     X/Y/Z

您可以使用 DAC 登錄來查看內部表。

當您查詢路徑表達式時會發生什麼（在這裡我再次有點推測）是用於確保具有相同名稱的元素獲得相同的 ordpath 值的相同字典/查找表用於查找隱藏用於內部表中的索引查找。XQuery 中的路徑表達式始終是靜態的，因此用於查詢的隱藏值可以儲存在查詢計劃中並重新使用。

對存在的節點的查詢會得到一個查詢，該查詢在 hid 上搜尋，其中Î要搜尋的 hid 值。

<SeekKeys>
 <Prefix ScanType="EQ">
   <RangeColumns>
     <ColumnReference Database="[yy]" Schema="[sys]" Table="[xml_index_nodes_658101385_256000]" 
                      Alias="[NodeThatExist:1]" Column="hid" />
   </RangeColumns>
   <RangeExpressions>
     <ScalarOperator ScalarString="'Î'">
       <Const ConstValue="'Î'" />
     </ScalarOperator>
   </RangeExpressions>
 </Prefix>
</SeekKeys>

對不存在的節點的查詢為您提供了一種非常不同的定位行的方法。

select count(*)
from dbo.T
where T.X.exist('NodeDoesNotExist') = 1

計算標量使用一些函式來計算在過濾器運算符中檢查的值。

我在這裡做的一些測試可以在這裡找到。請注意，您實際上並不需要為索引中的元素生成隱藏來獲取查找計劃，您只需嘗試插入它即可。

附帶說明一下，使用一種或另一種方式來查找 XML 查詢的行的查詢的重寫是在查詢優化器開始執行其工作之前完成的，因此，如果您獲得搜尋，也取決於基數上隱藏內部表中的列。

---

Paul White 在聊天中提到了插入元素名稱的儲存位置：

QName 儲存在 sys.sysqnames 中。XML algebrizer 需要現有的已知 QName 來生成優化的路徑轉換。即使用路徑索引。條目被添加到系統事務中的 sys.sysqnames 中，不會感覺到使用者事務

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