参考资料

• MySQL 8.4：How MySQL Uses Indexes
• MySQL 8.4：EXPLAIN Statement
• MySQL 8.4：InnoDB Indexes

Mysql（02） - 索引原理与执行计划

读完后，你应能完成以下任务：

• 绘制“Mysql（02） - 索引原理与执行计划 / B+Tree 索引如何工作”的关键对象与数据流，解释“通过二级索引查非覆盖列时，需要先定位主键再回聚簇索引取行，这就是回表。”，并用源码位置、日志或 Trace 标注证据。
• 为“Mysql（02） - 索引原理与执行计划 / 从查询反推索引”设计正常与异常输入，验证“低选择性字段单列索引通常收益有限，但与租户、时间等字段组成联合索引后可能有效。”，输出首个偏差位置与回归测试结果。
• 实现“Mysql（02） - 索引原理与执行计划 / 读懂执行计划”的最小代码或配置，检验“测试库只有几百行时，全表扫描可能比索引更快，不能代表生产计划。”，输出命令、结果与 Diff，并说明不适用边界。

一、先建立全局：索引原理与执行计划 是什么？

理解“索引原理与执行计划”，先要把标题中的对象放进同一条处理链：它接收什么输入，经过哪些状态变化，最终用什么证据判断结果。下表不另造概念，只把作者正文已经解释的章节按依赖顺序连起来。

“索引原理与执行计划”的第一个核心判断是：通过二级索引查非覆盖列时，需要先定位主键再回聚簇索引取行，这就是回表。。先弄清这个判断中的对象和输入输出，后面的实现、故障和验收才有共同语境。

顺序	章节	读完本节应抓住的结论
1	B+Tree 索引如何工作	通过二级索引查非覆盖列时，需要先定位主键再回聚簇索引取行，这就是回表。
2	从查询反推索引	低选择性字段单列索引通常收益有限，但与租户、时间等字段组成联合索引后可能有效。
3	读懂执行计划	测试库只有几百行时，全表扫描可能比索引更快，不能代表生产计划。
4	常见失败与治理	删除“未使用索引”前要覆盖完整业务周期，确认监控包含低频报表和故障流程，并准备可回滚 DDL。
5	InnoDB 聚簇索引的叶子节点保存整行	InnoDB 聚簇索引的叶子节点保存整行，二级索引叶子保存索引列和主键。
6	主键过大会膨胀所有二级索引	主键过大会膨胀所有二级索引，因此主键不仅影响一棵树。

1.1 核心对象之间怎样衔接

flowchart LR
  S1["B+Tree 索引如何工作"] --> S2
  S2["从查询反推索引"] --> S3
  S3["读懂执行计划"] --> S4
  S4["常见失败与治理"] --> S5
  S5["InnoDB 聚簇索引的叶子节点保存整行"]

这张图只表达本文的讲解顺序，不替代正文机制。判断“索引原理与执行计划”是否真正掌握，需要能从最后一个结果沿图回到前面每个章节的输入、状态变化和证据。

1.2 再看失败：问题最早会出现在哪一步？

在“索引原理与执行计划”的对象和顺序已经明确后，再看可观察的失败：计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致。定位时不从最后一条错误猜原因，而是沿上图找第一个偏离正文结论的节点。

二、B+Tree 索引如何工作

InnoDB 聚簇索引的叶子节点保存整行，二级索引叶子保存索引列和主键。通过二级索引查非覆盖列时，需要先定位主键再回聚簇索引取行，这就是回表。主键过大会膨胀所有二级索引，因此主键不仅影响一棵树。

联合索引按列顺序排序。等值过滤列通常放在前面，范围、排序列放后面，但最终顺序要结合选择性、查询组合和排序需求。最左前缀不是“SQL 必须写最左列”，而是优化器能否在有序索引中确定连续范围；跳过中间列通常不能继续缩小扫描区间。

三、从查询反推索引

