MySQL怎么定位慢查询还有分析执行计划

阳光穿透心脏的1/2处 2022-11-20 08:22 332阅读 0赞

## 背景 ##

大学期间就自学了MySQL数据库，懂得了怎么写简单的SQL查询数据，怎么多表查询。我还一直认为MySQL也不难呀，直到工作了才知道，原来我还是太年轻了～～～

工作期间，写一个简单的CRUD就会碰到上千万的数据量，这个时候简单的select \*就会花费大量的时间在查询上，这是不可容忍的。加上我公司的DBA会开启慢查询日志，还有连接接口报警，我才发现只会简单的MySQL是远远不够的，通过看书和学习总结，于是有了这篇博客。

## 定位慢查询 ##

在我实际工作中，碰到某个功能或者接口需要等待很长的时间才响应的话，我们就应该去定位是不是由慢SQL导致的。一般定位慢SQL有两种解决方案：

*  查看慢查询日志确定已经执行完的慢查询
 *  show processlist查看正在执行的慢查询

### 慢查询日志 ###

MySQL的慢查询日志用来记录在MySQL中响应时间超过参数long\_query\_time（单位秒，默认值10）设置的值并且扫描记录数不小于min\_examined\_row\_limit（默认值0）的语句，能够帮我们找到执行完的慢查询。

首先开启慢查询日志，由参数slow\_query\_log决定是否开启，开启命令如下：  
![在这里插入图片描述][20201027211202393.png_pic_center]

默认环境下，慢查询日志是关闭的。

设置慢查询时间阈值：  
![在这里插入图片描述][20201027211212604.png_pic_center]

确定慢查询日志路径，慢查询日志的路径默认是MySQL的数据目录：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center]

确定慢查询日志的文件名：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center 1]

根据以上查询到的结果可以去打开文件夹查看慢查询日志。

### show processlist ###

有时候一些慢查询已经在执行中，导致数据库负载偏高了，然后这个时候慢查询还没有执行完，这个时候查看慢查询日志是看不到任何结果的。此时可以使用show processlist命令查看正在执行的慢查询。如果由PROCESS权限，则可以看到所有线程。否则，只能看到当前会话的线程。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center 2]

time表示时间，info表示正在执行的SQL。通过判断时间可以得知哪些SQL是慢查询。

## 使用explain分析慢SQL ##

分析SQL执行计划是优化SQL的重要手段，通过以上两种方法定位出慢查询语句后，我们可以通过explain来分析慢查询。

explain可以获取到MySQL中SQL语句的执行计划，比如语句是简单查询还是复杂查询，是否使用了索引，扫描行数等等。可以帮我们选择更好的索引。  
定位表t1的结构如下所示，并且已经插入了1000条数据在t1表里：

