一句口诀教你辨别索引失效七大场景

2022-10-19
中国香港
本文字数：2992 字
阅读完需：约 1 分钟

一、口诀

虚竹哥教你一句功法口诀：模型数或运最快

二、初始化数据

创建存储引擎为 InnoDB 的学生表

drop table if exists student;CREATE TABLE `student` (`id` int NOT NULL AUTO_INCREMENT COMMENT '自增ID',`order_num` bigint NOT NULL  COMMENT '序号',`student_name` varchar(20) NOT NULL COMMENT '姓名',`age` int COMMENT '年龄',`create_time` TIMESTAMP COMMENT '创建时间',`gender` int COMMENT '性别 0：男； 1：女； 2：其他', PRIMARY KEY (`id`) )ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;

复制代码

插入 100 万条数据,这里使用了存储过程，进行批量提交数据，先关闭自动提交，插入一定条数再进行提交。

--创建存储过程
drop procedure if exists add_student;
CREATE  PROCEDURE `add_student`(in n int,in batchNum int) BEGIN    DECLARE i INT DEFAULT 1;		 DECLARE age1 INT DEFAULT 1;		  DECLARE gender1 INT DEFAULT 1;	WHILE (i < n+1 ) DO	set age1=floor(18+( rand() * 5));	set gender1 = floor(rand() * 3);	set autocommit = 0;		INSERT into student (order_num,student_name,age,create_time,gender) VALUES (i,concat('student_name',i),age1,now(),gender1);		set i=i+1;		if i mod batchNum = 0 then                commit;     end if;	END WHILE;	 commit;END

复制代码

-- 调用CALL add_student(1000000,100000)

复制代码

三、口诀详解

模

like 模糊全匹配（like ‘%内容 %’），会导致全表扫描；like 模糊左匹配（like ‘%内容’），会导致全表扫描。

实战验证

查看 student 表的索引

show index from student;

复制代码

对 student_name 字段添加索引

CREATE INDEX idx_student_name ON student(student_name(20));

复制代码

再查看 student 表的索引

show index from student;

复制代码

测试：like 右匹配是可以命中索引的

explain select count(1)from student where   student_name like 'student_name1%'

复制代码

解析出来的 type 级别是 range

当查询条件使用索引检索某个范围的数据，典型的场景为使用=、<>、>、>=、<、<=、IS [NOT] NULL、<=>、BETWEEN AND 或者 IN 操作符时,类型为 range

like 左匹配：

explain select count(1)from student where student_name like '%student_name1'

复制代码

解析出来的 type 级别是 index

查询条件中的字段包含索引中的字段(含有非索引字段，就会是 ALL 了)，此时只需要扫描索引树。也是全表扫描的。

like 完全匹配：

explain select count(1)from student where student_name like '%student_name1%'

复制代码

解析出来的 type 级别是 index

查询条件中的字段包含索引中的字段(含有非索引字段，就会是 ALL 了)，此时只需要扫描索引树。也是全表扫描的。

测试结论

模字决索引失效成立。

型

代表数据类型。例如对字符串 name 字段加的索引，where 条件写 name=1，索引会失效。

实战验证

查看 student 表的索引

show index from student;

复制代码

对 student_name 字段添加索引

CREATE INDEX idx_student_name ON student(student_name(20));

复制代码

再查看 student 表的索引

show index from student;

复制代码

测试：

explain select count(1)from studentwhere student_name=1

复制代码

解析出来的 type 级别是 index

查询条件中的字段包含索引中的字段(含有非索引字段，就会是 ALL 了)，此时只需要扫描索引树。也是全表扫描的。

测试结论

型字决索引失效成立。

数

是函数的意思。对索引的字段使用内部函数，索引也会失效。这种情况下应该建立基于函数的索引。

SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;这里使用 DATE 函数

实战验证

查看 student 表的索引

show index from student;

复制代码

对 create_time 字段添加索引

CREATE INDEX idx_create_time ON student(create_time);

复制代码

再查看 student 表的索引

show index from student;

复制代码

测试：

explain SELECT count(1) FROM student WHERE DATE(create_time) = '2020-09-03';

复制代码

解析出来的 type 级别是 index

查询条件中的字段包含索引中的字段(含有非索引字段，就会是 ALL 了)，此时只需要扫描索引树。也是全表扫描的。

