跳转至

XML 索引(上)— 主索引与三种次要索引,各管一类查询

这一章讲什么: 第 08 章结尾说「XQuery 谓词过滤比 WHERE 快一倍」——那是没索引的 情况下。本章讲怎么给 XML 列建索引,让谓词根本不用拆文档。原书第 7 章九个配方, 本篇拆前四个(主索引 + 三种次要索引),选择性索引和压缩在下一篇(10 章)。

1. 先看现象:92% 的成本花在哪

本章主线例子: PersonXML 表(Person.Person 的 19,972 行数据灌进一张带 Demographics XML 列的表),查询照旧:「找出 Occupation 元素存在(或 TotalPurchaseYTD > 9000)的行」1。

没建索引时,打开实际执行计划,会看到两个不常见于 T-SQL 查询的步骤:

  Table Valued Function [XML Reader with XPath filter]   ← 92% 成本
  Table Valued Function [XML Reader]                     ←  7% 成本

书里的解读直白:这两步就是 XML 的运行时拆分机制,「非常像普通关系查询里的表扫描」——XML 实例多大,拆分就多贵2。有数字为证:XML Reader 步骤的估计成本 35782.9(占 92%);建一个索引之后,它变成 Clustered Index Seek (PrimaryXML),成本降到 21.28(占 46%)3。三个数量级的差距,就是本章存在的理由。

2. 顶层全景:索引体系的分层图

  表(必须有聚集主键)
    │
    ├─ 主 XML 索引(每表每列一个):把列里所有文档预拆成内部行集
    │     存储代价:可能超过 XML 数据本身的两倍以上
    │     语法:CREATE PRIMARY XML INDEX 名 ON 表(列)
    │
    └─ 次要 XML 索引(三种,都挂在主索引上,USING XML INDEX 指明)
          FOR PATH     → 加速 exist() 的路径匹配
          FOR VALUE    → 加速带通配/不完整路径的谓词(值已知、路径模糊)
          FOR PROPERTY → 加速 value() 取值(一次取一个或多个值)

  图说:主索引是地基(必须第一个建),次要是三种专用工具——查询形态决定选哪把。

3. 核心原理

3.1 主 XML 索引:把「运行时拆」变成「提前拆好」

解决什么问题: 每次查询都现场拆分 XML,大文档上贵到离谱(上一节的 92%)。

怎么做: CREATE PRIMARY XML INDEX IX_名 ON 表(列)。效果:执行计划里的 XML Reader 消失,换成 Clustered Index Seek (PrimaryXML)——引擎不再解析文档,直接查预拆好的内部表3。

为什么有效: 建主索引时,SQL Server 把列里每份文档的所有节点拆成一种关系格式——这个内部表只有查询引擎能访问。查询来了, XPath 直接落在预拆结果上。

代价与规矩(本章最该背的部分):

  1. 存储可能翻倍以上:「could potentially consume more than double the storage space」——索引比数据还大,是常态而非意外4。
  2. 表必须有聚集主键(clustered primary key)——内部 shredded 表靠它挂回基表;反过来,主索引存在期间,主键不许改不许删(要先删所有 XML 索引)5。
  3. 七个会话选项必须就位:ARITHABORT、ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER 为 ON,NUMERIC_ROUNDABORT 为 OFF——每个建索引的配方开头都把这七行 SET 抄一遍,书里明说「These are important」6。
  4. 名字全库唯一、命名受视图同名规则约束、不能单独指定文件组/分区、IGNORE_DUP_KEY 和 ONLINE 必须 OFF、每列一个主索引、视图/表变量/XML 变量上不能建5。
  5. 删主索引,所有次要索引自动连带删除5。
  6. 列从无类型改成有类型之前,必须先删 XML 索引5。

什么时候不该建(书里的决策建议): XML 列不参与 XQuery 就不建;只在「一次查很多行」或「XML 文档很大」时才值得——否则存储成本盖过收益;如果目的是建次要索引,那主索引是非建不可的前置4。

3.2 三种次要索引:按查询形态对号入座

解决什么问题: 主索引是「全节点」索引,对特定查询形态还可以再精进一步。三种次要索引都是可选的加层,语法统一:

CREATE XML INDEX IX_次索引名 ON 表(列)
USING XML INDEX IX_主索引名 FOR PATH;   -- 或 FOR VALUE / FOR PROPERTY

USING XML INDEX 说明它依附于哪个主索引——删主索引它们跟着消失7。

