MySQL高级(四)、MySQL索引优化之索引失效问题
程序员文章站
2022-05-03 14:41:46
...
本文以案例形式介绍索引失效问题,并在最后给出索引失效的小结。
索引失效情况
- 全值匹配我最爱;
- 最佳左前缀法则:如果索引了多列,要遵守最左前缀法则,查询从索引的最左前列开始,且不能跳过索引中的列;
- 不在索引列上做任何操作(计算,函数,类型转换),会导致索引失效而转向全表扫描;
- 存储引擎不能使用索引中范围条件右边的列,即
范围之后全失效
; - 尽量使用覆盖索引,只访问索引的查询(索引列和查询列一致),减少selec *;
- MySQL在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描;
- is not null 也无法使用索引,is null是可以使用索引的;
- like 以通配符开头(’%aa’)索引会失效,变成全表扫描;
- 字符串不加单引号,索引失效;
- 少用 or,用它来连接时会索引失效
案例
创建员工表staffs,在staffs上创建索引idx_staffs_nameAgePos(name, age, pos),并在该表上分析上述索引失效的情况。
#建表和索引的语句
CREATE TABLE staffs (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR (24) NULL DEFAULT '' COMMENT '姓名',
age INT NOT NULL DEFAULT 0 COMMENT '年龄',
pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
) CHARSET utf8 COMMENT '员工记录表' ;
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());
SELECT * FROM staffs;
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);
- 全值匹配表示索引列和查询条件的字段全部匹配,精度高,key_len长度大,如
#索引字段为name, age, pos,则查询时最好全部匹配
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3' AND age = 23 AND pos = 'manager';
2. 最佳左前缀法则
#没有带头大哥,全表扫描,type为all
EXPLAIN SELECT * FROM staffs WHERE pos = 'manager';
#缺少中间楼梯,key_len长度仅仅为name的字节长度
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3' AND pos = 'manager';
3. 不在索引列上做任何操作,会导致索引失效而转为全表扫描
#使用索引
EXPLAIN SELECT * FROM staffs WHERE name = 'July';
#索引失效
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';
4. 范围之后全失效
#只使用到NAME 和age字段作为索引,范围之后的pos失效
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3' AND age < 11 AND pos = 'manager';
5. 尽量使用覆盖索引
#未使用覆盖索引
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3' AND age = 23 AND pos = 'manager';
#使用覆盖索引
EXPLAIN SELECT name, age, pos FROM staffs WHERE NAME = 'z3' AND age = 23 AND pos = 'manager';
6. 使用 != 或者 <>导致索引失效
#使用索引
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3';
#索引失效,全表扫描,要全表遍历一遍
EXPLAIN SELECT * FROM staffs WHERE NAME != 'z3';
7.IS NOT NULL无法使用索引,IS NULL可以使用索引
#IS NOT NULL无法使用索引
EXPLAIN SELECT * FROM staffs WHERE NAME IS NOT NULL;
#IS NULL可以使用索引
EXPLAIN SELECT * FROM staffs WHERE NAME IS NULL;
#选择条件就是索引字段可以使用索引
EXPLAIN SELECT name FROM staffs WHERE NAME IS NOT NULL;
8. Like以通配符开头,索引失效
#like以通配符开头,索引失效
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%uly';
#通配符放后面可以使用索引
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'Jul%';
9. 字符串不加单引号,索引失效
我们在staffs表中有一个name为2000的员工,通过姓名查找,若不加单引号能够找到该行,但是变为全表扫描
10. 使用OR,导致索引失效
select * from staffs where name = 'July' or name = 'z3';
#使用or导致索引失效
explain select * from staffs where name = 'July' or name = 'z3';
索引小结
Where语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b = 5 | Y,使用到a, b |
where a = 3 and b = 5 and c = 4 | Y,使用到a, b, c |
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N |
where a = 3 and c = 5 | 使用到a,但是c不可以,b中间断了 |
where a = 3 and b > 4 and c = 5 | 使用到a和b,c不能用在范围之后,b断了 |
where a = 3 and b like ‘kk%’ and c = 4 | Y,使用到a,b,c |
where a = 3 and b like ‘%kk’ and c = 4 | Y,使用到a |
上一篇: Spring Boot 微服务小案例
下一篇: Sql优化-2 索引