MySQL索引失效排查的优化实践

1. 内容整体设计与排查思路

先说一个最常见的场景：开发同事把sql抛过来，说“这条查询明明加了索引，为什么还是慢，好几秒才出结果”。打开执行计划一看， type 是 all ， key 是 null ，扫了全表几十万行——索引建了，但在这次查询里压根没用上。

这种问题在mysql日常运维里出现的频率极高，而且往往不是“没建索引”这么简单。索引失效背后，牵涉到优化器的判断逻辑、字段类型的隐式转换、查询条件的写法、表数据量的统计信息，甚至字符集和排序规则都会掺一脚。想要系统性排查，不能靠猜，得按顺序来。

我一般会分成四步走：

1. 确认慢sql到底慢在哪 ：先开慢查询日志，把执行时间超过阈值的sql捞出来，确认是单条sql慢，还是某类sql普遍慢。
2. 查看执行计划 ：用 explain 看这条sql到底走没走索引、走的是哪个索引、大概扫描多少行、有没有额外的排序和临时表。
3. 判断索引为什么没被用上 ：这一步是核心，要对索引失效的各种场景逐个对照排查，从查询写法到优化器估算，再到索引结构本身。
4. 针对根因做优化 ：改写sql、调整索引设计、更新统计信息，或者做一些更底层的调整。

这篇文章我就按照这个思路，把常见的索引失效坑做一个完整梳理。内容覆盖面比较广，但每一条都是实际工作里能直接对照检查的，适合所有被慢查询折磨过的后端开发、dba和数据运维同学。

1.1 先搞清楚索引在mysql里是怎么工作的

想理解索引为什么失效，得先知道mysql的索引结构是什么样。

默认的innodb引擎使用的是 b+树 索引结构。你可以把b+树想象成一棵倒着长的树，叶子节点上存的是整行数据（聚簇索引）或者索引列的值加主键值（二级索引），非叶子节点存的是“路由信息”——也就是用来定位数据范围的键值。

查询时，mysql的优化器会根据sql条件，从b+树根节点开始，一层一层往下找，定位到目标数据所在的叶子节点，这个过程叫 索引查找 。理论上，走索引可以把扫描范围从“全表”缩小到“一个很小的区间”，这也是索引能大幅提升查询性能的根本原因。

但问题来了： 索引能不能被用上，不是程序员说了算，是优化器说了算 。优化器会基于成本模型，比较“走索引扫描”和“全表扫描”两种方案哪个代价更低。如果走索引需要大量回表（根据二级索引里的主键再去聚簇索引查整行），或者查询条件让索引无法快速定位，优化器就会放弃索引，转头去做全表扫描。

所以“索引失效”这个词其实不太准确，更严谨的说法是“ 优化器认为在这个查询场景下，用索引的成本高于全表扫描，所以选择不用 ”。理解了这一点，很多失效场景就说得通了。

1.2 排查慢查询的第一步：把慢sql捞出来

遇到线上慢查询，我建议先别急着看sql，先把慢查询日志打开。有些环境的慢查询日志默认是关着的，需要手动开启。

-- 查看当前慢查询日志状态
show variables like 'slow_query%';
show variables like 'long_query_time';

-- 开启慢查询日志（当前会话级别）
set global slow_query_log = 'on';
set global long_query_time = 1;          -- 设置阈值：超过1秒算慢查询
set global slow_query_log_file = '/var/log/mysql/slow.log';

更推荐的做法是直接写进配置文件，避免重启丢失：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

log_queries_not_using_indexes 这个参数建议顺手打开，它会把没走索引的sql也记录到慢日志里，哪怕执行时间没超过阈值。很多索引失效的sql就是靠这个揪出来的。

日志打开之后，用 mysqldumpslow 或 pt-query-digest 做汇总分析，把产生慢查询最多的sql按频率排个序，优先处理那些出现次数最多、单次执行最慢的。

2. 索引失效的常见坑：逐个对照检查

这部分是全文的核心，我把实际工作中遇到的索引失效场景整理成了一个对照清单。每一条我都会给出典型sql、失效原因和修改方案，方便大家直接对号入座。

2.1 隐式类型转换：最常见的隐形杀手

先看一个真实案例。线上有一张用户表， user_id 字段定义的是 varchar(32) ，开发在查询的时候写成了这样：

