OceanBase ODC功能解析：SQL 检查规范，降低数据库高危变更风险

前言

数据库的开发与变更规范性是各企业开发过程中无法回避的课题。优秀的数据库存储设计能够充分释放数据库的性能潜力，进而有效减少运维成本。同时，规范且可控的数据库变更流程则是业务稳定运行的坚实基础。而要实现这一切，都离不开对SQL开发规范的严格遵守与坚持。

然而，目前很多SQL 开发规范更多还是停留在纸上。这样“口口相传”的方式缺乏约束，不同的人对同一条规范可能会有不同的理解，开发规范庞大的数量也让那些 DBA 新手们难以短时间融会贯通。

ODC看到了这个问题，在 4.2.0 版本推出了 SQL 检查规范功能，该功能支持 OB-MySQL 以及 OB-Oracle 数据源。

一条 SQL 引发的“血案”

删库跑路

2020 年某公司的员工因为生活不如意，无力偿还贷款等原因，在其住处通过电脑连接公司虚拟专用网，登录服务器后将公司数据库内数据全部删除，导致公司服务瘫痪，数百万用户无法正常使用。虽然经过数天的抢修最终恢复了服务，但还是造成了数千万元经济损失，公司市值蒸发数亿元。

查询效率低下

某公司的一张业务表定义如下：

create table bz_tbl (
  id bigint(11) primary key,
  name varchar(64),
  ...
  gmt_create timestamp,
  index bz_tbl_idx(name)
);

该表中有大量记录，开发人员使用如下语句查询 select * from bz_tbl where name like '%xxx'发现查询十分缓慢，查询执行计划发现该条语句竟然没有走索引。最终定位到了问题：

1. name列上虽然定义了索引，但是查询条件在name列上进行了左模糊匹配导致索引失效。
2. select *式的写法导致了大量的回表查询，进而造成查询性能低下。

查询结果不符合预期

开发人员想要查询bz_tbl表中col列的值不属于 1，2 和 NULL 的记录，于是他写下了如下 SQL：

select col from bz_tbl where col not in (1,2,NULL);

执行后发现这条 SQL 什么记录也没返回。开发人员紧接着又执行了一次查询，发现表中是存在数据的：

$ > select col from bz_tbl;
+------+
| COL  |
+------+
|    1 |
|    2 |
| NULL |
|    3 |
+------+
4 rows in set (0.00 sec)

此时他才发现原来是 not in 表达式中存在 NULL 值导致的。

SQL 检查规范

从上面的“血案”中可以看出：导致这些故障的原因是开发者对 SQL 研发经验的不足或恶意攻击导致的。针对开发者经验不足的问题，在以往通常是通过开发者文档或一些口口相传的规范来指引开发者的行为，无法形成有强约束，只能依靠开发者自身保证。面对恶意攻击，只能通过限制开发者数据库账号的权限来规避，但这通常会导致开发效率的下降。为了解决这些痛点 ODC 在 4.2.0 版本推出了 SQL 检查规范（SQL-Check）功能，通过内置的数十个 SQL 开发规则共同构成了 OceanBase 上的最佳 SQL 实践，完美适配 OB-MySQL 模式以及 OB-Oracle 模式。通过 SQL 检查功能，ODC 解决了如下问题：

1. SQL 检查规范流于纸面的问题：ODC 将 SQL 检查规范内置到产品中，用程序语言将规则固化下来，避免了口口相传过程中导致的偏差以及不同开发/DBA之间对规则适用性理解的差异。
2. SQL 检查规范没有强约束的问题：ODC 将 SQL 检查规范融入到了日常的开发/变更流程中，用户只要通过 ODC 进行开发和变更，就无法绕过 SQL 检查。增强了对数据库的保护和对开发变更人员的约束。
3. SQL 检查规范含义不清的问题：对于开发者而言以前可能并不能理解某条 SQL 检查规则有何意义，既然数据库提供了这样的功能，凭什么限制使用。ODC 在用户违反了某条规则时将会给出详细和完整的解释。

SQL 检查功能在不同的研发环境中提供不同的规则检查力度，在数据库开发和数据库变更 2 个场景下为用户提供服务。

细粒度配置

自定义规则内容

