本文摘要：线上订单加联合索引后从 200ms 变 8 秒，索引命中却更慢。根因是 MySQL 8.0 偏好免排序索引，低选择性前缀致回表暴涨。仅适用于非覆盖索引场景，统计信息过期需先刷新再判断。
一、问题与结论
环境：Docker 启动的 MySQL 8.0（InnoDB），Spring Boot 3.x 订单服务通过JdbcTemplate调同一条查询。表orders约 50 万行，status='shipped'占 15%，user_id范围 1~10000，初始只有idx_user_id。
SELECT*FROMordersWHEREstatus='shipped'ANDuser_id=42ORDERBYcreated_atDESCLIMIT20;上线前该查询约 200ms；为消除排序，有人加了idx_status_created (status, created_at)，同一查询涨到 8s。
结论：MySQL 8.0 默认prefer_ordering_index=on，优化器看到联合索引能省掉 filesort 就选了它，用status='shipped'（约 7.5 万行）作前缀扫描，每条都回表取user_id再过滤，回表次数从约 50 次涨到数万次。索引确实命中，只是命中了错的那个——这不是索引没建，而是优化器的取舍在低选择性前缀下翻车。
二、排查与选择依据
先看优化器选了什么：
EXPLAINFORMAT=JSONSELECT*FROMordersWHEREstatus='shipped'ANDuser_id=42ORDERBYcreated_atDESCLIMIT20;三个判据依次确认：
key从idx_user_id变成idx_status_created，Extra却没有Using filesort；FORMAT=JSON的cost_info里query_cost更低，但rows_examined_per_scan上万——代价模型按统计采样估算索引扫描，低估了非覆盖索引的回表随机 I/O（回表是乘法不是加法）；EXPLAIN ANALYZE（8.0.18+）的 actual rows 远大于估算，直接暴露真实扫描量。
若换索引后立刻出现，且SHOW INDEX FROM orders显示新索引cardinality为 0 或异常，则是统计信息未刷新导致的临时错选，先执行ANALYZE TABLE orders;再判断。
替代方案与取舍
| 方案 | 选择条件 | 代价 | 边界（什么时候不该用） |
|---|---|---|---|
重建联合索引(user_id, status, created_at)（本文推荐） | 该索引主要服务本查询 | 多一个索引的写入开销，需删旧索引 | 其他查询依赖idx_status_created免排序时，删改影响面大，不该直接动索引 |
IGNORE INDEX (idx_status_created) | 线上需快速止血 | SQL 与索引名强耦合，索引改名即失效 | 只适合临时，数据分布变化后不会自动换回 |
/*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ | 单条语句修正、不改表结构 | 该语句必然走 filesort | 排序数据量本身很大时，filesort 才是真瓶颈，不该用 |
不建议FORCE INDEX：它会阻止优化器在数据分布变化后自动切换，比IGNORE INDEX更危险。
三、关键原理
三个事实串起根因：
- 免排序偏好：
prefer_ordering_index启用时，只要某索引能满足 ORDER BY，优化器就优先选它，哪怕前缀列过滤选择性很差。这是设计取舍，不是 bug。 - 回表代价被低估：非覆盖索引每条匹配都要一次二级索引到聚簇索引的随机 I/O，扫描条目数与回表次数是乘法关系；前缀选择性越低，估算偏差越被放大。
- 统计信息延迟：
innodb_stats_auto_recalc默认开启但采样有延迟，新增索引后立即查询可能拿着过期统计做决策。
四、可运行示例
环境：Docker + MySQL 8.0，需 8.0.18+ 才有EXPLAIN ANALYZE。
dockerrun-d--namemysql8-p3306:3306\-eMYSQL_ROOT_PASSWORD=test-eMYSQL_DATABASE=demo mysql:8.0建表与造数（20 万行，completed约 80%、shipped约 15%、pending约 5%）。用n DIV 10000与n % 10000生成互不相关的状态和用户，避免纯取模产生固定关联：
USEdemo;DROPTABLEIFEXISTSorders;CREATETABLEorders(idBIGINTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,user_idBIGINTUNSIGNEDNOTNULL,statusVARCHAR(20)NOTNULL,amountDECIMAL(10,2)NOTNULL,created_atDATETIMENOTNULL,KEYidx_user_id(user_id))ENGINE=InnoDB;SETSESSIONcte_max_recursion_depth=200001;INSERTINTOorders(user_id,status,amount,created_at)WITHRECURSIVE seqAS(SELECT1nUNIONALLSELECTn+1FROMseqWHEREn<200000)SELECT1+n%10000,CASEWHEN(nDIV10000)%20<16THEN'completed'WHEN(nDIV10000)%20<19THEN'shipped'ELSE'pending'END,10+n%990,'2024-01-01'+INTERVAL(n*157)SECONDFROMseq;ANALYZETABLEorders;操作步骤：
-- 步骤 1：只有 idx_user_idEXPLAINANALYZESELECT*FROMordersWHEREstatus='shipped'ANDuser_id=42ORDERBYcreated_atDESCLIMIT20;-- 步骤 2：加联合索引并刷新统计ALTERTABLEordersADDINDEXidx_status_created(status,created_at);ANALYZETABLEorders;-- 步骤 3：重复同一查询EXPLAINANALYZESELECT*FROMordersWHEREstatus='shipped'ANDuser_id=42ORDERBYcreated_atDESCLIMIT20;预期输出：步骤 1 选中idx_user_id，回表约 20 次（user_id=42 恰好 20 行）；步骤 3 的key变为idx_status_created、Extra无Using filesort，actual rows 升到 3 万量级（约 15% 的行需回表过滤），耗时明显上涨。这是依据官方文档描述的推断，绝对毫秒数未验证，取决于缓冲池与机器。
实际输出：本文示例未在本机执行，需在你的 Docker 环境运行后回填；回填时重点记录EXPLAIN ANALYZE的 actual rows 与耗时，并与IGNORE INDEX (idx_status_created)的结果对照。
常见失败：加完索引立刻 EXPLAIN，key没变化或仍走旧索引。原因是新索引统计信息未生成（SHOW INDEX的cardinality为 0）。修复：执行ANALYZE TABLE orders;后重新 EXPLAIN。
五、验证结果与边界
- 仅对非覆盖索引成立；索引包含
user_id变成覆盖索引后无回表，差距会大幅缩小。 - 前缀列选择性必须低（匹配 15%~80% 的行）才明显；数据量小或缓冲池足够大时，差距可能不到一秒。
- MySQL 5.7 没有
prefer_ordering_index，从 5.7 升 8.0 后同一查询计划可能改变，需重新验证；8.0.21 调整过代价模型，小版本升级后同样要复测。 SET_VAR只影响当前语句；session 级关闭开关会影响该连接所有带 ORDER BY 的查询，只适合排查阶段，不要长期保留。
参考资料
- MySQL 8.0 Switchable Optimizations（prefer_ordering_index 默认值）
- MySQL 8.0 Index Hints（USE/FORCE/IGNORE INDEX 语法）
- MySQL 8.0 Optimizer Hints（SET_VAR 单语句开关）
- MySQL 8.0 EXPLAIN 与 EXPLAIN ANALYZE 输出说明
- MySQL 8.0 ANALYZE TABLE（索引统计刷新）
