查看 MySQL 慢日志

使用 MySQL自带命令 mysqldumpslow 查看

    OPTIONS

#查看访问次数最多的 20 个 sql 语句
[root@localhost ~]# mysqldumpslow -s c -t 20 /usr/local/mysql/var/mysql_slow_query.log
#查看返回记录集最多的 20 个 sql
[root@localhost ~]# mysqldumpslow -s r -t 20 /usr/local/mysql/var/mysql_slow_query.log
#按照时间返回前 10 条里面含有左连接的 sql 语句
[root@localhost ~]# mysqldumpslow -t 10 -s t -g "LEFT JOIN" /usr/local/mysql/var/mysql_slow_query.log

mysqlsla 分析 MySQL 慢查询日志

    mysqlsla(现已不在维护)是第三方提供的perl脚本, 不过它功能强悍, 可以分析包括慢查询在内的多种格式的日志.

[root@localhost tmp]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
[root@localhost tmp]# tar zxf mysqlsla-2.03.tar.gz
[root@localhost tmp]# cd mysqlsla-2.03
[root@localhost mysqlsla-2.03]# perl Makefile.PL
[root@localhost mysqlsla-2.03]# make
[root@localhost mysqlsla-2.03]# make install

    安装完成后, mysqlsla 会加入到 /usr/bin 目录.

    mysqlsla 会自动判断日志类型, 缺省会打印出前十条结果, 可以通过类似--top 100的参数来修改, 如果觉得每次输入麻烦, 还可以建立一个配置文件 ~/.mysqlsla, 在文件里写上:

top=100

    这样就不用每次都手动输入参数了.

[root@localhost mysqlsla-2.03]# mysqlsla -lt slow /usr/local/mysql/var/mysql_slow_query.log

    返回结果:

Report for slow logs: /usr/local/mysql/var/mysql_slow_query.log
4 queries total, 2 unique
Sorted by \'t_sum\'
Grand Totals: Time 11 s, Lock 0 s, Rows sent 712.40k, Rows Examined 712.40k

______________________________________________________________________ 001 ___
Count         : 3  (75.00%)
Time          : 8 s total, 2.666667 s avg, 2 s to 4 s max  (72.73%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
Rows sent     : 178.10k avg, 178.10k to 178.10k max  (75.00%)
Rows examined : 178.10k avg, 178.10k to 178.10k max  (75.00%)
Database      : test
Users         :
        root@localhost  : 66.67% (2) of query, 75.00% (3) of all users
        root1@localhost  : 33.33% (1) of query, 25.00% (1) of all users

Query abstract:
SELECT * FROM test_1;

Query sample:
select * from test_1;

______________________________________________________________________ 002 ___
Count         : 1  (25.00%)
Time          : 3 s total, 3 s avg, 3 s to 3 s max  (27.27%)
Lock Time (s) : 0 total, 0 avg, 0 to 0 max  (0.00%)
Rows sent     : 178.10k avg, 178.10k to 178.10k max  (25.00%)
Rows examined : 178.10k avg, 178.10k to 178.10k max  (25.00%)
Database      :
Users         :
        root@localhost  : 100.00% (1) of query, 75.00% (3) of all users

Query abstract:
SELECT * FROM test_2;

Query sample:
select * from test_2;