4、格式化慢查詢?nèi)罩?/p>

結(jié)構(gòu)化慢查詢?nèi)罩揪褪前崖樵內(nèi)罩局械闹匾畔凑毡阌陂喿x以及按照特定的排序方式來提取SQL。

這種方式有點(diǎn)類似于Oracle中有個(gè)tkprof來格式化oracle的trace文件。

對(duì)于前面的慢查詢?nèi)罩疚覀兪褂肕ySQLdumpslow來提取如下：

SUSE11b:~ # mysqldumpslow -s at,al /var/lib/mysql/suse11b-slow.log

Reading mysql slow query log from /var/lib/mysql/suse11b-slow.log

Count: 4? Time=16.87s (67s)? Lock=0.00s (0s)? Rows=0.0 (0), root[root]@localhost

insert into tb_slow select * from tb_slow

Count: 1? Time=0.20s (0s)? Lock=0.00s (0s)? Rows=1.0 (1), root[root]@localhost

select count(*) from tb_slow

Count: 1? Time=1.57s (1s)? Lock=0.00s (0s)? Rows=83.0 (83), root[root]@localhost

select table_schema,table_name,count(*) from tb_slow

group by table_schema,table_name order by N,N

#以下是按照最大耗用時(shí)間排最后，只顯示2條的方式格式化日志文件

suse11b:~ # mysqldumpslow -r -t 2 /var/lib/mysql/suse11b-slow.log

Reading mysql slow query log from /var/lib/mysql/suse11b-slow.log

Count: 1? Time=1.57s (1s)? Lock=0.00s (0s)? Rows=83.0 (83), root[root]@localhost

select table_schema,table_name,count(*) from tb_slow

group by table_schema,table_name order by N,N

Count: 4? Time=16.87s (67s)? Lock=0.00s (0s)? Rows=0.0 (0), root[root]@localhost

insert into tb_slow select * from tb_slow

#獲取mysqldumpslow的幫助信息

suse11b:~ # 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? ? ? ? #query的次數(shù)

l: lock time

r: rows sent? ? #返回的記錄數(shù)

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

總結(jié)

以上是生活随笔為你收集整理的suse查看mysql内存使用情况_MySQL 慢查询日志(Slow Query Log)的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。