select * from user_info where user_id = 10086;

表面上看起来没问题， user_id 字段上明明有索引，但执行计划就是不走。原因在于： user_id 是字符串类型，而查询条件里给的 10086 是数字类型。mysql在做比较时，会自动把 varchar 类型的字段转换成数字类型，相当于在索引列上加了 cast 函数。

一旦索引列参与了函数运算，b+树就没法按原始键值进行定位了，索引自然就废了。

排查方法：用 explain 看执行计划时，重点关注 key 列是否为 null ，同时查看 type 列是不是 all 。另外可以执行 show warnings ，mysql会显示优化器改写后的sql，里面能看到隐式转换的痕迹。

explain select * from user_info where user_id = 10086;
show warnings;

修改方案是把查询参数改成字符串，或者把字段类型改成 bigint 。具体情况具体分析：

-- 推荐方案1：参数改为字符串
select * from user_info where user_id = '10086';

-- 推荐方案2：如果业务上user_id本来就是数字，直接改表结构
alter table user_info modify column user_id bigint;

注意 ：不仅仅是等值查询会有这个问题， join 关联时如果两个表的关联字段类型不一致，也会导致索引失效。比如a表关联字段是 int ，b表是 varchar ，连表的时候同样会发生隐式转换。

2.2 最左前缀原则被破坏

联合索引是工作中用得最多的索引类型，但很多人对它的理解只停留在“建了就能加速”的层面，忽略了最左前缀原则。

假设表里有这样一个联合索引： (area_id, create_time, status) ，它实际上相当于建了三棵索引树： (area_id) 、 (area_id, create_time) 、 (area_id, create_time, status) 。也就是说，从最左列开始，任意连续的子集都可以用到索引。

但如果查询条件跳过了第一列，直接按第二列或第三列查，对不起，索引直接失效。

-- 不走索引：没有包含最左列 area_id
select * from order_list where create_time >= '2024-01-01' and status = 1;

-- 部分走索引：只用到 area_id 一个字段的索引
select * from order_list where area_id = '010' and status = 1;

前面那条sql为什么完全失效？因为b+树的排序规则是先按 area_id 排，再按 create_time 排，最后按 status 排。查询条件里没有 area_id ，就没法从根节点确定该往哪个子节点走，相当于索引树的分叉路由全部派不上用场。

第二条sql的情况更特殊，它属于“ 部分失效 ”。优化器通过 area_id = '010' 定位到一小批数据，但接下来的 status = 1 条件无法在索引树上继续过滤。mysql只能把满足 area_id = '010' 的那批记录都捞出来，再逐条回表判断 status 。这种场景下，如果回表的记录数很少，索引还是能用上的；但如果 area_id = '010' 对应的记录非常多，优化器同样会放弃索引。

优化建议 ：调整联合索引的列顺序，按照“区分度高、选择性强的列放前面”的原则设计，同时把等值查询的列放在最左，把范围查询的列放后面。比如 (area_id, status, create_time) 就比原来的设计合理得多。

2.3 对索引列做函数运算或表达式计算

这条和隐式类型转换是“同族兄弟”，本质上都是在索引列上做手脚，导致b+树快速定位失效。

常见的写法有：

-- 对索引列使用date函数
select * from operation_log where date(create_time) = '2024-01-01';

-- 对索引列做表达式计算
select * from account where balance + 100 > 5000;

这两种写法，mysql优化器连考虑索引的机会都不给，因为b+树里存的是原始值，不是计算后的结果。优化器没法根据计算后的值直接定位叶子节点，只能全量扫描所有记录，逐行计算、逐行比较。

修改方案 ：把函数运算从索引列移到条件值那边。

-- 正确写法1：范围查询
select * from operation_log 
where create_time >= '2024-01-01' 
  and create_time < '2024-01-02';

-- 正确写法2：表达式移右边
select * from account where balance > 5000 - 100;

这里有个经验之谈： 凡是索引列出现在函数、加减乘除、类型转换中，索引基本就是废的 。写sql的时候养成习惯，索引列保持“纯洁”，不要给它穿任何外套。

2.4 like前缀模糊匹配

这个坑在搜索类业务里几乎是必踩的。

-- 不走索引
select * from product where name like '%手机%';

-- 可能走索引（range范围扫描）
select * from product where name like '手机%';

