GaussDB技术解读系列之SQL Audit，面向应用开发的SQL审核工具

426 0 0

作者:华为云开发者社区

本文分享自华为云社区《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多个。