MySQL索引失效：从EXPLAIN排查到无法优化时的替代方案

很多人遇到 SQL 慢查询时，第一反应是：“这条 SQL 明明建了索引，为什么 MySQL 还是走全表扫描？”

于是就开始补索引、加联合索引、FORCE INDEX，甚至看到 EXPLAIN 里的 type=ALL 就认定“索引失效了”。

但真正做过数据库性能优化之后会发现，所谓“索引失效”其实不是一个单一问题。

有时候是 SQL 写法让优化器无法有效利用索引；有时候是联合索引设计不合理；有时候索引其实用了，只是扫描范围太大，使用索引反而比全表扫描更贵；还有一些场景，本身就不适合依赖 B+Tree 索引解决。

所以，排查索引问题最重要的并不是背诵“索引失效的 10 种情况”，而是建立一套稳定的分析路径：

先确认执行计划 → 再定位索引为什么没有产生收益 → 能改 SQL 就改 SQL → 不能改 SQL 就改索引/表结构 → 仍然无解时，往架构层处理。

本文就沿着这条思路，把 MySQL 索引问题完整梳理一遍。

一、先别急着说“索引失效”，先看执行计划

假设有一张订单表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    KEY idx_user_created (user_id, created_at),
    KEY idx_status_created (status, created_at)
);

我们执行：

SELECT *
FROM orders
WHERE user_id = 10001
  AND created_at >= '2026-09-01';

先不要猜，直接：

EXPLAIN SELECT *
FROM orders
WHERE user_id = 10001
  AND created_at >= '2026-09-01';

重点观察几个字段：

possible_keys：理论上哪些索引可能被使用。
key：最终优化器选择了哪个索引。
key_len：实际使用了联合索引的多少部分。
type：访问方式，例如 const、ref、range、index、ALL。
rows：优化器估算需要扫描多少行。
filtered：经过条件过滤后预计还剩多少比例。
Extra：是否出现 Using index、Using where、Using filesort、Using temporary 等信息。

这里有一个非常容易踩的误区：

key 不是 NULL，并不代表查询就一定很快；type 不是 ALL，也不代表索引使用得很好。

例如一个低选择性的索引可能只过滤掉很少的数据。此时优化器判断全表扫描成本更低，是完全合理的。

MySQL 官方也建议使用 EXPLAIN 来检查执行计划；对于需要进一步确认“优化器估算值”和“真实执行情况”是否一致的场景，可以使用 EXPLAIN ANALYZE。urlMySQL EXPLAIN 官方文档https://dev.mysql.com/doc/refman/8.4/en/using-explain.html

二、最常见的问题：对索引列做函数或表达式计算

例如有索引：

KEY idx_created_at (created_at)

但查询写成：

SELECT *
FROM orders
WHERE DATE(created_at) = '2026-09-09';

问题在于：MySQL 需要先计算 DATE(created_at)，再判断结果是不是目标日期。

这和直接在索引列上做范围查询完全不同：

SELECT *
FROM orders
WHERE created_at >= '2026-09-09 00:00:00'
  AND created_at <  '2026-09-10 00:00:00';

第二种写法可以直接把条件转换为索引范围。

类似的问题还有：

WHERE YEAR(created_at) = 2026
WHERE ABS(amount) > 100
WHERE LOWER(name) = 'samoy'
WHERE CAST(user_id AS CHAR) = '10001'

核心原则可以概括成一句话：

尽量让索引列保持“裸奔”，不要在索引列上再套函数、运算或转换。

不是所有函数都一定没办法

如果业务上就是必须查询计算后的值，可以考虑把表达式变成可索引的数据。

例如 MySQL 支持生成列并为生成列建立索引：

ALTER TABLE users
ADD COLUMN name_lower VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(name)) STORED,
ADD INDEX idx_name_lower (name_lower);

这样查询可以改成：

SELECT *
FROM users
WHERE name_lower = 'samoy';

这类方案的本质不是“强行让原来的 SQL 走索引”，而是把查询条件提前物化成一个真正可以建立索引的值。MySQL 官方文档也明确支持对 generated column 建立索引。urlMySQL Generated Column Index 官方文档https://dev.mysql.com/doc/refman/8.4/en/generated-column-index-optimizations.html

三、隐式类型转换：看起来一样，实际上类型不一样

这是线上非常常见的一类问题。

