13-日志的读取
和错误日志、查询日志一样，慢查询日志记录的格式也是纯文本，可以被直接读取。下例中演示了慢查询日志的设置和读取过程。
（1）首先查询一下long_query_time的值。
mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)
（2）为了方便测试，将修改慢查询时间为2秒。
mysql> set long_query_time=2;
Query OK, 0 rows affected (0.02 sec)
（3）依次执行下面两个查询语句。
第一个查询因为查询时间低于2秒而不会出现在慢查询日志中：
mysql> select count(1) from emp;
+----------+
| count(1) |
+----------+
| 131075 |
+----------+
1 row in set (0.00 sec)
第二个查询因为查询时间大于2秒而应该出现在慢查询日志中：
mysql> select count(1) from emp t1,dept t2 where t1.id=t2.id;
+----------+
| count(1) |
+----------+
| 33555200 |
+----------+
1 row in set (11.31 sec)
（4）查看慢查询日志。
[root@localhost mysql]# more localhost-slow.log
/usr/sbin/mysqld, Version: 5.0.41-community-log (MySQL Community Edition (GPL)). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
Time: 070810 23:43:55
User@Host: root[root] @ localhost []
Query_time: 297 Lock_time: 0 Rows_sent: 0 Rows_examined: 26214
use test;
Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 512
select count(1) from emp t1,dept t2 where t1.id=t2.id;
从上面日志中，可以发现查询时间超过2秒的SQL，而小于2秒的则没有出现在此日志中。
（5）设置微秒级慢查询。
mysql> set global long_query_time=0.01;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name | Value|
+-----------------+----------+
| long_query_time | 0.010000 |
+-----------------+----------+
1 row in set (0.01 sec)
查看日志输出方式为文件和表同时写：
mysql> show variables like '%output%';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| log_output | FILE,TABLE |
+---------------+------------+
执行慢查询SQL如下：
mysql> select count(1) from t1 a,t1 b where a.id=b.id;
+----------+
| count(1) |
+----------+
| 13705 |
+----------+
1 row in set (0.08 sec)
查看日志文件记录，日期的确精确到微秒：
Query_time: 0.077700 Lock_time: 0.000347 Rows_sent: 1 Rows_examined: 27408
use test;
SET timestamp=1297504958;
select count(1) from t1 a,t1 b where a.id=b.id;
查询系统表记录，如前所述，时间无法精确到微秒：
mysql> select time_format(query_time,'%f') time,sql_text from slow_log where sql_text='select count(1) from t1 a,t1 b where a.id=b.id';
+--------+------------------------------------------------+
| time | sql_text |
+--------+------------------------------------------------+
| 000000 | select count(1) from t1 a,t1 b where a.id=b.id |
+--------+------------------------------------------------+
1 row in set (0.00 sec)
如果慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。下例中对日志文件bj37-slow.log进行了分类汇总，只显示汇总后摘要结果：
[zzx@bj37 data]$ mysqldumpslow bj37-slow.log
[root@localhost mysql]# mysqldumpslow localhost-slow.log
Reading mysql slow query log from localhost-slow.log
Count: 1 Time=297.00s (297s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select count(N) from emp t1,emp t2 where t1.id<>t2.id
Count: 2 Time=11.00s (22s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
select count(N) from emp t1,dept t2 where t1.id=t2.id
Count: 1 Time=9.00s (9s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@localhost
select count(N) from emp t1,emp t2 where t1.id=t2.id
Count: 2 Time=3.00s (6s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
select count(N) from emp t1,dept t2 where t1.id=t2.id and t1.id=N
对于SQL文本完全一致，只是变量不同的语句，mysqldumpslow将会自动视为同一个语句进行统计，变量值用N来代替。这个统计结果将大大增加用户阅读慢查询日志的效率，并迅速定位系统的SQL瓶颈。
注意：慢查询日志对于我们发现应用中有性能问题的SQL很有帮助，建议正常情况下，打开此日志并经常查看分析。