延續上一篇對 XML 索引的介紹,這裡接著談輔助索引的選擇邏輯、XML 欄位上的全文索引,以及如何用「屬性提升」把熱門欄位拉到關聯式世界裡加速查詢。
先建立主 XML 索引:
CREATE PRIMARY XML INDEX idx_xCol ON T (xCol)
主索引建立之後,可以視工作負載再補上 PATH、PROPERTY、VALUE 三類輔助索引:
路徑式查詢——路徑 /book/@genre 與值 "novel" 正好對應 PATH 索引的鍵欄位:
SELECT pk, xCol FROM T
WHERE xCol.exist('/book[@genre = "novel"]') = 1
CREATE XML INDEX idx_xCol_Path ON T (xCol)
USING XML INDEX idx_xCol FOR PATH
取物件屬性——一次撈出 genre、title 與 ISBN:
SELECT xCol.value('(/book/@genre)[1]', 'varchar(50)'),
xCol.value('(/book/title)[1]', 'varchar(50)'),
xCol.value('(/book/@ISBN)[1]', 'varchar(50)')
FROM T
CREATE XML INDEX idx_xCol_Property ON T (xCol)
USING XML INDEX idx_xCol FOR PROPERTY
值導向查詢——路徑中含有 //,屬於部分路徑比對:
SELECT xCol FROM T
WHERE xCol.exist('//book[@ISBN = "1-8610-0157-6"]') = 1
CREATE XML INDEX idx_xCol_Value ON T (xCol)
USING XML INDEX idx_xCol FOR VALUE
你可以在 XML 欄位上建全文索引,把 XML 值的內容納入索引而忽略標記本身。屬性值不會被索引(因為視為標記的一部分),元素標記則被當成詞界。實務上常見的組合是兩段式:先用全文檢索篩出值得關注的 XML 值,再用 XML 索引去查詢這些值。
SELECT * FROM T
WHERE CONTAINS(xCol, 'custom')
AND xCol.exist('/book/title/text()[contains(., "custom")]') = 1
CONTAINS() 靠全文索引,把文件中任何位置出現 "custom" 的 XML 值縮小成一個子集;exist() 子句再確保這個字出現在書名裡。
要特別留意兩者的語意差異:全文 CONTAINS() 是詞彙還原後的標記比對,XQuery 的 contains() 則是子字串比對。查 "run" 時,全文條件會讓 "run"、"runs"、"running" 都命中,而 XQuery contains() 只認字面。反過來,標題裡的 "customizable" 不會被上述查詢命中,因為全文條件不成立。
更極端的例子是單獨使用 CONTAINS(xCol, 'run'):由於詞彙還原,文件中的 "ran" 也會匹配。這時 XQuery 完全不參與上下文檢查。因此,若你要的是純粹的子字串比對,就應該把全文 CONTAINS() 子句拿掉。
如果查詢集中於少數元素與屬性值——例如只靠 /Customer/@CustId 找客戶——那麼把這些值「提升」到關聯式欄位會是划算的做法。這種情境通常出現在:整份 XML 實例都會被取出,但實際查詢條件只落在其中一小塊資料上。此時 XML 索引並非必要,直接為提升後的欄位建索引即可。
要注意的是,查詢優化器不會自動把 XML 欄位的查詢導向提升欄位,所以查詢必須自己改寫去用它。提升欄位可以是同表中的計算欄,也可以是使用者自行維護的實體欄位。單值屬性用計算欄就夠了;多值屬性則需要另開屬性表。
CREATE FUNCTION udf_get_book_ISBN (@xData XML)
RETURNS varchar(20)
AS
BEGIN
DECLARE @ISBN varchar(20)
SELECT @ISBN = @xData.value('/book[1]/@ISBN', 'varchar(20)')
RETURN @ISBN
END
ALTER TABLE T
ADD ISBN AS dbo.udf_get_book_ISBN(xCol)
接著就能像一般欄位那樣為它建索引,並把原本的查詢改寫成:
SELECT xCol FROM T WHERE ISBN = '0-7356-1588-2'
計算欄的型別可以是任何 SQL 型別(包含 XML),但要注意:你無法在型別為 XML 的計算欄上建立 XML 索引。
當某幾個屬性覆蓋了大部分查詢負載,可以把多值屬性提升到獨立表格。常見的處理原則:
以作者姓名為例(一本書可有多位作者,因此是多值屬性):
create table tblPropAuthor (propPK int, propAuthor varchar(max))
create function udf_XML2Table (@pk int, @xCol XML)
returns @ret_Table table (propPK int, propAuthor varchar(max))
with schemabinding
as
begin
insert into @ret_Table
select @pk, nref.value('.', 'varchar(max)')
from @xCol.nodes('/book/author/first-name') R(nref)
return
end
再用插入、刪除、更新三個觸發器同步屬性表:
create trigger trg_docs_INS on T for insert as declare @wantedXML XML, @FK int select @wantedXML = xCol from inserted select @FK = PK from inserted insert into tblPropAuthor select * from dbo.udf_XML2Table(@FK, @wantedXML) create trigger trg_docs_DEL on T for delete as declare @FK int select @FK = PK from deleted delete tblPropAuthor where propPK = @FK create trigger trg_docs_UPD on T for update as if update(xCol) or update(pk) begin declare @FK int, @wantedXML XML select @FK = PK from deleted delete tblPropAuthor where propPK = @FK select @wantedXML = xCol from inserted select @FK = pk from inserted insert into tblPropAuthor select * from dbo.udf_XML2Table(@FK, @wantedXML) end
之後查詢「作者名為 David 的 XML 實例」,就能改走屬性表再回接基表:
SELECT xCol FROM T JOIN tblPropAuthor ON T.pk = tblPropAuthor.propPK WHERE tblPropAuthor.propAuthor = 'David'
總結一句:索引選型要貼合查詢形態,而屬性提升則是當你的查詢其實是「關聯式口味」時,最省事也最有效的加速手段。