亿级数据查询突然变慢？揪出MySQL索引失效的7个“隐形刺客“-CSDN博客

"凌晨3点，监控大屏突然飙红——核心接口响应时间从200ms暴涨到12秒。
你颤抖着打开Explain计划，发现那个本该横扫百万数据的联合索引，此刻正在执行全表扫描。
这不是灵异事件，而是VARCHAR字段里藏着一个隐形的’%'符。
本文，将对MySQL索引失效情况进行详解，让我们一起把"隐形刺客"全部揪出来。

种一棵树最好的时间是10年前，其次就是现在，加油！ --by蜡笔小柯南

1. 数据准备

1.1 创建表

创建表名为tb_user 的表

create table tb_user(
	id int primary key auto_increment comment '主键',
	name varchar(50) not null comment '用户名',
	phone varchar(11) not null comment '手机号',
	email varchar(100) comment '邮箱',
	profession varchar(11) comment '专业',
	age tinyint unsigned comment '年龄',
	gender char(1) comment '性别 , 1: 男, 2: 女',
	status char(1) comment '状态',
	createtime datetime comment '创建时间'
) comment '系统用户表';

1.2 添加数据

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

1.3 创建索引

①：为phone 字段创建普通索引

CREATE INDEX idx_phone on tb_user(phone)

②：为 profession ,age , status 三个字段创建联合索引

CREATE INDEX idx_profession_age_status on tb_user(profession,age,status)

1.4 查看创建的索引

show index from tb_user;

创建好的索引如下所示
在这里插入图片描述

2. 索引失效情形

2.1 不满足最左前缀法则

如果索引了多列，即创建了联合索引，要遵守最左前缀法则

最左前缀法则是指：查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，后面的字段索引将会失效

在tb_user表中，有一个联合索引，由profession ,age , status 三个字段组成

对于最左前缀法则指的是，查询时，最左变的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效

2.1.1 索引生效的情况

① 查看执行计划，三个字段全部使用

explain SELECT * from tb_user where profession='软件工程' and age = 31 and status = '0';

在这里插入图片描述

possible_keys：可能用到的索引，idx_profession_age_status
key：实际用到的索引，idx_profession_age_status
key_len：索引长度，54

可以看到，用到了联合索引

② 使用profession、age字段查询

explain SELECT * from tb_user where profession='软件工程' and age = 31;

在这里插入图片描述

key_len：索引长度，49

③ 只使用profession字段

explain SELECT * from tb_user where profession='软件工程';

在这里插入图片描述

以上的这三组测试中，我们发现只要联合索引最左边的字段profession存在，索引就会生效，只不过索引的长度不同。而且由以上三组测试，我们也可以推测出profession字段索引长度为47、age字段索引长度为2、status字段索引长度为5

2.1.2 索引失效的情况

① 跳过age字段

explain SELECT * from tb_user where profession='软件工程' and status = '0';

在这里插入图片描述

key_len：索引长度，变成了 47

索引长度为47，说明只有profession索引生效，现在由于中间跳过了age字段，导致后边的索引不生效，即status没有生效

② 不使用profession字段，只使用age、status字段

explain SELECT * from tb_user where age = 31 and status = '0';

在这里插入图片描述
可以看到type是ALL，即没有走索引，走的是全表扫描

③ 只使用status字段

explain SELECT * from tb_user where status = '0';

在这里插入图片描述
可以看到type是ALL，也没有走索引，走的是全表扫描

通过上面②、③组测试，我们也可以看到索引并未生效，原因是因为不满足最左前缀法则，联合索引最左边的列profession不存在

通过①组测试，发现存在profession字段，最左边的列是存在的，索引满足最左前缀法则的基本条件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效，所以索引的长度就是47

2.2 范围查询

联合索引中，出现范围查询，如：>，<，范围查询右侧的列索引失效

explain SELECT * from tb_user where profession='软件工程' and age > 31 and status = '0';

