0%

Mysql进阶--索引失效准则

索引失效(应该避免)

导致索引失效的一些准则,也算是一些规范,在实际开发中,在满足业务的条件下,可以尽量的避免,以达到优化的效果。

1.首先新建一张员工表,并插入数据,建立复合索引,执行语句,

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24)NOT 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());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

image-20201113153457208

索引失效准则:

  1. 全值匹配我最爱
  2. 最佳左前缀法则:如果索引了多例,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
  6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
  7. is nullis not null 也无法使用索引(早期版本不能走索引,后续版本应该优化过,可以走索引)
  8. like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描操作
  9. 字符串不加单引号索引失效
  10. 少用or,用它连接时会索引失效
最佳左匹配法则:带头大哥不能死,中间兄弟不能断

概念:如果索引了多列,要遵守最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中的列

只有第一个查询条件 name 的时候:

  • type=ref,进行了优化
  • key = index_staffs_nameAgePos 表明索引生效
  • ref = const ,这个常量就是查询时的 ‘July’ 字符串常量
1
EXPLAIN SELECT * FROM staffs WHERE name = 'July';

image-20201113154231090

在name的基础上加上age查询条件的时候:

  • type=ref,进行了优化
  • key = index_staffs_nameAgePos 表明索引生效
  • ref = const,const ,两个常量分别就代表了“July”和“23”,这两个查询条件
1
EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23;

image-20201113154649743

在name、age的基础上再加上pos查询条件:

  • type=ref,进行了优化
  • key = index_staffs_nameAgePos 表明索引生效
  • ref = const,const,const ,三个常量分别就代表了“July”和“23”和“dev”,这三个查询条件
1
EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';

image-20201113155032536

并且可以注意到,key_len的数值也在变化:74->78->140,就算忘记了它的涵义,但是key_len那么明显,什么的长度,别忘了这是在干嘛,执行一条sql,并且使用索引,就算还是不知道是索引长度,就算把它作为sql的执行长度,也应该是越短越好,

尝试去掉name查询条件:

  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,
1
EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';

image-20201113155719578

尝试去掉age查询条件:

  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,
1
EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

image-20201113155932855

毫无疑问,当name条件去掉以后,索引就不再生效了,说明老大哥确实不能死,死了后面都不再生效,

那试试去掉age查询字段,留下name和pos呢:

  • type=ref,毫无疑问,索引生效了,
  • key = index_staffs_nameAgePos,表明索引生效
  • ref = const,说明只有一个字段生效了,而查看它的key_len是为74的,而key_len代表的是索引的长度,并且这个数字很熟悉,因为在前面单独查询name的时候就为74,那就说明这个const代表的只会是“July”这个查询条件,

image-20201113160634862

这又证明了当中间条件age去掉之后,后面的索引也不再生效了,说明了带头大哥不能死,中间兄弟也不能断

不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

还是先只设置name一个查询条件:

  • type=ref,毫无疑问,索引生效了,
  • key = index_staffs_nameAgePos,表明索引生效
  • ref = const,代表了”July”查询条件
1
EXPLAIN SELECT * FROM staffs WHERE name = 'July';

image-20201113161724300

对name使用left进行截断操作:

  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,
1
EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';

image-20201113161923839

而我们查看两条sql的查询结果:

1
2
SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
SELECT * FROM staffs WHERE name = 'July';

image-20201113162049543

毫无疑问,结果是一致的,但是name进行了left截断操作的语句的索引失效了,所以,不要对索引的字段进行计算,将会导致索引失效

存储引擎不能使用索引中范围条件右边的列

先看一下全值匹配,也就是三个查询条件都是=:

  • type=ref,进行了优化
  • key = index_staffs_nameAgePos 表明索引生效
  • ref = const,const,const ,三个常量分别就代表了“July”和“23”和“dev”,这三个查询条件
1
EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';

image-20201113163028278

那试一试将age改为范围查询条件呢:

  • type = range ,表示范围扫描
  • key = index_staffs_nameAgePos 表示索引生效
  • ref = NULL ,且key_len为78,表明了范围搜索使其后面的索引均失效
1
EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 25 AND pos = 'dev';

image-20201113163356914

可以看出来,在范围查询后的索引会失效,所以,不要使用索引范围右边(也就是后面)的字段(列),

尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select *

先使用select * 来查询:

  • type = ref,表示范围扫描
  • key = index_staffs_nameAgePos 表示索引生效
  • ref = const,const,const,分别代表了三个常量
1
EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 25 AND pos = 'dev';

image-20201113164523102

再单独查询字段:

  • type = ref,表示范围扫描
  • key = index_staffs_nameAgePos 表示索引生效
  • ref = const,const,const,分别代表了三个常量
  • 而这里的Extra变为了Using index,这是覆盖索引,表示使用索引列进行查询,很大程度的提高查询的效率,
