本文摘要线上订单加联合索引后从 200ms 变 8 秒索引命中却更慢。根因是 MySQL 8.0 偏好免排序索引低选择性前缀致回表暴涨。仅适用于非覆盖索引场景统计信息过期需先刷新再判断。一、问题与结论环境Docker 启动的 MySQL 8.0InnoDBSpring Boot 3.x 订单服务通过JdbcTemplate调同一条查询。表orders约 50 万行statusshipped占 15%user_id范围 1~10000初始只有idx_user_id。SELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;上线前该查询约 200ms为消除排序有人加了idx_status_created (status, created_at)同一查询涨到 8s。结论MySQL 8.0 默认prefer_ordering_indexon优化器看到联合索引能省掉 filesort 就选了它用statusshipped约 7.5 万行作前缀扫描每条都回表取user_id再过滤回表次数从约 50 次涨到数万次。索引确实命中只是命中了错的那个——这不是索引没建而是优化器的取舍在低选择性前缀下翻车。二、排查与选择依据先看优化器选了什么EXPLAINFORMATJSONSELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;三个判据依次确认key从idx_user_id变成idx_status_createdExtra却没有Using filesortFORMATJSON的cost_info里query_cost更低但rows_examined_per_scan上万——代价模型按统计采样估算索引扫描低估了非覆盖索引的回表随机 I/O回表是乘法不是加法EXPLAIN ANALYZE8.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_switchprefer_ordering_indexoff) */单条语句修正、不改表结构该语句必然走 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_PASSWORDtest-eMYSQL_DATABASEdemo 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))ENGINEInnoDB;SETSESSIONcte_max_recursion_depth200001;INSERTINTOorders(user_id,status,amount,created_at)WITHRECURSIVE seqAS(SELECT1nUNIONALLSELECTn1FROMseqWHEREn200000)SELECT1n%10000,CASEWHEN(nDIV10000)%2016THENcompletedWHEN(nDIV10000)%2019THENshippedELSEpendingEND,10n%990,2024-01-01INTERVAL(n*157)SECONDFROMseq;ANALYZETABLEorders;操作步骤-- 步骤 1只有 idx_user_idEXPLAINANALYZESELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;-- 步骤 2加联合索引并刷新统计ALTERTABLEordersADDINDEXidx_status_created(status,created_at);ANALYZETABLEorders;-- 步骤 3重复同一查询EXPLAINANALYZESELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;预期输出步骤 1 选中idx_user_id回表约 20 次user_id42 恰好 20 行步骤 3 的key变为idx_status_created、Extra无Using filesortactual rows 升到 3 万量级约 15% 的行需回表过滤耗时明显上涨。这是依据官方文档描述的推断绝对毫秒数未验证取决于缓冲池与机器。实际输出本文示例未在本机执行需在你的 Docker 环境运行后回填回填时重点记录EXPLAIN ANALYZE的 actual rows 与耗时并与IGNORE INDEX (idx_status_created)的结果对照。常见失败加完索引立刻 EXPLAINkey没变化或仍走旧索引。原因是新索引统计信息未生成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 Optimizationsprefer_ordering_index 默认值MySQL 8.0 Index HintsUSE/FORCE/IGNORE INDEX 语法MySQL 8.0 Optimizer HintsSET_VAR 单语句开关MySQL 8.0 EXPLAIN 与 EXPLAIN ANALYZE 输出说明MySQL 8.0 ANALYZE TABLE索引统计刷新
本文摘要线上订单加联合索引后从 200ms 变 8 秒索引命中却更慢。根因是 MySQL 8.0 偏好免排序索引低选择性前缀致回表暴涨。仅适用于非覆盖索引场景统计信息过期需先刷新再判断。一、问题与结论环境Docker 启动的 MySQL 8.0InnoDBSpring Boot 3.x 订单服务通过JdbcTemplate调同一条查询。表orders约 50 万行statusshipped占 15%user_id范围 1~10000初始只有idx_user_id。SELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;上线前该查询约 200ms为消除排序有人加了idx_status_created (status, created_at)同一查询涨到 8s。结论MySQL 8.0 默认prefer_ordering_indexon优化器看到联合索引能省掉 filesort 就选了它用statusshipped约 7.5 万行作前缀扫描每条都回表取user_id再过滤回表次数从约 50 次涨到数万次。索引确实命中只是命中了错的那个——这不是索引没建而是优化器的取舍在低选择性前缀下翻车。二、排查与选择依据先看优化器选了什么EXPLAINFORMATJSONSELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;三个判据依次确认key从idx_user_id变成idx_status_createdExtra却没有Using filesortFORMATJSON的cost_info里query_cost更低但rows_examined_per_scan上万——代价模型按统计采样估算索引扫描低估了非覆盖索引的回表随机 I/O回表是乘法不是加法EXPLAIN ANALYZE8.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_switchprefer_ordering_indexoff) */单条语句修正、不改表结构该语句必然走 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_PASSWORDtest-eMYSQL_DATABASEdemo 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))ENGINEInnoDB;SETSESSIONcte_max_recursion_depth200001;INSERTINTOorders(user_id,status,amount,created_at)WITHRECURSIVE seqAS(SELECT1nUNIONALLSELECTn1FROMseqWHEREn200000)SELECT1n%10000,CASEWHEN(nDIV10000)%2016THENcompletedWHEN(nDIV10000)%2019THENshippedELSEpendingEND,10n%990,2024-01-01INTERVAL(n*157)SECONDFROMseq;ANALYZETABLEorders;操作步骤-- 步骤 1只有 idx_user_idEXPLAINANALYZESELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;-- 步骤 2加联合索引并刷新统计ALTERTABLEordersADDINDEXidx_status_created(status,created_at);ANALYZETABLEorders;-- 步骤 3重复同一查询EXPLAINANALYZESELECT*FROMordersWHEREstatusshippedANDuser_id42ORDERBYcreated_atDESCLIMIT20;预期输出步骤 1 选中idx_user_id回表约 20 次user_id42 恰好 20 行步骤 3 的key变为idx_status_created、Extra无Using filesortactual rows 升到 3 万量级约 15% 的行需回表过滤耗时明显上涨。这是依据官方文档描述的推断绝对毫秒数未验证取决于缓冲池与机器。实际输出本文示例未在本机执行需在你的 Docker 环境运行后回填回填时重点记录EXPLAIN ANALYZE的 actual rows 与耗时并与IGNORE INDEX (idx_status_created)的结果对照。常见失败加完索引立刻 EXPLAINkey没变化或仍走旧索引。原因是新索引统计信息未生成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 Optimizationsprefer_ordering_index 默认值MySQL 8.0 Index HintsUSE/FORCE/IGNORE INDEX 语法MySQL 8.0 Optimizer HintsSET_VAR 单语句开关MySQL 8.0 EXPLAIN 与 EXPLAIN ANALYZE 输出说明MySQL 8.0 ANALYZE TABLE索引统计刷新