例如：

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    phone VARCHAR(20),
    KEY idx_phone (phone)
);

如果查询：

SELECT *
FROM users
WHERE phone = 13800138000;

数据库字段是字符串，但传入的参数是数字。此时涉及类型转换，执行计划可能和你预期的不一样。

正确做法是保证参数类型和字段类型一致：

SELECT *
FROM users
WHERE phone = '13800138000';

在 Java、Spring Boot、MyBatis 这类项目里尤其要注意：不要只看 SQL 模板，还要看最终绑定参数的类型。

很多“数据库明明有索引，但线上就是不走”的问题，最后都是在参数类型上找到原因。

四、`LIKE` 并不是天然不能走索引

经常有人说：

“LIKE 会导致索引失效。”

这句话并不准确。

例如：

WHERE name LIKE 'Sam%'

这种“前缀匹配”在合适的索引和字符集条件下仍然可以使用 B+Tree 索引。

真正麻烦的是：

WHERE name LIKE '%Sam'

以及：

WHERE name LIKE '%Sam%'

因为前面有 %，数据库无法直接从索引树上确定一个连续的起始范围。

这种情况怎么优化？

先问自己一个问题：

我真正需要的是“前缀匹配”，还是“全文包含匹配”？

如果业务只是前缀搜索，把 SQL 改成：

WHERE name LIKE 'Sam%'

就可能已经解决问题。

但如果业务真的需要“任意位置包含”，那就别再执着于 B+Tree 索引。可以根据场景考虑：

MySQL FULLTEXT 全文索引；
专门的搜索引擎，例如 Elasticsearch；
业务侧拆分搜索字段；
维护额外的倒排数据结构。

这也是本文后面要强调的一件事：

不是所有查询都应该靠普通索引解决。

五、联合索引最容易踩的坑：最左匹配不是“背口诀”那么简单

假设有联合索引：

KEY idx_user_status_created (user_id, status, created_at)

那么下面几个查询的索引利用方式并不一样：

-- 很理想
WHERE user_id = 10001
  AND status = 1
  AND created_at >= '2026-09-01'

-- 仍然可以很好利用索引
WHERE user_id = 10001
  AND created_at >= '2026-09-01'

-- 少了最左列，通常无法按照这个联合索引直接定位 user_id 范围
WHERE status = 1
  AND created_at >= '2026-09-01'

很多文章把它简单总结成“联合索引必须遵循最左匹配原则”，这句话没错，但还不够。

真正应该理解的是：

B+Tree 联合索引本质上是按照 (user_id, status, created_at) 的顺序排序的。

因此 MySQL 能不能高效地缩小搜索范围，取决于查询条件能不能沿着这个排序顺序建立一个连续范围。

还有一个经常被误解的问题：范围条件之后的列到底还能不能用？

例如：

KEY idx_a_b_c (a, b, c)

查询：

WHERE a = 10
  AND b > 20
  AND c = 30

不能简单理解成“c 就完全没用了”。更准确的说法是：b > 20 已经把索引扫描范围扩大成一个区间，后面的 c 往往不能再像等值条件那样继续缩小 B+Tree 的查找边界，但仍可能参与其他优化，例如索引条件下推等。

所以分析联合索引时，不要只看“有没有命中”，而要看：到底利用了索引的哪一部分，以及最终扫描了多少行。

六、`OR`、`!=`、`NOT IN`：不是绝对失效，而是可能“不划算”

1. `OR`

比如：

WHERE user_id = 10001 OR status = 1

这不意味着一定不用索引。MySQL 在某些场景下可以使用 Index Merge 等策略。

但如果两个条件的选择性都很差，或者数据量很大，优化器判断直接扫表更便宜，也是正常的。

2. `!=`

WHERE status != 0

假设 95% 的数据 status 都是 1，那么这个条件本身就没有什么筛选能力。

就算有索引，扫描索引后仍然需要访问大量数据，成本可能高于全表扫描。

3. `NOT IN`

同理：

WHERE status NOT IN (1, 2)

真正需要关心的是查询结果的选择性，而不是“语法长什么样”。

所以看到这些 SQL 时，不应该下结论：

“因为用了 !=，所以索引失效。”

正确的分析应该是：

“这个条件到底能过滤掉多少数据？使用索引后需要回表多少次？整体成本是否真的比全表扫描低？”

