SQL Server 从零到生产级掌握

SQL Server 不能只学成“会写 T-SQL”。真正上项目时，你要能解释：表数据为什么按页存，聚集索引为什么叶子层就是数据，非聚集索引为什么会 Key Lookup，事务日志为什么能恢复，Read Committed 为什么会阻塞，RCSI 为什么会增加 TempDB 压力，参数嗅探为什么让同一条 SQL 忽快忽慢。

一句话建立主线：

SQL Server 是微软生态中常用的企业级关系型数据库。它用查询优化器选择执行计划，用存储引擎按页读写数据，用 Buffer Pool 缓存页，用事务日志保证恢复，用锁和行版本处理并发。

学习目标

学完这一页，你要能做到：

从零创建数据库、表、主键、唯一索引、非聚集索引和查询 Demo。
解释 MDF、NDF、LDF、页、区、Buffer Pool、TempDB 分别负责什么。
解释聚集索引、非聚集索引、堆表、RID Lookup、Key Lookup 的区别。
解释一条 select 从解析、优化、执行计划到 Buffer Pool 读页的全过程。
解释一条 update 为什么要获取锁、写事务日志、改缓存页、维护索引和提交。
看懂实际执行计划中的 Seek、Scan、Lookup、Nested Loops、Hash Match、Sort。
排查慢 SQL、阻塞、死锁、锁升级、参数嗅探、TempDB 压力、日志文件暴涨。

如果你已经读完主线，但还不知道怎么落到项目里，继续做：SQL Server 商业场景训练营。它把聚集索引、非聚集索引、Key Lookup、output inserted、阻塞、RCSI、事务日志、TempDB 和参数嗅探串成可验证训练。

学习路线

mermaid

flowchart TD
    A["T-SQL 基础<br/>建表、查询、分页"] --> B["存储结构<br/>数据文件、日志文件、页、区"]
    B --> C["索引结构<br/>聚集、非聚集、堆表"]
    C --> D["执行计划<br/>Seek、Scan、Lookup、Join"]
    D --> E["事务日志<br/>提交、回滚、恢复"]
    E --> F["锁与行版本<br/>阻塞、死锁、RCSI"]
    F --> G["生产排查<br/>等待、TempDB、参数嗅探"]

顺序很重要。你先要知道表怎么建、数据怎么查，再理解为什么索引能让查询少读页；先会看执行计划，再谈参数嗅探；先理解锁和日志，再排查阻塞和日志暴涨。

第一步：数据库、文件、页和区

SQL Server 数据库至少会涉及两类文件：

文件	作用	常见扩展名
Data File	保存表数据、索引数据等	`.mdf`、`.ndf`
Log File	保存事务日志	`.ldf`

数据文件内部不是按“一行一个文件”存，而是按页组织。SQL Server 常见数据页大小是 8KB，区 Extent 是一组页。

mermaid

flowchart TD
    A["Database"] --> B["Data File<br/>MDF/NDF"]
    A --> C["Log File<br/>LDF"]
    B --> D["Extent 区"]
    D --> E["Page 页<br/>8KB"]
    E --> F["Row 行"]
    C --> G["Transaction Log Records"]

为什么页很重要：

Buffer Pool 缓存的是页。
逻辑读统计的是读了多少页。
索引层级也是由页组成。
查询慢很多时候不是“行少不少”，而是“页读得多不多”。
行很宽会导致一页放不下多少行，同样行数需要读更多页。

第二步：创建表和基础索引

以医疗资产平台为例，建立资产表：

sql

