MySQL索引调优实际操作(一)

Posted by 好记性不如烂笔头 on 03-16,2020

前言

🙌在上一篇文章《我把mysql索引的裤子给扒了》中,我们大致了解了索引是什么,是干什么的,并且简单了解了内部的数据结构原理。

这一篇文章将会介绍索引调优的具体操作。如果你可以跟着我一起做,学习效率将会大大提高哦!

由于是之前很早安装的,博主的mysql版本是5.5,现在一般至少都用5.7了。存储引擎要用InnoDB,mysql的默认引擎就是这个。

单值索引和组合索引

我们建立一张名为student的表。

create table student(
    id int auto_increment,
    name varchar(5) null,
    class_num int null,
    age int null,
    constraint student_pk primary key(id)
);

总共4个字段,id是自增加的主键,name是姓名,class_num是班级,age是年龄。

student表的字段

单值索引,顾名思义,就是建立在一个字段上的索引,而组合索引,当然就是同时建立在多个字段上的索引咯。

比如我们在class_num字段上建立一个索引:

create index index_class on student(class_num);

那么,像上一篇文章里说的那样,mysql会用class_num上的数据维护起一棵B+树,树的叶子节点(也就是最终存放数据的节点)里存放的是class_num的数据和主键id数据。同理,若我们在age字段上建立索引,mysql也会用age字段上的数据维护起另一棵B+树。

如果我们在class_num和age两个字段上建立一个组合索引(先把刚刚的单值索引删了):

create index index_class_name on student(class_num, age);

这样的话,建立的B+树在叶子节点上就同时存放了class_num和age两个字段的数据,另外还有主键id的数据,若class_num相等的,那么就按age大小升序排,类似下面这样(假如共有5条数据的话):

class和age组合索引

注意!组合索引时,字段的顺序很重要,class_num在前和age在前,造成的效果是不一样的!这个后面会讲到。

explain关键字

索引调优最常用的方式是使用explain来观察SQL语句的执行情况,当我们输入explain+一条SQL时,mysql会输出给我们一张表,表的字段如下(刚看会很难理解,先记住,后面会细讲)(5.7版本后可能还会有partitions字段和filtered字段,这里不讲):

explain字段

id字段

表示表的加载顺序,值大的先加载,值相同就表示从上到下的顺序加载。

select_type字段

可出现的值为以下几个:

  1. SIMPLE: 简单查询
  2. PRIMARY: 查询中包含任何复杂的子部分,最外层则标记为这个
  3. SUBQUERY: 子查询
  4. DERIVED: 在from中包含的子查询会标记为derived,mysql会递归这些子查询,结果放在临时表中
  5. UNION: 若第二个select出现在union之后,则会被标记为union
  6. UNION RESULT: 从union表获取结果的select语句

table字段

最简单,表示哪个表被加载了。

type字段

最重要!表示查询类型,查询类型效率从高到低排列如下:

  1. system: 表只有一行记录,一般都是系统表,出现情况很少,基本可以忽略
  2. const:等值查询,且通过索引一次就找到了,只会有一条数据符合要求,用到主键索引或unique索引会出现这个值
  3. ref:也是等值查询,只不过结果不是唯一的,返回索引行相等的记录,可能会有多条记录
  4. range:范围查询,不是等值查询,使用一个索引来选择符合条件的记录,效率比全表扫描好
  5. index: 其实也是全部扫描,只不过是遍历索引树,而不是遍历数据文件,所以比ALL快
  6. All:扫描全部数据文件,最慢

possible_keys字段

表示可能用上的索引,一个或多个。但不一定使用到。

keys字段

表示实际上用到的索引。如果为NULL,则没建立索引或没用到索引。

key_len字段

表示索引中使用到的字节数。

ref字段

显示索引的哪一列被使用了,如果是const的话,说明某些列或常量被用于查找索引列上的值。

rows字段

表示根据表统计信息和索引选用情况,大致估算找到所需的记录须读取的行数,越少越好。

Extra字段

mysql输出的额外信息,可能出现的值有(只列出常见的):

  1. Using filesort:表示使用“文件内排序”,当SQL语句含有order by时可能会出现,表示mysql无法利用索引进行排序,这是效率低的表现。
  2. Using index:表示使用了索引,是效率良好的表现。
  3. Using where:表示用到了where子句。
  4. Using temporary:表示创建了临时表,当SQL语句中含有group by时可能会出现,这是效率低的表现。

开始实际操作

现在我们在student表中插入一些数据:

student表

用explain来分析这样一条查询语句:

explain select * from student where id = 2;

mysql返回给我们的结果是:

explain结果

这个结果传递的信息有:

  • student表是第一个被加载的,也只加载了这一张表(废话,from后面就一个值)
  • type为const说明是通过索引一次就查到了记录,对应key字段表明用到了PRIMARY主键索引(上一篇文章说了主键是自动建立索引的)
  • ref字段为const说明是等值查询,一个固定的值用来搜索索引树上的数据,这个固定的值也就是我们写的 id = 2

