罗罗编程学习记录
本页内容 展开查看

05.4.1.1 数据库文件、页与区

所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.1 架构与存储引擎

覆盖范围

  • 数据库文件类型:MDF / NDF / LDF 与文件组
  • 数据页(8KB)内部结构、区(64KB)与统一区/混合区
  • 系统页:GAM、SGAM、PFS、IAM、DCM、BCM
  • 行结构、行溢出与 LOB 离页存储
  • 事务日志、VLF、恢复模式、checkpoint、Buffer Pool
  • 碎片、填充因子、数据压缩,与 InnoDB / PostgreSQL 存储对照

高频题速览

编号 题目 难度 频率
05.4.1.1-002 数据页(Page)的内部结构 进阶 ★★★★☆
05.4.1.1-003 区(Extent)与统一区/混合区 进阶 ★★★★☆
05.4.1.1-004 GAM/SGAM/PFS/IAM 系统页 高级 ★★★★☆
05.4.1.1-010 事务日志、VLF 与 LSN 进阶 ★★★★☆
05.4.1.1-011 三种恢复模式与备份策略 进阶 ★★★★☆
05.4.1.1-014 页分裂与填充因子 进阶 ★★★★☆
05.4.1.1-018 与 InnoDB / PostgreSQL 存储对照 高级 ★★★★☆

物理存储层级

数据库(Database)
  ├─ 数据文件:.mdf(主)/ .ndf(次要)
  │    └─ 文件组(Filegroup)
  │         └─ 区 Extent:64KB = 8 个连续 8KB 页
  │              ├─ 统一区:8 页只属于一个对象
  │              └─ 混合区:8 页可分属最多 8 个对象
  │                   └─ 页 Page:8KB(8192 字节)
  │                        ├─ 页头(96 字节)
  │                        ├─ 数据行(行内 / 行溢出 / LOB 离页)
  │                        └─ 行偏移表(页尾,槽位 → 行偏移)
  └─ 日志文件:.ldf
       └─ VLF(虚拟日志文件)→ LSN 日志链

05.4.1.1-001 SQL Server 数据库由哪些文件组成?MDF / NDF / LDF 各做什么?

频率 ★★★★☆ | 难度 基础

一句话结论

每个数据库至少有一个主数据文件和一个事务日志文件;数据文件分主文件(.mdf)与次要文件(.ndf),日志文件为 .ldf,数据与日志必须分开存放。

概念解释

  • 主数据文件(.mdf):数据库的起点,包含系统表与用户数据,每个数据库有且仅有一个。
  • 次要数据文件(.ndf):把数据分散到多个磁盘/文件上扩展容量,也可用于文件组布局;可以没有。
  • 事务日志文件(.ldf):按 LSN 顺序记录所有修改,用于崩溃恢复、回滚与高可用同步;一个数据库可有多个日志文件。

原理流程

  • 写入先改 Buffer Pool 中的数据页(标记脏页),同时写日志记录到 LDF;脏页后续由 checkpoint 惰性落盘。
  • 恢复时先重做(redo)已提交但未落盘的修改,再回滚(undo)未提交事务。

代码 / 命令

-- 查看数据库文件与文件组
SELECT  f.name AS logical_name, f.physical_name, f.type_desc,
        fg.name AS filegroup_name, f.size * 8 / 1024 AS size_mb
FROM    sys.database_files f
LEFT JOIN sys.filegroups fg ON fg.data_space_id = f.data_space_id;

常见误区

  • 误区:日志文件可以和数据文件放同一盘节省成本。正解:顺序写的日志与随机读写的数据争抢磁盘 IO,恢复与高可用都会受影响,应分盘部署。
  • 误区:加多个 .ndf 就自动提升性能。正解:只有把不同文件放到不同物理磁盘并合理使用文件组,才能分散 IO;同盘多文件意义有限。

追问

  • 问:一个数据库能有几个 .mdf?答:只有一个主数据文件;其余数据文件用 .ndf。
  • 问:日志文件能放在文件组里吗?答:不能,日志文件独立于文件组,文件组只管理数据文件。

05.4.1.1-002 数据页(Page)的内部结构是怎样的?

频率 ★★★★☆ | 难度 进阶

一句话结论

SQL Server 的最小 IO 单位是 8KB(8192 字节)数据页,页内分页头、数据行区、行偏移表三部分;单行行内上限约 8060 字节,超出部分走行溢出或 LOB 离页存储。

