本文摘要联合索引满足左前缀却走全表扫描慢查询排查经常被误判为索引失效。以 MySQL 8.0 成本模型与 EXPLAIN 输出为依据区分优化器正确决策和统计信息过期两类根因。一、问题与结论MySQL 8.0.28 环境orders表建有联合索引(status, region, category)。执行这条查询EXPLAINSELECT*FROMordersWHEREstatuscompletedANDregionasia;EXPLAIN 输出中type ALL、key NULL但possible_keys列出了idx_status_region_category。条件完全符合左前缀索引也没有被删除——到底哪里出了问题结论优化器做出了正确判断这不是索引失效。当status和region两列的组合能匹配表中 70% 以上的行时二级索引回表的随机 I/O 累积成本会超过顺序全表扫描MySQL 8.0 放弃索引是成本模型的正常行为。正确的排查方向是确认数据分布是否已导致成本翻转再决定接受全表扫描、改写查询条件、还是强推索引。二、排查与选择依据看到type ALL且possible_keys有值时说明优化器看到了索引但经过成本计算后放弃了。按以下顺序排查确认索引在possible_keys中。有值 → 成本判断放弃了无值 → 索引不满足左前缀或列类型不匹配见第三节。EXPLAIN FORMATJSON查看cost_info。JSON 输出会展示优化器对每条访问路径的估算成本可以直接对比索引扫描与全表扫描的数字大小确认谁的成本更低。MySQL 8.0.18 用EXPLAIN ANALYZE。它返回实际执行的行数、循环次数和耗时用于验证成本估算是否偏离实际。检查统计信息。批量写入数据后若未执行ANALYZE TABLEInnoDB 统计信息可能停留在旧分布上优化器会基于过期数据做决策。先刷新统计信息再重新 EXPLAIN。替代方案与取舍方案选择条件代价不该用的场景接受全表扫描结果集占表 30%查询不频繁大表上单次查询耗时较高实时接口要求低延迟追加过滤条件业务层可增加时间范围、金额等限定需评估语义变化可能改变结果集查询本身就是全量导出或报表FORCE INDEX紧急止血数据分布短期内不会恶化绕过优化器分布继续变化后可能比全表扫描更慢长期使用覆盖索引查询只需索引中已有的列写入时维护额外索引增加 INSERT/UPDATE 开销需要SELECT *的场景如果查询结果用于分页或分批处理可以先通过覆盖索引取出主键列表再按主键批量取数据避免大结果集回表。三、关键原理左前缀匹配是必要条件不是充分条件MySQL 多列索引遵循左前缀规则条件必须匹配从最左列开始的连续列。WHERE status ? AND region ?命中(status, region, category)的前两列左前缀条件已满足。但满足左前缀后优化器会进行基于成本的决策比较两条路径二级索引扫描读取二级索引页找到匹配条目再对每个条目回表随机 I/O 读聚簇索引页。成本 索引页读取 匹配行数 × 回表随机 I/O。全表扫描顺序读取聚簇索引全部叶子页。成本 ≈ 表大小顺序 I/O单次读取成本低。InnoDB 中二级索引的叶子节点存储的是主键值不是完整行。使用二级索引时必须回表除非查询只需要索引中已有的列即覆盖索引。当statusregion组合匹配 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.28InnoDB。建表并插入 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))ENGINEInnoDB;INSERTINTOorders(status,region,category,amount)WITHRECURSIVE seqAS(SELECT1ASnUNIONALLSELECTn1FROMseqWHEREn1000000)SELECTCASEWHENn%100THENpendingELSEcompletedEND,CASEWHENn%50THENeuropeELSEasiaEND,CONCAT(cat_,LPAD(n%100,2,0)),ROUND(RAND(n)*10000,2)FROMseq;ANALYZETABLEorders;数据分布90% 的行status completed其中 80% 为region asia——两列组合匹配约 72% 的行。查询 1仅命中前两列。EXPLAINSELECT*FROMordersWHEREstatuscompletedANDregionasia;预期输出关键字段type: ALL possible_keys: idx_status_region_category key: NULL rows: ~720000 Extra: Using where查询 2加上第三列category。EXPLAINSELECT*FROMordersWHEREstatuscompletedANDregionasiaANDcategorycat_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 TABLEInnoDB 统计信息停留在旧分布上优化器仍选择索引扫描实际执行反而更慢。修复方法写入后刷新统计信息再重新 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)
本文摘要联合索引满足左前缀却走全表扫描慢查询排查经常被误判为索引失效。以 MySQL 8.0 成本模型与 EXPLAIN 输出为依据区分优化器正确决策和统计信息过期两类根因。一、问题与结论MySQL 8.0.28 环境orders表建有联合索引(status, region, category)。执行这条查询EXPLAINSELECT*FROMordersWHEREstatuscompletedANDregionasia;EXPLAIN 输出中type ALL、key NULL但possible_keys列出了idx_status_region_category。条件完全符合左前缀索引也没有被删除——到底哪里出了问题结论优化器做出了正确判断这不是索引失效。当status和region两列的组合能匹配表中 70% 以上的行时二级索引回表的随机 I/O 累积成本会超过顺序全表扫描MySQL 8.0 放弃索引是成本模型的正常行为。正确的排查方向是确认数据分布是否已导致成本翻转再决定接受全表扫描、改写查询条件、还是强推索引。二、排查与选择依据看到type ALL且possible_keys有值时说明优化器看到了索引但经过成本计算后放弃了。按以下顺序排查确认索引在possible_keys中。有值 → 成本判断放弃了无值 → 索引不满足左前缀或列类型不匹配见第三节。EXPLAIN FORMATJSON查看cost_info。JSON 输出会展示优化器对每条访问路径的估算成本可以直接对比索引扫描与全表扫描的数字大小确认谁的成本更低。MySQL 8.0.18 用EXPLAIN ANALYZE。它返回实际执行的行数、循环次数和耗时用于验证成本估算是否偏离实际。检查统计信息。批量写入数据后若未执行ANALYZE TABLEInnoDB 统计信息可能停留在旧分布上优化器会基于过期数据做决策。先刷新统计信息再重新 EXPLAIN。替代方案与取舍方案选择条件代价不该用的场景接受全表扫描结果集占表 30%查询不频繁大表上单次查询耗时较高实时接口要求低延迟追加过滤条件业务层可增加时间范围、金额等限定需评估语义变化可能改变结果集查询本身就是全量导出或报表FORCE INDEX紧急止血数据分布短期内不会恶化绕过优化器分布继续变化后可能比全表扫描更慢长期使用覆盖索引查询只需索引中已有的列写入时维护额外索引增加 INSERT/UPDATE 开销需要SELECT *的场景如果查询结果用于分页或分批处理可以先通过覆盖索引取出主键列表再按主键批量取数据避免大结果集回表。三、关键原理左前缀匹配是必要条件不是充分条件MySQL 多列索引遵循左前缀规则条件必须匹配从最左列开始的连续列。WHERE status ? AND region ?命中(status, region, category)的前两列左前缀条件已满足。但满足左前缀后优化器会进行基于成本的决策比较两条路径二级索引扫描读取二级索引页找到匹配条目再对每个条目回表随机 I/O 读聚簇索引页。成本 索引页读取 匹配行数 × 回表随机 I/O。全表扫描顺序读取聚簇索引全部叶子页。成本 ≈ 表大小顺序 I/O单次读取成本低。InnoDB 中二级索引的叶子节点存储的是主键值不是完整行。使用二级索引时必须回表除非查询只需要索引中已有的列即覆盖索引。当statusregion组合匹配 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.28InnoDB。建表并插入 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))ENGINEInnoDB;INSERTINTOorders(status,region,category,amount)WITHRECURSIVE seqAS(SELECT1ASnUNIONALLSELECTn1FROMseqWHEREn1000000)SELECTCASEWHENn%100THENpendingELSEcompletedEND,CASEWHENn%50THENeuropeELSEasiaEND,CONCAT(cat_,LPAD(n%100,2,0)),ROUND(RAND(n)*10000,2)FROMseq;ANALYZETABLEorders;数据分布90% 的行status completed其中 80% 为region asia——两列组合匹配约 72% 的行。查询 1仅命中前两列。EXPLAINSELECT*FROMordersWHEREstatuscompletedANDregionasia;预期输出关键字段type: ALL possible_keys: idx_status_region_category key: NULL rows: ~720000 Extra: Using where查询 2加上第三列category。EXPLAINSELECT*FROMordersWHEREstatuscompletedANDregionasiaANDcategorycat_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 TABLEInnoDB 统计信息停留在旧分布上优化器仍选择索引扫描实际执行反而更慢。修复方法写入后刷新统计信息再重新 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)
平方复杂度无法被常规优化消除｜TaoToken 统一 Key 通道实测)
