SQL Server 架构与存储

SQL Server 的存储要抓住：数据库文件、页、区、Buffer Pool、事务日志。

零基础学习 SQL Server 架构时，先记住一句话：

SQL Server 不是直接读写一行数据，而是围绕“页”读写；不是修改完立刻把数据页刷盘，而是先写事务日志保证可恢复，再让后台把脏页写回数据文件。

整体执行链路

mermaid

flowchart TD
    A["客户端提交 SQL"] --> B["关系引擎解析、绑定、优化"]
    B --> C["生成执行计划"]
    C --> D["存储引擎访问页"]
    D --> E{"Buffer Pool 是否命中"}
    E -- "命中" --> F["直接读内存页"]
    E -- "未命中" --> G["从 MDF/NDF 读取 8KB 页"]
    G --> F
    F --> H{"是否修改数据"}
    H -- "否" --> I["返回结果"]
    H -- "是" --> J["写事务日志到 LDF"]
    J --> K["修改 Buffer Pool 中数据页"]
    K --> L["提交后数据页后台刷盘"]

这个流程解释了很多现象：

为什么第一次查询慢、第二次可能快：第二次页可能已经在 Buffer Pool。
为什么逻辑读高会慢：读了很多 8KB 页，即使都在内存也消耗 CPU 和缓存。
为什么 commit 不等于数据页立即落盘：事务日志先保证可恢复。
为什么日志磁盘慢会拖慢写入：提交要等待日志持久化。

数据库文件

文件	说明
MDF	主数据文件
NDF	次数据文件，可选
LDF	事务日志文件

mermaid

flowchart TD
    A["Database"] --> B["MDF 主数据文件"]
    A --> C["NDF 次数据文件"]
    A --> D["LDF 事务日志文件"]

MDF、NDF、LDF 分别承担什么

文件	保存内容	类比	生产关注点
MDF	主数据文件，保存表和索引页	主要仓库	容量、IO、文件增长
NDF	次数据文件，可把数据分布到多个文件组	分仓库	大库分文件组、冷热数据
LDF	事务日志文件	操作流水和恢复凭据	日志备份、磁盘延迟、暴涨

不要把 LDF 当普通日志文件删除。它参与事务恢复和高可用同步，删掉可能导致数据库不可用。

页和区

SQL Server 数据页通常是 8KB。区 Extent 是连续页的集合。

mermaid

flowchart TD
    A["表 / 索引"] --> B["Extent 区"]
    B --> C["Page 8KB"]
    C --> D["Row 行"]

数据库读取不是一行一行从磁盘拿，而是以页为单位进入 Buffer Pool。

为什么页是理解性能的核心

假设一行资产数据 200 字节，一个 8KB 页能放几十行。查询 20 条数据不一定只读 20 行，可能读多个页；如果索引不合适，甚至要扫描成千上万个页。

现象	页层面的解释
覆盖索引快	只读索引页，不需要再读数据页
Key Lookup 慢	每条索引命中可能再回读数据页
全表扫描慢	需要把大量数据页读一遍
宽表查询慢	每页放的行少，读同样行数需要更多页
`select *` 慢	需要更多列，可能无法覆盖索引

区 Extent 和对象分配

区是连续 8 个页的集合。SQL Server 用区来管理空间分配，比一页一页分配更高效。

mermaid

flowchart TD
    A["表或索引增长"] --> B["申请页"]
    B --> C["SQL Server 从区中分配页"]
    C --> D["页写入行或索引记录"]

对开发者来说，不需要天天手动管理区，但要理解：大批量写入会申请大量页和区，数据文件增长、页分裂、索引维护都会消耗资源。

Buffer Pool

mermaid

flowchart TD
    A["查询数据"] --> B{"Buffer Pool 有页吗"}
    B -- "有" --> C["内存读取"]
    B -- "没有" --> D["从磁盘读页"]
    D --> C

Buffer Pool 缓存数据页和索引页。查询扫描页越多，缓存压力越大。

