mysql版本5.7.21

redhat 6.8

启用慢查询

mysql>setglobalslow_query_log=on;QueryOK,0rowsaffected(0.01sec)

启用未使用索引慢查询

mysql>setgloballog_queries_not_using_indexes=on;QueryOK,0rowsaffected(0.00sec)

创建测试表

mysql>usezxydb;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>insertintot_slowselect*fromt_slow;QueryOK,8388608rowsaffected(35.04sec)Records:8388608Duplicates:0Warnings:0

执行sql查询

mysql>selectcount(*)fromzxydb.t_slow;+----------+|count(*)|+----------+|16777216|+----------+1rowinset(6.48sec)

查询pt-query-digest选项

[root@three57percona-toolkit-3.1.0]#pt-query-digest--helppt-query-digestanalyzesMySQLqueriesfromslow,general,andbinarylogfiles.ItcanalsoanalyzequeriesfromC<SHOWPROCESSLIST>andMySQLprotocoldatafromtcpdump.Bydefault,queriesaregroupedbyfingerprintandreportedindescendingorderofquerytime(i.e.theslowestqueriesfirst).IfnoC<FILES>aregiven,thetoolreadsC<STDIN>.TheoptionalC<DSN>isusedforcertainoptionslikeL<"--since">andL<"--until">.Formoredetails,pleaseusethe--helpoption,ortry'perldoc/usr/local/bin/pt-query-digest'forcompletedocumentation.Usage:pt-query-digest[OPTIONS][FILES][DSN]Options:--ask-passPromptforapasswordwhenconnectingtoMySQL--attribute-aliases=aListofattribute|alias,etc(defaultdb|Schema)--attribute-value-limit=iAsanitylimitforattributevalues(default0)--charset=s-ADefaultcharacterset--config=AReadthiscomma-separatedlistofconfigfiles;ifspecified,thismustbethefirstoptiononthecommandline

获取完整pt-query-digest全部语义信息

[root@three57percona-toolkit-3.1.0]#manpt-query-digest>/pt-query.log

[root@three57percona-toolkit-3.1.0]#pt-query-digest/var/lib/mysql/three57-slow.log用户时间分布及进程内程内存#100msusertime,10mssystemtime,21.88Mrss,173.04Mvsz当前系统时间#Currentdate:WedNov1315:29:442019主机名称#Hostname:three57慢查询日志#Files:/var/lib/mysql/three57-slow.logQPS及并发#Overall:1total,1unique,0QPS,0xconcurrency______________________#Timerange:alleventsoccurredat2019-11-13T07:28:59属性：全部，最小，最大，平均，95%，平均利差，中差#Attributetotalminmaxavg95%stddevmedian#=============================================================SQL执行时间#Exectime7s7s7s7s7s07s锁定时间#Locktime127us127us127us127us127us0127usSQL产生结果个数#Rowssent1111101SQL扫描表记录#Rowsexamine16.00M16.00M16.00M16.00M16.00M016.00M查询大小#Querysize3333333333033符合慢查询SQL的概述，极重要，排版一个字，棒#ProfileRANK为排名编号queryid为具体SQLresponsetime为sql响应时间calls为SQL执行次数r/call为每次SQL执行的响应时间，具体的SQL语句#RankQueryIDResponsetimeCallsR/CallV/M#===================================================================#10xAC104A376C0A55B1F56FDA6E706E555F6.7433100.0%16.74330.00SELECTzxydb.t_slow具体慢查询SQL语句#Query1:0QPS,0xconcurrency,ID0xAC104A376C0A55B1F56FDA6E706E555Fatbyte0#Thisitemisincludedinthereportbecauseitmatches--limit.#Scores:V/M=0.00#Timerange:alleventsoccurredat2019-11-13T07:28:59#Attributepcttotalminmaxavg95%stddevmedian#================================================================#Count1001SQL执行时间为7s#Exectime1007s7s7s7s7s07sSQL锁定时间为127us,此值如极高，需要针对性分析#Locktime100127us127us127us127us127us0127us#Rowssent1001111101全表扫描相关#Rowsexamine10016.00M16.00M16.00M16.00M16.00M016.00M#Querysize1003333333333033#String:产生SQL的主机及用户#Hostslocalhost#UsersrootSQL不同时间范围分布，极好极好可见如上SQL执行时间在1S左右#Query_timedistribution#1us#10us#100us#1ms#10ms#100ms#1s#################################################################10s+#Tables#SHOWTABLESTATUSFROM`zxydb`LIKE't_slow'\G#SHOWCREATETABLE`zxydb`.`t_slow`\G#EXPLAIN/*!50100PARTITIONS*/selectcount(*)fromzxydb.t_slow\G[root@three57percona-toolkit-3.1.0]#