七、索引没走，还有一种可能：优化器认为“不值得走”

这是最容易被忽略的一层。

假设：

SELECT *
FROM orders
WHERE status = 1;

你建了：

KEY idx_status (status)

但如果表里 90% 的记录 status = 1，这个索引的选择性就很低。

此时如果走索引：

先扫描大量二级索引记录；
再根据主键回表；
最后拿到绝大部分数据。

还不如直接把整张表顺序扫一遍。

所以：

“不走索引”不一定是数据库出了问题，有时恰恰说明优化器认为全表扫描更便宜。

怎么验证是不是统计信息的问题？

如果你非常确定某个索引应该有明显收益，却发现优化器长期选择了一个奇怪的执行计划，可以先更新统计信息：

ANALYZE TABLE orders;

MySQL 官方文档也建议，当索引没有按预期被使用时，可以通过 ANALYZE TABLE 更新表统计信息。urlMySQL EXPLAIN 官方文档https://dev.mysql.com/doc/refman/8.4/en/explain.html

然后重新 EXPLAIN 看执行计划有没有变化。

八、`ORDER BY` / `GROUP BY` 也会让“有索引”和“高性能”变成两回事

例如：

SELECT *
FROM orders
WHERE user_id = 10001
ORDER BY amount DESC;

即使 user_id 有索引，也不意味着查询就可以顺便利用该索引完成排序。

如果索引顺序与过滤条件、排序条件不匹配，最终仍可能出现：

Using filesort

同样，Using filesort 也不等于“索引失效”。

它表达的是：排序没有完全依赖索引顺序完成。

有时这是合理且不可避免的；真正需要关心的是排序的数据量有多大、临时结构是否巨大、整体耗时是否可接受。

因此，一个更合理的联合索引可能是：

KEY idx_user_amount (user_id, amount)

然后让查询尽可能沿着索引顺序直接读取。

这里依然不要死记“看到 Using filesort 就加索引”，而应该回到执行计划和实际数据量。

九、为什么“加一个索引”往往不是最好的优化方式？

因为索引不是免费的。

每增加一个索引，就意味着：

占用额外磁盘空间；
增加 Buffer Pool 的内存压力；
INSERT 需要维护更多索引；
UPDATE 可能维护更多索引；
DELETE 也需要同步更新索引。

所以索引优化的目标不是：

“尽可能多地建索引。”

而应该是：

“用尽可能少的索引覆盖尽可能重要的查询模式。”

MySQL 官方同样提醒，不必要的索引会浪费空间，并增加写操作维护成本，需要在查询性能和索引成本之间取得平衡。urlMySQL Optimization and Indexes 官方文档https://dev.mysql.com/doc/refman/8.4/en/optimization-indexes.html

十、真正实战时，我会按照这个顺序排查

遇到一条慢 SQL，我一般不会上来就修改索引，而是按照下面的路径走。

第一步：确认是不是 SQL 本身慢

记录真实 SQL、参数、执行时间，不要拿一个脱离真实数据的例子分析。

第二步：看 `EXPLAIN`

重点确认：

key
key_len
rows
filtered
Extra

看“用了哪个索引”只是第一层，更重要的是：到底扫描了多少行。

第三步：看真实执行情况

对于支持的 MySQL 版本，可以进一步使用：

EXPLAIN ANALYZE
SELECT ...;

它会提供实际执行耗时、实际返回行数和循环次数等信息，可以帮助判断优化器的估算是否偏离真实情况。urlMySQL EXPLAIN ANALYZE 官方文档https://dev.mysql.com/doc/refman/8.4/en/explain.html

第四步：检查 SQL 有没有破坏索引使用条件

重点查：

索引列函数
隐式类型转换
LIKE '%xxx%'
复杂 OR
不必要的表达式

第五步：检查联合索引顺序

把查询中的条件拆成：

等值条件 → 范围条件 → 排序/分组 → 回表字段

再重新设计索引。

第六步：检查数据分布

重点看：

索引选择性
数据量
热点值
NULL/默认值分布

不要只根据字段“看起来应该建索引”来设计。

第七步：最后才考虑强制优化器选索引

MySQL 提供 index hint，例如：

SELECT *
FROM orders FORCE INDEX (idx_user_created)
WHERE user_id = 10001;

但我非常不建议把 FORCE INDEX 当作第一选择。

