90% 的 MySQL 慢查询都栽在这！索引失效底层原理与精准修复全攻略

MySQL索引是提升查询性能的核心手段，但80%以上的线上慢查询问题，都源于索引失效。很多开发者只知道“这样写会索引失效”，却不懂背后的底层逻辑，导致频繁踩坑，排查问题全靠猜。

一、InnoDB索引的核心底层逻辑

1.1 索引的本质：有序的数据结构

索引的本质是排好序的快速查找数据结构，核心作用是减少磁盘IO，避免全表扫描。InnoDB存储引擎默认使用B+树作为索引结构，相比二叉树、红黑树、B树，B+树的核心优势在于：

• 非叶子节点只存储索引键和页指针，不存储行数据，单个页能存储更多索引项，树的高度更低（通常3-4层就能支撑千万级数据），磁盘IO次数更少。
• 叶子节点使用双向链表串联，所有索引键按升序排列，天然支持范围查询、排序操作，无需额外排序。
• 所有查询最终都会落到叶子节点，查询IO次数稳定，性能可预测。

1.2 聚簇索引与二级索引的结构

InnoDB中，索引分为聚簇索引（主键索引）和二级索引（非主键索引），两者的结构差异直接决定了索引的生效逻辑。

• 聚簇索引：叶子节点存储整行数据，索引键就是主键。一张表只能有一个聚簇索引，InnoDB会严格按照主键的顺序组织数据。如果没有定义主键，InnoDB会选择第一个非空唯一索引作为聚簇索引；如果都没有，会自动生成一个6字节的隐式主键ROWID。
• 二级索引：叶子节点只存储索引键的值和对应的主键值，不存储整行数据。通过二级索引查询时，先通过B+树找到对应的主键值，再通过聚簇索引查找整行数据，这个过程叫做回表。

1.3 索引生效的核心前提

所有索引失效的场景，本质上都是打破了索引生效的两个核心前提：

1. 查询条件能匹配索引的有序性：优化器可以通过索引的排序规则，确定扫描的起始和终止位置，缩小扫描范围。
2. 索引扫描的成本低于全表扫描：优化器基于成本模型计算，认为走索引的IO、CPU成本比全表扫描更低，才会选择使用索引。

只要这两个前提有一个不满足，索引就会失效。

二、EXPLAIN执行计划：索引失效定位的核心工具

要精准定位索引失效问题，必须吃透MySQL的EXPLAIN执行计划。在SQL前加上EXPLAIN关键字，MySQL会模拟优化器的执行过程，输出SQL的执行计划，不会真正执行SQL，完全安全。

下面是和索引失效强相关的核心字段，必须掌握：

key_len精准计算规则：

• 整数类型：int占4字节，bigint占8字节，tinyint占1字节，允许NULL则加1字节。
• 字符串类型：char(n) utf8mb4 占 n4 字节；varchar(n) utf8mb4 占 n4 + 2 字节（变长字段长度），允许NULL则再加1字节。

例如varchar(64) utf8mb4 NOT NULL，key_len为 64*4 +2 = 258字节，可通过该值精准判断联合索引的实际使用列数。

三、12个高频索引失效场景，底层原理与修复方案

本文所有SQL示例均基于以下测试表，可直接在MySQL 8.0环境执行：

