MySQL中用通用查询日志找出查询次数最多的语句的教程

mysql开启通用查询日志general log
mysql打开general log之后，所有的查询语句都可以在general log文件中以可读的方式得到，但是这样general log文件会非常大，所以默认都是关闭的。有的时候为了查错等原因，还是需要暂时打开general log的（本次测试只修改在内存中的参数值，不设置参数文件）。

general_log支持动态修改：

mysql> select version();

+-----------+
| version() |
+-----------+
| 5.6.16  |
+-----------+
1 row in set (0.00 sec)

mysql> set global general_log=1;

query ok, 0 rows affected (0.03 sec)

general_log支持输出到table：

mysql> set global log_output='table';

query ok, 0 rows affected (0.00 sec)

mysql> select * from mysql.general_log\g;

*************************** 1. row ***************************
 event_time: 2014-08-14 10:53:18
  user_host: root[root] @ localhost []
  thread_id: 3
  server_id: 0
command_type: query
  argument: select * from mysql.general_log
*************************** 2. row ***************************
 event_time: 2014-08-14 10:54:25
  user_host: root[root] @ localhost []
  thread_id: 3
  server_id: 0
command_type: query
  argument: select * from mysql.general_log
2 rows in set (0.00 sec)
error: 
no query specified

输出到file：

mysql> set global log_output='file';

query ok, 0 rows affected (0.00 sec)

mysql> set global general_log_file='/tmp/general.log'; 

query ok, 0 rows affected (0.01 sec)

[root@mysql-db101 tmp]# more /tmp/general.log 

/home/mysql/mysql/bin/mysqld, version: 5.6.16 (source distribution). started with:
tcp port: 3306 unix socket: /home/mysql/logs/mysql.sock
time         id command  argument
140814 10:56:44   3 query   select * from mysql.general_log

查询次数最多的sql语句

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

1032 select * from wp_comments where ( comment_approved = 'x' or comment_approved = 'x' ) and comment_post_id = x order by comment_date_gmt desc
653 select post_id, meta_key, meta_value from wp_postmeta where post_id in (x) order by meta_id asc
527 select found_rows()
438 select t.*, tt.* from wp_terms as t inner join wp_term_taxonomy as tt on t.term_id = tt.term_id where tt.taxonomy = 'x' and t.term_id = x limit
341 select option_value from wp_options where option_name = 'x' limit
329 select t.*, tt.*, tr.object_id from wp_terms as t inner join wp_term_taxonomy as tt on tt.term_id = t.term_id inner join wp_term_relationships as tr on tr.term_taxonomy_id = tt.term_taxonomy_id where tt.taxonomy in (x) and tr.object_id in (x) order by t.name asc
311 select wp_posts.* from wp_posts where 1= x and wp_posts.id in (x) and wp_posts.post_type = 'x' and ((wp_posts.post_status = 'x')) order by wp_posts.post_date desc
219 select wp_posts.* from wp_posts where id in (x)
218 select tr.object_id from wp_term_relationships as tr inner join wp_term_taxonomy as tt on tr.term_taxonomy_id = tt.term_taxonomy_id where tt.taxonomy in (x) and tt.term_id in (x) order by tr.object_id asc
217 select wp_posts.* from wp_posts where 1= x and wp_posts.id in (x) and wp_posts.post_type = 'x' and ((wp_posts.post_status = 'x')) order by wp_posts.menu_order asc
202 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') order by wp_posts.post_date desc limit
118 set names utf8
115 set session sql_mode= 'x'
115 select @@session.sql_mode
112 select option_name, option_value from wp_options where autoload = 'x'
111 select user_id, meta_key, meta_value from wp_usermeta where user_id in (x) order by umeta_id asc
108 select year(min(post_date_gmt)) as firstdate, year(max(post_date_gmt)) as lastdate from wp_posts where post_status = 'x'
108 select t.*, tt.* from wp_terms as t inner join wp_term_taxonomy as tt on t.term_id = tt.term_id where tt.taxonomy in (x) and tt.count > x order by tt.count desc limit
107 select t.*, tt.* from wp_terms as t inner join wp_term_taxonomy as tt on t.term_id = tt.term_id where tt.taxonomy in (x) and t.term_id in (x) order by t.name asc
107 select * from wp_users where id = 'x'
106 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') and post_date > 'x' order by wp_posts.post_date desc limit
106 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') and post_date > 'x' order by rand() desc limit
105 select sql_calc_found_rows wp_posts.id from wp_posts where 1= x and wp_posts.post_type = 'x' and (wp_posts.post_status = 'x') and post_date > 'x' order by wp_posts.comment_count desc limit

ps：mysql general log日志清除技巧
mysql general log日志不能直接删除，间接方法

use mysql;
create table gn2 like general_log;
rename table general_log to oldlogs, gn2 to general_log;