MySQL 索引失效与慢查询优化我被这些SQL坑了3次后总结的保命指南先交代一下背景。我做了差不多六年半的后端开发绝大部分时间都在跟 MySQL 打交道按理说索引这种东西早该形成肌肉记忆了但现实很打脸就在今年我连续三次在生产环境被同一类问题掀翻在地全是索引失效导致的慢查询其中一次还直接把线上数据库的 CPU 打到了 99%被迫紧急重启实例。复盘的时候我发现一个扎心的事实——那些导致索引失效的写法我平时在教程和文档里都见过但真正落到自己写的 SQL 上就是发现不了。事后看都是细节事前看全是盲区。这篇文章不是什么官方文档的复述而是我把那三次事故从头到尾拆开揉碎之后整理出的一份适合普通后端开发者的索引失效排查手册和慢查询优化实操指南。无论你是刚接触 MySQL 的初级开发还是已经被慢查询折磨过几轮的中级工程师这篇文章的目标只有一个让你写出来的 SQL 能稳稳走索引让慢查询日志不再三天两头报警。1. 内容整体设计与思路拆解1.1 我踩过的三个坑先摊开给你看第一个坑发生在某个订单列表接口上。上线快一年了数据量也就几十万行结果某天下午接口突然从 100ms 变成了 1.8 秒业务方直接找上门。我拉出慢查询日志一看罪魁祸首是一个用了date_format(order_time, %Y-%m-%d) 2024-03-18的查询。这条 SQL 表面上看用到了order_time字段但实际上我在它外面套了一层函数索引直接失效全表扫描跑起来数据量一大就原形毕露。第二个坑更隐蔽。订单表里有个status字段业务人员为了统计方便直接写了一条where status ! 1的查询。我第一反应是这很常见没什么问题。但执行计划打出来之后我愣住了明明status字段上有索引走的却是全表扫描。原因在于 MySQL 的优化器在评估的时候发现!条件下索引的区分度不高加上表里大部分数据都是status 1的状态它干脆放弃了索引。这事儿给我的教训是不是所有带索引的查询都会走索引优化器有自己的小算盘。第三个坑是被坑得最惨的一次。一个报表模块的统计 SQL用了left join关联三张表其中一张表的关联字段虽然是索引字段但两表的字符集不一致——一张表是utf8mb4另一张是utf8。MySQL 在比较的时候要做隐式类型转换和字符集转换索引又失效了。那次事故让我明白一个道理索引失效的原因往往不在 SQL 本身而在表结构设计上。1.2 为什么索引会失效先从 B 树说起要搞清楚索引为什么失效得先弄明白 MySQL 的索引在底层是怎么工作的。以 InnoDB 为例最常用的主键索引和二级索引底层结构都是 B 树。B 树是一种多路平衡搜索树数据只存在叶子节点非叶子节点全是索引键值。每一层节点都按顺序排列查询的时候从根节点出发一层一层往下走通过二分查找的方式快速定位到目标位置。B 树能高效工作的核心前提是查询条件必须能够沿着索引的有序性进行范围扫描或等值匹配。一旦查询条件破坏了这种有序性B 树的优势就荡然无存。举个例子比如索引建立在a字段上B 树里的数据是按照a的值排序存储的。当你写where a 5的时候优化器能直接在树里找到对应的位置。但如果你写where a 1 5这就变成了对索引字段做运算。索引里存的是a的值不是a 1的值优化器没办法直接在树里定位只能把所有a的值都取出来算一遍。这就是所谓的索引失效本质上是查询条件破坏了 B 树的有序定位能力。提示优化器决定走不走索引主要看两个指标——预估扫描行数和回表成本。如果预估扫描行数占全表比例过高一般超过 20%~25%优化器会认为走索引还不如全表扫描划算。1.3 慢查询优化的整体思路先定位再分析最后改优化慢查询这件事千万不要一上来就盲改。我在前两年也犯过这个错误看到一条慢 SQL二话不说直接加索引结果加了之后查询反而更慢了。后来我才总结出一套相对固定的优化流程现在已经成了我处理所有慢查询问题的标准动作。第一步是定位慢查询靠的是 MySQL 自带的慢查询日志或者performance_schema里的事件统计。第二步是分析执行计划也就是explain命令的输出。第三步才是针对性优化可能是改写 SQL可能是调整索引也可能是重构表结构。第四步是回归验证用explain配合实际数据量做前后对比确认优化有效。这四个步骤说起来简单但每一步都有很多细节需要注意。特别是explain的分析很多人只会看type字段是不是ALL实际上rows、Extra、key_len这些字段里隐藏着大量关键信息。后面我会逐一拆解。2. 核心细节解析与实操要点索引失效的九种典型场景2.1 对索引列做了函数运算或隐式转换这是最常见也最容易犯的一类问题。对索引列使用函数、表达式或类型转换会让索引失效。前面提到的date_format(order_time, ...)是一个典型例子另外几个高频场景也值得记牢。where left(title, 10) MySQL优化这种写法对字符串列调用substr或left函数索引失效。where price * 0.9 100这种写法对数值列做算术运算索引失效。where phone 13812345678这种写法如果phone是varchar类型而查询条件是数值类型MySQL 会先把索引列隐式转换为数值类型再比较索引失效。最后一个隐式转换的场景我特别想多说两句。很多人在设计表的时候手机号、身份证号、订单号这类字段喜欢用varchar存储这本身没问题。但在查询的时候如果参数是从其他接口传过来的数值类型或者是从 JSON 里解析出来没做类型处理的就会出现隐式转换。有一个很简单的判断技巧如果查询条件里索引列的类型和参数类型不一致先检查是不是有隐式转换。注意隐式转换导致的索引失效非常隐蔽explain里看到的type可能仍然是ref或range但实际性能已经打了折扣。对于varchar字段要主动确认参数传递是否统一为字符串类型。2.2 LIKE 模糊查询的前导通配符问题like %keyword这种写法因为通配符在最前面B 树的有序性没办法利用索引会失效。但like keyword%这种写法是可以走索引的因为前缀匹配仍然可以利用 B 树的最左前缀特性。这里有一个很多人没意识到的问题即便是like %keyword%这种前后都有通配符的写法在某些特殊情况下优化器也可能选择全索引扫描覆盖索引而不是全表扫描但这种方式能生效的前提是查询的所有列都在索引中且数据量不算特别大。实际业务中这种写法通常还是慢。如果业务确实需要做包含匹配我的建议是不要过分迷信索引直接上全文索引fulltext或者外部搜索组件效率高得多。如果一个表经常需要做前导通配符的模糊查询又没法引入外部搜索组件可以考虑用冗余字段的方案比如把需要搜索的关键词单独存一列配合fulltext索引。2.3 联合索引不满足最左前缀原则联合索引复合索引是另一个重灾区。比如在(user_id, status, create_time)上建了联合索引查询条件只写了where status 1那么索引是走不了的因为联合索引的最左前缀是user_id查询条件里没带user_idB 树的排序结构就没法利用了。很多人对最左前缀原则的理解有偏差以为只要查询条件里有联合索引的第一个字段就行实际上完整的规则是这样的从联合索引的第一个字段开始查询条件必须连续命中且不能跳过中间字段。比如索引是(a, b, c)查询条件是where a 1 and c 3这时只有a能利用索引c没法利用。查询条件是where b 2 and c 3则完全不走索引。提示最左前缀原则是面试高频题但实际开发中更重要的是理解它的背后逻辑。联合索引本质上是一棵多级排序树先按a排序a相同的再按b排序b相同的再按c排序。就像查字典先按拼音首字母找没有首字母就没法继续往下翻。2.4 OR 连接的条件中有一部分没有索引where a 1 or b 2如果a上有索引而b上没有整条查询可能走全表扫描。原因很简单MySQL 的优化器要让这条查询同时匹配两个条件的结果集如果其中一个条件没法用索引快速定位那它只能把全表数据捞出来一条条判断。处理or问题的方案通常有两种。一种是确保涉及的每个字段都有独立索引让优化器可以用 index merge 的方式合并两个索引的结果。另一种是把or改写成两个查询用union合并这种方式更可控但 SQL 会变长。需要注意的是index merge 并不是所有版本都能高效工作5.7 之后表现比较好但也不建议过度依赖能用覆盖索引还是优先覆盖索引。我个人更推荐的思路是能不写or就不写or。很多时候or条件的出现意味着业务逻辑本身就有点拧巴值得回头审视一下需求。2.5 条件中使用 IS NULL / IS NOT NULL 导致索引失效where name is not null这种写法在大多数情况下索引是失效的。原因在于 MySQL 的索引中会存储NULL值但is null和is not null的语义判断需要额外的位图处理优化器评估后发现利用索引的成本并不比全表扫描低尤其当表中NULL值比例较高的时候。这里有个重要的细节对于可空字段nullableInnoDB 引擎在二级索引中会额外存储一个NULL标志位。查询is null时理论上可以利用索引的排序结构但 MySQL 优化器对is null的处理并不像等值查询那么高效尤其是在索引区分度不高的情况下。实际调优中如果是频繁需要判断空值的字段我倾向于直接给字段设置一个默认值如空字符串或 0然后在业务层统一处理尽量避免is null的出现。2.6 负向条件查询!、not in、not exists负向条件查询基本都是索引失效的高发区。!、、not in、not exists这些操作符优化器往往选择全表扫描。核心原因是 B 树的索引结构对于“排除某个值”这种操作并不友好——它无法快速定位到所有“不等于某值”的行只能扫描完整个索引或全表再过滤掉不符合条件的记录。not in的问题比!更严重因为not in可以看作多个!的叠加。如果子查询返回的结果集很大not in的扫描成本会成倍增加。一个可行的替代方案是用left join ... where b.id is null来模拟not in的语义这种改写方式在关联表数据量可控的情况下效果不错。不过负向条件查询也并非 100% 失效。在某些特定情况下——比如查询结果集只占全表的极少比例——优化器可能会选择索引扫描但这属于优化器的自由裁量我们不能赌这种运气。设计查询时优先考虑正向条件。2.7 索引列参与计算或类型转换这类问题的典型特征是在where条件中对索引列进行了运算比如where create_time interval 1 day now()。前面提到的函数运算本质上也是计算的一种但这里强调的不只是函数还包括数值运算、位运算和日期运算。有一个非常容易踩坑的日期场景某天我接到一个需求要查最近 7 天的订单。我的第一版写法是select * from orders where create_time date_sub(now(), interval 7 day)这条 SQL 的问题不在create_time列上而是在now()这个函数上。now()是个非确定性的函数每次执行结果不同MySQL 优化器无法对其进行常量折叠因此不得不每次执行都重新计算date_sub的结果。虽然create_time列本身没有参与运算但查询依然可能无法高效地利用索引范围扫描的优化空间。正确的写法应该是先拿到当前时间戳通过参数传入select * from orders where create_time 2024-03-11 00:00:00这其实就是典型的“把函数从列上移到参数上”的思路要尽量让索引列独立出现在比较符的一侧。2.8 表关联时字符集或排序规则不一致这个坑在前面的第三个事故里提到过这里展开说。MySQL 在做表关联时如果关联字段的字符集和排序规则不一致会触发隐式类型转换从而无法使用索引。在实际排查中我用过一条 SQL 来查库里的字符集分布select table_schema, table_name, column_name, character_set_name, collation_name from information_schema.columns where column_name in (user_id, order_id, product_id)执行之后能快速找出有哪些表的关联字段字符集不一致。修正方案很简单把字符集统一改成utf8mb4排序规则统一改成utf8mb4_unicode_ci或utf8mb4_0900_ai_ci取决于 MySQL 版本。2.9 优化器判断失误导致放弃索引这类情况和前面几种不同它并不是索引在技术上不可用而是优化器基于统计信息的判断出了问题。典型场景是表数据量很小的时候全表扫描成本比走索引低优化器选择了全表扫描但数据量增长后统计信息还没来得及更新优化器依然沿用旧的决策。解决方法是定期执行analyze table来更新统计信息或者在 SQL 中用force index强制走索引。但force index属于最后的手段因为一旦数据分布变化强制走索引反而可能更慢。我在使用force index时有一条原则只在业务高峰期应急使用后续必须配合统计信息更新和 SQL 改写来根治。3. 实操过程与核心环节实现从执行计划到索引设计3.1 用 explain 读懂 MySQL 的内心戏任何一个优化过 SQL 的人都绕不开explain。但很多人的使用方式还停留在“看看type是不是ALL”的层面。实际上explain输出包含了至少 12 个字段每个字段都有它的含义我会重点讲几个关键字段的判断标准。type字段代表访问类型从好到差的排列大致是type含义说明system系统表仅一行数据极少出现const主键或唯一索引等值匹配性能最好eq_ref联表查询时被驱动表通过主键或唯一索引等值匹配高性能ref非唯一索引等值匹配常见的高效访问range索引范围扫描能接受index全索引扫描比全表扫描好一点但要警惕ALL全表扫描需要重点优化rows字段是优化器预估的需要扫描的行数这个值越大说明扫的数据越多。我这里踩过一个误区早期一直以为rows是精确值后来才知道这是基于统计信息的估算值实际执行可能偏离不少。在 MySQL 8.0 里可以结合explain analyze来看真实执行情况它会输出实际行数和实际耗时。Extra字段里有几个值得留意的值。如果出现Using filesort说明排序没走索引MySQL 在内存或磁盘上做了额外的排序操作数据量大时非常耗时。出现Using temporary说明用了临时表通常是 group by 或 distinct 导致的。出现Using index则是好消息说明是覆盖索引回表都省了。如果Extra里同时出现Using where; Using index说明虽然走了索引但还有其他过滤条件是在索引扫描后进一步过滤的。下面我放一个真实的explain输出做演示id | select_type | table | type | key | rows | Extra 1 | SIMPLE | orders | ref | idx_user_time | 500 | Using index condition这个输出说明查询走了idx_user_time这个索引预估扫描 500 行使用了索引下推Using index condition即 ICP索引条件下推。在 MySQL 5.6 之后ICP 可以把部分where条件下推到存储引擎层进行过滤减少回表次数这是一个很有效的优化机制但前提是联合索引的字段顺序设计得当。3.2 慢查询日志配置与分析实战慢查询日志的默认配置是关闭的需要手动开启。我一般会设置两个关键参数slow_query_log和long_query_time。在生产环境long_query_time我习惯设置为 1 秒配合log_queries_not_using_indexes参数把所有没走索引的查询也记录下来这样能发现一些隐藏的隐患。-- 查看当前慢查询配置 show variables like slow_query_log%; show variables like long_query_time%; -- 开启慢查询日志注意生产环境重启后可能失效建议写入 my.cnf set global slow_query_log on; set global long_query_time 1; set global log_queries_not_using_indexes on;慢查询日志的分析我用得比较多的是mysqldumpslow工具它能按执行次数或耗时排序快速找出最需要优化的 TOP N 查询。如果想分析得更彻底可以打开performance_schema里的events_statements_summary_by_digest表这个表会按 SQL 指纹聚合统计方便我看到同类型 SQL 的总耗时。我处理慢查询最喜欢用的是pt-query-digest它是 Percona Toolkit 里的工具输出格式非常清晰能按总耗时、平均耗时、出现次数等维度排序还能自动识别出常见的问题模式。这个工具稍微有点学习成本但值得花时间掌握。3.3 优化器追踪查看 MySQL 为什么没走索引有时候explain只能告诉我们结果不能告诉我们原因。这时候就要用 MySQL 的优化器追踪功能optimizer_trace它能输出优化器在做决策时的完整评估过程。具体用法是在会话级开启追踪set optimizer_trace enabledon; -- 执行要分析的 SQL select * from orders where user_id 123 and status 1; -- 查看追踪结果 select * from information_schema.optimizer_trace\G追踪结果里有一个rows_estimation部分会列出优化器对每个可用索引的成本估算还会解释为什么选择或放弃某个索引。看完之后通常会有一种“原来优化器是这么想的”的感觉。不过这个输出非常冗长建议有明确疑问的时候再用日常用explain就够了。3.4 联合索引设计的三条实战原则联合索引设计可能是 MySQL 表结构设计里最考验功力的环节根据我这几年的经验总结了三条原则每一条都是用真金白银换来的。第一条等值匹配的字段放在最前面。比如查询条件经常用到user_id ?和status ?那联合索引要把user_id放最前面因为等值匹配能最大程度利用 B 树的排序结构。如果范围查询放在等值查询前面那等值查询就只能部分利用索引后面字段的排序优势就浪费了。第二条利用索引下推让非前导字段也能过滤。MySQL 5.6 之后的索引下推特性使得联合索引中前面字段匹配之后后面字段的过滤条件也能在索引层完成减少了大量回表。比如索引(user_id, status, create_time)查询where user_id 1 and status 1时status 1的过滤可以在索引层直接完成。这也是为什么联合索引的字段顺序不一定要把区分度最高的字段放前面而要把满足等值匹配的字段放前面。第三条频繁排序和分组的字段放进联合索引。order by和group by如果使用的字段恰好是联合索引的一部分MySQL 可以直接利用索引的有序性省去了filesort操作。比如索引(user_id, create_time)可以直接满足where user_id 1 order by create_time的查询需求。这里要特别注意顺序的问题(user_id, create_time)和(create_time, user_id)是两种完全不同的索引。前者能高效支持where user_id ? order by create_time后者则不能。所以联合索引字段顺序的设计一定要基于实际查询模式不能闭门造车。3.5 覆盖索引让回表彻底消失覆盖索引是我在做查询优化时最喜欢用的一招它的原理很简单如果查询需要的所有列都包含在索引中那就不需要回表直接从索引叶子节点拿数据。举例说明。假如订单表有一个联合索引(user_id, order_no, status, create_time)那么下面的查询就完全可以用覆盖索引完成select user_id, order_no, status from orders where user_id 10086 and create_time 2024-01-01这里要查询的列user_id、order_no、status全在索引里MySQL 只扫索引就能返回数据不需要回表查主表性能提升非常明显。覆盖索引的实际效果可以通过explain的Extra字段确认只要看到Using index字样就说明命中了覆盖索引。但覆盖索引并不是越多越好因为联合索引本身会占用额外的存储空间每加一个字段写入时索引维护的成本就增加一分。我的建议是优先把查询频率最高的字段组合放进覆盖索引而不是把所有字段都塞进去。实务提醒覆盖索引的收益主要体现在高并发读场景。写多读少的表不要过度设计覆盖索引否则会造成不必要的写入性能损耗和存储成本。4. 常见问题与排查技巧实录那些年我踩过的坑4.1 加索引后查询反而更慢问题出在基数统计上我有一次给一张千万级数据量的表加了索引本以为查询能起飞结果explain一看优化器还是选择了全表扫描。后来查了优化器追踪才发现是统计信息没更新优化器以为全表扫描成本更低。解决方案很简单执行analyze table orders;analyze table会让 InnoDB 重新采样统计信息更新索引的基数cardinality。执行完之后再跑一遍查询发现索引已经被正确使用了。这里有个维护习惯值得养成对数据量变化较大的表或者是频繁大批量插入、删除的表建议每周做一次analyze table避免统计信息失真导致优化器判断失误。4.2 MySQL 8.0 的隐式转换新变化MySQL 8.0 对隐式类型转换的规则做了一些调整特别是字符集和排序规则之间的转换逻辑有所变化。一个比较典型的场景是utf8mb4和utf8字段比较时8.0 里可能出现Illegal mix of collations错误而 5.7 里可能只是静默地做转换。我在一次从 5.7 升级到 8.0 的过程中就遇到了这个报错。解决思路依然是统一字符集不要依赖隐式转换。从长远来看统一字符集不仅是为了避免错误更是为了确保索引能被正确利用。4.3 order by 导致慢查询的排查实录有一次优化一个分页接口explain显示查询走了索引type是range但接口还是很慢。我仔细一看Extra字段发现有一个Using filesort。问题出在分页的order by create_time和查询条件的联合索引字段顺序不匹配。我的改写方案是把create_time字段加入联合索引的末尾这样查询条件和排序就能同时利用索引的有序性。这里还有一个分页深翻页的经典问题。limit 100000, 20这种写法MySQL 需要先扫描并丢弃前 10 万行再取后面的 20 行。越往后翻页扫描的行数越多性能呈线性恶化。一个通用的优化技巧是使用“延迟关联”select t.* from orders t inner join ( select id from orders where user_id 10086 order by create_time desc limit 100000, 10 ) tmp on t.id tmp.id这种写法的核心思路是先用覆盖索引找到目标 id再回表取完整行数据减少回表的次数。实测在百万级数据量的深度分页场景下性能提升可达数倍。4.4 慢 SQL 的九大现象快速自查清单我把这几年遇到的慢查询问题归纳成一个速查表适合在线上问题刚出现时快速定位方向现象可能原因排查方向查询突然变慢统计信息过旧执行analyze table同样的 SQL 时快时慢缓存命中率变化查看 buffer pool 命中率加了索引没效果索引字段上有函数运算检查 where 条件改写联表查询很慢字符集不一致检查关联字段的 collation分页越翻越慢深翻页问题用延迟关联改写排序慢使用 filesort把排序字段加入索引某些值查得很慢数据倾斜比如 status1 占比 99%批量插入后变慢索引碎片增多考虑optimize tableSQL 写法没问题但就是慢索引基数估算失真检查统计信息4.5 独家避坑几个我自己常用的排查技巧先讲一个查看索引真实使用情况的方法。MySQL 8.0 提供了视图sys.schema_unused_indexes可以直接列出所有从未使用过的索引。这个功能特别适合做索引清理。我在一次大扫除中通过这个视图发现了一张表上有 5 个冗余索引清理之后写入性能提升了 15% 左右。select * from sys.schema_unused_indexes;再分享一个排查索引失效现场的技巧。如果某条 SQL 在测试环境走索引上了生产环境就走全表优先怀疑数据分布差异过大。测试环境可能只有几百行数据优化器认为全表扫描更划算生产环境有上千万行但统计信息没收集完全也会误判。这时候可以手动执行explain看执行计划再配合analyze table刷新统计信息。还有一个我特别想强调的排查思路不要只盯着单条 SQL 的执行时间还要看它背后的执行频率。一条耗时 200ms 的 SQL如果每秒执行 200 次对数据库的压力远超一条偶发耗时 5 秒的 SQL。这类高频低耗 SQL 往往藏在业务代码里要通过performance_schema的语句汇总表来发现。4.6 几条能立刻上手的优化建议如果你手上的系统已经出现了慢查询但来不及做大规模改造可以先执行下面这几条改动通常能快速缓解大部分问题。第一把查询中所有对索引列做运算的写法都改掉。这是一个排查成本最低但收益最高的动作。无论是date_format、concat、还是算术运算统一改成对参数做处理。第二把select *改成只查需要的列。这能大大提高覆盖索引的命中率。我在好几个项目里看到一个只需要 3 个字段的列表接口SQL 却把 30 多个字段全查出来了导致每次查询都要回表。第三检查所有联表查询的关联字段字符集是否一致。如果发现不一致尽快统一。第四对慢查询日志做定期分析把 TOP 10 的 SQL 拿出来逐条看执行计划。不要等线上事故发生了才做这件事我现在的习惯是每个月做一次。注意优化慢查询是持续性工作一次优化完成不代表一劳永逸。业务数据量在增长查询模式在变化索引的使用情况也在动态调整。保持定期的体检习惯才是根治之道。5. 从失败中总结我的索引设计心法和执行计划复盘5.1 索引设计的全局视角经历过三次事故之后我重新审视了所有核心表的索引设计。发现一个普遍问题很多索引是开发过程中为了应付某一条慢 SQL 临时加的加完之后没人负责review越积越多最后变成了既占空间又拖慢写入的负担。我现在做索引设计会遵循一套相对固定的评审流程。第一步从慢查询日志和业务接口清单里统计出高频查询模式按频率排序。第二步根据 TOP 查询模式设计联合索引优先满足等值匹配和排序需求。第三步用explain验证核心 SQL 的执行计划确认type至少达到range尽量是ref或const。第四步持续观察慢查询日志验证索引是否真正解决了问题。这套流程看起来不复杂但贵在坚持。我认识的很多优秀 DBA 和资深后端其实都是靠这套流程在做日常维护只是他们做得比大多数人更细致、更持续。5.2 三次事故的复盘与教训第一次事故函数运算导致索引失效这条给我的教训是写 SQL 的时候要始终意识到索引列的独立性是索引能够生效的前提。任何在索引列上做的包装都是在跟 B 树过不去。第二次事故!条件导致全表扫描这条给我的教训是索引不是有了就能用优化器有自己的成本模型。写 SQL 的时候要主动思考数据分布如果一个字段的某个值占了绝大多数比例那针对这个值的过滤条件大概率不会走索引。第三次事故字符集不一致导致隐式转换这条给我的教训是最深刻的慢查询优化不能只盯着 SQL 本身表结构设计的合理性往往才是决定查询性能的根源。字符集不一致这种问题光靠改写 SQL 是修不好的必须从表结构层面解决。5.3 优化效果的度量和回归验证每次优化完 SQL我都会记录优化前后的关键指标。最简单的方式是记录执行时间但我更推荐记录explain里的rows字段和执行计划的变化。因为执行时间受缓存、并发、网络环境影响很大而rows和执行计划是相对稳定的指标。我优化完成的标准定义如下第一执行计划中不再出现ALL类型扫描第二Extra中没有Using filesort和Using temporary第三预估扫描行数rows至少比原先减少 90% 以上第四线上监控中该 SQL 的 P99 耗时明显回落。四个条件全部满足我才会在优化记录里打勾。提示优化完成后不要马上关掉慢查询日志至少观察一周。因为有些优化在测试环境看似完美放到生产环境后可能因为数据分布、并发压力等因素出现新问题。一周的观察期能帮你发现所有潜在隐患。6. 最终实操一个完整案例的优化全过程6.1 问题描述与初始状态我拿一个线上真实案例来做完整演示。业务背景是电商平台的订单列表页用户可按照订单状态筛选并按下单时间倒序排列。数据量约 800 万行日增约 3 万行。某次上线一个新筛选条件后接口响应时间从 300ms 涨到 7 秒直接触发告警。原始 SQL 大致如下select id, order_no, user_id, status, amount, create_time from orders where user_id 10086 and status in (0, 1, 2) and date_format(create_time, %Y-%m-%d) 2024-03-18 order by create_time desc limit 20;执行计划显示type为ALL扫描行数约 780 万行Extra里还有Using filesort。6.2 问题拆解与优化策略这条 SQL 存在三个核心问题我逐个拆解。第一个问题date_format(create_time, %Y-%m-%d)对索引列做了函数运算导致索引失效。改写方法很简单把函数运算移到参数一侧where create_time 2024-03-18 00:00:00 and create_time 2024-03-19 00:00:00第二个问题联合索引缺失。原表的索引是idx_user_id(user_id)和idx_create_time(create_time)两个独立索引。这个查询需要同时用user_id等值过滤和create_time范围排序所以要建一个联合索引(user_id, create_time)。第三个问题order by create_time desc和查询条件的联合索引顺序要匹配。上面这个联合索引正好能满足where user_id ? order by create_time desc的需求所以排序可以走索引不需要 filesort。6.3 优化后的 SQL 与执行计划对比改写后的 SQL 如下select id, order_no, user_id, status, amount, create_time from orders where user_id 10086 and status in (0, 1, 2) and create_time 2024-03-18 00:00:00 and create_time 2024-03-19 00:00:00 order by create_time desc limit 20;创建联合索引alter table orders add index idx_user_time (user_id, create_time);执行计划对比指标优化前优化后typeALLrangekeyNULLidx_user_timerows780万约50ExtraUsing filesortUsing index condition优化后接口耗时从 7 秒降到 80ms 左右效果非常明显。这里也顺带说明一个细节status in (0, 1, 2)在联合索引中并没有被直接利用因为联合索引(user_id, create_time)中没有status字段它是在索引下推阶段完成过滤的。这就是前面提到的 ICP 机制的实战应用。6.4 后续的持续监控优化完成之后我并没有马上拍屁股走人而是做了一周的持续监控。我观察到该接口的日常耗时稳定在 50ms~80ms 之间慢查询日志里再没有出现过这条 SQL 的记录。另外我还顺手检查了同类的其他查询模式发现有几个类似的筛选条件组合也能复用这个联合索引相当于一次优化惠及了多条 SQL。这个案例最大的价值在于它把前面讲到的所有知识点做了一次串行落地定位慢查询、用 explain 分析执行计划、找到索引失效原因、设计正确的联合索引、改写 SQL、验证优化效果、持续监控。整个过程并不复杂但每一步都需要足够的耐心和细致。最后再分享一个小技巧。如果你和我一样经常会被各种慢查询问题缠身建议在你的常用工具集里加上两个命令的肌肉记忆explain和show profile。前者让你在看到 SQL 的第一时间就能判断有没有走索引后者让你快速定位到底耗时在哪一步。这两个命令用熟之后我从拿到一条慢 SQL 到确定优化方案通常只需要五到十分钟。