一句口訣教你辨別索引失效七大場景-有解無憂

摘要：本文將通過一句口訣，教你如何辨別索引失效，

本文分享自華為云社區《虛竹哥教你一句口訣辨別索引失效七大場景》，作者：小虛竹，

一、口訣

教你一句功法口訣：模型數或運最快

二、初始化資料

創建存盤引擎為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 student
where 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 student
where 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 student
where 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 student
where student_name ='student_name9527'
and gender =1

決議出來的type級別是ref

當查詢陳述句中的連接條件或者查詢條件使用的索引不是主鍵和非空唯一索引，或者只是一個索引的一部分，則type的取值為ref

索引生效，查詢條件中包含索引的第一列，其他排列組合，大家可自行體驗下，
查詢條件中不包含索引的第一列，索引不生效：

explain select count(1)
from student
where age=20
and 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 student
where 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 student
where create_time >='2022-10-03 22:48:12'

測驗結論

快字決索引失效成立，

點擊關注，第一時間了解華為云新鮮技術~

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/518589.html

標籤：其他

上一篇：一句口訣教你辨別索引失效七大場景

下一篇：多表連接查詢