SQL Server 性能优化实战（第六期）：存储引擎内幕——数据页、区、空间管理

SQL Server 性能优化实战（第六期）：存储引擎内幕——数据页、区、空间管理

前五期我们聚焦在查询、索引、执行计划、等待统计和事务隔离上。这些都是数据库的“逻辑层”优化。今天，我们钻进数据库的“物理层”——存储引擎。为什么一个只有 1000 行的表，查询却要扫描几万页？为什么删除数据后文件大小没变？为什么索引重建能提升性能？答案都藏在 SQL Server 如何物理存储数据的细节里。

一、SQL Server 存储的基本单位：页和区

1.1 页（Page）——8 KB

页是 SQL Server 中最小的 I/O 单位。无论你读取一行还是 100 行，SQL Server 每次至少读取一页。

属性	值
大小	8 KB（8192 字节）
每行最大	8060 字节（正常情况下）
页头	96 字节（存储页号、类型、LSN、对象ID等）
可用数据空间	~8096 字节

-- 查看表和索引占用的页数
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    i.name AS IndexName,
    p.index_id,
    p.rows,
    a.total_pages,
    a.used_pages,
    a.data_pages
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE p.object_id = OBJECT_ID('Orders')
ORDER BY p.index_id;

1.2 页的类型

页类型	用途
Data Page	存储堆表或聚集索引的数据行
Index Page	存储非聚集索引或聚集索引的中间节点
LOB Page	存储大对象（TEXT、IMAGE、VARCHAR(MAX)等）
Row Overflow Page	存储行溢出的数据（VARCHAR 等变长列超 8060 字节）
IAM Page	跟踪表和索引的页面分配
PFS Page	跟踪每页的空间使用情况
GAM/SGAM Page	跟踪区分配

1.3 区（Extent）——64 KB

区是 8 个连续页的集合（8 × 8KB = 64KB）。SQL Server 以区为单位分配空间。

区类型	说明
统一区（Uniform Extent）	8 页全部属于同一个对象
混合区（Mixed Extent）	8 页可以分给最多 8 个不同对象

小于 8 页的首次分配使用混合区；超过 8 页后切换到统一区。

二、堆表 vs 聚集索引表

2.1 堆表（Heap）

定义：没有聚集索引的表。数据行按插入顺序无序存储。

内部结构：

• 数据页之间没有顺序关系
• 使用 IAM（Index Allocation Map） 跟踪哪些页属于该表
• 行没有逻辑顺序，通过 RID（Row ID = 文件号:页号:槽号）定位

优点：

• 插入速度快（直接追加到最后一页）
• 无索引维护开销

缺点：

• 查询需要全表扫描（除非有非聚集索引）
• 更新可能导致行移动（页拆分）或转发指针
• 删除产生“幽灵记录”

-- 创建堆表
CREATE TABLE HeapTable (ID INT, Data VARCHAR(100));
-- 不创建聚集索引，就是堆表

-- 查看转发记录（性能杀手）
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.partition_number,
    ps.forwarded_record_count
FROM sys.dm_db_partition_stats ps
JOIN sys.partitions p ON ps.partition_id = p.partition_id
WHERE ps.forwarded_record_count > 0;

2.2 聚集索引表（Clustered Index）

定义：有聚集索引的表。数据行按聚集键顺序存储。

内部结构：

• 叶子节点就是完整的数据行
• 使用 B-Tree 结构组织数据
• 数据页通过双向链表连接

优点：

• 范围查询快（按索引顺序扫描）
• 不需要转发指针
• 非聚集索引更小（存储聚集键而非 RID）

缺点：

• 插入/更新可能引起页拆分（Page Split）
• 随机插入顺序可能导致碎片

2.3 堆表 vs 聚集索引表性能对比

-- 测试对比
CREATE TABLE HeapTest (ID INT IDENTITY, Col1 CHAR(1000));
CREATE TABLE ClusteredTest (ID INT IDENTITY PRIMARY KEY, Col1 CHAR(1000));

-- 插入 10 万行
INSERT INTO HeapTest (Col1) SELECT TOP 100000 'A' FROM sys.all_columns a, sys.all_columns b;
INSERT INTO ClusteredTest (Col1) SELECT TOP 100000 'A' FROM sys.all_columns a, sys.all_columns b;