我们再来一条SQL语句:

#执行时student表我没有建立任何索引
explain select * from student where name="aaa";

此时的结果为:

name未建索引

这告诉我们,这一条语句的效率只有ALL,也就是全表扫描,key字段也是NULL,没有用到任何索引。 不难想到,我们where子句里的name字段上没有建立任何索引,当然会是这个结果。

现在我们在name字段上加上名叫index_name索引,就像上面提到的那样用create index语句。建好之后再次执行刚刚的SQL查询,我们将会发现:

name建立索引

这次type字段变成ref了,key字段也是索引名index_name,表示我们用到了刚刚的索引,mysql是利用where子句中name的值在索引树上查找的。

讲到这里,读者应该可以基本明白explain语句的结果的含义了。

刚刚只是单值索引的情况,现在我们删除刚刚的索引,在name,class_num,age三个字段上建立一个组合索引,名叫index_3(先把前面的索引都删了)。

create index index_3 on student (name, class_num, age);

注意这个组合索引字段的顺序!

然后我们依次explain以下的等值查询语句:

explain select * from student where name='bbb';
explain select * from student where class_num=3;
explain select * from student where age=18;

等值查询三连击

我们会发现,只有第一个语句的type字段达到了ref级别,而后面两个的type字段都是index,即遍历索引树。若你使用的是5.7之前的版本,后面两个语句的type字段可能直接会退化为ALL。

为什么会这样呢?

如果你还记得我前面提到了索引中字段的顺序的重要性,你就会发现,name字段刚好是索引中的第一个字段!那么这中间就必定存在着某种联系。

如果你又记得B+Tree组织数据的方式,你就全都明白了为什么会出现这种情况。

B+Tree是按照索引的第一个字段来组织数据的,叶子节点中也是按照第一个字段的值大小来串起链表,第一个字段值相等再按照第二个字段,第二个字段值相等再按照第三个字段......以此类推。若where子句中有第一个字段,当然可以利用这个值在索引树上搜寻。倘若是没有第一个字段,直接跳到后面的字段,由于后面的字段在树上是无序的,当然就只能遍历了,要么在索引树上遍历(稍好),要么直接暴力全表扫描(凉凉)。

懂了这一点,那么下面的语句,type自然可以达到ref级别:

explain select * from student where name='bbb';
explain select * from student where name='bbb' and class_num=3;
explain select * from student where name='bbb' and class_num=3 and age=19;

原因也非常明了,因为“带头大哥”name字段没有丢掉。

那如果是这一条语句呢:

explain select * from student where name='bbb' and age=19;

看出区别了吗?虽然带头大哥name在,但是中间丢掉了一个class_num,直接到age。会发生什么情况呢?

丢了一个兄弟

答案是可以达到ref级别,但是有个细节,key_len字段的值只有23,和explain select * from student where name='bbb'的key_len一样。这说明只是部分用到了索引,也就是用到了搜寻name的那一部分,后面的部分则失效了。就好比你上了一楼,但是二楼没了,三楼自然就不可能上去,你最多也只能到一楼。索引后面的部分没用到。

上述规律可以形象地总结为:带头大哥不能死,中间兄弟不能丢

那如果是范围查找的SQL呢?试一下呗

explain select * from student where name='aaa' and class_num>1

range查询

在带头大哥存在的情况下,type达到了range级别,与我们的预期是相符合的。key_len到了28说明不止用到了name的部分,还用到了class_num的部分。

如果跳过class_num:

explain select * from student where name='aaa' and age>18

跳过中间的range查询

果然,key_len只有23,只用到了name的索引部分,由于name是等值查询,故type为ref级别。

若是带头大哥也去掉:

explain select * from student where class_num>1

没有带头大哥的range查询

那么就只有遍历搜寻了,原因前面也说过了,就不再复述了。

总结

只要把握住mysql索引的内部数据结构B+Tree,理解一条SQL查询语句能达到什么样的效率就十分简单了,牢记explain结果中每个字段的含义,索引调优也不过如此嘛。

补充

索引调优还有一些需要注意的事项,列在下方:

  • 尽量使用覆盖索引(select后面的字段包含在索引字段中,少用select *,尽可能地,索引中有啥字段,就select啥字段)
  • SQL语句中使用不等于!=会导致无法使用索引
  • is nullis not null也无法使用索引
  • 字符串不加单引号会导致无法使用索引
  • SQL语句中like以通配符*开头也会无法使用索引

不推荐建立索引的情况

  • 频繁更新的字段不推荐建立索引
  • where条件用不到的字段不需要建立索引

推荐建立索引的情况

  • 频繁作为查询条件的字段
  • 查询中关联其他表的字段,外键建立索引
  • 查询中要用order by排序的字段
  • 高并发条件下倾向建立组合索引