MySQL慢查询日志超详细总结

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | OFF                                      |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name       | Value                                    |
+---------------------+------------------------------------------+
| slow_query_log      | ON                                       |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
#使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

slow_query_log =1
slow_query_log_file=/usr/local/mysql/data/localhost-slow.log
mysql> show variables like 'slow_query%';
+---------------------+---------------------+
| Variable_name       | Value               |
+---------------------+---------------------+
| slow_query_log      | ON                  |
| slow_query_log_file | /usr/local/mysql/data/localhost-slow.log |
+---------------------+---------------------+
2 rows in set (0.00 sec)
mysql> #慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql>
mysql> set global long_query_time=4;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql>

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
mysql> 
mysql> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql>

 [root@localhost~]# 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
mysqldumpslow --help