CREATE TABLE `t_user` (
 `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
 `user_no` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户编号',
 `user_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户名',
 `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '手机号',
 `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
 `gender` tinyint NOT NULL DEFAULT '0' COMMENT '性别：0-未知，1-男，2-女',
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
 PRIMARY KEY (`id`),
 UNIQUE KEY `uk_user_no` (`user_no`),
 KEY `idx_user_name_age_gender` (`user_name`,`age`,`gender`),
 KEY `idx_create_time` (`create_time`),
 KEY `idx_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';


场景1：索引列上使用函数/表达式操作

底层原理：B+树中存储的是索引列的原始值，索引的有序性是基于原始值建立的。当在索引列上使用函数、算术表达式时，MySQL无法提前计算出所有行的转换结果，也就无法匹配索引的有序性，无法确定扫描范围，只能放弃索引进行全表扫描。

反例SQL：

-- 反例1：索引列使用函数
EXPLAIN SELECT * FROM t_user WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2026-04-08';
-- 反例2：索引列使用算术表达式
EXPLAIN SELECT * FROM t_user WHERE id + 1 = 1000;


执行计划特征：type字段为ALL，key字段为NULL，Extra字段出现Using where，代表全表扫描后再做条件过滤。

修复方案：将函数/表达式操作转移到常量端，保证索引列本身不被任何函数包裹，维持原始值的有序性。

正例SQL：

-- 正例1：范围查询替代函数操作
EXPLAIN SELECT * FROM t_user WHERE create_time >= '2026-04-08 00:00:00' AND create_time < '2026-04-09 00:00:00';
-- 正例2：表达式转换为常量等值匹配
EXPLAIN SELECT * FROM t_user WHERE id = 999;


正例执行计划特征：type字段为range/const，key字段为对应索引名，rows字段大幅降低，仅扫描符合条件的行。

场景2：隐式类型转换

底层原理：当查询条件中，索引列的类型和传入值的类型不匹配时，MySQL会自动触发隐式类型转换。如果转换操作作用在索引列上，就等同于对索引列使用了函数，打破了索引的有序性，导致索引失效。

MySQL隐式转换核心规则：当字符串类型和数字类型做比较时，MySQL会将字符串统一转换为数字再进行比较。

• 情况1：索引列是字符串类型，传入的是数字。MySQL会把索引列的字符串值转为数字，对索引列做了转换，索引失效。
• 情况2：索引列是数字类型，传入的是字符串。MySQL会把字符串常量转为数字，对常量做转换，不影响索引列的有序性，索引正常生效。

反例SQL：

-- 反例：索引列phone是varchar类型，传入数字13800138000，触发隐式转换
EXPLAIN SELECT * FROM t_user WHERE phone = 13800138000;


执行计划特征：type字段为ALL，key字段为NULL，possible_keys字段出现idx_phone但优化器最终放弃使用。

修复方案：保证查询条件的值的类型和索引列的类型完全一致，避免隐式转换。

正例SQL：

-- 正例：传入字符串类型的值，和索引列类型一致
EXPLAIN SELECT * FROM t_user WHERE phone = '13800138000';


正例执行计划特征：type字段为ref，key字段为idx_phone，ref字段为const，成功使用索引等值匹配。

场景3：模糊查询以通配符%开头

底层原理：B+树的索引是按照索引值的前缀进行排序的，只有前缀匹配才能利用索引的有序性。当模糊查询以%或_开头时，索引的前缀是不确定的，MySQL无法匹配索引的排序规则，只能进行全表扫描。

反例SQL：

-- 反例1：%开头的模糊查询
EXPLAIN SELECT * FROM t_user WHERE user_name LIKE '%果酱';
-- 反例2：前后都有%的模糊查询
EXPLAIN SELECT * FROM t_user WHERE user_name LIKE '%果酱%';


执行计划特征：type字段为ALL，key字段为NULL，全表扫描。

特殊情况说明：如果查询的列全部包含在索引中（覆盖索引），即使是%开头的模糊查询，MySQL也会走全索引扫描（type=index），比全表扫描性能好，但依然不是最优的范围扫描。

-- 覆盖索引场景，走全索引扫描
EXPLAIN SELECT user_name, age, gender FROM t_user WHERE user_name LIKE '%果酱%';


执行计划中type为index，key为idx_user_name_age_gender，Extra为Using where; Using index，使用了覆盖索引，无需回表。

修复方案：

1. 前缀匹配查询：将通配符放到后缀，使用前缀匹配，正常走索引。
2. 反转索引+前缀匹配：针对后缀匹配的场景，将字段值反转存储，把后缀匹配转为前缀匹配。
3. 全文索引：针对大文本模糊查询，使用MySQL内置的FULLTEXT全文索引，或接入专业搜索引擎。

正例SQL：

-- 正例1：前缀匹配，正常走索引
EXPLAIN SELECT * FROM t_user WHERE user_name LIKE '果酱%';
-- 正例2：反转索引实现后缀匹配
ALTER TABLE t_user ADD COLUMN user_name_reverse varchar(64) GENERATED ALWAYS AS (REVERSE(user_name)) STORED COMMENT '用户名反转值';
CREATE INDEX idx_user_name_reverse ON t_user(user_name_reverse);
-- 查询时反转常量，转为前缀匹配
EXPLAIN SELECT * FROM t_user WHERE user_name_reverse LIKE CONCAT(REVERSE('果酱'), '%');


场景4：联合索引不满足最左前缀原则

底层原理：联合索引的B+树是按照索引定义的列顺序进行排序的：先按第一列排序，第一列值相同的情况下，再按第二列排序，以此类推。

最左前缀原则的本质是：查询条件必须匹配联合索引的从左到右的连续列，才能充分利用索引。如果跳过了中间的列，只能用到最左匹配的列，后面的列无法利用索引；如果不包含最左列，联合索引完全失效。

核心规则：

• 匹配最左N个连续列，索引可以充分利用。
• 匹配最左列+中间列的等值匹配+后面列的范围匹配，可以利用到范围列之前的所有列。
• 跳过中间列，只能用到最左匹配的列。
• 不包含最左列，联合索引完全失效。

本场景基于联合索引idx_user_name_age_gender(user_name, age, gender)演示。

反例SQL：

-- 反例1：不包含最左列，联合索引完全失效
EXPLAIN SELECT * FROM t_user WHERE age = 20 AND gender = 1;
-- 反例2：跳过中间列，仅能用到最左的user_name列
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' AND gender = 1;


反例执行计划特征：

• 反例1：type=ALL，key=NULL，完全未使用索引。
• 反例2：type=ref，key_len=258，仅用到了user_name列，gender列未用到。

修复方案：调整查询条件，匹配联合索引的最左前缀原则；或调整联合索引的列顺序，匹配高频查询场景。

正例SQL：

-- 正例1：匹配最左连续列，充分利用索引
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' AND age = 20;
-- 正例2：匹配全部列，索引完全利用
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' AND age = 20 AND gender = 1;
-- 正例3：最左列等值匹配+第二列范围匹配，用到前两列
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' AND age BETWEEN 20 AND 30;


正例执行计划特征：

• 正例1：key_len=262，用到了前两列。
• 正例2：key_len=263，用到了全部三列。
• 正例3：type=range，key_len=262，用到了前两列，范围列之后的列无法用到。

场景5：索引列使用不等于、not in、is not null

底层原理：不等于（!=、<>）、not in、is not null这类查询，无法确定索引的扫描范围，优化器会预估扫描的行数。当预估需要扫描的行数占表总行数的比例较高（通常超过20%）时，优化器会认为走索引+回表的成本高于全表扫描，从而放弃索引。

关键区分：

• is null 可以正常使用索引，因为B+树会存储NULL值，等值匹配可以确定扫描范围。
• 主键、唯一索引的不等于查询，通常可以走range范围扫描，因为选择性极高，扫描行数少。
• 普通索引的不等于查询，大概率会全表扫描。

反例SQL：

-- 反例1：普通索引列使用不等于
EXPLAIN SELECT * FROM t_user WHERE user_name != '果酱';
-- 反例2：普通索引列使用not in
EXPLAIN SELECT * FROM t_user WHERE age NOT IN (20,21,22);
-- 反例3：索引列使用is not null
EXPLAIN SELECT * FROM t_user WHERE phone IS NOT NULL;


执行计划特征：type=ALL，key=NULL，possible_keys有值但优化器放弃索引。

修复方案：

1. 主键/唯一索引的不等于查询，无需修改，正常走索引。
2. 普通索引场景，使用覆盖索引，避免回表，优化器会选择走全索引扫描。
3. 拆分查询，将不等于查询拆分为多个范围查询，缩小扫描范围。

正例SQL：

-- 正例1：使用覆盖索引，避免回表
EXPLAIN SELECT user_name, age, gender FROM t_user WHERE user_name != '果酱';
-- 正例2：拆分范围查询
EXPLAIN SELECT * FROM t_user WHERE age < 20 OR age > 22;
-- 正例3：主键不等于查询，正常走索引
EXPLAIN SELECT * FROM t_user WHERE id != 1000;


场景6：使用OR连接包含非索引列的条件

底层原理：OR查询需要满足两个条件中的任意一个。如果OR两边的条件，有一个列没有索引，即使另一个列有索引，MySQL也无法通过索引获取所有符合条件的行，必须进行全表扫描，从而导致索引失效。

关键区分：如果OR两边的列都建立了索引，MySQL会使用Index Merge（索引合并） 优化，分别从两个索引中获取符合条件的行，再合并结果集，索引可以正常生效。

反例SQL：

-- 反例：OR两边，user_name有索引，age无单独索引，索引失效
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' OR age = 20;


执行计划特征：type=ALL，key=NULL，全表扫描。

修复方案：

1. 给OR两边的列都建立索引，触发Index Merge优化。
2. 拆分两个查询，使用UNION ALL合并结果集，避免全表扫描。

正例SQL：

-- 正例1：给age列建立索引，触发Index Merge
CREATE INDEX idx_age ON t_user(age);
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' OR age = 20;
-- 正例2：使用UNION ALL拆分查询
EXPLAIN
SELECT * FROM t_user WHERE user_name = '果酱'
UNION ALL
SELECT * FROM t_user WHERE age = 20;


正例执行计划特征：

• 正例1：type=index_merge，key=idx_user_name_age_gender,idx_age，Extra=Using union(idx_user_name_age_gender,idx_age)，成功使用索引合并。
• 正例2：两个子查询都走了索引，合并结果集，无全表扫描。

场景7：优化器选错索引

底层原理：MySQL优化器选择索引的核心依据是成本估算，会计算不同索引的扫描行数、IO成本、CPU成本，选择成本最低的执行方案。当表的统计信息不准确、索引的基数（Cardinality）失真、多个索引的成本接近时，优化器可能会选错索引，甚至放弃索引走全表扫描。

典型场景：

• 表的统计信息未及时更新，比如大批量插入/删除数据后，索引基数统计失真。
• 多个索引都能匹配查询条件，优化器估算错误，选择了选择性更低的索引。

反例特征：EXPLAIN结果中，possible_keys有多个可选索引，key字段选择了非最优的索引，甚至为NULL。

修复方案：

1. 更新表的统计信息，让优化器获取准确的索引基数。
2. 使用FORCE INDEX强制指定索引，避免优化器选错。
3. 调整索引结构，删除冗余索引，减少优化器的选择成本。

示例SQL：

-- 1. 更新表的统计信息
ANALYZE TABLE t_user;
-- 2. 强制指定索引
EXPLAIN SELECT * FROM t_user FORCE INDEX (idx_user_name_age_gender) WHERE user_name = '果酱' AND age = 20;


场景8：使用SELECT * 无法利用覆盖索引，导致索引失效

底层原理：二级索引的叶子节点只存储索引键和主键值，如果查询的列不在二级索引中，就需要通过主键回表查询聚簇索引，获取完整行数据。

当使用SELECT * 时，通常查询的列都不在二级索引中，必须回表。当优化器预估需要回表的行数较多时，会认为回表的随机IO成本高于全表扫描的顺序IO成本，从而放弃索引，走全表扫描。

反例SQL：

-- 反例：SELECT * 需要回表，数据量较大时优化器放弃索引
EXPLAIN SELECT * FROM t_user WHERE age BETWEEN 20 AND 30;


执行计划特征：type=ALL，key=NULL，全表扫描。

修复方案：

1. 只查询业务需要的列，避免SELECT * 。
2. 建立覆盖索引，将查询的所有列都包含在索引中，避免回表。

正例SQL：

-- 正例1：只查询需要的列，建立覆盖索引
CREATE INDEX idx_age_name ON t_user(age, user_name);
EXPLAIN SELECT user_name, age FROM t_user WHERE age BETWEEN 20 AND 30;


正例执行计划特征：type=range，key=idx_age_name，Extra=Using index，使用了覆盖索引，无需回表，性能最优。

场景9：关联查询字段字符集不匹配，触发隐式转换

底层原理：多表JOIN查询时，如果关联字段的字符集不匹配，MySQL会自动对索引列做字符集转换，等同于对索引列使用了函数，打破了索引的有序性，导致索引失效。高频场景为一张表用utf8mb4字符集，另一张表用utf8字符集，关联字段分别为两个字符集，触发隐式转换。

反例示例： 先建一张字符集为utf8的订单表：

CREATE TABLE `t_order` (
 `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
 `order_no` varchar(32) NOT NULL COMMENT '订单编号',
 `user_no` varchar(32) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '用户编号',
 `order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
 `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 PRIMARY KEY (`id`),
 KEY `idx_user_no` (`user_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='订单表';


关联查询，t_user的user_no是utf8mb4，t_order的user_no是utf8，字符集不匹配：

-- 反例：关联字段字符集不匹配，t_order的user_no索引失效
EXPLAIN SELECT * FROM t_user u
LEFT JOIN t_order o ON u.user_no = o.user_no
WHERE u.user_name = '果酱';


执行计划特征：驱动表t_user走了索引，被驱动表t_order的type=ALL，key=NULL，全表扫描，关联字段的索引失效。

修复方案：统一关联字段的字符集，确保JOIN两边的字段字符集完全一致。

正例SQL：

-- 统一字符集为utf8mb4
ALTER TABLE t_order MODIFY COLUMN user_no varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '用户编号';
-- 重新执行关联查询
EXPLAIN SELECT * FROM t_user u
LEFT JOIN t_order o ON u.user_no = o.user_no
WHERE u.user_name = '果酱';


正例执行计划特征：被驱动表t_order的type=ref，key=idx_user_no，成功使用索引，关联查询性能大幅提升。

场景10：ORDER BY/GROUP BY与索引顺序不一致，导致索引失效

底层原理：B+树的索引本身是有序的，如果ORDER BY/GROUP BY的列顺序、排序方向和联合索引完全一致，MySQL可以直接利用索引的有序性，避免额外的排序操作（Using filesort）。

如果ORDER BY/GROUP BY的列顺序与索引不一致，或者排序方向不统一，MySQL无法利用索引的有序性，必须在内存中进行filesort排序，当数据量较大时，会严重影响性能，甚至导致优化器放弃索引。

核心规则：

• ORDER BY的列必须和联合索引的列顺序完全一致。
• 所有列的排序方向必须统一（全ASC或全DESC，MySQL 8.0支持反向扫描，全DESC也可以利用索引）。
• 必须先满足最左前缀的等值匹配，才能利用索引的有序性。

反例SQL：

-- 反例1：ORDER BY列顺序与联合索引不一致
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' ORDER BY gender, age;
-- 反例2：排序方向不统一
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' ORDER BY age ASC, gender DESC;
-- 反例3：GROUP BY列顺序与索引不一致
EXPLAIN SELECT age, gender, COUNT(*) FROM t_user WHERE user_name = '果酱' GROUP BY gender, age;


执行计划特征：Extra字段出现Using filesort，代表触发了文件排序，无法利用索引的有序性。

修复方案：调整ORDER BY/GROUP BY的列顺序和排序方向，与联合索引完全一致。

正例SQL：

-- 正例1：ORDER BY顺序与索引一致，排序方向统一
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' ORDER BY age ASC, gender ASC;
-- 正例2：全DESC排序，MySQL 8.0支持反向扫描，利用索引有序性
EXPLAIN SELECT * FROM t_user WHERE user_name = '果酱' ORDER BY age DESC, gender DESC;
-- 正例3：GROUP BY顺序与索引一致，避免排序
EXPLAIN SELECT age, gender, COUNT(*) FROM t_user WHERE user_name = '果酱' GROUP BY age, gender;


正例执行计划特征：Extra字段无Using filesort，成功利用索引的有序性，无需额外排序。

场景11：索引列选择性过低，优化器放弃索引

底层原理：索引的选择性=count(distinct 列名)/count(*)，越接近1，选择性越高，索引的过滤效果越好。如果索引列的选择性过低（比如性别字段，只有2-3个值，选择性接近0.5），即使建立了索引，优化器也会认为走索引需要扫描大量的行，还要回表，成本高于全表扫描，从而放弃索引。

反例SQL：

-- 反例：gender列选择性极低，索引失效
EXPLAIN SELECT * FROM t_user WHERE gender = 1;


执行计划特征：type=ALL，key=NULL，possible_keys有值但优化器放弃使用。

修复方案：

1. 不单独给低选择性字段建索引，和高选择性字段建立联合索引，提升整体选择性。
2. 使用覆盖索引，避免回表，优化器会选择走索引。

正例SQL：

-- 正例1：建立高选择性+低选择性的联合索引
CREATE INDEX idx_gender_create_time ON t_user(gender, create_time);
EXPLAIN SELECT * FROM t_user WHERE gender = 1 AND create_time >= '2026-04-01';
-- 正例2：使用覆盖索引
EXPLAIN SELECT gender, COUNT(*) FROM t_user WHERE gender = 1 GROUP BY gender;


场景12：子查询结果集过大，导致索引失效

底层原理：MySQL 8.0对子查询做了大量优化，大部分IN子查询都会被优化为JOIN操作。但如果子查询的结果集过大，优化器会选择将驱动表和被驱动表反转，或者选择全表扫描，导致索引失效。

关键纠正：MySQL 8.0中，IN和EXISTS的执行计划基本一致，不存在“EXISTS一定比IN好”的说法，优化器会自动做语义等价转换。

反例SQL：

-- 反例：子查询结果集过大，索引失效
EXPLAIN SELECT * FROM t_user WHERE id IN (SELECT user_id FROM t_order WHERE order_amount > 100);


执行计划特征：type=ALL，key=NULL，全表扫描。

修复方案：

1. 确保子查询的关联字段有索引，优化器可以正常做JOIN优化。
2. 结果集过大时，使用JOIN查询替代子查询，明确关联关系。

正例SQL：

-- 正例：使用INNER JOIN替代子查询，确保关联字段有索引
EXPLAIN SELECT DISTINCT u.* FROM t_user u
INNER JOIN t_order o ON u.id = o.user_id
WHERE o.order_amount > 100;


四、索引失效问题的完整排查与修复流程

详细排查步骤：

1. 获取执行计划：在慢SQL前加上EXPLAIN，执行获取完整的执行计划。
2. 判断是否索引失效：查看type字段，如果是ALL（全表扫描）或index（全索引扫描），代表索引失效或未充分利用。
3. 判断索引是否被使用：查看key字段，如果为NULL，代表完全未使用索引；如果有值，代表使用了索引，但可能未充分利用。
4. 判断索引利用程度：通过key_len计算实际用到的索引列数，对比索引的总长度，确定是否存在最左前缀截断、范围列截断等问题。
5. 获取额外提示：查看Extra字段，是否有Using where、Using filesort、Using temporary等提示，定位具体问题。
6. 匹配失效场景：结合执行计划的特征，匹配前面的12个高频失效场景，确定根本原因。
7. 修复与验证：制定修复方案，修改SQL或索引结构，重新执行EXPLAIN验证，直到索引正常生效。
8. 上线与监控：修复方案上线后，监控慢查询日志，确认性能提升。

五、Java实战

以下代码基于Spring Boot 3.2.5、MyBatis-Plus 3.5.7、JDK 17。

5.1 Maven核心依赖

<dependencies>
   <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-web</artifactId>
       <version>3.2.5</version>
   </dependency>
   <dependency>
       <groupId>com.baomidou</groupId>
       <artifactId>mybatis-plus-boot-starter</artifactId>
       <version>3.5.7</version>
   </dependency>
   <dependency>
       <groupId>com.mysql</groupId>
       <artifactId>mysql-connector-j</artifactId>
       <version>8.4.0</version>
       <scope>runtime</scope>
   </dependency>
   <dependency>
       <groupId>org.springdoc</groupId>
       <artifactId>springdoc-openapi-starter-webmvc-ui</artifactId>
       <version>2.5.0</version>
   </dependency>
   <dependency>
       <groupId>org.projectlombok</groupId>
       <artifactId>lombok</artifactId>
       <version>1.18.32</version>
       <scope>provided</scope>
   </dependency>
   <dependency>
       <groupId>com.alibaba.fastjson2</groupId>
       <artifactId>fastjson2</artifactId>
       <version>2.0.52</version>
   </dependency>
   <dependency>
       <groupId>com.google.guava</groupId>
       <artifactId>guava</artifactId>
       <version>33.1.0-jre</version>
   </dependency>
   <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-core</artifactId>
       <version>6.1.6</version>
   </dependency>
</dependencies>


5.2 实体类

package com.jam.demo.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.v3.oas.annotations.media.Schema;
import lombok.Data;

import java.time.LocalDateTime;

/**
* 用户实体类
*
* @author ken
* @date 2026-04-08
*/
@Data
@TableName("t_user")
@Schema(description = "用户实体")
public class User {

   @TableId(type = IdType.AUTO)
   @Schema(description = "主键ID", example = "1")
   private Long id;

   @TableField("user_no")
   @Schema(description = "用户编号", example = "U20260408001")
   private String userNo;

   @TableField("user_name")
   @Schema(description = "用户名", example = "果酱")
   private String userName;

   @TableField("phone")
   @Schema(description = "手机号", example = "13800138000")
   private String phone;

   @TableField("age")
   @Schema(description = "年龄", example = "25")
   private Integer age;

   @TableField("gender")
   @Schema(description = "性别：0-未知，1-男，2-女", example = "1")
   private Integer gender;

   @TableField("create_time")
   @Schema(description = "创建时间")
   private LocalDateTime createTime;

   @TableField("update_time")
   @Schema(description = "更新时间")
   private LocalDateTime updateTime;
}


5.3 Mapper接口

package com.jam.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jam.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.time.LocalDateTime;
import java.util.List;

/**
* 用户Mapper接口
*
* @author ken
* @date 2026-04-08
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {

   /**
    * 反例：索引列使用函数，导致索引失效
    *
    * @param date 日期字符串，格式yyyy-MM-dd
    * @return 用户列表
    */
   List<User> selectByCreateTimeFunc(@Param("date") String date);

   /**
    * 正例：范围查询，正常使用索引
    *
    * @param startTime 开始时间
    * @param endTime 结束时间
    * @return 用户列表
    */
   List<User> selectByCreateTimeRange(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

   /**
    * 反例：隐式类型转换，索引失效
    *
    * @param phone 手机号数字类型
    * @return 用户信息
    */
   User selectByPhoneNumber(@Param("phone") Long phone);

   /**
    * 正例：类型匹配，正常使用索引
    *
    * @param phone 手机号字符串类型
    * @return 用户信息
    */
   User selectByPhoneString(@Param("phone") String phone);
}


5.4 Mapper XML文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.jam.demo.mapper.UserMapper">
   <resultMap id="BaseResultMap" type="com.jam.demo.entity.User">
       <id column="id" property="id" jdbcType="BIGINT"/>
       <result column="user_no" property="userNo" jdbcType="VARCHAR"/>
       <result column="user_name" property="userName" jdbcType="VARCHAR"/>
       <result column="phone" property="phone" jdbcType="VARCHAR"/>
       <result column="age" property="age" jdbcType="INTEGER"/>
       <result column="gender" property="gender" jdbcType="TINYINT"/>
       <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
       <result column="update_time" property="updateTime" jdbcType="TIMESTAMP"/>
   </resultMap>

   <select id="selectByCreateTimeFunc" resultMap="BaseResultMap">
       SELECT * FROM t_user WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = #{date}
   </select>

   <select id="selectByCreateTimeRange" resultMap="BaseResultMap">
       SELECT * FROM t_user WHERE create_time >= #{startTime} AND create_time < #{endTime}
   </select>

   <select id="selectByPhoneNumber" resultMap="BaseResultMap">
       SELECT * FROM t_user WHERE phone = #{phone}
   </select>

   <select id="selectByPhoneString" resultMap="BaseResultMap">
       SELECT * FROM t_user WHERE phone = #{phone}
   </select>
</mapper>


5.5 Service接口与实现

package com.jam.demo.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.jam.demo.entity.User;

import java.time.LocalDate;
import java.util.List;

/**
* 用户服务接口
*
* @author ken
* @date 2026-04-08
*/
public interface UserService extends IService<User> {

   /**
    * 反例：通过日期函数查询用户，索引失效
    *
    * @param date 日期
    * @return 用户列表
    */
   List<User> getUserListByDateFunc(LocalDate date);

   /**
    * 正例：通过日期范围查询用户，正常使用索引
    *
    * @param date 日期
    * @return 用户列表
    */
   List<User> getUserListByDateRange(LocalDate date);

   /**
    * 反例：通过数字类型手机号查询用户，索引失效
    *
    * @param phone 手机号
    * @return 用户信息
    */
   User getUserByPhoneNumber(Long phone);

   /**
    * 正例：通过字符串类型手机号查询用户，正常使用索引
    *
    * @param phone 手机号
    * @return 用户信息
    */
   User getUserByPhoneString(String phone);
}


package com.jam.demo.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.jam.demo.entity.User;
import com.jam.demo.mapper.UserMapper;
import com.jam.demo.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
* 用户服务实现类
*
* @author ken
* @date 2026-04-08
*/
@Slf4j
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

   @Override
   public List<User> getUserListByDateFunc(LocalDate date) {
       if (ObjectUtils.isEmpty(date)) {
           return List.of();
       }
       String dateStr = date.toString();
       log.info("反例：通过日期函数查询用户，日期：{}", dateStr);
       return baseMapper.selectByCreateTimeFunc(dateStr);
   }

   @Override
   public List<User> getUserListByDateRange(LocalDate date) {
       if (ObjectUtils.isEmpty(date)) {
           return List.of();
       }
       LocalDateTime startTime = date.atStartOfDay();
       LocalDateTime endTime = date.plusDays(1).atStartOfDay();
       log.info("正例：通过日期范围查询用户，开始时间：{}，结束时间：{}", startTime, endTime);
       return baseMapper.selectByCreateTimeRange(startTime, endTime);
   }

   @Override
   public User getUserByPhoneNumber(Long phone) {
       if (ObjectUtils.isEmpty(phone)) {
           return null;
       }
       log.info("反例：通过数字类型手机号查询用户，手机号：{}", phone);
       return baseMapper.selectByPhoneNumber(phone);
   }

   @Override
   public User getUserByPhoneString(String phone) {
       if (!org.springframework.util.StringUtils.hasText(phone)) {
           return null;
       }
       log.info("正例：通过字符串类型手机号查询用户，手机号：{}", phone);
       return baseMapper.selectByPhoneString(phone);
   }
}


5.6 Controller层

package com.jam.demo.controller;

import com.jam.demo.entity.User;
import com.jam.demo.service.UserService;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.Parameter;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.*;

import java.time.LocalDate;
import java.util.List;

/**
* 用户控制器
*
* @author ken
* @date 2026-04-08
*/
@RestController
@RequestMapping("/user")
@RequiredArgsConstructor
@Tag(name = "用户管理", description = "用户相关接口")
public class UserController {

   private final UserService userService;

   @GetMapping("/list/func")
   @Operation(summary = "反例：日期函数查询用户（索引失效）", description = "索引列使用DATE_FORMAT函数，导致索引失效")
   public List<User> getUserListByFunc(
           @Parameter(description = "查询日期，格式yyyy-MM-dd", example = "2026-04-08")
           @RequestParam LocalDate date) {
       return userService.getUserListByDateFunc(date);
   }

   @GetMapping("/list/range")
   @Operation(summary = "正例：日期范围查询用户（索引生效）", description = "使用范围查询，正常使用索引")
   public List<User> getUserListByRange(
           @Parameter(description = "查询日期，格式yyyy-MM-dd", example = "2026-04-08")
           @RequestParam LocalDate date) {
       return userService.getUserListByDateRange(date);
   }

   @GetMapping("/phone/number")
   @Operation(summary = "反例：数字类型手机号查询（索引失效）", description = "字符串索引列传入数字，触发隐式类型转换，索引失效")
   public User getUserByPhoneNumber(
           @Parameter(description = "手机号", example = "13800138000")
           @RequestParam Long phone) {
       return userService.getUserByPhoneNumber(phone);
   }

   @GetMapping("/phone/string")
   @Operation(summary = "正例：字符串类型手机号查询（索引生效）", description = "类型匹配，正常使用索引")
   public User getUserByPhoneString(
           @Parameter(description = "手机号", example = "13800138000")
           @RequestParam String phone) {
       return userService.getUserByPhoneString(phone);
   }
}


六、索引设计与使用的最佳实践

1. 索引设计三原则

• 高选择性优先：优先给选择性高的字段建索引，低选择性字段不单独建索引，和高选择性字段建联合索引。
• 最左匹配优先：联合索引的列顺序，按照高频查询场景、字段选择性从高到低排列。
• 覆盖索引优先：针对高频查询，建立覆盖索引，避免回表，性能最优。

2. SQL编写避坑规范

• 禁止在索引列上使用函数、算术表达式，避免隐式类型转换、隐式字符集转换。
• 模糊查询禁止以%开头，如需后缀匹配，使用反转索引方案。
• 禁止使用SELECT *，只查询业务需要的列。
• OR查询确保两边的列都有索引，或使用UNION ALL拆分。
• ORDER BY/GROUP BY的列顺序与联合索引保持一致，避免Using filesort。

3. 索引维护规范

• 单表索引数量控制在5个以内，避免冗余索引、重复索引。
• 大批量数据更新后，执行ANALYZE TABLE更新统计信息，避免优化器选错索引。
• 定期监控慢查询日志，排查索引失效问题，优化索引结构。

结尾

MySQL索引失效的核心本质，就是打破了索引的有序性，或者优化器认为索引扫描的成本高于全表扫描。本文从B+树的底层原理出发，拆解了12个高频索引失效场景的本质，通过EXPLAIN执行计划精准定位问题。