Appearance

SQL Server 商业场景训练营 ​

SQL Server 学习不能只停在 T-SQL 语法。商业项目里要理解：聚集索引为什么影响数据组织，非聚集索引为什么会 Key Lookup，事务日志为什么能恢复，默认读提交为什么可能阻塞，RCSI 为什么会用 TempDB 行版本，参数嗅探为什么让同一条 SQL 忽快忽慢。

训练目标：通过订单、资产、报表、阻塞、行版本和参数嗅探，把 SQL Server 的索引、日志、锁、TempDB、执行计划和排查方法练成闭环。

训练总流程 ​

mermaid
flowchart TD
    A["建表和约束"] --> B["选择聚集索引"]
    B --> C["设计非聚集索引"]
    C --> D["查看实际执行计划"]
    D --> E["观察 Seek / Scan / Lookup"]
    E --> F["模拟阻塞和死锁"]
    F --> G["理解日志和行版本"]
    G --> H["排查参数嗅探和 TempDB"]

训练一：订单表和聚集索引 ​

建表 Demo ​

sql
create table dbo.PaymentOrder (
  Id bigint identity(1,1) not null,
  OrderNo varchar(64) not null,
  UserId bigint not null,
  Status int not null,
  Amount decimal(12,2) not null,
  CreatedAt datetime2 not null default sysdatetime(),
  PaidAt datetime2 null,
  constraint PK_PaymentOrder primary key clustered (Id),
  constraint UX_PaymentOrder_OrderNo unique (OrderNo)
);

create index IX_PaymentOrder_User_Status_Time
on dbo.PaymentOrder(UserId, Status, CreatedAt desc)
include (OrderNo, Amount);

为什么主键常做 clustered ​

SQL Server 聚集索引的叶子层就是数据行。表按聚集键的逻辑顺序组织。一个表只能有一个聚集索引，因为数据行只能按一种方式组织。

mermaid
flowchart TD
    A["聚集索引 root"] --> B["中间层"]
    B --> C["叶子层"]
    C --> D["数据行本身"]

非聚集索引叶子层不是完整数据行，而是索引键、包含列和行定位器。如果聚集表上通过非聚集索引找不到所需列，就要 Key Lookup 回聚集索引取整行或缺失列。

训练二：覆盖索引和 Key Lookup ​

查询用户已支付订单：

sql
select top (20) OrderNo, Amount, CreatedAt
from dbo.PaymentOrder
where UserId = 1001
  and Status = 1
order by CreatedAt desc;

这个查询可以被 IX_PaymentOrder_User_Status_Time 较好支持，因为：

  1. UserId, Status, CreatedAt 匹配过滤和排序。
  2. include (OrderNo, Amount) 让返回字段在非聚集索引里。
  3. 避免为了取 OrderNo, Amount 大量 Key Lookup。

如果没有 include 会怎样 ​

sql
drop index IX_PaymentOrder_User_Status_Time on dbo.PaymentOrder;

create index IX_PaymentOrder_User_Status_Time
on dbo.PaymentOrder(UserId, Status, CreatedAt desc);

执行计划可能出现 Key Lookup。

mermaid
flowchart TD
    A["Index Seek<br/>非聚集索引定位"] --> B["拿到聚集键 Id"]
    B --> C["Key Lookup<br/>回聚集索引"]
    C --> D["取缺失列"]
    D --> E["返回结果"]

Key Lookup 少量没问题，大量 Lookup 会变成很多随机访问。优化方向是 include 常用返回列，或调整查询字段，或重新设计索引。

训练三：用 output inserted 做状态流转 ​

支付回调把订单从未支付改成已支付，并返回更新后的记录：

sql
update dbo.PaymentOrder
set Status = 1,
    PaidAt = sysdatetime()
output inserted.Id, inserted.OrderNo, inserted.Status, inserted.PaidAt
where OrderNo = 'P202607060001'
  and Status = 0;

原理 ​

output inserted 可以返回更新后的新值，类似 PostgreSQL 的 returning。状态条件 Status = 0 放在 SQL 里，可以避免重复回调重复处理。业务根据影响行数和返回结果判断是否第一次处理成功。

不这样会怎样 ​

