SQL Server 索引、锁与行版本原理

SQL Server 学不懂，通常卡在三个地方：

1. 聚集索引和非聚集索引到底怎么找数据。
2. Key Lookup 为什么会慢。
3. 锁、锁升级、行版本到底怎样影响并发。

这一页把这些过程串起来。

聚集索引到底是什么

聚集索引不是“给主键建了个普通目录”这么简单。SQL Server 中，聚集索引的叶子层就是数据行本身。

flowchart TD
    A["Clustered Index Root"] --> B["Intermediate Page"]
    B --> C["Leaf Page"]
    C --> D["Data Row: Id / AssetNo / OwnerId / CreatedAt"]

这意味着：

1. 表数据按聚集键组织。
2. 一个表只能有一个聚集索引。
3. 聚集键会出现在非聚集索引的行定位信息里。
4. 聚集键过大，会让非聚集索引也变大。

Heap 表是什么

如果表没有聚集索引，它就是 heap。heap 表中的数据没有按聚集键组织。

非聚集索引定位 heap 表数据时，叶子层保存 RID。

flowchart TD
    A["Nonclustered Index"] --> B["Leaf: key + RID"]
    B --> C["RID 定位 Heap 中的数据行"]

商业系统多数表更推荐有合适聚集索引，因为 heap 在更新、转发记录、查找等场景可能带来额外复杂度。

非聚集索引怎么找数据

假设表：

create table dbo.Asset (
  Id bigint identity(1,1) not null,
  AssetNo varchar(64) not null,
  OwnerId bigint not null,
  CreatedAt datetime2 not null,
  constraint PK_Asset primary key clustered (Id)
);

create index IX_Asset_AssetNo on dbo.Asset(AssetNo);

查询：

select Id, AssetNo, OwnerId, CreatedAt
from dbo.Asset
where AssetNo = 'A001';

执行过程：

flowchart TD
    A["根据 AssetNo 查非聚集索引"] --> B["找到 Leaf"]
    B --> C["Leaf 中有 AssetNo 和聚集键 Id"]
    C --> D["用 Id 回到聚集索引"]
    D --> E["读取完整数据行"]

这个“回到聚集索引取完整行”的动作，就是 Key Lookup。

Key Lookup 为什么会慢

Key Lookup 少量发生没问题。真正危险的是大量发生。

flowchart TD
    A["非聚集索引找到 100000 行"] --> B["每行做一次 Key Lookup"]
    B --> C["访问聚集索引 100000 次"]
    C --> D["大量随机 IO / CPU / Buffer 消耗"]

例如：

select Id, AssetNo, OwnerId, CreatedAt
from dbo.Asset
where AssetNo like 'A%';

如果匹配很多行，每行都要 Key Lookup，就可能比直接扫描聚集索引还慢。

优化方式：

create index IX_Asset_AssetNo_Cover
on dbo.Asset(AssetNo)
include (OwnerId, CreatedAt);

include 字段不参与索引排序，但会存放在非聚集索引叶子层，用于覆盖查询。

覆盖索引不是越宽越好

覆盖索引能减少 Key Lookup，但不是字段越多越好。

过宽索引的问题	原因
写入变慢	insert/update/delete 要维护更多索引数据
占用空间大	include 字段也占索引页
缓存效率下降	Buffer Pool 能缓存的有效页减少
统计和维护成本增加	重建、更新统计更重

设计索引要围绕高频 SQL，而不是把所有字段都 include。

统计信息和参数嗅探

SQL Server 优化器会根据统计信息估算行数。

flowchart TD
    A["SQL + 参数"] --> B["读取统计信息"]
    B --> C["估算返回行数"]
    C --> D["选择 Index Seek / Scan / Join 方式"]
    D --> E["缓存执行计划"]

参数嗅探是指：第一次编译计划时，优化器根据当时的参数选择了计划，后续不同参数复用这个计划，可能不合适。

示例：

create procedure dbo.GetOrdersByStatus
  @Status tinyint
as
begin
  select *
  from dbo.Orders
  where Status = @Status;
end

如果 Status = 1 返回 90% 数据，适合扫描；Status = 9 返回 0.1% 数据，适合索引 Seek。同一个缓存计划不一定同时适合两种参数。

锁的层级

SQL Server 锁有层级：

flowchart TD
    A["Database"] --> B["Table"]
    B --> C["Page"]
    C --> D["Row / Key"]

意向锁用于告诉上层对象：下层有锁。

