mysql性能优化-慢查询分析、优化索引和配置

短命女 2022-08-18 15:11 250阅读 0赞

**一、优化概述**

具体看《[最全面的mysql索引知识大盘点][mysql]》

**二、查询与索引优化分析**

当语句执行时间较长时，通过日志的方式进行记录，这种方式就是慢查询的日志。  
**1、临时开启慢查询日志（如果需要长时间开启，则需要更改mysql配置文件）**  
set global slow\_query\_log = on;  
注：如果想关闭慢查询日志，只需要执行 set global slow\_query\_log = off; 即可  
\---------------------  
**2、临时设置慢查询时间临界点  查询时间高于这个临界点的都会被记录到慢查询日志中（如果需要长时间开启，则需要更改mysql配置文件）**  
set long\_query\_time = 1;  
现在起，所有执行时间超过1秒的sql都将被记录到慢查询文件中（我这里就是 /data/mysql/mysql-slow.log）  
\---------------------  
**3、设置慢查询存储的方式**  
set globle log\_output = file;  
说明: 可以看到,我这里设置为了file,就是说我的慢查询日志是通过file体现的,默认是none,我们可以设置为table或者file,如果是table则慢查询信息会保存到mysql库下的slow\_log表中  
\---------------------  
**4、查询慢查询日志的开启状态和慢查询日志储存的位置**  
show variables like '%quer%';  
参数说明:  
slow\_query\_log : 是否已经开启慢查询  
slow\_query\_log\_file : 慢查询日志文件路径  
long\_query\_time :  超过多少秒的查询就写入日志  
log\_queries\_not\_using\_indexes 如果值设置为ON，则会记录所有没有利用索引的查询(性能优化时开启此项,平时不要开启)  
\---------------------  
**5、使用慢查询日志示例**  
cat -n  /data/mysql/mysql-slow.log  
从慢查询日志中，我们可以看到每一条查询时间高于1s钟的sql语句，并可以看到执行的时间是多少。

![70][]

比如上面，就表示sql语句  select \* from comic where comic\_id < 1952000;  执行时间为3.902864秒，超出了我们设置的慢查询时间临界点1s，所以被记录下来了。  
\---------------------  
**6、永久设置慢查询日志开启，以及设置慢查询日志时间临界点**  
linux中，mysql配置文件一般默认在 /etc/my.cnf  
更改对应参数即可。

为何比较慢？见我之前的分享《[玩转explan][explan]》

**三、配置优化**  
1)max\_connections    
2)back\_log    
3)interactive\_timeout    
4)key\_buffer\_size    
5)query\_cache\_size  
6)record\_buffer\_size    
7)read\_rnd\_buffer\_size    
8)sort\_buffer\_size    
9)join\_buffer\_size     
10)able\_cache    
11)ax\_heap\_table\_size    
12)mp\_table\_size    
13)hread\_cache\_size    
14)hread\_concurrency    
15)ait\_timeout

随着mysql版本越来越高，已有些配置的调整一定要参考官方文档，否则可能不会有效。![mysql][]

[mysql]: /images/20220731/17b8e65a2e8344d89ebdb46f9690ad6d.png
[70]: /images/20220731/6edd0234b7b64e4fa5f7412ea0a18943.png
[explan]: /images/20220731/17b8e65a2e8344d89ebdb46f9690ad6d.png#6.%E5%A6%82%E4%BD%95%E5%88%86%E6%9E%90%E5%8F%8A%E4%BC%98%E5%8C%96SQL%E8%AF%AD%E5%8F%A5%E7%9A%84%E6%80%A7%E8%83%BD%E9%97%AE%E9%A2%98