利用mysql general log日志找出查询次数最多的SQL句子

我们知道不管是apache,iis及mysql都可以通过日志能判断程序性能与错误定位了,今天我们来介绍利用mysql general log日志找出查询次数最多的SQL句子.

查询最多的sql语句,开启general log:

1. mysql>showvariableslike'%general%';
2. +------------------+-------------------------------------+
3. |Variable_name|Value|
4. +------------------+-------------------------------------+
5. |general_log|OFF|
6. |general_log_file|/usr/local/mysql/data/localhost.log|
7. +------------------+-------------------------------------+
8. mysql>setglobalgeneral_log="ON";

analysis-general-log.py脚本:

1. #!/usr/bin/python
3. #sortandcountmysqlgenerallog
4. #Author:Jason
5. #Created:UTC2015-02-1517:51:53
7. importre
8. importsys
9. importos
11. iflen(sys.argv)==2:
12. logPath=sys.argv[1]
13. ifnotos.path.exists(logPath):
14. print("file"+logPath+"doesnotexists.")
15. sys.exit(1)
16. else:
17. print("Usage:"+sys.argv[0]+"logPath")
18. sys.exit(1)
20. logFo=open(logPath)
21. match=0
23. forlineinlogFo:
24. line=re.sub(r"＼n","",line)
25. ifmatch==0:
26. #matchlinebeginwithnumbers
27. lineMatch=re.match(r"＼s+[0-9]+＼s+.*",line,flags=re.I)
28. iflineMatch:
29. lineTmp=lineMatch.group(0)
30. match=match+1
31. continue
33. elifmatch==1:
34. #matchlinebeginwithnumbers
35. lineMatch=re.match(r"＼s+[0-9]+＼s+.*",line,flags=re.I)
36. iflineMatch:--phpfensi.com
37. #matchonlyquery
38. lineMatchQuery=re.match(r".*Query＼s+(.*)",lineTmp,flags=re.I)
39. iflineMatchQuery:
40. lineTmp=lineMatchQuery.group(1)
41. #removeextraspace
42. lineTmp=re.sub(r"＼s+","",lineTmp)
43. #replacevalues(value)tovalues(x)
44. lineTmp=re.sub(r"values＼s*＼(.*?＼)","values(x)",lineTmp,flags=re.I)
45. #replacefiled='value'tofiled='x'
46. lineTmp=re.sub(r"(=|>|<|>=|<=)＼s*('|＼").*?＼2","＼＼1'x'",lineTmp)
47. #replacefiled=valuetofiled=x
48. lineTmp=re.sub(r"(=|>|<|>=|<=)＼s*[0-9]+","＼＼1x",lineTmp)
49. #replacelike'value'tolike'x'
50. lineTmp=re.sub(r"like＼s+('|＼").*?＼1","like'x'",lineTmp,flags=re.I)
51. #replacein(value)toin(x)
52. lineTmp=re.sub(r"in＼s+＼(.*?＼)","in(x)",lineTmp,flags=re.I)
53. #replacelimitx,ytolimit
54. lineTmp=re.sub(r"limit.*","limit",lineTmp,flags=re.I)
56. print(lineTmp)
58. match=1
59. lineTmp=lineMatch.group(0)
60. else:
61. lineTmp+=line
62. match=1
64. logFo.close()

使用方法:

1. analysis-general-log.pygeneral.log|sort|uniq-c|sort-nr
2. 1032SELECT*FROMwp_commentsWHERE(comment_approved='x'ORcomment_approved='x')ANDcomment_post_ID=xORDERBYcomment_date_gmtDESC
3. 653SELECTpost_id,meta_key,meta_valueFROMwp_postmetaWHEREpost_idin(x)ORDERBYmeta_idASC
4. 527SELECTFOUND_ROWS()
5. 438SELECTt.*,tt.*FROMwp_termsAStINNERJOINwp_term_taxonomyASttONt.term_id=tt.term_idWHEREtt.taxonomy='x'ANDt.term_id=xlimit
6. 341SELECToption_valueFROMwp_optionsWHEREoption_name='x'limit
7. 329SELECTt.*,tt.*,tr.object_idFROMwp_termsAStINNERJOINwp_term_taxonomyASttONtt.term_id=t.term_idINNERJOINwp_term_relationshipsAStrONtr.term_taxonomy_id=tt.term_taxonomy_idWHEREtt.taxonomyin(x)ANDtr.object_idin(x)ORDERBYt.nameASC
8. 311SELECTwp_posts.*FROMwp_postsWHERE1=xANDwp_posts.IDin(x)ANDwp_posts.post_type='x'AND((wp_posts.post_status='x'))ORDERBYwp_posts.post_dateDESC
9. 219SELECTwp_posts.*FROMwp_postsWHEREIDin(x)
10. 218SELECTtr.object_idFROMwp_term_relationshipsAStrINNERJOINwp_term_taxonomyASttONtr.term_taxonomy_id=tt.term_taxonomy_idWHEREtt.taxonomyin(x)ANDtt.term_idin(x)ORDERBYtr.object_idASC
11. 217SELECTwp_posts.*FROMwp_postsWHERE1=xANDwp_posts.IDin(x)ANDwp_posts.post_type='x'AND((wp_posts.post_status='x'))ORDERBYwp_posts.menu_orderASC
12. 202SELECTSQL_CALC_FOUND_ROWSwp_posts.IDFROMwp_postsWHERE1=xANDwp_posts.post_type='x'AND(wp_posts.post_status='x')ORDERBYwp_posts.post_dateDESClimit
13. 118SETNAMESutf8
14. 115SETSESSIONsql_mode='x'
15. 115SELECT@@SESSION.sql_mode
16. 112SELECToption_name,option_valueFROMwp_optionsWHEREautoload='x'
17. 111SELECTuser_id,meta_key,meta_valueFROMwp_usermetaWHEREuser_idin(x)ORDERBYumeta_idASC
18. 108SELECTYEAR(min(post_date_gmt))ASfirstdate,YEAR(max(post_date_gmt))ASlastdateFROMwp_postsWHEREpost_status='x'
19. 108SELECTt.*,tt.*FROMwp_termsAStINNERJOINwp_term_taxonomyASttONt.term_id=tt.term_idWHEREtt.taxonomyin(x)ANDtt.count>xORDERBYtt.countDESClimit
20. 107SELECTt.*,tt.*FROMwp_termsAStINNERJOINwp_term_taxonomyASttONt.term_id=tt.term_idWHEREtt.taxonomyin(x)ANDt.term_idin(x)ORDERBYt.nameASC
21. 107SELECT*FROMwp_usersWHEREID='x'
22. 106SELECTSQL_CALC_FOUND_ROWSwp_posts.IDFROMwp_postsWHERE1=xANDwp_posts.post_type='x'AND(wp_posts.post_status='x')ANDpost_date>'x'ORDERBYwp_posts.post_dateDESClimit
23. 106SELECTSQL_CALC_FOUND_ROWSwp_posts.IDFROMwp_postsWHERE1=xANDwp_posts.post_type='x'AND(wp_posts.post_status='x')ANDpost_date>'x'ORDERBYRAND()DESClimit
24. 105SELECTSQL_CALC_FOUND_ROWSwp_posts.IDFROMwp_postsWHERE1=xANDwp_posts.post_type='x'AND(wp_posts.post_status='x')ANDpost_date>'x'ORDERBYwp_posts.comment_countDESClimit

mysql general log日志清除技巧

mysql general log日志不能直接删除,间接方法.

1. USEmysql;
2. CREATETABLEgn2LIKEgeneral_log;
3. RENAMETABLEgeneral_logTOoldLogs,gn2TOgeneral_log;