提供网站技术,安阳网站建设哪里最好,房山建站公司,做视频网站 带宽4、格式化慢查询日志结构化慢查询日志就是把慢查询日志中的重要信息按照便于阅读以及按照特定的排序方式来提取SQL。这种方式有点类似于Oracle中有个tkprof来格式化oracle的trace文件。对于前面的慢查询日志我们使用MySQLdumpslow来提取如下#xff1a;SUSE11b:~ # mysqldumps…4、格式化慢查询日志结构化慢查询日志就是把慢查询日志中的重要信息按照便于阅读以及按照特定的排序方式来提取SQL。这种方式有点类似于Oracle中有个tkprof来格式化oracle的trace文件。对于前面的慢查询日志我们使用MySQLdumpslow来提取如下SUSE11b:~ # mysqldumpslow -s at,al /var/lib/mysql/suse11b-slow.logReading mysql slow query log from /var/lib/mysql/suse11b-slow.logCount: 4 Time16.87s (67s) Lock0.00s (0s) Rows0.0 (0), root[root]localhostinsert into tb_slow select * from tb_slowCount: 1 Time0.20s (0s) Lock0.00s (0s) Rows1.0 (1), root[root]localhostselect count(*) from tb_slowCount: 1 Time1.57s (1s) Lock0.00s (0s) Rows83.0 (83), root[root]localhostselect table_schema,table_name,count(*) from tb_slowgroup by table_schema,table_name order by N,N#以下是按照最大耗用时间排最后只显示2条的方式格式化日志文件suse11b:~ # mysqldumpslow -r -t 2 /var/lib/mysql/suse11b-slow.logReading mysql slow query log from /var/lib/mysql/suse11b-slow.logCount: 1 Time1.57s (1s) Lock0.00s (0s) Rows83.0 (83), root[root]localhostselect table_schema,table_name,count(*) from tb_slowgroup by table_schema,table_name order by N,NCount: 4 Time16.87s (67s) Lock0.00s (0s) Rows0.0 (0), root[root]localhostinsert into tb_slow select * from tb_slow#获取mysqldumpslow的帮助信息suse11b:~ # 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 defaultal: average lock timear: average rows sentat: average query timec: count #query的次数l: lock timer: rows sent #返回的记录数t: query time-r reverse the sort order (largest last instead of first)-t NUM just show the top n queries-a dont 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 dont subtract lock time from total time