例如某事务锁住一行，表上可能有意向锁，避免另一个事务直接拿表级排他锁。

读写为什么会阻塞

默认 Read Committed 下，读通常需要共享锁，写需要排他锁。

sequenceDiagram
    participant W as 写事务
    participant R as 读事务
    W->>W: update 行，持有 X 锁
    R->>W: select 同一行，请求 S 锁
    W-->>R: 未提交前可能等待
    W->>W: commit
    R->>R: 读取完成

这和 PostgreSQL 默认 MVCC 读旧版本的体验不同。SQL Server 可以通过行版本相关隔离来减少读写阻塞。

Read Committed Snapshot

开启 Read Committed Snapshot 后，Read Committed 下的读可以使用版本存储，而不是等待写锁释放。

flowchart TD
    A["写事务 update"] --> B["旧版本进入版本存储"]
    C["读事务 select"] --> D["读取语句开始时可见版本"]
    B --> D
    A --> E["新版本未提交时读不阻塞"]

代价是 tempdb 压力会增加，因为版本存储通常依赖 tempdb。

锁升级为什么发生

SQL Server 管理大量细粒度锁也有成本。如果一个事务持有大量行锁，可能升级为表锁。

flowchart TD
    A["事务扫描并更新大量行"] --> B["产生大量行锁"]
    B --> C["锁管理内存和 CPU 成本升高"]
    C --> D["触发锁升级"]
    D --> E["表锁"]
    E --> F["其他事务更容易被阻塞"]

锁升级不是“数据库坏了”，而是 SQL Server 在锁管理成本和并发范围之间做取舍。

减少锁升级影响：

1. 大批量更新分批处理。
2. 让条件命中索引，减少扫描行数。
3. 缩短事务。
4. 避免在事务里调用外部接口。

一次 update 的完整过程

flowchart TD
    A["执行 update"] --> B["优化器选择访问路径"]
    B --> C["通过索引或扫描找到目标行"]
    C --> D["申请更新所需锁"]
    D --> E["写事务日志"]
    E --> F["修改 Buffer Pool 中的数据页"]
    F --> G["事务提交"]
    G --> H["日志保证持久性"]
    H --> I["脏页稍后刷盘"]

每一步的意义：

步骤	说明
选择访问路径	决定 Seek、Scan、Join 等
找目标行	索引好坏影响扫描范围
申请锁	保证并发修改正确
写日志	支持回滚和崩溃恢复
修改页	数据页先在内存中改变
提交	日志持久后返回成功
刷脏页	后台异步写回磁盘

商业场景：资产按编号查询

高频接口：

select Id, AssetNo, OwnerId, CreatedAt
from dbo.Asset
where AssetNo = @AssetNo;

如果只有：

create index IX_Asset_AssetNo on dbo.Asset(AssetNo);

过程是：

1. Seek 非聚集索引。
2. 拿到聚集键 Id。
3. Key Lookup 到聚集索引。
4. 取 OwnerId、CreatedAt。

如果接口 QPS 高，可以考虑：

create index IX_Asset_AssetNo_Cover
on dbo.Asset(AssetNo)
include (OwnerId, CreatedAt);

但如果 OwnerId、CreatedAt 经常更新，就要权衡写入成本。

常见坑

坑	后果	正确理解
以为 Index Seek 一定快	Seek 后大量 Key Lookup 仍可能慢	要看总成本和实际行数
include 很多字段	写入和缓存成本上升	只覆盖高频关键 SQL
忽略参数嗅探	同一 SQL 时快时慢	对比不同参数实际计划
长事务批量更新	锁升级和阻塞	分批提交，缩短事务
不关注 tempdb	行版本、排序、临时表变慢	监控 tempdb 和版本存储

面试标准回答

SQL Server 聚集索引的叶子层就是数据行，一个表只能有一个聚集索引。非聚集索引叶子层保存索引键和行定位器，如果表有聚集索引，通常保存聚集键，再通过聚集键回到聚集索引取完整行，这就是 Key Lookup。少量 Key Lookup 没问题，大量 Key Lookup 会带来随机访问和 Buffer 消耗，可以通过 INCLUDE 覆盖高频查询字段，但索引过宽会增加写入和缓存成本。SQL Server 默认读写可能通过共享锁和排他锁互相阻塞，开启 Read Committed Snapshot 后读可以使用行版本减少阻塞，但会增加 tempdb 压力。锁升级是大量细粒度锁带来管理成本时升级为表锁，能降低锁管理成本但会扩大阻塞范围。