伴鱼数据库之 SQL 审核系统

作者： Hacker_ubN7WXjw 原文来源：https://tidb.net/blog/6582c91b

一、背景

随着伴鱼业务的快速发展，公司各产品线的业务不断丰富，日常的 SQL 上线也在不断增加。SQL 审核与执行，作为 DBA 每天工作中相当重要的一环，如何保证 SQL 语句的质量，对于系统的高效运行和长久稳定有着很大的影响。

本文在对开源 SQL 审核系统 (例如 Yearning、See 和 Archery 等) 进行调研，并结合 DBA 在 SQL 上线实践经验的基础上，设计了伴鱼 SQL 审核系统。相比其它 SQL 审核系统，新系统主要包括以下等核心功能：

1. 基于 TiDB Parse 做 sql 语法解析和检测，践行 SQL 规范

2. 基于公司组织架构做权限管理和流程审核

3. 基于审核规则列表的动态开关

4. 支持 SQL 执行数据备份和回滚

5. 支持任务定时调度执行

下面从整体架构、流程设计等方面详细介绍下伴鱼 SQL 审核系统以及设计背后的一些思考。

二、整体架构

SQL 审核架构，如下图所示，主要包括 web 前端、SQL 审核后端和数据存储 TiDB。

三、流程设计

SQL 语句的质量对于系统的稳定高效运行有很大影响，因此 SQL 审核平台必须加强语句质量的审核。其次，SQL 审核平台在确保数据库平稳运行的同时，尽量提高上线的效率。

规则设计

通过系统约束是践行数据库规范最有效的手段。SQL 审核规则除了加入业界认可的规则外，我们还根据日常 SQL 上线暴露的一些风险场景，加入我们设计的一些规则。SQL 审核部分规则列表，如下图所示。

数据的删改关系到数据安全和 SQL 性能，其中 SQL 性能关系到线上服务的稳定性。这里简单介绍下“删改数据规则”，主要包括以下三条规则：

1. 删改数据索引不完全匹配

2. 删改数据影响行数超过 100

3. 影响行数超过 3000

下面对这 3 条规则进行解释：

• 日常数据修改，大多数场景只涉及少部分数据修改，所以只要完全走索引，性能基本没问题。如果系统检测到语句条件与线上索引不完全匹配，检测结果就会不通过。

• 在某些特殊场景下，索引完全匹配，但数据影响的实际行数可能较多 (大于配置影响行数 100)，这样检测结果也是不通过。

• 当然在表数据量不大 (万级别以内) 和索引没法覆盖等极少数场景下，可以通过关闭 1、2 两条规则，同时满足 3 这条规则的前提下，检测通过。

规则这样配置，主要因为数据执行权限已经下放给业务负责人，系统尽可能保证 SQL 的执行性能。

任务设计

业务 app 大版本上线，涉及 SQL 上线条数众多，在任务设计上主要做了如下几点考虑：

1. 通常业务大版本上线，涉及多个业务线，所以一条 SQL 任务单必须支持多库多表的执行操作

2. SQL 任务通常包括建表、改表和增删改数据三种类型，每种任务需要区别对待，比如建表不会锁表，但需要关注表的索引；改表需要关注数据大小，任务最好不要在业务高峰期执行；增删改数据需要在执行前对数据进行备份，保证数据安全。

基于以上两点要求和任务提交的易用性，我们设计了任务检测页面，如下图所示。

其中，对于建表选项，我们要求每个输入框只输入一条建表语句，并备注每个表的查询和更新，这样设计的原因是符合 DBA 审核习惯，方便 DBA 审核索引好坏。

任务检测

研发提交 SQL 任务检测后，后台基于 SQL 审核规则，对语句进行语法和规则进行检查，并将检测结果反馈给研发。在任务检测结果页，从易用性角度做了如下几点考虑：

1. 提交检测的 SQL 众多，如果其中某些 SQL 不满足要求，需要支持在检测结果页直接修改并立即检测，不需要重新编辑所有任务再次提交

2. 改表 (除加索引操作外) 只修改元数据，不需要拷贝数据，影响行数为 0，形象的“代表”执行速度很快

3. 删改数据，我们需要将数据真正影响的行数展示给研发，让他们看到实际操作的数据条数，形象的“告诉”数据操作是否符合目标

检测结果页，如下图所示。

其中，修改数据条件与索引不匹配，检测状态为失败；增加索引，需要拷贝全表数据，影响行数为表总条数；增加字段和数据类型加大，只涉及修改元数据，影响行数为 0；第 4 条更新语句，满足删改规则，检测状态通过；整个任务单，有条语句未通过，只需要修改该条语句满足审核规则，整个任务单才才可以提交审核，进入下一步流程。

任务审核

任务审核角色有 2 个，一级审核为业务负责人，负责审核任务提交同学的 SQL 质量，二级审核为 DBA，进一步审核和提高 SQL 质量。审核流程，如下图所示。

目前，任务审核流程如下：

1. 对于增删改数据操作，审核规则已经保证 SQL 性能和数据备份，审核和执行权限下放给一级审核人

2. 对于建表，DBA 关注表的索引好坏问题，审核和执行权限由 DBA 负责

3. 对于改表，涉及添加索引操作，需要关注语句的性能，审核和执行权限由 DBA 负责

任务执行

任务执行阶段，主要考虑 2 个问题，包括大表添加索引可能导致的性能问题和数据删改可能导致的数据误操作问题。针对这 2 个问题，我们采取的措施如下：

1. 定时调度，大表加索引操作，可以设置调度时间，调度到业务低峰期执行

2. 数据备份，对于删改数据操作，在真正执行前，会根据语句条件，对数据进行备份

任务待执行列表，如下图所示。任务如果设置了定时调度，后台调度到该设置的时间点执行，当然待调度的任务也可以修改调度时间或者人工调度立即执行。

任务历史

任务历史主要保存 SQL 语句操作记录，便于审计。同时对于删改操作，任务历史提供数据回滚入口，如下图所示。

四、总结及规划

目前，伴鱼 SQL 审核系统简化了 DBA 的工作，提高了研发 SQL 上线效率和研发使用数据库的水平。系统已稳定运行近半年时间，审核规则也不断完善，更加契合公司内部场景。未来，我们有以下几点需要完善：

1. 建表，目前小表采用自增主键，大表主键依赖公司分布式 id 生成器，后续版本升级到 4.0，小表主键可以使用 TiDB 自带 auto_random 方式生成

2. 建表，索引的好坏，还需要 DBA 人工介入，在没有有效的方式阻止引入性能较差的 sql 到线上前，目前还不打算将执行权限下放给业务负责人