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(其它系统变量也是如此)
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> 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>
如上所示,我修改了变量long_query_time,但是查询变量long_query_time的值还是10,难道没有修改到呢?注意:使用命令 set global long_query_time=4修改后,需要重新连接或新开一个会话才能看到修改值。你用show variables like 'long_query_time'查看是当前会话的变量值,你也可以不用重新连接会话,而是用show global variables like 'long_query_time'; 如下所示:
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> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
系统变量log-queries-not-using-indexes:未使用索引的查询也被记录到慢查询日志中(可选项)。如果调优的话,建议开启这个选项。另外,开启了这个参数,其实使用full index scan的sql也会被记录到慢查询日志。
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF |
+-------------------------------+-------+
1 row in set (0.00 sec)
mysql> set global log_queries_not_using_indexes=1; Query OK, 0 rows affected (0.00 sec)
mysql> show variables like ‘log_queries_not_using_indexes’; ±------------------------------±------+ | Variable_name | Value | ±------------------------------±------+ | log_queries_not_using_indexes | ON | ±------------------------------±------+ 1 row in set (0.00 sec)
mysql> show variables like 'log_slow_admin_statements';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| log_slow_admin_statements | OFF |
+---------------------------+-------+
1 row in set (0.00 sec)
mysql>
另外,如果你想查询有多少条慢查询记录,可以使用系统变量。
mysql> show global status like '%slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 0 |
+---------------+-------+
1 row in set (0.00 sec)
1 [root@localhost~]# mysqldumpslow --help
2 Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
3
4 Parse and summarize the MySQL slow query log. Options are
5
6 --verbose verbose
7 --debug debug
8 --help write this text to standard output
9
10 -v verbose
11 -d debug
12 -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
13 al: average lock time
14 ar: average rows sent
15 at: average query time
16 c: count
17 l: lock time
18 r: rows sent
19 t: query time
20 -r reverse the sort order (largest last instead of first)
21 -t NUM just show the top n queries
22 -a don't abstract all numbers to N and strings to 'S'
23 -n NUM abstract numbers with at least n digits within names
24 -g PATTERN grep: only consider stmts that include this string
25 -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
26 default is '*', i.e. match all
27 -i NAME name of server instance (if using mysql.server startup script)
28 -l don't subtract lock time from total time