分析统计MySQL general日志 找出查询次数最多的SQL

当我们需要优化MySQL查询时，第一想到的是开启慢日志，慢日志可以看到执行消耗超过一定时间的SQL语句和未使用索引的SQL。但如果我们想分析所有SQL查询的分布，即哪类SQL查询次数最多的时候，我们可以开启general log来统计。

开启general log

mysql>showvariableslike'%general%';

+——————+————————————-+
| Variable_name | Value |
+——————+————————————-+
| general_log | OFF |
| general_log_file | /usr/local/mysql/data/localhost.log |
+——————+————————————-+

mysql>setglobalgeneral_log="ON";

analysis-general-log.py脚本

#!/usr/bin/python#sortandcountmysqlgenerallog#Author:Jason#Url:www.centos.bz#Email:admin#centos.bz#Created:UTC2015-02-1517:51:53importreimportsysimportosiflen(sys.argv)==2:logPath=sys.argv[1]ifnotos.path.exists(logPath):print("file"+logPath+"doesnotexists.")sys.exit(1)else:print("Usage:"+sys.argv[0]+"logPath")sys.exit(1)logFo=open(logPath)match=0forlineinlogFo:line=re.sub(r"\n","",line)ifmatch==0:#matchlinebeginwithnumberslineMatch=re.match(r"\s+[0-9]+\s+.*",line,flags=re.I)iflineMatch:lineTmp=lineMatch.group(0)match=match+1continueelifmatch==1:#matchlinebeginwithnumberslineMatch=re.match(r"\s+[0-9]+\s+.*",line,flags=re.I)iflineMatch:#matchonlyquerylineMatchQuery=re.match(r".*Query\s+(.*)",lineTmp,flags=re.I)iflineMatchQuery:lineTmp=lineMatchQuery.group(1)#removeextraspacelineTmp=re.sub(r"\s+","",lineTmp)#replacevalues(value)tovalues(x)lineTmp=re.sub(r"values\s*\(.*?\)","values(x)",lineTmp,flags=re.I)#replacefiled='value'tofiled='x'lineTmp=re.sub(r"(=|>|<|>=|<=)\s*('|\").*?\2","\\1'x'",lineTmp)#replacefiled=valuetofiled=xlineTmp=re.sub(r"(=|>|<|>=|<=)\s*[0-9]+","\\1x",lineTmp)#replacelike'value'tolike'x'lineTmp=re.sub(r"like\s+('|\").*?\1","like'x'",lineTmp,flags=re.I)#replacein(value)toin(x)lineTmp=re.sub(r"in\s+\(.*?\)","in(x)",lineTmp,flags=re.I)#replacelimitx,ytolimitlineTmp=re.sub(r"limit.*","limit",lineTmp,flags=re.I)print(lineTmp)match=1lineTmp=lineMatch.group(0)else:lineTmp+=linematch=1logFo.close()

使用方法：

analysis-general-log.pygeneral.log|sort|uniq-c|sort-nr

该python脚本使用与python2.7以上环境