SELECT id, order_no, amount, created_at
FROM orders
WHERE tenant_id = ?
  AND status = 'PENDING'
  AND created_at >= ?
ORDER BY created_at DESC
LIMIT 50;

CREATE INDEX idx_order_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

该索引先固定租户和状态，再扫描时间范围，并可直接提供排序。若查询只返回索引中已有的列和主键，可形成覆盖索引减少回表；但为了覆盖所有页面不断追加列会增加存储和写放大。索引设计应围绕高频、昂贵且有稳定契约的查询，不围绕单个字段列表。

低选择性字段单列索引通常收益有限，但与租户、时间等字段组成联合索引后可能有效。LIKE '%keyword%' 无法利用普通 B+Tree 前缀定位，应考虑全文索引或 Elasticsearch，而不是继续堆索引。

四、读懂执行计划

EXPLAIN ANALYZE
SELECT id, order_no, amount, created_at
FROM orders
WHERE tenant_id = 42 AND status = 'PENDING'
ORDER BY created_at DESC
LIMIT 50;

检查实际行数与估算行数、访问类型、使用的 Key、扫描行数、过滤比例、排序和临时表。估算严重偏差可能来自统计信息过旧、列相关性或参数分布倾斜。看到 Using index 不等于整体查询快，仍要看返回行数、循环次数和真实耗时。

优化前后使用相同数据规模和参数分布对比 P50、P95、逻辑读与返回行数。测试库只有几百行时，全表扫描可能比索引更快，不能代表生产计划。

五、常见失败与治理

重复索引和前缀重叠索引会增加写入成本；索引失效常见原因包括对索引列做函数计算、隐式类型转换、排序方向或列顺序不匹配。分页到很深时 OFFSET 仍需跳过大量行，改用基于稳定排序键的 Seek Pagination。

删除“未使用索引”前要覆盖完整业务周期，确认监控包含低频报表和故障流程，并准备可回滚 DDL。写入骤降时检查索引数量、页分裂、锁和磁盘 I/O，不能只看查询延迟。

验收清单

• 每个新增索引都有对应查询、预期扫描范围和删除条件。
• EXPLAIN ANALYZE 的实际扫描行数、排序方式和耗时符合预算。
• 覆盖索引收益大于写放大与存储成本。
• 慢查询使用真实参数分布回归，避免只测一个理想值。

六、动手验证：先跑通 索引原理与执行计划，再改变一个变量

前面的章节已经建立问题、概念和机制。现在把“索引原理与执行计划”放进同一套基线中运行；本节不再引入新术语，只验证前文结论能否被复现。

6.1 基线与候选只允许一个变量不同

验证“索引原理与执行计划”时，先固定数据快照、并发条件、客户端配置、拓扑和故障注入点。候选方案只能改变本次要验证的变量；如果同时更换数据、依赖和配置，即使结果改善，也不能知道是哪一项产生作用。

执行“索引原理与执行计划”时，动作是：执行正常读写与故障场景，记录查询计划、锁、复制或消费状态。原始结果不能只保留截图或汇总分数，必须同步保存：执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验，使下一次复查可以在同一输入上重放。

实验要素	本文要求
固定条件	固定数据快照、并发条件、客户端配置、拓扑和故障注入点
唯一变量	本次候选方案与基线之间的一项明确差异
原始证据	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验
通过阈值	一致性与性能满足正文约束，故障恢复后没有丢失或重复副作用
立即停止	计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致

6.2 执行前先排除不可比较条件

“索引原理与执行计划”开始前先确认下面四项；任一项不成立，都应先修复实验条件，而不是解释结果。

• 基线能够在“索引原理与执行计划”的当前环境重复运行。
• 候选只改变一个与“索引原理与执行计划”结论直接相关的条件。
• “索引原理与执行计划”的基线和候选使用同一批输入、同一版本依赖与同一通过阈值。
• “索引原理与执行计划”的原始输出和失败现场不会被重试、格式化或汇总覆盖。

