数据库优化——慢查询MySQL定位优化流程

末蓝、 2023-01-22 09:53 15阅读 0赞

> 继前篇[博文看了这么多，终于理解了MySQL索引][MySQL]之后，给大家再来一篇日常开发需要用到的优化技巧。

### 文章目录 ###

*  1.如何定位并优化慢查询SQL？
 *  2.如何使用慢查询日志？
 *  3.慢查询例子演示，新手都能看懂
 *  4.查询语句慢怎么办？explain带你分析sql执行计划
 *  5.当主键索引、唯一索引、普通索引都存在，查询优化器如何选择？

# 1.如何定位并优化慢查询SQL？ #

一般有3个思考方向  
1.根据慢日志定位慢查询`sql`  
2.使用`explain`等工具分析`sql`执行计划  
3.修改`sql`或者尽量让`sql`走索引

# 2.如何使用慢查询日志？ #

先给出步骤，后面说明  
有3个步骤  
1.开启慢查询日志

首先开启慢查询日志，由参数`slow_query_log`决定是否开启，在`MySQL`命令行下输入下面的命令：

set global slow_query_log=on;

默认环境下，慢查询日志是关闭的，所以这里开启。

2.设置慢查询阈值

set global long_query_time=1;

只要你的`SQL`实际执行时间超过了这个阈值，就会被记录到慢查询日志里面。这个阈值默认是`10s`，线上业务一般建议把`long_query_time`设置为`1s`，如果某个业务的`MySQL`要求比较高的`QPS`，可设置慢查询为`0.1s`。发现慢查询及时优化或者提醒开发改写。一般测试环境建议`long_query_time`设置的阀值比生产环境的小，比如生产环境是`1s`，则测试环境建议配置成`0.5s`。便于在测试环境及时发现一些效率的`SQL`。

甚至某些重要业务测试环境`long_query_time`可以设置为`0`，以便记录所有语句。并留意慢查询日志的输出，上线前的功能测试完成后，分析慢查询日志每类语句的输出，重点关注`Rows_examined`（语句执行期间从存储引擎读取的行数），提前优化。

3.确定慢查询日志的文件名和路径

show global variables like 'slow_query_log_file'

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MTE1ODk5_size_16_color_FFFFFF_t_70]  
结果会发现慢日志默认路径就是`MySQL`的数据目录，我们可以来看一下`MySQL`数据目录

show global variables like 'datadir';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MTE1ODk5_size_16_color_FFFFFF_t_70 1]  
不用关注这里为什么不是`MySQL 8.0`，这和版本没什么关系的。

来，直接上菜，干巴巴的定义我自己都看不下去

我们先来查看一下变量，我框出了需要注意的点  
查询带有`quer`的相关变量

show global variables like '%quer%';

![20210519114132392.png][] ![2021051911443155.png][]

这里设置慢查询阈值为1s

set global long_query_time=1;

可以看到已经修改过来了

![20210519121933813.png][]

但是重启`mysql`客户端设置和统计慢查询日志条数就会清零，即所有配置修改会还原

命令修改配置之后，在命令行`net stop mysql`关闭`MySQL`服务，再`net start mysql`开启`MySQL`服务，接着执行`show global variables like '%quer%';`会发现配置还原了。

在配置文件修改才能永久改变，否则重启数据库就还原了

# 3.慢查询例子演示，新手都能看懂 #

数据表结构，偷懒没写`comment`