CREATE TABLE `t1` (             
      `id` int(11) NOT NULL auto_increment,
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
      PRIMARY KEY (`id`),
      KEY `idx_a` (`a`),
      KEY `idx_b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们试着用explain来分析一下SQL的执行计划，如下所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center 3]

explain执行后的结果参数如下所示：

<table> 
 <thead> 
  <tr> 
   <th>列名</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>查询编号</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>查询类型：简单查询或者复杂查询</td> 
  </tr> 
  <tr> 
   <td>table</td> 
   <td>涉及到的表</td> 
  </tr> 
  <tr> 
   <td>partitions</td> 
   <td>匹配的分区，仅当使用partition关键字才显示该列</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>本次查询的表连接类型</td> 
  </tr> 
  <tr> 
   <td>possible_keys</td> 
   <td>可能用到的索引</td> 
  </tr> 
  <tr> 
   <td>key</td> 
   <td>实际用到的索引</td> 
  </tr> 
  <tr> 
   <td>key_len</td> 
   <td>被选择的索引长度</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>与索引比较的列</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>预计需要扫描的行数，对于InnoDB来说，这个值是一个估值，并不一定准确</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>按条件筛选的行的百分比</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>附加信息</td> 
  </tr> 
 </tbody> 
</table>

以上是explain对SQL生成的执行计划的参数，其中select\_type,type和Extra是非常重要。

## select\_type各个参数解释 ##

<table> 
 <thead> 
  <tr> 
   <th>select_type的值</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>SIMPLE</td> 
   <td>简单查询（不实用关联查询或子查询）</td> 
  </tr> 
  <tr> 
   <td>PRIMARY</td> 
   <td>如果包含关联查询或者子查询，则最外层的查询部分标记为primary</td> 
  </tr> 
  <tr> 
   <td>UNION</td> 
   <td>联合查询中第二个及以后的查询</td> 
  </tr> 
  <tr> 
   <td>DEPENDENT UNION</td> 
   <td>满足依赖外部的关联查询中第二个及以后的查询</td> 
  </tr> 
  <tr> 
   <td>UNION RESULT</td> 
   <td>联合查询的结果</td> 
  </tr> 
  <tr> 
   <td>SUBQUERY</td> 
   <td>子查询中的第一个查询</td> 
  </tr> 
  <tr> 
   <td>DEPENDENT SUBQUERY</td> 
   <td>子查询中的第一个查询，并且依赖外部查询</td> 
  </tr> 
  <tr> 
   <td>DERIVED</td> 
   <td>用到派生表的查询</td> 
  </tr> 
  <tr> 
   <td>MATERIALIZED</td> 
   <td>被物化的子查询</td> 
  </tr> 
 </tbody> 
</table>

##  ##

## type各个参数解释 ##

<table> 
 <thead> 
  <tr> 
   <th>type的值</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>system</td> 
   <td>查询对象只有一行数据，且只能用户MyISAM和Memory引擎的表</td> 
  </tr> 
  <tr> 
   <td>const</td> 
   <td>基于主键或唯一索引查询，最多返回一条结果</td> 
  </tr> 
  <tr> 
   <td>eq_ref</td> 
   <td>表连接时基于主键或非NULL的唯一索引晚餐扫描</td> 
  </tr> 
  <tr> 
   <td>ref</td> 
   <td>基于普通索引的等值查询，或者表间等值连接</td> 
  </tr> 
  <tr> 
   <td>fulltest</td> 
   <td>全文检索</td> 
  </tr> 
  <tr> 
   <td>ref_or_null</td> 
   <td>表连接类型是ref，但进行扫描的索引列中可能包含NULL值</td> 
  </tr> 
  <tr> 
   <td>index_merge</td> 
   <td>利用多个索引</td> 
  </tr> 
  <tr> 
   <td>unique_subquery</td> 
   <td>子查询中使用唯一索引</td> 
  </tr> 
  <tr> 
   <td>index_subquery</td> 
   <td>子查询中使用普通索引</td> 
  </tr> 
  <tr> 
   <td>range</td> 
   <td>利用索引进行范围查询</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>全索引扫描</td> 
  </tr> 
  <tr> 
   <td>ALL</td> 
   <td>全表扫描</td> 
  </tr> 
 </tbody> 
</table>

查询性能由上到下越来越差。

## Extra各个参数的解释 ##

<table> 
 <thead> 
  <tr> 
   <th>Extra常见的值</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using filesort</td> 
   <td>用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘排序</td> 
  </tr> 
  <tr> 
   <td>Using temporary</td> 
   <td>创建一个临时表来存储结构，通过发现对没有索引的列进行GROUP BY时</td> 
  </tr> 
  <tr> 
   <td>Using index</td> 
   <td>使用覆盖索引</td> 
  </tr> 
  <tr> 
   <td>Using where</td> 
   <td>使用where语句来处理结果</td> 
  </tr> 
  <tr> 
   <td>Impossible WHERE</td> 
   <td>对where字句判断的结果总是false而不能选择任何数据</td> 
  </tr> 
  <tr> 
   <td>Using join buffer(Block Nested Loop)</td> 
   <td>关联查询中，被驱动表的关联字段没索引</td> 
  </tr> 
  <tr> 
   <td>Using index condition</td> 
   <td>先条件过滤索引，再查数据</td> 
  </tr> 
  <tr> 
   <td>Select tables optimized away</td> 
   <td>使用某些聚合函数来访问存在索引的某个字段值</td> 
  </tr> 
 </tbody> 
</table>

## 总结 ##

1.  分析了如何定位慢查询
    
     *  查看慢查询日志
     *  show process查看正在执行的SQL
2.  explain分析慢SQL的参数信息

在工作和面试中，SQL性能优化是我们经常遇到的问题，要想做好性能优化，我们必须学会使用SQL优化时需要的工具，进行定位和分析。相信小伙伴们SQL性能优化时一定可以越来越熟练。

## 参考资料 ##

*  《深入浅出MySQL》（第2版）：第18章第1节
 *  《高性能MySQL》（第3版）：第3章第3节

[20201027211202393.png_pic_center]: /images/20221120/f1ce85f659de4881bb18716a44575b72.png
[20201027211212604.png_pic_center]: https://img-blog.csdnimg.cn/20201027211212604.png#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center]: https://img-blog.csdnimg.cn/20201027211227310.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center 1]: https://img-blog.csdnimg.cn/20201027211235468.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201027211306454.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ==,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/20201027211359948.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zNzY4NjQxNQ==,size_16,color_FFFFFF,t_70#pic_center