第四期：堆、聚集索引与非聚集索引 —— 物理存储与访问路径

第四期：堆、聚集索引与非聚集索引 —— 物理存储与访问路径

1. 基础回顾：页（Page）与区（Extent）

• 页（8KB）：SQL Server 中最小的 I/O 单元。每页包含 96 字节的页头（对象ID、分区ID、下一页指针等）+ 实际数据行。
• 区（64KB）：8 个物理连续页的组。混合区（存放多个对象的页）和统一区（只属于一个对象）。
• IAM（Index Allocation Map）页：记录表或索引使用了哪些区，用于扫描整个对象。

2. 堆（Heap）—— 没有聚集索引的表

存储结构：数据页无序排列，新行通常放在最后一个页（如果有空间），或者从可用空间中分配。

• 行的物理位置：由 RID（8 字节：文件号:页号:槽号）唯一标识。
• 查找方式：必须 全表扫描（按 IAM 逐页读取）或通过非聚集索引的 RID 定位。
• 修改特性：插入时可随意找空位；更新行若长度增长且本页放不下会移动到新页（原位置留下转发指针）。

现象解释：

• 为什么堆上查询任意条件都很慢？因为没有有序结构，只能全表扫描。
• 为什么堆会产生碎片？因为行移动产生转发指针，增加额外 I/O。

3. 聚集索引（Clustered Index）—— 在树上组织数据

存储结构：B+ 树（非叶子节点存放索引键和子页指针；叶子节点就是 数据页本身）。

• 唯一特性：一个表只能有一个聚集索引。数据行按聚集键顺序物理存储（但实际不严格连续，通过双向链表连接叶子页）。

• 查找路径（等于或范围查询）：

  根页 → 中间页 → 叶子页（数据页）→ 解压行数据
  

  深度一般为 2~4 层（IO 次数极少）。

• 插入/更新：维护键顺序可能引发 页分裂（开销大）。

现象解释：

• 为什么主键默认是聚集索引（但并非必然）？因为主键常作为查询入口，聚集索引能最快定位。
• 为什么插入 GUID 主键会产生大量碎片？因为 GUID 随机，页分裂频繁。

4. 非聚集索引（Nonclustered Index）—— 目录索引

存储结构：也是 B+ 树，但叶子节点 不包含完整数据行，而是存放一个指向数据行的“指针”：

• 表有聚集索引 → 指向 聚集索引键（不是 RID）。
• 表是堆 → 指向 RID。

查找方式：

• 索引查找（Index Seek）：从根页下探，定位到少量索引行。
• 索引扫描（Index Scan）：整体遍历索引的叶子页（比表扫描小，但范围大时仍昂贵）。

关键概念：书签查找（Bookmark Lookup）
当查询需要返回非索引列（不在索引键或包含列中）时：

非聚集索引找到聚集键/RID → 再到聚集索引/堆中取完整行 → 产生额外 IO。

• 代价：如果查找的行数很少，书签查找没问题；如果行数太多（例如超过总行数的 1%~5%），优化器可能选择全表扫描。

5. 覆盖索引（Covering Index）—— 避免书签查找

定义：一个非聚集索引包含了查询所需的 所有列（通过 INCLUDE 或索引键本身）。

• 语法示例：

CREATE INDEX idx_orders_date 
ON Orders(OrderDate) 
INCLUDE (CustomerID, ShipAmount);

• 访问路径：只扫描非聚集索引的叶子页，获取全部数据，无需访问数据页。
• 性能提升：减少一倍以上 I/O（数据页可能更宽且分散）。

现象解释：为什么同样的查询，加一个 INCLUDE 后速度暴增？因为消除了书签查找中昂贵的随机 I/O。

6. 索引交叉与索引联合（Index Intersection / Union）

• 索引交叉：多个非聚集索引分别查找，然后交集结果（较少用）。
• 索引联合：先用一个索引找到行，再用另一个索引做书签查找（不常见）。

7. 常见“索引误用”现象与根因

现象	根本原因	架构解释
明明有索引，还是全表扫描	查询条件不符合索引查找要求（如函数包裹列、OR 条件、非前导列范围查询）	B+ 树只能从最左前缀开始下探
使用非聚集索引反而比全表扫描慢	书签查找行数占比过大（阈值约 1%~5%）	每条记录需要随机 I/O 访问数据页，而全表扫描是顺序 I/O
插入性能急剧下降	表上有多个非聚集索引，且聚集索引键宽或频繁分裂	每插入一行，所有非聚集索引也要维护（页分裂、重平衡）
查询统计数据不准导致选错索引	统计信息过时或采样不足	优化器基于代价估算，依赖统计信息
索引查找深度大（5+ 次逻辑读）	索引树层数多（通常因为索引键非常宽且表巨大）	B 树高度 ≈ log( 行数 / 每页索引行数 )

8. 监控与评估索引使用情况

DMV	作用
sys.dm_db_index_physical_stats	索引碎片（avg_fragmentation_in_percent）、深度、页数
sys.dm_db_index_usage_stats	查找次数（user_seeks）、扫描次数（user_scans）、更新次数（user_updates）
sys.dm_db_missing_index_details	建议创建哪些索引（但需验证）

• 无用索引判断：user_updates 远大于 user_seeks/scans → 维护成本高，可考虑删除。

9. 设计指导原则

1. 聚集索引的选择：

   • 窄、唯一、递增（如 IDENTITY，或 OrderDate 倒序）。
   • 避免 GUID、随机宽字符串。
   • 最常做范围查询或排序的列。

2. 非聚集索引策略：

   • 高选择性（唯一值接近行数）的列做索引键。
   • 在 =, > 等常用筛选中使用前导列。
   • 覆盖频繁查询（用 INCLUDE 包含所有回表列）。

3. 维护与重建：

   • 碎片 > 30% → ALTER INDEX … REBUILD。
   • 碎片 5%~30% → REORGANIZE。
   • 定期更新统计信息（自动维护或手动）。

4. 警惕过度索引：每个索引都会减慢插入、更新、删除，并占用缓冲池空间。

---

第四期小结

堆是随机的页面集合，适合高插入无查询的日志表。聚集索引将数据组织成 B+ 树，定位极快，但代价是维护顺序。非聚集索引是目录，通过书签查找回到数据行。理解这些物理存储和访问方式，就能解释为什么“覆盖索引”能提速、为什么书签查找有阈值，以及为什么不是索引越多越好。

下期预告：并发控制（上）—— 隔离级别、锁与锁升级，什么情况下会产生死锁？如何降低阻塞？