分组自研的审计平台最近推出慢SQL优化的功能,topN慢SQL可以通过mysqldumpslow拿的到,但由于mysqldumpslow输出的信息不包含数据库,这让程序后续的自动优化变得有些棘手。在观察了MySQL慢日志结构后,决定自己写一个python解析程序,返回的结果比mysqldumpslow解析结果上多出数据库名称这一列:
<代码>数:15时间=0.002961秒(0.034505)=8.8 e-05s锁(0.000767秒)行=446 (6690),dbmgr [dbmgr] @10.33.46.179 mysql——SQL所属数据库 代码显示全局变量; 之前python版本:2.7
文件名称:slowParse。py——目前仅支持按查询时取topN
代码内容:<代码>导入操作系统 导入系统 def get_sql (slowlog topN): #慢日志所在目录,请自行替换 f1=开放("/var/mysql/data3306/" + slowlog,“r”) flag1=1 flag2=0 sqltext=" " slow_sql_all={} sql_info=[] queryTime_list=[] locksTime_list=[] rows_list=[] logonInfo_list=[] db="没有" rownum=0 而1: 行=f1.readline () rownum +=1 如果不行: 打破 elif“使用”线和len(线)& lt;30: db=getDB (line.strip ()) elif“# User@Host”线和flag1==1: flag2=1 userAndHost=getUserAndHost (line.strip ()) logonInfo=userAndHost [0] +”(“+ userAndHost [0] +”) @”+ userAndHost [1] logonInfo_list.append (logonInfo) elif“# Query_time”线和flag1==1和flag2==1: execInfo=getExecInfo (line.strip ()) queryTime_list.append (execInfo [0]) locksTime_list.append (execInfo [1]) rows_list.append (execInfo [2]) elif”;“和“时间戳”不一致和flag1==1 flag2==1: flag2=0 sqltext=sqltext + line.strip () 如果slow_sql_all.has_key (sqltext): tmp=slow_sql_all [sqltext] tmp [0] .append (queryTime_list [0]) tmp [1] .append (locksTime_list [0]) tmp [2] .append (rows_list [0]) tmp [3] .append (logonInfo_list [0]) 其他: #数=1 # sql_info.append(计数) sql_info.append (queryTime_list) sql_info.append (locksTime_list) sql_info.append (rows_list) sql_info.append (logonInfo_list) sql_info.append (db) slow_sql_all [sqltext]=sql_info queryTime_list=[] locksTime_list=[] rows_list=[] logonInfo_list=[] sqltext=" " sql_info=[] elif flag1==1和flag2==1和“# User@Host”线和“# Query_time”不一致,“#”线和“时间戳”不一致: sqltext=sqltext + line.strip () + " f1.close () sqlCombined={} sqlTmp={} 因为我在slow_sql_all: #我打印,slow_sql_all[我] 数=len (slow_sql_all[我][0]) totalQueryTime=0 totalLocksTime=0 totalRows=0 在slow_sql_all j[我][0]: totalQueryTime +=浮动(j) maxQueryTime=浮动(max (slow_sql_all[我][0])) 对k slow_sql_all[我][1]: totalLocksTime +=(k)浮动 maxLocksTime=浮动(max (slow_sql_all[我][1])) l在slow_sql_all[我][2]: totalRows +=int(左) maxRows=int (max (slow_sql_all[我][2])) logonInfo=slow_sql_all[我][3][0] db=slow_sql_all[我][4] sqlCombined[我]=( 统计,maxQueryTime、totalQueryTime maxLocksTime、totalLocksTime maxRows, totalRows logonInfo, db) sqlTmp[我]=maxQueryTime sqlTopN=排序(sqlTmp.items(),关键=λx: x[1],反向=True) [: topN] #请自行替换生成文件的所在目录 f2=开放("/var/mysql/data3306/" + slowlog [4] + + str (topN) +“最高。txt”、“w”) 因为我在sqlTopN: sqltext=我[0] count_str="数:" + str (sqlCombined [sqltext] [0]) queryTime_str="时间=" + str (sqlCombined [sqltext] [1]) +”(“+ str (sqlCombined [sqltext] [2]) + " s)” 锁locksTime_str="=" + str (sqlCombined [sqltext] [3]) +”(“+ str (sqlCombined [sqltext] [4]) + " s)” rows_str="行=" + str (sqlCombined [sqltext] [5]) +”(“+ str (sqlCombined [sqltext] [6]) +”),“ logonInfo_str=sqlCombined [sqltext] [7] db_str=sqlCombined [sqltext] [8] f2.write ( count_str + " " + queryTime_str + " " + locksTime_str + " " + rows_str + " " + logonInfo_str + " " + db_str +“\ n”+ sqltext +“\ n”) f2.close () def getDB(线): 信息=线。分割(" ") db=信息[1](:1) 返回数据库 def getUserAndHost(线): info_list=线。分割(" ") 用户=info_list [2] .split (“(”) [0] idx=info_list.index (“@”) hostInfo=info_list (idx + 2) 如果hostInfo==癧]”: 主机=" localhost " 其他: 主机=hostInfo (1): 1) 返回用户、主机 def getExecInfo(线): info_list=线。分割(" ") Query_time=info_list [0]。分割(" ")[2] Lock_time=info_list [1]。分割(" ")[1] Rows_sent=info_list [1]。分割(" ")[3] 返回Query_time Lock_time Rows_sent if __name__==癬_main__”: 文件名=str (sys.argv [1]) topN=int (sys.argv [2]) topN get_sql(文件名)重写慢日志解析程序,实现打印慢SQL信息及其所属数据库
