慢查SQL定位与分析
dd
  2023-07-09 02:22:57 2023-07-09 02:22   2023-08-05 18:57:16      2.4k 字  9 分钟  

慢查询概述

定义：查询时间超过指定时间的SQL语句

影响：数据查询的时间变慢，一方面会影响用户的体验；另一方面在高并发的场景下慢查询会占用数据库连接，查询请求堆积，DB服务器CPU一直占用，严重会导致DB挂了

出现场景：全表扫描、索引使用不当、复杂SQL语句

慢查询定位：慢查询日志

MySQL中会有一个阈值（默认为10s），在SQL执行过程中，会将运行时间超过该阈值的SQL语句存储到慢查询日志中。因此可以通过该日志定位慢查询SQL

默认情况下是不开启该日志的，需要手动开启

1
2
3
4
5
6
7
mysql> show variables like 'slow_query_log';                                                                                                   
+----------------+-------+                                                                                                                     
| Variable_name  | Value |                                                                                                                     
+----------------+-------+                                                                                                                     
| slow_query_log | OFF   |                                                                                                                     
+----------------+-------+                                                                                                                     
1 row in set (0.00 sec)

慢查询日志相关参数

long_query_time

慢查询阈值，默认为10s。在MySQL5.1之后以微秒记录

long_query_not_using_indexes

如果运行的SQL没有使用到索引，MySQL同样会将该SQL语句存储到慢查询日志中

long_throttle_queries_not_using_indexes（MySQL5.6.5之后）

每分钟允许记录到slow log中并未使用索引的SQL语句次数。默认为0，表示不限制

主要是为了防止slow log过大

log_output

指定慢查询日志的输出格式，默认为FILE。为TABLE时可以在mysql架构下的slow_log中查看

查看慢查询日志

当slow log中记录的慢SQL过多的时候，直接查看不太方便。有两种方式可以解决这个问题：

使用MySQL提供的mysqldumpslow命令

1
2
3
4
5
root@iZwz9evxymdqdx56ut6rv0Z:/var/lib/mysql# mysqldumpslow /var/lib/mysql/iZwz9evxymdqdx56ut6rv0Z-slow.log                                     
                                                                                                                                               
Reading mysql slow query log from /var/lib/mysql/iZwz9evxymdqdx56ut6rv0Z-slow.log                                                              
Count: 2  Time=10.00s (20s)  Lock=0.00s (0s)  Rows=1.0 (2), root[root]@localhost                                                               
  select sleep(N)

会对SQL语句中的查询条件参数进行替换，替换后相同的SQL语句看成整体进行统计

使用slow_log表

MySQL5.1后可以将慢查询日志记录存放在mysql架构下的slow_log表中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
mysql> show variables like 'log_output';                                                                                                       
+---------------+-------+                                                                                                                      
| Variable_name | Value |                                                                                                                      
+---------------+-------+                                                                                                                      
| log_output    | FILE  |                                                                                                                      
+---------------+-------+                                                                                                                      
1 row in set (0.01 sec)                                                                                                                        
                                                                                                    
mysql> set GLOBAL log_output='TABLE';                                                                                                          
Query OK, 0 rows affected (0.00 sec)                                                                                                           
                                                                                                                                               
mysql> select * from mysql.slow_log;                                                                                                           
Empty set (0.01 sec)                                                                                                                           
                                                                                                           
mysql> select sleep(10);                                                                                                                       
+-----------+                                                                                                                                  
| sleep(10) |                                                                                                                                  
+-----------+                                                                                                                                  
|         0 |                                                                                                                                  
+-----------+                                                                                                                                  
1 row in set (10.00 sec)                                                                                                                       
                                                                                                                                               
mysql> select sleep(10);                                                                                                                       
+-----------+                                                                                                                                  
| sleep(10) |                                                                                                                                  
+-----------+                                                                                                                                  
|         0 |                                                                                                                                  
+-----------+                                                                                                                                  
1 row in set (10.00 sec)                                                                                                                       
                                                                                                                                               
mysql> select * from mysql.slow_log;                                                                                                           
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------
+-----------+-----------+------------------------------------+-----------+                                                                     
| start_time                 | user_host                 | query_time      | lock_time       | rows_sent | rows_examined | db | last_insert_id 
| insert_id | server_id | sql_text                           | thread_id |                                                                     
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------
+-----------+-----------+------------------------------------+-----------+                                                                     
| 2023-07-06 23:59:40.406137 | root[root] @ localhost [] | 00:00:10.000616 | 00:00:00.000000 |         1 |             1 |    |              0 
|         0 |         1 | 0x73656C65637420736C65657028313029 |      4220 |                                                                     
| 2023-07-06 23:59:51.690646 | root[root] @ localhost [] | 00:00:10.000516 | 00:00:00.000000 |         1 |             1 |    |              0 
|         0 |         1 | 0x73656C65637420736C65657028313029 |      4220 |                                                                     
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+----+----------------
+-----------+-----------+------------------------------------+-----------+                                                                     
2 rows in set (0.00 sec)

slow_log是使用CSV存储引擎，对大数据量下的查询效率不高，可以将其更改为MyISAM，同时在start_time列上添加索引（需要先将慢查询关闭）

逻辑读取 & 物理读取

当数据库的容量比较小时，可能由于数据全部被缓存在缓冲池中，导致一些不合格的SQL运行时间比较短，无法记录在slow_log中

InnoDB增强了对SQL语句的捕获，在slow log中增加了物理读取与逻辑读取

物理读取：从磁盘IO中进行读取的次数

逻辑读取：所有的读取

long_query_io

将超过指定逻辑IO次数的SQL语句记录到slow log中，默认为100

slow_query_type（为了兼容原MySQL）

