如何自动化完成SQL审核

标签： CODE DB MYSQL PHP PYTHON | 发表时间：2015-01-21 17:22 | 作者：ISADBA|FH.CN

出处：http://isadba.com

sql审核主要完成两方面的目的.
1、避免性能太差的sql进入生产系统,导致整体性能降低
2、检查开发设计的索引是否合理,是否需要添加索引

第一点是SQL审核最核心的地方,避免乱七八糟的sql影响线上性能,甚至导致线上系统崩溃.
第二点是属于建模的范畴,要解决建模的最好办法是DBA参与项目前期审核,由DBA建模,如果DBA人力资源不足,那么就定期由DBA对开发人员进行培训.然后发现建模太烂的就扣KPI.

现在很多公司都是人肉来完成SQL审核的,人肉审核对dba的要求较高,需要懂一些代码,另外是费时费力,毕竟一般公司几十个开发,对应一个DBA，而且DBA还要干很多其他的事情.
如何将DBA从人肉SQL审核中解放出来呢?

思路其实很简单:
1、获取程序要执行的SQL
2、对要执行的SQL做分析,可以加各种分析条件来判断这个SQL是否可以自动审核通过,未通过审核的需要人工处理.
3、配合后期的慢查询日志分析系统完成长期的监控.

开源的解决方案主要有淘宝丹臣sqlautoreview系统.可以在github上搜索到.
但是这个系统主要是基于java sqlmapfile.xml解决自动创建索引的问题,对源数据有要求,并且是通过解析SQL结构来假设SQL的执行计划,不是特别准确,并且不能够很好的区分新sql还是老sql.

所以产生了一个新的方案:
1、为所有的执行过的sql产生一个figerprint
2、基于慢查询提供的数据,加上explain 提供的数据来判断这个sql的性能是否可接受,或者可优化.
3、自动审核通过性能可接受的部分,给DBA展示性能较差的sql,然后进行优化.

方案的优点在于:
基于用户真正执行的SQL,并且可以观察SQL执行频率.
基于MySQL真正的执行计划和执行结果,分析更准确.
每个SQL都有一个fingerprint,只需要增量处理新加的SQL,效率和性能提高.
基于Box anemometer二次开发,让慢查询和sql审核同平台,增加工具集成性,提高用户体验(DBA和开发人员)。

方案实施:
既然咱是DBA，肯定会有更DBA的思维方式.基于现有软件二次开发完成,减少开发成本,整合管理平台.
基于Box anemometer. 安装Box anemometer
Box anemometer是一款B/S架构,图形化的MySQL慢查询分析工具.功能强大易用,设计简单直接.anemometer是基于pt-query-digest的二次封装得来.

核心处理流程:
mysql node–>计划任务通过pt-query-digest收集慢查询信息–>结果写入到数据库中–>anemometer按条件去展示慢查询的结果,并且提供了图形化和趋势分布图等功能.
所以anemometer已经帮我们完成了数据收集,包括每个sql的fingerprint信息,以及相关的信息,我们在测试环境,基于anemometer,将long_query_time设置为0,就可以收集到所以的SQL及相关信息.

在我们收集到所有SQL以后,我们就要来分析这个SQL是否可以自动审核通过.这里开始我们就要定制了.

定制内容如下:
一、
设置一个单独的datasources,可以命名为audit_sql.
这个datasources里面只放置开发环境或者测试环境的慢查询(你要做sql审核基于哪个环境),将此环境的long_query_time设置为0,接收所有的sql查询.

二、修改anemometer
ALTER TABLE `global_query_review` ADD audit_status VARCHAR(255) NOT NULL DEFAULT ‘refuse’ comment ‘sql审计的状态 refuse未通过 pass审核通过’;

修改PHP代码.
在report模块的where条件中增加一个Aduit Status的选项框,可以过滤audit_status的状态
在show_query模块中增加一个Audit Status的选项框,可以人工设置audit_status的状态

三、增加两个额外的脚本,准实时的分析audit_status为refuse的sql,如果sql的满足自动审核通过的条件,那么就设置audit_status为pass,表示自动审核通过.
自动审核未通过的sql,由DBA人工在anemometer上检索和处理.
这里就涉及到一个自动审核通过的算法:
算法分两种.
第一种是准实时,也就是可以几分钟或者一个小时运行一次,主要是根据每个sql的执行效率判断是否pass.
对应的脚本名字叫做:audit_sql.py

第二种是一天一次,弱化执行效率判断,增加一天执行的频率判断.
对应的脚本名字叫做:audit_sql_day.py

各家根据自己的实际情况调整或者优化这两个脚本.
至此,你已经可以让99%以上的代码自动审核通过了,审核不通过的代码你可以让开发自己来tracking也可以主动推给开发.
对于才搭建的环境,可能会有一些乱七八糟的sql,不过使用一段时间稳定以后,异常的sql指纹都有了,那么每天产生的sql指纹就比较少了,而这部分SQL指纹也就是程序员编写新的代码产生的.

二次修改过的Box anemometer代码和对应的python脚本都在我的GITHUB上.

https://github.com/ISADBA/anemometerAudit_SQL