create table dbo.Asset (
  Id bigint identity(1,1) not null,
  AssetNo varchar(64) not null,
  HospitalId bigint not null,
  DepartmentId bigint null,
  AssetName nvarchar(128) not null,
  Status varchar(20) not null,
  CreatedAt datetime2 not null default sysdatetime(),
  UpdatedAt datetime2 not null default sysdatetime(),
  constraint PK_Asset primary key clustered (Id),
  constraint UX_Asset_AssetNo unique (AssetNo),
  constraint CK_Asset_Status
    check (Status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

列表查询索引：

sql

create index IX_Asset_Hospital_Status_CreatedAt
on dbo.Asset(HospitalId, Status, CreatedAt desc)
include (AssetNo, AssetName);

为什么这样设计：

设计	原因
`Id` 聚集主键	插入递增，数据组织稳定
`AssetNo` 唯一约束	防止资产编号重复
`Status` check	防止脏状态进入数据库
`HospitalId, Status, CreatedAt` 联合索引	支撑医院 + 状态 + 时间分页
`include` 查询列	减少 Key Lookup

不要把数据库约束都交给后端代码。商业系统会有接口、脚本、导入任务、补偿任务、数据修复脚本同时写库，数据库约束是最后防线。

第三步：聚集索引、非聚集索引和堆表

SQL Server 的索引理解，最关键是这句话：

聚集索引的叶子层就是数据行；非聚集索引的叶子层保存索引键和行定位器。

聚集索引

mermaid

flowchart TD
    A["Clustered Index Root"] --> B["Intermediate Pages"]
    B --> C["Leaf Pages"]
    C --> D["完整数据行"]

特点：

特点	解释
一个表只能有一个	数据行只能按一种聚集结构组织
叶子层就是数据行	找到叶子页就拿到整行
聚集键很重要	非聚集索引会用它作为回表定位器

聚集键不建议太宽、频繁更新、随机性太强。过宽会放大所有非聚集索引；频繁更新会导致维护成本高；随机 GUID 做聚集键容易页分裂和碎片。

非聚集索引和 Key Lookup

mermaid

flowchart TD
    A["非聚集索引 Seek"] --> B["Leaf Page<br/>索引键 + 聚集键"]
    B --> C["Key Lookup"]
    C --> D["回到聚集索引取完整行"]

示例：

sql

select Id, AssetNo, AssetName, Status
from dbo.Asset
where HospitalId = @HospitalId
  and Status = @Status
order by CreatedAt desc;

如果非聚集索引只有 (HospitalId, Status, CreatedAt)，但查询还要 AssetName，计划可能出现 Key Lookup。少量 Lookup 没问题，大量 Lookup 会变成一行一回表，随机访问多，性能明显下降。

解决方式：

sql

create index IX_Asset_Hospital_Status_CreatedAt
on dbo.Asset(HospitalId, Status, CreatedAt desc)
include (AssetNo, AssetName);

但 include 不是越多越好：

索引变宽，占用更多页。
写入和更新要维护更多数据。
Buffer Pool 缓存压力变大。
低频查询不值得专门覆盖。

堆表和 RID Lookup

没有聚集索引的表叫堆表。非聚集索引叶子层保存 RID 定位行。

mermaid

flowchart TD
    A["非聚集索引"] --> B["RID"]
    B --> C["Heap Page"]
    C --> D["读取数据行"]

堆表不是不能用，但 OLTP 业务表通常更推荐有合适的聚集索引。堆表可能出现转发行、碎片和 RID Lookup 问题，排查和维护复杂度更高。

第四步：一条 SELECT 怎么执行

查询示例：

sql

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

执行过程：

mermaid

flowchart TD
    A["提交 SQL"] --> B["解析和绑定"]
    B --> C["优化器读取统计信息"]
    C --> D["估算不同访问路径成本"]
    D --> E["选择执行计划"]
    E --> F["执行计划算子运行"]
    F --> G["Index Seek / Scan"]
    G --> H["Buffer Pool 查页"]
    H --> I{"页是否在内存"}
    I -- "是" --> J["逻辑读"]
    I -- "否" --> K["物理读磁盘"]
    K --> J
    J --> L["Top / Sort / 返回结果"]

看实际执行计划时重点看：

观察点	为什么重要
Index Seek 还是 Scan	Seek 通常定位更精确，Scan 可能读很多页
Key Lookup 次数	次数多会拖慢
Estimated Rows vs Actual Rows	差距大说明估算或参数问题
Logical Reads	读了多少页，是 SQL 成本核心指标之一
Sort / Hash Match	可能需要较大内存或 TempDB
Warnings	Hash/Sort 溢出、隐式转换等

打开执行统计：

sql

set statistics io on;
set statistics time on;

然后执行 SQL，观察逻辑读和 CPU 时间。真正优化时不要只看耗时，因为耗时会受缓存、并发、锁影响；逻辑读更能反映访问路径是否合理。

第五步：UPDATE 和事务日志全过程

状态流转：

sql

update dbo.Asset
set Status = 'USED',
    UpdatedAt = sysdatetime()
where AssetNo = @AssetNo
  and Status = 'IDLE';

执行过程：

mermaid

flowchart TD
    A["通过唯一索引定位 AssetNo"] --> B["读取目标页到 Buffer Pool"]
    B --> C["检查 Status 条件"]
    C --> D["获取更新锁/排他锁"]
    D --> E["写事务日志记录修改"]
    E --> F["修改内存中的数据页"]
    F --> G["维护受影响索引"]
    G --> H["commit"]
    H --> I["日志刷盘"]
    I --> J["返回成功"]
    J --> K["脏页后续写回 MDF/NDF"]

事务日志为什么先于数据页可靠：

设计	作用
先写日志	宕机后知道怎么恢复
数据页延迟刷盘	避免每次提交随机写数据文件
恢复时重做已提交	保证提交不丢
恢复时撤销未提交	保证未提交不污染

日志不是普通业务日志。它是数据库恢复的依据。Full 恢复模式下还依赖日志备份支持时间点恢复。

第六步：Read Committed、锁和阻塞

SQL Server 默认 Read Committed 下，读通常会加共享锁，写会加排他锁。共享锁和排他锁冲突。

mermaid

sequenceDiagram
    participant A as 事务A
    participant B as 事务B
    A->>A: update dbo.Asset set Status='USED' where Id=1
    A->>A: 未提交，持有排他锁
    B->>B: select * from dbo.Asset where Id=1
    B-->>A: 等待排他锁释放
    A->>A: commit
    B->>B: 继续读取

常见锁：

锁	作用
S	共享锁，读常见
X	排他锁，写使用
U	更新锁，降低锁转换死锁概率
IS/IX	意向锁，表示更细粒度对象有锁
Sch-S/Sch-M	架构稳定/架构修改锁

如果接口卡住，不要先重启。要先找阻塞链：谁在等，谁挡住了别人，阻塞会话是否事务未提交。

第七步：RCSI 和行版本

Read Committed Snapshot Isolation 可以让 Read Committed 下的读使用版本，减少读写阻塞。

sql

alter database YourDb
set read_committed_snapshot on
with rollback immediate;

原理简化：

mermaid

flowchart TD
    A["写事务更新行"] --> B["旧版本进入版本存储"]
    B --> C["新版本留在数据页"]
    D["读事务"] --> E["按语句开始时间读取一致版本"]
    E --> F["必要时从 TempDB 版本存储取旧版本"]

RCSI 的好处和代价：

好处	代价
普通读不容易被写阻塞	TempDB 版本存储增长
查询体验更接近快照读	长事务会保留旧版本
读写并发提升	需要监控 TempDB IO 和空间

不要以为打开 RCSI 就没有锁了。写写冲突仍然需要锁，DDL 仍可能阻塞，长事务仍可能制造版本存储压力。

第八步：参数嗅探为什么忽快忽慢

参数嗅探是 SQL Server 常见面试和生产问题。

mermaid

flowchart TD
    A["第一次执行 @HospitalId=小医院"] --> B["估算返回 20 行"]
    B --> C["生成 Nested Loops + Seek 计划"]
    C --> D["计划缓存"]
    D --> E["第二次 @HospitalId=大医院"]
    E --> F["实际返回 200000 行"]
    F --> G["复用小医院计划导致很慢"]

为什么会这样：

SQL Server 为了性能会缓存执行计划。
编译计划时会参考第一次参数值的数据分布。
如果不同参数的数据量差异很大，同一个计划可能不适合所有参数。
统计信息或直方图只能帮助估算，不能保证所有参数都最佳。

处理方式：

方式	适用场景	代价
更新统计信息	统计信息过期	不能解决所有偏斜
`OPTION (RECOMPILE)`	单条特殊查询	每次编译有成本
拆 SQL	大客户和小客户不同路径	代码复杂度增加
Query Store 强制计划	已知稳定好计划	数据变化后需复核
优化索引	让常见参数都能接受	增加写成本

第九步：TempDB 为什么是关键基础设施

TempDB 承载很多内部工作：

用途	示例
临时表	`#TempAsset`
排序溢出	`Sort` 内存不够写 TempDB
Hash 溢出	`Hash Match` 内存不够
版本存储	RCSI / Snapshot
内部工作表	spool、游标、部分查询中间结果

TempDB 压力症状：

查询中出现 Sort/Hash spill。
打开 RCSI 后磁盘 IO 上升。
大量临时表创建删除导致争用。
TempDB 文件增长频繁。

优化方向：

优化 SQL，减少大排序和大 Hash。
给排序和 Join 条件设计合适索引。
控制长事务，减少版本存储保留时间。
合理配置 TempDB 数据文件和存储。

第十步：完整 Demo

建表：

sql

create table dbo.Asset (
  Id bigint identity(1,1) not null,
  AssetNo varchar(64) not null,
  HospitalId bigint not null,
  DepartmentId bigint null,
  AssetName nvarchar(128) not null,
  Status varchar(20) not null,
  CreatedAt datetime2 not null default sysdatetime(),
  UpdatedAt datetime2 not null default sysdatetime(),
  constraint PK_Asset primary key clustered (Id),
  constraint UX_Asset_AssetNo unique (AssetNo),
  constraint CK_Asset_Status
    check (Status in ('IDLE', 'USED', 'REPAIR', 'SCRAPPED'))
);

create index IX_Asset_Hospital_Status_CreatedAt
on dbo.Asset(HospitalId, Status, CreatedAt desc)
include (AssetNo, AssetName);

插入：

sql

insert into dbo.Asset(AssetNo, HospitalId, DepartmentId, AssetName, Status)
values
  ('A001', 1001, 10, N'CT-001', 'IDLE'),
  ('A002', 1001, 11, N'MRI-001', 'USED'),
  ('A003', 1002, 12, N'DR-001', 'REPAIR');

查询执行计划：

sql

set statistics io on;
set statistics time on;

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

状态更新：

sql

begin tran;

update dbo.Asset
set Status = 'USED',
    UpdatedAt = sysdatetime()
where AssetNo = 'A001'
  and Status = 'IDLE';

commit tran;

这个 Demo 对应的原理：

聚集主键决定数据行组织。
唯一约束防止资产编号重复。
非聚集索引支持列表页，include 减少 Key Lookup。
update 需要锁、日志、页修改和索引维护。
commit 依赖日志持久化。

线上排查总流程

mermaid

flowchart TD
    A["SQL Server 线上问题"] --> B{"表现是什么"}
    B -- "SQL 慢" --> C["看实际执行计划和 IO"]
    C --> D["Seek/Scan/Lookup/Sort/Hash"]
    B -- "接口卡住" --> E["查阻塞链和死锁"]
    E --> F["找 blocker、事务边界、锁升级"]
    B -- "忽快忽慢" --> G["查参数嗅探和计划缓存"]
    G --> H["对比估算行数和实际行数"]
    B -- "TempDB 高" --> I["查版本存储、Sort/Hash spill"]
    B -- "日志暴涨" --> J["查大事务、恢复模式、日志备份、AG 延迟"]

排查清单：

问题	先看什么
慢 SQL	实际执行计划、逻辑读、等待类型
Key Lookup 多	返回行数、include 字段、查询列
阻塞	blocker、事务是否未提交
死锁	deadlock graph、访问顺序、索引
参数嗅探	Query Store、计划缓存、估算/实际行数
TempDB 压力	spill、版本存储、临时表
日志暴涨	大事务、日志备份、AG/复制延迟

常见坑

坑	后果	正确做法
以为 Index Seek 一定快	范围大也会读很多页	看实际行数和逻辑读
忽略 Key Lookup	小数据没事，大数据很慢	高频查询用 include 或调整查询
非聚集索引 include 太多	写慢、占空间、缓存压力大	只覆盖高频核心查询
大事务批量更新	日志暴涨、锁升级、阻塞	分批、缩短事务
打开 RCSI 不看 TempDB	版本存储压垮 TempDB	监控版本存储和长事务
统计信息过期	计划选择错误	维护统计信息
忽略参数嗅探	同 SQL 忽快忽慢	Query Store、重编译、拆 SQL

面试标准回答

SQL Server 怎么从零学到生产可用

text

SQL Server 要按 T-SQL、表设计、页和文件、聚集/非聚集索引、执行计划、事务日志、锁和行版本、TempDB、参数嗅探和线上排查这条线学习。先理解数据存放在 MDF/NDF 数据文件中，事务日志在 LDF 中，数据按 8KB 页读写并缓存在 Buffer Pool。再理解聚集索引叶子层就是数据行，非聚集索引叶子层保存索引键和行定位器，查询缺列时可能 Key Lookup。SQL 执行时优化器根据统计信息选择计划，生产排查要看实际执行计划、逻辑读、估算和实际行数差异、等待类型、锁、TempDB 和日志。

为什么 SQL Server 事务日志不能删

text

SQL Server 事务日志不是普通文本日志，而是事务恢复机制核心。更新数据时会先记录日志，再修改 Buffer Pool 中的数据页；提交时日志保证事务可恢复。宕机恢复时 SQL Server 根据日志重做已提交事务并撤销未提交事务。Full 恢复模式下，日志还用于日志备份和时间点恢复，Always On、复制等高可用也依赖日志传递和重放。所以不能把 LDF 当成可随便删除的文件，日志暴涨要查恢复模式、日志备份、大事务和副本延迟。

RCSI 解决什么问题，有什么代价

text

RCSI 让 Read Committed 下的普通读使用行版本，减少读写阻塞。写事务更新数据时，旧版本会放到版本存储中，读事务可以按语句开始时的一致版本读取，不必等待写锁释放。代价是 TempDB 版本存储和 IO 压力增加，长事务会保留更多旧版本，写写冲突仍然需要锁，所以开启 RCSI 后仍要监控 TempDB、长事务和阻塞。

关联知识点

知识点	说明
SQL Server 总览	专栏入口和学习顺序
核心全过程原理	查询、索引、事务日志、锁全链路
索引、锁与行版本原理	Key Lookup、锁升级、RCSI
架构与存储	文件、页、区、Buffer Pool
索引与优化器	聚集/非聚集索引、执行计划
事务与锁	锁、隔离级别、死锁
性能排查	等待、TempDB、参数嗅探
SQL Server 面试	标准回答和追问

本章小结

SQL Server 从零到生产级掌握，重点是把“数据怎么存、SQL 怎么执行、索引怎么定位、事务怎么恢复、锁怎么并发、问题怎么排查”串起来。聚集索引、Key Lookup、事务日志、RCSI、TempDB、参数嗅探都不是孤立概念，它们都出现在真实 SQL 执行链路里。理解过程之后，你才能说清为什么这样设计，不这样会怎样。

JDK 8/11/17/21 LTS演进

线程池

SQL Server 从零到生产级掌握 ​

学习目标 ​

学习路线 ​

第一步：数据库、文件、页和区 ​

第二步：创建表和基础索引 ​

第三步：聚集索引、非聚集索引和堆表 ​

聚集索引 ​

非聚集索引和 Key Lookup ​

堆表和 RID Lookup ​

第四步：一条 SELECT 怎么执行 ​

第五步：UPDATE 和事务日志全过程 ​

第六步：Read Committed、锁和阻塞 ​

第七步：RCSI 和行版本 ​

第八步：参数嗅探为什么忽快忽慢 ​

第九步：TempDB 为什么是关键基础设施 ​

第十步：完整 Demo ​

线上排查总流程 ​

常见坑 ​

面试标准回答 ​

SQL Server 怎么从零学到生产可用 ​

为什么 SQL Server 事务日志不能删 ​

RCSI 解决什么问题，有什么代价 ​

关联知识点 ​

本章小结 ​

SQL Server 从零到生产级掌握

学习目标

学习路线

第一步：数据库、文件、页和区

第二步：创建表和基础索引

第三步：聚集索引、非聚集索引和堆表

聚集索引

非聚集索引和 Key Lookup

堆表和 RID Lookup

第四步：一条 SELECT 怎么执行

第五步：UPDATE 和事务日志全过程

第六步：Read Committed、锁和阻塞

第七步：RCSI 和行版本

第八步：参数嗅探为什么忽快忽慢

第九步：TempDB 为什么是关键基础设施

第十步：完整 Demo

线上排查总流程

常见坑

面试标准回答

SQL Server 怎么从零学到生产可用

为什么 SQL Server 事务日志不能删

RCSI 解决什么问题，有什么代价

关联知识点

本章小结