1秒内审核3万条SQL：如何用规范识别与解决数据库风险？

# 一分钟精华速览 #

数据库是存储关键信息、支持业务运营和分析决策的基石。然而，数据库的复杂性——研发效率低、安全管控难、变更不稳定、数据管控难，和对其稳定性、安全性的极高要求同样使得它们成为企业 IT 结构中的一个脆弱环节。通过数据库规范的建设，NineData平台目前已支持100+规范，覆盖60种数据源，已审核800万 SQL，实现3万条 SQL 1秒审核完。详细的实践方法和细节，请参阅文章正文。

作者介绍

NineData技术总监——薛晓乐

TakinTalks 稳定性社区专家团成员，NineData 技术总监。前阿里云数据库技术专家，曾任分布式 SQL 查询引擎、高性能数据库驱动和数据管理产品基础架构负责人，服务全球 60 多万开发者。目前负责 NineData 数据库 DevOps 产品整体研发工作。

温馨提醒：本文约8000字，预计花费15分钟阅读。

后台回复 “交流” 进入读者交流群；回复“Q101”获取课件；

背景

不论是企业还是个人开发者，我们大多通过客户端或命令行工具来执行数据查询和管理任务，有时辅以云厂商工具产品来管理云数据库。然而，这些手段并不总是能满足我们多样化的开发要求，特别是在对接云数据库或一些新兴国产数据库产品方面存在限制。

数据库开发的规范化同样滞后于代码开发。缺少适当的标准和工具统一开发流程，导致 DBA 成了效率的瓶颈，常常需要亲自进行 SQL 审核和部署。

安全管理也是一大难题，特别是当开发人员普遍能够访问生产数据库时，权限控制变得棘手，数据泄露风险上升，而操作审计则不尽人意。同时，数据库的任何变动都可能带来不稳定性，DBA 或开发者在更新线上表结构时易引发故障，大数据量的修改操作也可能影响正常业务。若出现误操作，也常缺乏迅速的回滚和恢复能力。因此，在传统数据库开发中，我们面临着效率、安全和稳定性的多重挑战。

一、影响安全稳定性的根源是什么？

那么，究竟是什么核心因素影响着数据库安全稳定性？首先，我们通过 SQL 语句与数据库进行交互。这些 SQL 分为两大类：运维变更 SQL 和业务系统 SQL。运维变更 SQL 包括查询、结构变更、数据变更等，它们的编写与执行对数据库的运行至关重要。而业务系统 SQL，则是开发者在应用代码中嵌入的，主要是查询和DML，它们中的低效查询，俗称“烂 SQL”或“慢 SQL”，对数据库性能有直接影响。

1.1 图 – 影响数据库安全稳定的因素

尽管本文不会深入讨论，但技术架构的选择、硬件、网络和部署策略等因素也对保障数据库安全稳定发挥着至关重要的作用。下文将集中讨论运维变更 SQL 和业务系统 SQL，深入分析它们如何影响数据库的稳定与安全，并提出切实可行的策略来缓解这些影响，以维护数据库良好运行。

二、如何设定规范，识别并解决变更SQL和业务SQL的风险？

2.1 安全规范引擎

2.1.1 架构和流程

2.1.1 图 -NineData 安全规范引擎架构图

该平台配备了多种功能，例如使用 SQL 窗口来查询数据，使用 SQL 任务来执行数据变更和结构变更，以及常用的 SQL 审核、数据导入导出等能力。

安全规范引擎的核心融合了多种规范，包括查询规范、数据操纵语言（DML）规范、数据定义语言（DDL）规范，以及配置规范。同时我们还会进行一系列的检查，如语法和语义校验，权限审核，以及对影响行数和扫描行数的探测。

引擎的运行基于一套底层基础组件。这包括我们自研的高效 SQL 解析、数据脱敏引擎和内部权限管理框架，以及专为防止结构和数据变更引起的稳定性风险而设计的 OnlineDML 和 OnlineDDL 组件。此外，我们还开发了 SQL 优化和索引推荐引擎，进一步提升了安全规范引擎的能力。

2.1.1 图 -NineData 单条 SQL 审核流程

