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 mc

try:
	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 e


slog=open('.//slowlog-17.log','r')
lines=slog.readlines()
print len(lines)
print "\#"
status=False
a=0
Sql=[]
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 Sqlset
		a=0
		a=a+1
	elif line[0]!="#" and a<3:
		#print line
		Sql=[]
		a=0
	elif line[0]=="#" and a<3:
		field=line[1:].split(":")
		Sql.append(field[1].split()[0])
		a=a+1
	elif line[0]!="#" and a>=3:
		if line[0:3] in ('use','SET'):
			pass
		else:
			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+text
		print '#',
		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_query
group by substring(sqltext,1,50)
order by 3 desc
limit 30
'''