mysql统计类似SQL语句查询次数

vc-mysql-sniffer 工具抓取的sql分析。

1、先用shell脚本把所有enter符号替换为null，再根据语句前后的字符分隔语句

grep -Ev '# Time:|# User@Host' /tmp/tmp_vc_mysql.txt |sed 's/# Query_time.*/myxxxxx/g' |awk BEGIN{RS=EOF}'{gsub(/\n/," ");print}'|awk BEGIN{RS=EOF}'{gsub(/myxxxxx/,"\n");print}'

2、python实现替换无关字符串 vim analyze_sql.py

#!/usr/bin/python

#coding:utf8

# python analysis-vc-log.py 3315 | sort | uniq -c | sort -nr |head -n 10

import re

import sys

import os

import commands

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)

for line in logFo:

line = re.sub(r" ","",line)

lineMatch = re.match(r".*",line,re.IGNORECASE)

if lineMatch:

lineTmp = lineMatch.group(0)

# remove extra space 把多个空格的替换成一个空格 s+ 表示空格至少出现一次

lineTmp = re.sub(r"s+", " ",lineTmp)

# replace values (value) to values (x) s*表示有0或多个空格, .*表示匹配除了换行外任意多次, ?为非贪婪模式

lineTmp = re.sub(r"valuess*(.*?)", "values (x)",lineTmp)

# replace filed = 'value' to filed = 'x' s*('|")表示匹配单引或双引0或多次 2表示应用第二个(...)分区,即 ('|") \1可以写成r"1" 其实只是<number> 。表示应用第一个分组 (=|>|<|>=|<=)

lineTmp = re.sub(r"(=|>|<|>=|<=)s*('|").*?","\1 'x'",lineTmp)

# replace filed = value to filed = x s* 匹配0或多个空格 [0-9]+ 匹配一个或多个数字

lineTmp = re.sub(r"(=|>|<|>=|<=)s*[0-9]+","\1 x",lineTmp)

# replace like 'value' to like 'x'

lineTmp = re.sub(r"likes+('|").*?","like 'x'",lineTmp)

# replace in (value) to in (x) (.*?) 匹配括号内的任意内容

lineTmp = re.sub(r"ins+(.*?)","in (x)",lineTmp)

# replace between '...' and '...' to between 'x' and 'x'

lineTmp = re.sub(r"betweens+('|").*?1s+ands+1.*?1","between 'x' and 'x' ",lineTmp)

# replace between ... and ... to between x and x

lineTmp = re.sub(r"betweens+[0-9]+s+ands+[0-9]+","between x and x ",lineTmp)

# replace limit x,y to limit

lineTmp = re.sub(r"limit.*","limit",lineTmp)

print lineTmp

logFo.close()

3 执行 python analyze_sql.py tmp_vc_mysql.txt