概念解释

  • 页头(约 96 字节):包含页类型、页号、对象 ID、页空闲量、上一页/下一页指针等。
  • 数据行区:从页头后向页尾方向依次存放数据行。
  • 行偏移表(slot 数组):页尾起向上增长,每个槽位记录该行在页内的偏移;聚集/非聚集索引按槽位顺序组织键序。

原理流程

  • 读数据按页加载进 Buffer Pool;一页内多行可被同一页读取覆盖。
  • 插入新行先找空闲空间,页满后触发页分裂(见 05.4.1.1-014)。
  • 页类型包括数据页、索引页、IAM 页、页空闲空间页等,由页头类型标识。

代码 / 命令

-- 查看某表各索引/堆占用的页与行数
SELECT  OBJECT_NAME(p.object_id) AS table_name, i.name AS index_name,
        ps.page_count, ps.row_count, ps.avg_fragmentation_in_percent
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN    sys.partitions p ON p.partition_id = ps.partition_id
JOIN    sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id;

-- 打开跟踪标记后可用 DBCC PAGE 查看页头与行槽位(输出到错误日志)
DBCC TRACEON (3604);
DBCC PAGE (N'你的数据库名', 1, <页号>, 3);

常见误区

  • 误区:页大小可以像 MySQL 那样按实例调成 16KB。正解:SQL Server 页大小固定 8KB,不可配置;InnoDB 默认 16KB。
  • 误区:一行数据可以无限长地放在页内。正解:行内约 8060 字节上限,长列必须离页。

追问

  • 问:行偏移表的作用是什么?答:按槽位定位行偏移,并支持索引按键序排列而不移动物理行。
  • 问:为什么按页而不是按行做 IO?答:磁盘按扇区/块读取,页是引擎管理与缓存的基本单位,一次读取覆盖多行以减少随机 IO。

05.4.1.1-003 什么是区(Extent)?统一区和混合区有什么区别?

频率 ★★★★☆ | 难度 进阶

一句话结论

区是 64KB 的连续 8 个页,是空间分配的基本单位;统一区(Uniform)8 页只属于一个对象,混合区(Mixed)8 页可分配给最多 8 个不同对象。

概念解释

  • 混合区:新表/小表刚创建时,从混合区按页分配,避免一个小表独占 64KB 造成浪费。
  • 统一区:对象增长到一定规模后,改为按整个区分配,减少分配开销并提升连续读写效率。

原理流程

  • 一个表最初只占用几页,分散在混合区;随着数据增长,新页优先从统一区取。
  • GAM 记录哪些区是统一区,SGAM 记录哪些混合区还有空闲页(见 05.4.1.1-004)。

代码 / 命令

-- 查看对象占用的盘区/页数
SELECT  OBJECT_NAME(object_id) AS table_name,
        SUM(page_count) AS total_pages,
        SUM(page_count) * 8 / 1024 AS size_mb
FROM    sys.dm_db_partition_stats
GROUP BY OBJECT_NAME(object_id);

常见误区

  • 误区:所有页一开始就按 64KB 整区分配。正解:小对象先按页从混合区分配,长大之后才进入统一区。
  • 误区:区就是索引的叶子节点。正解:区是磁盘空间单位,不是索引结构概念;索引页只是落在区里。

追问

  • 问:为什么连续 8 页很重要?答:连续页可减少寻道,顺序扫描时一次定位读取多页。
  • 问:混合区最多服务几个对象?答:8 页每页可属不同对象,最多 8 个。

05.4.1.1-004 GAM、SGAM、PFS、IAM、DCM、BCM 这些系统页分别做什么?

频率 ★★★★☆ | 难度 高级

一句话结论

系统页由引擎自动维护,记录空间分配、页空闲、对象与索引的页映射、差异/批量变更标记;理解它们是排查 tempdb 争用与空间问题的前提。

概念解释

系统页 作用
GAM(Global Allocation Map) 记录哪些区已分配、哪些区空闲可用
SGAM(Shared GAM) 记录哪些混合区仍有空闲页可分配
PFS(Page Free Space) 逐页记录分配状态与空闲空间比例(0~100%)
IAM(Index Allocation Map) 记录某个索引/堆占用了哪些区,是对象访问页的入口
DCM(Differential Changed Map) 记录自上次完整备份后变更的区,服务差异备份
BCM(Bulk Changed Map) 记录大容量日志操作(如批量导入)改动的区

