SQL自动审核-自助上线平台

sqlops流程.png

演示地址 http://fander.jios.org:8008/

普通上线账号guest 密码123456

管理员审批账号admin密码123456

感谢好友陈俊聪友情提供云主机。

为了让DBA从日常繁琐的工作中解放出来通过SQL自助平台可以让开发自上线开发提交SQL后就会自动返回优化建议无需DBA的再次审核从而提升上线效率有利于建立数据库开发规范。

借鉴了去哪网Inception的思路并且把美团网SQLAdvisor索引优化建议集成在一起并结合了之前写的《DBA的40条军规》纳入了审核规则里用PHP实现。目前在我公司内部使用。

SQL自动审核主要完成两方面目的

1、避免性能太差的SQL进入生产系统导致整体性能降低。

2、检查开发设计的索引是否合理是否需要添加索引。

思路其实很简单:

1、获取开发提交的SQL

2、对要执行的SQL做分析触碰事先定义好的规则来判断这个SQL是否可以自动审核通过未通过审核的需要人工处理。

下面是首页界面

界面3.png

使用说明

1、针对select/insert/update/create/alter加了规则delete需要审批。 

2、语句之间要有空格例where id = 100没有空格会影响判断的准确性。 

3、SQL语句后面要加分号; MySQL解析器规定分号才可以执行SQL。

4、反引号`会造成上线失败需要用文本编辑器替换掉。

5、支持多条SQL解析用一个分号;分割。例如

     insert into t1 values(1,'a');

     insert into t1 values(2,'b'); 

6、JSON格式里的双引号要用反斜杠进行转义例如{\"dis_text\":\"nba\"}。

SELECT审核

1、开发人员可以直接将SQL语句提交到平台进行风险评估

2、平台对SQL语句进行分析自动给出其不符合开发规范的改进意见

3、适用场景应用开发阶段

检查项

1、select * 是否有必要查询所有的字段

2、警告没有where条件注意where后面的字段要加上索引

3、没有limit会查询更多的数据

4、警告子查询性能低下请转为join表关联

5、提示in里面的数值不要超过1000个

6、提示采用join关联注意关联字段要都加上索引如on a.id=b.id

7、提示MySQL对多表join关联性能低下建议不要超过3个表以上的关联

8、警告like '%%'双百分号无法用到索引like 'mysql%'这样是可以利用到索引的

9、提示默认情况下MySQL对所有GROUP BY col1col2...的字段进行排序。如果查询包括GROUP BY

想要避免排序结果的消耗则可以指定ORDER BY NULL禁止排序。

10、警告MySQL里用到order by rand()在数据量比较多的时候是很慢的因为会导致MySQL全表扫描故也不会用到索引

11、提示是否要加一个having过滤下

12、警告禁止不必要的order by排序,因为前面已经count统计了

13、警告MySQL里不支持函数索引例DATE_FORMAT('create_time','%Y-%m-%d')='2016-01-01'是无法用到索引的需要改写为

create_time>='2016-01-01 00:00:00' and create_time<='2016-01-01 23:59:59'

之后会调用美团网SQLAdvisor进行索引检查

使用概述

1.png

1、选中你的数据库名字

2、在对话框中输入你要提交的SQL

3、点击提交审核按钮

2.png

提交以后系统自动返回SQL优化改进意见。

insert审核

检查项

1、警告: insert 表1 select 表2会造成锁表。

3.png

审核通过以后会弹出用户名和密码提示上线

4.png

点击我要上线按钮会调用MySQL客户端进行语法校验和表是否存在等校验。

5.png

update审核

检查项

1、警告没有where条件update会全表更新禁止执行

2、更新的行数小于10000行可以由开发自助执行。否则请联系DBA执行!

6.png

7.png

防止where 1=1 绕过审核规则

update5.png

必须写真实的where条件才可以执行更新操作。

上线成功的SQL会记录到一张操作日志表里并且会把binlog位置点记录下来方便日后的回滚操作。

QQ图片20171223192835.png

create审核

检查项

1、警告表没有主键

2、警告表主键应该是自增的缺少AUTO_INCREMENT

3、提示id自增字段默认值为1auto_increment=1

4、警告表没有索引

5、警告表中的索引数已经超过5个索引是一把双刃剑它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间

6、警告表字段没有中文注释COMMENT应该有默认值如COMMENT '姓名'

7、警告表没有中文注释

8、警告表缺少utf8字符集否则会出现乱码

9、警告表存储引擎应设置为InnoDB

10、警告表应该为timestamp类型加默认系统当前时间

c1.png

c2.png

审核通过后就可以上线了

c3.png

上线失败提示

c4.png

alter审核

检查项

1、警告不支持create index语法请更改为alter table add index语法。

2、警告更改表结构要减少与数据库的交互次数应改为例alter table t1 add index IX_uid(uid),add index IX_name(name)

3、表记录小于100万行可以由开发自助执行。否则表太大请联系DBA执行!

a1.png

a2.png

4、支持删除索引但不支持删除字段

alter4.png

——————————————————————————————————————

数据库上线工单查询只记录成功执行的SQL

工单查询.png

---------------------------------------------------------------------------------------------------

安装部署脚本代码

http://dbaplus.cn/news-155-1944-1.html

工具下载更新

链接-https://pan.baidu.com/s/1eUct4Bo

1、修复了一些子查询的bug。

2、首页不用手工写库了直接从dbinfo表里获取。

3、增加一个导航栏超链接到工单查询。

--------------------------------------------

增加主键字段名必须是id

修改sql_review.php

增加如下代码

if(!preg_match('/.*\bid\b.*int.*/',$multi_sql[$x])){

    echo "<big><font color=\"#FF0000\">警告$parmArr[2]表主键字段名必须是id。</font></big></br>";

    $c++;

}

----------------------

2018-03-26更新

1、增加了用户登录页面

2、提交SQL时多了一次确认弹窗

--------------

多增加

1、login_user.sql  用户登录验证表

注

客户端版本使用mysql5.5或者mariadb10.X。

5.6会出现Warning: Using a password on the command line interface can be insecure导致上线失败。

脚本解释

1、index.html用户登录入口

2、login.php用户密码校验

https://pan.baidu.com/s/1TPNIEFp5-mpz3a1mGUsawg

-------------------------------------------

2018-04-12更新

1、增加上线统计页面

2、增加规则

1、主键必须是id

  2、用DECIMAL代替FLOAT和DOUBLE存储精确浮点数

  3、应使用默认的字符>集核对utf8_general_ci

  4、避免使用外键

https://pan.baidu.com/s/1KKw9aMWa-9Q_8efaiQndMQ

-------------------------------------------

2018-04-27更新

1、增加人工确认审核功能

上线流程为开发提交SQL系统自动审核sql_review.php审核通过后生成我的工单待管理员批复管理员人工确认审核通过后开发点击执行完成上线。

表

1、login_user.sql  用户登录验证表权限功能

2、sql_order_wait.sql  工单待审核生成表

3、dbinfo.sqlDB配置信息表

脚本解释

1、index.html用户登录入口

2、login.php用户密码校验

3、main.php首页框架栏

4、header.php用户登录欢迎页面和注销

5、left.php导航栏

6、sql_interface.phpSQL传参入口

7、sql_review.phpSQL审核

8、my_order.php查看我的工单执行撤销

9、wait_order.php管理员人工批复通过否决

10、update.php管理员审批确认

11、update_status.php修改审批状态值

12、execute.php开发执行SQL工单

13、execute_status.php修改执行工单状态

14、cancel.php开发自行撤销工单

15、cancel_status.php修改撤销工单状态

16、stat/show.html工单动态统计图表

17、db_config.phpDB配置信息的IP、端口、用户名、密码、库名

18、sqladvisor_config.php访问SQLAdvisor服务器的IP、SSH端口、SSH用户名、SSH密码

---------------------

注

1、客户端版本使用mysql5.5或者mariadb10.X。

5.6会出现Warning: Using a password on the command line interface can be insecure导致上线失败。

2、php文件里的涉及连接数据库的用户名和密码要修改这块没有做成统一个DB配置文件调用。

https://pan.baidu.com/s/194zXrat-GLJa0AZu2KxebQ

 ---------------------

2018-05-04更新

1、增加发邮件给管理员审核SQL工单功能

上线流程为开发提交SQL系统自动审核sql_review.php审核通过后生成我的工单待管理员批复并且发邮件通知管理员人工确认审核通过后开发点击执行完成上线。

脚本解释

1、mail/mail.php邮件配置信息--修改你自己的邮箱用户名和密码

2、mail/sendEmail开源邮件perl脚本

3、sql_review.php触发发送邮件-覆盖掉之前的旧文件

https://pan.baidu.com/s/1abduiOMV8UguxfHCfBw5sg

------------------------------------------------------------------------------------------

10.26日更新

增加一键生成反向SQL回滚功能。

https://pan.baidu.com/s/1eUct4Bo

https://github.com/hcymysql/sqlops

©著作权归作者所有来自51CTO博客作者hcymysql的原创作品如需转载请注明出处否则将追究法律责任

每一份赞赏源于懂得