原理不复杂：b+树的叶子节点存储时是按照索引列的值排序的， '手机%' 这种条件可以直接定位到以“手机”开头的第一个位置，然后向后扫描，这部分操作走的是索引。但 '%手机%' 不一样，它没法确定起始位置，优化器只能全表扫描，逐行去匹配是否包含“手机”这两个字。

优化方案 ：

• 能改成前缀匹配就改前缀匹配，比如搜索词自动补全场景， like 'keyword%' 完全够用。
• 实在需要全文模糊搜索，考虑引入全文索引（ fulltext ）或专门的搜索引擎。
• 数据量不大但业务确实需要，可以接受全表扫描，但要做好限流和缓存。

2.5 or条件导致的索引失效

再看这个经典场景：

select * from user_info 
where nickname = 'admin' or phone = '13800138000';

假设 nickname 和 phone 上分别建了单列索引。这条sql看起来两个条件都有索引，但实际上mysql在遇到 or 时，需要同时满足“某个条件走索引”再加上“另一个条件的处理”。如果优化器可以把 or 改写成两个索引扫描的并集（即 index_merge ），那还行；但实际情况下，mysql经常因为成本估算或者其他限制，直接选择全表扫描。

更常见的一个坑是： or连接的条件里，有一个字段没有索引，整个查询直接退化 。

-- 假设 nickname 有索引，email 没有索引
select * from user_info 
where nickname = 'admin' or email = 'admin@example.com';

这种情况下，mysql只能把所有记录都扫描一遍，逐行判断是否满足 nickname = 'admin' 或者 email = 'admin@example.com' ，因为 email 字段没索引，它没法先走 nickname 索引再回头处理 email 。

优化建议 ：

• 用 union all 拆开：

select * from user_info where nickname = 'admin'
union all
select * from user_info where email = 'admin@example.com';

• 或者确保or连接的每一个字段都有索引，并且让优化器走 index_merge 。但说实话， index_merge 这个功能在mysql里的表现不是很稳定，能拆就拆，别把宝押在它身上。

2.6 is null / is not null / != 等不等值查询

很多人不知道， is null 、 is not null 、 != 、 <> 、 not in 这类“非等值”查询，也是索引失效的高发区。

-- 可能不走索引
select * from user_info where delete_flag is not null;

-- 可能不走索引
select * from order_list where status != 'paid';

原因也回到优化器的成本核算上。 is not null 这种条件，优化器如果觉得大部分行都满足条件，那走索引扫描加回表的成本，比直接全表扫描还高，那就干脆全表扫。

当然这不是绝对的。如果 null 值占比很低，优化器算下来走索引更划算，它还是会走。比如一个字段1000行里只有3行是 null ， where column is not null 大概率会走索引。

经验总结 ：这类查询能不能走索引，非常依赖数据分布。不要只看执行计划是不是用了索引，还要看 rows 列估算的扫描行数，判断优化器的选择是否合理。

2.7 数据量和统计信息的问题：优化器“判断失误”

还有一种情况特别坑：sql写法没问题，索引也建了，但优化器就是不走索引，或者选错索引。

这背后的原因通常有两个：

• 统计信息过期 ：innodb的优化器依赖统计信息来估算扫描行数。如果频繁的大批量增删改之后，没有及时更新统计信息，优化器会用“旧数据”做成本判断，结果就是明明走索引更快，它却选了一条更慢的路。
• 数据量太小 ：表里只有几百行数据，全表扫描成本极低，优化器压根不屑于走索引。这种情况不是“失效”，而是“不值得”。

处理方法 ：

-- 手动更新统计信息
analyze table user_info;

-- 查看执行计划是否改变
explain select * from user_info where status = 'active';

如果遇到优化器“头铁”非要选错索引，也可以使用索引提示（ force index 或 use index ）来干预：

select * from user_info force index (idx_status) where status = 'active';

但 force index 属于“下策”，根治方案还是得把索引和sql都设计好，让优化器在绝大多数情况下能做出正确选择。

3. 实操过程：用explain和慢查询日志一步步定位

3.1 一个完整的排查流程示例

我拿最近处理的一个线上案例做演示。业务方反馈某个订单列表页打开要3秒多，接口查的是订单主表。我先开慢查询日志，等了几分钟，抓到了这样一条sql：