直接查 create_time 字段是可以的：

explain SELECT count(1) FROM student WHERE create_time = '2020-09-03';

复制代码

解析出来的 type 级别是 ref

当查询语句中的连接条件或者查询条件使用的索引不是主键和非空唯一索引，或者只是一个索引的一部分，则 type 的取值为 ref

测试结论

数字决索引失效成立。

或

在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；

实战验证

查看 student 表的索引

show index from student;

复制代码

对 order_num 字段添加索引

CREATE INDEX idx_order_num ON student(order_num);

复制代码

再查看 student 表的索引

show index from student;

复制代码

测试：

explain select count(1)from studentwhere order_num = 20 or student_name='student_name10'

复制代码

测试结论

或字决索引失效成立。

运

对索引的列进行运算，索引失效,例如：WHERE age+1=8;

实战验证

查看 student 表的索引

show index from student;

复制代码

对 age 字段添加索引

CREATE INDEX idx_age ON student(age);

复制代码

再查看 student 表的索引

show index from student;

复制代码

测试：

explain select count(1)from studentwhere age+1 = 20

复制代码

解析出来的 type 级别是 index

查询条件中的字段包含索引中的字段(含有非索引字段，就会是 ALL 了)，此时只需要扫描索引树。也是全表扫描的。

测试结论

运字决索引失效成立。

最

组合索引，查询时的条件列不是联合索引中的第一个列，索引失效（索引的最左原则）。

实战验证

查看 student 表的索引

show index from student;

复制代码

对 student_name,age,gender 字段添加组合索引

CREATE INDEX idx_student_name_age_gender ON student(student_name,age,gender);

复制代码

再查看 student 表的索引

show index from student;

复制代码

测试：查询条件中包含索引的第一列，索引生效：

explain select count(1)from studentwhere student_name ='student_name9527'and gender =1

复制代码

解析出来的 type 级别是 ref

当查询语句中的连接条件或者查询条件使用的索引不是主键和非空唯一索引，或者只是一个索引的一部分，则 type 的取值为 ref

索引生效，查询条件中包含索引的第一列，其他排列组合，大家可自行体验下。查询条件中不包含索引的第一列，索引不生效：

explain select count(1)from studentwhere age=20and gender =1

复制代码

测试结论

最字决索引失效成立。

快

查询数量是超过表的一部分,mysql30%,oracle 20%（这个数据可能不准确，不是官方说明，仅供参考），导致索引失效；

实战验证

show index from student;

复制代码

对 create_time 字段添加索引

CREATE INDEX idx_create_time ON student(create_time);

复制代码

再查看 student 表的索引

show index from student;

复制代码

测试：查出来的数据量少，可命中索引：

explain select *from studentwhere create_time >='2022-10-03 22:48:12' and create_time <='2022-10-03 22:48:13'

复制代码

解析出来的 type 级别是 range

当查询条件使用索引检索某个范围的数据，典型的场景为使用=、<>、>、>=、<、<=、IS [NOT] NULL、<=>、BETWEEN AND 或者 IN 操作符时,类型为 range。

查询出来的数据量多，会直接走全表：

explain select *from studentwhere create_time >='2022-10-03 22:48:12'

复制代码

测试结论

快字决索引失效成立。

点击关注，第一时间了解华为云新鲜技术~

发布于: 刚刚阅读数: 6

原文链接:【http://xie.infoq.cn/article/8c8c6f184c6b87676c99a372d】。文章转载请联系作者。

华为云开发者联盟

关注

提供全面深入的云计算技术干货 2020-07-14 加入

华为云开发者社区，提供全面深入的云计算前景分析、丰富的技术干货、程序样例，分享华为云前沿资讯动态，方便开发者快速成长与发展，欢迎提问、互动，多方位了解云计算！传送门：https://bbs.huaweicloud.com/

发布

暂无评论

创作场景

一句口诀教你辨别索引失效七大场景

一、口诀

二、初始化数据

三、口诀详解

模

实战验证

测试结论

型

实战验证

测试结论

数

实战验证

测试结论

或

实战验证

测试结论

运

实战验证

测试结论

最

实战验证

测试结论

快

实战验证

测试结论

华为云开发者联盟

评论