2.1.2 引擎和功能模块的交互

2.1.2 图 -引擎和功能模块的交互

SQL 语句作为引擎的输入主体，来源于多个不同的业务模块，包括 SQL 窗口、SQL 任务、慢 SQL 诊断，以及 SQL 审核。这些不同来源的 SQL 将通过我们的引擎进行审核，其中包括规范审核、索引推荐，以及性能分析等。

安全规范引擎本身具备插件化能力，能够灵活地对接上层功能模块、并整合各类审核检测能力，如语法检测、行数影响评估和权限检测等。它能够针对不同的业务场景和数据输入，动态生成相应的输出结果。例如，在 SQL 窗口的场景下，引擎会提供语法修正建议、影响行数的预测以及权限验证的反馈。而在慢 SQL 诊断场景中，由于 SQL 已在数据库中执行，语法问题不再是关注点，更多的是关注安全规范的建议、索引推荐以及性能优化。

因此，引擎根据输入源的不同，提供定制化的安全规范审核结果。这些结果也将通过多种方式呈现给用户，包括生成定期报告，页面展示，以及实时弹窗通知，确保用户能够及时了解并采取行动。

2.1.3 安全规范分类

安全规范大致分为三大类：结构类规范、查询与 DML 类规范、配置类规范。如下图所示。

2.1.3 图 – 内置 100+安全规范

2.1.4 规范的配置过程

安全规范引擎的设计不仅注重于规范的应用，也侧重于其与多样化数据源间的动态绑定能力，我们提供了两套默认的规范模板。举个例子，针对线上生产数据库，由于其数据的敏感性和业务的重要性，我们通常会实施更加严格的规范审查。而针对开发和测试环境的数据库，规范可以设置得相对宽松，从而优化开发流程的灵活性和效率。

2.1.4 图 – 支持与不同数据源的灵活绑定

进一步地，我们还能够对每个规范条目进行细致的配置。规范可以根据需求开启或关闭，并且可以针对特定的业务模块进行定制化的应用。例如，在下图中展示的规范，我们限定了 SELECT 语句在进行多表关联时的数量。这样一个具体的规则可以被设置为仅在应用代码的 SQL 审核中有效。

此外，我们也可以精确地控制规范在不同数据源上的适用性，并且可以针对规范内部的详细参数进行调整。例如，我们可以设定允许关联的最大表数量是3个，允许更灵活的5个，甚至更宽松的10个表。这种灵活性确保了规范能够在不损害业务灵活性的同时，保持数据处理的严谨性和效率。

2.1.4 图 – 规范本身可灵活配置

2.1.5 平台建设成果

安全规范引擎自发布以来已经支持了 100 多条规范，并且覆盖了 60 多种数据源，包括开源数据库、云数据库以及国产数据库。到目前为止，我们已经在线上执行了 800 万次 SQL 审核。我们也对安全规范引擎进行了性能优化，可在一秒钟内完成 3 万条 SQL 的审核。

2.1.5 图 – 安全规范引擎落地成果

2.2 运维查询类 SQL 的规范

在本节中，我们将深入探讨运维查询类 SQL 的规范化实践及其应用。

2.2.1 细粒度权限体系

场景：

当研发人员需要对线上问题进行排查，或运营人员需要进行数据分析时，他们往往依赖特定工具或平台来执行 SQL 查询。为了确保这些活动的合规与安全，我们构建了一个细粒度的权限体系，这个体系能够精确控制不同用户对不同数据对象的访问权限。

具体操作案例：

如图所示，某个用户可能只具有对生产数据源的只读权限，在 SQL 窗口模块中也仅限于只读操作。如果该用户尝试执行 UPDATE 语句，系统会提示权限不足，并要求用户具备相应的数据库操作权限。这种集中化权限控制确保了操作的合规性和安全性。

2.2.1 图 – 集中管控人、对象、操作

2.2.2 数据脱敏

场景：

在查询结果中可能会包含敏感数据。为了应对这个问题，我们的平台提供了数据脱敏功能。管理员、DBA 或安全人员可以为数据库中的敏感字段配置脱敏规则，包括应用不同的遮掩算法。

