MySQL 8.0 联合索引走错：为什么加了索引查询反而从 200ms 变成 8s-北京尧图网络

本文摘要线上订单加联合索引后从 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索引统计刷新

MySQL 8.0 联合索引走错：为什么加了索引查询反而从 200ms 变成 8s

相关推荐

【无套路自取】给 Inspector 换上 X 光镜：Odin V3.5 属性绘制器，让 Unity 数据编辑省心得多

【附下载链接】纵横四海文字游戏：新手从零到进游戏的完整上手指南

如何快速看懂 AUTO-MAS 日志采集管线 log_box：从采集到推送的完整指南

更多资讯

【纯白嫖】凸优化资源合集：Boyd《Convex Optimization》中英双语版免费完整指南

【纯免费分享】SMIC 180工艺库 Virtuoso 导入指南：完整 PDK 资源一次讲清

【纯白嫖】如何快速规范公文格式：Word 公文模板选型与安装完整指南

dsh-connect-workbuddy 模型管理详解：积分倍率、图片输入开关与夜间折扣徽标怎么看

【纯白嫖】南方电网通信类复习资料：考试内容拆解与备考节奏指南

2026年会员管理系统多少钱？先算清交易费率和第二年续费这笔账

今日更新

AI智能体开发实战：从技术原理到商业落地

OpenShell 完全定制指南：从安装到深度配置，打造高效 Windows 开始菜单

Superpowers技能体系全解析：从Skills引入到安装的完整指南

本周精选

防幻觉智能体架构：电源硬件设计中的大模型编排与工具化实践

OpenGL面试核心考点：渲染管线、矩阵变换与性能排查全解析

全国计算机二级C语言真题PDF高效刷题指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？