MySQL之慢日志（慢查询日志，Slow Query Log）
Tags： long_query_timeMySQLSlow Query Log图文结合慢日志慢查询日志日志
什么是慢日志（慢查询日志，Slow Query Log）？
MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的 SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10s以上的语句。
慢查询日志的主要作用是，帮助我们发现那些执行时间特别长的SQL查询，并且有针对性地进行优化，从而提高系统的整体效率。当我们的数据库服务器发生阻塞、运行变慢的时候，检查一下慢查询日志，找到那些慢查询，对解决问题很有帮助。比如一条sq|执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合explain进行全面分析。
默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志或多或少会带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
- 5.6官方说明：https://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
- 5.7官方说明：https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
什么情况下产生慢日志？
看图说话，有很多开关影响着慢日志的生成，相关的参数后面会挨个说明。从上图可以看出慢日志输出的内容有两个，第一执行时间过长（大于设置的long_query_time阈值）；第二未使用索引，或者未使用最优的索引。
这两种日志默认情况下都没有打开，特别是未使用索引的日志，因为这一类的日志可能会有很多，所以还有个特别的开关log_throttle_queries_not_using_indexes用于限制每分钟输出未使用索引的日志数量。
关键代码如下：
Slow log 调用栈（MySQL 5.6.34 ）：
慢日志相关参数
以上应该是最完整的和慢日志相关的所有参数，大多数参数都有前置条件，所以在使用的时候可以参照上面的流程图。
5.6官方文档：
- https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
- https://dev.mysql.com/doc/refman/5.6/en/server-options.html
如何开启慢查询日志
开启slow_query_log
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> show variables like '%slow_query_log%'; +-----------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------+--------------------------------+ | slow_query_log | OFF | | slow_query_log_always_write_time | 10.000000 | | slow_query_log_file | /var/lib/mysql/KAiTO-slow.log | | slow_query_log_use_global_control | | +-----------------------------------+--------------------------------+ 4 rows in set (0.00 sec) # 开启慢查询 mysql > set global slow_query_log='ON'; Query OK, 0 rows affected (0.12 sec) |
然后我们再来查看下慢查询日志是否开启，以及慢查询日志文件的位置：
1 2 3 4 5 6 7 8 9 10 | mysql> show variables like '%slow_query_log%'; +-----------------------------------+--------------------------------+ | Variable_name | Value | +-----------------------------------+--------------------------------+ | slow_query_log | ON | | slow_query_log_always_write_time | 10.000000 | | slow_query_log_file | /var/lib/mysql/KAiTO-slow.log | | slow_query_log_use_global_control | | +-----------------------------------+--------------------------------+ 4 rows in set (0.00 sec) |
你能看到这时慢查询分析已经开启，同时文件保存在 /var/lib/mysql/KAiTO-slow.log 文件中。
修改long_query_time阈值
接下来我们来看下慢查询的时间阈值设置，使用如下命令：
1 2 3 4 5 6 7 | mysql> show variables like '%long_query_time%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) |
意思就是超过10秒的SQL语句就会被记录慢查询日志中，那要如何修改这个阈值呢？
1 2 | mysql> set global long_query_time = 1; mysql> show global variables like '%long-query_time%'; |
或修改 my.cnf 文件，[mysqld]下增加或修改参数long_query_time、slow_query_log和slow_query_log_file后，然后重启MySQL服务器。
1 2 3 4 5 | [mysqld] slow_query_log=ON #开启慢查询日志的开关 slow_query_log_file=/var/lib/mysql/my-slow.log #慢查询日志的目录和文件名信息 long_query_time=3 #设置慢查询的阈值为3秒，超出此设定值的SQL即被记录到慢查询日志 log_output=FILE # 一般有两种形式，一种是输出到文件FILE中，一种是写入数据表格table中，会保存到mysql库的slow_log表中 |
如果不指定存储路径，慢查询日志将默认存储到 MySQL 数据库的数据文件夹下。如果不指定文件名，默认文件名为hostname-slow.log。
补充
min_examined_row_limit
除了上述变量，控制慢查询日志的还有一个系统变量: min_examined_row_limit。
这个变量的意思是，查询扫描过的最少记录数。这个变量和查询执行时间，共同组成了判别一个查询是否是慢查询的条件。如果查询扫描过的记录数大于等于这个变量的值，并且查询执行时间超过long_query_time的值，那么，这个查询就被记录到慢查询日志中; 反之，则不被记录到慢查询日志中。
1 2 3 4 5 6 7 | mysql> show variables like 'min%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | min_examined_row_limit | 0 | +------------------------+-------+ 1 row in set (0.01 sec) |
你也可以根据需要，通过修改 my.cnf 文件，来修改min_examined_row_limit的值。
除了记录普通的慢查询之外，MySQL 还提供了两个参数来让我们记录未使用索引的查询，它们分别是：log-queries-not-using-indexes 和 log_throttle_queries_not_using_indexes
log-queries-not-using-indexes
系统变量log-queries-not-using-indexes作用是未使用索引的查询也被记录到慢查询日志中。
log_throttle_queries_not_using_indexes
可通过设置 log_throttle_queries_not_using_indexes 来限制每分钟写入慢日志中的不走索引的SQL语句个数，该参数默认为 0，表示不开启，也就是说不对写入SQL语句条数进行控制。
在生产环境下，如果没有使用索引，那么此类 SQL 语句会频繁地被记录到 slow log，从而导致 slow log 文件大小不断增加，我们可以通过调整此参数进行配置。
log_slow_extra
如果启用 log_slow_extra 系统变量（从 MySQL 8.0.14 开始提供），服务器会在日志写入几个额外字段。若要记录bytes_received 与 bytes_sent这两个字段则需要开启
percona slow log
GreatSQL是源于Percona Server的分支版本，除了Percona Server已有的稳定可靠、高效、管理更方便等优势外，特别是进一步提升了MGR(MySQL Group Replication)的性能及可靠性，以及众多bug修复。这就是为什么在使用GreatSQL查看慢查询日志时，会有Query_time、Lock_time等信息，这些都是我们GreatSQL源于Percona Server的原因，使查询内容更加丰富，更多的数据可以使得我们更好的排查错误。
关闭慢查询日志
MySQL服务器停止慢查询日志功能的方法：
- 方式1
1 2 | [mysqld] slow_query_log=OFF |
- 方式2
1 | SET GLOBAL slow_query_log=off; |
慢日志输出内容
第一行：标记日志产生的时间，准确说是 SQL 执行完成的时间点，改行记录每一秒只打印一条。
第二行：客户端的账户信息，两个用户名（第一个是授权账户，第二个为登录账户），客户端 IP 地址，还有mysqld的线程 ID。
第三行：查询执行的信息，包括查询时长，锁持有时长，返回客户端的行数，扫描行数。通常我需要优化的就是最后一个内容，尽量减少 SQL 语句扫描的数据行数。
第四行：通过代码看，貌似和第一行的时间没有区别。
第五话：最后就是产生慢查询的 SQL 语句。
1 | --log-short-format=true： |
如果mysqld启动时指定了--log-short-format参数，则不会输出第一、第二行。
1 2 | log-queries-not-using-indexes=on log_throttle_queries_not_using_indexes > 0 : |
如果启用了以上两个参数，每分钟超过log_throttle_queries_not_using_indexes配置的未使用索引的慢日志将会被抑制，被抑制的信息会被汇总，每分钟输出一次。
格式如下：
查看慢查询数目
查询当前系统中有多少条慢查询记录
1 | SHOW GLOBAL STATUS LIKE '%Slow_queries%'; |
慢日志分析工具
在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow ，或者是可以使用另一个工具pt-query-digest。它可以从logs、processlist、和 tcpdump 来分析 MySQL 的状况，logs包括 slow log、general log、binlog。也可以把分析结果输出到文件中，或则把文件写到表中。分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。
