索引失效情況

1. 最佳左前綴法則：如果索引了多列，要遵循最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列。
2. 不在索引列上做任何計算、函數操作，會導致索引失效而轉向全表掃描
3. 存儲引擎不能使用索引中范圍條件右邊的列
4. MySQL在使用不等于時無法使用索引會導致全表掃描
5. is null 可以使用索引，但是 is not null 無法使用索引
6. like 以通配符開頭會使索引失效導致全表掃描
7. 字符串不加單引號，索引會失效
8. 使用 or 鏈接時索引失效

drop table if exists students;
CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT COMMENT "主鍵id",sname VARCHAR (24) COMMENT '學生姓名',age INT COMMENT '年齡',score INT COMMENT '分數',time TIMESTAMP COMMENT '入學時間'
);INSERT INTO students(sname,age,score,time) VALUES('小明',22,100,now());
INSERT INTO students(sname,age,score,time) VALUES('小紅',23,80,now());
INSERT INTO students(sname,age,score,time) VALUES('小綠',24,80,now());
INSERT INTO students(sname,age,score,time) VALUES('黑',23,70,now());alter table students add index idx_sname_age_score(sname,age,score);-- 索引失效情況
explain select * from students where sname="小明" and age = 22 and score = 100;
explain select * from students where sname="小明" and age = 22;
explain select * from students where sname="小明";
explain select * from students where sname="小明" and score = 80;
-- 不在索引列上做任何計算、函數操作，會導致索引失效而轉向全表掃描。
explain select * from students where left(sname,2) = "小明";
-- 存儲引擎不能使用索引中范圍條件右邊的列。
explain select * from students where sname="小明" and age > 22 and score = 100;
-- Mysql在使用不等于時無法使用索引會導致全表掃描。
explain select * from students where sname!="小明";
-- is null可以使用索引，但是is not null無法使用索引。
explain select * from students where sname is not null;
-- like以通配符開頭會使索引失效導致全表掃描。
explain select * from students where sname like "明%";
-- 字符串不加單引號索引會失效。
explain select * from students where sname = 123;
-- 使用or連接時索引失效。
explain select * from students where sname="小明" or age = 22;

注意：

建議：

• 1.對于單值索引，盡量選擇針對當前查詢字段過濾性更好的索引。
• 2.對于組合索引，當前where查詢中過濾性更好的字段在索引字段順序中位置越靠前越好。
• 3.對于組合索引，盡量選擇能夠包含在當前查詢中where子句中更多字段的索引。
• 4.盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的。