select order_no, user_id, amount, status, pay_time 
from order_main 
where status = 'paid' 
  and create_time >= '2024-06-01' 
order by create_time desc 
limit 20;

表上有两个单列索引： idx_status(status) 、 idx_create_time(create_time) 。

第一步， explain 看执行计划：

explain 
select order_no, user_id, amount, status, pay_time 
from order_main 
where status = 'paid' 
  and create_time >= '2024-06-01' 
order by create_time desc 
limit 20;

结果如下：

看到 key 是 null ，扫描行数48万，还有 using filesort （文件排序），这条sql慢得理所当然。

第二步，逐条分析为什么索引没用上。

possible_keys 里两个索引都在，说明mysql知道这俩索引存在，但最终一个都没选。原因大概率是：单查 status 会捞出一大批 paid 订单（这张表里paid占比接近60%），优化器觉得回表成本太高；单查 create_time 也有同样的范围过大问题。两个索引单独拎出来，覆盖度都太宽，倒不如全表扫描来得痛快。

第三步，对症下药。

既然单列索引不够用，那就建联合索引，把过滤条件合并起来：

alter table order_main 
add index idx_status_create_time (status, create_time);

联合索引建好之后，再跑一次 explain ：

扫描行数从48万降到1.25万， using filesort 也消失了——因为索引本身按 (status, create_time) 排序， create_time 的范围过滤直接利用索引的有序性，排序环节省掉了。

这个案例是典型的“索引设计不当导致失效” ，不是sql写法问题，而是索引本身没有覆盖到查询模式。类似的场景，查 where status = ? and create_time >= ? 的两个条件，单独建两个单列索引基本没有意义，必须建联合索引。

3.2 用explain的关键列判断索引使用情况

很多人会用 explain ，但只会看 key 列有没有值。实际上，真正决定sql是否高效的是另外几列，我习惯把这几列看成“体检指标”：

type列 ：访问类型，性能从好到差依次是：

如果 type 是 all ，基本就是全表扫描，sql的性能就有大问题了。

key列 ：实际用到的索引名称。如果为 null ，说明没有使用索引。这里有个细节： possible_keys 列出的是优化器“可能考虑”的索引，而 key 才是它“真正选择”的索引，一定要区分开。

rows列 ：优化器估算的需要扫描的行数。这个数字不一定精确，但能直观反映查询的扫描范围。如果一个查询 rows 是几十万，即使走了索引，也意味着性能有隐患。

extra列 ：这个信息量极大，常见的有：

出现 using filesort 或 using temporary 时，优先考虑能否通过调整索引来消除它们，因为这两个操作通常都在内存或磁盘上额外消耗大量资源。

3.3 慢查询日志里如何快速筛出索引失效的sql

慢查询日志文件默认是纯文本格式，一行记录一条sql，直接看大文件很痛苦。我常用的工具有两个：

mysqldumpslow ：mysql自带的慢日志分析工具，按执行次数、耗时汇总。比如按平均耗时排序看前10条：

mysqldumpslow -t 10 -s at /var/log/mysql/slow.log

pt-query-digest ：percona toolkit里的神器，分析维度更细，会统计每条sql的执行频率、总耗时、平均耗时、扫描行数等，还能生成可读性很强的报告：

pt-query-digest /var/log/mysql/slow.log

在 pt-query-digest 的报告中，重点看“profile”部分，它会按总耗时排出top sql。拿到sql之后，挨个用 explain 验证，基本就能定位到所有索引失效的问题。

另外，如果开了 log_queries_not_using_indexes = 1 ，慢日志里还会记录那些“没走索引但执行时间不长”的sql。这类sql单次不慢，但高频执行会加重数据库负载，强烈建议一起优化掉。

3.4 实操中容易忽略的“隐蔽因素”：字符集与排序规则

这是我在实际排查中踩过最深的一个坑，这里单独提出来讲。

两张表做 join ，关联字段都建了索引，执行计划显示关联字段没用上索引，反复检查都找不出原因。最后发现，问题出在字符集上——一张表的关联字段是 utf8mb4 ，另一张是 utf8 。mysql在做比较时，需要对字符集做隐式转换，等于在索引列上加了转换函数，索引就废了。

