Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MYSQL slow query log view method", in daily operation, I believe many people have doubts about MYSQL slow query log view method, Xiaobian consulted all kinds of information, sorted out simple and easy to use operation method, hope to answer "MYSQL slow query log view method" doubts helpful! Next, please follow the small series to learn together!

mysql slow query log is a database tuning of the main basis, but the file is a text file out of the trace, look up time-consuming and laborious, mysql also provides a tool to facilitate the search from the text file inside the tool mysqldumpslow.

Mysqldumpslow is a software that comes with installing the database, under the directory/usr/local/mysql/bin

[root@mysql01 data]# /usr/local/mysql/bin/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 //Sort by average lock time

ar: average rows sent //average rows sent

at: average query time //average query time sort

c: count //number of executions

l: lock time //lock time

r: rows sent //Get rows sorted

t: query time //query time

-r reverse the sort order (largest last instead of first) //sort from smallest to largest

-t NUM just show the top n queries //query result entries followed by numbers

-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 //Contain a specific character or 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

[root@mysql01 data]# /usr/local/mysql/bin/mysqldumpslow -s c -t 10 /data/JOEONE-IPOSDS-01-slow.log

View the top 10 SQL statements;

l [root@mysql01 data]# /usr/local/mysql/bin/mysqldumpslow -s r -t 10 /data/JOEONE-IPOSDS-01-slow.log

View the top 10 sql statements that return the most recordsets

l [root@mysql01 data]# /usr/local/mysql/bin/mysqldumpslow -t 10 -s t -g "left join" /data/JOEONE-IPOSDS-01-slow.log

View statements using left join

Count: 132 Time=4.21s (555s) Lock=0.00s (0s) Rows=0.4 (53), ipos[ipos]@3hosts

Number of executions Average execution time/total execution time Average lock wait time/total lock wait Average number of rows acquired/total number of rows acquired Users

Detailed SQL statements

SELECT cus.zjhfrq,cus.zjrq,cus.headphoto,cus.sr1,cus.sr2,cus.vip,cus.xb,cus.Id,cus.gkdm,cus.gkmc,cus.sj,cus.dh,cus.dz,cus.bz,cus.scdh,cus.sy,cus.lylx,cus.cdbh,cus.jdrq,cus.zd_id,kh.khmc as zdmc, dj.khmc as djzdmc,cus.xfje,cus.dj_zd

FROM ipos_customer cus LEFT JOIN com_base_kehu kh ON kh.Id=cus.zd_id LEFT JOIN com_base_kehu dj ON dj.Id=cus.dj_zd WHERE N=N and ( ((cus.zd_id='S'OR cus.dj_zd='S') and cus.vip=N) or ((cus.zd_id='S'OR cus.dj_zd='S') and cus.vip=N)) and cus.sj like 'S' and N=N and N=N order by cus.gkdm DESC LIMIT N

The initial feeling is that this sentence can be optimized;

Summary: Today, I saw the company's database optimize a statement running 10S into a statement running 0.1S through slow query logs, and I felt very happy; but this is only the first step in the long march. After all, when a database runs to the point where the leader wants DBA to optimize, it means that there are already many SQL statements with performance problems, so the road is still long.

At this point, the study of "MYSQL slow query log view method" is over, I hope to solve everyone's doubts. Theory and practice can better match to help everyone learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.