MySQL索引优化解决方案--索引失效(3)

索引失效情况

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的写法来达到选择合适索引的目的。