Published on

从引擎到硬盘再到聚集索引

聚集索引

聚集索引通常在创建主键时自动生成，也可以由用户手动指定。它基于 B+ 树结构构建，包含根页、中间页和叶页（数据页），共同构成索引路径。查询时，SQL Server 会根据元数据定位根页，通过索引键逐层向下查找，最终定位到目标数据页以返回结果。

在未深入了解之前，我对这些描述也觉得很抽象。因为我只知道库、表、列、行；只知道查询慢了就加索引，但为什么加、索引具体解决了什么问题，并不了解。要真正理解索引是如何加快查询速度的，我们还需要从更底层的角度看看。

数据库引擎究竟是如何定位并读取数据的？

SQL Server 将数据页视为最小的物理存储单位，每个数据页大小为 8KB，具有唯一的页号用于逻辑寻址。当 SQL Server 需要读取某页时，它通过“页号 × 8192（字节）”来计算该页在 .mdf 文件中的偏移量，进而定位数据页的位置。

举例来说，.mdf 文件可以看作一个连续的字节流，每隔 8KB 就是一个新的数据页。页号对应的偏移量就是数据页在字节流中的“起始位置”。

在实际操作中，数据库引擎并不直接操作磁盘，而是将计算出来的偏移量与文件路径交由操作系统处理。操作系统通过文件系统（如 NTFS）查找该偏移量对应的磁盘位置并完成数据读取，最终将数据返回给 SQL Server。

整个过程中，SQL Server 负责“逻辑寻址”（页号 → 偏移量），而操作系统负责“物理寻址”（偏移量 → 磁盘位置），协作完成从磁盘到内存的数据加载。

I/O 与预读机制

定位并读取数据的过程其实就是 I/O 操作。

I/O 操作通常由预读器负责。一次数据页的读取即一次 I/O 。预读会智能地将连续的页合并成一个批量 I/O 请求，一次性读入内存，从而大幅提升查询性能。预读让执行器总是能够在内存中找到所需的数据，而不需要频繁地访问磁盘。

预读的核心就是要促成批量 I/O 请求，得到顺序 I/O 的性能。

一个批量 I/O 请求代表着逻辑页号连续，并且物理位置连续，形成顺序 I/O 。在机械硬盘条件下，性能会有百倍提升，因为磁头只需一次移动。在现代硬盘条件下，提升没有那么夸张，但仍有带宽打满，减少调度开销等优势。

也会出现逻辑页号连续，物理位置不连续的情况，由于页碎片等原因。遇到这种情况，系统层面还会退化成多次小规模随机 I/O 。

PFS、GAM、IAM

PFS（Page Free Space）：记录了数据库中每个数据页的使用状态。 GAM（Global Allocation Map）：跟踪哪些区（Extent）是可用的。一个区是 8 个连续的数据页（8 × 8KB = 64KB）。记录了每个区是否是空闲的，可以用来分配给新的对象。 IAM（Index Allocation Map）：记录一个对象（如表或索引）分配了哪些区（Extent），包括它跨越的数据文件、页的范围等。

堆表

堆表是没有聚集索引的表，数据无特定顺序。新增数据时，SQL Server 会通过 PFS页查找有空余空间的数据页，插入数据；若无空页，则通过 GAM 申请新页。

查询堆表时，SQL Server 会根据 IAM 记录的页分布信息，遍历全部属于自己的数据页，读取其中的行，直到满足查询条件或扫描结束。

优化查询速度的本质

当全表查询一张堆表时，理想情况下：

  • 数据页号连续
  • 物理位置连续
    这时，预读机制可以发挥作用，一次 I/O 读取多个数据页，查询速度快。

不理想情况（常态）：

  • 数据页号无序
  • 物理位置不连续
    这时，预读机制发现无法促成批量 I/O 请求，就不工作，让执行器同步读取数据页，查询速度慢。

所以，促成批量 I/O 可以提高查询速度，回到逻辑层，有序的数据页可以提高查询速度。

可是，当我只想查询一条数据，就算是理想情况的堆表还是会从头到尾扫描所有数据页，这样的查询效率太低了。

再谈聚集索引

索引的核心思想就像字典的目录页，能直接定位到某页而非逐页翻找。聚集索引以 B+ 树结构存储，数据页即为叶子节点，节点间通过指针形成链表。此处需自行了解 B+ 树的结构。

插入操作

当向存在聚集索引的表中插入新行时，SQL Server 需要确保新行按照聚集索引键的顺序插入到正确的数据页中：

  1. 定位目标页：根据新行的索引键值，SQL Server 遍历 B+ 树，找到应插入的数据页。
  2. 插入数据：- 如果目标页有足够空间，新行直接插入。- 如果目标页空间不足，SQL Server 会执行页分裂：- 分配一个新的数据页（页号通常较高）。- 将原页中的部分数据（通常是后半部分）移动到新页。- 将新行插入到适当的位置（原页或新页）。- 更新 B+ 树的指针，确保逻辑顺序正确。

    页分裂的影响

    • 逻辑顺序：页分裂后，数据在 B+ 树中的逻辑顺序（即索引键的顺序）保持不变。
    • 物理顺序：新分配的页可能导致数据页的物理页号不连续，但这不影响查询性能，因为 SQL Server 按逻辑顺序读取数据，可以触发预读机制。
    • I/O 增加：分配新页、移动数据和更新指针需要额外的 I/O。
    • 碎片化：长期的插入和删除操作会导致数据页碎片化，影响空间利用率和查询性能。

    缓解措施

    • 选择合适的聚集索引键：使用自增 ID 或时间戳等单调递增的键值，可以使新数据追加到表末尾，减少页分裂。
    • 设置填充因子（Fill Factor）：在创建或重建索引时，设置适当的填充因子，预留页内空间，减少插入时的页分裂频率。

示例

假设聚集索引键是整数，数据页 1 存储 1-10，数据页 100 存储 1001-1010（已满）。插入 1002 时：

  1. 定位到数据页 100，但页满。
  2. 执行页分裂：分配新页 201，将 1006-1010 移动到页 201。
  3. 将 1002 插入到页 100（现在存储 1001-1005,1002）。
  4. 更新 B+ 树：页 100（1001-1005）→页 201（1006-1010）。 尽管页 201 的页号高于页 100，但在逻辑上，页 201 紧随页 100 之后，保持了索引键的顺序。

查询操作

  1. 全等查询 通过 B+ 树快速定位到包含目标键值的数据页，然后在该页中找到具体行。查询效率高。
  2. 范围查询 找到范围起始键值的数据页后，按叶子节点的顺序读取后续页。预读机制会批量加载连续页，减少 I/O 开销。
  3. 全表查询 按聚集索引键的顺序扫描所有数据页。预读机制确保逻辑连续页的高效加载，比无索引的堆表扫描更快。

重建索引的重要性

聚集索引初始数据页按键值顺序排列，但插入操作可能引发页面分裂，导致碎片增加，查询效率下降。重建索引（Rebuild Index）重新整理 B+ 树，减少碎片，提升性能。不过，重建会分配新页面（高页号），旧页面未分配前仍占空间，可能引起膨胀，需后续处理。

优化方案

  • 用自增 ID 或有序 GUID 做键值，减少分裂。
  • 定期重建索引，控制碎片。
  • 核心表单独分组，连续且有序，可以满速读取