MySQL开启慢查询定位优化SQL

ゞ浴缸里的玫瑰 2023-09-28 15:44 136阅读 0赞

## 启用慢查询日志 ##

要想优化查询SQL语句时，首先要清楚我们应该优化哪些SQL？毫无疑问，肯定是要优化查询时间长的SQL语句。假设一条SQL语句的执行时间是：1s，5s，10s。。。哪些算是查询时间较长的呢？

这涉及到如何定义查询时间过长？以及在哪个地方能够搜集查看到这些查询时间过长的SQL？只有清楚了这2个问题，我们才能够有针对性的优化SQL语句。

在MySQL中，只需要简单的设置3个变量就可以；

*  long\_query\_time：它的值是数字（默认值10），单位秒；SQL查询时间超过该数字，那么SQL将会被定义为查询时间过长记录在慢查询日志里；
 *  slow\_query\_log ：ON/OFF ; ON :表示开启慢查询日志，SQL查询时间超过long\_query\_time定义的时间，就会被记录在日志里； OFF ：表示关闭慢查询日志；
 *  slow\_query\_log\_file：日志的路径；也有默认值；

![在这里插入图片描述][5d1c987dc2c543f5aa70899d2c652401.png_pic_center]

*  查询

<table> 
 <thead> 
  <tr> 
   <th>查询变量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><b>show variables like ‘%变量名%’ ;</b></td> 
  </tr> 
 </tbody> 
</table>

*  设置

<table> 
 <thead> 
  <tr> 
   <th>设置变量</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>set long_query_time=0.1</td> 
  </tr> 
  <tr> 
   <td>set global slow_query_log = on;</td> 
  </tr> 
 </tbody> 
</table>

## 慢查询日志 ##

D:\IT_SOFT\DATABASE\MySQL\mysql\bin\mysqld, Version: 8.0.19 (MySQL Community Server - GPL). started with:
    TCP Port: 3306, Named Pipe: MySQL
    Time                 Id Command    Argument
    # Time: 2022-07-18T04:39:05.766496Z
    # User@Host: root[root] @ localhost [::1]  Id:     8
    # Query_time: 0.349242  Lock_time: 0.000339 Rows_sent: 14  Rows_examined: 14
    use ssm01;
    SET timestamp=1658119145;
    select * from books;

这是在使用：select \* from books;查询时，产生的慢查询日志；设置的long\_query\_time=0.1s, 因此超过这个时间的查询SQL都会被记录在这个日志里 ；

*  Query\_time: 表示SQL查询时间。
 *  Lock\_time：表示等待获取锁的时间。
 *  Rows\_sent: 表示查询结果的行数；
 *  Rows\_examined：表示执行这段SQL扫描的行数；

SQL的实际执行时间：exeTime = Query\_time + Lock\_time；有一种情况会导致SQL运行时间很长但是不会被记录到慢日志中：如果等待获取锁的时间很长，但是查询时间很短没有超过long\_query\_time，这种情况SQL是不会被记录在慢日志中；如果生产环境遇到这种情况，就要考虑其他原因了；  
  在实际环境中，可能会产生一个很大的慢日志文件，如果这样直接看日志文件可能不太方便，因此MySQL自带了一个分析慢日志文件的工具：mysqldumpslow；在windows平台还要安装perl环境才能运行mysqldumpslow；([windows平台使用mysqldumpslow][windows_mysqldumpslow])

在确定了哪些SQL需要优化之后，用[explain][],查看SQL的执行计划，重点关注type，key，Extra；

*  key表示用到的索引，
 *  type表示索引类型，几种索引类型的效率比较：system > const>eq\_ref>ref>range>index>all
 *  Extra包含MySQL解决查询的额外信息；

[5d1c987dc2c543f5aa70899d2c652401.png_pic_center]: https://img-blog.csdnimg.cn/5d1c987dc2c543f5aa70899d2c652401.png#pic_center
[windows_mysqldumpslow]: https://blog.csdn.net/living_ren/article/details/79360242
[explain]: https://blog.csdn.net/m0_37550986/article/details/116550010