原文：http://blog.csdn.net/langkeziju/article/details/49301993
mysqldumpslow使用说明mysqldumpslow --helpUsage: 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
经常使用几个命令-s ORDER what to sort by (t, at, l, al, r, ar etc), ‘at’ is default-t NUM just show the top n queries-g PATTERN grep: only consider stmts that include this string
-s，是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有c,t,l,r和ac,at,al,ar，分别是按照query次数，查询时间，lock的时间和返回的记录数来排序，前面加了a的平均数-t，是top n的意思，即为返回前面多少条的数据-g，后边可以写一个正则匹配模式，大小写不敏感的例子mysqldumpslow -t 10 -s t -g “left join” host-slow.log使用mysqldumpslow的分析结果不会显示具体完整的sql语句，说明：1：假如真正的sql语句如下：SELECT * FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;mysqldumpslow显示的结果会是：Count: 1 Time=1.91s (1s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N;
2：如果我们再执行一条SELECT * FROM sms_send WHERE service_id=20 GROUP BY content LIMIT 10000, 1000;mysqldumpslow显示的结果会是：Count: 2 Time=2.79s (5s) Lock=0.00s (0s) Rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]SELECT * FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N;
虽然这两条语句条件不一样，1：一个是server_id=10,一个是server_id=202：一个是LIMIT 0, 1000,一个是LIMIT 10000, 1000但是mysqldumpslow分析会认为这是一种类型的语句，会合并显示。
3：假设我们执行SELECT * FROM sms_send WHERE service_id<=10 GROUP BY content LIMIT 0, 1000;执行mysqldumpslow结果是Count: 1 Time=2.91s (2s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]SELECT * FROM sms_send WHERE service_id<=N GROUP BY content LIMIT N, N;可以看出它和上面我们写的sql语句是两种类型
mysqldumpslow的分析结果Count会告诉我们这种类型的语句执行了几次，Time会告诉我们这种类型的语句执行的最大时间，Time=2.79s (5s)中（5s）是指这类型的语句执行总共花费的时间Count: 2 Time=2.79s (5s) Lock=0.00s (0s) Rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]告诉我们执行了2次，最大时间是2.79s,总共花费时间5s,lock时间0s,单次返回的结果数是1条记录，2次总共返回2条记录mmysqldumpslow -s t -t 10 slow.log查询的结果是10条执行时间最慢的sql语句，其中-s t是指此类类型的语句的执行总时长Count: 1 Time=2.91s (2s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]和Count: 2 Time=2.79s (5s) Lock=0.00s (0s) Rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]比较的结果是Count: 2 Time=2.79s (5s) Lock=0.00s (0s) Rows=1.0 (2), vgos_dba[vgos_dba]@[10.130.229.196]排在前面，以为比较的时长是(5s)和(2s)，而不是2.79s和2.91s-s at比较的也是(5s)/count:2和(2s)/Count: 1所以：-s at是Count: 1 Time=2.91s (2s) Lock=0.00s (0s) Rows=1000.0 (1000), vgos_dba[vgos_dba]@[10.130.229.196]排在前面。
Rows=1.0 (2) 是按照以下逻辑展示的（2）是指在Count: 2次数总共返回了2条记录集；row=1.0显示（2）/Count: 2，如果此时Count是3，那么row的计算方式是Rows=2/3,Rows=0.67
主要功能是, 统计不同慢sql的出现次数(Count),执行最长时间(Time),累计总耗费时间(Time),等待锁的时间(Lock),发送给客户端的行总数(Rows),扫描的行总数(Rows)
转载于:https://www.cnblogs.com/liujiacai/p/7602610.html
相关资源：各显卡算力对照表！