罗罗编程学习记录
本页内容 展开查看

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 取行。
  • 执行计划中键查找通常配合嵌套循环,外层每命中一行就回表一次。

原理流程

  1. 非聚集索引扫描/Seek 得到键值与定位信息。
  2. 所需列不在索引中 → 对每行执行键查找。
  3. 命中行数多时回表成本累加,优化器可能直接选择聚集索引扫描。

代码 / 命令

-- 只有 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 统计用户查找/扫描/更新次数。

原理流程

  1. 查缺失索引建议,按 user_seeks * avg_user_impact 排序。
  2. 核对建议列是否与已有索引重复,避免冗余。
  3. 检查长期 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 为什么二级索引存主键?答:聚簇重组时主键稳定,二级索引不必存会变的行地址。

参考来源