具体操作案例：

以手机号和地址信息为例，我们平台已经内置了默认的遮掩算法。在应用脱敏规则之前，用户能够在用户表中明文看到完整的手机号和地址信息。然而，一旦脱敏规则启动，并且如果用户未被授权访问这些敏感列，他们将只能看到被加密或者遮掩处理过的数据。

2.2.2 图 – 敏感数据脱敏展示

2.2.3 数据水印

场景：

在企业内部，即便我们提供了查询功能，仍难以避免一些情况，比如员工可能会截图查询结果并分享至其他地方，这构成了数据安全风险。为此，我们引入了数据水印功能。

具体操作案例：

当管理员激活这一功能，所有用户的操作或截图将包含隐性水印。这个水印可能包括当前登录用户的账号或姓名等信息，虽然可能不易一眼看出，但它对于防范数据安全问题及其发生后的溯源调查非常有效。

2.2.3 图 – 数据安全问题防范和溯源

2.2.4 审计日志

平台的每项操作都会记录在审计日志中，这是大多数工具产品应该具备的标准功能。详细记录了用户在特定模块的操作内容，并且这些审计日志将被长期保存，以供审计之用。

2.2.4 图 – 长期保存SQL操作记录

2.2.5 限制可执行的 SQL 类型

场景：

在 SQL 窗口中，为了避免可能对线上数据库造成影响的高风险操作，我们允许管理员设置可以执行的 SQL 类型。

具体操作案例：

例如，若某 SQL 类型如 UPDATE 被禁止，用户尝试在 SQL 窗口执行这类语句时将会被拦截。同样，我们也可以限制 DDL 操作，比如执行 DROP TABLE 或 ALTER TABLE 语句时，系统会提示该操作不被允许。用户需要创建一个 SQL 任务并经过审核后，才能执行这类 DDL 操作，从而确保限制高风险 SQL 操作。

2.2.5 图 – 通过系统限制高危操作

2.2.6 限制返回行数

场景：

基于对数据库性能和网络带宽的考量，同时也是为了预防数据过量泄露的安全风险。我们设立了“单次查询最大返回行数”的具体规则。

具体操作案例：

例如，若设置为 100 行，用户即便尝试检索 1000 行数据，系统也会根据内部规定仅返回前 100 行，并提示用户，由于超出了设定的返回行数上限，剩余数据无法显示。

2.2.6 图 – 避免返回数据过多，数据安全风险

2.2.7 限制单日查询总次数和总行数

场景：

尽管单次查询受到行数限制，但用户可能会尝试多次查询以获取更多数据。为应对这一潜在风险，我们进一步实施了对用户单日查询次数和总行数的限制。

具体操作案例：

举例来说，若用户在一天内的查询次数超出设定的阈值，系统会发出警示，提示用户已达到日查询上限。若用户需要进行更多查询，必须联系管理员以调整限额。这同样适用于查询行数，确保对数据访问进行全面控制。

2.2.7 图 – 对用户单日查询次数和总行数的限制

2.2.8 限制查询超时时间

场景：

不论是研发人员还是运营人员，都可能编写执行时间过长的 SQL 语句。为了防止慢 SQL 查询对线上数据库的稳定性造成威胁，我们在 NineData 平台上也实施了查询超时时间的控制功能。

具体操作案例：

我们给每个数据源设置最大查询超时时间限制，例如 5 秒。若用户执行的 SQL 语句需要 10 秒才能完成，一旦执行时间达到 5 秒，我们的平台将自动终止该查询，并返回超时错误，从而有效避免慢 SQL 对数据库稳定性的影响。

2.2.8 图 – 控制最大查询超时时间，预防慢 SQL 风险

2.3 结构变更类 SQL的规范

2.3.1 结构设计规范

我们对结构变更类规范进行了分类，包括表级别的规范、列级别的规范、索引的规范以及非表对象的规范。

2.3.1 图 – 结构设计规范的分类

2.3.2 执行时间窗口设置

场景：

经常会有研发人员在业务高峰期进行 DDL 变更，导致业务产生不可控的影响，那么执行时间窗口的设置就至关重要。

