GaussDB技术解读系列之SQL Audit,面向应用开发的SQL审核工具
本文分享自华为云社区《GaussDB技术解读系列之SQL Audit,面向应用开发的SQL审核工具》,作者:华为云数据库和应用迁移专家。
前言
我们先从一个SQL语句说起(以某传统单机数据库为例)。
也许这就是我们业务代码中潜藏的一个SQL语句,对于一个普通开发者来说,这个语句编写工整,逻辑清晰,没有什么问题,可以直接推到代码仓中交付上线。但是一个有经验的开发者或数据库管理员可能会发现这个SQL存在诸多的优化点:
-
两张表的id字段是否有索引?
-
like语句不符合最左匹配原则,能否改写?
-
test_1表where条件中的create_time判断不符合单边原则,无法走索引,可以改写;
-
union会对结果集去除,效率较低,能否换成union all?
-
test_2表的id字段被函数引用,也可能用不上索引,可以优化;
-
test_2表是否存在id和name联合索引,能否加hint,指定特定索引提高查询性能?
貌似经过上述的分析后,这个SQL可以焕然一新,在该数据库上飞一样地跑起来,但这就完了吗?其实并没有,在单机数据库上也许已经优化到了极致,可当我们的数据库是一个分布式数据库呢?它可能又会带来新的性能问题,我们要考虑where条件中的id是否是分布键,concat函数是否会影响算子的下推…...这一系列的问题都会产生。
这实际就是我们所面临的现状,开发者的技术能力良莠不齐,DBA对数据库知识的局限性导致烂SQL无处不在,而且随着数据库的不断变更或演进,一些好的SQL也可能逐步变成需要优化的烂SQL, 我们要时刻不断地找寻它们的踪迹。
SQL Audit审核工具介绍
华为内部有很多业务部门,对传统单机数据库、MySQL、PostgreSQL等各种数据库都有深度的使用,也一直备受烂SQL的困扰,随着GaussDB在内部业务系统的规模应用,现存SQL在GaussDB中能否高质量运行也面临挑战,于是我们开发了SQL Audit工具,根据公司内部各业务部门多年积累的SQL开发规范和GaussDB数据库的优秀实践,整理出SQL审核规则上百条,对命名规范、表结构/索引设计、SQL性能优化、分布键及算子下推等常见影响SQL质量的问题都可以做深入的分析和审核,同时我们又开发了一些插件,直接集成到开发的流水线中,自动从代码仓获取SQL语句,做到一键审核。
SQL审核的核心流程可以分为以下三个阶段:
SQL获取:即我们能从哪些渠道获取到需要审核的SQL, 获取能力决定了我们能否对开发中的代码做更全面的审核;
SQL语法解析:是针对具体的每一条SQL做语法树的生成和分析;
SQL规则审核:是拆解SQL语句的每一部分,和相关审核规则项逐一做匹配,找出待优化或风险点,最终形成审核报告。
SQL获取
客户通过SQL访问数据库的渠道多种多样,客户端工具、命令行、SQL脚本、应用代码…...
代码开发又可以采用JDBC、ODBC、底层API调用等各种方式,SQL语句既可以直接在代码中拼接,也可以通过配置文件(如:Mybatis),还可以通过ORM框架(如:Hibernate )访问数据库,所以如果想要获取到客户的全部SQL是一件非常困难的事情。
SQL Audit对当前大部分SQL使用场景进行了支持,而且还在持续扩大SQL能获取的范围,力求能够全面地将客户使用的所有SQL全部审核到,下图是当前SQL Audit工具支持的SQL获取范围。
-
手动输入
手动输入为客户提供了一个简单、易操作的平台,客户可以随时把自己编写的SQL语句输入到SQL Audit工具中进行审核,根据审核结果直接对语句做调整,同时也可以将一个.sql文件整体上传上来,进行批量的审核。
-
源代码
源代码是烂SQL最主要的来源,但因其编程语言多种多样(C/C++/JAVA/GO/PYTHON/SHELL…...),编写方式也千奇百怪,所以很难将每种场景的SQL都获取完整,我们将代码中的SQL分成了三类:
1) 源码拼接SQL
通过拼接的方式生成SQL语句,拼接的过程可能会引入很多变量,这种情况无法获取到完整的SQL,所以通过静态文件提取SQL的方式会有很大缺陷,SQL Audit工具支持对Java代码做语法解析,提取里面的SQL,对于其他语言的代码目前暂不支持。
2) 无SQL的ORM框架
例如Hibernate、SQLAlchemy等这些ORM框架无法从代码中获取到SQL语句,SQLAudit工具提供了基于Java二进制改写技术,在JVM运行时动态监听JDBC API,获取SQL语句。
3) 配置SQL的ORM
很多业务系统基于Mybatis框架搭建访问数据库的能力,Mybatis通过注解或配置文件的方式编写SQL语句,SQL Audit工具能够对Mybatis的注解和配置文件进行深度解析,提取SQL成功率达99%以上。
-
数据库对象
数据库表结构、索引、约束的设计以及存储过程、函数等PL/SQL的编写对数据库的性能起决定性作用,SQL Audit工具可以连接到数据库,获取数据库中的全部对象定义,从设计的规范性(如:命名规范、长度/大小写限制)、合理性(如:索引是否合理)和性能等方面进行考量,给出审核建议。
-
数据库日志
为了更全面地获取到发生在数据库的SQL语句,从数据库本身的日志层面着手也是一个比较可行的方案,解析数据库的redo、开启数据库审计日志、查询SQL缓存区等方式都能够有效获取到运行SQL,SQL Audit工具也支持通过数据库日志获取SQL语句的能力。
-
流量抓取
为了解决从源代码中无法获取全部SQL的问题,我们开发了基于流量抓取的SQL审核能力,它能极大提升对SQL获取的完整度。IP+端口作为数据库对外的统一入口,基本可以包含客户业务和运维所产生的全部SQL语句,通过对数据库服务器端口的旁路监听,获取到网络协议包,经过对数据库网络协议解析和重复SQL过滤,得到有效的SQL语句,最后将这些SQL传入SQL Audit工具进行审核。
SQL解析
SQL解析的过程就是将SQL语句按照语法规则解析成语法树的过程,一般的解析过程分为词法解析和语法解析,然后生成语法树,大部分对SQL语句分析的工具都是直接遍历语法树实现的,SQL Audit工具没有直接解析语法树,而是增加了一个处理过程,将语法树解析成Java描述类,后面所有的审核规则都是基于这个语法描述类进行,这样大大提高对审核规则的开发效率,同时降低了开发难度。
SQL审核
-
丰富的审核规则
审核的核心是审核规则,而审核规则的核心是对数据库的理解+对客户业务开发理解的实践经验总结,我们结合GaussDB数据库的最佳实践+公司内外部客户的实际使用场景,整理出审核规则数百条,目前产品中已支持规则78条,包含了SQL开发过程中常见的规范和性能问题,后续会有更多的规则持续丰富到产品中。
SQL Audit同时提供了模板配置功能,客户可以根据自身业务场景灵活地选择需要审核的规则。
-
深度审核
SQL Audit审核流程如下图所示:
当一个SQL输入到SQL Audit中后,首先会对SQL进行语法解析,然后根据SQL中所依赖的表、视图等对象,去数据库中获取元数据信息(列信息、索引信息等),如果这个SQL语句的性能可能受执行计划的影响,则会再从数据库中获取该语句的执行计划,综合上述全部信息,逐一匹配每一个相关的规则进行审核,最终输出全部违反规则项。
实践案例
华为云内部某系统有一部分的业务代码是基于JAVA的Mybatis框架开发,在将数据库替换到GaussDB的过程中有大量的SQL做了兼容性改造,为保证改造后的SQL能够高质量地在GaussDB数据库中运行,该系统通过SQL Audit工具对整个代码仓进行全面审核,同时在流水线中部署了SQL Audit审核插件,持续对增量代码进行看护。SQL Audit发现了大量的不规范和低性能的SQL,提前规避了风险SQL流入到生产环境,开发人员根据SQL Audit的审核报告对代码进行了优化,业务切换到GaussDB后持续稳定运行。
以其中一个任务为例,该任务涉及SQL总数有1881个,审核出有问题的SQL有300多个。
审核结果统计报告
审核问题SQL详情
总结
GaussDB在打造内核竞争力的同时,希望给客户提供全流程、全链路,面向开发和运维的数据库自动驾驶体验。今年我们发布的SQL自动审核工具,在开发环节帮助客户写好SQL,拒绝烂SQL。
未来,我们还将进一步支持对PL/SQL审核的支持,比如存储过程、函数、触发器、包等的审核,以及与AI大模型的结合,大模型在SQL语言的处理上已经做得很好,SQL Audit工具会和华为的盘古大模型进行对接,通过大模型的能力增强它的审核、优化和改写能力。