DBA工具:SQL自审工具sqlreview

释放双眼,带上耳机,听听看~!

DBA+工具:SQL 自审工具 sqlreview,摆脱人肉审核就在当下

DBA工具:SQL自审工具sqlreview

工具作者
贺春旸,《MySQL 管理之道:性能调优、高可用与监控》第一、二版一书作者,从事数据库管理工作多年,曾经任职于中国移动飞信、安卓机锋网、凡普金科(爱钱进),致力于MariaDB、MongoDB等开源技术的研究,主要负责数据库性能调优、监控和架构设计。

一、工具概述

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

此工具借鉴了去哪儿网 Inception 的思路和 Percona 在线 SQL 审核思路,并且把美团网 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\”}。
注:审核规则是根据我公司的情况制定而成,非 Inception 审核规则(只借鉴思路),使用时请注意!
其内部的原理,主要用正则表达式匹配规则实现。

二、功能实现

SELECT 审核

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

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

适用场景:应用开发阶段

检查项:

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′
14、之后会调用美团网 SQLAdvisor 进行索引检查

INSERT 审核

检查项:

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

UPDATE 审核规则

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

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

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

4、检查更新字段有无索引

CREATE 审核规则

检查项:

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、警告!不支持 create index 语法,请更改为 alter table add index 语法。

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

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

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

具体演示,请移步

http://blog.51cto.com/hcymysql/2053798

三、安装部署

环境安装

1、PHP 环境安装
# yum install httpd php mysql php-mysql php-devel php-pear libssh2 libssh2-devel -y
2、安装 PHP SSH2 扩展

pecl install -f ssh2

3、修改/etc/php.ini

在最后一行添加

extension=ssh2.so

4、关闭 selinux
# vim /etc/selinux/config
SELINUX=disabled

5、美团网 SQLAdvisor 安装
请移步
https://github.com/Meituan-Dianping/SQLAdvisor/blob/master/doc/QUICK_START.md

部署

将 php-sqlreview.zip 解压缩到/var/www/html/目录下

1、导入 dbinfo.sql(DB 配置信息表)和 operation.sql(SQL 工单记录表)
2、修改 db_config.php(DB 配置信息的 IP、端口、用户名、密码、库名)
3、修改 sqladvisor_config.php(访问 SQLAdvisor 服务器的 IP、SSH 端口、SSH 用户名、SSH 密码)
4、修改 sql_submit.php(记录工单表的 IP、端口、用户名、密码)和(调用 mysql 客户端的 IP、SSH 端口、SSH 用户名、SSH 密码)

脚本解释

1、index.html(SQL 传参入口)
2、sql_review.php(过审核规则)
3、sql_submit.php(通过后,SQL 上线提交)
4、order.php(工单查询-只记录成功入库的 SQL)
5、order_result1.php(按照用户名分页搜索)
6、order_result2.php(按照时间范围分页搜索)

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

笔者测试了,并制作了Docker 镜像,如下:

Github 地址 https://github.com/ppabc/SQLAdvisor

Docker 镜像 docker run -d –name=sqlreview -p 80:80 -p 3306:3306 ppabc/sqladvisor:sqlreview

登录账号 hcy   密码 123456

 

给TA打赏
共{{data.count}}人
人已打赏
安全运维

美团SQLAdvisor工具docker镜像

2018-4-11 15:24:16

安全运维

基于Alpine的Docker镜像快速搭建MySQL主从

2018-4-25 14:46:51

个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索