开发者日记

外观

MySQL索引失效10个常见场景与实用规避方法 ​

很多开发者在业务库中新建完索引后，经常遇到查询性能没达到预期的问题，排查后才发现索引根本没有被执行器命中，也就是出现了索引失效问题。索引失效是MySQL慢查询占比最高的诱因之一，掌握常见失效场景的识别方法，能帮开发者快速完成SQL调优，避免线上业务出现性能抖动。

在真实业务中，“建了索引却没走”通常不是 MySQL 优化器“失灵”，而是 SQL 写法、数据分布、索引设计、统计信息或字符集配置让优化器认为：全表扫描、全索引扫描或其他访问路径成本更低。因此，调优时不能只凭“这个字段有索引”下结论，而应结合 EXPLAIN、EXPLAIN ANALYZE（MySQL 8.0.18+）、慢查询日志、数据量和选择率综合判断。

索引失效的核心原理 ​

MySQL InnoDB引擎的B+树索引是按照索引字段的原始值进行排序存储的，当查询条件无法直接匹配索引的排序规则，需要对索引字段做额外运算、截断、转换才能匹配目标值时，引擎就会放弃走索引，转而使用全表扫描，最终导致查询耗时指数级上升。

更准确地说，B+树索引能高效工作，依赖三个前提：

  1. 有序性：索引列按数据类型对应的排序规则排列，等值和范围条件可以通过二分定位快速缩小扫描范围。
  2. 原始值可匹配：条件左侧必须尽量保持索引列的原始值，例如 WHERE phone = ?，而不是 WHERE phone + 0 = ?。
  3. 成本足够低：优化器会估算走索引后的回表次数、顺序页与随机页读取成本。如果条件命中的数据比例很高，普通二级索引可能不如聚簇索引顺序扫描。

InnoDB 的二级索引叶子节点存储的是索引列值和主键值。通过普通二级索引查询非覆盖列时，往往需要再用主键回表查询完整记录。当命中行数占比较高时，大量随机回表 IO 会让优化器放弃二级索引。一个常见经验值是：如果条件命中约 20%～30% 以上的数据，普通二级索引的吸引力会明显下降；但阈值并不固定，取决于数据缓存、行宽度、统计信息和是否覆盖索引。

从访问类型看，EXPLAIN 中常见性能层级大致如下：

type含义性能判断
const主键/唯一索引等值匹配最好
eq_ref被驱动表按主键/唯一索引等值关联很好
ref非唯一索引等值匹配较好
range索引范围扫描可接受，取决于范围大小
index_merge多个索引合并可能可行，但要额外验证
index扫描整个索引树通常一般
ALL全表扫描大表上重点排查

10类高频索引失效场景与SQL示例 ​

我们先创建一张测试用户表并插入普通索引、联合索引作为演示基础：

sql
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `phone` varchar(16) NOT NULL COMMENT '手机号',
  `name` varchar(32) NOT NULL,
  `age` int NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_phone` (`phone`),
  KEY `idx_age_name_ctime` (`age`,`name`,`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

为了让执行计划更稳定，建议准备一批不同分布的数据，而不是只插几条记录：