-- 对比占用的页数
SELECT OBJECT_NAME(p.object_id) AS TableName, SUM(a.data_pages) AS DataPages
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.object_id IN (OBJECT_ID('HeapTest'), OBJECT_ID('ClusteredTest'))
GROUP BY p.object_id;
-- 聚集索引表通常页数稍多（B-Tree 结构），但查询性能远优于堆表

三、页拆分与碎片

3.1 页拆分是如何发生的？

当向聚集索引表插入新行，且目标页已满时：

1. SQL Server 分配一个新页
2. 将原页中约 50% 的数据移到新页
3. 更新页之间的双向链表
4. 插入新行

后果：

• 逻辑碎片：页顺序不再连续
• 内部碎片：页空间未充分利用（平均 50%）
• 性能下降：扫描需要更多 I/O

-- 查看索引碎片率
SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.page_count,
    ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 15
ORDER BY ips.avg_fragmentation_in_percent DESC;

3.2 碎片阈值与处理策略

碎片率	推荐操作	原因
< 5%	无操作	健康状态
5% - 30%	重组（ALTER INDEX ... REORGANIZE）	在线操作，碎片整理
> 30%	重建（ALTER INDEX ... REBUILD）	完全重建，消除碎片，更新统计信息

-- 重组索引（在线，轻量）
ALTER INDEX IX_Orders_CustomerID ON Orders REORGANIZE;

-- 重建索引（可选在线/离线）
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD;  -- 离线重建（默认）
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD WITH (ONLINE = ON);  -- 企业版支持在线重建

-- 重建所有索引
ALTER INDEX ALL ON Orders REBUILD;

四、行结构与特殊存储

4.1 行结构（Data Row）

组成部分	大小	说明
状态位 A	1 字节	行类型、版本信息
状态位 B	1 字节	空位图存在标志等
定长数据	各字段长度	CHAR, INT, DATETIME 等
空位图	2 + 列数/8 字节	标记哪些列为 NULL
变长列偏移数组	2 × 变长列数	指向变长数据的偏移
变长数据	各字段长度	VARCHAR, NVARCHAR, VARBINARY

4.2 行溢出（Row Overflow）

当行大小超过 8060 字节时：

1. 定长列不允许超过 8060（会报错）
2. 变长列（VARCHAR、NVARCHAR 等）可以超过
3. 超出的列数据被移到单独的 ROW_OVERFLOW 页
4. 原页保留 24 字节指针指向溢出页

-- 创建会溢出的表
CREATE TABLE OverflowTest (
    ID INT,
    LargeData VARCHAR(10000)  -- 单行会超过 8060
);

-- 查看溢出页使用情况
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.index_id,
    a.total_pages,
    a.used_pages,
    a.data_pages
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE a.type_desc = 'ROW_OVERFLOW_DATA';

4.3 大对象存储（LOB）

TEXT、IMAGE、VARCHAR(MAX)、NVARCHAR(MAX) 等使用独立的 LOB 页：

-- LOB 数据单独存储
-- 小 LOB（< 8000 字节）可能内联存储（取决于设置）
-- 大 LOB 使用 LOB_DATA 页

五、空间重用与文件收缩

5.1 为什么删除数据后文件大小不变？

删除行或页后：

• 页被标记为“空闲”，但不会自动返还给操作系统
• 空闲页可以被同一对象的新数据重用
• 文件大小只有通过 DBCC SHRINKFILE 才能缩小

⚠️ 注意：收缩文件会导致索引严重碎片，通常不推荐作为常规维护操作。

-- 查看文件空间使用
SELECT 
    name,
    size/128.0 AS CurrentSizeMB,
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS UsedMB,
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS FreeMB
FROM sys.database_files;

-- 收缩文件（谨慎使用！）
DBCC SHRINKFILE (YourDataFile, TargetSizeMB);

-- 更好的做法：重建索引 + 定期维护
ALTER INDEX ALL ON LargeTable REBUILD;

5.2 正确释放空间的方法

1. 重建索引：整理碎片，释放内部空间
2. 删除后重建表：完全释放空间
3. 使用分区切换：ALTER TABLE ... SWITCH

六、压缩技术

SQL Server 2008+ 支持行压缩和页压缩，大幅减少存储空间和 I/O。

6.1 行压缩（Row Compression）

