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

2023-08-02
广东
本文字数：3195 字
阅读完需：约 10 分钟

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