CREATE TABLE `person_info_large` (  
        `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  
        `account` VARCHAR (10),   
        `name` VARCHAR (20),  
        `area` VARCHAR (20),  
        `title` VARCHAR (20), 
        `motto` VARCHAR (50),
        PRIMARY KEY (`id`),  
        UNIQUE(`account`),
        KEY `index_area_title`(`area`,`title`) 
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

**这里的数据是`200W`条。请注意表结构，记住哪几个字段有索引即可，后续围绕这个表进行分析。**  
![20210519122914704.png][]

这个`3.36s`并不是实际执行时间，实际执行时间得去慢查询日志去看`Query_time`参数

![20210519123024769.png][]

可以看到`Query_time: 6.337729s`，超过了`1s`，所以会被记录，一个`select`语句查询这么久，简直无法忍受。

> 图中其他的参数解释如下：  
> `Time`：慢查询发生的时间  
> `Query_time`：查询时间  
> `Lock_time`：等待锁表的时间  
> `Rows_sent`：语句返回的行数  
> `Rows_exanined`：语句执行期间从存储引擎读取的行数

上面这种方式是用系统自带的慢查询日志查看的，如果觉得系统自带的慢查询日志不方便查看，可以使用`pt-query-digest`或者`mysqldumpslow`等工具对慢查询日志进行分析。

注意：有的慢查询正在执行，结果已经导致数据库负载过高，而由于慢查询还没执行完，因此慢查询日志看不到任何语句，此时可以使用`show processlist`命令查看正在执行的慢查询。`show processlist`显示哪些线程正在运行，如果有PROCESS权限，则可以看到所有线程。否则，只能看到当前会话线程。

# 4.查询语句慢怎么办？explain带你分析sql执行计划 #

根据上一节的表结构可以知道，`account`是添加了唯一索引的字段。explain分析一下执行计划。  
![20210519133556914.png][]

我们重点需要关注`select_type`、`type`、`possible_keys`、`key`、`Extra`这些列，我们来一一说明，看到`select_type`列，这里是`SIMPLE`简单查询，其他值下面给大家列出。  
![20210519134223489.png][]

`type`列，这里是`index`，表示全索引扫描

![20210519134506115.png][]

表格从上到下代表了`sql`查询性能从最优到最差，如果是`type`类型是`all`，说明`sql`语句需要优化。

> 注意：如果`type = NULL`，则表明个`MySQL`不用访问表或者索引，直接就能得到结果，比如`explain select sum(1+2);`

`possible_keys`代表可能用到的索引列，`key`表示实际用到的索引列，以实际用到的索引列为准，这是查询优化器优化过后选择的，然后我们也可以根据实际情况强制使用我们自己的索引列来查询。

`Extra`列，这里是`Using index`

![20210519135506542.png][] ![20210519232052684.png][]

一定要注意，`Extra`中出现`Using filesort`、`Using temporary`代表`MySQL`根本不能使用索引，效率会受到严重影响，应当尽可能的去优化。

出现`Using filesort`说明`MySQL`对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容，有索引就维护了`B+`树，数据本来就已经排好序了，这说明根本没有用到索引，而是数据读完之后再排序，可能在内存或者磁盘上排序。也有人将`MySQL`中无法利用索引的排序操作称为“文件排序”。

出现`Using temporary`表示`MySQL`在对查询结果排序时使用临时表，常见于`order by`和分组查询`group by`

回到上一个话题，我们看到`account`是添加了唯一索引的字段。`explain`分析了执行计划后

![20210519140612182.png][]

直接按照`account`降序来查

![20210519140715635.png][]

查看慢查询日志发现，使用索引之后，查询`200W`条数据的速度快了`2s`

接着我们分析一下查询`name`的`sql`执行计划

![20210519221041956.png][]

然后给`name`字段加上索引

![20210519221316971.png][]

加上索引之后，继续看看查询`name`的`sql`执行计划

![20210519221428975.png][]

对比一下前面`name`不加索引时的执行计划就会发现，加了索引后，`type`由`ALL`全表扫描变成`index`索引扫描。`order by`并没有 `using filesort`，而是`using index`，这里`B+`树已经将这个非聚集索引的索引字段的值排好序了，而不是等到查询的时候再去排序。

接着我们继续执行查询语句，此时`name`已经是添加了索引的。

![20210519225921722.png][]

结果发现，`name`添加索引之前，降序查询`name`是花费`6.337729s`，添加索引之后，降序查询`name`花费了`3.479827s`，原因就是`B+`树的结果集已经是有序的了。

![20210519230020131.png][]

# 5.当主键索引、唯一索引、普通索引都存在，查询优化器如何选择？ #

查询一下数据的条数，这里`count(id)`，分析一下`sql`执行计划

![20210519230904290.png][]

这里实际使用的索引是`account`唯一索引。

分析一下：实际使用哪个索引是查询优化器决定的，`B+`树的叶子结点就是链表结构，遍历链表就可以统计数量，但是这张表，有主键索引、唯一索引、普通索引，优化器选择了`account`这个唯一索引，这肯定不会使用主键索引，因为主键索引是聚集索引，每个叶子包含具体的一个行记录（很多列的数据都在里面），而非聚集索引每个叶子只包含下一个主键索引的指针，很显然叶子结点包含的数据是越少越好，查询优化器就不会选择主键索引

当然，也可以强制使用主键索引，然后分析`sql`执行计划  
![20210519231332537.png][]

我们看一下优化器默认使用唯一索引大致执行时间`676ms`  
![20210519231447662.png][]

强制使用主键索引大致执行时间`779ms`  
![20210519231537527.png][]

我们可以用`force index`强制指定索引，然后去分析执行计划看看哪个索引是更好的，因为查询优化器选择索引不一定是百分百准确的，具体情况可以根据实际场景分析来确定是否使用查询优化器选择的索引。

欢迎一键三连~  
  
有问题请留言，大家一起探讨学习  
  
\----------------------Talk is cheap, show me the code-----------------------

[MySQL]: https://blog.csdn.net/qq_34115899/article/details/116856692
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MTE1ODk5_size_16_color_FFFFFF_t_70]: /images/20221020/0b3c5396f0dc4ec8a14181a0dde5e977.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MTE1ODk5_size_16_color_FFFFFF_t_70 1]: /images/20221020/ffd31c9d09804d7fa2979fcddd898b6c.png
[20210519114132392.png]: https://img-blog.csdnimg.cn/20210519114132392.png
[2021051911443155.png]: /images/20221020/8e6af15132a7407d898615f0382013a1.png
[20210519121933813.png]: /images/20221020/d5e051d93dca41d28bc8aabefffbbfdb.png
[20210519122914704.png]: /images/20221020/f3cf283936354a7bbb6a13aa49363368.png
[20210519123024769.png]: /images/20221020/af94821229604e2badb6b6722baa6eab.png
[20210519133556914.png]: /images/20221020/797f0202406a4d96b237f7451aa0baa2.png
[20210519134223489.png]: /images/20221020/69dbc9f521164cfb86ddd461437be499.png
[20210519134506115.png]: /images/20221020/4980a04adc9c45fcba985b8a6c1d48ff.png
[20210519135506542.png]: https://img-blog.csdnimg.cn/20210519135506542.png
[20210519232052684.png]: /images/20221020/eae30cdeae734807abbc8f216b98f293.png
[20210519140612182.png]: /images/20221020/e65cf0a3b47a4a25b3ca389fc432ee37.png
[20210519140715635.png]: /images/20221020/76662382e4084daaa137d514ffc5e482.png
[20210519221041956.png]: /images/20221020/0fed019c1e6b449e8f0c3cc880c27d88.png
[20210519221316971.png]: /images/20221020/e64af89370354c6ca890ca3685090a87.png
[20210519221428975.png]: /images/20221020/6a104d85706446a7a64df4ded3c28416.png
[20210519225921722.png]: /images/20221020/9f1ff640fb7a4a5192a5e4f7f9a2c198.png
[20210519230020131.png]: /images/20221020/4c6c95034a0940f085d5ace72db67dea.png
[20210519230904290.png]: /images/20221020/f95062d7150946cb89ed9d46f1f4b549.png
[20210519231332537.png]: /images/20221020/2e16b9f60ee4444b87d0e3b1f768c91e.png
[20210519231447662.png]: /images/20221020/36353aa5a98b455cb9ece0479b671434.png
[20210519231537527.png]: /images/20221020/11ccd92d26e744fb8ee4bce9280fde1c.png