MySQL 索引、B+ 树与 EXPLAIN
同一条订单列表查询，为什么“用了索引”仍会扫描九万行？先判断返回集合与顺序，再沿索引记录→过滤→聚簇记录→服务层迭代数工作量。索引是访问路径，不能替代 SQL 语义或事务协议。
固定基线为 mysql/mysql-server 的 mysql-8.0.39、InnoDB、默认 16KB 页；8.0 手册为滚动文档。2026-10-23 Docker 的8.0.39实验未运行；用户随后提供本机服务，实际运行版本为 MySQL Community 8.4.7。源码与实测版本分开，以下标明8.4.7的结果才是本机证据；Python推演始终另标教学模型。
从 SQL 语义到实际访问
同一条 SQL 在 MySQL 中至少经过三层：
- SQL 层语义：解析、权限、名称解析，决定查询“应该返回什么”。
- 优化器：根据统计信息、索引、连接顺序和成本模型，选择访问路径，产出执行计划。
- 存储引擎执行：InnoDB 通过 buffer pool、索引页、undo、redo 等完成实际读写。
EXPLAIN 展示的是优化器选择的计划，不是磁盘 I/O 账单。rows、filtered 和 cost 都是估算；真正的行数、循环次数和耗时要用 EXPLAIN ANALYZE（MySQL 8.0.18+）观察。即使 EXPLAIN ANALYZE 给出了实际值，它也会执行语句并带来测量开销，不能当作压测结果。
访问路径也不等于 I/O 次数。B+ 树的根页和上层页可能长期在 buffer pool 中；一次“走索引”的查询可能只读内存页，也可能因为冷缓存产生多次随机 I/O。计划中的 type 和 key 只能说明访问方式，不能单独推出延迟。
请求的 ORDER BY created_at DESC,id DESC 必须先定义唯一顺序；只按时间排序，两个同秒订单的先后没有语义保证。执行计划再回答从哪个索引开始、候选先过滤还是先回表、什么时候 LIMIT 可以停止。查询改写必须先用结果集对照证明等价，再讨论速度。
页、聚簇记录与二级索引
InnoDB 以页为索引读写单位。默认页大小是 16KB，由初始化时的 innodb_page_size 决定，之后不能随意改变。手册把 InnoDB 索引称为 B-tree，并说明索引记录存放在叶子页；工程上通常称这种“内部页只做导航、记录集中在叶子页、叶子页按 key 连接”的结构为 B+ 树。本文用“B+ 树”指这种形态，引用手册时保留其 B-tree 表述。
B+ 树的价值来自扇出和有序性：
- 内部页保存 key 与子页指针，扇出约为“页大小 ÷（key 宽度 + 指针开销）”。扇出越大，同样数据量下树越矮。
- 叶子页保存记录，并按 key 有序；范围查询可以定位起点后顺序扫描叶子页。
- 等值查询的成本约为“树高次页访问 + 叶子内查找”，范围查询还要加上扫描的叶子记录数。
页不会总是装满。手册给出的经验值是：顺序插入时页约 15/16 满；随机插入时页在 1/2 到 15/16 之间。页内空间不足会触发页分裂，删除或更新导致页填充率低于 MERGE_THRESHOLD（默认 50%）时，InnoDB 会尝试与相邻页合并。分裂和合并都会带来额外的页写、redo、undo 和 buffer pool 压力，所以“多建索引”不是免费的。
树高也不能简单背成“3 层就是 3 次 I/O”。树高取决于行宽、key 宽度、页大小和填充率；根页/上层页是否命中缓存、叶子页是否连续、是否回表，都会改变实际 I/O。能确定的是：B+ 树把“按 key 定位”的成本压到对数级，并让范围与排序可以利用叶子页顺序。
叶子页保存的对象
InnoDB 的表数据按聚簇索引组织：
- 有
PRIMARY KEY时，主键就是聚簇索引。 - 没有主键时，InnoDB 选择第一个所有列都
NOT NULL的UNIQUE索引作为聚簇索引。 - 两者都没有时，InnoDB 生成隐藏的
GEN_CLUST_INDEX，用一个 6 字节、随插入单调递增的 row ID 作为 key。
聚簇索引的叶子页保存完整行记录，所以按主键定位后可以直接得到整行。二级索引的叶子页保存“索引列 + 主键列”，不保存完整行；查询未覆盖的列时，需要先拿到主键，再回到聚簇索引取行，这就是回表。
这带来两个直接后果：
- 主键宽度会扩散到所有二级索引。主键越长，每个二级索引条目越大，页能容纳的条目越少，缓存效率越差。主键应尽量短、稳定，并避免使用会频繁更新的业务字段。
- 覆盖索引能省掉回表。如果查询需要的列都在同一个二级索引中（包括隐含的主键），InnoDB 只读二级索引即可；
EXPLAIN的Extra可能出现Using index。
注意：SELECT * 往往需要回表，因为二级索引通常不包含全部列；把 SELECT * 改成只取必要列，才有可能让覆盖索引生效。覆盖索引也不是无条件更快：索引列越多，叶子越宽，写入和缓存成本越高。
做一个有边界的扇出估算：假设每内部条目 24B、可用页空间 14KiB，则每页约容纳 597 个导航条目；这不是实际格式测量。叶子记录宽 200B 时约 71 条/页，1 亿行至少约 140 万叶页，内部层级约需要三层导航。压缩、记录头、变长字段、填充率改变后必须重算，不能由此承诺固定树高或 I/O 次数。
页分裂需要的是更强结构保护；事务 undo 描述行的逻辑修改，并非每次页分裂都产生一份业务 undo。btr_cur_optimistic_insert 与 btr_cur_pessimistic_insert 的 optimistic/pessimistic 指树修改策略，与业务版本 CAS 不是一回事。页 latch/mini-transaction 保护结构，事务记录锁保护并发语义。
联合索引的边界、过滤与排序
联合索引 (a, b, c) 可以看成一个按 a、再按 b、再按 c 排序的字典序数组。手册的规则是：查询可以使用索引的任意最左前缀，即 (a)、(a,b)、(a,b,c)；只查 b 或 (b,c) 不属于最左前缀，通常不能直接用这个索引定位。
对 WHERE a = ? AND b = ? AND c >= ?：
a、b的等值条件把扫描范围收窄到一段；c的范围条件继续收窄这段范围；- 在该范围内，结果天然按
c有序，ORDER BY c有机会直接利用索引顺序。
对 WHERE a = ? AND b >= ? AND c = ?：
a、b可以用于定位范围；c不能继续缩小 B+ 树的扫描边界，因为在b的范围内，c不是全局有序的；- 但
c仍在索引中，可以由 ICP（Index Condition Pushdown）在存储引擎读取索引条目时先过滤，减少不必要的回表。
对 WHERE a = ? AND c >= ?（缺少中间的 b）：
a的等值条件仍可用于定位；c在剩余范围内不连续，不能作为完整的范围边界；c可能通过 ICP 过滤，ORDER BY c也可能需要额外排序。key_len通常能看出实际用了哪些前缀，但要以真实执行计划为准。
联合索引的列顺序不是“把选择性高的列放前面”这一条规则就能决定。等值条件、范围条件、排序、覆盖和写入成本要一起看；最可靠的办法是用真实数据分布跑 EXPLAIN ANALYZE，而不是只看索引定义。
“范围后的列不再有用”过于粗糙。应分三问：能否缩小连续扫描边界，能否在索引上过滤，能否提供所需顺序或投影。即使范围优化构造了更精确端点，后续列也不因此在整个前置范围内全局有序。MySQL 8.0 的 skip scan 在特定覆盖单表查询等条件下可枚举缺失前缀，故“非最左一定全表扫”也不是定理。
ORDER BY 要求与索引列的方向模式兼容，固定为常量的前缀可跳过；全列反向可以反扫，混合升降要看实际索引定义。Using filesort 只是额外排序，不代表一定落磁盘。函数/类型转换通常使原列索引无法直接定位，但函数索引、生成列和索引全扫描仍是不同路径，不能统称索引消失。
原码：ICP 的继续、终止与聚簇读取
ha_innobase::index_read 把 Server 查找模式转为 InnoDB 游标模式，首次定位进入 row_search_mvcc，后续 index_next/general_fetch 推进游标；范围扫描不是每行都从根重新定位。树下降追到 btr_cur_search_to_nth_level，热点路径可能借自适应哈希，哈希不是另一份持久表索引。
原码连续摘录，仅截取局部、未改写：mysql/mysql-server · mysql-8.0.39 · storage/innobase/handler/ha_innodb.cc · L10287–L10295。
m_prebuilt->ins_sel_stmt = thd_is_ins_sel_stmt(m_user_thd);
ret = row_search_mvcc(buf, mode, m_prebuilt, match_mode, 0);
} else {
m_prebuilt->session = thd_to_innodb_session(m_user_thd);
ret = row_search_no_mvcc(buf, mode, m_prebuilt, match_mode, 0);
}这段位于 index_read 的分支尾部，普通行进入 MVCC 搜索；内部表等路径用 row_search_no_mvcc。buf 是返回行缓冲，找到索引键后仍可能需要 undo、聚簇查找和服务层过滤。
原码连续摘录，仅截取局部、未改写：mysql/mysql-server · mysql-8.0.39 · storage/innobase/row/row0sel.cc · L5416–L5433。
/* Check if the record matches the index condition. */
switch (row_search_idx_cond_check(buf, prebuilt, rec, offsets)) {
case ICP_NO_MATCH:
prebuilt->try_unlock(true);
goto next_rec;
case ICP_OUT_OF_RANGE:
err = DB_RECORD_NOT_FOUND;
prebuilt->try_unlock(true);
goto idx_cond_failed;
case ICP_MATCH:
break;
}
/* Get the clustered index record if needed, if we did not do the
search using the clustered index. */
if (index != clust_index && prebuilt->need_to_access_clustered) {
requires_clust_rec:ICP_NO_MATCH 跳过当前候选继续扫描；若误当 EOF 会漏掉后续匹配行。ICP_OUT_OF_RANGE 才转向结束路径，若只当不匹配则多扫描范围。匹配后仍检查 need_to_access_clustered，所以 ICP 省的是不合格候选的聚簇读取，不是覆盖索引的同义词。
源码的 requires_clust_rec 标签还供历史可见性分支使用：二级条目没有完整聚簇行版本，遇删除标记或可见性不确定时，即使投影覆盖也可能读取聚簇记录与 undo。覆盖索引通常省掉取列回表，不是所有并发分支零聚簇访问的保证。
EXPLAIN 的估算与实际迭代
EXPLAIN 的常用列：
| 列 | 含义 | 容易误读的点 |
|---|---|---|
type | 表的访问方式 | 不是“索引质量评分”；index 是全索引树扫描，不等于精确查找 |
possible_keys | 可能被选中的索引 | 有候选不代表一定使用 |
key | 优化器实际选择的索引 | NULL 表示没有使用索引 |
key_len | 实际使用的索引字节数 | 可判断用了联合索引的哪些部分；可空列通常多 1 字节，且受字符集/列宽影响 |
ref | 与索引比较的列或常量 | 等值查找时常见 const、列名 |
rows | 估算需要检查的行数 | 是估算，不是实际返回行数 |
filtered | 表条件过滤后的估算百分比 | rows × filtered / 100 近似参与下一次连接的行数 |
Extra | 访问与过滤的附加信息 | 同名信号含义不同，必须结合 type/key 读 |
常见 type 从较优到较差大致包括：system、const、eq_ref、ref、range、index、ALL。其中：
const：最多一行，通常按主键或唯一索引等值查找。eq_ref：连接中每次最多匹配一行，通常依赖主键或唯一非空索引。ref：使用索引的最左前缀做等值匹配，可能返回多行。range：用索引取一段范围，key_len表示使用的 key 部分。index：扫描整棵索引树。若Extra有Using index，可能是覆盖索引扫描；否则仍要按索引顺序回表读取行。ALL：全表扫描。
Extra 中最常混淆的三个信号：
| 信号 | 含义 | 是否回表 |
|---|---|---|
Using index | 只读索引树就能得到所需列，即覆盖索引 | 通常不需要 |
Using index condition | ICP：把部分条件下推到存储引擎，在索引条目上先过滤 | 仍可能需要回表取未覆盖列 |
Using where | 服务器层在取到行后再过滤 | 取决于前面的访问方式 |
因此：
Using index condition不是覆盖索引，它优化的是“少读一些完整行”，不是“完全不读行”。Using index不是“用到了索引”的同义词；用了索引但需要回表时，Extra可能没有它。type=index不是“精确索引查找”，而是整棵索引树扫描；它可能比ALL好，也可能仍然很慢。Using filesort表示需要额外排序，不表示一定写磁盘；结果集放不下内存时才会使用临时文件。Using temporary表示需要临时表保存中间结果，常见于GROUP BY与ORDER BY列不一致等场景。
对 InnoDB 还有一个细节：如果表有用户定义的聚簇索引，type=index 且 key=PRIMARY 时，即使 Extra 没有 Using index，也可能是在读聚簇索引本身。不要只凭一个字段下结论。
用实际迭代数检查估算
MySQL 8.0.18 引入 EXPLAIN ANALYZE。它会执行语句，输出固定为 TREE 格式，并在每个迭代器上给出：
actual time：以首行时间..总时间表示，单位毫秒；存在多次循环时，手册说明显示每次循环的平均值；rows：该迭代器返回的行数，结合loops判断总访问量；loops：迭代器执行的次数。
TREE 输出还保留优化器的估算 cost 和 rows，因此可以直接对照“估算 10 行，实际 10,000 行”这类偏差。读输出时先看 loops：内层迭代器循环很多次时，单次 actual time 很小也可能累积成主要成本；不能只扫一眼最外层的数字。
EXPLAIN ANALYZE 支持 SELECT、多表 UPDATE/DELETE，以及 8.0.19 起的 TABLE 语句；它不能与 FOR CONNECTION 一起使用。由于它会真正执行语句，分析写操作前要先确认事务和副作用。
例如某内表 rows=5, loops=100000，实际输出量约 50 万；若 actual time 是每循环平均值，不能只看单次 0.01ms 就认定不重要。各父子迭代器时间往往包含关系，不能把整棵树耗时相加作请求总耗时。测量还会改变缓存与工作负载，应保留冷/热、参数、版本和采样窗口。
深分页的消费循环与 keyset 的稳定边界由诊断正文统一解释。索引顺序消除排序，也消除不了 OFFSET 要丢弃的结果。
可运行输入：空范围与命中范围必须对照
实验入口：版本、命令与结果模板，原始 Q1–Q9 保留在 queries.sql。运行 bash labs/mysql/run.sh 会重建专用 study.orders，不要连接业务库。
原数据 user_id=n%10000、status=ELT(1+n%4,...) 强相关：1234 对应 SHIPPED，故 Q1/Q2 的 user_id=1234 AND status='PAID' 无匹配；1233 则全为 PAID。空范围不能证明命中时回表便宜。新增 Q10 明确输出这两组基数，Q11/Q12 在 1233 上对比整行与覆盖投影，Q13/Q14 反转 ICP 开关并在末尾恢复会话值。本机8.4.7实测1234+PAID为0行、1233+PAID为10行；Q11增加3月起时间过滤后实际9行，Q12未加该时间条件为10行。二者不是完全同谓词的计时对照，不能由9/10行或不同投影直接计算覆盖提速倍数。
| 查询组 | 输入与要验证的事实 | 不能据此断言 |
|---|---|---|
| Q1/Q2 与 Q10–Q12 | 空范围 vs 命中，传统 EXPLAIN 对照实际 rows/loops | 单次空读能代表线上 |
| Q3 与 Q13/Q14 | 前导通配在索引中可过滤，比较 ICP 开关 | 索引条件一定被优化器选中 |
| Q4/Q9 | 单列范围/状态范围及排序 | filesort 一定写磁盘 |
| Q5/Q6 | offset 90000 与 keyset id>90000 同页 | 热缓存耗时可以外推冷读 |
| Q7/Q8 | 覆盖计数、缺失中间前缀 | 有索引就低扫描 |
无需数据库的推演命令是 python3 labs/mysql/models.py index。它检查 100000 条确定性输入中的 0/10 行对照、offset 需消费 90020 个结果、keyset 只需消费 20 个后继；模型不实现优化器、物理页、MySQL 锁或时间测量。
本机真实计划（8.4.7，与源码标签分开）
公开入口 run_local.py --mode plans 在唯一新库运行同份init/queries并finally清理：Q5的PRIMARY扫描实际90020行、Q6的PRIMARY范围扫描实际20行，返回ID均90001–90020。Q2/Q12传统计划显示 Backward index scan; Using index，Q3为 Using index condition，Q8还出现 Using filesort；Q11 key_len=79、Q12 key_len=74反映谓词不同。Q13/Q14的ICP on/off分别把status检查放在索引条件与Server Filter，匹配结果均10行。
这是单次本机合成数据观察，不是基准；输出在 output/t16-local-plans.json，运行环境和清理方式见实验说明。8.0.39镜像仍未运行，不能把8.4.7计划当成源码tag构建的输出。
成本推演与条件变化
假设每秒 2 万次列表读、2000 次订单写，拟加 80 字节覆盖列到 1 亿条二级条目中，仅新增字段原始量约 8GB，尚未算页空隙和日志；副本也要承担维护成本。方案一加窄排序索引后按页取少量主键回表，方案二加入较多覆盖列减少读放大。前者更节省写与缓存，后者可能降低热点读取延迟。应以 P99、每请求扫描/回表数、写事务延迟、索引尺寸和复制积压做同窗对比，不能只报告 QPS 提升。
若需求改为按任意字段排序且可跳第 1 万页，单个联合索引无法覆盖所有排列。可限制交互分页深度、提供异步导出，或建立搜索投影；后者又引入刷新可见性和重建责任。若租户分布改为一个租户占 60%，均匀造数的计划不再可信，要加入热租户参数，验证统计与缓存能否反映热点。三轮追问依次要求解释树结构、定位具体执行分支、量化写放大与条件变化；每轮机制与证据各 1 分，答出“加索引”而没有维护成本不能通过架构师验收。
主答“为什么加索引可能更慢”：每次写要维护更多条目、更宽页降低缓存效率；读节省是否超过写/日志/副本成本，要在相同负载与观察窗口比较。先补窄索引还是加宽覆盖，应保留回滚开关与写 P99，不能只报读均值。
三轮追问：从树结构到列表设计
第一轮，为什么不是哈希表？哈希适合等值，有序叶链支持的范围、排序和前缀不是哈希直接提供的；B+树高扇出换较少层级，代价是分裂/合并和扫描多条候选。自适应哈希只是热点定位优化，不替代这份持久有序索引。
第二轮，完美索引为何不选？比较选择性、回表量、覆盖、排序与统计偏差；低选择性时全表扫描可能胜过大量随机回表。用慢/热参数对照EXPLAIN ANALYZE与ANALYZE TABLE，不凭索引名称强制生产。
第三轮，审核待办按status+queue_id等值、created_at/id降序，候选可为 (status,queue_id,created_at,id) 或 (queue_id,status,created_at,id)。完整双等值查询差别有限，顺序还取决于只按其中一列的其他查询；用完整游标边界续页，状态汇总另评估窄索引或汇总表。记录真实扫描、写P99及空间，没有数据就标待采集。
技术案例与取舍
以下场景用于分析技术选型与故障边界，可使用实验数据验证，无需填写个人项目经历。
- 审核：待审列表常按
status + queue + created_at过滤和排序。先收集真实 SQL、调用频率和返回行数，再设计联合索引；用 keyset 替代深 offset；核对回表比例、扫描行数和写入放大。证据可以是EXPLAIN ANALYZE原始输出、slow log 和接口 P95/P99。 - 积分：按用户查流水通常以
user_id为最左前缀，再按时间/流水号排序；分页要有稳定 tie-breaker，避免同一时间戳下重复或漏行。余额和流水的事实源仍是数据库事务与唯一约束，索引只解决访问路径，不解决账务一致性。 - 消息：重试扫描常按
status + next_retry_at过滤。低选择性的status单独建索引未必有效，联合索引和批量领取策略要一起评估；领取的并发正确性依赖条件更新或FOR UPDATE SKIP LOCKED等机制，不能只靠索引。 - 内容中心：频道页常按
channel + publish_status + publish_time查询。覆盖索引可以减少回表，但宽正文不要放进二级索引；深分页同样优先 keyset。搜索类需求如果交给 Elasticsearch，要明确 DB 是事实源、ES 是派生索引，并记录同步/回填/对账责任。
追问一：能不能把余额锁也放在这个索引里？索引决定加锁访问集合，不能把访问优化等同原子扣减；条件写与多行协议见MVCC与锁。追问二：要求搜索任意词而非前缀？要比较全文/检索投影、刷新滞后与重建责任，而不是不断堆 B+ 树。
闭卷验收
本页 8 道闭卷题收进了下方自测面板：先回忆，再展开参考答案与评分点，最后给三档自评。
来源与版本
| 来源 | 版本 | 用途 |
|---|---|---|
| MySQL 8.0 Reference Manual · Clustered and Secondary Indexes | MySQL 8.0 | 聚簇索引、二级索引叶子内容、主键回退、GEN_CLUST_INDEX 与 6 字节 row ID |
| MySQL 8.0 Reference Manual · Physical Structure of an InnoDB Index | MySQL 8.0 | B-tree、16KB 默认页、顺序/随机插入填充率、页合并 |
| MySQL 8.0 Reference Manual · Multiple-Column Indexes | MySQL 8.0 | 最左前缀、联合索引的有序数组模型、索引列数上限 |
| MySQL 8.0 Reference Manual · Index Condition Pushdown Optimization | MySQL 8.0 | ICP 适用访问方式、InnoDB 仅二级索引、限制条件 |
| MySQL 8.0 Reference Manual · EXPLAIN Statement | MySQL 8.0 | EXPLAIN ANALYZE 8.0.18、执行语句、TREE、actual time/rows/loops |
| MySQL 8.0 Reference Manual · EXPLAIN Output Format | MySQL 8.0 | type、key_len、rows、filtered、Extra 语义 |
| MySQL 8.0 Reference Manual · ORDER BY Optimization | MySQL 8.0 | 索引满足排序的条件、filesort、升降模式 |
| MySQL 8.0 Reference Manual · LIMIT Query Optimization | MySQL 8.0 | LIMIT 提前终止、排序提前结束；深分页成本为本文推导，需结合执行计划验证 |
| MySQL 8.0 Reference Manual · Index Page Merge Threshold | MySQL 8.0 | MERGE_THRESHOLD 默认 50% |
labs/mysql | mysql:8.0.39 | 固定8.0.39镜像协议、8.4.7本机实测与独立模型，运行版本分别记录 |
访问日期：2026-10-23。实验镜像固定为 mysql:8.0.39；手册为 8.0 系列滚动文档，若使用其他 8.0.x 版本，请以对应版本文档和实际执行计划复核。本机8.4.7实测仅可按其版本、数据与配置引用，不能外推为8.0.39或生产性能结论。