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

《MYSQL数据库MySQL中用通用查询日志找出查询次数最多的语句的教程》要点：
本文介绍了MYSQL数据库MySQL中用通用查询日志找出查询次数最多的语句的教程，希望对您有用。如果有疑问，可以联系我们。

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

MYSQL教程general_log支持动态修改：

MYSQL教程
mysql> select version();

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

MYSQL教程
mysql> set global general_log=1;

MYSQL教程
Query OK,0 rows affected (0.03 sec)

MYSQL教程general_log支持输出到table：

MYSQL教程
mysql> set global log_output='TABLE';

MYSQL教程
Query OK,0 rows affected (0.00 sec)

MYSQL教程
mysql> select * from mysql.general_logG;

MYSQL教程
*************************** 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

MYSQL教程输出到file：

MYSQL教程
mysql> set global log_output='FILE';

MYSQL教程
Query OK,0 rows affected (0.00 sec)

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

MYSQL教程
Query OK,0 rows affected (0.01 sec)

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

MYSQL教程
/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

MYSQL教程查询次数最多的SQL语句

MYSQL教程
analysis-general-log.py general.log | sort | uniq -c | sort -nr

MYSQL教程
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_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

MYSQL教程PS：mysql general log日志清除技巧
mysql general log日志不能直接删除,间接方法

MYSQL教程
USE mysql;
CREATE TABLE gn2 LIKE general_log;
RENAME TABLE general_log TO oldLogs,gn2 TO general_log;

《MYSQL数据库MySQL中用通用查询日志找出查询次数最多的语句的教程》是否对您有启发，欢迎查看更多与《MYSQL数据库MySQL中用通用查询日志找出查询次数最多的语句的教程》相关教程，学精学透。编程之家PHP学院为您提供精彩教程。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!