2017-07-27 DBA日记，使用Python解释mysql 5.6 slowquery.log

目的(Why)
在mysql 5.6环境下，提高slow_query.log的可读性，快速定位慢查询SQL
使用人群（Who)
MySql DBA
功能描述（What)
1. 数据采集：开启mysql slow query log，方法如下：
set global slow_query_log='ON'
set global slow_query_log_file='/u01/log/slow_query.log'
set global slow_launch_time=2
2. ETL：使用python代码，解析slow_query.log，执行ETL转换后，存储在MYSQL数据库中。
3. 聚合统计，按执行次数，按执行时间排序。按不同维度的优化先级找出最需要进行优化的SQL语句。
什么时候做（When)
1）每天早上9-10点期间分析昨天晚上至凌晨的慢SQL
2）每天下午17点期，分析白天的慢SQL
执行地点（Where)
目前处于demo阶段，都是通过ssh SFTP等工具，把日志拉回到桌面机上进行分析。
各位可以根据实际需要，在python代码中封装下载文件或远程打开的接口，也可以把python程序放了在MYSQL主机上自动运行，我们在后台数据库查询，这个就靠各位脑补了。
实现代码（HOW）:
1) 先决条件：
a)安装mysql connector ptyhon包，网址如下：
https://dev.mysql.com/downloads/connector/python/
b) python 运行环境
c) MYSQL客户端工具，TOAD，NAVICAT
2) 存放slow query 表设计代码:
CREATE TABLE `slow_query` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `start_time` varchar(50) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,  `username` varchar(50) CHARACTER SET gbk COLLATE gbk_bin DEFAULT NULL,  `duration` decimal(10,2) DEFAULT NULL,  `sqltext` text CHARACTER SET latin1,  PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    3)Python代码
#!/usr/bin/python
#-*-coding:utf-8 -*-'''开辟一个数组，存放三个字段，执行时长，执行用户，SQL前50个字符, SQL完整字符功能：1) 找出执行时间最长的sql语句2) 重复的SQL语句合并，并显示执行时间最长的SQL语句步骤：1）LOG日志结构化2) 合并去重3) 统计'''import mysql.connector as mctry:conn = mc.connect(user='username', password='pwd', host='192.168.0.1', database='qqt')cur=conn.cursor()Inssql='''insert into qqt.slow_query (  start_time  ,username  ,duration  ,sqltext) VALUES (%s,%s,%s,%s)'''print 'importing slow sql to mysql.......'except Exception as e:print eslog=open('.//slowlog-17.log','r')lines=slog.readlines()print len(lines)print "\#"status=Falsea=0Sql=[]Sqlset=[]Sqltext=''#格式化slow_query.log ,  算法，连续3行是以“#”之后的，不是以#开头的为一个分段；#当遇到“#”开头的，但计数器由0开始统计，显示大于第3行时重，所有变量重置，重新计数#列表加入到新列表，并重新赋值时，不影响新列表各列表元素的值。for line in lines[4:]:if line[0]=="#" and a>=3:Sqlset.append(Sql)Sql=[]Sql.append(line)#print Sqlseta=0a=a+1elif line[0]!="#" and a<3:#print lineSql=[]a=0elif line[0]=="#" and a<3:field=line[1:].split(":")Sql.append(field[1].split()[0])a=a+1elif line[0]!="#" and a>=3:if line[0:3] in ('use','SET'):passelse:Sql.append(line)a=a+1#print Sqlset[1]for sql1 in Sqlset:#print sql1[0][8:],sql1[1],sql1[2],sql1[3:]try:#print sql1[1].split('[')[0],sql1[2]start_time=sql1[0][8:27]username=sql1[1].split('[')[0]duration=round(float(sql1[2]),2)Sqltext=''for text in sql1[3:]:Sqltext=Sqltext+textprint '#',cur.execute(Inssql,(start_time,username,duration,Sqltext))conn.commit()except Exception as e:print e'''聚合去重+统计,以下是可选部份，完全可以通过mysql客户端完成''''''秉承将事交给更专业的工具完成原则，将slow query log结构化后，导入到数据库，并通过sql语句的group by方法快速完成聚合和统计. '''SelSQL='''select substring(sqltext,1,50) text,(select sqltext from qqt.slow_query where substring(sqltext,1,50)=text limit 1) sql_fulltext,count(*),max(duration)from qqt.slow_querygroup by substring(sqltext,1,50)order by 3 desclimit 30'''