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

查询最多的sql语句

开启general log
 
mysql> show  variables like '%general%';
+------------------+-------------------------------------+
| Variable_name | Value |
+------------------+-------------------------------------+
| general_log | OFF |
| general_log_file | /usr/local/mysql/data/localhost.log |
+------------------+-------------------------------------+
mysql> set global general_log = "ON";
 
analysis-general-log.py脚本
 
#!/usr/bin/python
 
# sort and count mysql general log
# Author: Jason
# Created: UTC 2015-02-15 17:51:53
 
import re
import sys
import os
 
if len(sys.argv) == 2:
    logPath = sys.argv[1]
    if not os.path.exists(logPath):
        print ("file " + logPath + " does not exists.")
        sys.exit(1)
else:
    print ("Usage: " + sys.argv[0] + " logPath")
    sys.exit(1)
 
logFo = open(logPath)
match = 0
 
for line in logFo:
    line = re.sub(r"＼n","",line)
    if match == 0:
        # match line begin with numbers
        lineMatch = re.match(r"＼s+[0-9]+＼s+.*",line,flags=re.I)
        if lineMatch:
            lineTmp = lineMatch.group(0)
            match = match + 1
            continue
 
    elif match == 1:
        # match line begin with numbers
        lineMatch = re.match(r"＼s+[0-9]+＼s+.*",line,flags=re.I)
        if lineMatch:
            # match only query
            lineMatchQuery = re.match(r".*Query＼s+(.*)",lineTmp,flags=re.I)
            if lineMatchQuery:
                lineTmp = lineMatchQuery.group(1)
                # remove extra space
                lineTmp = re.sub(r"＼s+", " ",lineTmp)
                # replace values (value) to values (x)
                lineTmp = re.sub(r"values＼s*＼(.*?＼)", "values (x)",lineTmp,flags=re.I)
                # replace filed = 'value' to filed = 'x'
                lineTmp = re.sub(r"(=|>|<|>=|<=)＼s*('|＼").*?＼2","＼＼1 'x'",lineTmp)
                # replace filed = value to filed = x
                lineTmp = re.sub(r"(=|>|<|>=|<=)＼s*[0-9]+","＼＼1 x",lineTmp)
                # replace like 'value' to like 'x'
                lineTmp = re.sub(r"like＼s+('|＼").*?＼1","like 'x'",lineTmp,flags=re.I)
                # replace in (value) to in (x)
                lineTmp = re.sub(r"in＼s+＼(.*?＼)","in (x)",lineTmp,flags=re.I)
                # replace limit x,y to limit
                lineTmp = re.sub(r"limit.*","limit",lineTmp,flags=re.I)
               
                print (lineTmp)
 
            match = 1
            lineTmp = lineMatch.group(0)
        else:  
            lineTmp += line
            match = 1
 
logFo.close()

使用方法：

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

mysql general log日志清除技巧

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

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

时间： 2024-09-22 07:08:20

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

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

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

linux中Shell分析Nginx日志 找出被阻止的IP

在日常运维中会发现流量突增现象或者服务器负载升高等现象,为找到原因,需要使用nginx limit模块 对访问的Ip进行限制,然后可以分析日志. 配置nginx限制IP访问,可配置多个zone. limit_req_zone $binary_remote_addr zone=allips:10m rate=20r/s; 对产生的nginx日志进行分析 脚本. ##提供默认日志路径和名称 logsFile="日志名称" ##配置多个zoneName zoneName="alli

sql 多条件组合查询，并根据指定类别找出所有最小子类别的SQL语句备忘_MsSql

复制代码 代码如下: DECLARE @PAGESIZE INT DECLARE @PAGEINDEX INT DECLARE @PAGECOUNT INT DECLARE @RECORDCOUNT INT SELECT @PAGESIZE=5 SELECT @PAGEINDEX=1 DECLARE @FIELDNAME VARCHAR(50) DECLARE @FIELDVALUE VARCHAR(50) DECLARE @OPERATION VARCHAR(50) --组合条件 DECLAR

mysql查询数据库中出重复次数最多的记录

 代码如下 复制代码 SELECT keyword, count( * ) AS count FROM article_keyword GROUP BY keyword ORDER BY count DESC LIMIT 20 此段查询语句返回 article_keyword 表中 keyword 重复次数(count) 最多的20条记录.  代码如下 复制代码 SELECT DISTINCT count( * ) AS count FROM article_keyword GROUP BY k

加强版水王：找出出现次数刚好是一半的数字

我们知道,水王问题:有N个数,其中有一个数出现超过一半,要求在线性时间求出这个数.那么,我的问题是,加强版水王:有N个数,其中有一个数刚好出现一半次数,要求在线性时间内求出这个数. 因为,很明显,如果是刚好出现一半的话,如此例: 0,1,2,1 : 方案一: 根据上面的例子,最后我们可能会输出不是符合条件的数字,那么仔细分析的话,占一半的数字,只能在两个变量中出现:candidate和arr[n-1].如果arr[n-1]不是占一半的数据key,那么candidate最后保持着key,另一种情况

c语言 设计一个找出同数值部分排列的程序

问题描述 c语言 设计一个找出同数值部分排列的程序 定义一行的整数的输入有相同连续的地方为"同数值部分排列"找出有最长的同数值部分排列,并输出排列长度及这个数字的程序.最长的同数值部分排列有两个以上的时候,输出最后那个.输入的数字用空格或者换行区别 例1输入:0 1 1 1 2 0 0输出:3 1 例2输入:1 1 1 31 2 223输出:3 2 解决方案 #include <stdio.h>int main(){ int x; int c = 0; int px = -

使用MySQL Slow Log来解决MySQL CPU占用高的问题_Mysql

但是怎么找到是哪个SQL语句的执行时间过长呢?可以通过MySQL Slow Log来找,详解如下. 首先找到MySQL的配置文件my.cnf,根据不同版本的mysql开启慢查询的配置也不一样 mysql 5.0 [mysqld] long_query_time = 1 log-slow-queries = /var/log/mysql/slow.log mysql 5.1 [mysqld] long_query_time = 1 slow_query_log=1 slow_query_log_f

如何找出消耗cup的进程信息和执行的语句

进程|语句|执行 要找出最耗资源的sql,我们可以首先使用top等工具,找到最好资源的进程(记住进程号),例如,操作系统进程号为1217,然后根据这个进程号(v$process.spid)在v$process中找到进程地址(v$process.addr),然后根据这个地址在v$session中找到相应的sid(v$session.sid),然后根据这个sid找到相应的hash alue(v$session. sql_hash_value),然后根据这个hash alue在v$sqltext,$s

十个整数-输入10个整数，求出其中互不相同的数的个数，哪一个数的出现次数最多，共出现了多少次，急

问题描述 输入10个整数,求出其中互不相同的数的个数,哪一个数的出现次数最多,共出现了多少次,急 输入10个整数,求出其中互不相同的数的个数,哪一个数的出现次数最多,共出现了多少次,急. 解决方案 #include <stdio.h> #define MAX 11 /*输入MAX-1个整型数字,找出不同数字的个数,并且给出出现次数最多的数字.*/ int *sel_sort(int n , int *a,int seq)/*选择排序 seq:0为由小到大,1为由大到小*/ { int i=0,