逻辑读和物理读

指标	含义	怎么理解
逻辑读	从 Buffer Pool 读取页的次数	即使不读磁盘，也要访问内存页
物理读	Buffer Pool 没有，从磁盘读取页	通常更慢
预读	SQL Server 预测后续需要，提前读页	扫描大范围时常见

查看 SQL 逻辑读：

sql

set statistics io on;

select top (20) Id, AssetNo
from dbo.Asset
where HospitalId = 1001
order by CreatedAt desc;

如果一个列表接口逻辑读几万页，即使响应还勉强能接受，也会在高并发下挤压 Buffer Pool，影响其他接口。

脏页和 Checkpoint

数据页在 Buffer Pool 中被修改后，叫脏页。脏页不一定在事务提交时立刻写回 MDF/NDF。

mermaid

flowchart TD
    A["update 修改数据"] --> B["写事务日志"]
    B --> C["修改内存中的数据页"]
    C --> D["数据页变成脏页"]
    D --> E["事务提交：日志落盘"]
    E --> F["Checkpoint 或后台写入"]
    F --> G["脏页写回数据文件"]

为什么这样设计：

如果每次提交都刷所有数据页，随机 IO 太多。
日志是顺序写，更适合快速提交。
宕机后可以通过日志恢复数据页状态。

不这样做会怎样：提交延迟会非常高，高并发 OLTP 系统很难承受。

事务日志

事务日志记录数据修改过程，用于：

事务回滚。
崩溃恢复。
日志备份。
Always On、复制等高可用能力。

mermaid

flowchart TD
    A["修改数据页"] --> B["写事务日志"]
    B --> C["事务提交"]
    C --> D["数据页稍后刷盘"]
    D --> E["崩溃后用日志恢复"]

WAL：先写日志再写数据

SQL Server 遵循 Write-Ahead Logging 思想：数据页写回磁盘前，相关日志必须先持久化。这样即使宕机，恢复时也知道哪些修改应该重做、哪些未提交事务应该撤销。

mermaid

flowchart TD
    A["事务开始"] --> B["修改数据前记录日志"]
    B --> C["修改 Buffer Pool 数据页"]
    C --> D["commit 等待日志刷盘"]
    D --> E["返回提交成功"]
    E --> F["后台稍后刷数据页"]

恢复阶段可以简单理解为：

阶段	做什么
Analysis	分析日志，找出脏页和未完成事务
Redo	重做已提交或需要重做的修改
Undo	撤销未提交事务

聚集索引、非聚集索引和堆表

SQL Server 表有两种常见组织方式：

表组织	说明	生产建议
聚集索引表	聚集索引叶子层就是数据行	大多数业务表推荐有合理聚集索引
堆表 Heap	没有聚集索引，数据行无序存放	临时导入表可用，核心业务表谨慎

非聚集索引叶子层保存索引键和行定位器。若表有聚集索引，行定位器通常是聚集键；若是堆表，行定位器是 RID。

mermaid

flowchart TD
    A["非聚集索引 IX_Asset_Hospital"] --> B["找到索引键"]
    B --> C{"查询字段都在索引里吗"}
    C -- "是" --> D["覆盖索引直接返回"]
    C -- "否" --> E["通过聚集键或 RID 回表"]
    E --> F["读取数据行"]

这就是 Key Lookup 的来源。返回行数少时问题不大，返回行数多时会产生大量随机访问。

TempDB 在架构中的位置

TempDB 是 SQL Server 的临时工作区，常用于：

临时表和表变量。
排序、Hash Join、Hash 聚合的中间结果。
行版本存储，比如 RCSI。
游标和内部工作表。

mermaid

flowchart TD
    A["复杂查询"] --> B{"是否需要中间结果"}
    B -- "大排序" --> C["TempDB"]
    B -- "Hash 溢写" --> C
    B -- "RCSI 行版本" --> C
    C --> D["TempDB IO 和空间压力"]

