跳转至

XML 索引(下)— 选择性索引、提示词与索引压缩

这一章讲什么: 上一篇的主索引是「全量预拆」,简单粗暴但存储翻倍。选择性 XML 索引 (Selective XML Index,2012 年引入)是微软认定的更优解:只拆你指名的几条路径。 本篇讲它的建法、四个优化提示、它的次级索引、怎么给它加删路径, 以及 2022 年新增的索引压缩——全章最后一个配方给了压缩前后的实测体积对比。

1. 先看现象:主索引的存储之痛,选择性的解法

主 XML 索引的效果无可否认(09 篇的成本数字),但它把每份文档的每个节点都拆进内部表——XML 天生啰嗦,索引比数据还臃肿。「选择性 XML 索引」的思路倒过来:索引里只放你在 FOR 子句里点名的那几条路径的节点,查询命中这些路径时走索引,其它路径照旧运行时拆1。书里点明优势:大文档场景下,选择性索引「在很多情况下可以比主索引小得多」2。

2. 顶层全景:本篇的五站

  第一站 7-5  建选择性索引:FOR(别名 = '完整路径') 点名要索引的路径
  第二站 7-6  四个优化提示:node() / SINGLETON / DATATYPE / MAXLENGTH
  第三站 7-7  次级选择性索引:挂在选择性索引上,路径必须有提升类型
  第四站 7-8  ALTER INDEX 增删路径:每条 ALTER 都重建整个索引
  第五站 7-9  索引压缩(2022):WITH (XML_COMPRESSION = ON),实测省六成

  图说:前四站是一条产品线(选择性索引的建、优、挂、改),第五站是给所有索引减体积。

3. 核心原理

3.1 建选择性索引:FOR 子句点路径(第一站)

解决什么问题: 大 XML 文档列上,主索引的存储成本 unacceptable,而查询只用其中少数路径。

怎么做: 语法比主索引多一个 FOR 子句——给每条路径起一个别名,路径写成字符串:

CREATE SELECTIVE XML INDEX IX_SELECTIVE_XML_ProductXML
ON dbo.ProductXML (ProductDetails)
FOR (
    Quantity    = '/Products/Categories/Category/Subcategory/Product/ProductLocation/Quantity',
    ProductName = '/Products/Categories/Category/Subcategory/Product/ProductName'
);

