MySQL 慢查询

1. MySQL 慢查询定位

1.1 MySQL 慢查询日志格式

--MySQL 慢查询日志格式
1）Time 日志的记录时间
2) User@Host 执行SQL的用户和主机
3) Query_time SQL执行的耗时时间
4) Lock_time 锁表的时间
5) Rows_sent SQL返回的执行记录条数
6) Rows_examined SQL语句扫描的记录条数
7) SET timestamp SQL语句执行的时间点
8) select SQL执行语句
1
2
3
4
5
6
7
8
9

1.2 MySQL 慢查询日志配置

slow_query_log
slow_query_log_file
long_query_time
log_queries_not_using_indexes

-- 显示慢查询的配置变量
mysql> show global variables like '%slow%';
+---------------------------+-------------------------------+
| Variable_name             | Value                         |
+---------------------------+-------------------------------+
| log_slow_admin_statements | OFF                           |
| log_slow_slave_statements | OFF                           |
| slow_launch_time          | 2                             |
| slow_query_log            | OFF                           |
| slow_query_log_file       | /data/mysql/data/11g-slow.log |
+---------------------------+-------------------------------+
5 rows in set (0.11 sec)
1
2
3
4
5
6
7
8
9
10
11
12

1.3 打开慢查询日志开关 ON/OFF

set global slow_query_log = on;
set global slow_query_log = off;



1)   慢查询日志文件的路径和文件名
mysql> show global variables like 'slow_query_log_file';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log_file | /data/mysql/data/11g-slow.log |
+---------------------+-------------------------------+


2)  慢查询SQL执行阈值 SQL执行时间超过0.2S算慢查询、会被记录慢查询日志
-- long_query_time 0.2
set global long_query_time = 0.2;
set global long_query_time = 1; --1秒
set global long_query_time = 2; --2秒


3)   记录没有使用索引的SQL
 log_queries_not_using_indexes ON|OFF
 mysql> show global variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
mysql> set global log_queries_not_using_indexes=on;
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

2. MySQL 慢查询日志工具mysqldumpslow

mysqldumpslow原理: 解析慢查询日志文件.使用Perl脚本语言写的。
MySQL 慢查询日志工具：
mysqldumpslow
mysqlsla
pt-query-digest

[root@11g ~]# 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

--例：
[root@11g data]# mysqldumpslow -s at -t 5 11g-slow.log 

Reading mysql slow query log from 11g-slow.log
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  # Time: N-N-07T02:N:N.408513Z
  # User@Host: root[root] @ localhost []  Id:     N
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select sleep(N)
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