欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL高级(四)、MySQL索引优化之索引失效问题

程序员文章站 2022-05-03 14:41:46
...

本文以案例形式介绍索引失效问题,并在最后给出索引失效的小结。

索引失效情况

  1. 全值匹配我最爱;
  2. 最佳左前缀法则:如果索引了多列,要遵守最左前缀法则,查询从索引的最左前列开始,且不能跳过索引中的列
  3. 不在索引列上做任何操作(计算,函数,类型转换),会导致索引失效而转向全表扫描;
  4. 存储引擎不能使用索引中范围条件右边的列,即范围之后全失效
  5. 尽量使用覆盖索引,只访问索引的查询(索引列和查询列一致),减少selec *;
  6. MySQL在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描;
  7. is not null 也无法使用索引,is null是可以使用索引的;
  8. like 以通配符开头(’%aa’)索引会失效,变成全表扫描;
  9. 字符串不加单引号,索引失效;
  10. 少用 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);
  1. 全值匹配表示索引列和查询条件的字段全部匹配,精度高,key_len长度大,如
#索引字段为name, age, pos,则查询时最好全部匹配
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3'  AND age = 23 AND pos = 'manager';

MySQL高级(四)、MySQL索引优化之索引失效问题
2. 最佳左前缀法则

#没有带头大哥,全表扫描,type为all
EXPLAIN SELECT * FROM staffs WHERE pos = 'manager'; 
#缺少中间楼梯,key_len长度仅仅为name的字节长度
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3' AND pos = 'manager';

MySQL高级(四)、MySQL索引优化之索引失效问题
3. 不在索引列上做任何操作,会导致索引失效而转为全表扫描

#使用索引
EXPLAIN SELECT * FROM staffs WHERE name = 'July';
#索引失效
EXPLAIN SELECT * FROM staffs WHERE left(NAME,4) = 'July';

MySQL高级(四)、MySQL索引优化之索引失效问题
4. 范围之后全失效

#只使用到NAME 和age字段作为索引,范围之后的pos失效
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3'  AND age < 11 AND pos = 'manager';

MySQL高级(四)、MySQL索引优化之索引失效问题
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';

MySQL高级(四)、MySQL索引优化之索引失效问题
6. 使用 != 或者 <>导致索引失效

#使用索引
EXPLAIN SELECT * FROM staffs WHERE NAME = 'z3';
#索引失效,全表扫描,要全表遍历一遍
EXPLAIN SELECT * FROM staffs WHERE NAME != 'z3';

MySQL高级(四)、MySQL索引优化之索引失效问题
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;

MySQL高级(四)、MySQL索引优化之索引失效问题
MySQL高级(四)、MySQL索引优化之索引失效问题
8. Like以通配符开头,索引失效

#like以通配符开头,索引失效
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE '%uly';
#通配符放后面可以使用索引
EXPLAIN SELECT * FROM staffs WHERE NAME LIKE 'Jul%';

MySQL高级(四)、MySQL索引优化之索引失效问题
9. 字符串不加单引号,索引失效
我们在staffs表中有一个name为2000的员工,通过姓名查找,若不加单引号能够找到该行,但是变为全表扫描
MySQL高级(四)、MySQL索引优化之索引失效问题
10. 使用OR,导致索引失效

select * from staffs where name = 'July' or name = 'z3';
#使用or导致索引失效
explain select * from staffs where name = 'July' or name = 'z3';

MySQL高级(四)、MySQL索引优化之索引失效问题

索引小结

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