SQL 检查规范由数十条规则组成，用户可以在：

安全规范 -> 环境 -> SQL 检查规范

看到这些规则：

某些规则存在可配置的空间，可以通过传入不同配置值的方式改变规则的行为。

以“限制主键数据类型”为例，如果主键引用列的数据类型不在规则允许的数据类型范围内就会触发此规则。默认情况下，主键引用列类型的允许范围包括int，varchar2，number，float以及bigint，如果用户想放宽或收紧到其他类型范围，可以通过编辑此规则的“允许作为主键的类型”项来实现：

适配异构开发环境

企业的数据库资源通常会划分为不同的环境，例如开发环境，测试环境，生产环境等。不同环境的数据库承担不同的职责。相应地，会施以不同力度的管控。ODC 4.2.0 将用户的数据库划分为“默认”，“开发”，“生产”以及“测试” 4 个研发环境：

用户根据自身的管控需要在不同的环境上施以不同力度的 SQL 检查规范。举例来说，假设用户希望“SELECT 语句不建议使用 *”在生产环境上生效，但在测试环境上禁用，就可以通过在“生产环境”和“测试环境”上对这条规则设置不同的“改进等级”来实现。

守护研发流程

接下来我们以一个实际的数据库研发场景展示 SQL 检查规则是如何做到全流程守护用户的数据库研发的。假定用户的研发流程如下：

第一步：“测试环境”下创建一张数据库表，结构如下：

CREATE TABLE IF NOT EXISTS `user`(
	`id` bigint NOT NULL,
 	`name` varchar(128) NOT NULL,
 	`account_name` varchar(128) NOT NULL,
 	`organization_id` bigint NOT NULL,
 	`email_address` varchar(320) DEFAULT NULL,
 	`password` varchar(256) NOT NULL,
 	`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record insertion time',
 	`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record modification time',
 	`description` varchar(512) DEFAULT NULL,
 	CONSTRAINT pk_user_id PRIMARY KEY(`id`),
 	CONSTRAINT uk_user_organization_id_account_name UNIQUE KEY(`organization_id`, `account_name`)
);

第二步：“测试环境”下在数据库表中插入若干条测试数据：

insert into `user` values (1, 'test', 'test_account', 1, 'xxx@test.com', '****', CURRENT_TIMESTAMP, null, 'desp info');
insert into `user` values (2, 'test2', 'test_account2', 1, 'xxx@test2.com', '****', CURRENT_TIMESTAMP, null, 'desp info2');
insert into `user` values (3, 'test3', 'test_account3', 1, 'xxx@test3.com', '****', CURRENT_TIMESTAMP, null, 'desp info3');

第三步：“测试环境”下查询表中的数据，确保已经插入完成：

select * from `user`;

第四步：在“生产环境”中将第一步的表结构建立。

约束数据库开发

管理员在“开发环境”下制定了如下 SQL 检查规范：

1. 建表语句必须有表级注释，以更好地帮助开发人员理解每张表的业务意义，减少沟通成本。
2. 建表语句中必须含有主键，且主键列只能使用 bigint 类型，必须使用自动自增属性，自增初始值不要求。
3. 建表语句中每个列都建议有注释，如果想创建出不包含有列注释的表需要审批。
4. 建表语句中声明为 NOT NULL 的列必须有默认值，避免上层业务系统没有传值导致的数据库写入异常。
5. 允许使用 SELECT * 查询数据。
6. 允许在 INSERT 语句中不指定具体的列。

说明：所有标记为必须的规范都是一定要遵守的，除非管理员调整该规则的级别；所有需要审批级别的规则无法直接执行，需要走审批；所有允许级别的规则不进行卡点，仅提示。

用户进入一个开发库，将准备好的建表语句贴入编辑窗口。开发人员可以先点击“SQL 检查按钮”确认输入的 SQL 是否违反了规则：

所有违反的规则通过不同的颜色区分不同的级别，可以通过点击“定位”按钮定位到 SQL 违反检查规则的具体位置。用户可以选择按照检测结果修改自己的 SQL，也可以选择直接执行，如果选择直接执行 ODC 将会按照以下流程来处理：

