- 最佳左前缀法则:如果索引了多列,要遵循最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列。
- 不在索引列上做任何计算、函数操作,会导致索引失效而转向全表扫描。
- 存储引擎不能使用索引中范围条件右边的列。
- Mysql在使用不等于时无法使用索引会导致全表扫描。
- is null可以使用索引,但是is not null无法使用索引。
- like以通配符开头会使索引失效导致全表扫描。
- 字符串不加单引号索引会失效。
- 使用or连接时索引失效。
建议:1.对于单值索引,尽量选择针对当前查询字段过滤性更好的索引。2.对于组合索引,当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好。3.对于组合索引,尽量选择能够包含在当前查询中where子句中更多字段的索引。4.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
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;