MySQL慢查询跟踪

参考：https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html
MySQL 版本：MySQL-5.7.1

慢查询用到的几个参数，默认值如下：
+—————————————+——————————————+
| Variable_name | Value |
+—————————————+——————————————+
| slow_query_log | OFF |
| log_output | FILE |
| long_query_time | 10.000000 |
| slow_query_log_file | /usr/local/mysql/data/server223-slow.log |
| log_slow_admin_statements | OFF |
| log_queries_not_using_indexes | OFF |
| log_throttle_queries_not_using_indexes| 0 |
| log_slow_slave_statements | OFF |
| min_examined_row_limit | 0 |
+—————————————+——————————————+
slow_query_log ：是否启用慢查询跟踪记录
log_output ：慢查询记录格式，可保持到文件（FILE），也可保存到表（TABLE ）,表为mysql.slow_log ，也可同时保留都文件和表中（log_output=’FILE,TABLE’）。
long_query_time ：慢查询时间定义，默认10秒。执行语句超过10秒则表示为慢查询。
slow_query_log_file ：定义慢查询记录保存的文件位置，log_output 为 FILE 时则记录。默认名称为hostname-slow.log 。
log_slow_admin_statements 超过 long_query_time 时间的管理类语句是否记录，如 ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, REPAIR TABLE。
log_queries_not_using_indexes ：没有使用索引查找的查询是否记录。
log_throttle_queries_not_using_indexes ：如果log_queries_not_using_indexes 开启，每分钟查询写到 slow log 的数量，0表示无限制。若不限制，慢查询太多会使文件不断增大。
log_slow_slave_statements ：slave 执行的慢查询语句是否记录下来。
min_examined_row_limit ：查询结果集行数小于该数值，则不会记录下来；0 为无限制。

启用跟踪，可在配置文件中设置参数（不建议启用）：
shell> vim /etc/my.cnf
slow_query_log =1
slow_query_log_file=/usr/local/mysql/data/server223-slow.log

更改变量，重启 mysqld 服务则会失效，long_query_time 在新的连接生效。
log_queries_not_using_indexes 这里不启用，否则执行 “select * from mysql.slow_log;” 会记录下来。
set global slow_query_log=1;
set global log_output=’Table,File’;
set global long_query_time=5;
set global log_slow_admin_statements=1;

查看变量
mysql> show variables where Variable_name in (‘slow_query_log’, ‘log_output’, ‘long_query_time’, ‘log_slow_admin_statements’);
+—————————+————+
| Variable_name | Value |
+—————————+————+
| log_output | FILE,TABLE |
| log_slow_admin_statements | ON |
| long_query_time | 5.000000 |
| slow_query_log | ON |
+—————————+————+

测试：执行6秒钟的查询
mysql> select sleep(5);

查看慢查询结果（表）：
mysql> select * from mysql.slow_log;
+—————————-+—————————+—————–+—————–+———–+—————+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined |
+—————————-+—————————+—————–+—————–+———–+—————+
| 2017-02-26 01:01:59.406321 | root[root] @ localhost [] | 00:00:05.000672 | 00:00:00.000000 | 1 | 0 |
+—————————-+—————————+—————–+—————–+———–+—————+

+—-+—————-+———–+———–+————————————————————–+———–+
| db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+—-+—————-+———–+———–+————————————————————–+———–+
| | 0 | 0 | 1 | select sleep(5) | 4 |
+—-+—————-+———–+———–+————————————————————–+———–+

查看慢查询结果（文件）：
shell> tail -n 30 /usr/local/mysql/data/server223-slow.log
# Time: 2017-02-26T09:01:59.406321Z
# User@Host: root[root] @ localhost [] Id: 4
# Query_time: 5.000672 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1488099719;
select sleep(5);

或使用工具 mysqldumpslow 查询：
Usage: mysqldumpslow [ OPTS… ] [ LOGS… ]
Parse and summarize the MySQL slow query log. Options are
–verbose verbose
–debug debug
–help write this text to standard output

-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), ‘at’ is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don’t abstract all numbers to N and strings to ‘S’
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is ‘*’, i.e. match all

shell> mysqldumpslow -t 2 /usr/local/mysql/data/server223-slow.log
如果不是调优的话不建议开启，因为实时分析语句写入文件消耗性能。如果跟踪建议写到文件中。