原理流程

  • 申请新页时先查 GAM/SGAM 找空闲区/空闲页,再由 PFS 判断页内空间是否够;IAM 维护对象与区的对应关系。
  • GAM/SGAM/PFS 在数据文件前部按固定间隔重复出现,高并发分配时这些页面本身可能成为闩锁争用点(见 05.4.1.4-004)。

代码 / 命令

-- 查看等待类型中是否有分配页争用(PAGELATCH 等待常指向 GAM/PFS/SGAM)
SELECT  wait_type, waitting_tasks_count, wait_time_ms, average_wait_time_ms
FROM    sys.dm_os_wait_stats
WHERE   wait_type LIKE 'PAGELATCH%'
ORDER BY wait_time_ms DESC;

常见误区

  • 误区:IAM 页里存的是索引数据。正解:IAM 只存"对象占用哪些区"的映射,不存业务数据。
  • 误区:DCM/BCM 用于崩溃恢复。正解:它们服务备份(差异备份、大容量日志恢复模式),崩溃恢复靠事务日志。

追问

  • 问:PFS 除了空闲比例还记录什么?答:页是否已分配、是否为 IAM 页、页内最小空闲记录等状态。
  • 问:为什么多数据文件能缓解 GAM/PFS 争用?答:每个文件有自己的一套系统页,分配请求可分散到不同文件的系统页上。

05.4.1.1-005 文件组(Filegroup)是什么?怎么规划?

频率 ★★★☆☆ | 难度 进阶

一句话结论

文件组是数据文件的逻辑集合,用于把表/索引部署到指定磁盘上;默认有 PRIMARY 文件组,可自建用户文件组并设置只读、默认文件组。

概念解释

  • PRIMARY 文件组:存放系统表与未指定文件组的对象。
  • 用户文件组:按磁盘或业务划分,例如把大表、索引放到不同文件组。
  • 只读文件组:可用于历史数据归档,备份与恢复策略可单独安排。

原理流程

  • 建表/建索引时用 ON 文件组名 指定落点;文件组内多个文件按比例填充(proportional fill)分配页。
  • 日志文件不属于任何文件组。

代码 / 命令

-- 创建文件组并添加次要数据文件
ALTER DATABASE DemoDB ADD FILEGROUP FG_Archive;
ALTER DATABASE DemoDB
  ADD FILE ( NAME = N'ArchiveData1', FILENAME = N'/data/ArchiveData1.ndf',
             SIZE = 256MB, FILEGROWTH = 64MB )
  TO FILEGROUP FG_Archive;

-- 把历史表建到归档文件组
CREATE TABLE ArchiveLog (Id BIGINT, LogText NVARCHAR(MAX)) ON FG_Archive;

常见误区

  • 误区:文件组可以跨数据库。正解:文件组只在单个数据库内组织文件。
  • 误区:把文件组设为默认后旧表自动迁移。正解:默认文件组只影响新建对象,旧表需重建才能迁移。

追问

  • 问:比例填充(proportional fill)解决什么问题?答:按各文件可用空间比例分配新页,避免某一文件先写满。
  • 问:只读文件组有什么运维价值?答:历史数据不可误改,可单独备份到低成本介质。

05.4.1.1-006 堆表和聚集索引表在物理存储上有什么区别?

频率 ★★★★☆ | 难度 进阶

一句话结论

堆表的数据行不按键排序,按插入位置堆放,用 RID(文件:页:槽位)定位;聚集索引表的数据行按聚集键有序组织,叶子节点就是数据本身。

概念解释

  • 堆(Heap):没有聚集索引;行的物理顺序与任何键无关,非聚集索引叶子存 RID。
  • 聚集索引(Clustered Index):表的数据按聚集键 B 树有序排列,一张表只能有一个。

原理流程

  • 堆表插入直接找空闲页放置;删除后空间由 PFS 记录复用,不保证顺序。
  • 聚集索引表新行按键值落到对应叶子页,键递增时倾向追加到右侧页。

代码 / 命令

-- 判断表是否为堆:index_id = 0 即堆
SELECT  t.name AS table_name, i.index_id, i.type_desc
FROM    sys.tables t
JOIN    sys.indexes i ON i.object_id = t.object_id
WHERE   i.index_id IN (0, 1);

常见误区

  • 误区:每张表都"应该"有聚集索引。正解:堆表在批量装载、随机插入为主的场景可考虑,但 OLTP 查询几乎都受益于聚集索引。
  • 误区:堆表上非聚集索引叶子存主键。正解:堆表存 RID;有聚集索引时非聚集索引叶子存聚集键(见 05.4.1.2-002)。

