SQL自动审核-自助上线平台
为了让DBA从日常繁琐的工作中解放出来,通过SQL自助平台,可以让开发自上线,开发提交SQL后就会自动返回优化建议,无需DBA的再次审核,从而提升上线效率,有利于建立数据库开发规范。
借鉴了去哪网Inception的思路并且把美团网SQLAdvisor(索引优化建议)集成在一起,并结合了之前写的《DBA的40条军规》纳入了审核规则里,用PHP实现。目前在我公司内部使用。
SQL自动审核主要完成两方面目的:
1、避免性能太差的SQL进入生产系统,导致整体性能降低。
2、检查开发设计的索引是否合理,是否需要添加索引。
思路其实很简单:
1、获取开发提交的SQL
2、对要执行的SQL做分析,触碰事先定义好的规则来判断这个SQL是否可以自动审核通过,未通过审核的需要人工处理。
下面是首页界面:
使用说明:
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 col1,col2...的字段进行排序。如果查询包括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、选中你的数据库名字
2、在对话框中输入你要提交的SQL
3、点击提交审核按钮
提交以后,系统自动返回SQL优化改进意见。
-
insert审核
检查项:
1
|
1、警告: insert 表1 select 表2,会造成锁表。 |
审核通过以后,会弹出用户名和密码,提示上线:
点击我要上线按钮,会调用MySQL客户端进行语法校验和表是否存在等校验。
-
update审核
检查项:
1
2
|
1、警告!没有where条件,update会全表更新,禁止执行!!! 2、更新的行数小于10000行,可以由开发自助执行。否则请联系DBA执行!!! |
防止where 1=1 绕过审核规则
必须写真实的where条件才可以执行更新操作。
上线成功的SQL会记录到一张操作日志表里,并且会把binlog位置点记录下来,方便日后的回滚操作。
-
create审核
检查项:
1
2
3
4
5
6
7
8
9
10
|
1、警告!表没有主键 2、警告!表主键应该是自增的,缺少AUTO_INCREMENT 3、提示: id 自增字段默认值为1,auto_increment=1 4、警告!表没有索引 5、警告!表中的索引数已经超过5个,索引是一把双刃剑,它可以提高查询效率但也会降低插入和更新的速度并占用磁盘空间 6、警告!表字段没有中文注释,COMMENT应该有默认值,如COMMENT '姓名' 7、警告!表没有中文注释 8、警告!表缺少utf8字符集,否则会出现乱码 9、警告!表存储引擎应设置为InnoDB 10、警告!表应该为timestamp类型加默认系统当前时间 |
审核通过后,就可以上线了
上线失败提示:
-
alter审核
检查项:
1
2
3
|
1、警告!不支持create index语法,请更改为alter table add index语法。 2、警告!更改表结构要减少与数据库的交互次数,应改为,例alter table t1 add index IX_uid(uid),add index IX_name(name) 3、表记录小于100万行,可以由开发自助执行。否则表太大请联系DBA执行! |
1
|
4、支持删除索引,但不支持删除字段 |
——————————————————————————————————————
数据库上线工单查询(只记录成功执行的SQL)
---------------------------------------------------------------------------------------------------
安装部署,脚本代码:
http://dbaplus.cn/news-155-1944-1.html
工具下载更新:
链接-https://pan.baidu.com/s/1eUct4Bo
1、修复了一些子查询的bug。
2、首页不用手工写库了,直接从dbinfo表里获取。
3、增加一个导航栏,超链接到工单查询。
--------------------------------------------
增加主键字段名必须是id
修改sql_review.php
增加如下代码:
1
2
3
4
|
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
本文来源:无奈安全网[http://www.wnhack.com] (责任编辑:无奈人生)
下一篇:SQL自动审核-自助上线平台