两条硬规矩:路径必须完整(fully qualified,不许 // 捷径)3;文档带命名空间时,WITH XMLNAMESPACES 子句要在建索引语句里再声明一次——书里给的例子是给 HumanResources.JobCandidate 的 Resume 列建索引,WITH XMLNAMESPACES(DEFAULT '…Resume') 放在 FOR 之前4。

为什么有效: 索引只覆盖点名的节点,体积小、建得快;exist('路径[条件]') 查询命中索引路径时直接走索引查找,书里用计时统计和执行计划的前后对比演示了加速5。

官方立场(书里以注释形式给出): 微软建议优先选择选择性 XML 索引;只有选择性覆盖不了需求时,才回退到主 XML 索引6。

边界: 查询路径不在 FOR 名单里时,选择性索引帮不上忙——所以 FOR 名单要按真实的查询负载来列,不是越多越好(每条路径都是存储)。

3.2 四个优化提示(第二站)

解决什么问题: 索引建了,但引擎不知道路径值的类型和形态,索引组织不够紧凑。

怎么做: 在 FOR 的路径后面挂 AS XQUERY '类型' 或 AS SQL 类型 提示,可加修饰:

FOR (
    SubcategoryName = '…/SubcategoryName' AS XQUERY 'node()',
    Bin             = '…/ProductLocation/Bin' AS XQUERY 'xs:double' SINGLETON,
    ProductName     = '…/ProductName' AS SQL nvarchar(40),
    CategoryName    = '…/CategoryName' AS XQUERY 'xs:string' MAXLENGTH(35)
)

两族提示:SQL 族(AS SQL nvarchar(40)——映射到 SQL Server 类型)和 XQUERY 族(AS XQUERY 'xs:double'——映射到 XQuery 类型)7。四个提示的分工8:

提示 管什么 存储↓ 性能↑
node() 只索引节点存在性,不管值 ✓ —
SINGLET ON 声明此路径在文档里最多出现一次,引擎按单值优化 — ✓
DATATYPE 指定值的类型,类型出错索引里出现 NULL ✓ ✓
MAXLENGTH 字符串按最大长度截断优化;超长会出问题 ✓ ✓

(表中效果栏来自原书表 7-3 的归纳。)

边界(都是坑): SINGLETON 是基于「后续实例不会出现重复」的承诺——一旦文档里真出现了重复,会出问题9;DATATYPE 下值不符合类型,索引里就是 NULL;MAXLENGTH 截断比现有字符串短时也会出事。表里eligible 类型分两栏:XQUERY 族支持几十个 xs: 类型,DATATYPE/MAXLENGTH 只对 XQUERY 族有效,node() 也只对 XQUERY 族有效10。

3.3 次级选择性索引:类型必须「提升」(第三站)

解决什么问题: 选择性索引本身之上,还想像次级 XML 索引那样再加速某条具体路径。

怎么做: 语法和次级 XML 索引同构,但 USING 指向选择性索引,FOR 点名路径别名:

CREATE XML INDEX IX_…_SECONDARY ON HumanResources.JobCandidate (Resume)
USING XML INDEX IX_SELECTIVE_XML_…
FOR (LastName);

硬门槛:路径必须有「提升」(promoted)数据类型——FOR 里点名的那条路径,在建选择性索引时就得声明过 AS SQL 类型或 AS XQUERY '类型'。没提升?直接报错 Msg 6391:「Path 'LastName' is promoted to a type that is invalid for use as a key column in a secondary selective XML index」11。这是选择性格体系里最容易撞的报错:想给某路径建次级索引,先回去给该路径加类型提示。

3.4 ALTER INDEX 增删路径:一次说清,别分多条(第四站)

解决什么问题: 查询负载变了,想往选择性索引里加一条路径、删掉一条旧的。

怎么做: ALTER INDEX 索引名 ON 表 FOR (ADD 新路径, REMOVE 旧路径)——ADD 和 REMOVE 可以写在同一条 ALTER INDEX 里,也可以拆成多条12。

为什么强调「同一条」: 每条 ALTER INDEX 都重建整个索引。多条 ALTER = 多次全量重建,大表上是真金白银的时间浪费——书里明确建议「为了效率,把变更列表放进单条语句」13。

边界: 删选择性索引用 DROP INDEX IF EXISTS 名 ON 表;和主索引一样,选择性索引一删,它名下的所有次级选择性索引自动连带删除14。

3.5 索引压缩:29,512 KB → 11,048 KB(第五站)

解决什么问题: XML 索引体积大,第 09 篇说过主索引可能超数据两倍——磁盘和 I/O 都疼。

怎么做: SQL Server 2022 起,建索引时加 WITH (XML_COMPRESSION = ON),或对已有索引 ALTER INDEX … REBUILD WITH (XML_COMPRESSION = ON)。对 XML 列有效,主索引和次索引一起压15。

实测(本章最有传播价值的一个数字): 用 sp_spaceused 对比——作者 favorite 的空间分析工具,注意表名参数要带引号 'Production.Product'16:

  压缩前:29,512 KB
  压缩后:11,048 KB     ← 省了约 63%

(数字来自原书 Figure 7-15/7-16 的实测;比例随数据而异。)

为什么有效: XML 索引的 shredded 行集重复度极高(同样的路径名反复出现),页级压缩的收益天然大;压缩后读 I/O 的量也跟着降,读密集环境双赢17。

边界: 2022 之前的版本没有这个功能(与 01 章的表级 XML_COMPRESSION 同版本引入,但那是压数据、这是压索引,两个开关互不影响);对已有索引要 REBUILD,大索引重建要挑窗口。

4. 作者的判断与证据

有实测支撑的: 索引压缩的 29,512 → 11,048 KB(sp_spaceused 截图)16;选择性索引建前后的计时统计和执行计划对比5。压缩比是全书最硬的优化数字之一——不过单表单库,作量级参考。

官方立场转述(非作者本人观点): 「微软建议优先选择性索引」6——书里以注释形式转述。结合 09 篇的规矩清单,整体路线清晰:新需求先看选择性索引,覆盖不了再上主索引+次要索引。

作者的操作建议: ALTER INDEX 变更合并成一条13;sp_spaceused 用引号包表名16;这些都属于「被坑过才写得出」的细节。

5. 边界与局限

  • 提示词表格在转码文本里部分乱码(SINGETON/DATATPE/MAENGTH 等拼写被 PDF 提取损坏),本篇按语义还原并以上下文核对——引用原文短语时已避开损坏处8。
  • 选择性索引与主索引不能共存于同一列(补充,不在书里明写,来自产品行为):二选一,选错要重建。
  • 压缩比不可移植:63% 是样例数据的数字,路径名长短、值重复度都影响结果——上线前用 sp_spaceused 自己量16。
  • 次级选择性索引的类型提升报错(Msg 6391)在书里只给错误文本,排查路径(回 FOR 加 AS 提示)是从错误语义直接推得的,书里没有展开步骤。

6. 可带走的

本篇主线,一行走完: 大文档 XML 列 → 主索引太肥 → 建选择性索引(FOR 点名两条路径,完整路径、命名空间进 WITH XMLNAMESPACES)→ 按负载加提示(AS SQL/XQUERY 类型 + SINGLETON/MAXLENGTH)→ 某路径要再加速 → 确认该路径有提升类型,否则 Msg 6391,建次级选择性索引 → 负载变了 → 单条 ALTER INDEX 同时 ADD+REMOVE → 体积疼 → REBUILD WITH (XML_COMPRESSION = ON),实测省六成。

  1. 微软推荐顺序:先选择性索引,后主索引6。
  2. 选择性索引 = FOR 子句点名路径,路径必须完整,不许 //3。
  3. 带命名空间的列建索引:WITH XMLNAMESPACES 写进 CREATE 语句4。
  4. 四个提示: node()(只查存在性)、SINGLETON(承诺最多一个)、DATATYPE(定类型)、MAXLENGTH(限长);前两族 AS SQL/AS XQUERY 各有适用范围8。
  5. 次级选择性索引的前提是路径有提升类型,否则 Msg 639111。
  6. ALTER INDEX 一次说完所有变更——每条都重建整个索引13。
  7. 删选择性/主索引连带删次级索引14。
  8. 索引压缩:WITH (XML_COMPRESSION = ON) / REBUILD;实测 29,512 → 11,048 KB(约省 63%)1516。
  9. sp_spaceused '架构.表名' 看体积,表名要引号16。
  10. 压缩开关有两种:表级(压数据,01 章)与索引级(压索引,本章),2022 起都有15。

7. 原文地图

主题 原书章 原文位置
选择性索引的定位与体积优势 Chapter 7: Enhancing XML Data Performance with SQL Server Indexing text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:618(搜「does not」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:618(搜「a lot of space」)
FOR 子句与完整路径规矩 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:602(搜「CREATE SELECTIVE XML INDEX」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:631(搜「fully qualified and complete path」)
带命名空间的建索引语法 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:684(搜「XMLNAMESPACE must be declared」)
微软推荐选择性优先 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:734(搜「recommends」)
两族提示与示例 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:767(搜「two flavors」)
提示效果与适用范围表 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:833(搜「benefits」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:843(搜「supported with each data type」)
次级选择性索引与 Msg 6391 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:897(搜「improves the performance of a selective」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:919(搜「Msg 6391」)
ALTER INDEX 增删路径 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1020(搜「REMOVE keyword」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1043(搜「rebuilds the entire index」)
连带删除提示 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1053(搜「all associated secondary」)
索引压缩与实测数字 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1061(搜「Starting with SQL Server 2022」) · text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1136(搜「29,512 KB」)
sp_spaceused 提示 同上 text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1121(搜「favorite tools」)

  1. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 530–536 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:534,搜「designed to improve」)。原文:「A selective XML index is designed to improve XQuery performance and XML index storage for an XML column that stores large XML documents」。 ↩

  2. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 616–621 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:618,搜「a lot of space」)。原文:「A primary XML index can utilize a lot of space and consume server resources for large XML instances. The selective XML index acts against the values that are specified in one or more paths. This can make the selective XML much smaller than the primary XML index in many cases」。 ↩

  3. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 631–635 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:631,搜「fully qualified and complete path」)。原文:「A selective XML index path must be a fully qualified and complete path, i.e., no shortcuts are allowed for the path」。 ↩↩

  4. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 683–728 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:684,搜「XMLNAMESPACE must be declared」)。原文:「When an XML column stores the XML instances with XMLNAMESPACES, the XMLNAMESPACE must be declared within the CREATE SELECTIVE INDEX statement」;语法骨架(WITH XMLNAMESPACES(DEFAULT '…') FOR(…))见第 721–728 段。 ↩↩

  5. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 641–674 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:641,搜「before the selective XML index was created」)。原文:建索引前跑 Listing 7-18 的 exist 查询,「After the index IX_SELECTIVE_XML_ProductXML was created, the query…generates a more efficient runtime and execution plan」。 ↩↩

  6. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 734–736 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:734,搜「recommends」)。注释原文:「Microsoft recommends setting the preference to selective XML indexes first…they(需要时)the PRIMARY XML index could be a better choice」(转码文本此句局部损坏,语义按上下文还原)。 ↩↩↩

  7. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 767–772 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:767,搜「two flavors」)。原文:「SQL Server – oriented with SQL data-type mappings. For example, PathName = '/root/element' AS SQL nvarchar(40)」「XQUERY – oriented with XQuery data-type mappings. For example, PathName = '/root/element AS XQUERY 'xs:string'」;完整示例见第 745–764 段(Listing 7-22)。 ↩

  8. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 773–791 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:775,搜「optimization hints」)与第 833–841 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:836,搜「Storage Efficiency」)。表 7-1 描述四提示(node()/SINGLETON/DATATYPE/MAXLENGTH),表 7-3 给出效果矩阵(node() 存效✓、SINGLETON 性能✓、DATATYPE 两者✓、MAXLENGTH 两者✓)。注:转码文本中这些提示名拼写损坏(SINGETON/DATATPE/MAENGTH),已按语义还原。 ↩↩↩

  9. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 780–783 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:780,搜「SINGETON」)。原文:「P…of the group so that the index can be optimized with that in mind. A…instances subsequently, as this can cause issues」(转码局部损坏;语义:SINGLETON 声明单值性,后续若出现重复实例会出问题)。 ↩

  10. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 793–831 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:796,搜「eligible XQuery data types」)与第 843–851 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:843,搜「supported with each data type」)。表 7-2 分类型化/无类型两栏列 xs: 类型;表 7-4:node()/DATATYPE/MAXLENGTH 仅 XQUERY 族,SINGLETON 两族皆可。 ↩

  11. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 910–921 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:910,搜「promoted data type」)。原文:「The secondary selective XML index must have a path with a promoted data type」;报错:「Msg 6391, Level 16, State 0…Path 'LastName' is promoted to a type that is invalid for use as a key column in a secondary selective XML index」。 ↩↩

  12. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 1018–1045 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1020,搜「REMOVE keyword」)。原文:「It is possible to drop an existing path using the REMOVE keyword, and a new path can be added by using the ADD keyword within the FOR() clause」。 ↩

  13. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 1043–1045 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1043,搜「rebuilds the entire index」)。原文:「Each ALTER INDEX statement rebuilds the entire index. Therefore, for efficiency reasons, it is more practical to implement a list of changes in a single ALTER INDEX statement」。 ↩↩↩

  14. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 1052–1054 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1053,搜「all associated secondary」)。原文:「when the selective XML index is dropped, all associated secondary selective XML indexes are dropped automatically」。 ↩↩

  15. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 1056–1078 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1061,搜「Starting with SQL Server 2022」)。原文:「Starting with SQL Server 2022, you can enable XML compression at the time of table creation using the XML_COMPRESSION = ON option. This applies to XML columns and affects both primary and secondary XML indexes」。 ↩↩↩

  16. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 1135–1136 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1136,搜「29,512 KB」)。原文:「the index size after compression decreased from 29,512 KB to 11,048 KB」;sp_spaceused 提示见第 1121–1125 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1121,搜「favorite tools」)。 ↩↩↩↩↩↩

  17. 出处:「Chapter 7: Enhancing XML Data Performance with SQL Server Indexing」第 1079–1098 段(text/11-ch07-chapter-7-enhancing-xml-data-performance-with-sq.txt:1083,搜「saves disk space」)。原文:「By compressing these indexes, SQL Server not only saves disk space but also improves I/O performance, especially in read-heavy environments」;索引臃肿的三条原因(XML Reader 存全部路径/节点/值、索引大且冗余、无压缩时占盘可观)见第 1089–1095 段。 ↩