前提:
如果慢查日志中的信息多了,我们根本没法手动统计急需优化的sql。这个时候就需要慢查日志统计分析工具。
mysql自带一款工具为mysqldumpslow.
对日志进行统计分析
/Applications/MAMP/Library/bin/mysqldumpslow -s c /Applications/MAMP/db/mysql56/bogon-slow.log
Reading mysql slow query log from /Applications/MAMP/db/mysql56/bogon-slow.log
Count: 2 Time=5.25s (10s) Lock=0.00s (0s) Rows=1.0 (2), root[root]@localhost
select count(*) from test.card
Count: 1 Time=4.95s (4s) Lock=0.00s (0s) Rows=1.0 (1), root[root]@localhost
select count(*) from card
以下内容转自(麦歪博客)
[root@DB-Server ~]# 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(返回前面n条数据)-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 '
-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'
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
比如,得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合
|
和more
使用 ,否则有可能出现刷屏的情况。mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more