6.3 执行后先核对证据完整性

结果出来后先检查证据，再讨论“索引原理与执行计划”是否通过。缺少中间状态时，最终输出只能说明现象，不能证明机制。

检查项	当前文章的判定
输入可追溯	固定数据快照、并发条件、客户端配置、拓扑和故障注入点
过程可回放	执行正常读写与故障场景，记录查询计划、锁、复制或消费状态
结果可审计	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验

“索引原理与执行计划”的一次合格基线对照按以下顺序执行：

1. 保存“索引原理与执行计划”基线版本及输入摘要，确认基线本身可以重复运行。
2. 写下“索引原理与执行计划”候选方案唯一变化的变量，以及它预期影响的指标。
3. 在同一环境执行“索引原理与执行计划”：执行正常读写与故障场景，记录查询计划、锁、复制或消费状态。
4. 为“索引原理与执行计划”保存：执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验。
5. 使用“索引原理与执行计划”预登记条件判断：一致性与性能满足正文约束，故障恢复后没有丢失或重复副作用。
6. 如果“索引原理与执行计划”未通过，不修改第二个变量，先恢复基线并保留失败现场。

七、用一张矩阵验证 索引原理与执行计划 的关键结论

矩阵按正文顺序列出“索引原理与执行计划”的结论。一次实验只选择一行，只改变这一行对应的条件；不要把多行合并成一个无法归因的大实验。

正文章节	已解释的结论	本轮唯一变量	必须保存的证据
B+Tree 索引如何工作	通过二级索引查非覆盖列时，需要先定位主键再回聚簇索引取行，这就是回表。	只改变与“B+Tree 索引如何工作”相关的条件	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验
从查询反推索引	低选择性字段单列索引通常收益有限，但与租户、时间等字段组成联合索引后可能有效。	只改变与“从查询反推索引”相关的条件	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验
读懂执行计划	测试库只有几百行时，全表扫描可能比索引更快，不能代表生产计划。	只改变与“读懂执行计划”相关的条件	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验
常见失败与治理	删除“未使用索引”前要覆盖完整业务周期，确认监控包含低频报表和故障流程，并准备可回滚 DDL。	只改变与“常见失败与治理”相关的条件	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验
InnoDB 聚簇索引的叶子节点保存整行	InnoDB 聚簇索引的叶子节点保存整行，二级索引叶子保存索引列和主键。	只改变与“InnoDB 聚簇索引的叶子节点保存整行”相关的条件	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验
主键过大会膨胀所有二级索引	主键过大会膨胀所有二级索引，因此主键不仅影响一棵树。	只改变与“主键过大会膨胀所有二级索引”相关的条件	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验

7.1 记录本次实际实验

下面的记录用于“索引原理与执行计划”当前这一次实验，不是第二套知识目录。先从矩阵选择一个章节，再填写实际值；没有填写的字段表示尚未验证。

topic: "索引原理与执行计划"
selected_chapter: required
claim_from_article: required
baseline_version: required
changed_condition: exactly_one
execution: "执行正常读写与故障场景，记录查询计划、锁、复制或消费状态"
evidence: "执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验"
pass_when: "一致性与性能满足正文约束，故障恢复后没有丢失或重复副作用"
stop_when: "计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致"
observed_result: required
first_deviation: null_or_evidence
recovery_replay: required_after_failure

7.2 边界实验必须证明能够停止和恢复

成功路径只能证明“索引原理与执行计划”在当前样本上工作，不能证明它可以进入生产。边界实验需要主动制造：计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致，并观察系统是否在产生不可逆副作用前停止。

场景	只改变什么	应保存什么	通过标准
正常路径	使用已知有效输入	执行计划、慢日志、锁等待、Offset、复制延迟、指标和数据校验	一致性与性能满足正文约束，故障恢复后没有丢失或重复副作用
边界路径	把一个输入推进到约束临界值	临界值前后的输出与指标	不静默降级，不把部分结果冒充成功
明确失败	注入：计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致	原始错误、首个异常阶段和最终状态	失败被正确分类且没有扩大副作用
恢复重放	执行：从数据入口、存储状态、复制消费链路和恢复步骤定位根因	原失败样本的复测证据	原样本恢复，正常样本没有回归

