本文摘要加了联合索引反而更慢EXPLAIN 显示 key 命中也难提速。拆解最左前缀断裂、隐式转换与非覆盖回表三类失效并给出可运行示例。排查适用于大表低选择性场景小表及高选择性索引无需此流程。一、问题与结论订单表status字段建了联合索引idx_status_created (status, created_at)后WHERE status 1 AND created_at 2024-01-01的查询耗时不降反升而EXPLAIN明确显示key idx_status_created。key有值只代表优化器选择了索引不代表执行效率更高。下文拆解三类失效场景每类给出扫描行数与回表特征并补充索引写放大这一第四种变慢可能。二、排查与选择依据排查顺序先用EXPLAIN ANALYZEMySQL 8.0 新增观察实际扫描行数与耗时再用SHOW SESSION STATUS LIKE Handler_read%对比Handler_read_key回表点查与Handler_read_next索引扫描的次数量化回表占比最后对照下表判断失效类型。失效类型EXPLAIN 特征根因最左前缀断裂key为 NULL 或仅用于排序WHERE 缺索引最左列隐式类型转换key为 NULL 或仅用于排序列类型与查询值不匹配触发逐行 CAST回表代价过高key命中且rows接近总行数非覆盖索引 低选择性替代方案与取舍方案选择条件代价与边界扩展为覆盖索引SELECT 列固定且少索引变宽写放大加剧删除该索引该索引从未正确过滤过丢失ORDER BY消除 filesort 的收益FORCE INDEX临时验证阶段数据分布变化后计划可能反向错误不该用本文排查流程的场景高选择性索引命中行数占比不足 5%回表代价远低于全表扫描不足万行的小表三类失效差异不显著无排查必要。三、关键原理回表InnoDB 聚簇索引叶子节点存完整行二级索引叶子只存索引列 主键。查询列不在索引中时每命中一条记录都需用主键再访聚簇索引——一次随机 I/O。status 1占 75% 时约 37.5 万次随机回表总 I/O 可能超过全表顺序扫描。最左前缀断裂WHERE user_id 1000001不含status索引(status, created_at)无法过滤。若查询含ORDER BY created_at DESC优化器可能仍选此索引仅用于排序每行照常回表实际扫描行数等于总行数。隐式类型转换status为VARCHAR(10)查询写WHERE status 1数字时MySQL 逐行将字符串列 CAST 为数值再比较B 树无法直接定位退化为全表扫描。反向场景——列为TINYINT写 1——MySQL 将字符串字面量转为数字索引正常方向取决于哪一侧是字符串。四、可运行示例环境MySQL 8.0InnoDB 引擎先执行SELECT VERSION()确认版本号。CREATETABLEorders(idBIGINTAUTO_INCREMENTPRIMARYKEY,user_idBIGINTNOTNULL,-- status 用 VARCHAR 演示隐式类型转换0待支付, 1已支付, 2已完成statusVARCHAR(10)NOTNULLDEFAULT0,amountDECIMAL(10,2)NOTNULL,created_atDATETIMENOTNULL,INDEXidx_status_created(status,created_at))ENGINEInnoDB;SETSESSIONcte_max_recursion_depth1000000;CREATETABLEseq_gen(nINT);INSERTINTOseq_gen(n)WITHRECURSIVE c(n)AS(SELECT1UNIONALLSELECTn1FROMcWHEREn500000)SELECTnFROMc;INSERTINTOorders(user_id,status,amount,created_at)SELECT1000000(n%90000),CASEWHENn375000THEN1WHENn475000THEN0ELSE2END,ROUND(RAND(n)*1000,2),DATE_ADD(2024-01-01,INTERVAL(n%365)DAY)FROMseq_gen;ANALYZETABLEorders;DROPTABLEseq_gen;失效 1 — 回表代价EXPLAINANALYZESELECTid,user_id,amount,created_atFROMordersWHEREstatus1ANDcreated_at2024-01-01;预期输出key idx_status_created扫描行数约 37.5 万75% 命中user_id与amount不在索引中需逐行回表。实际输出需本地执行后填入真实计划树与耗时本文不预填未验证数据。失效 2 — 最左前缀断裂EXPLAINANALYZESELECTid,amountFROMordersWHEREuser_id1000001ORDERBYcreated_atDESC;预期输出key为 NULL 或idx_status_created仅为排序扫描行数接近 50 万而user_id 1000001实际仅匹配约 6 行。实际输出需本地执行后填入本文不预填未验证数据。失效 3 — 隐式类型转换EXPLAINANALYZESELECTidFROMordersWHEREstatus1;预期输出key NULL全表扫描因为数字1触发对VARCHAR列的逐行 CAST索引无法定位。实际输出需本地执行后填入本文不预填未验证数据。常见失败递归 CTE 生成 50 万行时被cte_max_recursion_depth默认 1000截断报Recursive CTE aborted。修复先执行上方 SQL 中的SET SESSION cte_max_recursion_depth 1000000;后重试。五、验证结果与边界已验证基于 MySQL 8.0 官方文档的机制描述非覆盖索引回表为随机 I/O低选择性下可超过全表顺序扫描最左列缺失时索引无法过滤列类型与查询值不匹配时触发 CAST 破坏索引定位。未验证本文所有EXPLAIN ANALYZE行数、耗时与计数器数值均为预期值需本地实测后填入真实数据。索引写放大反例idx_status_created让每次INSERT/UPDATE多维护一棵 B 树。若该索引仅服务于低频统计查询写吞吐下降的代价可能超过读收益——这不属于三类失效但属于加索引反而变慢的第四种可能需压测确认。适用边界大表≥10 万行 低选择性字段才需要按本流程排查小表、高选择性或已覆盖索引场景直接跳过。思考低选择性联合索引保留用于ORDER BY消除 filesort与删除索引让优化器回归全表扫描哪个收益更大扩展索引为覆盖时新列是否可能引入高写入表的写放大瓶颈参考资料MySQL 8.0 Reference Manual — How MySQL Uses IndexesMySQL 8.0 Reference Manual — EXPLAIN Output FormatMySQL 8.0 Reference Manual — How InnoDB Chooses an Index
