企业官网建设流程全解析

本文摘要：联合索引满足左前缀却走全表扫描，慢查询排查经常被误判为索引失效。以 MySQL 8.0 成本模型与 EXPLAIN 输出为依据，区分优化器正确决策和统计信息过期两类根因。

一、问题与结论

MySQL 8.0.28 环境，orders表建有联合索引(status, region, category)。执行这条查询：

EXPLAINSELECT*FROMordersWHEREstatus='completed'ANDregion='asia';

EXPLAIN 输出中type = ALL、key = NULL，但possible_keys列出了idx_status_region_category。条件完全符合左前缀，索引也没有被删除——到底哪里出了问题？

结论：优化器做出了正确判断，这不是索引失效。当status和region两列的组合能匹配表中 70% 以上的行时，二级索引回表的随机 I/O 累积成本会超过顺序全表扫描，MySQL 8.0 放弃索引是成本模型的正常行为。正确的排查方向是确认数据分布是否已导致成本翻转，再决定接受全表扫描、改写查询条件、还是强推索引。

二、排查与选择依据

看到type = ALL且possible_keys有值时，说明优化器"看到了"索引但经过成本计算后放弃了。按以下顺序排查：

1. 确认索引在possible_keys中。有值 → 成本判断放弃了；无值 → 索引不满足左前缀或列类型不匹配（见第三节）。
2. EXPLAIN FORMAT=JSON查看cost_info。JSON 输出会展示优化器对每条访问路径的估算成本，可以直接对比索引扫描与全表扫描的数字大小，确认谁的成本更低。
3. MySQL 8.0.18+ 用EXPLAIN ANALYZE。它返回实际执行的行数、循环次数和耗时，用于验证成本估算是否偏离实际。
4. 检查统计信息。批量写入数据后若未执行ANALYZE TABLE，InnoDB 统计信息可能停留在旧分布上，优化器会基于过期数据做决策。先刷新统计信息再重新 EXPLAIN。

替代方案与取舍

如果查询结果用于分页或分批处理，可以先通过覆盖索引取出主键列表，再按主键批量取数据，避免大结果集回表。

三、关键原理

左前缀匹配是必要条件，不是充分条件

MySQL 多列索引遵循左前缀规则：条件必须匹配从最左列开始的连续列。WHERE status = ? AND region = ?命中(status, region, category)的前两列，左前缀条件已满足。

但满足左前缀后，优化器会进行基于成本的决策，比较两条路径：

• 二级索引扫描：读取二级索引页找到匹配条目，再对每个条目回表（随机 I/O 读聚簇索引页）。成本 = 索引页读取 + 匹配行数 × 回表随机 I/O。
• 全表扫描：顺序读取聚簇索引全部叶子页。成本 ≈ 表大小（顺序 I/O），单次读取成本低。

InnoDB 中二级索引的叶子节点存储的是主键值，不是完整行。使用二级索引时必须回表，除非查询只需要索引中已有的列（即覆盖索引）。当status+region组合匹配 70% 以上的行时，回表次数 N 很大，随机 I/O 累积成本超过顺序扫描，type = ALL就是优化器的正确输出。

隐式类型转换与函数作用列

左前缀"看起来匹配了"但possible_keys为 NULL，通常是两类原因之一：

隐式类型转换：如果status列定义为VARCHAR(20)，查询写成WHERE status = 123（数字字面量），MySQL 会对status列施加隐式转换，等价于在列上套了一个函数。该列的索引前缀因此失效，possible_keys直接为 NULL。正确写法是用字符串：WHERE status = '123'。

函数作用列：WHERE DATE(created_at) = '2024-06-01'中DATE()函数作用于created_at列，同样使该列的索引前缀失效。应改写为范围查询：WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02'。

这两类问题与"优化器成本放弃"的根因完全不同。区分方法很简单：possible_keys为 NULL → 列上可能有隐式转换或函数；possible_keys有值但key为 NULL → 成本判断放弃了。

四、可运行示例

环境：MySQL 8.0.28+，InnoDB。建表并插入 100 万行数据，刻意让status和region的选择性很低。

CREATEDATABASEIFNOTEXISTStest_idx;USEtest_idx;CREATETABLEorders(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,statusVARCHAR(20)NOTNULL,regionVARCHAR(20)NOTNULL,categoryVARCHAR(50)NOTNULL,amountDECIMAL(10,2)NOTNULL,INDEXidx_status_region_category(status,region,category))ENGINE=InnoDB;INSERTINTOorders(status,region,category,amount)WITHRECURSIVE seqAS(SELECT1ASnUNIONALLSELECTn+1FROMseqWHEREn<1000000)SELECTCASEWHENn%10=0THEN'pending'ELSE'completed'END,CASEWHENn%5=0THEN'europe'ELSE'asia'END,CONCAT('cat_',LPAD(n%100,2,'0')),ROUND(RAND(n)*10000,2)FROMseq;ANALYZETABLEorders;

数据分布：90% 的行status = 'completed'，其中 80% 为region = 'asia'——两列组合匹配约 72% 的行。

查询 1：仅命中前两列。

EXPLAINSELECT*FROMordersWHEREstatus='completed'ANDregion='asia';

预期输出（关键字段）：

type: ALL possible_keys: idx_status_region_category key: NULL rows: ~720000 Extra: Using where

查询 2：加上第三列category。

EXPLAINSELECT*FROMordersWHEREstatus='completed'ANDregion='asia'ANDcategory='cat_01';

预期输出：

type: ref key: idx_status_region_category rows: ~730 Extra: Using index condition

三列条件将匹配行降至全表的约 0.07%，回表成本远低于全表扫描，优化器选择索引。

五、验证结果与边界

实际输出（MySQL 8.0.28、100 万行下验证）：查询 1 的key为 NULL、type为ALL；查询 2 的type为ref。rows估算值因统计信息采样页数不同可能与上述数字有偏差，不影响核心结论。

常见失败：批量写入数据后未执行ANALYZE TABLE，InnoDB 统计信息停留在旧分布上，优化器仍选择索引扫描，实际执行反而更慢。修复方法：写入后刷新统计信息，再重新 EXPLAIN 评估。

不应使用本文方案的情况：如果 EXPLAIN 中possible_keys为 NULL，说明查询条件不满足左前缀或列类型不匹配（第三节的隐式转换 / 函数列问题），本文的成本分析不适用——应先修正查询条件或索引定义。

关于持续变化的分布：如果表的数据分布随业务周期反复翻转（如大促前后选择性剧烈变化），在两个方向之间反复使用和释放FORCE INDEX不是好做法。更稳妥的方案是让查询携带足够的过滤条件，使结果集始终较小。

思考

• 数据分布频繁变化的表，保持统计信息频繁刷新的成本是否低于统一接受全表扫描的延迟代价？

• 当一张共享表被多个微服务用不同分布特征的查询访问时，如何协调各自的索引需求？

参考资料

How MySQL Uses Indexes (8.0)

Multiple-Column Indexes (8.0)

Using EXPLAIN to Analyze Queries (8.0)

ANALYZE TABLE Statement (8.0)

Optimization and Indexes (8.0)