0：不将SQL语句记录到slow log

1：根据运行时间将SQL语句记录到slow log

2：根据逻辑IO次数将SQL语句记录到slow log

3：根据运行时间与逻辑IO将SQL记录到slow log

慢查询分析

慢SQL可以从SQL语句结构、使用场景、执行计划三方面进行分析

SQL语句结构

对于SQL结构拆解分析需要理清以下三点：

  • SQL的结构特点：简单单一查询？join关联查询？子查询？……
  • SQL语句关键字可能带来的问题：like模糊匹配、order by/group by/join使用的驱动表大小、limit高起点的深翻页问题、not in带来的全表扫描问题……
  • 相关表建立的索引情况

使用场景

站在sql语句语法本身之外的角度分析sql的使用方式上，包括但不限如下几点：

使用的业务场景：

  • 需要支持模糊关键词搜索
  • 需要多条件的复杂的在线实时查询
  • 定时任务
  • 页面查询 or 系统调用
  • ……

运行的环境：

  • 产生慢sql的应用机器/DB实例：预发or线上机器产生的，之前遇到过预发环境工具导致的慢sql问题；是否是同一个DB实例产生慢sql，可能实例磁盘问题或数据倾斜等问题
  • sql运行的周期/频率/时间点：根据周期运行规律可以判断是否为定时任务产生，定时任务有时会捞取大量数据扫全表导致慢sql；其次针对某一时间点的某个特定DB实例造成的慢sql，可以通过DBPaas分析，发现是由于夜间的磁盘抖动造成慢sql，联系DBA确认确实磁盘有问题

分析执行计划

要优化慢查询，最重要的是需要知道一条SQL语句的执行需要经历哪些步骤，在哪些步骤可能会存在导致查询时间过长，最后在卡点处进行优化

一条SQL语句的执行主要分为几个步骤：语法解析==》生成执行计划==》执行SQL语句==》输出结果

一般的慢查询主要卡点在执行SQL语句这一步，我们需要通过执行计划去分析是怎么执行的，就可以有效帮助我们分析SQL性能差的原因

SQL性能优化的目标：type至少要达到range级别，要求是ref级别，如果可以最好是const

慢查询日志分析

对于慢查询日志中的每一条SQL语句，主要从以下的几个指标去进行分析：

参数 含义 要点
query_time 查询时间 默认超过10s会被记录
lock_time 锁表时间 当该时间占据执行时间的大部分时，要注意锁表的范围、冲突情况等
rows_sent 查询返回的结果条数 当检查条数较多但是返回条数较少时，通常说明SQL语句可以优化
rows_examined 查询检查的结果条数
rows_affected 查询影响的条数

explain分析

通过explain可以分析SQL语句的执行计划，但是只是表示MySQL可能的执行计划，在实际执行过程中并不是完全按照该计划执行，只有慢查询日志中显示的才是真正的执行计划

以下是explain执行计划中的每个字段详情：

参数 含义 要点
id 查询执行的顺序一般有多少个select（子查询）就有多少个idid越大执行的优先级越高，越先被执行，id相同则从上往下执行，id为null表示最后执行
select_type SIMPLE：简单查询，不包含子查询和unionPRIMARY：复杂查询中最外层的selectSUBQUERY：包含在select中的子查询DERIVED：包含在from子句中的子查询，结果会存放在临时表中
table 当前查询正在访问的表
type 表示MySQL如何查找表中的行system、const：primary key或unique key与常数比较是，最多只有一个匹配行，读取一次。system是const的特里，表中只有一条数据时为systemeq_ref：primary key或unique key索引的所有部分被连接使用，最多只有一个匹配行ref：使用普通索引或联合索引的部分前缀与值匹配，可能会找到多个符合条件的行range：使用一个索引来检索给定范围的行index：全表扫索引，通过扫描整棵索引树得到结果如果是覆盖索引则Extra为Using indexall：全表扫描 表现从好到坏是：system > const > eq_ref > ref > range > index > all
possible_keys 可能会使用到哪些索引进行查询 possibles_keys和key均不为空：正常使用索引possibles_key不为空，key为空：通过索引并不能提高查询效率，一般是因为索引字段选择性差或表数据量小possibles_key为空，keys不为空：一般是因为where没有命中索引，但是查询的列是索引字段，命中了覆盖索引
key 实际使用到的索引
key_len 索引使用到的字节数 在联合索引中可以用于判断使用到了索引中的哪些列：tinyint：1smallint：2int：4bigint：8data：3timestamp：4datetime：8
ref 在key列记录的索引中，表查找值所用到的列或常量
rows 估计要读取并检查的行数
filtered 返回结果的行占需要读到的行(rows列的值)的百分比
extra 额外的信息Using index：使用覆盖索引Using where：使用where语句处理结果，并且查询的列未被索引覆盖Using index condition：查询的列不完全被索引覆盖，需要回表查询Using temporary：需要使用临时表处理查询Using filesort：使用外部排序而不是索引排序Using tables optimized away：使用某些聚合函数访问存在索引的字段Using join buffer(Block Nested Loop）：使用join buffer(BNL算法）进行关联执行Using MRR(Multi-Range Read)：使用辅助索引进行多范围读
  • 本文标题：慢查SQL定位与分析
  • 本文作者：dd
  • 创建时间：2023-07-09 02:22:57
  • 本文链接：2023/07/09/慢查SQL定位与分析/
  • 版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
  1. 慢查询概述
  2. 慢查询定位：慢查询日志
    1. 慢查询日志相关参数
    2. 查看慢查询日志
    3. 逻辑读取 & 物理读取
  3. 慢查询分析
    1. SQL语句结构
    2. 使用场景
    3. 分析执行计划
      1. 慢查询日志分析
      2. explain分析