追问

  • 问:RID 由哪几部分组成?答:文件号:页号:槽位号(file:page:slot)。
  • 问:堆表删除大量数据后空间会自动归还磁盘吗?答:不会自动收缩,需收缩文件或重建。

05.4.1.1-007 一行的数据结构是怎样的?行溢出和 LOB 离页是什么?

频率 ★★★☆☆ | 难度 高级

一句话结论

行内记录包含状态位、定长列、变长列偏移数组与列值;行内约 8060 字节放不下时,变长大列被迁移到行溢出页或 LOB 页,原位置只留指针。

概念解释

  • 定长列在行内按固定偏移存储;变长列(varchar、varbinary 等)先存长度偏移数组再存值。
  • 行溢出页(Row-Overflow):超出 8060 字节的变长列被推到单独页,主行存 24 字节指针。
  • LOB 离页:text/ntext/image 与 varchar(max)/nvarchar(max)/varbinary(max)/xml 可把大值存到 LOB 页链。

原理流程

  • 查询需要离页列时,沿指针读取溢出/LOB 页,额外增加 IO;只查不涉及大列的列时可避免离页读取。

代码 / 命令

-- 查看表的最大行大小与列类型
SELECT  c.name, t.name AS type_name, c.max_length
FROM    sys.columns c
JOIN    sys.types t ON t.user_type_id = c.user_type_id
WHERE   c.object_id = OBJECT_ID('dbo.Orders')
ORDER BY c.column_id;

常见误区

  • 误区:varchar(max) 就是无限长度的普通列。正解:它是 LOB 类型,大值离页存储,读取代价与普通列不同。
  • 误区:一行可以跨多页连续存放。正解:一行主体在一页内,超出部分离页,行不跨页拆分。

追问

  • 问:8060 字节限制针对哪部分?答:行内定长+变长列总和;LOB 不计入行内上限。
  • 问:频繁读取大文本列有什么优化方向?答:大列拆到附属表、按需查询,或考虑列存/文档库。

05.4.1.1-008 IAM 是如何定位一个对象占用的空间的?

频率 ★★☆☆☆ | 难度 高级

一句话结论

IAM 页用位图记录某个索引或堆覆盖了哪些区,引擎先读 IAM 找到区,再访问区内数据页;每个索引/堆至少有一套 IAM。

概念解释

  • IAM 按每 64KB 区一位的粒度标记归属;覆盖范围大时由多组 IAM 页串联。
  • 表或索引被访问时,优化器/执行器通过 IAM 确定扫描哪些区与页。

原理流程

  • 分配新区时在 IAM 位图置位;对象释放区时清位。
  • 堆表的全表扫描沿 IAM 列出的页进行,不按键序。

代码 / 命令

-- DBCC IND 可列出某对象的 IAM 页与数据页(2012 后推荐用 DMV,DBCC IND 供排查)
DBCC IND (N'你的数据库名', N'dbo.Orders', -1);

常见误区

  • 误区:IAM 就是索引页。正解:IAM 是空间映射页,与业务索引内容无关。
  • 误区:一个对象只有一张 IAM 页。正解:对象很大时 IAM 由多页组成。

追问

  • 问:IAM 与分区是什么关系?答:每个分区的每个索引/堆各有自己的 IAM 链。
  • 问:统计一个表占多少页以什么为准?答:sys.dm_db_partition_stats 或 sys.dm_db_index_physical_stats。

05.4.1.1-009 数据库文件的自动增长(autogrow)应该怎么配置?

频率 ★★★★☆ | 难度 进阶

一句话结论

自动增长是兜底手段而不是容量规划;应设置合理初始大小、固定 MB 增长(避免按百分比增长导致增量失控),并在磁盘上预留空间。

概念解释

  • 初始大小:按业务容量预估,避免上线后频繁 autogrow 打断写入。
  • 增长增量:建议固定 MB(如 64MB/256MB),百分比增长在大文件上会产生过大增量。
  • 日志文件增长:每次增长都会新建/扩展 VLF,增长方式直接影响 VLF 数量(见 05.4.1.1-010)。

原理流程

  • 文件写满后触发 autogrow:先扩文件再继续写;增长期间相关写入会等待文件扩展完成。
  • 即时文件初始化(Instant File Initialization)可让数据文件扩展不做零填充,显著缩短扩文件时间(日志文件不享受)。

代码 / 命令

-- 查看文件增长配置
SELECT  name, size * 8 / 1024 AS size_mb, growth,
        is_percent_growth
FROM    sys.database_files;