因为今天的数据分布可能让这个索引最优，半年后数据分布发生变化，原本正确的 hint 可能反而把优化器锁死在一个更差的执行计划上。

MySQL 8.4 还提供了更细粒度的 optimizer hints，可以针对具体语句控制优化器行为。urlMySQL Optimizer Hints 官方文档https://dev.mysql.com/doc/refman/8.4/en/optimizer-hints.html

十一、索引真的无法解决时，怎么办？

这是我认为比“索引失效 10 种情况”更值得掌握的一部分。

因为有些查询，从根上就不适合继续堆索引。

场景一：搜索条件本身就是全文匹配

例如：

WHERE content LIKE '%mysql%'

如果数据量已经非常大，再怎么折腾普通 B+Tree，也很难让它变成高效的全文搜索。

此时应该考虑 FULLTEXT 或专门的搜索系统。

场景二：查询本身就需要扫描大量数据

比如：

SELECT SUM(amount)
FROM orders
WHERE created_at >= '2020-01-01';

如果命中的就是全表 80% 的数据，索引不一定能带来决定性收益。

这时候可以考虑：

预聚合；
汇总表；
按天/月维护统计数据；
缓存热门统计结果。

例如把每天的订单金额提前汇总到：

order_daily_stat
----------------
date
order_count
total_amount

查询就从“扫描海量明细”变成“扫描少量统计数据”。

这已经不是索引优化，而是改变数据访问模型。

场景三：分页越来越慢

很多系统都会写成：

SELECT *
FROM orders
ORDER BY id DESC
LIMIT 100000, 20;

即使 id 有索引，也意味着数据库需要跳过前面的大量记录。

这时可以改成基于游标/范围的分页：

SELECT *
FROM orders
WHERE id < 123456
ORDER BY id DESC
LIMIT 20;

这种方案的收益通常不是“多建了一个索引”，而是减少了数据库必须扫描的数据范围。

场景四：单表已经巨大

如果一张业务表已经达到非常大的规模，单纯继续优化索引可能收益越来越有限。

此时可以根据业务访问模式考虑：

分区表；
冷热数据分离；
历史数据归档；
读写分离；
分库分表。

但要注意：分区、分库分表都不是“索引失效后的万能药”。

它们解决的是数据规模和访问路径的问题，复杂度和运维成本也会显著上升。

十二、还有一个经常被忽略的方向：减少回表

假设：

SELECT user_id, created_at
FROM orders
WHERE user_id = 10001
  AND created_at >= '2026-09-01';

如果索引本身已经包含：

KEY idx_user_created (user_id, created_at)

那么查询所需要的字段都在索引里，MySQL 在合适的情况下可以直接从索引得到结果，而不必再回表读取整行数据。

这就是常说的覆盖索引。

可以通过 EXPLAIN 观察是否出现：

Using index

覆盖索引的价值往往比单纯讨论“走不走索引”更实际：

索引不只是用来定位数据，还可以直接承载查询需要的数据。

当然，也不能为了覆盖一个查询就无节制地建立“巨宽索引”，因为索引越宽，存储和写放大成本越高。

十三、不要迷信“看到全表扫描就是坏事”

最后再强调一次：

type = ALL

并不等于数据库一定有问题。

如果一张表只有几百行，甚至几十行：

SELECT * FROM config WHERE status = 1;

直接扫表可能就是最合理的方案。

同样：

Using filesort

也不等于必须加索引。

possible_keys 有值也不代表一定应该使用；key 有值也不代表一定比全表扫描快。

真正需要关注的是成本，而不是某一个 EXPLAIN 字段是否“好看”。

十四、我对“索引失效”的理解

做数据库性能优化时，我越来越不喜欢“索引失效”这个词。

因为它很容易让人陷入一种错误思维：

“索引没用上，所以我要想办法让它用上。”

但正确的问题应该是：

“为什么当前执行计划成本更高？我要怎么让数据库更少地扫描、更少地回表、更少地排序、更少地处理无关数据？”

于是整个优化过程其实就变成了一个漏斗：

慢 SQL
  ↓
EXPLAIN / EXPLAIN ANALYZE
  ↓
扫描行数是否过多？
  ↓
SQL 写法有问题？ ──→ 改 SQL
  ↓
索引设计有问题？ ──→ 改索引
  ↓
