详解MySQL慢SQL定位、分析

叁歲伎倆 2023-09-27 19:27 150阅读 0赞

**目录**

1.概述

2.慢SQL定位

3.SQL性能分析

3.1.例子

3.2.SQL性能分析

3.3.参数说明

3.3.1.id

3.3.2.select\_type

3.3.3.key\_len

3.3.4.rows

3.3.5.type

3.3.6.extra

--------------------

## 1.概述 ##

解决慢SQL的问题无非3步：

*  定位慢SQL
 *  分析慢SQL
 *  优化慢SQL

本文将按顺序介绍前两步该怎么做，第三步将会在后续的文章中详细讨论。

## 2.慢SQL定位 ##

mysql自带了慢sql日志这个功能，会记录下慢SQL，以下是使用方法。

开启慢sql统计：

SET GLOBAL slow_query_log = on;   //开启慢sql统计开关

设置判断为慢sql的阈值（单位秒）：

SET GLOBAL long_query_time = 1;

设置日志位置：

set global slow_query_log_file="D:\\slow.log";

我在sys\_user这个自己建的表里插入了一百万条数据，简单执行一个数量统计的SQL就会触发慢sql的阈值被记录下来：

![9bff0bbaebc142c0af0a937e878a6281.png][]

## 3.SQL性能分析 ##

### 3.1.例子 ###

三张表，course（课程表）、teacher（教师表）、teacherCard（教师信息表），表关系如下：

![898980645d3f4398805645797051afad.png][]

建表语句：

create table course_info
    (
        cid   bigint primary key,
        name varchar(255),
        _desc varchar(255)
    ) engine = innodb
      default charset = utf8;
    
    create table teacher_info
    (
        tid   bigint primary key,
        name varchar(255),
        _desc varchar(255)
    ) engine = innodb
      default charset = utf8;
    
    create table school_timetable
    (
        id   bigint primary key,
        tid bigint,
        cid bigint
    )engine = innodb
     default charset = utf8;

数据：

insert into course_info value(1,'计算机组成原理','介绍计算机的体系结构');
    insert into course_info value(2,'数据结构','介绍如何高效的组织数据');
    insert into course_info value(3,'操作系统','介绍如何管理调度计算机的资源');
    insert into course_info value(4,'JAVA','天下第一的编程语言');
    
    insert into teacher_info value(1,'冯诺依曼','现代计算机之父');
    insert into teacher_info value(2,'图灵','计算机科学之父');
    insert into teacher_info value(3,'林纳斯托瓦兹','Linux之父');
    insert into teacher_info value(4,'詹姆斯高斯林','java之父');
    
    insert into school_timetable value(1,1,1);
    insert into school_timetable value(2,2,2);
    insert into school_timetable value(3,3,3);
    insert into school_timetable value(4,4,4);

### **3.2.SQL性能分析** ###

可以通过explain关键字来对SQL进行性能分析，MySQL的EXPLAIN是一个查询优化工具，用于分析查询语句的执行计划，它会清晰的展示MySQL将会如何执行某个查询语句，包括执行的步骤、执行顺序、使用的索引、访问表的方式、以及如何连接表等。

以这条SQL为例：

explain select * from teacher_info;

我们能得到以下结果集：

![bebd2192779e47479538fb8878020513.png][]

结果集包含以下参数：

<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>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>完成当前查询，预计所要读取的行数</td> 
  </tr> 
  <tr> 
   <td>Extra</td> 
   <td>额外的信息</td> 
  </tr> 
 </tbody> 
</table>

下面对一些核心参数进行一下详细介绍。

### 3.3.参数说明 ###

#### 3.3.1.id ####

每条SQL都会有个id用来决定执行顺序，

id值同则由大向小降序执行。

explain
    select * from teacher_info
    UNION
    SELECT * FROM school_timetable

![f20e4b1cf01d4c98a6bcf03c5b230b2c.png][]

