【MYSQL】MYSQL 的学习教程（七）之慢 SQL 优化思路

Myth丶恋晨 2024-02-05 18:19 17阅读 0赞

## 1. 慢 SQL 优化思路 ##

1.  慢查询日志记录慢 SQL
2.  explain 分析 SQL 的执行计划
3.  profile 分析执行耗时
4.  Optimizer Trace 分析详情
5.  确定问题并采用相应的措施

### 1. 慢查询日志记录慢 SQL ###

> 如何定位慢SQL呢？

我们可以通过 慢查询日志 来查看慢 SQL。

①：开启慢查询日志：

*  `SET global slow_query_log = ON;`：设置慢查询开启的状态（ON：开启；OFF：关闭）
 *  `slow_query_log_file`：设置慢查询日志存放的位置
 *  `SET global log_queries_not_using_indexes = ON;`：记录没有使用索引的查询 SQL。前提是`slow_query_log` 的值为 ON，否则不会奏效
 *  `SET long_query_time = 10;`：设置慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询 记录到日志文件中

②：查看慢查询日志配置：

*  `show variables like 'slow_query_log%`
 *  `show variables like 'long_query_time'`

③：慢查询日志分析工具：

`mysqldumpslow`：该工具是慢查询自带的分析慢查询工具，一般只要安装了mysql，就会有该工具

# 取出使用最多的10条慢查询
    mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log 
    # 取出查询时间最慢的3条慢查询
    mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log 
    # 得到按照时间排序的前10条里面含有左连接的查询语句
    mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log 
    # 按照扫描行数最多的
    mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log

注意: 使用 `mysqldumpslow` 的分析结果不会显示具体完整的sql语句,只会显示sql的组成结构;

假如: `SELECT * FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;`