具体操作案例：

如图，我们设定了 DDL 操作的执行时间窗口。比如，我们通常禁止在早上 8 点至晚上这段高峰时间内进行任何 DDL 变更，但允许在凌晨 12 点至早上 8 点之间变更。

2.3.2 图1 – 设定 DDL 操作执行时间窗口

时间窗口规范设置好后，当提交任务时，如果选定了下午 4 点这样一个业务高峰期时段，系统将不允许提交。系统会提示该时间段是业务高峰期，并要求选择其他时间段执行 DDL 操作。如果选择了一个合适的时间点，比如凌晨 1 点执行，那么后台的调度系统会在指定时间执行该 DDL 操作。

2.3.2 图2 – 对可执行SQL任务的时间做规则限制

可能有人会担心，如果一个 SQL 任务在凌晨开始执行，但执行时间非常长，一直持续到了早上 9 点，即业务高峰期，它会不会继续执行？对此，我们的平台会有相应的限制措施。我们会定时检测当前的任务是否已经完成。如果到了定义的业务高峰时间窗口，而 SQL 任务仍在运行，我们会主动终止该任务，以避免在业务高峰期对业务造成不可控的影响。

2.3.2 图3 – 定时执行SQL任务

2.3.3 变更并发控制

场景：

为了保证数据库的稳定性和性能，我们实施了变更并发控制机制。这个机制的主要目的是防止多个研发同时对同一数据库执行 DDL 变更，因为如果在同一时刻数据库接受多个 DDL 变更，会对数据库造成巨大压力。

具体操作案例：

例如，如果配置规定同一时刻只能有一个 DDL 变更正在进行，那么当第一个 DDL 变更任务提交并开始执行后，它可以正常进行。但在此期间，如果有其他研发提交了针对同一数据源的 DDL 任务，那么该任务将会在尝试获取数据源资源锁时失败，因为系统检测到已有一个 DDL 任务在执行。新的任务必须等待，直到第一个任务完成后，才能开始执行。这样的并发控制机制确保了 DDL 任务可以串行化执行。

2.3.3 图 – 变更并发控制，避免多个DDL变更同时执行

2.3.4 NineData OnlineDDL

场景：

表结构的变更在数据库管理中是一个熟悉却又充满挑战的议题，DDL 操作常常会带来线上数据库稳定性风险。具体如下图所示。自研的 NineData OnlineDDL 可以实现图形化界面进行大表变更，不影响业务运行，无需设置触发器，不增加数据库负担，还能智能识别 MySQL 的在线变更能力。

2.3.4 图1 – 表结构变更的风险与挑战

具体操作案例：

例如，当检测到某 SQL 任务意图修改列类型，系统会识别出 MySQL 原生 OnlineDDL 功能无法处理，需借助 NineData OnlineDDL 实现“不锁表”操作。流程包括创建临时表，对其进行变更，然后将旧表数据逐步同步至新表，最终通过重命名完成整个 DDL 操作。此外，用户也可以选择强制使用 NineData OnlineDDL，我们也提供了临时表的清理策略，可手动或自动处理。

2.3.4 图2 – OnlineDDL执行方式

性能优化方面，我们一方面就会近部署避免网络延迟影响；另一方面，针对 gh-ost 单线程处理增量日志较慢的瓶颈，我们使用并发回放 binlog 事件和热点合并等技术来提升性能，实现增量回放速度提升 100 倍以上。OnlineDDL 过程中，用户可以动态调整参数和实时监控进度，包括切换表的超时时间、重试次数，以及全量策略，同时系统会根据数据库状态进行限流，减少对生产环境的影响。

2.3.4 图3 – OnlineDDL性能优化

2.4 数据变更类 SQL 的规范

针对 DML 操作如插入、更新、以及删除，必须遵循一定的标准。开发人员在提交数据变更 SQL 时，可能预计影响的数据条目较少，但实际执行可能由于 SQL 编写不当而导致大量数据被意外更新，从而造成数据故障。为防止此类情况，我们实施了以下数类规范措施。

2.4 图 – DML类规范

