Home >Database >Mysql Tutorial >MySQL中用通用查询日志找出查询次数最多的语句的教程_MySQL

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

WBOY
WBOYOriginal
2016-05-27 13:46:211026browse

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;

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn