Mysql数据库优化

一、前言

在Mysql数据库索引详解章节中，我们介绍了数据库索引结构和各种索引的概念及其作用。我们知道，对数据库优化可以加索引，但是我们怎么知道加了是什么索引呢？就是说，具体这条sql走的是哪个索引呢？这就需要我们运行执行计划—explain ，通过执行计划来看是否按照我们的意愿走了对的索引，还是走的全表扫描，而且还需要先排除缓存的干扰，还有就是哪些情况会导致索引失效等等。

下面先带大家了解下数据库缓存的知识点。

二、正文

1.Mysql基础架构

数据库调优其实一般情况都是我们的SQL调优，SQL的调优就可以解决大部分问题了，当然也不排除SQL执行环节的调优。

我们先来看下Mysql的基本架构图：

我们所谓的调优也就是在，执行器执行之前的分析器，优化器阶段完成的，那我们开发工作中怎么去调优的呢？

一般在开发涉及SQL的业务都会去本地环境跑一遍SQL，用explain去看一下执行计划，看看分析的结果是否符合自己的预期，用没用到相关的索引，然后再去线上环境跑一下看看执行时间（这里只有查询语句，修改语句也无法在线上执行）。

遇SQL不决explain，但是这里就要说到第一个坑了。

排除缓存干扰

因为在MySQL8.0之前我们的数据库是存在缓存这样的情况的，我之前就被坑过，因为存在缓存，我发现我sql怎么执行都是很快，当然第一次其实不快但是我没注意到，以至于上线后因为缓存经常失效，导致rt（Response time）时高时低。

后面就发现了是缓存的问题，我们在执行SQL的时候，记得加上SQL NoCache去跑SQL，这样跑出来的时间就是真实的查询时间了。

我说一下为什么缓存会失效，而且是经常失效。

如果我们当前的MySQL版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以key-value的形式缓存在内存中的，大家也看到我们的结构图了，一个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之前的电商场景，可能静态表可以用到缓存。

大家如果是8.0以上的版本就不用担心这个问题，如果是8.0之下的版本，记得排除缓存的干扰。

更加详细请看：Mysql的基础架构

2.Explain执行计划

首先说下运用索引的优点：

• 大大减少了服务器需要扫描的数据行数。
• 帮助服务器避免进行排序和分组，以及避免创建临时表（B+Tree 索引是有序的，可以用于 ORDER BY 和 GROUP BY 操作。临时表主要是在排序和分组过程中创建，不需要排序和分组，也就不需要创建临时表）。
• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）。

在对一条select进行explain之后，我们会看到返回结果中有如下几个字段：

1. id 表示执行的顺序，id越大越先执行，id一样的从上往下执行。

2. select_type

   表示查询类型，通常有:

   • simple：表示不需要union操作或者不包含子查询的简单查询。
   • primary：表示最外层查询。
   • union：union操作中第二个及之后的查询。
   • dependent union：union操作中第二个及之后的查询，并且该查询依赖于外部查询。
   • subquery：子查询中的第一个查询。
   • dependent subquery：子查询中的第一个查询，并且该查询依赖于外部查询。
   • derived：派生表查询，既from字句中的子查询。
   • materialized：物化查询。
   • uncacheable subquery：无法被缓存的子查询，对外部查询的每一行都需要重新进行查询。
   • uncacheable union：union操作中第二个及之后的查询，并且该查询属于uncacheable subquery。

   常用的有 SIMPLE 简单查询，UNION 联合查询，SUBQUERY 子查询等。

3. table 表名或者表的别名。

4. partitions 分区信息，非分区表为null。

5. type

   访问类型，表示找到所查询数据的方法，也是本文重点介绍的属性。该属性的常见值如下，性能从好到差：

   • NULL：无需访问表或者索引，比如获取一个索引列的最大值或最小值。
   • system/const：当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。
   • eq_ref：多表关联查询时，根据唯一非空索引进行查询的情况。
   • ref：可以是单独查询或者关联查询，根据非唯一非空索引进行查询的情况。
   • range：在一个索引上进行范围查找。
   • index：遍历索引树查询，通常发生在查询结果只包含索引字段时。
   • ALL：全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。

6. possible_keys 这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

7. key 表示mysql实际在此次查询中使用的索引。

8. key_len 表示mysql使用的索引的长度。该值越小越好。

9. ref 表示连接查询的连接条件，即哪些列或常量被用于查找索引列上的值。

10. rows 表示mysql估计此次查询所需读取的行数。该值越小越好。

11. extra 表示mysql解决查询的其他信息，有几十种不同的值，该信息也是我们优化sql可以专注的一个值。关于这个extra信息我会在下面介绍。

到目前为止，我们基本已经了解了explain语句的输出的含义，结下来就进入实战，借助例子来介绍一下每种type的不同。

• DDL:

CREATE TABLE class (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
grade int(11) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE student (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
name char(20) NOT NULL,
age tinyint(4) NOT NULL,
score tinyint(4) NOT NULL,
class_id int(11) NOT NULL,
PRIMARY KEY (id),
KEY IDX_CLASS_ID (class_id),
KEY IDX_AGE (age)
);

• DML:

INSERT INTO class(grade) VALUES(90),(80),(85),(100),(70);
INSERT INTO student(name, age, score, class_id)
VALUES (‘Sawyer’, 29, 88, 1), (‘Del Piero’, 22, 91, 1),
(‘Mitsunari’, 44, 99, 2), (‘Natalie’, 41, 95, 2);

运行结构如下：

student表：

class表：

至此，准备工作完成，接下来我们将一个一个地分析常见的type类型，从最效率的到最不效率的。

（1）NULL
无需访问表或者索引，比如获取一个索引列的最大值或最小值。由于innodb采用B+树最为索引的物理结构，而B+树的叶子节点是顺序排列的，所以当查询索引的最大或最小值时，不需要遍历叶子节点，只需要拿到叶子节点头或者尾即可。看下面的例子：

explain select max(age) from student;

执行结果如下：

（2）system/const
当查询最多匹配一行时，常出现于where条件是＝的情况。system是const的一种特殊情况，既表本身只有一行数据的情况。我们这里演示一下const的情况：

explain select * from student where id = 1;

执行结果如下：

但是要注意的是，并不是所有的where＝都是const，只有＝的右边是常量的时候才会走const。比如：

explain select * from class where id = grade;

执行结果如下：

由于＝的右边并不是常量，且grade上没有建索引，所以该查询走了效率最差的ALL全表扫描。通常，我们会将查询条件里的所有字段都建立索引，上面的sql优化后会变成使用index索引查询：

alter table class add index IDX_GRADE(grade);
explain select * from class where id = grade;

执行结果如下：

（3）eq_ref
多表关联查询时，根据唯一非空索引进行查询的情况。这个只会出现在关联查询中，并且是根据唯一非空键（主键或唯一非空索引）查询的情况。如下：

explain select * from student s left join class c on s.class_id = c.id where s.name = 'Sawyer';

执行结果如下：

发现了么，这里的执行结果出现了两条数据，id同为1，回忆一下我们一开始说的，id代表了执行的顺序，当id相同时，顺序自上而下。所以分析这个执行结果可知，mysql首先在s表也就是student表中查询name字段为Sawyer的值，由于name字段上并没有索引，所以使用了全表扫描，该表一共有4条记录，所以扫描了4行，rows为4。然后c表也就是class表使用主键和之前的结果通过s.class_id关联，由于是关联查询，并且是通过唯一键进行查询，所以使用了eq_ref的类型。这里也可以通过建立name字段的索引来优化全表扫描的问题，这里就不再演示。

（4）ref
根据非唯一非空索引进行查询的情况，可以是单独查询或者关联查询。比如：

select * from class c join student s
on c.id = s.class_id
where c.id = 1;

执行结果如下：

通过这个执行计划可以分析出，由于查询条件是c.id=1,是一个常数查询，mysql首先使用const对c表进行查询，之后s表使用索引IDX_CLASS_ID对结果进行关联，由于索引IDX_CLASS_ID是非唯一非空索引，所以这里的查询方式为ref。

（5）range
在一个索引上进行范围查找。既只使用一个索引，查询条件满足多个时。比如：

explain select * from student where age between 20 and 30;

执行结果如下：

通常情况下，where in 也是range查询，但是下面的例子却显示ALL：

explain select * from student where age in (29, 44);

执行结果如下：

这是Mysql的优化器（optimizer）的优化结果。mysql通过分析发现此查询走全表扫描的代价比走索引的代价要小，所以选择了走全表扫描而非索引。大体的原因是通过索引读取一条数据至少要经过两次节点检索（聚簇索引的高度为3，数据存在叶子节点上），而全表扫描是一次读取一个page中的多条记录，当查询结果超过总数据一定比例的时候（在这个例子中有一半的数据满足了查询条件），走索引的查询开销反而比全表扫描要大，这时mysql则会放弃索引而选择进行全表扫描。优化器不仅仅能在索引和全表扫描中选择，甚至在不影响查询结果的前提下改变查询顺序等黑科技，这里就不再展开。对于我们的例子，有两种方式让mysql使用索引查询。一种是使用语法强制mysql使用索引：

explain select * from student force index(IDX_AGE) where age in (29, 44);

执行结果如下：

当然，实际开发中在你没有十足的把握时，最好不要使用这样的语法，毕竟mysql优化器还是比较准的。

另一种方式是增加数据，使得查询条件命中的数据占总数据的占比比较小。这也是索引最能发挥作用的情况（既只有少部分数据满足查询条件）。

INSERT INTO student(name, age, score, class_id)
VALUES ('Tom', 61, 84, 3), ('Obama', 56, 100, 4),
('Nedved', 43, 86, 5), ('Buffon', 49, 86, 5);

这个时候我们再执行之前的sql就发现这次走的是索引的range了，原因是这次条件命中的数据（2条）占所有数据（8条）的占比比较小。

执行结果如下：

（6）index
遍历索引树查询，通常发生在查询结果只包含索引字段时。比较好理解，既只select索引字段，并且没有where条件，如：

explain select id from student;

执行结果如下：

（7）ALL
全表扫描，没有任何索引可以使用时。这是最差的情况，应该避免。比如：

explain select * from student where score = 100;

执行结果如下：

有时就算是通过索引的字段查询，也会出现全表扫描的情况，最常见的情况就是对字段进行了函数处理，如：

explain select * from student where coalesce(age, 18) > 20;

执行结果如下：

这里虽然age字段上建有索引，但是由于我们对age进行了函数处理，所以就没法再使用索引了，在写sql时一定要避免这种情况。

这里说一个比较特殊的情况：

 explain select * from student where  name like '%o' and  class_id = 1

首先说下"name"是(name,age)的组合索引，“class_id”是单独的索引。

那上面的查询会走索引嘛？

会的！如下所示：

我们知道 like以通配符开头（’%abc…’）mysql索引会失效的，但是加了一个单独的索引，就会变成走单独的索引了，这个要注意下。

另外：要是 “class_id”和“name”换个位置呢？还会走索引嘛？ 比如：

explain select * from student where  class_id = 1 and name like '%o'

会的! 大家可以试试。

extra字段

这一字段包含不适合在其他列显示，但是也非常重要的额外信息。

（1）Using filesort
”filesort“并不是指文件排序，file没什么用，有用的只是sort，就是指排序，也就是说没有使用索引进行排序。当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。反之呢？由于索引的底层是B+Tree实现的，他的叶子节点本来就是有序的，这样的查询能不爽吗？

explain select * from student ORDER BY name

执行结果如下：

没有给 name 建立索引，所以在根据 name 排序的时候，他就使用了外部排序。
我后面也写了个一篇文章关于Using filesort的优化：”Mysql之Using filesort优化“

（2）Using temporary
表示在对MySQL查询结果进行排序时，使用了临时表,这样的查询效率是比外部排序更低的，常见于order by和group by

explain select * from student GROUP  BY name

上面这个查询就是同时触发了Using temporary和Using filesort，可谓是雪上加霜。
这里推荐看一篇文章：”Mysql 调优记： INNER JOIN查询 Using temporary; Using filesort 问题优化“
“
（3）Using index 表示使用了索引，很优秀👍。
（4）Using where 使用了where但是好像没啥用。
（5）Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。
（6）impossible where 筛选条件没能筛选出任何东西
（7）distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

3.什么情况下会导致索引失效？

• 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
• 存储引擎不能使用索引范围条件（比如：age > 20）右边的列，具体看我写的一篇文章：“MySQL存储引擎不能使用索引中范围条件右边的列”
• 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
• like以通配符开头（’%abc…’）和（’%abc%’）mysql索引失效会变成全表扫描的操作，但是使用（’abc%’）是可以走索引的。

三、总结

本章主要讲数据库优化，但是在进行索引优化前，一定要先排除缓存的干扰，然后通过explain执行计划查看是否走对了我们所加的索引，最后讲了什么情况会导致索引失效。

最后引用我很佩服的一个人经常说的话：你知道的越多，你不知道的越多！

文章参考：

https://www.jianshu.com/p/b5c01bd4a306

https://segmentfault.com/a/1190000023565685

https://mp.weixin.qq.com/s/NDL1Q6nqdPq5oMBWSpq4ug