MySQL高级性能优化---慢日志查询

太过爱你忘了你带给我的痛 2021-12-12 20:34 371阅读 0赞

# 1. 概述 #

MySQL的慢查询日志时MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句。  
具体指运行时间超过**long\_query\_time**值的SQL，则会被记录到慢查询日志中。  
**long\_query\_time的默认值为10**，意思是运行10s以上的语句。就会被认作是慢查询。  
默认情况下，**mysql数据库并不启动慢查询日志**，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。  
慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表，  
![在这里插入图片描述][20190714114830446.png]

# 2. 查询是否开启慢查询日志 #

show variables like '%slow_query_log%';

![在这里插入图片描述][20190714115006616.png]  
参数:  
slow\_query\_log 慢查询是否开启 OFF 关闭 ON开启  
slow\_query\_log\_file 日志文件位置

# 3. 开启慢查询 #

set global slow_query_log=1;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]  
这种方式只对当前数据库有效，如果重启后，则会失效

如果想永久生效，在my.cnf文件加上以下配置

slow_query_log=1
    slow_query_log_file=/usr/local/mysql/data/docker-slow.log

# 4. 配置long\_query\_time参数 #

查询long\_query\_time当前阀值，默认是10秒

show variables like 'long_query_time';

![在这里插入图片描述][20190714122115630.png]  
更改long\_query\_time的阀值时间

set global long_query_time=4;

设置完查询long\_query\_time的值还是10s，这个参数断开连接之后才会生效  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]  
重新连接之后long\_query\_time值为4秒  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]

测试以下慢查询功能

select sleep(5);

可以通过 show global status like ‘%slow\_queries%’;这个语句查看日志文件中记录了几条慢查询sql

show global status like '%slow_queries%';

![在这里插入图片描述][20190714122817881.png]

慢查询日志文件在第二步已经获取到/usr/local/mysql/data/docker-slow.log  
查看慢查询日志文件

cat /usr/local/mysql/data/docker-slow.log

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]

# 5. 慢日志分析工具 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 4]

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>-s</td> 
   <td>按照哪种方式排序</td> 
  </tr> 
  <tr> 
   <td>c</td> 
   <td>访问计数</td> 
  </tr> 
  <tr> 
   <td>l</td> 
   <td>锁定时间</td> 
  </tr> 
  <tr> 
   <td>r</td> 
   <td>返回记录</td> 
  </tr> 
  <tr> 
   <td>al</td> 
   <td>平均锁定时间</td> 
  </tr> 
  <tr> 
   <td>ar</td> 
   <td>平均访问记录数</td> 
  </tr> 
  <tr> 
   <td>at</td> 
   <td>平均查询时间</td> 
  </tr> 
  <tr> 
   <td>-t</td> 
   <td>是top n的意思，返回多少条数据</td> 
  </tr> 
  <tr> 
   <td>-g</td> 
   <td>可以跟上正则匹配模式，大小写不敏感</td> 
  </tr> 
 </tbody> 
</table>

## 5.1 得到返回记录集最多的10个SQL ##

mysqldumpslow -s r -t 10 /usr/local/mysql/data/docker-slow.log

![在这里插入图片描述][20190714142349667.png]

# 6. Show Profile #

## 6.1 Show Profile概述 ##

Show Profile是mysql提供的可以用来分析当前会话中sql语句执行的资源消耗情况的工具，可用于sql调优的测量。  
默认情况下处于关闭状态，并保存最近15次的运行结果。把一条sql在mysql当中每一个环节耗费的时间都记录下来。

## 6.2 Show Profile使用 ##

### 6.2.1 查看当前版本是否支持 ###

Show variables like 'profiling';

默认是关闭状态  
![在这里插入图片描述][20190714151316778.png]

### 6.2.3 打开profile ###

set profiling=on;

![在这里插入图片描述][20190714151449122.png]

### 6.2.4 查看结果 ###

先写一条sql并执行

SELECT * from employee WHERE id > 100000

**查看执行结果**

show profiles;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 5]

### 6.2.5 诊断sql ###

这里的数字3是在上一步show profiles的时候sql对应的Query\_ID

show profile cpu,block io for query 3

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 6]

### 6.2.6 诊断结果出现以下选项时，要进行优化 ###

1.  converting HEAP to MyIsAM 查询结果太大，内存不够用了，往磁盘上存上了
2.  Creating tmp table 创建临时表， copy数据到临时表用完再进行删除
3.  Copying to tmp table on disk 把内存中临时表复制到磁盘，危险操作
4.  Locked 被锁定

[20190714114830446.png]: /images/20211210/d4a2c1d58f9840369f44b764e7ae7c8a.png
[20190714115006616.png]: /images/20211210/b8eb396ed6a249f3b7357f22f7ac6227.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20190714115655352.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw==,size_16,color_FFFFFF,t_70
[20190714122115630.png]: /images/20211210/d2e449df9e15473299b133a9fd036846.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 1]: /images/20211210/5423eeb54aa0471b989e826258d7ef9c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 2]: /images/20211210/c1a0a1cb3cd54558804f52382f71b3a5.png
[20190714122817881.png]: /images/20211210/3f200bc61ad44f8f8111d658ed021422.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 3]: /images/20211210/a789788a4e1a434b93d69538c1fb2500.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 4]: /images/20211210/643e5ddb27ac400783e00ee7139b2e2c.png
[20190714142349667.png]: /images/20211210/4c5316a02efe42d5afcb135ad3f615d9.png
[20190714151316778.png]: /images/20211210/d9ab11dd12ff43adbb339d49d3cfa305.png
[20190714151449122.png]: /images/20211210/3821eb2a6bec4b95ad6b716f9ba85250.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 5]: /images/20211210/ff97454848374c04a8b3a61bbf2585f5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3Bqc2RzZw_size_16_color_FFFFFF_t_70 6]: /images/20211210/8a5c22cb37d3499dbfe348997afe7cae.png