-- 调整为固定 256MB 增长
ALTER DATABASE DemoDB MODIFY FILE ( NAME = N'OrderData1', FILEGROWTH = 256MB );

常见误区

  • 误区:开启 autogrow 就不用管容量。正解:autogrow 是突发兜底,频繁自动增长说明初始容量规划失败。
  • 误区:数据文件和日志文件用同样的增长策略。正解:日志增长影响 VLF 结构,需单独评估。

追问

  • 问:即时文件初始化需要什么权限?答:给 SQL Server 服务账号授予"执行卷维护任务"权限;日志文件无法跳过零填充。
  • 问:为什么不建议把 growth 设为百分比?答:文件越大百分比增量越大,单次扩文件时间越长,易引发写入停顿。

05.4.1.1-010 事务日志的 VLF 和 LSN 是什么?

频率 ★★★★☆ | 难度 进阶

一句话结论

LDF 内部由若干 VLF(虚拟日志文件) 循环使用;每条日志记录有唯一 LSN(日志序列号),恢复、复制与高可用都按 LSN 顺序追踪。

概念解释

  • VLF:日志文件内部的分段,文件增长时按规则生成一批 VLF;VLF 过多过小会降低日志写入与恢复效率,过少过大则日志管理不灵活。
  • LSN:单调递增的日志序号,标识日志记录位置;事务提交、redo/undo、副本同步都依赖 LSN 定位。
  • 日志链(Log Chain):从最早可恢复 LSN 到当前 LSN 的连续记录;日志备份断裂会影响 PITR。

原理流程

  • 事务修改先写日志(WAL 思路),提交时日志落盘确认;日志空间在备份/截断后才能复用。
  • 完整恢复模式下日志备份截断旧 LSN;简单恢复模式下 checkpoint 后自动截断。

代码 / 命令

-- 查看日志文件使用率与 VLF 概览
DBCC SQLPERF(LOGSPACE);

-- 查看当前日志 LSN 与备份链
SELECT  name, log_reuse_wait_desc, checkpoint_lsn,
        database_lsn, recovery_lsn
FROM    sys.databases WHERE name = N'DemoDB';

常见误区

  • 误区:日志文件 shrink 后空间立即变小且性能更好。正解:频繁 shrink/grow 会产生大量碎片与小 VLF,应先备份日志再谨慎收缩。
  • 误区:VLF 越少越好。正解:要平衡——数量适中、单个不过大;极端过小或过大都有问题。

追问

  • 问:日志为什么不能像数据那样随机写?答:日志必须按提交顺序追加,顺序写才能保证可恢复性。
  • 问:log_reuse_wait_desc 常见值有哪些?答:NOTHING 可复用;LOG_BACKUP 等待日志备份;ACTIVE_BACKUP_OR_RESTORE、REPLICATION 等表示对应活动阻塞截断。

05.4.1.1-011 三种恢复模式分别是什么?和备份策略是什么关系?

频率 ★★★★☆ | 难度 进阶

一句话结论

恢复模式决定日志记录保留与可备份方式:完整(Full) 支持日志备份与 PITR;简单(Simple) 日志自动截断,不能日志备份;大容量日志(Bulk-logged) 对批量操作最小化记录,配合完整模式短期使用。

概念解释

恢复模式 日志保留 可做备份 典型场景
完整 Full 保留到日志备份 完整 + 差异 + 日志备份,支持时点恢复 生产核心库
简单 Simple checkpoint 后自动截断 完整 + 差异,不能日志备份 测试库、可重建的派生数据
大容量日志 Bulk-logged 批量操作最小记录 完整 + 差异 + 日志备份(批量期间不能精确时点恢复) 大批量导入窗口临时切换

原理流程

  • 完整模式:周期完整备份 → 差异备份 → 频繁日志备份;恢复时按顺序还原完整、差异、日志链到指定时点。
  • 批量日志模式下,BULK INSERT、SELECT INTO 等操作按区最小记录,缩小日志体积。

代码 / 命令

-- 查看与设置恢复模式
SELECT name, recovery_model_desc FROM sys.databases WHERE name = N'DemoDB';
ALTER DATABASE DemoDB SET RECOVERY FULL;

-- 日志备份(完整模式下周期性执行)
BACKUP LOG DemoDB TO DISK = N'/backup/DemoDB.trn';

常见误区

  • 误区:完整模式下日志文件会一直无限增长。正解:周期性日志备份会截断可复用的旧日志段;不备份日志才会持续增长。
  • 误区:批量日志模式可以长期替代完整模式。正解:它牺牲了批量期间的时点恢复能力,只用于导入窗口。

