database►Mysql►mysql slow log

mysql slow log

慢查询日志输出格式种类

  • TABLE: 直接输出到数据库
  • FILE: 直接输出到物理磁盘文件

慢查询操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
//修改输出格式为table
mysql> set global log_output='TABLE';
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
mysql> select * from mysql.slow_log;

//修改输出格式为file
mysql> set global log_output='FILE';
mysql> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set (0.00 sec)

//设置慢查询时间(超过时间输出)
mysql> set global long_query_time=10;
mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

//打开关闭慢查询功能
mysql> set global slow_query_log=1;
mysql> set global slow_query_log=0;
mysql> show variables like 'slow_query%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log      | OFF                              |
| slow_query_log_file | /mnt/data/mysql/3306/10-slow.log |
+---------------------+----------------------------------+

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
查看mysqldumpslow的帮助信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[root@DB-Server ~]# mysqldumpslow --help
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
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
说明:
-s,是表示按照何种方式排序:
   c: 访问计数
   l: 锁定时间
   r: 返回记录
   t: 查询时间
   al:平均锁定时间
   ar:平均返回记录数
   at:平均查询时间
-t, 是top n的意思,即为返回前面多少条的数据
-g, 后边可以写一个正则匹配模式，大小写不敏感的


列出返回记录集最多的10个SQL:
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

列出访问次数最多的10个SQL:
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

列出按照时间排序的前10条里面含有左连接的查询语句:
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/mysql06_slow.log