SQL Server 索引与优化器

SQL Server 的索引学习要抓住一条主线：

数据按 8KB 页读写，聚集索引的叶子层就是数据行，非聚集索引的叶子层保存索引键和行定位器，优化器根据统计信息估算成本并选择执行计划。

学习目标

学完这一页，你要能说明：

聚集索引、非聚集索引、堆表分别是什么。
Key Lookup、RID Lookup 为什么出现，什么时候会很慢。
Index Seek 为什么不一定快，Index Scan 为什么不一定错。
INCLUDE 覆盖索引解决什么问题，又有什么代价。
统计信息、基数估算、参数嗅探如何影响执行计划。
商业系统如何为列表页、详情页、报表和批处理设计索引。

SQL Server 为什么特别强调聚集索引

SQL Server 的聚集索引不是“另一个普通索引”。它决定表数据行的逻辑组织方式。

mermaid

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

一个表只能有一个聚集索引，因为数据行只能按一种聚集结构组织。

聚集键选择很重要：

聚集键特征	影响
窄	非聚集索引也要保存聚集键，窄键更省空间
稳定	聚集键更新会移动定位信息，成本高
递增	插入更集中，页分裂少
高唯一性	定位清晰，避免额外 uniquifier

常见选择是递增 bigint identity 主键。

非聚集索引

非聚集索引的叶子层不是完整数据行，而是索引键和行定位器。

mermaid

flowchart TD
    A["Nonclustered Index Root"] --> B["Leaf Page"]
    B --> C["索引键"]
    B --> D["行定位器"]
    D --> E{"表是否有聚集索引"}
    E -- "有" --> F["定位到聚集索引键"]
    E -- "没有，堆表" --> G["定位到 RID"]

如果表有聚集索引，非聚集索引通常用聚集键定位数据行；如果表是堆表，则用 RID 定位。

Key Lookup

查询：

sql

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

如果非聚集索引只有：

sql

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

但查询还需要 AssetNo、AssetName，执行计划可能：

mermaid

flowchart TD
    A["Index Seek<br/>非聚集索引"] --> B["得到聚集键 Id"]
    B --> C["Key Lookup"]
    C --> D["回到聚集索引叶子层取完整列"]
    D --> E["返回结果"]

少量 Key Lookup 没问题。如果返回 10 行，回 10 次可以接受；如果返回 10 万行，就变成大量随机访问。

INCLUDE 覆盖索引

可以把查询需要但不参与过滤排序的列放进 include：

sql

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

这样叶子层已经有查询需要的列，可以减少 Key Lookup。

mermaid

flowchart TD
    A["Index Seek"] --> B["叶子层包含过滤列"]
    B --> C["叶子层包含 include 列"]
    C --> D["无需 Key Lookup 或显著减少"]

但覆盖索引不是越宽越好：

代价	说明
写入变慢	insert/update/delete 要维护更宽索引
占空间	更多页，更多磁盘和备份成本
缓存压力	Buffer Pool 被索引页占用
维护复杂	查询变化后索引可能不再合适

堆表和 RID Lookup

没有聚集索引的表叫堆表。非聚集索引命中后通过 RID 回到堆表行。

mermaid

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

堆表不是绝对不能用，但 OLTP 主业务表一般更推荐有聚集索引。堆表在更新变长列时可能产生转发行，RID Lookup 和碎片会让排查复杂。

Index Seek 不一定快

很多人看到 Index Seek 就认为 SQL 已经最优，这是误区。

mermaid

flowchart TD
    A["Index Seek"] --> B{"Seek 后返回多少行"}
    B -- "几十行" --> C["通常较好"]
    B -- "几十万行" --> D["仍然可能很慢"]
    D --> E["大量 Key Lookup、排序或逻辑读"]

判断是否快，要看：

实际行数。
逻辑读。
Key Lookup 次数。
是否 Sort/Hash 溢出。
估算行数和实际行数是否差距巨大。

执行计划常见算子

算子	含义	排查重点
Index Seek	按索引范围定位	返回行数是否过大
Index Scan	扫描索引页	是否缺少过滤列索引
Table Scan	扫表	大表高频 SQL 风险
Key Lookup	非聚集索引回聚集索引取列	次数多会慢
RID Lookup	非聚集索引回堆表取列	堆表场景
Nested Loops	外层驱动内层	外层实际行数大时慢
Hash Match	哈希 Join/聚合	内存不足会 spill 到 TempDB
Sort	排序	可否通过索引顺序避免

打开统计：

sql

set statistics io on;
set statistics time on;

重点看 logical reads。耗时会受缓存、并发、锁影响；逻辑读更能说明访问路径是否读了太多页。

优化器和统计信息

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

mermaid

flowchart TD
    A["SQL 文本"] --> B["解析和绑定"]
    B --> C["读取统计信息"]
    C --> D["估算选择率和行数"]
    D --> E["枚举访问路径和 Join 顺序"]
    E --> F["计算成本"]
    F --> G["选择执行计划"]
    G --> H["缓存计划"]