追问

  • 问:PITR(时点恢复)依赖哪种备份?答:完整恢复模式下的日志备份链。
  • 问:从简单模式切到完整模式要注意什么?答:切换后应先做一次完整备份,开始新的日志链。

05.4.1.1-012 checkpoint 有哪些类型?起什么作用?

频率 ★★★☆☆ | 难度 高级

一句话结论

checkpoint 把 Buffer Pool 中的脏页写入数据文件,并记录恢复基准 LSN;用于缩短崩溃恢复时间,类型分自动、手动、间接、被动与启动 checkpoint。

概念解释

  • 自动 checkpoint:按服务器配置的恢复间隔/脏页比例周期触发。
  • 手动 checkpoint:CHECKPOINT 语句显式触发。
  • 间接 checkpoint:以目标恢复时间(TARGET_RECOVERY_TIME)为驱动,持续平滑刷脏,避免集中刷盘。
  • 被动 checkpoint:日志写满前由引擎触发以截断日志。
  • 启动 checkpoint:实例重启时的恢复起点。

原理流程

  • checkpoint 记录脏页落盘状态与最小恢复 LSN;崩溃恢复只需从该 LSN 之后 redo。
  • 刷脏速度跟不上日志生成速度时,写入会被拖住。

代码 / 命令

-- 手动触发 checkpoint
CHECKPOINT;

-- 查看数据库恢复目标与脏页相关配置
SELECT name, target_recovery_time_in_seconds, log_reuse_wait_desc
FROM sys.databases WHERE name = N'DemoDB';

常见误区

  • 误区:checkpoint 提交事务。正解:事务提交由日志落盘保证;checkpoint 只负责把脏页刷盘。
  • 误区:间接 checkpoint 一定比自动 checkpoint 快。正解:它把刷盘压力分摊到平时,峰值写更平滑,但总 IO 不变。

追问

  • 问:脏页是什么?答:Buffer Pool 中已修改但尚未写入数据文件的页。
  • 问:崩溃恢复从哪里开始 redo?答:从最近 checkpoint 记录的最小恢复 LSN 开始。

05.4.1.1-013 Buffer Pool 是怎么缓存数据页的?脏页与惰性写入是什么关系?

频率 ★★★☆☆ | 难度 进阶

一句话结论

Buffer Pool 以 8KB 页为单位缓存数据/索引页;修改先在内存中完成并标记脏页,再由 checkpoint 与惰性写入器(Lazy Writer)择机落盘。

概念解释

  • Buffer Pool:实例最大的一块内存,缓存热页,命中内存避免磁盘随机读。
  • 脏页(Dirty Page):内存版本与磁盘不一致的页。
  • Lazy Writer:在内存压力下扫描最近最少使用的页,把脏页写回磁盘并释放缓冲。

原理流程

  • 读请求先查 Buffer Pool,未命中再从磁盘读入页。
  • 写请求改内存页 → 写日志 → 标记脏页;脏页由 checkpoint / lazy writer 异步落盘。

代码 / 命令

-- 查看 Buffer Pool 命中率
SELECT  object_name, counter_name, cntr_value
FROM    sys.dm_os_performance_counters
WHERE   counter_name IN ('Buffer cache hit ratio', 'Page life expectancy');

-- 查看各库缓存页数
SELECT  database_id, COUNT(*) AS cached_pages, COUNT(*) * 8 / 1024 AS cached_mb
FROM    sys.dm_os_buffer_descriptors
GROUP BY database_id ORDER BY cached_pages DESC;

常见误区

  • 误区:DBCC DROPCLEANBUFFERS 可以在生产随手执行。正解:它清空所有干净缓存,会导致后续查询大量物理读,只用于测试基准。
  • 误区:Buffer Pool 越大越好、不用留内存给系统。正解:需预留操作系统与其他组件内存,配置过大可能导致系统换页。

追问

  • 问:Page Life Expectancy(PLE)衡量什么?答:页在缓存中平均存活秒数;持续偏低说明内存压力或扫描过大。
  • 问:脏页落盘前实例断电会丢数据吗?答:已提交事务的日志已落盘,崩溃恢复可从日志 redo 回来。

05.4.1.1-014 什么是页分裂?填充因子(fill factor)如何缓解?

频率 ★★★★☆ | 难度 进阶

一句话结论

聚集/索引叶子页写满后插入新键,需要把一半行挪到新页,即页分裂;填充因子预留页内空闲空间,减少后续插入导致的分裂。

