MySQL索引失效与慢查询优化：从B+树原理到SQL避坑实战

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_id，B+ 树的排序结构就没法利用了。

很多人对最左前缀原则的理解有偏差，以为只要查询条件里有联合索引的第一个字段就行，实际上完整的规则是这样的：从联合索引的第一个字段开始，查询条件必须连续命中，且不能跳过中间字段。比如索引是(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值比例较高的时候。

这里有个重要的细节：对于可空字段（nullable），InnoDB 引擎在二级索引中会额外存储一个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字段代表访问类型，从好到差的排列大致是：

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 = 'enabled=on'; -- 执行要分析的 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 的九大现象，快速自查清单

我把这几年遇到的慢查询问题归纳成一个速查表，适合在线上问题刚出现时快速定位方向：

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);

执行计划对比：

优化后接口耗时从 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 到确定优化方案，通常只需要五到十分钟。