统计信息有问题？ ──→ ANALYZE TABLE
  ↓
索引仍然收益有限？ ──→ 覆盖索引 / 生成列 / 重构查询
  ↓
查询本身就要处理海量数据？
  ↓
缓存 / 汇总表 / 归档 / 分区 / 搜索引擎 / 读写分离

到最后你会发现：

数据库优化的终点，从来不是“让 SQL 走索引”，而是“让系统少做无意义的工作”。

索引只是其中一个非常重要的工具，但绝不是唯一的工具。

编程手札

MySQL索引失效：从EXPLAIN排查到无法优化时的替代方案

一、先别急着说“索引失效”，先看执行计划

二、最常见的问题：对索引列做函数或表达式计算

不是所有函数都一定没办法

三、隐式类型转换：看起来一样，实际上类型不一样

四、`LIKE` 并不是天然不能走索引

这种情况怎么优化？

五、联合索引最容易踩的坑：最左匹配不是“背口诀”那么简单

还有一个经常被误解的问题：范围条件之后的列到底还能不能用？

六、`OR`、`!=`、`NOT IN`：不是绝对失效，而是可能“不划算”

1. `OR`

2. `!=`

3. `NOT IN`

七、索引没走，还有一种可能：优化器认为“不值得走”

怎么验证是不是统计信息的问题？

八、`ORDER BY` / `GROUP BY` 也会让“有索引”和“高性能”变成两回事

九、为什么“加一个索引”往往不是最好的优化方式？

十、真正实战时，我会按照这个顺序排查

第一步：确认是不是 SQL 本身慢

第二步：看 `EXPLAIN`

第三步：看真实执行情况

第四步：检查 SQL 有没有破坏索引使用条件

第五步：检查联合索引顺序

第六步：检查数据分布

第七步：最后才考虑强制优化器选索引

十一、索引真的无法解决时，怎么办？

场景一：搜索条件本身就是全文匹配

场景二：查询本身就需要扫描大量数据

场景三：分页越来越慢

场景四：单表已经巨大

十二、还有一个经常被忽略的方向：减少回表

十三、不要迷信“看到全表扫描就是坏事”

十四、我对“索引失效”的理解

参考资料

OKF Agent Memory：基于 Git 的 AI 编程智能体持久化记忆方案

欢迎在评论区留下您的见解~

一、先别急着说“索引失效”，先看执行计划

二、最常见的问题：对索引列做函数或表达式计算

不是所有函数都一定没办法

三、隐式类型转换：看起来一样，实际上类型不一样

四、LIKE 并不是天然不能走索引

这种情况怎么优化？

五、联合索引最容易踩的坑：最左匹配不是“背口诀”那么简单

还有一个经常被误解的问题：范围条件之后的列到底还能不能用？

六、OR、!=、NOT IN：不是绝对失效，而是可能“不划算”

1. OR

2. !=

3. NOT IN

七、索引没走，还有一种可能：优化器认为“不值得走”

怎么验证是不是统计信息的问题？

八、ORDER BY / GROUP BY 也会让“有索引”和“高性能”变成两回事

九、为什么“加一个索引”往往不是最好的优化方式？

十、真正实战时，我会按照这个顺序排查

第一步：确认是不是 SQL 本身慢

第二步：看 EXPLAIN

第三步：看真实执行情况

第四步：检查 SQL 有没有破坏索引使用条件

第五步：检查联合索引顺序

第六步：检查数据分布

第七步：最后才考虑强制优化器选索引

十一、索引真的无法解决时，怎么办？

场景一：搜索条件本身就是全文匹配

场景二：查询本身就需要扫描大量数据

场景三：分页越来越慢

场景四：单表已经巨大

十二、还有一个经常被忽略的方向：减少回表

十三、不要迷信“看到全表扫描就是坏事”

十四、我对“索引失效”的理解

参考资料

OKF Agent Memory：基于 Git 的 AI 编程智能体持久化记忆方案

欢迎在评论区留下您的见解~

四、`LIKE` 并不是天然不能走索引

六、`OR`、`!=`、`NOT IN`：不是绝对失效，而是可能“不划算”

1. `OR`

2. `!=`

3. `NOT IN`

八、`ORDER BY` / `GROUP BY` 也会让“有索引”和“高性能”变成两回事

第二步：看 `EXPLAIN`