先查再改，在并发回调下可能两个线程都认为订单未支付。正确做法是让数据库在一次 update 中完成条件判断、加锁、更新和返回。

训练四：阻塞和锁等待 ​

会话 A ​

sql
begin transaction;

update dbo.PaymentOrder
set Status = 2
where OrderNo = 'P202607060001';

-- 不提交

会话 B ​

sql
update dbo.PaymentOrder
set Status = 1
where OrderNo = 'P202607060001';

会话 B 会被阻塞。

排查阻塞 ​

sql
select
  r.session_id,
  r.blocking_session_id,
  r.wait_type,
  r.wait_time,
  r.status,
  t.text
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle) t
where r.blocking_session_id <> 0;

原理 ​

SQL Server 默认 Read Committed 下，写操作持有排他锁，其他写会等待。排查时要找到阻塞会话、等待类型、事务持续时间和 SQL。

训练五：RCSI 和 TempDB 行版本 ​

默认 Read Committed 可能发生读写阻塞。开启 RCSI 后，读提交隔离级别下的读可以使用行版本，减少读写阻塞。

sql
alter database YourDbName
set read_committed_snapshot on
with rollback immediate;

原理图 ​

mermaid
flowchart TD
    A["更新数据行"] --> B["旧版本写入版本存储"]
    B --> C["版本存储位于 TempDB"]
    C --> D["读请求按语句开始时间读版本"]
    D --> E["减少读写阻塞"]

代价 ​

收益代价
普通读减少阻塞TempDB 压力增加
查询体验更稳定长事务可能保留更多版本
适合读多写多系统需要监控版本存储和 TempDB IO

RCSI 不是万能开关。写写冲突仍然要等待，长事务和大更新仍可能造成压力。

训练六：事务日志和恢复 ​

SQL Server 的事务日志保存在 .ldf 文件里。事务提交、回滚、崩溃恢复、日志备份、Always On 都依赖它。

mermaid
flowchart TD
    A["事务修改数据页"] --> B["生成日志记录"]
    B --> C["日志写入 LDF"]
    C --> D["commit 返回"]
    D --> E["数据页之后刷盘"]
    E --> F["宕机后日志恢复"]

日志文件暴涨常见原因 ​

原因解释
Full 恢复模式但不做日志备份日志无法截断
大事务一次产生大量日志
长事务未提交活跃日志不能复用
Always On 或复制延迟日志需要保留等待同步

排查：

sql
select name, recovery_model_desc, log_reuse_wait_desc
from sys.databases;

训练七：参数嗅探 ​

同一条存储过程或参数化 SQL，有时快有时慢，可能是参数嗅探。

原理 ​

SQL Server 编译计划时会参考第一次执行传入的参数。如果第一次参数选择性很高，优化器可能选择 Index Seek + Lookup；后续传入选择性很低的参数时，仍复用旧计划，就可能变慢。

mermaid
flowchart TD
    A["第一次执行参数很少命中"] --> B["编译 Seek 计划"]
    B --> C["计划缓存"]
    C --> D["第二次参数命中大量行"]
    D --> E["复用 Seek + Lookup"]
    E --> F["大量 Lookup 导致慢"]

处理方向 ​

方法适用情况
更新统计信息统计信息过旧
重新编译参数差异极大但可接受编译成本
优化索引访问路径不合理
拆 SQL不同参数场景差异很大
查询提示谨慎使用，避免固化错误计划

最终验收清单 ​

做完这页后，你要能回答：

  1. 聚集索引和非聚集索引的叶子层分别是什么？
  2. Key Lookup 为什么会慢？
  3. include 列解决什么问题？
  4. output inserted 适合什么场景？
  5. SQL Server 阻塞怎么查？
  6. RCSI 为什么减少读写阻塞？
  7. RCSI 为什么会增加 TempDB 压力？
  8. 事务日志为什么能支持恢复？
  9. 日志文件为什么会暴涨？
  10. 参数嗅探为什么导致同一条 SQL 忽快忽慢？

关联知识点 ​

知识点入口
SQL Server 主线从零到生产级掌握
核心流程核心全过程原理
索引、锁和行版本索引、锁与行版本原理
事务与锁事务与锁
性能排查性能排查
面试SQL Server 面试