> Count: 1 Time=1.91s (1s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos\_dba\[vgos\_dba\]@\[10.130.229.196\]  
> SELECT \* FROM sms\_send WHERE service\_id=N GROUP BY content LIMIT N, N;

工具其实还有很多，并不限制只有这一种，还有 `pt-query-digest`、`mysqlsla` 等，这些都是可以定位慢查询日志的小工具

慢查询原因：

*  全表扫描：explain分析type属性all
 *  全索引扫描：explain分析type属性index
 *  索引过滤性不好：靠索引字段选型、数据量和状态、表设计
 *  频繁的回表查询开销：尽量少用select \*，使用覆盖索引

[＜转＞详解 慢查询 之 mysqldumpslow][_ _ mysqldumpslow]

## 2. explain 查看分析 SQL 的执行计划 ##

当定位出查询效率低的 SQL 后，可以使用 explain 查看 SQL 的执行计划。

当 explain 与 SQL 一起使用时，MySQL 将显示来自优化器的有关语句执行计划的信息。即：MySQL 解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表等信息：

![在这里插入图片描述][e209b6e50f1b4cefbb3af299b0e20e14.png]  
一般来说，我们需要重点关注 `type、key、rows、extra`

#### 13.1 type ####

type 表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：`system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL`

*  **NULL**：表示不用访问表，速度最快
 *  **system**：这种类型要求数据库表中只有一条数据，是 const 类型的一个特例，一般情况下是不会出现的
 *  **const**：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，速度非常快
 *  **eq\_ref**：常用于主键或唯一索引扫描，一般指使用主键的关联查询
 *  **ref** : 常用于非主键和唯一索引扫描
 *  **ref\_or\_null**：这种连接类型类似于 ref，区别在于 MySQL 会额外搜索包含 NULL 值的行
 *  **index\_merge**：使用了索引合并优化方法，查询使用了两个以上的索引
 *  **unique\_subquery**：类似于 eq\_ref，条件用了 in 子查询
 *  **index\_subquery**：区别于 unique\_subquery，用于非唯一索引，可以返回重复值
 *  **range**：常用于范围查询，比如：between … and 或 In 等操作
 *  **index**：全索引扫描
 *  **all**：全表扫描

#### 13.2 possible\_keys ####

表示查询时能够使用到的索引（显示的是索引名称），只是可能用到的索引，而不是实际上用到的索引

#### 13.3 key ####

该列表示实际用到的索引。一般配合 `possible_keys` 列一起看

#### 13.4 rows ####

MySQL查询优化器会根据统计信息，估算 SQL 要查询到结果需要扫描多少行记录。原则上 rows 是越少效率越高，可以直观的了解到SQL效率高低

#### 13.5 extra ####

该字段包含有关 MySQL 如何解析查询的其他信息，它一般会出现这几个值：

*  **Using filesort**：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于 order by 语句。建议优化
 *  **Using temporary**: 表示使用了临时表，性能特别差，需要重点优化。一般多见于 group by 语句，或者 union 语句
 *  **Using index** ：表示用了覆盖索引
 *  **Using where** : 表示使用了 where 条件过滤，需要通过索引回表查询数据
 *  **Using index condition**：MySQL5.6 之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据
 *  **NULL**：查询的列未被索引覆盖

总结：

<table> 
 <thead> 
  <tr> 
   <th>extra</th> 
   <th>where 条件</th> 
   <th>select 的字段</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>null</td> 
   <td>where 筛选条件是索引的前导列</td> 
   <td>查询的列未被索引覆盖</td> 
  </tr> 
  <tr> 
   <td>Using index</td> 
   <td>where 筛选条件是索引的前导列</td> 
   <td>查询的列被索引覆盖</td> 
  </tr> 
  <tr> 
   <td>Using where; Using index</td> 
   <td>where 筛选条件是索引列之一但不是前导列或者where筛选条件是索引列前导列的一个范围</td> 
   <td>查询的列被索引覆盖</td> 
  </tr> 
  <tr> 
   <td>Using where;</td> 
   <td>where 筛选条件不是索引列</td> 
   <td>-</td> 
  </tr> 
  <tr> 
   <td>Using where;</td> 
   <td>where 筛选条件不是索引前导列、是索引列前导列的一个范围(&gt;)</td> 
   <td>查询列未被索引覆盖</td> 
  </tr> 
  <tr> 
   <td>Using index condition</td> 
   <td>where 索引列前导列的一个范围（&lt;、between）</td> 
   <td>查询列未被索引覆盖</td> 
  </tr> 
 </tbody> 
</table>

两种排序的情况：

<table> 
 <thead> 
  <tr> 
   <th>extra</th> 
   <th>出现场景</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Using filesort</td> 
   <td>filesort主要用于查询数据结果集的排序操作，首先MySQL会使用sort_buffer_size大小的内存进行排序，如果结果集超过了sort_buffer_size大小，会把这一个排序后的chunk转移到file上，最后使用多路归并排序完成所有数据的排序操作。</td> 
  </tr> 
  <tr> 
   <td>Using temporary</td> 
   <td>MySQL使用临时表保存临时的结构，以用于后续的处理，MySQL首先创建heap引擎的临时表，如果临时的数据过多，超过max_heap_table_size的大小，会自动把临时表转换成MyISAM引擎的表来使用。</td> 
  </tr> 
 </tbody> 
</table>

> filesort 只能应用在单个表上，如果有多个表的数据需要排序，那么MySQL会先使用using temporary保存临时数据，然后再在临时表上使用filesort进行排序，最后输出结果

#### 13.6 select\_type ####

select\_type：表示查询的类型。

常用的值如下：

*  **SIMPLE** ： 表示查询语句不包含子查询或 UNION
 *  **PRIMARY**：表示此查询是最外层的查询
 *  **UNION**：表示此查询是 UNION 的第二个或后续的查询
 *  **DEPENDENT UNION**：UNION 中的第二个或后续的查询语句，使用了外面查询结果
 *  **UNION RESULT**：UNION 的结果
 *  **SUBQUERY**：SELECT 子查询语句
 *  **DEPENDENT SUBQUERY**：SELECT子查询语句依赖外层查询的结果

最常见的查询类型是 `SIMPLE`，表示我们的查询没有子查询也没用到 UNION 查询

#### 13.7 filtered ####

该列是一个百分比的值，通过查询条件最终查询记录行数和通过 type 字段扫描记录行数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

#### 13.8 key\_len ####

表示查询使用了索引的字节数量（可以判断是否全部使用了组合索引）

key\_len的计算规则如下：

1.  字符串类型：字符串长度跟字符集有关：latin1 = 1、gbk = 2、utf8 = 3、utf8mb4 = 4
    
     *  `char(n)`：n \* 字符集长度
     *  `varchar(n)`：n \* 字符集长度 + 2字节
2.  数值类型
    
     *  `TINYINT`：1个字节
     *  `SMALLINT`：2个字节
     *  `MEDIUMINT`：3个字节
     *  `INT`、`FLOAT`：4个字节
     *  `BIGINT`、`DOUBLE`：8个字节
3.  时间类型
    
     *  `DATE`：3个字节
     *  `TIMESTAMP`：4个字节
     *  `DATETIME`：8个字节
4.  字段属性
    
     *  NULL 属性占用1个字节，如果一个字段设置了 NOT NULL，则没有此项

## 3. profile 分析执行耗时 ##

explain 只是看到 SQL 的**预估执行计划**，**如果要了解 SQL 真正的执行线程状态及消耗的时间，需要使用 profiling**

开启 profiling 参数后，后续执行的 SQL 语句都会记录其资源开销，包括 IO，上下文切换，CPU，内存等等，我们可以根据这些开销进一步分析当前慢 SQL 的瓶颈再进一步进行优化

查看是否开启 profiling：

show variables like '%profil%'

开启 profiling ：

set profiling=ON

使用 profiling ：

show profiles

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

show profiles 会显示最近发给服务器的多条语句，条数由变量 `profiling_history_size` 定义，默认是 15。如果我们需要看单独某条 SQL 的分析，可以 show profile 查看最近一条 SQL 的分析，也可以使用 `show profile for query id`（其中id就是show profiles中的 QUERY\_ID）查看具体一条的 SQL 语句分析：

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

## 4. Optimizer Trace 分析详情 ##

profile 只能查看到 SQL 的执行耗时，但是无法看到 SQL 真正执行的过程信息，即不知道 MySQL 优化器是如何选择执行计划。这时候，我们可以使用 `Optimizer Trace`，它可以跟踪执行语句的解析优化执行的全过程

开启：

set optimizer_trace="enabled=on";

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

查看分析其执行树，会包括三个阶段：

*  join\_preparation：准备阶段
 *  join\_optimization：分析阶段
 *  join\_execution：执行阶段

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

## 5. 确定问题并采用相应的措施 ##

确认问题，就采取对应的措施。

*  多数慢 SQL 都跟索引有关，比如不加索引，索引不生效、不合理等，这时候，我们可以优化索引
 *  我们还可以优化 SQL 语句，比如一些in元素过多问题（分批），深分页问题（基于上一次数据过滤等），进行时间分段查询
 *  SQL 没办法很好优化，可以改用 ES 的方式，或者数仓
 *  如果单表数据量过大导致慢查询，则可以考虑分库分表
 *  如果数据库在刷脏页导致慢查询，考虑是否可以优化一些参数，跟 DBA 讨论优化方案
 *  如果存量数据量太大，考虑是否可以让部分数据归档

[_ _ mysqldumpslow]: https://blog.csdn.net/sunshingheavy/article/details/124679558
[e209b6e50f1b4cefbb3af299b0e20e14.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/9cada4e4ce8d4abbbccfa1ca5361ce4f.png
[95f4ba6108894e539edcf7934a05f395.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/039b4779be10441a9c28a8887008458d.png
[bf2d7a51fd9848aaa25e4b448cf08818.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/d6e24ef8839249a7ad7337177acb093b.png
[b9768afa2f5340a9b57fd3ce84f8ef01.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/6abcf27c56694a29947d5a7704903db8.png
[55fcb461cbfa4adba7b3e0f721dcea02.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/c99f46cfa90d4fbbace4a40d65735cf0.png