PATH —— 为 exist() 服务。 你的查询模式是「WHERE exist('路径')」或「exist('路径[谓词]')」时,FOR PATH 加速路径本身的匹配8。主线例子:exist('/IndividualSurvey/Occupation') = 1 与 exist('/IndividualSurvey[TotalPurchaseYTD > 9000]') = 1 都吃这个索引9。

VALUE —— 为「路径模糊、值明确」服务。 路径没写全、或带通配符时(//ELEMENT[…]、/ELEMENT/@*[. = …]、//ELEMENT[@ATTRIBUTE = …]),谓词里已知的那个值是检索的抓手,FOR VALUE 按值组织索引10。书里同时给了一条反向警告:nodes('//ELEMENT') 这种从根全文搜的写法,是「XML 拆分中最常见的性能问题」;至少把路径写实一点(//ELEMENT/CHILD_ELMNT),对深嵌套文档会有改善11。

PROPERTY —— 为 value() 取值服务。 查询要从 XML 列里取一个或多个值时(SELECT 列表里一串 ref.value(...),常配 nodes+谓词),FOR PROPERTY 让引擎直接定位到属性/元素值12。主线例子的形态:「TotalPurchaseYTD 在 1000 到 1005 之间的行,取出五个字段」13。

一张对号入座表:

你的查询长这样 建它
WHERE exist('路径') = 1 FOR PATH
路径带 // 或 @* 通配,谓词值已知 FOR VALUE
SELECT 里一串 value() 取值 FOR PROPERTY

边界: 次要索引不是越多越好——每种都是一份额外的存储和维护成本(改数据时全部要跟着改,本章小结明确「XML indexes can slow down insert/update operations」)14。实践中先建主索引,用执行计划看瓶颈,再针对性加一种次要索引。

4. 作者的判断与证据

有执行计划数字支撑的: 92%/7% 的成本分布、35782.9 → 21.2796 的成本变化,都是可复跑的实测截图23。这是全书少见的「带数字的优化论证」,可信度高(虽然绝对值随环境变)。

书里的规矩清单(官方文档性质): 主索引的十一条限制、七个 SET 选项——这些是产品行为的事实陈述,书里整理得比官方文档顺手56。

需要打问号的: 「PRIMARY XML 索引可能消耗两倍以上的存储」是上限式表述,实际取决于数据;决策建议(「查询多行/文档大才值得」)是经验法则,不是阈值——书里没给「多大算大」的线(补充说明:实践上从几十万行或大文档开始考虑,此数字不在书里)。

5. 边界与局限

  • 只覆盖前四个配方:选择性 XML 索引(微软现在的推荐方向)、索引压缩,都在下一篇(10 章)。
  • 索引维护成本只提了一句:改一行数据要同步更新所有 XML 索引,写密集表的代价书中未量化14。
  • 没有组合索引/包含列的概念迁移说明:次要索引是「一种一个」,不能像普通索引那样多列组合——复杂查询形态可能哪种次要索引都不完全对口(补充判断,不在书里)。
  • 七个 SET 选项是历史包袱:ANSI 设置在当代客户端默认都开着,但从脚本/ORM 里建索引时仍要核对——书里每次全量抄录是对的。

6. 可带走的

本章主线例子,一行走完: 2 万行 XML 列查询 → 执行计划里 XML Reader 吃掉 92% → 建主 XML 索引(前置:聚集主键 + 七个 SET 选项)→ XML Reader 变成 Clustered Index Seek(PrimaryXML),成本 35782.9 → 21.28 → 按查询形态选次要索引:exist() 加 FOR PATH、通配谓词加 FOR VALUE、value() 取值加 FOR PROPERTY。

  1. 没索引的 XML 过滤 = 运行时拆分 = 表扫描,执行计划里找 XML Reader 这个标志2。
  2. 主索引语法一行 CREATE PRIMARY XML INDEX 名 ON 表(列),但前置条件一堆:聚集主键、七个 SET 选项、全库唯一的名字56。
  3. 存储代价先想好:主索引可能超过数据本身两倍大;不参与 XQuery 的列别建4。
  4. 次要索引三选一,按查询形态对号:PATH→exist()、VALUE→通配路径+已知值、PROPERTY→value() 取值81012。
  5. 删主索引连带删全部次要索引;改列类型前也要先删索引5。
  6. nodes('//元素') 是最常见的拆分性能杀手,路径至少写实一级11。
  7. 改数据会变慢:所有 XML 索引都要同步维护,写密集表权衡14。
  8. 数字记量级不记绝对值:92% 成本、成本降三个数量级——方向可靠,数字随环境变3。

7. 原文地图

主题 原书章 原文位置
XML Reader 成本分布 Chapter 7: Enhancing XML Data Performance with SQL Server Indexing text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:117(搜「highest percentage」)
35782.9 → 21.2796 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:151(搜「21.2796」)
主索引语法与十一条规矩 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:155(搜「three parts」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:162(搜「several rules」)
七个 SET 选项 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:187(搜「must be set to ON」)
存储代价与决策建议 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:207(搜「consume a large amount」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:212(搜「recommendations」)
次要索引语法与 USING 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:312(搜「built on top of」)
PATH 服务 exist() 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:230(搜「method of the XML data type」)
VALUE 服务通配路径 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:385(搜「not fully specified」)
// 的性能警告 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:426(搜「convenient way」)
PROPERTY 服务 value() 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:494(搜「retrieves one or more values」)
本章小结 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1148(搜「powerful mechanism」)

  1. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 27–38 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:32,搜「Adding a primary XML index」)。示例表 dbo.PersonXML 从 Person.Person 灌入 BusinessEntityID 与 Demographics。 ↩

  2. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 112–123 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:117,搜「highest percentage」)。原文:「Two of these steps have the highest percentage of the total query cost, 92% and 7%, respectively」「These steps specify the XML runtime shredding mechanism and are very similar to a table scan for a normal relational SQL query」。 ↩↩↩

  3. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 143–153 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:151,搜「21.2796」)。原文:「the Table Valued Function [XML Reader with XPath filter] step and Table Valued Function [XML Reader] are replaced with a Clustered Index Seek (PrimaryXML) step」;「the estimated operator cost went down from 35782.9 (92%)…to 21.2796 (46%)」。 ↩↩↩↩

  4. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 204–223 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:207,搜「consume a large amount」)。原文:「adding a primary XML index might consume a large amount of storage space」「could potentially consume more than double the storage space」;决策建议:「Often times, an XML index is not needed if the XML columns are not part of an XQuery process」「An XML index is worth considering if you are querying against a large number of rows at a time or if your XML data is large in size」。 ↩↩↩

  5. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 162–186 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:162,搜「several rules」)。十一条规矩逐条列出,含「The table that contains the XML column must have a clustered primary key」「When the primary XML index is dropped, all secondary XML indexes relying on it are dropped automatically」「Each XML index must have a database-wide unique name」。 ↩↩↩↩↩↩↩

  6. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 42–49 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:43,搜「SET NUMERIC_ROUNDABORT OFF」)与第 187–202 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:187,搜「must be set to ON」)。原文:「These are important when creating XML columns in tables or adding XML indexes」。 ↩↩↩

  7. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 311–318 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:312,搜「built on top of」)。原文:「A secondary path type XML index, as well as value and property type secondary indexes, is each built on top of a primary XML index」;语法四部分见第 313–318 段。 ↩

  8. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 224–233 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:230,搜「method of the XML data type」)。原文:「The secondary path type XML index improves query performance for the exist() method of the XML data type」。 ↩↩

  9. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 285–309 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:298,搜「Occupation」)。两个示例查询:exist('…/IndividualSurvey/Occupation') = 1 与 exist('…/IndividualSurvey[TotalPurchaseYTD > 9000]') = 1。 ↩

  10. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 323–331 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:326,搜「not fully specified」)与第 383–394 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:385,搜「wildcards」)。原文:「A secondary value type XML index benefits XQuery performance when the path is not fully specified or if it includes a wildcard」,例://ELEMENT[ELEMENT = "Filter Condition"]、/ELEMENT/@*[. = "Filter Condition"]、//ELEMENT[@ATTRIBUTE = "Filter Condition"]。 ↩↩

  11. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 425–431 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:426,搜「convenient way」)。原文:「XML_Column.nodes('//ELEMENT'), is a convenient way…However…it could cause performance problems」「XML_Column.nodes('//ELEMENT/CHILD_ELMNT') could improve your XML shredding performance」。 ↩↩

  12. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 433–441 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:494,搜「retrieves one or more values」)。原文:「A secondary property type XML index can be beneficial if your XQuery retrieves one or more values from an XML column」;How It Works 见第 492–495 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:494,搜「via the value() method」)。 ↩↩

  13. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 497–520 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:519,搜「TotalPurchaseYTD」)。示例查询 nodes 谓词 IndividualSurvey[TotalPurchaseYTD > 1000 and TotalPurchaseYTD < 1005…] 配 SELECT 中五个 value()。 ↩

  14. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 1159–1167 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1160,搜「Query Workload」)。小结四要素:「Query Workload」「Data Modification Patterns: XML indexes can slow down insert/update operations due to their complexity」「Storage Implications」「Schema Design: A clustered primary key is required…only one primary XML index is allowed per table」。 ↩↩↩