在这里插入图片描述
索引长度是49，即profession索引长度 + age索引长度，status是没有走索引的

为避免失效，可使用 >=、<=

explain SELECT * from tb_user where profession='软件工程' and age >= 31 and status = '0';

在这里插入图片描述

当范围查询使用，>=或<=时，走联合索引了，索引的长度为54，就说明所有的字段都是走索引的

2.3 在索引列上进行函数计算

在索引列上进行运算操作，索引将失效

①：不是使用函数计算

explain select * from tb_user where phone = '17799990002';

在这里插入图片描述

②：使用函数计算

explain select * from tb_user where substring(phone,10,2) = '15';

在这里插入图片描述

通过以上对比，使用 substring 函数的 phone 索引列的执行计划，走的是全表扫描，索引失效；没有使用函数的正常走索引

2.4 字符串不加引号

字符串索引列在使用时，不加引号，索引将失效

为方便查看，这里先列出来索引字段的长度

profession：47
age：2
status：5
phone：46

①：正常使用联合索引

explain SELECT * from tb_user where profession='软件工程' and age = 31 and status = '0';

在这里插入图片描述
索引长度为54，profession + age + status = 47 + 2 + 5 = 54，全部走索引

②：status 不加引号

explain SELECT * from tb_user where profession='软件工程' and age = 31 and status = 0;

在这里插入图片描述
索引长度是49，profession + age = 47 + 2 = 49，status索引没有生效

③：phone 字段加引号

explain select * from tb_user where phone = '17799990002';

在这里插入图片描述
索引字段是46，走了索引

④：phone字段不加引号

explain select * from tb_user where phone = 17799990002;

在这里插入图片描述
走的全表扫描，索引失效

2.5 模糊查询

尾部模糊查询，索引不会失效；头部模糊查询和全模糊查询，索引失效

①：尾部模糊查询

explain SELECT * from tb_user where profession like '软件%';

在这里插入图片描述
索引长度是47，profession索引生效

②：头部模糊查询

explain SELECT * from tb_user where profession like '%软件';

在这里插入图片描述
走的全表扫描

③：全模糊查询

explain SELECT * from tb_user where profession like '%软件%';

在这里插入图片描述
走的全表扫描，索引失效

经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。

2.5 or连接条件

如果 or 的两侧中，一侧有索引，一侧没有索引，那么索引会失效

explain select * from tb_user where phone = '17799990002' or email = 'lvbu666@163.com';

explain select * from tb_user where id = 1 or email = 'lvbu666@163.com';

在这里插入图片描述

在这里插入图片描述
type类型是ALL，没有走索引，走的是全表扫描，id、phone有索引，email没有索引，导致索引失效

or 两边字段全都有索引时，索引才会生效

2.7 数据分布影响

如果MySQL评估使用索引比全表扫描更慢，则不使用索引，索引失效

情况①

explain select * from tb_user where phone >='17799990005';

在这里插入图片描述

情况②

explain select * from tb_user where phone >='17799990015';

在这里插入图片描述

我们的phone字段是有索引的，可以看到，情况①走的是全表扫描，没有走索引；情况②走的是索引

就是因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效

MySQL索引失效的7个"隐形刺客"已经被全部揪出，"此刻，你的EXPLAIN计划应该比凌晨三点的监控大屏更让人安心了——7个'隐形刺客'已全部落网，但数据库江湖从不缺新剧本。
在评论区留下你遇到的『最离谱索引失效案例』，你是怎么把它揪出的。
切勿将本文转发给CTO，否则他明天就会要求你——『照着这个清单，把全库索引给我梭哈一遍！』"

不管在任何时候，我希望你永远不要害怕挑战，不要畏惧失败。每一个错误都是向成功迈出的一步，每一个挑战都是成长的机会，因为每一次的努力，都会使我们离梦想更近一点。只要你行动起来，任何时候都不算晚。最后，把座右铭送给大家：种一棵树最好的时间是10年前，其次就是现在，加油！共勉 💪。