统计信息包含列值分布的直方图等信息。统计信息过期会导致估算错误。

sql

update statistics dbo.Asset;

或：

sql

exec sp_updatestats;

估算错误的后果：

估算错误	可能结果
低估返回行数	选择 Nested Loops + Key Lookup，实际大量回表
高估返回行数	放弃合适索引，选择扫描或 Hash
Join 基数错误	Join 顺序错误
内存估算错误	Sort/Hash spill 到 TempDB

参数嗅探

参数嗅探是 SQL Server 最常见的“同一条 SQL 忽快忽慢”原因之一。

mermaid

flowchart TD
    A["第一次执行 @HospitalId=小医院"] --> B["编译计划"]
    B --> C["估算返回很少"]
    C --> D["选择 Seek + Nested Loops"]
    D --> E["计划缓存"]
    E --> F["第二次 @HospitalId=大医院"]
    F --> G["实际返回很多"]
    G --> H["复用小结果计划导致慢"]

处理方式：

方式	适用场景	代价
更新统计信息	统计信息过期	不能解决严重倾斜
`OPTION (RECOMPILE)`	少数特殊查询	每次编译成本
拆 SQL	大客户/小客户路径不同	代码复杂
Query Store 强制计划	已知稳定好计划	数据变化后需复核
优化索引	提供更稳的访问路径	写入维护成本

商业项目索引设计

医疗资产平台示例：

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)
);

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

场景	查询特点	索引建议
资产详情	`AssetNo = ?`	唯一索引
医院资产列表	`HospitalId + Status + CreatedAt`	联合索引 + include
待办任务	`AssigneeId + Status + CreatedAt`	联合索引
报表统计	大范围 group by	离线汇总、列存索引或数仓
批量更新	按条件定位大量行	条件列索引 + 分批
多租户查询	每条 SQL 带 TenantId	TenantId 放索引前部

可运行 Demo

sql

drop table if exists dbo.Asset;

create table dbo.Asset (
  Id bigint identity(1,1) not null,
  AssetNo varchar(64) not null,
  HospitalId bigint not null,
  AssetName nvarchar(128) not null,
  Status varchar(20) not null,
  CreatedAt datetime2 not null default sysdatetime(),
  constraint PK_Asset primary key clustered (Id),
  constraint UX_Asset_AssetNo unique (AssetNo)
);

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

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

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;

观察：

实际执行计划是否走 IX_Asset_Hospital_Status_CreatedAt。
是否出现 Key Lookup。
logical reads 是否较低。
返回行数变化后计划是否仍然稳定。

常见坑

坑	后果	正确做法
聚集键使用随机 GUID	页分裂和碎片明显	使用顺序键或评估 sequential guid
看到 Seek 就认为快	返回行多仍慢	看实际行数和逻辑读
忽略 Key Lookup	小数据没事，大数据慢	高频查询用 include 覆盖
include 太多	写慢、索引大	只覆盖核心查询
统计信息过期	计划不准	维护统计信息
参数嗅探忽快忽慢	某些参数复用坏计划	Query Store、重编译、拆 SQL
隐式转换	索引失效或估算差	字段和参数类型一致

面试标准回答

text

SQL Server 索引主要分聚集索引和非聚集索引。聚集索引的叶子层就是数据行，一个表只能有一个；非聚集索引的叶子层保存索引键和行定位器，如果查询列不在非聚集索引里，就可能发生 Key Lookup 回到聚集索引取完整列。优化器根据统计信息估算行数和成本选择计划，所以统计信息过期、数据倾斜和参数嗅探都可能导致计划不稳定。排查时要看实际执行计划、Estimated Rows 和 Actual Rows、logical reads、Key Lookup、Sort/Hash spill 以及等待类型。

追问：

聚集索引叶子层是什么？
Key Lookup 为什么会慢？
INCLUDE 字段有什么代价？
参数嗅探为什么导致同一 SQL 忽快忽慢？

关联知识点

知识点	说明
SQL Server 从零到生产级掌握	主学习线
索引、锁与行版本原理	Key Lookup、锁升级、RCSI 深入
事务与锁	锁、阻塞、隔离级别
性能排查	等待、TempDB、参数嗅探

JDK 8/11/17/21 LTS演进

线程池

SQL Server 索引与优化器 ​

学习目标 ​

SQL Server 为什么特别强调聚集索引 ​

非聚集索引 ​

Key Lookup ​

INCLUDE 覆盖索引 ​

堆表和 RID Lookup ​

Index Seek 不一定快 ​

执行计划常见算子 ​

优化器和统计信息 ​

参数嗅探 ​

商业项目索引设计 ​

可运行 Demo ​

常见坑 ​

面试标准回答 ​

关联知识点 ​

SQL Server 索引与优化器

学习目标

SQL Server 为什么特别强调聚集索引

非聚集索引

Key Lookup

INCLUDE 覆盖索引

堆表和 RID Lookup

Index Seek 不一定快

执行计划常见算子

优化器和统计信息

参数嗅探

商业项目索引设计

可运行 Demo

常见坑

面试标准回答

关联知识点