概念解释

  • 页分裂:目标页满时拆分页面,带来额外 IO、页碎片化与索引体积膨胀。
  • 填充因子:重建索引时叶子页保留的空闲百分比;100 表示不留空闲,适合只读为主;低于 100 为未来更新/插入留空间。
  • PAD_INDEX:把填充比例同样应用到非叶子节点。

原理流程

  • 自增递增键总落在右侧页,顺序写分裂少;随机键(如 GUID)插入中间页,分裂频繁。
  • 页内空闲耗尽 → 分配新页 → 搬迁半数行 → 更新页链与索引项。

代码 / 命令

-- 查看填充因子配置
SELECT name, fill_factor FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.Orders');

-- 重建索引并指定填充因子
ALTER INDEX PK_Orders ON dbo.Orders REBUILD WITH (FILLFACTOR = 90, PAD_INDEX = ON);

常见误区

  • 误区:填充因子设得越低越好。正解:预留空间越多,索引体积越大、缓存效率越低;只在有大量随机插入/更新的表上降低。
  • 误区:填充因子对堆表有效。正解:填充因子作用于索引页;堆表没有叶子键序概念。

追问

  • 问:GUID 聚集键为什么分裂严重?答:随机值插入 B 树中间位置,频繁命中满页触发分裂。
  • 问:已经分裂出的碎片怎么收回?答:重建(REBUILD)或重组(REORGANIZE)索引,见 05.4.1.2-010。

05.4.1.1-015 页面碎片怎么评估?什么时候重建、什么时候重组?

频率 ★★★☆☆ | 难度 进阶

一句话结论

碎片分内部碎片(页内空闲/稀疏)与外部碎片(区物理不连续);用 sys.dm_db_index_physical_stats 看碎片百分比与页数,低碎片重组、高碎片重建。

概念解释

  • 内部碎片:页内有较多空闲空间,有效数据密度低。
  • 外部碎片:索引页在物理区上不连续,顺序扫描时寻道增加。
  • REORGANIZE(重组):整理叶子页顺序与压缩页内空间,联机进行,开销较小。
  • REBUILD(重建):重建整个索引,彻底消除碎片,但代价高、可能锁/占资源(联机重建视版本与选项)。

原理流程

  • 经验分界(需结合实际页数验证):碎片 < 10% 通常无需处理;10%~30% 可重组;> 30% 且索引较大时考虑重建。
  • 小索引即使碎片比例高,绝对页数少,处理收益有限。

代码 / 命令

SELECT  OBJECT_NAME(ps.object_id) AS table_name, i.name AS index_name,
        ps.index_type_desc, ps.avg_fragmentation_in_percent,
        ps.page_count, ps.fragment_count
FROM    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN    sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE   ps.page_count > 1000
ORDER BY avg_fragmentation_in_percent DESC;

-- 重组 / 重建
ALTER INDEX IX_Orders_Status ON dbo.Orders REORGANIZE;
ALTER INDEX PK_Orders ON dbo.Orders REBUILD WITH (ONLINE = ON);

常见误区

  • 误区:碎片百分比高就立刻重建全库索引。正解:先看页数与业务收益,小表处理价值低;重建会占用大量 IO 与日志。
  • 误区:重组能消除外部碎片。正解:重组主要整理页内与叶子链顺序,彻底去碎片要靠重建。

追问

  • 问:LIMITED 扫描模式和 DETAILED 区别?答:LIMITED 快但不读 LOB 页;DETAILED 统计更全但成本高。
  • 问:联机重建(ONLINE = ON)受什么限制?答:部分索引类型/版本不支持,具体行为需按目标版本文档核对。

05.4.1.1-016 text/ntext/image 与 varchar(max) 等大类型有什么演进?

频率 ★★☆☆☆ | 难度 进阶

一句话结论

text/ntext/image 是早期 LOB 类型,已不推荐新开发使用;现代等价类型为 varchar(max)/nvarchar(max)/varbinary(max),功能更完整。

概念解释

  • text/ntext/image:旧版大对象类型,部分 T-SQL 函数与索引行为受限。
  • varchar(max) 等:最大容量与旧 LOB 相当,支持常规字符串操作与更多函数。
  • nvarchar(max) 存 Unicode;varbinary(max) 存二进制(文件、图片等)。

原理流程

  • 大值优先离页存 LOB 页链;小值可保留行内,由引擎按大小决定。

代码 / 命令

