中原银行 SQL 治理实践

一、项目背景

作为对科技系统能否稳定运行最为敏感的金融行业，SQL 质量的高低对全行业务连续性起着至关重要的作用。而 SQL 治理，正是通过一系列工具和手段，发现和收集低质量风险 SQL，将存在性能问题的 SQL 语句拦截在进入真实业务场景之前，来实现对 SQL 质量的审核和管控，进而达到提升和保证 SQL 质量的效果，以保障生产环境科技系统的稳定性和金融服务的连续性。

SQL 检核通过检查 SQL 语句内容、判断 SQL 问题，来甄别 SQL 质量，是 SQL 治理的重要手段。常见的 SQL 检核方法，是基于约定的 SQL 规范形成一系列的静态规则，由工具基于这些规则对 SQL 进行预审核，然后再由人工复审之后，才能在测试或生产数据库执行，但这种审核方法主要存在以下问题：

1.审核范围小：常见的检核方法只能针对单次的表结构变更 SQL 或开发人员主动提供的 SQL 进行审核，无法对应用真正运行时执行的大量 SQL 进行审核，而实际情况中影响生产系统稳定性的往往是运行时的 SQL。

2.审核能力有限：基于静态审核规则的方式难以判断 SQL 是否真的存在性能问题，通常需要结合生产环境的实际数据量及索引信息等才能判断出 SQL 是否存在性能问题。

3. 审核效率低下：SQL 经过系统预审核后，还需要 DBA 基于经验进行人工审核，不仅对人工处理依赖度较高，且审核效率较低。

二、痛点分析

基于以上这些痛点，中原银行自 2022 年初开始筹划自主研发建设一套 SQL 检核平台，将基于静态规则的 SQL 审核上升为对基于真实数据的动态 SQL 审核来解决这些问题，以实现真正切实有效的全流程自动化 SQL 审核，但却发现有多项难题摆在面前：

1.如何进行运行时 SQL 收集：应用运行时的 SQL 在系统运行态实时执行，数据量级大且难以获取，平台如何在不侵入业务系统的前提下，准确全面的获取应用所有执行过的 SQL 语句？

2. 如何识别 SQL 是否存在性能问题：测试环境数据量小，与真实生产环境数据不对等，在测试环境执行很快的 SQL，很可能到生产环境之后就会有性能问题，平台如何实现自动化识别 SQL 是否存在性能问题？

3. 如何进行常态化 SQL 治理：平台发现风险 SQL 后，如何推动 SQL 问题及时得到处理，如何对处理结果进行跟踪和管控，来实现常态化 SQL 治理？

中原银行技术平台团队通过一系列调研、实践，于 2022 年 4 月建成 SQL 检核平台，该平台不仅实现了大量的应用运行时 SQL 的自动化全量收集，更实现了基于生产数据的 SQL 性能问题准确分析，并建立了 SQL 质量门禁，真正实现了 SQL 全流程自动化检核和管控，有效帮助研发团队在测试环境提前识别风险 SQL，提升了全行应用系统的 SQL 质量。

下文就详细介绍中原银行 SQL 检核平台是如何解决这些难题的。

三、系统建设

3.1 运行时 SQL 全面收集

3.1.1 SQL 收集

为尽可能全面的覆盖所有应用运行时 SQL，中原银行 SQL 检核平台设计了 3 种运行时 SQL 收集方法：

• 数据库视图：部分数据库会在系统视图中保留近期执行的所有 SQL 语句，如 Oracle、达梦、OceanBase 等。SQL 检核平台支持配置应用系统数据库的地址和用户信息，自动收集应用实际执行过的 SQL 语句。

• 解析 ORM 框架：针对基于 MyBatis 等 ORM 框架执行 SQL 的应用系统，SQL 检核平台提供了 Jenkins 插件，在应用的 CI 阶段自动解析源码文件中的 SQL，拼接出应用可能执行的 SQL，并上传至 SQL 检核平台进行检核。

• 自定义 SQL：支持开发人员在指定源代码目录中上传 SQL，在 CI 阶段自动上传 SQL 至 SQL 检核平台实现检核。

3.1.2 SQL 预处理

平台收集到的初始 SQL 数据量级较大，且数据杂乱，若直接进行检核会对平台产生非常大的压力，同时也会对开发人员后续跟进修复 SQL 产生干扰。平台在收集到 SQL 后会执行以下预处理流程：

• SQL 过滤：从数据库系统视图中收集 SQL 时，数据库自身执行的 SQL 及开发人员通过数据库管理工具执行的 SQL 也会被收集到，而这些并非应用运行时的 SQL，平台会基于预先配置的过滤规则对 SQL 进行过滤操作。

• SQL 排重：从测试环境收集到的 SQL 量级非常大，其中大量的 SQL 只是参数不同。平台基于语法树解析的方式对 SQL 执行格式化操作，同时替换所有参数为“？”后得到 SQL 的指纹信息，指纹相同的 SQL 只保留一条，可有效减少对平台的检核压力。

3.2 SQL 性能问题精准识别

中原银行 SQL 检核平台通过创新实践，首创性的构建了一个类生产数据库，在该类生产数据库中为每个应用数据库分配一个数据库用户来模拟生产数据库，并实现自动同步应用生产数据库的统计信息到类生产数据库，通过从该类生产数据库中获取执行计划来识别 SQL 的性能风险，这种方法相对于常规直接在生产数据库获取执行计划的方式解决了如下两个问题：