类似的还有排序规则（collation）不一致的情况，比如一张表是 utf8mb4_general_ci ，另一张是 utf8mb4_unicode_ci ，关联或排序时也会导致索引不可用。

检查方法 ：

-- 查看表的字符集和排序规则
show table status like 'order_main';
-- 或用information_schema查看
select table_name, table_collation 
from information_schema.tables 
where table_schema = 'your_database';

解决思路 ：统一业务表、关联字段的字符集和排序规则。现在新项目建议全部使用 utf8mb4 + utf8mb4_unicode_ci ，老项目应该逐步迁移。

这个坑隐蔽性很强，因为mysql不会报错，不会警告，一切看起来“正常”，就是慢，而且排查思路容易一直在索引设计和sql写法上打转。如果遇到怎么也解释不通的索引失效，建议先查一下字符集和排序规则。

4. 日常工作中如何从根源上减少索引失效

4.1 索引设计阶段的规划

很多索引失效问题，根源是索引设计阶段没想清楚。建索引前建议先问自己四个问题：

1. 这条sql最核心的过滤条件是什么？ 优先把等值过滤的列放到索引最前面。
2. 排序、分组用的字段有哪些？ 索引不只能加速 where ，还能让 order by 、 group by 利用索引的有序性，避免 filesort 。
3. 查询需要返回哪些列？ 如果查询列都能被索引覆盖，也就是所谓的“覆盖索引”，连回表都省了，性能直接起飞。
4. 索引不是越多越好，怎么取舍？ 每个索引都会拖慢写入速度。更新表时，mysql要把相关索引同步更新一次，索引太多，写入qps会肉眼可见地下降。建议单表索引控制在5个以内，联合索引要精打细算。

4.2 写sql时养成的好习惯

从我的经验来说，大部分索引失效问题，在写sql的时候就可以避免，不需要等到线上报警再去救火。

这里整理几条我自己写sql时遵循的规则：

• 不给索引列“穿外套” ：不在索引列上使用函数、加减乘除、类型转换，如果必须用，就考虑用生成列（ generated column ）或者改写sql。
• 让联合索引吃满“最左前缀” ：查询条件尽量包含联合索引的最左列，同时把范围查询字段放在联合索引靠后的位置。
• 避免 select * ：能写具体列就写具体列，一方面减少回表，另一方面增加覆盖索引命中概率。
• or 改成 union all ：尤其是多个条件涉及不同索引列的时候。
• like 尽量前缀匹配 ：业务上实在无法避免，考虑搜索引擎或者接受全表扫描并做缓存。
• 加 limit ：大范围查询场景，加上 limit 可以降低优化器对“回表成本”的畏惧感，有时候会促使优化器选择索引。

4.3 定期监控和巡检建议

最后说点运维层面的东西。索引失效不是一次性问题，数据量增长、业务逻辑变更、统计信息过期都可能让原来的好sql变慢。建议做到下面几点：

1. 慢查询日志长期开启 ，不要只在出问题时才开。日志文件用工具做轮转，避免磁盘被撑爆。
2. 定期用 pt-query-digest 做慢sql分析 ，按照“总耗时”排名，把排在前面的sql纳入优化清单。
3. 数据库大版本升级或数据量翻倍后，主动做一次 analyze table ，确保统计信息新鲜。
4. 用 sys.schema_unused_indexes 检查冗余无用索引 ：

select * from sys.schema_unused_indexes;

把没用过的索引清理掉，可以为写入释放压力。

5. 巡检时关注 performance_schema 里的表io统计 ，定位那些扫描行数远超返回行数的sql，这些sql大概率存在索引问题。

我自己踩过几次大坑之后，总结出来的体会是：索引失效查起来确实费神，因为它的触发条件太多——类型不一致、联合索引顺序不对、查询写法不规范、优化器统计信息失真、字符集不统一……任何一环出了偏差，结果都是“建了索引不走”。

所以与其等问题爆发再排查，不如把日常的sql开发和索引设计规范抓实。尤其是团队协作的项目，建议把“索引列保持纯洁”“联合索引遵循最左前缀”“关联字段类型一致”这三条写进开发规范，让所有人在写sql的第一时间就避免踩坑。毕竟数据库出了问题，痛苦的是所有值班的人。

到此这篇关于mysql索引失效排查的优化实践的文章就介绍到这了,更多相关mysql索引失效排查内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！