sql
-- 语言：SQL
-- 递归生成 10 万行测试数据，MySQL 8.0+ 可直接执行
INSERT INTO `user` (`phone`, `name`, `age`, `create_time`)
WITH RECURSIVE seq(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT
  CONCAT('138', LPAD(n, 8, '0')) AS phone,
  CONCAT('user_', n) AS name,
  18 + (n MOD 50) AS age,
  DATE_ADD('2024-01-01 00:00:00', INTERVAL n MINUTE) AS create_time
FROM seq;

ANALYZE TABLE `user`;

在 10 万行规模下，等值查询手机号通常可以稳定走 idx_phone；而对低区分度字段（如年龄只有 50 个值）单独查某个年龄段，可能扫描较多记录，是否走索引还要看查询列和数据分布。

1. 前导模糊匹配失效 ​

当使用LIKE查询时，通配符%出现在匹配词的前侧，会破坏B+树的前缀排序规则导致索引失效，这也是最常见的入门级坑：

sql
-- 失效写法：前导加%，无法走idx_phone索引
EXPLAIN SELECT * FROM `user` WHERE phone LIKE '%1380000';
-- 优化写法：非前导模糊可正常走索引
EXPLAIN SELECT * FROM `user` WHERE phone LIKE '1380000%';

B+树适合按字符串从左到右比较。LIKE '138%' 能定位到 138 开头的区间；而 LIKE '%138' 的目标可能散落在索引任意位置，无法利用前缀有序性。若查询列恰好被索引覆盖，优化器也可能选择 index，即扫描整个索引，而不是全表扫描，但这仍不是理想的范围定位。

可运行的覆盖索引对比例子：

sql
-- 语言：SQL
-- 只查索引列时，可能走 idx_phone 的全索引扫描 type=index
EXPLAIN SELECT phone FROM `user` WHERE phone LIKE '%1380000';

-- 查询完整行，通常更容易出现 ALL 或低效扫描
EXPLAIN SELECT * FROM `user` WHERE phone LIKE '%1380000';

如果业务必须要做前后都模糊的全匹配搜索，建议改用全文索引或者搜索引擎方案规避。例如短文本检索可评估 MySQL FULLTEXT，复杂中文搜索、多条件过滤、相关性排序更适合 Elasticsearch、OpenSearch 等系统。

避坑清单：

  • 不要把手机号、订单号、身份证号的“尾号搜索”直接写成 LIKE '%xxx'。
  • 如果只按固定后缀查询，可新增反转字段并建索引，例如 phone_reverse，查询 REVERSE('1380000') 的前缀。
  • 搜索类需求不要长期依赖 %keyword%，数据量增长后延迟和 IO 都会明显恶化。

2. 隐式类型转换失效 ​

也就是核心场景中的隐式转换：当索引字段是字符串类型，查询条件传入数字类型时，MySQL会自动将索引字段值转为数字做对比，导致索引值被运算破坏：

sql
-- 失效写法：phone是varchar类型，传入数字触发隐式转换
EXPLAIN SELECT * FROM `user` WHERE phone = 13800001234;
-- 优化写法：传入字符串常量，直接匹配索引
EXPLAIN SELECT * FROM `user` WHERE phone = '13800001234';

这类语句在 ORM 中非常隐蔽。例如 Java 实体类把 phone 声明成 Long，Go 结构体使用 int64，或者前端参数未经校验直接以数字传入，都可能让生成的 SQL 缺少引号。

可以在 EXPLAIN 的扩展结果中看到隐式转换痕迹：

sql
-- 语言：SQL
EXPLAIN SELECT * FROM `user` WHERE phone = 13800001234;
SHOW WARNINGS;

类似地，字符串字段与数字比较时，优化器可能将条件改写为：

sql
-- 语言：SQL
-- 概念等价形式：对索引列做 cast，索引有序性被破坏
WHERE CAST(phone AS SIGNED) = 13800001234

注意反向场景也要谨慎：如果索引列是数字，而参数传字符串，MySQL 通常会把字符串常量转成数字，常量侧转换一般不会破坏索引；但开发规范仍建议参数类型与字段类型严格一致，避免跨版本和排序规则带来的差异。

3. 索引字段参与函数/运算失效 ​

直接对索引字段调用函数、做算术运算，相当于修改了索引的原始值，引擎无法直接匹配B+树排序规则：

sql
-- 失效写法：对create_time字段调用date函数
EXPLAIN SELECT * FROM `user` WHERE DATE(create_time) = '2024-01-01';
-- 优化写法：把运算逻辑移到常量侧，保留索引字段原始匹配
EXPLAIN SELECT * FROM `user` WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';

更严谨的时间范围推荐使用半开区间，避免 DATETIME(6) 这类高精度时间在 23:59:59.999999 被漏掉：

sql
-- 语言：SQL
-- 推荐：半开区间，覆盖全天且不依赖字段精度
SELECT *
FROM `user`
WHERE create_time >= '2024-01-01 00:00:00'
  AND create_time <  '2024-01-02 00:00:00';

常见错误还包括：

sql
-- 语言：SQL
-- 对索引列做算术运算
SELECT * FROM `user` WHERE age + 1 = 20;

-- 推荐改写
SELECT * FROM `user` WHERE age = 19;

-- 对索引列使用函数
SELECT * FROM `user` WHERE LEFT(name, 4) = 'user';

-- 推荐前缀匹配
SELECT * FROM `user` WHERE name LIKE 'user%';

如果确实需要频繁按表达式过滤，MySQL 5.7+ 可考虑生成列并建索引，MySQL 8.0+ 也可使用函数索引：

sql
-- 语言：SQL
-- MySQL 8.0 函数索引示例
ALTER TABLE `user` ADD INDEX idx_date_create ((DATE(create_time)));

-- 此时与函数索引表达式完全一致，才可能命中
EXPLAIN
SELECT * FROM `user` WHERE DATE(create_time) = '2024-01-01';

函数索引能解决特定表达式查询，但会增加写入成本和存储空间，不应滥用。

剩余7类常见失效场景 ​

剩下的高频场景还包括：联合索引不遵循最左匹配原则跳过首列、OR条件中存在未加索引的字段、字符集排序规则不匹配导致隐式转换、范围查询后联合索引的后置列无法命中、索引字段使用负向查询（!=、not in）被优化器判定为命中过多放弃走索引、小表数据量低于全页缓存阈值触发全表扫描、不同数据库实例间字段字符集不一致引发隐式转换。

下面逐一补充可观测方法和实战建议。

4. 联合索引不满足最左匹配原则 ​

联合索引 (age, name, create_time) 可以理解为先按 age 排序，age 相同时再按 name 排序，前两列都相同时再按 create_time 排序。跳过 age 直接按 name 查，通常无法使用该索引的有序定位：

sql
-- 语言：SQL
-- 难以使用 idx_age_name_ctime 的前缀
EXPLAIN SELECT * FROM `user` WHERE name = 'user_100';

-- 可以使用联合索引第一列
EXPLAIN SELECT * FROM `user` WHERE age = 20;

-- 可以使用 age + name 前缀
EXPLAIN SELECT * FROM `user` WHERE age = 20 AND name = 'user_100';

需要注意，SQL 中条件书写顺序不影响最左匹配，优化器会重排 AND 条件；关键是索引列是否连续出现。例如下面两句通常等价：

sql
-- 语言：SQL
SELECT * FROM `user` WHERE name = 'user_100' AND age = 20;
SELECT * FROM `user` WHERE age = 20 AND name = 'user_100';

索引方案对比：

  • (age, name, create_time)：适合 age、age+name、age+name+create_time 以及最左前缀排序。
  • (name, age, create_time)：适合按姓名查询，再按年龄和时间过滤。
  • 为每个列分别建单列索引：写入成本高，且不一定适合多列组合查询。
  • 覆盖索引：若只查少量列，可把查询列纳入索引减少回表。

5. OR 两侧访问路径不一致 ​

OR 条件中只要有一个分支无法有效使用索引，优化器就可能为保证正确性选择全表扫描：

sql
-- 语言：SQL
-- 假设 remark 无索引，phone 虽有索引，也可能整体走 ALL
SELECT *
FROM `user`
WHERE phone = '13800000001'
   OR remark = 'VIP';

可选优化方式：

sql
-- 语言：SQL
-- 方案一：为 remark 增加合适索引
ALTER TABLE `user` ADD INDEX idx_remark(remark);

-- 方案二：改写 UNION，注意保留业务语义
SELECT * FROM `user` WHERE phone = '13800000001'
UNION
SELECT * FROM `user` WHERE remark = 'VIP';

UNION 会去重，成本可能较高；若能保证两个分支不重复或可接受重复，可评估 UNION ALL。不要机械改写，必须先确认结果集语义。

6. 字符集或排序规则不匹配 ​

跨表关联时，如果两个字段字符集或 collation 不同，MySQL 可能对其中一列执行转换，导致索引失效：

sql
-- 语言：SQL
CREATE TABLE user_ext (
  id INT PRIMARY KEY AUTO_INCREMENT,
  phone VARCHAR(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  KEY idx_phone(phone)
) ENGINE=InnoDB;

-- user.phone 是 utf8mb4_0900_ai_ci，user_ext.phone 是 utf8_general_ci
-- 关联时可能出现 collation 不匹配或隐式转换
SELECT u.id
FROM `user` u
JOIN user_ext e ON e.phone = u.phone;

推荐在建表规范中统一字符集和排序规则，例如 MySQL 8.0 统一使用 utf8mb4_0900_ai_ci，MySQL 5.7 常统一使用 utf8mb4_general_ci 或 utf8mb4_unicode_ci。已有大表修改字符集可能耗时很久，并产生 DDL 锁、主从延迟和磁盘空间压力，应使用在线 DDL 工具并在低峰执行。

7. 联合索引中范围查询后的列不能继续用于精准定位 ​

对于 (age, name, create_time)，如果 age 使用范围，后面的 name 通常不能再作为索引定位的等值前缀；如果 name 使用范围，后面的 create_time 也难以继续做精准范围定位：

sql
-- 语言：SQL
-- age 是范围，name/create_time 的利用会受限
EXPLAIN
SELECT *
FROM `user`
WHERE age > 20
  AND name = 'user_100'
  AND create_time >= '2024-01-01';

-- 更适合 idx_age_name_ctime 的写法：等值列在前，范围列在后
EXPLAIN
SELECT *
FROM `user`
WHERE age = 20
  AND name = 'user_100'
  AND create_time >= '2024-01-01';

设计联合索引时常用原则是：等值过滤列尽量靠前，范围过滤列靠后，排序列结合场景放置。但这不是绝对公式，还要考虑字段区分度、排序、分组和回表比例。

8. 负向条件命中过多或无法形成有效区间 ​

!=、NOT IN、NOT LIKE 描述的是“排除一小部分值”，剩余范围可能非常大，优化器往往认为全表扫描更便宜：

sql
-- 语言：SQL
-- 可能扫描大量记录
EXPLAIN
SELECT * FROM `user`
WHERE age NOT IN (18, 19, 20);

如果业务实际只想查少数值，优先改成正向范围或 IN：

sql
-- 语言：SQL
EXPLAIN
SELECT * FROM `user`
WHERE age BETWEEN 21 AND 67;

注意：负向条件并不必然“索引失效”。如果表很小、统计信息认为排除后命中很少，或者查询能被索引覆盖，优化器仍可能走索引。真正判断依据仍是执行计划和实际行数。

9. 小表全表扫描成本更低 ​

当表数据量很小，数据页本来就在缓冲池中，优化器可能认为走二级索引再回表反而多此一举：

sql
-- 语言：SQL
CREATE TABLE config_item (
  id INT PRIMARY KEY AUTO_INCREMENT,
  config_key VARCHAR(64) NOT NULL,
  config_value VARCHAR(255),
  KEY idx_key(config_key)
) ENGINE=InnoDB;

-- 只有几十行时，即使 config_key 有索引，也可能看到 type=ALL
EXPLAIN SELECT * FROM config_item WHERE config_key = 'max_retry';

这类场景不必强迫使用索引。顺序扫描少量聚簇页的成本可能只有毫秒级。量化上，若表只有几十个页、查询频率不高，全表扫描通常可接受；但当表增长到几十万、几百万行后，需要重新收集统计信息并验证执行计划。

10. 跨库、跨实例或 ETL 链路字段定义不一致 ​

分库分表、历史库迁移、多实例合并时，字段类型、字符集、默认 collation 可能不一致。应用层看似执行的是简单等值关联，实际却发生隐式转换：

sql
-- 语言：SQL
-- 实例 A：utf8mb4
-- 实例 B：latin1 或 utf8
-- 数据同步或联邦查询中关联字符串字段时，可能触发转换
SELECT a.*
FROM db_a.user a
JOIN db_b.user b ON a.phone = b.phone;

这类问题在线上很容易被忽略，因为单表查询正常，跨库关联或数据校验任务却很慢。建议把字段类型、长度、字符集、排序规则、是否允许 NULL、默认值纳入数据库设计评审，并通过 information_schema.COLUMNS 做自动巡检。

索引失效通用排查方法 ​

日常排查时直接在SQL前加EXPLAIN关键字，查看输出结果的key字段是否为预期的索引名，type字段如果为ALL则代表触发全表扫描，基本可以判定出现了索引失效问题。

重点关注以下字段：

字段关注点
typeALL、index 在大表上通常要重点分析
key实际选择的索引，NULL 表示没用索引
possible_keys可选索引列表，有候选但没选说明成本或写法可能有问题
key_len判断联合索引实际用了多少列
rows优化器预估扫描行数
filtered过滤后剩余比例，越小通常说明无效扫描越多
ExtraUsing where、Using index、Using filesort、Using temporary 等信息

进一步验证可以使用 MySQL 8.0 的实际执行分析：

sql
-- 语言：SQL
-- MySQL 8.0.18+
EXPLAIN ANALYZE
SELECT *
FROM `user`
WHERE create_time >= '2024-01-01'
  AND create_time <  '2024-01-03';

如果新建索引后优化器仍选择旧计划，可先更新统计信息：

sql
-- 语言：SQL
ANALYZE TABLE `user`;

如需强制验证某个索引的效果，可短期使用 FORCE INDEX，但不建议在代码中无监控地长期硬编码：

sql
-- 语言：SQL
EXPLAIN
SELECT *
FROM `user` FORCE INDEX(idx_age_name_ctime)
WHERE age = 20
  AND name = 'user_100';

通用排查顺序建议如下：

  1. 先确认表规模、区分度和返回行数，而不是只看 SQL 写法。
  2. 执行 EXPLAIN，确认 key、type、rows、Extra。
  3. 使用 SHOW WARNINGS 查看是否存在隐式转换。
  4. 检查字段类型、参数类型、字符集和 collation。
  5. 检查联合索引是否满足最左前缀，范围列是否阻断后续列。
  6. 评估是否可用覆盖索引减少回表。
  7. 改写 SQL 后在测试环境用相同数据分布验证耗时、扫描行数和执行计划。
  8. 上线后观察慢查询、CPU、IO、锁等待和主从延迟。

日常开发中很多索引失效场景藏在多表关联的复杂慢查询里，很难靠人工逐行排查定位，你可以使用本站的免费慢查询日志分析工具，上传全量慢日志文件就能自动识别所有未命中索引、隐式转换、前导模糊等问题，一键生成对应的优化建议，大幅降低SQL调优的排查成本。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。