id值相同则由上往下顺序执行。

explain
    select * from teacher_info
    left join school_timetable on teacher_info.tid=school_timetable.tid
    left join course_info on course_info.cid=school_timetable.cid;

![0d603b7d86304cebb45a4660acbc9141.png][]

#### 3.3.2.select\_type ####

select\_type，查询类型，这个参数会有点绕，但是其实理解即可，它在调优里用处并不大。 ![e549279f0e674c9ca274d7c364c8372d.png][]

#### 3.3.3.key\_len ####

key\_len，实际用到的索引长度，可以用来辅助判断复合索引内生效的部分。

假设我建立了一个复合索引：

CREATE INDEX cid_tid ON school_timetable (cid, tid)

索引全部生效：

explain select * from school_timetable where cid=4 and tid=4

![1f79263d2e4745e68c86351c520c0f8b.png][]

部分索引生效：

explain select * from school_timetable where cid=4

![7f0cc837c0894e5787a38819cbf994ba.png][]

#### 3.3.4.rows ####

完成当前查询，预计所要读取的行数，是个估计值，不准确。

explain select * from school_timetable where cid<4

![b9bfa111c5ed4ba58944b22f2828e531.png][]

#### 3.3.5.type ####

type，查找方式，查询操作的访问类型，它描述了 MySQL 在执行查询时使用的访问方法。

整个执行计划中重中之重的一个参数，整个SQL优化就是围绕此参数进行优化。

常用的访问方法按速度排：

*  `system`：系统表的查询，仅返回一行结果，速度最快。
 *  `const`：常量查询，这种类型的查询是基于常量条件进行的，例如主键或唯一索引的查询，MySQL 在查询过程中已经确定只有一条匹配的结果。
 *  `eq_ref`：唯一索引访问，通过唯一索引查找。这种类型的查询通常用于使用主键或唯一索引进行关联查询，每个索引值只有一条匹配的结果。
 *  `ref`：非唯一索引访问，通过非唯一索引查找。这种类型的查询通常用于使用非唯一索引进行查询，每个索引值可能有多条匹配的结果。
 *  `range`：范围扫描，对索引使用了范围查找，例如使用 `BETWEEN`、`<`、`>` 等操作符进行的查询。
 *  `index`：索引扫描，MySQL 使用非唯一索引进行扫描，表示在索引列上进行了查找。
 *  `ALL`：全表扫描，MySQL 将对表中的每一行进行遍历。这种类型的查询通常发生在没有使用索引或无法使用索引的情况下，性能较差。

**在实际工程中，前三种情况是很难达到的，基本没有什么适用场景，所以我们需要尽力保障能达到ref、range或者index，也就是至少要保证索引是有效的。**

以下是前文表中出现以上情况的示例，由于system和eq\_ref比较难造，暂时不包括：

**const:**

**![c4a8b826ea114dcb9d45c9f249d6065e.png][]**

**ref:**

**![b0d92624f55b49039569010aaba3f196.png][]**

**index:**

![f5121ca7782840a696543c9358145e25.png][]

**range:**

**![5e0fedf293d047b69327600cb1e2cb19.png][]**

#### 3.3.6.extra ####

这个字段表示查询后是否还要进行额外的操作再生成结果集。常见的值如下：

1.  `Using index`: 表示查询使用了覆盖索引，即查询的数据可以直接从索引中获取，而无需进一步访问表数据。
2.  `Using where`: 表示查询使用了 `WHERE` 条件进行过滤。
3.  `Using temporary`: 表示查询需要创建临时表来处理结果集，通常发生在需要进行排序、分组或多表连接的情况下。
4.  `Using filesort`: 表示查询需要进行排序操作，MySQL 无法使用索引进行排序，因此需要额外的文件排序操作。
5.  `Using index condition`: 表示查询使用了索引条件进行过滤。
6.  `Using join buffer`: 表示查询使用了连接缓冲区。
7.  `Distinct`: 表示查询使用了 `DISTINCT` 关键字进行去重。
8.  `Full scan on NULL key`: 表示在索引上执行全表扫描，但索引键值为空。
9.  `Range checked for each record`: 表示对每条记录都进行了范围检查。
10. `Using index for group-by`: 表示查询使用了索引来进行分组操作。
11. `Using index for order by`: 表示查询使用了索引来进行排序操作。
12. `Using index condition; Using where`: 表示查询同时使用了索引条件和 `WHERE` 条件。

