本文摘要线上订单加联合索引后从 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索引统计刷新
企业数字化 ERP 产品动态
相关推荐
AI代码评审两件套：superpowers-skills中如何请求评审，以及如何优雅地回怼反馈 【免费下载链接】superpowers-skills Community-editable skills for Claude Codes superpowers plugin 项目地址： https://gitcode.com/gh_mirrors/su/superpowers-skills 点击查看 免费下载 如果你在用 Claude Code 做开发，superpowers-skills 是 sup… · 2026/11/14 3:27:10
TTT3R长序列数据集准备指南：ScanNet、TUM、Sintel、Bonn、KITTI逐步上手 【免费下载链接】TTT3R [ICLR 2026] A simple state update rule to enhance length generalization for CUT3R 项目地址： https://gitcode.com/gh_mirrors/ttt/TTT3R 点击查看 免费下载 TTT3R 是一款把「3D 重建当作测试时训练」的长序列重建模型（ICLR… · 2026/11/13 6:47:40
看懂macOS热压力5级：以MacThrottle为例，一文搞懂热降频如何影响你的Mac性能 【免费下载链接】MacThrottle Menu bar app to know when your Mac is thermal throttling 项目地址： https://gitcode.com/gh_mirrors/mac/MacThrottle 点击查看 免费下载 Mac 在重度编译、导出视频或跑大型模型时会悄悄变慢，这往往是**热降频… · 2026/11/6 5:59:03
赤峰电工证号怎么报名，学历不高怕报不上名？手把手教你解决 赤峰电工证号怎么报名，学历不高怕报不上名？手把手教你解决 学历不高怕报不上名？其实赤峰电工证号怎么报名，根本不看学历，只要你符合条件，就能顺利报考。很多人一听到“电工证”，第一反应就是“要大学文凭才能考”，其实不是的，很多从事电工工作的人员… · 2026/11/16 3:08:33
低压电工作业是指多少V以下的电气设备多少钱 低压电工作业是指多少V以下的电气设备多少钱 之前考的证在外省能不能转过来？这事儿我帮人办了不下200单，说实话，很多人一开始都不太清楚怎么操作，特别是关于低压电工作业是指多少V以下的电气设备多少钱这个问题，更是被问得最多。别急，下面我用多年… · 2026/11/16 3:08:33
增城在哪里考电工证千万别踩坑，工地太忙根本没时间复习考试 增城在哪里考电工证千万别踩坑，工地太忙根本没时间复习考试 工地太忙根本没时间复习考试，这几乎是每个特种作业人员的真实写照。尤其是像电工证这种门槛高、需求大、考试难度也不低的证书，很多人在报名时一头雾水，报了培训班也迟迟没通过，最后才发现，… · 2026/11/16 3:08:33
考电工证入职骗局别怕，电子证书在手不怕挂科 考电工证入职骗局别怕，电子证书在手不怕挂科 实操考试心里没底怕挂科，这事儿我懂。作为干过几年电工的我，也知道不少刚入行的朋友，就因为这事儿被骗过。今天咱就聊聊【考电工证入职骗局】这事儿，顺便说说怎么考个真本事的 电子证书… · 2026/11/16 3:08:33
德州市电工证查询有效期多久，上岗必备怎么确认？ 德州市电工证查询有效期多久，上岗必备怎么确认？ 你是不是正为找不到工作发愁？是不是因为手里没有【德州市电工证查询】的结果，连面试机会都拿不到？别急，今天就给你讲清楚，怎么查、怎么确认有效期、怎么保住这个【上岗必备】的证书。… · 2026/11/16 3:08:33
深圳有电工证考场吗网上查询最新解答，工地太忙根本没时间复习考试？ 深圳有电工证考场吗网上查询最新解答，工地太忙根本没时间复习考试？ 工地太忙根本没时间复习考试，这是很多平顶山房建工程从业者的真实写照。尤其是像电工证这类必须上手操作的证书，考试内容涉及安全规范、操作流程、设备原理等，如果不提前准备，考试通过… · 2026/11/16 3:07:32
长春初级电工证怎么考？通过率揭秘全在这里 长春初级电工证怎么考？通过率揭秘全在这里 之前考的证在外省能不能转过来？这几乎是每个想从事电工工作的人都会问的问题。特别是那些在外地打拼多年、手里握着一张“外地电工证”的人，到了长春想继续干这一行，第一反应就是“我的证能用吗？”。今天我们就… · 2026/11/15 0:00:50
国内电工证报考条件与考试时间 低压电工证怎么考？学历不高也能报名，官方报名入口全在这 你是不是经常听说“电工证是入行门槛”，但又担心自己学历不够、经验不足，根本报不了名？别急，今天就带你从零了解【国内电工证】的报考流程、考试内容，还有怎么在 官方报名入口… · 2026/11/16 3:01:18
电工作业时如何断电？电子证书到底能不能全国通用？ 电工作业时如何断电？电子证书到底能不能全国通用？ 实操考试心里没底怕挂科，特别是电工作业时如何断电这一操作，不仅关系到你能不能拿到证书，更关系到以后能不能顺利上岗。很多人觉得这不过就是“断个电”，但其实这里面的门道可不少，稍有不慎就可能出错… · 2026/11/15 0:00:50
电工证培训的通知在哪考？别再被中介骗了 电工证培训的通知在哪考？别再被中介骗了 你是不是还在为 电工证培训的通知在哪考 发愁？网上搜了一堆信息，结果要么是挂羊头卖狗肉的培训机构，要么就是打着“包过”旗号的骗子？别急，今天我来给你讲讲， 电工证培训的通知… · 2026/11/16 0:00:34
电工证去哪个部门考核值不值得考 信阳转岗人真实经历分享 电工证去哪个部门考核值不值得考 信阳转岗人真实经历分享 怕考不过白交培训费？你不是一个人，信阳的李师傅去年刚转岗做电工，就因为没搞清楚电工证去哪个部门考核，差点交了8000块的培训费。别急，下面这份实操考试全攻略，告诉你电工证到底值不值得考… · 2026/11/16 0:00:34
双极步进电机控制实战：DRV8818PWPR与PIC18F86K22驱动调试 /* MD / 富文本中的 .toc（含博客园搬家等嵌套结构）；.toc-box 在侧栏，不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p（:empty 仍占 20px），一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views … · 2026/11/15 1:51:20
STM32F207ZG + DRV8818PWPR 步进电机驱动实战解析 /* MD / 富文本中的 .toc（含博客园搬家等嵌套结构）；.toc-box 在侧栏，不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p（:empty 仍占 20px），一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views … · 2026/11/15 4:02:19
DRV8818PWPR+dsPIC30F4013工业级双极步进电机控制方案 /* MD / 富文本中的 .toc（含博客园搬家等嵌套结构）；.toc-box 在侧栏，不受影响 */#content_views .toc,/* 编辑器常在目录前后插入空 p（:empty 仍占 20px），一并去掉避免顶空隙 */#content_views.markdown_views > p:empty:has(+ .toc),#content_views.markdown_views … · 2026/11/15 3:02:05