-- 旧类型迁移为现代类型(示例,变更前需评估长度与依赖)
ALTER TABLE dbo.Article ALTER COLUMN Content NVARCHAR(MAX);

常见误区

  • 误区:(MAX) 列可以随意建普通索引。正解:大列不能直接作为普通索引键,需考虑前缀/全文/列存方案。
  • 误区:nvarchar(max) 与 varchar(max) 存储成本相同。正解:Unicode 按字符双倍空间,需按内容选择。

追问

  • 问:存大文件用数据库还是文件系统/对象存储?答:超大文件建议文件系统/对象存储,库内只存路径或元数据。
  • 问:xml 类型与 nvarchar(max) 怎么选?答:需要 XQuery/索引时用 xml,纯文本展示用 nvarchar(max)。

05.4.1.1-017 数据压缩有哪些层级?各自适合什么场景?

频率 ★★☆☆☆ | 难度 高级

一句话结论

SQL Server 提供行压缩、页压缩与列存压缩;压缩降低存储与缓冲占用,但增加 CPU 解压成本,适合读多写少、数据仓库类大表。

概念解释

  • 行压缩(Row):按类型压缩定长列、减少未用空间,元数据更紧凑。
  • 页压缩(Page):在行压缩基础上做前缀压缩与字典压缩,消除页内重复值。
  • 列存档压缩(Columnstore Archive):面向列存的高压缩率模式,用于冷数据归档。

原理流程

  • 读取时解压到内存,写入时重新压缩;压缩比取决于重复度与类型分布。
  • 压缩通常在重建表/索引时指定。

代码 / 命令

-- 查看对象压缩配置
SELECT  OBJECT_NAME(object_id) AS table_name, compression_desc
FROM    sys.partitions WHERE compression_desc <> 'NONE';

-- 对表启用页压缩
ALTER TABLE dbo.FactSales REBUILD WITH (DATA_COMPRESSION = PAGE);

常见误区

  • 误区:压缩对所有表都能提速。正解:压缩节省 IO 与内存,但 CPU 开销上升;高并发写、CPU 饱和的库要压测后再开。
  • 误区:页压缩包含行压缩。正解:页压缩在行压缩之上叠加,开启页压缩即包含行压缩行为。

追问

  • 问:压缩后统计信息会变吗?答:压缩改变行宽与页数,重建后应更新统计信息。
  • 问:列存压缩与行存压缩能叠加吗?答:列存走自己的段/字典压缩机制,与行存压缩不是同一套。

05.4.1.1-018 SQL Server 的页/区结构与 InnoDB、PostgreSQL 有什么对照?

频率 ★★★★☆ | 难度 高级

一句话结论

三者都按页管理磁盘,但页大小、组织方式与日志模型不同:SQL Server 8KB 页 + 64KB 区,InnoDB 16KB 页 + 1MB 区,PostgreSQL 8KB 页堆表。

概念解释

维度 SQL Server MySQL InnoDB PostgreSQL
默认页大小 8KB 16KB 8KB
空间单位 64KB 区(8 页) 1MB 区(64 页) 页 + 表空间,无同款区概念
表组织 堆或聚集索引表 聚簇索引组织表 堆表,多版本行存于堆
日志 LDF(VLF + LSN) redo log + undo log + binlog WAL(pg_wal)
缓存 Buffer Pool(8KB 页) Buffer Pool(16KB 页) shared_buffers + OS page cache

原理流程

  • 对照学习时抓住一条主线:页是 IO 单位,区是分配单位,日志是恢复依据;三者实现不同,目标一致——减少随机 IO、保证可恢复。

代码 / 命令

-- SQL Server:确认页大小(引擎固定 8KB,可通过错误日志/文档核对)
SELECT  @@VERSION;

常见误区

  • 误区:三者的"区"是同一概念。正解:InnoDB 区为 1MB,SQL Server 区为 64KB,PG 没有同款统一区概念。
  • 误区:SQL Server 的 LDF 同时对应 InnoDB 的 redo 和 binlog。正解:LDF 更接近引擎 redo/WAL;SQL Server 没有独立于引擎的 binlog 等价物,变更捕获用 CDC/变更跟踪。

追问

  • 问:为什么 InnoDB 默认页更大?答:B+ 树扇出与行宽权衡,16KB 是 InnoDB 的历史默认值。
  • 问:PG 堆表为什么需要 HOT/VACUUM?答:更新产生新版本留在堆中,需 HOT 链与 VACUUM 回收,详见 05.3.1。

参考来源