对于 SQL 调优来说，`extra` 字段是非常重要的。它提供了关于查询执行计划中的额外信息，可以帮助我们识别查询的性能瓶颈和优化的方向。

通过分析 `extra` 字段，我们可以判断以下情况：

1.  是否使用了索引：`Using index` 表示查询使用了覆盖索引，可以避免访问表数据，提高查询性能。如果没有使用索引，可能需要考虑添加适当的索引来优化查询。
2.  是否进行了排序：`Using filesort` 表示需要额外的文件排序操作，这可能导致性能下降。如果频繁出现文件排序，可能需要考虑优化查询或调整索引。
3.  是否创建了临时表：`Using temporary` 表示需要创建临时表来处理结果集，可能会影响性能。需要审查查询语句并考虑是否可以避免使用临时表。
4.  是否进行了全表扫描：`Using index` 表示使用了索引，而 `Using index; Using where` 表示同时使用了索引和 `WHERE` 条件进行过滤。如果出现 `Using index` 之外的情况，可能需要优化查询或调整索引以避免全表扫描。
5.  是否使用了连接缓冲区：`Using join buffer` 表示使用了连接缓冲区，可能会影响查询性能。需要审查查询语句并考虑是否可以优化连接操作。

[9bff0bbaebc142c0af0a937e878a6281.png]: https://img-blog.csdnimg.cn/9bff0bbaebc142c0af0a937e878a6281.png
[898980645d3f4398805645797051afad.png]: https://img-blog.csdnimg.cn/898980645d3f4398805645797051afad.png
[bebd2192779e47479538fb8878020513.png]: https://img-blog.csdnimg.cn/bebd2192779e47479538fb8878020513.png
[f20e4b1cf01d4c98a6bcf03c5b230b2c.png]: https://img-blog.csdnimg.cn/f20e4b1cf01d4c98a6bcf03c5b230b2c.png
[0d603b7d86304cebb45a4660acbc9141.png]: https://img-blog.csdnimg.cn/0d603b7d86304cebb45a4660acbc9141.png
[e549279f0e674c9ca274d7c364c8372d.png]: https://img-blog.csdnimg.cn/e549279f0e674c9ca274d7c364c8372d.png
[1f79263d2e4745e68c86351c520c0f8b.png]: https://img-blog.csdnimg.cn/1f79263d2e4745e68c86351c520c0f8b.png
[7f0cc837c0894e5787a38819cbf994ba.png]: https://img-blog.csdnimg.cn/7f0cc837c0894e5787a38819cbf994ba.png
[b9bfa111c5ed4ba58944b22f2828e531.png]: https://img-blog.csdnimg.cn/b9bfa111c5ed4ba58944b22f2828e531.png
[c4a8b826ea114dcb9d45c9f249d6065e.png]: https://img-blog.csdnimg.cn/c4a8b826ea114dcb9d45c9f249d6065e.png
[b0d92624f55b49039569010aaba3f196.png]: https://img-blog.csdnimg.cn/b0d92624f55b49039569010aaba3f196.png
[f5121ca7782840a696543c9358145e25.png]: https://img-blog.csdnimg.cn/f5121ca7782840a696543c9358145e25.png
[5e0fedf293d047b69327600cb1e2cb19.png]: https://img-blog.csdnimg.cn/5e0fedf293d047b69327600cb1e2cb19.png