首先判断 SQL 的类型是否可以被直接执行（注：可通过“安全规范 -> 环境 -> SQL 窗口规范 -> SQL 窗口允许执行的 SQL 类型”来修改可直接执行的 SQL 类型）。如果可以直接执行，其次判断 SQL 是否违反了“必须改进”级别的规则，如果有则直接阻断执行；如果没有违反“必须改进”级别的规则，再判断是否违反了“需要审批”级别的规则，如果有则将 SQL 转到数据库变更工单；如果都没有违反上述任意一条规则就直接执行。

用户对建表语句做了有限修改后点击执行，结果如下：

由于 SQL 违反的规则中包含“必须改进”级别的规则，ODC 将会拦截 SQL 的执行。用户继续修改，将所有“必须改进”级别的规则都改掉了，还剩下若干“需要审批”级别的规则：

由于 SQL 违反的规则中包含“需要审批”级别，因此 ODC 依然会阻断该 SQL 的执行，用户可以点击“发起审批”来创建一个数据库变更工单，让具体的审批人决定该 SQL 是否可以执行。

最后，如果用户将所有的问题都改进，没有违反任意一条规则或只违反了“无需改进”的规则，那么 SQL 就可以通过窗口直接执行：

用户如果想要在表中插入几条测试数据并查询，可以直接在窗口中输入 SQL：

虽然 INSERT 语句以及 SELECT 语句都命中了规则，但是由于这些规则都是“无需改进”级别的，因此并不影响执行。在“生产环境”中，管理员可以将这两条规则的风险等级改为“必须改进”，用户如果执行的 SQL 命中了这两条规则就会被 ODC 阻断执行：

保护数据库变更

在开发环境中，用户大多是通过 SQL 窗口进行数据库操作的，但在生产环境中，基于更加严格的管控策略，与数据库的交互大多通过数据库变更来进行，SQL 检查的结果在以下两个场景中发挥作用：

第一个场景：是否能发起数据库变更：如果待变更的 SQL 违反了“必须改进”级别的规则，数据库变更工单将无法发起。

第二个场景：数据库变更匹配到哪一个审批流：ODC 将根据 SQL 检查的结果计算变更的风险，并以此匹配审批路径。

管理员依然采用“约束数据库开发”一节中的规则约束所有的待变更 SQL：

1. 建表语句必须有表级注释，以更好地帮助开发人员理解每张表的业务意义，减少沟通成本。
2. 建表语句中必须含有主键，且主键列只能使用 bigint 类型，必须使用自动自增属性，自增初始值不要求。
3. 建表语句中每个列都建议有注释，如果想创建出不包含有列注释的表需要审批。
4. 建表语句中声明为 NOT NULL 的列必须有默认值，避免上层业务系统没有传值导致的数据库写入异常。

用户使用如下 SQL 发起数据库变更工单：

由于该 SQL 违反的 SQL 检查规则中存在若干违反“必须改进”级别的，因此工单发起失败，当我们点击该工单详细信息的时候可以看到违反规则的具体情况：

在“安全规范 -> 环境 -> 风险等级”中可以根据多种因素共同定义风险等级，并以此作为匹配一个审批流的依据：

用户将所有“必须改进”级别的规则全部修复后，就可以正常发起工单了。由于待变更的 SQL 命中了“需要审批”级别的规则，因此其风险等级将会被判定为“高风险”，受到项目管理员以及项目 DBA 共同的审批：

总结&展望

SQL 检查是降低数据库变更风险和提升数据库开发效率的重要工具。目前 ODC 集成了数十条规则，这些规则来源于一些通用的，有广泛共识的数据库开发经验。SQL 检查功能最大的价值在于将这些经验程序语言化了，避免了认知上的差异以及口口相传过程中带来的误差，同时，将这些经验不仅仅作为一种辅助性质的咨询，还成为日常开发的重要卡点，这对于提升数据库稳定性的帮助是巨大的。

在未来，我们会增加更多的检查规则，共同构成在不同数据源上的最佳实践，为数据库开发和变更提供专业的建议和全方位的保护。探索 OceanBase开发者中心（ODC），获取源码及更多详情，请访问我们的GitHub仓库：https://github.com/oceanbase/odc。加入我们，共同推动技术创新！