恢复动作不是简单重启。对于“索引原理与执行计划”，第一步是：从数据入口、存储状态、复制消费链路和恢复步骤定位根因。完成后使用原始失败样本复测；只验证一个新样本成功，不能证明触发条件已经消失。

“索引原理与执行计划”边界实验结束后，应把正常、临界、失败和恢复四类记录放在同一个运行批次中。这样才能区分“候选方案真的修复问题”和“环境变化让问题暂时没有出现”。

八、索引原理与执行计划 的结果解释

解释“索引原理与执行计划”实验时先看首个偏差，而不是最后一条错误。最后的异常通常只是上游状态错误的结果；从末端反推容易误把症状当根因。

观察结果	可以支持的判断	下一步
主链路没有达到预期	计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致	先执行：从数据入口、存储状态、复制消费链路和恢复步骤定位根因
异常链路无法恢复	计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致	先执行：从数据入口、存储状态、复制消费链路和恢复步骤定位根因
新样本成功但原样本仍失败	修复没有覆盖原始触发条件	固定原失败输入，恢复基线后重新比较
指标改善但证据无法回链	数据、版本或中间状态没有固定	暂停发布，补齐可追溯记录后重跑

“索引原理与执行计划”只有同时满足“一致性与性能满足正文约束，故障恢复后没有丢失或重复副作用”，并且没有出现“计划退化、死锁、热点击穿、消息重复丢失或恢复后数据不一致”，才可以认为主链路通过。这里的“通过”只对当前固定版本、样本和环境有效，不能外推到尚未测试的容量、权限或数据分布。

如果“索引原理与执行计划”候选方案与基线差异很小，先检查证据分辨率是否足够；如果差异很大，先排除数据泄漏、环境漂移和版本不一致。两种情况都不能只看一个汇总均值，需要回到逐样本输出和中间状态。

“索引原理与执行计划”故障定位完成后，记录“现象、首个偏差、根因、改动、原样本复测”五项。缺少原样本复测时，只能标记为待观察，不能标记为已解决。

九、索引原理与执行计划 的发布判断

发布判断需要把“索引原理与执行计划”的质量、失败边界和恢复能力放在同一份记录中。以下任一条件缺失，都应停止扩量，而不是用“基本正常”替代证据。

• “索引原理与执行计划”的基线与候选只存在一个计划内变量。
• “索引原理与执行计划”的输入、代码、依赖、配置和数据版本可以追溯。
• “索引原理与执行计划”的正常、临界、失败和恢复样本使用同一套断言。
• “索引原理与执行计划”的原始输出、中间状态和失败现场已经保留。
• “索引原理与执行计划”的日志、Trace、截图和测试数据已经脱敏。
• “索引原理与执行计划”的停止条件、负责人和回滚入口已经演练。
• “索引原理与执行计划”尚未覆盖的输入、权限、容量和外部依赖已经登记。

最终记录至少包含基线版本、唯一变量、原始证据、首个偏差、恢复复测和发布责任人。没有参与本次修改的人如果不能据此重放“索引原理与执行计划”的判断，就不能发布。

十、总结

• B+Tree 索引如何工作：通过二级索引查非覆盖列时，需要先定位主键再回聚簇索引取行，这就是回表。
• 从查询反推索引：低选择性字段单列索引通常收益有限，但与租户、时间等字段组成联合索引后可能有效。
• 读懂执行计划：测试库只有几百行时，全表扫描可能比索引更快，不能代表生产计划。
• 常见失败与治理：删除“未使用索引”前要覆盖完整业务周期，确认监控包含低频报表和故障流程，并准备可回滚 DDL。