MySQL慢查询日志

slow日志记录的时间是结束时间，也就是sql开始时间要在日志中的记录时间往前加上慢查询的秒数

开启慢查询日志查询记录

show variables like 'slow_query_log%';
show variables like 'long_query_time%';

# 临时开启
set global slow_query_log=1;

# 大于该值的时间（秒）则为慢日志
set global long_query_time=5;

在实际使用中在慢查询日志文件中出现了很多查询时间在小于指定时间的记录。

主要跟以下两个参数有关

log_queries_not_using_indexes
log_throttle_queries_not_using_indexes

开启了log_queries_not_using_indexes后，未使用索引的查询是否被记录和slow_query_time的设置无关

log_queries_not_using_indexes的作用是用于记录未走索引的查询，不论查询是否大于定义的long_query_time值，都会被记录，如果开启了这个配置，会导致慢日志快速增加而大量占用磁盘空间。

show variables like '%log_queries%';

# 临时关闭log_queries_not_using_indexes配置，要想重启后仍然生效，
# 请在my.cnf中添加log_queries_not_using_indexes=0
set global log_queries_not_using_indexes=0;

如果想记录未通过索引查询的语句日志；这时就要用log_throttle_queries_not_using_indexes这个参数来限制未走索引的查询语句记录条数，默认是0，即不限制。如果配置一个大于0的值，例如5，则表示每分钟只记录5条不走索引的查询语句，超过的未走索引语句只记录总数量和总体花费时间。 临时配置

show variables like '%log_throttle_queries_not_using_indexes%';
set global log_throttle_queries_not_using_indexes=10;

要重启后仍然生效需要修改my.cnf中的配置，在[mysqld]部分增加log_throttle_queries_not_using_indexes=5，具体值根据自己的需求任意定义。 下面就是一个slow_log中记录的未走索引查询超过限制之后，有48条未走索引的查询记录

# Time: 2022-04-06T09:53:17.906190Z
# User@Host: [] @  []  Id: 651863
# Query_time: 0.317486  Lock_time: 0.004418 Rows_sent: 1  Rows_examined: 2
use mml_backend;
SET timestamp=1649238797;
throttle:         48 'index not used' warning(s) suppressed.;

---

控制慢查询日志大小

使用[[../../Linux命令/logrotate命令–管理日志文件|logrotate]]进行控制

在/etc/logrotate.d/mysql中加入如下内容

/data/mysql/slow.log {
        weekly	# 每周轮替
        rotate 4	# 保留4个
        missingok	# 忽略错误
        maxsize 1G	# 单个日志最大1G或到时间轮替
        nocompress	# 不压缩
        notifempty	# 文件为空则不轮替
        copytruncate	# 对打开的文件进行截断
}

---

慢查询日志分析工具

explain执行计划

explain是MySQL自带的一个sql分析命令 用法：在正常执行的sql前面加上explain即可

EXPLAIN select * from mysql.user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

常见字段：

• id 由一组数字组成，表示执行顺序，id相同则执行顺序由上至下；id不同，则id值越大优先级越高。id为null时表示一个结果集，不需要使用它查询

• select_type

  • SIMPLE 不包含任何子查询或union等查询

  • PRIMARY 包含子查询最外层查询就显示为PRIMARY

  • SUBQUERY 在select或where字句中包含的查询

  • DERIVED from字句中包含的查询

  • UNION 出现在union后的查询语句中

  • UNION RESULT 从UNION中获取结果集

• table 查询的数据表，当从衍生表中查询数据时会显示<derivedx>最后的x表示对应的执行计划id

• partitions 表分区、创建的时候可以指定通过哪个列进行表分区

• type 访问类型

  • ALL 扫描全表

  • index 遍历索引

  • range 索引范围查找

  • index_subquery 在子查询中使用 ref

  • unique_subquery 在子查询中使用 eq_ref

  • ref_or_null 对null进行索引的优化ref

  • fulltext 使用全文索引

  • ref 使用非唯一索引查找数据

  • eq_ref 在join查询中使用PRIMARY KEY或UNIQUE NOT NULL索引关联。

  • const 使用主键或唯一索引，且匹配的结果只有一条记录。

  • system const 连接类型的特例，查询的表为系统表。

• prosible_keys 可能使用的索引（不一定会使用）。查询涉及到的字段上若存在索引，则索引将被列出来。当该列为NULL时要考虑当前SQL是否需要优化了。

• key 显示在查询中实际使用的索引，若没有使用索引，显示为NULL

• key_length 索引长度

• ref 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

• rows 返回估算的结果集数目，不是精确值。

• extra

  • Using index 使用覆盖索引

  • Using where 使用了用where的子句来过滤结果集

  • Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化

  • Using temporay 使用了临时表。

pt-query-digest第三方分析工具

pt-query-digest是[[pt(percona-toolkit)工具集]]中的一个，可以用来分析MySQL的慢日志

安装

# CentOS
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-toolkit

# Ubuntu
apt install percona-toolkit

使用

常用参数：

• --since= 指定要分析的时间，(1h，分析最近1小时)

• --since 指定时间范围的开始，格式 --since '2022-04-28 00:00:00'，要搭配--until

• --until 指定时间范围的结束,格式 --until '2022-04-29 00:00:00'

• --filter 过滤指定关键字，格式--filter 'event->'

pt-query-digest --since '2022-05-05 18:00:00' --until '2022-05-05 20:00:00' db-pro-slow.log