2.4.1 影响行数与扫描行数确认

在数据变更审批前，要求用户提供一个预估的影响行数。我们会检测实际扫描与影响的行数，并向用户反馈。若实际扫描行数与用户预估存在较大差异，我们将予以提示，以避免潜在的稳定性风险。

2.4.1 图 – 影响行数和扫描行数确认，避免误操作风险

2.4.2 变更备份与回滚策略

在执行变更前，我们会备份数据和结构。例如，在执行 Delete 操作之前，我们将生成相应的 INSERT SQL 语句以便备份被删除数据；在 Update 操作中，我们会备份变更前的数据。无论是修改表结构还是删除表，我们都会在变更前对表结构进行快照备份。这些措施构成了我们数据变更安全的重要防线。若变更出现问题，我们可以通过这些备份快速恢复结构或数据。

2.4.2 图 – 变更备份和回滚

2.4.3 NineData OnlineDML

场景：

除结构变更外，大表数据变更是另一关键场景。我们采用的 OnlineDML 技术，类似于 OnlineDDL，旨在确保变更不会对在线业务造成影响。虽然并不意味着完全无锁，但它确实减少了锁的粒度。OnlineDML 主要应用于清理大量历史数据或对全表数据进行订正的场景。为此，我们开发了 NineData OnlineDML，它可以将 SQL 任务拆分并基于主键分批执行，同时允许动态调整参数、查看执行进度。

具体操作案例：

例如，我们有一个名为 OnlineDML 的规范，它允许管理员配置风险检测参数。如果 DML 语句的扫描行数超过设定的阈值（如 2 万或 20 万行），系统将提示潜在风险。启用 OnlineDML 后，系统将自动分批执行 SQL，并允许用户配置 Sleep 时间，以减少对在线业务的影响。任务运行期间，我们可以监控已处理的记录数、当前进度百分比及预估剩余时间。

在提交任务，即安全审核阶段，我们将动态识别出 SQL 可能影响的数据行数。例如，如果一条 SQL 预计将影响 98 万行数据，超过管理员设定的阈值，且管理员已启用 OnlineDML，则当前 SQL 将通过我们的 OnlineDML 执行，以安全且无锁的方式实现数据变更。

2.4.3 图 – 大表数据变更-OnlineDML

2.5 业务系统 SQL的规范

2.5.1 端到端性能治理方案

接下来，聊一聊业务系统内嵌的 SQL，探讨如何对应用代码中的 SQL 进行安全和规范性审核。为此，我们设计了一套端到端的性能治理方案，它贯穿了开发、测试和生产的整个软件生命周期。

2.5.1 图1 – 覆盖开发、测试、生产的端到端SQL治理方案

在开发阶段，开发者能够将他们的 SQL 代码提交至系统进行自动化Review。在测试阶段，测试工程师或DBA进行人工 Review。代码部署至生产环境后，我们可以动态捕获慢查询 SQL 或者是全量的 SQL 语句，对它们进行诊断和优化，并提出具体的改进建议。整个流程大致如下。

2.5.1 图2 – 业务SQL审核流程

首先，用户可以将业务代码中的 SQL 以文本形式提交，或者指定数据源以便我们收集慢查询或全量 SQL。收集后的 SQL 将通过我们早先提到的安全规范引擎进行审计。对于慢查询或全量 SQL，我们会分析它们的执行指标，如响应时间、扫描行数、缓存利用率等，以此为基础进行性能分析和提出诊断建议。

智能审核完成后，我们还可加入一层人工审核，以防止不合理的代码上线。最终，我们会生成一份审核报告，包含对 SQL 的建议：哪些 SQL 写法需要改进，哪些建议进行优化，哪些是合理的，以及哪些 SQL 需要添加索引以提高执行效率。

2.5.2 慢日志采集治理，生产环境稳定性保障

在慢日志采集治理方面，支持多种类型的数据库，包括开源和商业数据库。我们可以进行定期或实时的慢日志采集。采集完成后，会对这些数据进行加工，如对 SQL 进行参数化处理、SQL 模板聚合，并在 60 多个指标的基础上进行诊断，这些指标涵盖了执行频率、持续时间、资源和 CPU 消耗等。根据 SQL 本身及这些指标，我们会提供索引推荐或 SQL 优化建议。