如果 TempDB 出问题，很多业务都会一起慢。开启 RCSI、报表大排序、临时表滥用都要关注 TempDB。

一条 update 的完整过程

mermaid

flowchart TD
    A["update Asset set Status = 2"] --> B["优化器选择索引定位行"]
    B --> C["存储引擎读取索引页和数据页"]
    C --> D["对目标行加锁"]
    D --> E["写事务日志"]
    E --> F["修改 Buffer Pool 中的数据页"]
    F --> G["维护相关索引"]
    G --> H["commit 日志刷盘"]
    H --> I["释放锁并返回成功"]
    I --> J["脏页后台写回 MDF/NDF"]

这个过程解释了：

为什么 update 缺少索引会锁更多行：定位目标行前需要扫描更多数据。
为什么索引太多会拖慢写入：每个相关索引都要维护。
为什么大事务会占用日志：修改越多，日志越多。
为什么 commit 可能慢：等待日志刷盘或同步副本确认。

商业场景：资产采集批量入库

资产采集平台每分钟写入大量设备状态，如果每条都单独提交：

sql

insert into dbo.AssetCollectLog(...)
values (...);

可能出现：

日志刷盘频繁。
索引维护频繁。
数据文件增长。
Buffer Pool 被写入页挤压。
查询和写入互相影响。

更合理的思路：

批量写入，但控制批次大小。
核心唯一约束保留，非核心索引谨慎添加。
日志文件预分配合理大小，避免频繁自动增长。
采集明细和业务主表分开。
冷历史归档或分区。

常见坑

坑	后果	正确理解
认为 SQL Server 一行一行读磁盘	无法解释逻辑读	以 8KB 页为单位读写
删除 LDF 日志文件	数据库可能损坏或无法启动	LDF 是恢复机制核心
认为 commit 就刷数据页	无法解释脏页和恢复	commit 主要保证日志持久化
索引越多越好	写入变慢、空间增加	索引是读写权衡
忽略 TempDB	报表、RCSI、排序一起慢	TempDB 是临时工作区

面试标准回答

text

SQL Server 数据库存储包括 MDF 主数据文件、可选 NDF 次数据文件和 LDF 事务日志文件。数据以 8KB 页为基本单位，页组成区，表和索引都由页组织。查询和更新优先访问 Buffer Pool 中的数据页和索引页，未命中才从磁盘读取。更新数据时会先写事务日志，再修改内存中的数据页，commit 主要保证日志持久化，脏页可以由 Checkpoint 或后台线程稍后写回数据文件。事务日志用于回滚、崩溃恢复、日志备份和 Always On 等高可用能力。理解页、Buffer Pool、事务日志、聚集索引、Key Lookup 和 TempDB，才能真正排查 SQL Server 性能问题。

JDK 8/11/17/21 LTS演进

线程池

SQL Server 架构与存储 ​

整体执行链路 ​

数据库文件 ​

MDF、NDF、LDF 分别承担什么 ​

页和区 ​

为什么页是理解性能的核心 ​

区 Extent 和对象分配 ​

Buffer Pool ​

逻辑读和物理读 ​

脏页和 Checkpoint ​

事务日志 ​

WAL：先写日志再写数据 ​

聚集索引、非聚集索引和堆表 ​

TempDB 在架构中的位置 ​

一条 update 的完整过程 ​

商业场景：资产采集批量入库 ​

常见坑 ​

面试标准回答 ​

SQL Server 架构与存储

整体执行链路

数据库文件

MDF、NDF、LDF 分别承担什么

页和区

为什么页是理解性能的核心

区 Extent 和对象分配

Buffer Pool

逻辑读和物理读

脏页和 Checkpoint

事务日志

WAL：先写日志再写数据

聚集索引、非聚集索引和堆表

TempDB 在架构中的位置

一条 update 的完整过程

商业场景：资产采集批量入库

常见坑

面试标准回答