• 安全问题：获取 SQL 执行计划的用户需要与执行 SQL 的用户有相同权限，会导致赋给 SQL 检核平台专用用户的权限太大，存在安全隐患。

• 部分 SQL 无法检核：当应用运行时 SQL 使用到尚未部署至生产环境的新增字段或表时，从生产环境获取 SQL 执行计划会失败，影响 SQL 检核覆盖度。

3.2.1 构建类生产数据库

平台为每个应用数据库分配了一个类生产数据库的用户，该类生产数据库用户下的表结构信息同步于测试环境的应用系统数据库，而统计信息同步于生产环境的应用系统数据库，通过在该类生产数据库中获取和分析 SQL 的执行计划来进行 SQL 审核。

为实现全流程的自动化审核能力，平台又实现了自动化同步测试环境应用系统数据库表结构到类生产数据库，同步生产环境应用系统数据库的统计信息到类生产数据库。若开发人员优化了测试环境的数据库索引，平台会及时同步至类生产数据库，并重新对存量的高风险 SQL 进行分析，将一些因索引问题引起的问题 SQL 风险降级。

而基于类生产数据库获取执行计划方式是否真正有效，关于此问题有以下几个方面考虑因素：

• 虽然对于数据库来说影响执行计划的因素有统计信息、数据库的负载、数据库实际存储数据等多方面因素，但是主要参考因素还是基于统计信息。

• 一些数据库如 Mysql 在生成执行计划时会对实际的索引数据进行抽样来计算预估行数，针对该问题可以通过参数配置强制 Mysql 基于统计信息生成执行计划。

• 大部分的风险 SQL 是由于缺少索引或者索引不合理导致的，对于这种 SQL 来说在类生产数据库和生产环境数据库的执行计划基本无差别。

• 平台并不要求执行计划是完全正确的，整体上来说在类生产数据库上获取的执行计划会比生产环境数据库的执行计划质量稍差一些，相当于说可能会存在误判，但是有问题的风险 SQL 都可以检核出来。

3.2.2 解析执行计划

获取到执行计划后，平台会解析执行计划并基于预先配置的检核规则对执行计划进行分析，部分规则如大表的全表扫描，列存在类型转换，笛卡尔积 join 等，根据命中规则的风险等级对 SQL 语句进行风险打分，从而判定 SQL 是否存在性能风险。

3.3 风险 SQL 常态化治理

通过上述方法平台实现了自动化 SQL 收集、检核的能力，当平台发现有性能风险的 SQL 时会及时通知系统的开发人员进行修复，且与我行的 DevOps 平台打通实现了质量门禁管控能力，当检测到应用存在未解决的风险 SQL 时，会阻止应用上线，从而避免了测试环境风险 SQL 进入生产环境，保证生产环境的稳定性。

此外，部分 SQL 随着生产环境数据量或表结构的变化可能会从无风险变成有风险，为避免这类 SQL 影响生产环境系统稳定性，平台会定期对收集到的 SQL 进行复审，若被判定为风险 SQL，会及时通知开发人员修复。

四、成效展示

4.1 平台的功能流程图

4.2 风险 SQL 列表

当 SQL 被识别为风险 SQL 后，开发人员可登录平台查看所属应用系统的所有风险 SQL，查看风险 SQL 详情并及时分析处理。

4.3 风险 SQL 详情

风险 SQL 详情页面展示 SQL 的详细信息，包括 SQL 文本、SQL 来源、近期所有执行计划、执行计划详情、违反规则等，可帮助开发人员快速定位风险 SQL 的问题根因。

4.4 SQL 治理报表

平台会定期向各部门负责人发送 SQL 治理报表数据，方便各部门负责人及时了解本部门的 SQL 治理现状，有助于督促各系统负责人及时处理风险 SQL，持续提升应用系统的 SQL 质量。

4.5 风险 SQL 豁免审批

部分风险 SQL 是用于每周或每日的定时跑批任务，执行频率低，对系统影响较小，针对该类风险 SQL 可经过部门审批后对该 SQL 执行豁免例外操作，即标记 SQL 为无需修复，避免影响应用的后续上线流程。

五、总结展望

中原银行通过完全自主研发建设 SQL 检核治理平台，实现了运行时 SQL 的全流程自动化收集、检核、审批及管理能力。平台于 2022 年 4 月初 MVP 版本上线，之后又经过不断的功能迭代与优化，目前已支持 Oracle、达梦数据库类型，已接入应用系统 100+，扫描风险 SQL 70W+，发现并修复问题 SQL 1W+。

平台不仅建立了运行时 SQL 的质量门禁，提高了 SQL 检核效率，降低了人工审核成本，而且提升了应用 SQL 质量，有效的支撑了中原银行研发效能的提升，已成为中原银行 SQL 检核治理的重要工具。

未来我们将持续优化平台能力，不断提升风险 SQL 的检核准确度和 SQL 治理效率，将陆续支持 OceanBase、Mysql 等更多数据库类型，增加基于 Java Agent 的 SQL 收集方式，提供智能化的索引优化建议等更多功能。同时也将探索与 AI 技术结合，以实现更优更快速的自动化 SQL 检核及优化建议能力。

本文转载自原银科技微信公众号

原文链接：中原银行SQL治理实践