定位SQL慢查询

2023-10-11 15:48 由 Enid_Lin 发表于 #数据库

一、概念

MySQL的慢查询（慢查询日志）：是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阈值的语句。
具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是记录运行10秒以上的语句。
默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。（如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响）
慢查询日志支持将日志记录写入文件和数据库表。

二、在数据库中设置SQL慢查询

开启慢查询

方式一：

修改配置文件。在 my.ini 增加几行：**主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

[mysqlld]
;定义查过多少秒的查询算是慢查询（比如2秒）
long_query_time=2

;5.0、5.1等版本配置如下选项
log_slow_queries='mysql_slow_query.log'
;5.5以上版本配置如下选项
slow_query_log='ON'
slow_query_log_file='mysql_slow_query.log'

;记录没有使用索引查询语句
log-queries-not-using-indexe

MySQL和慢查询相关的系统变量如下：

参数	含义
slow_query_log	是否启用慢查询日志， ON为启用，OFF为没有启用，默认为OFF
log_output	日志输出位置，默认为FILE，即保存为文件，若设置为TABLE，则将日志记录到mysql.show_log表中，支持设置多种格式
slow_query_log_file	指定慢查询日志文件的路径和名字
long_query_time	执行时间超过该值才记录到慢查询日志，单位为秒，默认为10

方式二：

通过MySQL数据库开启慢查询。

mysql>set global slow_query_log = ON;
mysql>set global long_query_time = 3600;
mysql>set global log_queries_not_using_indexes = ON;

分析慢查询日志

直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句
```
explain SQL语句
```
比如：

explain的参数：

输出字段	含义
id	执行编号，标志select所属的行。如果在语句中没有子查询或关联查询，只有唯一的select,每行都将显示1。否则内层的select语句一般会顺序编号，在嵌套查询中id越大的语句越先执行。
select_type	显示本行是简单或复杂select。 simple：简单子查询，不包含子查询和union primary：包含union或者子查询，最外层的部分标记为primary subquery：一般子查询中的子查询被标记为subquery，也就是子查询中第一个select语句 derived：派生表--该临时表是从子查询中派生出来的，也就是位于select列表中的查询 union：位于union中第二个及其以后的子查询被标记为union,第一个就被标记为primary如果是位于from中则标记为derived union result：用来从匿名临时表里检索结果的select被标记为union result dependent union：顾名思义，首先需要满足union的条件，及union中第二个以及后面的select语句，同时该语句依赖外部的查询 dependent subquery：子查询中第一个select语句，且该语句依赖外部的查询
table	对应行正在访问哪一个表，表名或者别名。 ·关联查询优化器会为查询选择关联顺序，左侧深度优先 ·当form中有子查询的时候，表名是derivedN的形式,N指向子查询,也就是explain结果中的下一列 ·当有union result的时候，表名是union 1,2等的形式，1,2表示参与uion的query id
type	访问类型，是较为重要的一个指标，结果值从好到坏依次是： system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL，一般来说，得保证查询至少达到range级别，最好能达到ref 访问类型详解： ALL：最坏的情况，全表扫描 index：和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序，但是开销仍然非常大。如在Extra列看到Using index,说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多 range：范围扫描，一个有限制的索引扫描。key列显示使用了哪个索引。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range ref：一种索引访问访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只是用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。 eq_ref：最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生（高效） const：当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效） system：这是const连接类型的一种特例，表仅有一行满足条件。 Null：意味说mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）
possible_keys	显示查询使用了哪些索引，表示该索引可以进行高效地查找，但是列出来的索引对于后续优化过程可能是没有用的
key	显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
key_len	显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不瞬时精确性的情况下，长度越短越好。
ref	显示使用哪个列或常数与key仪器从表中选择行。
rows	显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。（预估值）
filtered	显示通过条件过滤出的行数的百分比估计值。
Extra	Extra是EXPLAIN输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息，MySQL查询优化器执行查询的过程中对查询计划的重要补充信息。 Extra类型详解： Distinct：MySQL发现第1个匹配行后停止为当前的行组合搜索更多的行。 Not exists：MySQL能够对查询进行LEFT JOIN优化发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #)：MySQL没有发现好的可以使用的索,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using hlesort：MySQL需要额外的一次传递以找出如何按排序顺序检索行 Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary：为了解决查询MySQL需要创建一个临时表来容纳结果 Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户 Using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index merge联接类型合并索引扫描。 Using index for group-by：类似于访问表的Using index方式,Using index for group-by表示MVSQL发现了一个索引,可以用来查询GROUPBY或DISTINCT查询的所有列而不要额外搜索硬盘访问实际的表。

常见的慢查询优化
1. 索引没起作用的情况
  - 使用like关键字的查询语句时“%”不在第一个字段
  - 使用多列索引时遵从最左原则
2. 优化数据库结构
  - 将字段很多的表分解成多个表
  - 增加中间表
    
    对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
3. 分解关联查询
  例如：
```
SELECT * FROM tag 
        JOIN tag_post ON tag_id = tag.id
        JOIN post ON tag_post.post_id = post.id
        WHERE tag.tag = 'mysql';
#分解为：
SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);
```
4. 优化limit分页
5. 分析具体的SQL语句

三、定位慢查询

1、查看慢查询日志确定已经执行完的慢查询

确定慢查询日志路径（默认是 MySQL 的数据目录）
```
mysql> show global cariables like "datadir";
```

确定慢查询日志的文件名

show global variables like "slow_query_log_file";

获取慢日志文件已经执行完的慢查询
```
tail -n5 log文件路径
```

2、show processlist 查看正在执行的慢查询

有时慢查询正在执行，已经导致数据库负载偏高了，而由于慢查询还没执行完，因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS 权限，则可以看到所有线程。否则，只能看到当前会话的线程。

如果不使用 FULL 关键字，在 info 字段中只显示每个语句的前 100 个字符，如果想看语句的全部内容可以使用 full 修饰（show full processlist）。

mysql> show processlist\G`

`*************************** 10. row ***************************`

   `Id: 7651833`

   `User: one`

   `Host: 192.168.1.251:52154`

   `db: ops`

   `Command: Query`

   `Time: 3`

  `State: User sleep`

  `Info: select sleep(10)`

`......`

`10 rows in set (0.00 sec)`

Time表示执行时间，Info表示SQL语句。

参考资料：https://zhuanlan.zhihu.com/p/149338523#:~:text=MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。,具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。 long_query_time的默认值为10，意思是记录运行10秒以上的语句。

https://www.jianshu.com/p/7c9e2c479c15