原理：

• 定长列改为变长存储（如 CHAR(100) 实际只存 10 字符）
• 数值类型使用变长格式（如 INT 值 5 只占 1 字节）
• 减少 NULL 和空字符串的空间

6.2 页压缩（Page Compression）

原理：在行压缩基础上，增加：

• 前缀压缩：识别页内重复的前缀
• 字典压缩：识别页内重复的完整值

6.3 启用压缩

-- 表级压缩
ALTER TABLE Orders REBUILD WITH (DATA_COMPRESSION = ROW);
ALTER TABLE Orders REBUILD WITH (DATA_COMPRESSION = PAGE);

-- 分区级压缩
ALTER TABLE Orders REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = PAGE);

-- 查看压缩效果
SELECT 
    OBJECT_NAME(p.object_id) AS TableName,
    p.data_compression_desc,
    SUM(a.total_pages) * 8 / 1024 AS SizeMB
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY p.object_id, p.data_compression_desc;

6.4 压缩性能权衡

维度	行压缩	页压缩
空间节省	20-40%	40-70%
CPU 开销	低	中高
I/O 减少	中	高
适用场景	OLTP	OLAP、归档表

💡 最佳实践：归档表、历史表使用页压缩；OLTP 表使用行压缩或不压缩。

七、文件组与分区表

7.1 文件组（Filegroup）

将表分布在多个物理文件上，提升 I/O 并行度。

-- 创建文件组
ALTER DATABASE YourDB ADD FILEGROUP FG_Orders_2024;

-- 添加文件
ALTER DATABASE YourDB ADD FILE (
    NAME = Orders_2024_Data,
    FILENAME = 'D:\Data\Orders_2024_Data.ndf',
    SIZE = 1GB,
    MAXSIZE = UNLIMITED
) TO FILEGROUP FG_Orders_2024;

-- 将表移到文件组
CREATE TABLE Orders_Archive (
    OrderID INT PRIMARY KEY,
    OrderDate DATE
) ON FG_Orders_2024;

7.2 分区表

将大表按指定列（如日期）切分成多个分区，实现快速删除、归档和查询优化。

-- 分区函数（按月份）
CREATE PARTITION FUNCTION PF_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES 
('2024-01-01', '2024-02-01', '2024-03-01', ...);

-- 分区方案
CREATE PARTITION SCHEME PS_OrderDate
AS PARTITION PF_OrderDate
TO (FG2024_Jan, FG2024_Feb, FG2024_Mar, ...);

-- 创建分区表
CREATE TABLE Orders_Partitioned (
    OrderID INT,
    OrderDate DATE,
    CustomerID INT
) ON PS_OrderDate(OrderDate);

八、核心总结

知识点	核心要点
页（8KB）	最小 I/O 单位，每行最大 8060 字节
区（64KB）	8 个页的集合，空间分配单位
堆表	无序存储，插入快，查询慢
聚集索引表	按索引顺序存储，范围查询快
页拆分	页满时发生，产生碎片
碎片处理	碎片 < 30% 重组，> 30% 重建
行溢出	单行超 8060 字节，数据移到溢出页
压缩	行压缩省空间，页压缩更高效
文件组	分布 I/O 负载
分区表	快速维护和归档

一句话记住本期内容：

页是 SQL Server 的积木，区是它的包装盒——理解物理存储，才知道碎片从哪来、压缩省在哪、分区快在哪。

快速检查清单

• 定期检查索引碎片，>30% 时重建
• 监控转发记录（堆表），考虑添加聚集索引
• 大表考虑分区（按日期、按范围）
• 归档表启用页压缩，节省 50%+ 空间
• 数据文件放在不同物理磁盘，提升 I/O 并行度
• 避免频繁收缩文件，否则碎片会加剧

下一期预告

内存架构——缓冲池、计划缓存与内存配置

• 缓冲池（Buffer Pool）如何缓存数据页
• 计划缓存：为什么查询第二次比第一次快
• 内存配置陷阱：max server memory 应该设多少
• 内存压力诊断与优化
• 列存储索引的内存特性

---

📌 本文代码已在 SQL Server 2019+ 验证。页结构分析可以使用 DBCC PAGE 命令（未文档化，仅用于深入分析）。

本系列持续更新中，点击关注不错过下一期。