慢查询日志可以帮助DBA定位存在问题的SQL语句，从而进行SQL语句层面的优化。MySQL通过设置一个阈值，将运行时间超出这个值的所有SQL语句都记录到慢查询日志中。运维可以通过filebeat等工具将日志抽取到Elastic Search中并通过Kibana查看(ELK或EFK)。这需要开发人员或DBA每天或每隔一段时间进行检查并优化。
启用慢查询日志
MySQL中可以通过参数 long_query_time 进行设置，它的默认值是10，代表10秒。MySQL中默认是不启用慢查询日志的，我们需要通过设置参数 slow_query_log (log_slow_queries兼容性保留) 启用它，slow_query_log_file 慢查询日志文件存放的位置。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set
mysql> show variables like 'slow_query_%
';
+---------------------+----------------+
| Variable_name | Value |
+---------------------+----------------+
| slow_query_log | OFF |
| slow_query_log_file | mysql-slow.log |
+---------------------+----------------+
2 rows in set
mysql> set @@global.log_slow_queries=ON;
Query OK, 0 rows affected
注意：设置阈值long_query_time后，MySQL会记录运行时间超过该值的所有SQL语句，运行时间恰好等于它的情况并不会记录。
记录无索引查询
慢查询日志另外一个有关的参数是 log_queries_not_using_indexes，如果运行的SQL语句没有使用索引，MySQL同样会将这条SQL语句记录到慢查询日志中。它默认也关闭的，需要手动启用。
1
2
3
4
5
6
7
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set
在生产环境下，如果没有使用到索引，就会有大量的这类的SQL语句被频繁记录到慢查询日志中，导致慢查询日志文件迅速、不断增大。所以MySQL5.6.5版本开始新增了另外一个参数 log_throttle_queries_not_using_indexes,用来表示每分钟记录到慢查询日志中SQL未使用索引的次数，默认值为0，表示未做限制。通过设置它就可以减少记录这类慢查询日志，从而减轻服务器压力。
随着慢查询日志的增加，如果直接在服务器上查看就不那么直观了。MySQL提供了 mysqldumpslow 命令，如果需要查询执行时间最长的10条SQL语句，可以通过以下命令：
1
2
3
4
mysql> mysqldumpslow -s al -n 10 /var/lib/mysql/mysql-slow.log
Reading mysql slow query log from /var/lib/mysql/mysql-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows_sent=0.0 (0), Rows_examined=0.0 (0), 0users@0hosts
...
记录到表
从MySQL5.1开始可以将慢查询日志记录到mysql.slow_log表中，可以更方便的查询。
可以通过参数 log_output 进行指定输出格式，默认值为FILE，若设置为 TABLE 就会记录到 mysql.slow_log 表下了。
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | FILE |
+---------------+-------+
1 row in set
mysql> set global log_output="table";
Query OK, 0 rows affected
mysql> select * from mysql.slow_log;
Empty set
参数 log_output 是全局的，并且是动态的，可以直接在线进行修改。通过show create table mysql.slow_log 可以看到slow_log表是基于CSV存储引擎的，数据量较大情况下查询效率并不太好，我们可以将slow_log表引擎修改为MyISAM，并可以为start_time列增加索引以提高查询效率。但是如果慢查询功能已经启用的情况下是不能进行修改的，不过可以暂时停用下它：
1
2
3
4
5
6
7
8
9
10
11
12
mysql> alter table mysql.slow_log engine=MyISAM;
1580 - You cannot 'ALTER' a log table if logging is enabled
mysql> set global slow_query_log=off;
Query OK, 0 rows affected
mysql> alter table mysql.slow_log engine=MyISAM;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> set global slow_query_log=on;
Query OK, 0 rows affected