1
EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age = 25 AND pos = 'dev';

image-20201113164500550

所以,*最好使用覆盖索引,索引列和查询列保持一致,减少select * 查询*

mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

在使用 != 会 <> 时会导致索引失效:

  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,
1
2
EXPLAIN SELECT * FROM staffs WHERE name != 'July';
EXPLAIN SELECT * FROM staffs WHERE name <> 'July';

image-20201113165317184

有的时候确实一些业务要求这样写,不能为了索引而索引,该写还是要写,只能说在保证业务的情况下去进行优化,

is null,is not null也无法使用索引

使用is null,is not null导致索引失效:

  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,
1
2
EXPLAIN SELECT * FROM staffs WHERE name is null;
EXPLAIN SELECT * FROM staffs WHERE name is not null;

image-20201113165712133

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描操作,所以like % 写在查询条件的最右边

使用like,前后都添加通配符:

  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,
1
EXPLAIN SELECT * FROM staffs WHERE name like '%July%';

image-20201113170411196

使用like,前面加通配符,后面不加:

  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,
1
EXPLAIN SELECT * FROM staffs WHERE name like '%July';

image-20201113170500892

使用like,后面加通配符,前面不加:

  • type = range ,表示like是范围扫描
  • key = index_staffs_nameAgePos 表示索引生效
  • ref = NULL ,表明了范围搜索使其后面的索引均失效,那是不是代表
1
EXPLAIN SELECT * FROM staffs WHERE name like 'July%';

image-20201113170536703

由上面三种情况可以得出,like的通配符%最好加右边,但是有的时候就是需要前后加通配符,如何解决like ‘%字符串%’时索引不被使用的方法?

解决like ‘%字符串%’ 索引失效的问题:覆盖索引

1.创建一张user表,并插入部分数据,执行语句,

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `tbl_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`age`INT(11) DEFAULT NULL,
`email` VARCHAR(20) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

image-20201113171405716

2.新建一条复合索引,为了解决通配符问题,使用覆盖索引,

1
CREATE INDEX idx_user_nameAge ON tbl_user(name, age);

image-20201113171736749

3.测试覆盖索引,也就是说name和age这两个字段,在作为索引的情况下,是否能解决通配符后不被引用的问题,

1
2
3
4
5
6
7
EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%'; 
EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';

查询name,age:

  • type=index,索引生效了,
  • key = idx_user_nameAge,表明索引生效
1
EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%'; 

image-20201113172301789

查询id:

  • type=index,索引生效了,默认主键索引
  • key = idx_user_nameAge,表明索引生效
1
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%'; 

image-20201113172547154

单独查询name和age:

  • type=index,索引生效了,
  • key = idx_user_nameAge,表明索引生效
1
2
EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%'; 
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';

image-20201113172459510

image-20201113172710719

查询id和name、age的搭配查询的情况:

1
2
3
EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%'; 
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';
  • type=index,索引生效了,
  • key = idx_user_nameAge,表明索引生效

image-20201113172857346

image-20201113172908822

image-20201113172917802

上面的sql,查询的东西至少都是和索引有所关联,所以都生效了,那么再测试一个查询不属于索引的字段呢,

查询id, name, age, email 或者 *:

1
2
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
  • type=ALL,毫无疑问,进行了全表扫描,
  • key = null,表明索引并没有生效
  • ref = null,说明ref没有索引字段生效,

image-20201113173213108

从上述的测试可以看出,如果业务中非要用双百分号通配符like,而要实现索引生效的最好方式,就是使用覆盖索引,也就是查询的字段加入索引,

字符串不加单引号索引失效

先查看正确的情况,也就是正常使用单引号:

  • type = ref,表示范围扫描
  • key = index_staffs_nameAgePos 表示索引生效
  • ref = const,代表了一个常量
1
explain select * from staffs where name='2000';

image-20201113173658523

而忘记写单引号的情况,那么 mysql 会为我们进行隐式的类型转换,但凡进行了类型转换,索引都会失效,如果还记得的话,在第三条准则有说:

  • type = ALL,表示全表查询
  • key = null表示索引失效
  • ref = null,代表没有索引字段
1
explain select * from staffs where name=2000;

image-20201113173841162

少用or,用它连接时会索引失效

使用or的情况;

  • type = ALL,表示全表查询
  • key = null表示索引失效
  • ref = null,代表没有索引字段
1
explain select * from staffs where name='z3' or name = 'July';

image-20201113174214845

总结,十大失效准则,避免这些情况就是生效原则,一些常见的索引处理会使用到,

小demo;

image-20201113174719189

----------本文结束感谢您的阅读----------