05.4.1.2 聚集索引与非聚集索引
所属:全栈开发面试题库 / 05 数据库与存储 / 05.4 SQL Server / 05.4.1 架构与存储引擎
覆盖范围
- 聚集索引与非聚集索引的 B 树结构、叶子节点内容
- 键查找(Key Lookup)、书签查找与覆盖索引
- INCLUDE 包含列、联合索引、筛选索引、唯一索引
- 索引重建/重组、碎片与填充因子
- 缺失索引排查、冗余索引、堆表适用场景
- 与 MySQL InnoDB、PostgreSQL 索引体系对照
高频题速览
| 编号 | 题目 | 难度 | 频率 |
|---|---|---|---|
| 05.4.1.2-001 | 聚集索引的物理组织 | 进阶 | ★★★★★ |
| 05.4.1.2-002 | 非聚集索引叶子存什么 | 进阶 | ★★★★★ |
| 05.4.1.2-005 | 键查找(Key Lookup)与回表 | 进阶 | ★★★★★ |
| 05.4.1.2-006 | 包含列 INCLUDE 与覆盖索引 | 进阶 | ★★★★★ |
| 05.4.1.2-007 | 联合索引与键列顺序 | 进阶 | ★★★★☆ |
| 05.4.1.2-008 | 筛选索引 | 高级 | ★★★★☆ |
| 05.4.1.2-015 | 缺失索引排查 | 高级 | ★★★★☆ |
| 05.4.1.2-018 | 与 MySQL / PG 索引对照 | 高级 | ★★★★☆ |
两类索引结构对照
聚集索引(Clustered Index)
非叶子:键 + 子页指针
叶子 :完整数据行(数据按聚集键有序存放)
非聚集索引(Nonclustered Index)
非叶子:索引键 + 子页指针
叶子 :索引键 + 聚集键值(表有聚集索引)
或 RID file:page:slot(堆表)
未覆盖查询 → 键查找(Key Lookup)回到聚集索引/堆取整行
05.4.1.2-001 聚集索引是如何物理组织数据的?
频率 ★★★★★ | 难度 进阶
一句话结论
聚集索引的 B 树叶子节点就是数据行本身,表的数据按聚集键有序物理组织;一张表只能有一个聚集索引。
概念解释
- 聚集键决定数据行的物理存储顺序:键值相近的行落在相近叶子页。
- 建表未显式建聚集索引时,表是堆;主键默认通常被建为聚集索引。
- 聚集索引除了加速按键查询,也让范围扫描与按键排序更高效。
原理流程
- 插入新行按聚集键落到对应叶子页;键递增时追加到右侧,键随机时可能触发页分裂。
- 非聚集索引叶子保存聚集键值作为回表定位(见 05.4.1.2-002)。
代码 / 命令
-- 创建聚集主键
ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (Id);
-- 查看索引类型
SELECT i.name, i.type_desc, i.is_primary_key
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Orders');
常见误区
- 误区:聚集索引就是"主键索引"的别名。正解:主键可以建为非聚集;聚集索引可以是非唯一键(引擎补充唯一化值)。
- 误区:一张表可以按不同排序各建一个聚集索引。正解:数据只能按一种顺序物理存放,聚集索引唯一。
追问
- 问:聚集键为什么建议窄、唯一、静态、递增?答:窄则所有非聚集索引叶子都更省空间;唯一避免额外唯一化列;静态避免键更新引发移动;递增减少页分裂(见 05.4.1.2-004)。
- 问:聚集索引叶子存的是整行吗?答:是,行内所有列;离页 LOB 除外。
05.4.1.2-002 非聚集索引的叶子节点存什么?
频率 ★★★★★ | 难度 进阶
一句话结论
表有聚集索引时,非聚集索引叶子存索引键 + 聚集键值;堆表叶子存 RID(文件:页:槽位);需要整行时再回表取数。
概念解释
- 有聚集索引:非聚集叶子用聚集键定位聚集索引,回表即按聚集键查找数据行。
- 堆表:非聚集叶子存 RID,直接按 RID 定位堆中数据行。
- 若查询所需列都在非聚集索引中,即为覆盖索引,无需回表。
原理流程
- 非聚集 B 树按键有序;叶子到数据的路径:聚集键 → 聚集索引叶子,或 RID → 堆页槽位。
- 修改聚集键值时,所有引用该键的非聚集索引都要同步更新。
代码 / 命令
-- 非聚集索引:键列 Status,包含列 Amount(见 05.4.1.2-006)
CREATE NONCLUSTERED INDEX IX_Orders_Status
ON dbo.Orders (Status)
INCLUDE (Amount, CreateTime);
常见误区
- 误区:非聚集索引叶子存整行数据。正解:只存键(+ 包含列)与定位值;整行在聚集索引/堆中。
- 误区:把聚集键放进每个非聚集索引键列才方便回表。正解:引擎自动携带聚集键,无需重复声明。
追问
- 问:为什么更换聚集键代价大?答:所有非聚集索引叶子都存旧聚集键,重建聚集键会导致全部非聚集索引重建。
- 问:堆表 RID 会不会变?答:堆行移动(如页拆分/更新迁移)后 RID 可能变化,堆表非聚集索引会做转发记录处理。
05.4.1.2-003 一张表能建几个聚集索引?非聚集索引有数量限制吗?
频率 ★★★☆☆ | 难度 基础
一句话结论
聚集索引每表一个;非聚集索引可建多个,但数量受写入成本、索引体积与引擎上限约束,不是越多越好。
概念解释
- 聚集索引唯一:数据只有一种物理顺序。
- 非聚集索引数量实际受限于:每个索引都要在写入时维护、占空间、占用 Buffer Pool。
原理流程
- 每次
INSERT/UPDATE/DELETE都要同步维护相关索引;索引越多写入放大越明显。
代码 / 命令
-- 列出某表全部索引
SELECT i.index_id, i.name, i.type_desc, i.is_unique, i.has_filter, i.filter_definition
FROM sys.indexes i
WHERE i.object_id = OBJECT_ID('dbo.Orders')
ORDER BY i.index_id;
常见误区
- 误区:为每个查询条件各建一个单列索引。正解:应优先设计能覆盖多条件的联合索引,控制索引总数。
- 误区:索引上限到了才需要考虑数量。正解:真正的约束是写入性能与维护成本,而非硬上限。
追问
- 问:非聚集索引过多对查询有坏处吗?答:优化器可选方案变多、成本估算开销上升,也可能选错计划。
- 问:主键默认建成什么索引?答:默认聚集主键(可在建表时显式指定 NONCLUSTERED)。
05.4.1.2-004 聚集键应该怎么选?
频率 ★★★★☆ | 难度 高级
一句话结论
理想聚集键满足:窄(列短)、唯一、静态(不更新)、递增(顺序写);自增整数或 Snowflake 类有序键优于随机 GUID。
概念解释
| 原则 | 原因 |
|---|---|
| 窄 | 所有非聚集索引叶子都携带聚集键,键越短二级索引越小 |
| 唯一 | 避免引擎为非唯一聚集键追加 4 字节唯一化值 |
| 静态 | 键值不更新,否则行移动并波及全部非聚集索引 |
| 递增 | 新行追加右侧页,减少页分裂与碎片 |
原理流程
- 随机聚集键(GUID v4)插入 B 树中间位置,频繁页分裂、碎片高、缓存局部性差。
- 有序键(自增
BIGINT、GUID v7/ULID)顺序追加,写入与碎片表现更好。
代码 / 命令
-- 反例:随机 GUID 作为聚集主键
CREATE TABLE dbo.LogBad (
Id UNIQUEIDENTIFIER PRIMARY KEY CLUSTERED DEFAULT NEWID(),
Message NVARCHAR(MAX)
);
-- 正例:自增有序键
CREATE TABLE dbo.LogGood (
Id BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Message NVARCHAR(MAX)
);
常见误区
- 误区:用宽列(如手机号、长字符串)做聚集键无所谓。正解:宽键会被复制到每个非聚集索引,二级索引体积显著膨胀。
- 误区:业务字段天然唯一就适合做聚集键。正解:业务字段可能更新或变长,稳定性比唯一性更重要。
追问
- 问:自增键被遍历有安全风险吗?答:可被枚举,敏感业务应在权限层校验或对外使用不透明 ID。
- 问:GUID v7 相比 v4 好在哪?答:带时间前缀趋于有序,插入位置更接近追加,分裂减少。
05.4.1.2-005 什么是键查找(Key Lookup)?什么时候出现、怎么消除?
频率 ★★★★★ | 难度 进阶
一句话结论
非聚集索引未覆盖查询所需列时,引擎按叶子中的聚集键(或 RID)再回聚集索引/堆取整行,该回表算子即键查找;可通过覆盖索引消除。
概念解释
- 键查找(Key Lookup):回聚集索引按聚集键取行。
- RID 查找(RID Lookup):堆表上按 RID 取行。
- 执行计划中键查找通常配合嵌套循环,外层每命中一行就回表一次。
原理流程
- 非聚集索引扫描/Seek 得到键值与定位信息。
- 所需列不在索引中 → 对每行执行键查找。
- 命中行数多时回表成本累加,优化器可能直接选择聚集索引扫描。
代码 / 命令
-- 只有 Status 索引,查询需要 Amount → 计划中出现 Key Lookup
SELECT Id, Status, Amount FROM dbo.Orders WHERE Status = 1;
-- 用包含列覆盖(见 05.4.1.2-006)
CREATE NONCLUSTERED INDEX IX_Orders_Status
ON dbo.Orders (Status) INCLUDE (Amount);
常见误区
- 误区:出现 Key Lookup 就一定是坏计划。正解:命中行数少时回表代价低;问题在于命中大量行后逐行回表。
- 误区:把所有查询列都加进索引键列。正解:非定位/排序用的列应放 INCLUDE,不占用键序位置。
追问
- 问:执行计划怎么判断回表次数?答:看键查找算子的实际行数(Actual Number of Rows)与外层索引扫描行数。
- 问:键查找与书签查找是一回事吗?答:SQL Server 文档称 Key Lookup;早期/通用说法叫书签查找,堆表对应 RID Lookup。
05.4.1.2-006 什么是包含列(INCLUDE)?和把列放进索引键有什么区别?
频率 ★★★★★ | 难度 进阶
一句话结论
INCLUDE 把非键列挂到非聚集索引叶子上用于覆盖查询,不参与索引键的排序与定位;放键列则参与最左前缀与顺序。
概念解释
- 键列:决定索引顺序,支持 Seek、范围扫描、排序,受最左前缀约束。
- 包含列:只存在于叶子页,让查询不必回表;不影响索引组织结构。
- 覆盖索引:查询的过滤/连接列在键上,返回列在键或包含列中。
原理流程
- 建索引时区分
ON (键列...)与INCLUDE (包含列...);包含列不用于定位条件。
代码 / 命令
-- 高频查询:按 Status 等值过滤,返回 Amount、CreateTime
SELECT Status, Amount, CreateTime
FROM dbo.Orders WHERE Status = 1;
CREATE NONCLUSTERED INDEX IX_Orders_Status
ON dbo.Orders (Status)
INCLUDE (Amount, CreateTime); -- 覆盖列,不进键序
常见误区
- 误区:包含列也能用于
WHERE定位。正解:包含列不出现在键序上,不能单独 Seek;过滤仍需键列。 - 误区:INCLUDE 列没有宽度限制。正解:索引总体宽度受引擎限制,大列不应批量纳入。
追问
- 问:包含列能建唯一约束吗?答:唯一性由键列决定,INCLUDE 列不参与唯一性判断。
- 问:覆盖索引有什么代价?答:索引体积变大、写入维护成本上升,需按高频查询权衡。
05.4.1.2-007 联合索引的键列顺序怎么排?
频率 ★★★★☆ | 难度 进阶
一句话结论
联合索引按键列顺序逐级有序:等值列在前、范围列在后,排序列接续;顺序不同是不同索引。
概念解释
- 索引
(a, b, c):先按 a 排序,a 相同按 b,再按 c。 - 只能从最左列开始定位;遇到范围条件后,后续列不再用于索引定位。
- 与 MySQL 最左前缀规则一致,可对照 05.1.1-005。
原理流程
索引 (Status, CreateTime)
Status=1 内按 CreateTime 有序
WHERE Status=1 AND CreateTime >= @t → 定位 Status 后范围扫描时间
WHERE CreateTime >= @t → 缺最左列,无法走该索引
代码 / 命令
-- 高频:租户 + 状态等值,时间范围并排序
CREATE NONCLUSTERED INDEX IX_Orders_TenantStatusTime
ON dbo.Orders (TenantId, Status, CreateTime);
SELECT * FROM dbo.Orders
WHERE TenantId = @t AND Status = 1
ORDER BY CreateTime DESC;
常见误区
- 误区:
(a,b)与(b,a)可以互相替代。正解:最左列不同,可用查询模式不同。 - 误区:范围列后面的等值列还能参与索引过滤。正解:定位到范围后后续列无序,只能在结果上过滤。
追问
- 问:区分度低的列放前面一定不好吗?答:若它是等值过滤的入口仍可前置;需结合实际查询与优化器估算。
- 问:排序方向不一致能走索引吗?答:普通索引按键序固定,混合升降序可能需要排序算子;SQL Server 降序索引支持需按版本核对(待确认目标版本)。
05.4.1.2-008 什么是筛选索引(Filtered Index)?
频率 ★★★★☆ | 难度 高级
一句话结论
筛选索引是带 WHERE 谓词的非聚集索引,只索引满足条件的行;体积小、维护成本低,适合选择性高的子集数据。
概念解释
- 典型场景:状态列只有少量"未完成"行;只给活跃行建索引,而非全表。
- 筛选索引必须能被查询谓词精确匹配才能命中(谓词需与索引过滤条件兼容)。
原理流程
- 索引页只包含满足
filter_definition的行;插入不满足条件的行不进入该索引。
代码 / 命令
-- 只为未支付订单建索引
CREATE NONCLUSTERED INDEX IX_Orders_Open
ON dbo.Orders (CustomerId)
INCLUDE (Amount, CreateTime)
WHERE Status = 0;
-- 命中条件:查询谓词需包含 Status = 0
SELECT CustomerId, Amount FROM dbo.Orders
WHERE CustomerId = @c AND Status = 0;
常见误区
- 误区:筛选索引可以替代普通索引服务全表查询。正解:不满足过滤谓词的行不在索引中,相关查询无法使用它。
- 误区:筛选条件可以用任意复杂表达式。正解:筛选谓词有受支持的表达式范围(比较常量、
IN列表等),需按文档核对。
追问
- 问:筛选索引与筛选统计信息是什么关系?答:可配合创建筛选统计信息,提升子集查询的估算准确性。
- 问:什么数据适合筛选索引?答:低基数列中只占小比例的活跃子集,如未删除、未支付、未归档记录。
05.4.1.2-009 唯一索引怎么处理 NULL?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
SQL Server 唯一索引中,多条 NULL 是否允许取决于索引类型与版本:普通唯一索引默认只允许一个 NULL(与 MySQL 多 NULL 不同)。
概念解释
- SQL Server 把
NULL视为可比较的相等值,唯一键上重复 NULL 会冲突;可用筛选索引排除 NULL 行。 WHERE Col IS NULL/IS NOT NULL可以走索引。
原理流程
- 插入第二个 NULL 到唯一索引列时触发唯一约束冲突。
代码 / 命令
-- 唯一索引只约束非 NULL 值
CREATE UNIQUE NONCLUSTERED INDEX UX_Orders_Code
ON dbo.Orders (Code)
WHERE Code IS NOT NULL;
常见误区
- 误区:各数据库唯一索引对 NULL 行为一致。正解:SQL Server 默认单 NULL,MySQL InnoDB 唯一索引允许多个 NULL,PG 同样允许多 NULL。
- 误区:
COUNT(Col)统计 NULL。正解:COUNT(Col)忽略 NULL,COUNT(*)统计全部行。
追问
- 问:业务列尽量用 NOT NULL 吗?答:能确定默认值时建议
NOT NULL DEFAULT,减少三值逻辑歧义。 - 问:三值逻辑对
WHERE有什么影响?答:NULL = NULL结果为 UNKNOWN,不被WHERE选中。
05.4.1.2-010 索引重建(REBUILD)和重组(REORGANIZE)有什么区别?
频率 ★★★☆☆ | 难度 进阶
一句话结论
REORGANIZE 联机整理叶子页顺序与页内空间,开销小;REBUILD 重建整个索引,彻底消除碎片并可改填充因子,资源开销大。
概念解释
- REORGANIZE:压缩叶子页、整理页链顺序,不重建非叶子与结构。
- REBUILD:丢弃旧索引重建,可更换填充因子、压缩选项、文件组;等价于先删后建(内部优化)。
- 联机重建选项
ONLINE = ON可减少阻塞,但受索引类型与版本限制。
原理流程
- 碎片低、页数少:重组即可;碎片高、索引大:重建收益明显。
- 重建期间产生日志与临时空间需求,大表应低峰执行并监控。
代码 / 命令
-- 重组单个索引
ALTER INDEX IX_Orders_Status ON dbo.Orders REORGANIZE;
-- 重建单个索引
ALTER INDEX IX_Orders_Status ON dbo.Orders
REBUILD WITH (FILLFACTOR = 90, ONLINE = ON);
-- 重建表上所有索引
ALTER INDEX ALL ON dbo.Orders REBUILD;
常见误区
- 误区:重组能把索引迁移到别的文件组。正解:迁移文件组要重建索引并指定
ON 文件组。 - 误区:重建索引会自动更新统计信息。正解:重建会刷新统计,但重组不一定;必要时显式
UPDATE STATISTICS。
追问
- 问:重建聚集索引会连带重建非聚集索引吗?答:会,非聚集索引依赖聚集键,重建聚集索引会导致非聚集索引重建。
- 问:大表重建空间不够怎么办?答:先评估磁盘空间与日志空间,可分区/分段处理或联机重建。
05.4.1.2-011 索引和统计信息是什么关系?
频率 ★★★☆☆ | 难度 进阶
一句话结论
索引提供访问路径,统计信息提供行数与分布估算;优化器依据统计信息判断走哪个索引,二者缺一不可。
概念解释
- 统计信息(直方图、密度)描述键值分布,供成本模型估算选择性与扫描行数。
- 索引存在但统计过期,优化器可能选错路径。
- 详细统计信息机制见 05.4.2.2。
原理流程
- 建索引时自动生成对应统计;大批量增删后统计可能过期,需
UPDATE STATISTICS。
代码 / 命令
-- 查看索引对应的统计信息与更新时间
SELECT s.name AS stats_name, st.stats_date = STATS_DATE(st.object_id, st.stats_id)
FROM sys.stats st
JOIN sys.stats_columns sc ON sc.stats_id = st.stats_id AND sc.object_id = st.object_id
JOIN sys.columns c ON c.object_id = sc.object_id AND c.column_id = sc.column_id
WHERE st.object_id = OBJECT_ID('dbo.Orders');
常见误区
- 误区:建了索引就一定被使用。正解:统计信息不准时优化器可能估算走扫描更便宜而放弃索引。
- 误区:索引删了统计信息还在。正解:索引删除时其自动统计信息一并移除。
追问
- 问:什么操作会刷新统计信息?答:重建索引、
UPDATE STATISTICS、达到自动更新阈值;具体见 05.4.2.2。 - 问:统计信息采样是全表吗?答:默认采样估算,大表可配置采样比例或增量统计。
05.4.1.2-012 怎么评估索引碎片并决定处理动作?
频率 ★★★☆☆ | 难度 进阶
一句话结论
用 sys.dm_db_index_physical_stats 看 avg_fragmentation_in_percent 与 page_count,结合页数决定重组、重建或不处理。
概念解释
- 碎片百分比区分逻辑碎片(页链顺序)与区碎片(物理连续)。
- 阈值经验:<10% 忽略;10%~30% 重组;>30% 且页数多则重建;小索引即使比例高也收益有限。
原理流程
- 先按
page_count > 1000过滤出有处理价值的大索引,再按碎片排序。
代码 / 命令
SELECT OBJECT_NAME(ps.object_id) AS table_name, i.name AS index_name,
ps.avg_fragmentation_in_percent, ps.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ps
JOIN sys.indexes i ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.page_count > 1000
ORDER BY ps.avg_fragmentation_in_percent DESC;
常见误区
- 误区:每天定时重建所有索引。正解:按碎片与页数择机处理,无谓重建消耗 IO 与日志。
- 误区:碎片高一定查询慢。正解:小表全缓存后碎片影响很小;顺序扫描大表时外部碎片才明显。
追问
- 问:碎片统计在联机状态下准确吗?答:是估算采样,
LIMITED模式不读 LOB 页,大表可用SAMPLED/DETAILED复核。 - 问:删除大量数据后碎片高怎么办?答:先更新统计,再评估重建;空间归还还需收缩文件。
05.4.1.2-013 填充因子在索引维护中怎么设置?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
填充因子只在重建索引时决定叶子页保留的空闲比例;写入频繁、随机插入多的表适当降低,只读表用 100。
概念解释
- 默认 0(等价 100):页填满,不留空闲。
- 低于 100:预留空间给后续插入/更新,减少页分裂,但索引体积变大。
PAD_INDEX = ON把空闲比例应用到非叶子节点。
原理流程
- 填充因子不实时作用于已有页,只在重建时按比例填充;页后续写满仍会分裂。
代码 / 命令
ALTER INDEX IX_Orders_Status ON dbo.Orders
REBUILD WITH (FILLFACTOR = 90, PAD_INDEX = ON);
常见误区
- 误区:填充因子设一次就长期生效。正解:它是重建时的物理布局参数,持续写入后碎片仍会累积。
- 误区:堆表也受填充因子控制。正解:填充因子只作用于索引。
追问
- 问:自增键聚集索引需要低填充因子吗?答:顺序追加主要写右侧新页,通常 100 即可;随机更新变长列的表才考虑预留空间。
- 问:降低填充因子有什么代价?答:索引页数增加,缓存命中与扫描效率下降。
05.4.1.2-014 列存索引在索引体系中处于什么位置?
频率 ★★☆☆☆ | 难度 进阶
一句话结论
列存索引按列组织数据、配合批处理与高压缩,服务分析型扫描聚合;与行存 B 树索引共存,详细原理见 05.4.2.4。
概念解释
- 行存索引:单行各列在一起,适合点查与事务。
- 列存索引:同列数据连续存放,适合大表扫描、分组聚合与高压缩。
原理流程
- 优化器按代价在执行计划中选择行存或列存访问路径;混合负载可用行存 + 列存组合。
代码 / 命令
-- 聚集列存索引(分析大表示例)
CREATE CLUSTERED COLUMNSTORE INDEX CIX_FactSales ON dbo.FactSales;
常见误区
- 误区:列存索引可以替代所有 B 树索引。正解:高频点查、单行更新用行存;列存面向扫描聚合。
- 误区:列存表更新很快。正解:频繁单行更新维护成本高,增量行组与压缩合并需要代价。
追问
- 问:列存为什么压缩率高?答:同列类型一致、重复值多,可做字典与增量编码。
- 问:列存详细行组结构在哪看?答:见 05.4.2.4-001 至 05.4.2.4-005。
05.4.1.2-015 怎么发现缺失的索引和无效索引?
频率 ★★★★☆ | 难度 高级
一句话结论
缺失索引用 sys.dm_db_missing_index_* DMV 与查询计划中的建议查看;无效索引通过索引使用 DMV 与写入成本排查。
概念解释
- 缺失索引 DMV:记录查询优化过程中发现的可用索引建议,含等值/包含列与用户开销。
- 索引使用情况:
sys.dm_db_index_usage_stats统计用户查找/扫描/更新次数。
原理流程
- 查缺失索引建议,按
user_seeks * avg_user_impact排序。 - 核对建议列是否与已有索引重复,避免冗余。
- 检查长期
user_seeks/scans = 0却持续被更新的索引,评估删除。
代码 / 命令
-- 缺失索引建议(等值列 equality_columns,包含列 included_columns)
SELECT TOP 20
OBJECT_NAME(d.object_id) AS table_name,
d.equality_columns, d.inequality_columns, d.included_columns,
s.user_seeks, s.avg_user_impact
FROM sys.dm_db_missing_index_details d
JOIN sys.dm_db_missing_index_groups g ON g.index_handle = d.index_handle
JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
ORDER BY s.user_seeks * s.avg_user_impact DESC;
-- 从未被读取的索引
SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
us.user_seeks, us.user_scans, us.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats us
ON us.object_id = i.object_id AND us.index_id = i.index_id
AND us.database_id = DB_ID()
WHERE i.type_desc IN ('CLUSTERED','NONCLUSTERED')
AND COALESCE(us.user_seeks,0) = 0 AND COALESCE(us.user_scans,0) = 0;
常见误区
- 误区:DMV 推荐的索引照单全建。正解:建议基于历史查询快照,需核对重复度、写入成本与实际选择性。
- 误区:实例重启后 DMV 统计仍保留。正解:这些 DMV 自上次实例重启起累计,需结合时间窗口解读。
追问
- 问:数据库引擎优化顾问(DTA)和 DMV 建议什么关系?答:DTA 可对 workload 做更全面的索引/统计建议;DMV 是运行期轻量线索。
- 问:删除索引前要注意什么?答:先隐藏/观察一段时间,确认无查询依赖,保留回滚脚本。
05.4.1.2-016 索引的写入代价与冗余/重复索引怎么排查?
频率 ★★★☆☆ | 难度 高级
一句话结论
每个索引都在写入时被维护,冗余(前缀被另一索引覆盖)与重复(同列同序)索引只增加写放大,应定期清理。
概念解释
- 重复索引:列与顺序完全相同,建了多次。
- 冗余索引:已有
(a,b),再建(a)即冗余(最左前缀可复用);(b,a)不算冗余。 - 例外:唯一约束与普通索引语义不同,不能因结构前缀相同就删除唯一索引。
原理流程
- 写入语句对每个相关索引做插入/删除/更新;索引越多,事务持锁与日志成本越高。
代码 / 命令
-- 列出各表索引键列,人工比对重复与前缀冗余
SELECT OBJECT_NAME(i.object_id) AS table_name, i.name AS index_name,
STUFF((SELECT ', ' + c.name
FROM sys.index_columns ic
JOIN sys.columns c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
AND ic.is_included_column = 0
ORDER BY ic.key_ordinal
FOR XML PATH('')), 1, 2, '') AS key_columns
FROM sys.indexes i
WHERE i.type_desc IN ('CLUSTERED','NONCLUSTERED')
ORDER BY table_name, index_name;
常见误区
- 误区:
(a,b)存在时(b,a)也是冗余。正解:最左列不同,服务不同查询模式。 - 误区:清理冗余索引可以直接在线上 DROP。正解:先确认无计划依赖、低峰执行、保留回滚脚本。
追问
- 问:唯一索引被普通索引前缀覆盖时能删吗?答:不能,唯一性约束语义不同,需保留。
- 问:索引更新是事务内同步完成的吗?答:是,提交前必须维护相关索引结构。
05.4.1.2-017 堆表上的非聚集索引有什么特点?什么场景适合堆表?
频率 ★★☆☆☆ | 难度 高级
一句话结论
堆表非聚集索引叶子存 RID;堆表无键序,适合批量装载后再建索引、或数据以随机插入为主且无强排序查询的场景,OLTP 多数情况仍建议聚集索引。
概念解释
- 堆表行位置由 RID 标识;行因更新迁移时用转发指针维护 RID 定位。
- 堆表没有聚集键带来的顺序写与范围扫描优势,但也避免了聚集键维护成本。
原理流程
- 批量导入:先装堆数据,再建非聚集索引,可避免装载过程中维护聚集 B 树。
- 删除/更新后堆空间不自动归还,需收缩或重建。
代码 / 命令
-- 显式创建非聚集主键(表保持为堆)
ALTER TABLE dbo.Staging ADD CONSTRAINT PK_Staging PRIMARY KEY NONCLUSTERED (Id);
常见误区
- 误区:堆表就是"没有索引的表"。正解:堆是无聚集索引,仍可建多个非聚集索引。
- 误区:堆表插入一定比聚集索引快。正解:仅在无聚集键维护时占优;有序自增聚集键的顺序写通常很快。
追问
- 问:转发指针会带来什么问题?答:行迁移后经指针间接定位,额外一跳;长期更新的堆表宜重建。
- 问:堆表适合日志类数据吗?答:日志通常按时间范围查询,聚集索引(时间/自增键)更合适。
05.4.1.2-018 SQL Server 索引体系与 MySQL InnoDB、PostgreSQL 怎么对照?
频率 ★★★★☆ | 难度 高级
一句话结论
三者都用 B 树类索引,但组织基线不同:SQL Server/InnoDB 有聚簇概念,PG 是堆表 + 二级索引存堆元组指针;INCLUDE、覆盖索引思路相通。
概念解释
| 维度 | SQL Server | MySQL InnoDB | PostgreSQL |
|---|---|---|---|
| 表基线 | 堆或聚集索引表 | 聚簇索引组织表 | 堆表 |
| 二级索引叶子 | 聚集键或 RID | 主键值 | 堆元组指针(tid) |
| 覆盖非键列 | INCLUDE | 索引键携带,无独立 INCLUDE 子句(8.0 起有覆盖列思路不同) | INCLUDE |
| 筛选索引 | Filtered Index | 不支持等价物(可用生成列) | Partial Index |
| 其他索引 | 列存、XML、空间、全文 | 全文、空间、哈希(自适应) | GIN/GiST/BRIN/Hash/表达式/部分索引 |
原理流程
- 对照主线:二级索引如何回到主表——SQL Server 键查找、InnoDB 回聚簇索引、PG 按 tid 取堆元组。
代码 / 命令
-- SQL Server 覆盖索引
CREATE NONCLUSTERED INDEX IX_C ON dbo.T (A) INCLUDE (B, C);
常见误区
- 误区:SQL Server 聚集索引等价于 InnoDB 聚簇索引。正解:概念相近(叶子=数据行),但实现、页大小与二级索引携带信息有差异。
- 误区:PG 二级索引回表也存主键。正解:PG 存堆元组指针 tid,更新后靠 MVCC 版本链定位。
追问
- 问:PG 表达式索引对应 SQL Server 什么?答:SQL Server 用计算列 + 索引实现类似效果。
- 问:InnoDB 为什么二级索引存主键?答:聚簇重组时主键稳定,二级索引不必存会变的行地址。
参考来源
- Microsoft Learn:聚集索引与非聚集索引:https://learn.microsoft.com/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described
- Microsoft Learn:包含列索引:https://learn.microsoft.com/sql/relational-databases/indexes/create-indexes-with-included-columns
- Microsoft Learn:筛选索引:https://learn.microsoft.com/sql/relational-databases/indexes/create-filtered-indexes
- Microsoft Learn:缺失索引 DMV:https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-db-missing-index-details-transact-sql