MySQL中用通用查询日志找出查询次数最多的语句的教程
MySQL开启通用查询日志generallog
mysql打开generallog之后，所有的查询语句都可以在generallog文件中以可读的方式得到，但是这样generallog文件会非常大，所以默认都是关闭的。有的时候为了查错等原因，还是需要暂时打开generallog的（本次测试只修改在内存中的参数值，不设置参数文件）。
general_log支持动态修改：
mysql>selectversion();
+-----------+ |version()| +-----------+ |5.6.16| +-----------+ 1rowinset(0.00sec)
mysql>setglobalgeneral_log=1;
QueryOK,0rowsaffected(0.03sec)
general_log支持输出到table：
mysql>setgloballog_output='TABLE';
QueryOK,0rowsaffected(0.00sec)
mysql>select*frommysql.general_log\G;
***************************1.row*************************** event_time:2014-08-1410:53:18 user_host:root[root]@localhost[] thread_id:3 server_id:0 command_type:Query argument:select*frommysql.general_log ***************************2.row*************************** event_time:2014-08-1410:54:25 user_host:root[root]@localhost[] thread_id:3 server_id:0 command_type:Query argument:select*frommysql.general_log 2rowsinset(0.00sec) ERROR: Noqueryspecified
输出到file：
mysql>setgloballog_output='FILE';
QueryOK,0rowsaffected(0.00sec)
mysql>setglobalgeneral_log_file='/tmp/general.log';
QueryOK,0rowsaffected(0.01sec)
[root@mysql-db101tmp]#more/tmp/general.log
/home/mysql/mysql/bin/mysqld,Version:5.6.16(Sourcedistribution).startedwith: Tcpport:3306Unixsocket:/home/mysql/logs/mysql.sock TimeIdCommandArgument 14081410:56:443Queryselect*frommysql.general_log
查询次数最多的SQL语句
analysis-general-log.pygeneral.log|sort|uniq-c|sort-nr
1032SELECT*FROMwp_commentsWHERE(comment_approved='x'ORcomment_approved='x')ANDcomment_post_ID=xORDERBYcomment_date_gmtDESC 653SELECTpost_id,meta_key,meta_valueFROMwp_postmetaWHEREpost_idin(x)ORDERBYmeta_idASC 527SELECTFOUND_ROWS() 438SELECTt.*,tt.*FROMwp_termsAStINNERJOINwp_term_taxonomyASttONt.term_id=tt.term_idWHEREtt.taxonomy='x'ANDt.term_id=xlimit 341SELECToption_valueFROMwp_optionsWHEREoption_name='x'limit 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 311SELECTwp_posts.*FROMwp_postsWHERE1=xANDwp_posts.IDin(x)ANDwp_posts.post_type='x'AND((wp_posts.post_status='x'))ORDERBYwp_posts.post_dateDESC 219SELECTwp_posts.*FROMwp_postsWHEREIDin(x) 218SELECTtr.object_idFROMwp_term_relationshipsAStrINNERJOINwp_term_taxonomyASttONtr.term_taxonomy_id=tt.term_taxonomy_idWHEREtt.taxonomyin(x)ANDtt.term_idin(x)ORDERBYtr.object_idASC 217SELECTwp_posts.*FROMwp_postsWHERE1=xANDwp_posts.IDin(x)ANDwp_posts.post_type='x'AND((wp_posts.post_status='x'))ORDERBYwp_posts.menu_orderASC 202SELECTSQL_CALC_FOUND_ROWSwp_posts.IDFROMwp_postsWHERE1=xANDwp_posts.post_type='x'AND(wp_posts.post_status='x')ORDERBYwp_posts.post_dateDESClimit 118SETNAMESutf8 115SETSESSIONsql_mode='x' 115SELECT@@SESSION.sql_mode 112SELECToption_name,option_valueFROMwp_optionsWHEREautoload='x' 111SELECTuser_id,meta_key,meta_valueFROMwp_usermetaWHEREuser_idin(x)ORDERBYumeta_idASC 108SELECTYEAR(min(post_date_gmt))ASfirstdate,YEAR(max(post_date_gmt))ASlastdateFROMwp_postsWHEREpost_status='x' 108SELECTt.*,tt.*FROMwp_termsAStINNERJOINwp_term_taxonomyASttONt.term_id=tt.term_idWHEREtt.taxonomyin(x)ANDtt.count>xORDERBYtt.countDESClimit 107SELECTt.*,tt.*FROMwp_termsAStINNERJOINwp_term_taxonomyASttONt.term_id=tt.term_idWHEREtt.taxonomyin(x)ANDt.term_idin(x)ORDERBYt.nameASC 107SELECT*FROMwp_usersWHEREID='x' 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 106SELECTSQL_CALC_FOUND_ROWSwp_posts.IDFROMwp_postsWHERE1=xANDwp_posts.post_type='x'AND(wp_posts.post_status='x')ANDpost_date>'x'ORDERBYRAND()DESClimit 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
PS：mysqlgenerallog日志清除技巧
mysqlgenerallog日志不能直接删除，间接方法
USEmysql; CREATETABLEgn2LIKEgeneral_log; RENAMETABLEgeneral_logTOoldLogs,gn2TOgeneral_log;