2.5.2 图 – 慢日志采集治理，生产环境稳定性保障

2.5.3 慢日志大盘：宏观到微观的诊断

我们的慢日志大盘提供了两个层级的视图：宏观聚合分析和微观趋势分析。

2.5.3 图1 – 慢日志大盘-宏观到微观的诊断

用户可以查看多个数据源的整体慢日志情况，包括顶级数据源和 MySQL 数据源概况。同时，也可以深入到单个数据源，跟踪其慢日志趋势。

2.5.3 图2 – 基于研发规范及性能的诊断优化能力

除了单条 SQL 的优化，我们还提供多条 SQL 的聚合优化服务。有时，多个查询可能针对同一表，且各自推荐不同的索引。如果这些索引存在重叠，我们会进行聚合处理，以生成最终的索引优化方案。这也是我们对多条 SQL 或局部 SQL 优化的一种方法。

三、如何兼顾数据库开发效率？

图1 – 规范与流程结合-数据源、环境、规范、审批流

在确保数据库的安全稳定性的同时，提高数据库开发效率是我们面临的另一项挑战。为了实现这个目标，除了需要依靠规则引擎，还需要流程引擎的强有力支持。流程引擎充当着变更管理的关键角色，确保每一次数据库的更改都经过了仔细的审查和授权。

具体到不同的数据库环境，生产数据库须遵守严格的开发规范和审批流程，而开发环境的数据库则可以实施较为灵活的规范和流程。这些规范和流程具备动态配置功能，可以根据不同的操作环境和需求进行调整。

如果提交的 SQL 代码完全遵循既定规范，审批流程可以相应简化。例如，这样的 SQL 任务可能只需主管审批，或在某些情况下，可以配置为免去人工审核，直接执行。

如果存在建议改进的 SQL，我们可能允许其提交，但执行前须经过主管和 DBA 的共同审查。

如果 SQL 代码存在严重问题而不宜提交，系统将禁止其提交，以避免潜在的风险。

管理员能够根据实际情形动态配置这些审查规则，实现安全规则引擎与审批流程的灵活绑定，确保只有合规的 SQL 能够被执行，既最大化了开发效率，又确保了线上系统的安全与稳定。

图2 – 规范与流程结合-移动审批提升办公效率

为了提升审批效率，我们引入了移动端审批功能，让管理过程变得更便利、效率更高。此外，我们还实施了一系列提升效率的措施，包括但不限于实现可视化查询管理、数据的快速导入导出、单点登录（SSO）集成，以及结合人工智能技术的 ChatDBA 服务。ChatDBA 是一个革新性的功能，为用户提供专业级的 DBA 咨询服务，无论遇到任何数据库相关问题，用户都能通过 ChatDBA 获得专家级的指导和解答。虽然篇幅所限，无法详尽介绍这些功能，但它们都是提升数据库开发效率不可或缺的重要组成部分。

四、客户最佳实践

在 NineData 产品推向市场的过程中，我们有幸服务于多家规模较大的客户和企业。在这里，我们将分享一个客户的最佳实践案例，以供各位参考。

案例背景：

这家公司在 AI 界享有盛名，拥有数百个数据库实例，这些数据库横跨阿里云、腾讯云以及远在海外的 AWS。他们的数据库布局覆盖了国内及海外多个区域，为全球范围内的客户提供智能服务。数据库方面，他们使用了一系列云数据库，包括 RDS、AWS 的 Aurora、MongoDB 和 DocumentDB 等。

业务诉求与挑战：

需求：经常进行表结构和数据的变更，对 Online 的 DDL和 DML 有强烈诉求。
痛点：
- 高 TPS（Transaction Per Second）表的稳定性问题。
- 在高 TPS 情况下，如何保证云数据库的 DDL 操作不受影响。
- OnlineDDL 期间，需要保证备库提供近乎一致性的读服务，避免主备延迟。
- 定期进行大数据量的历史数据清理与归档，同时不影响在线业务。