SQL治理高阶实践：异常防御体系建设与应用挖掘

作者：蔡朋 2023-06-05 07:24:46数据库 其他数据库 在开发阶段，理高研发通常不受相应开发规范和SQL审核约束。阶实践异掘从开发到测试或生产发布时，常防才会进行DDL和DML的御体用挖审核。目前业内SQL治理，系建主要还是理高在SQL出问题之后进行相应的治理。

一、阶实践异掘防微杜渐：异常SQL防御体系建设

1.SQL治理阶段

如上图所示，常防SQL治理的御体用挖基本阶段主要包括开发（事前）、测试（事中）、系建生产运维（事后）三阶段。理高

在开发阶段，阶实践异掘研发通常不受相应开发规范和SQL审核约束。常防从开发到测试或生产发布时，御体用挖才会进行DDL和DML的系建审核。目前业内SQL治理，主要还是在SQL出问题之后进行相应的治理。

所以我们思考：能否在测试阶段提前发现有问题的SQL，提前预判性能并治理？如何在事中进行SQL的兜底和止损？

之所以要把治理能力前置到测试阶段，是因为越早发现有问题的SQL，对整体治理或改造的成本就越低，对生产的影响也越小。

2.事前发现

1）SQLReview

SQLReview是在开发环境向测试环境或生产环境发布时，对语句进行基本审核。此部分的整体能力建设与当前业界主流的开源没有太大差别，只是我们的集成规范会更个性化或更丰富。

第一，根据DBA在日常中的反馈，完善相应规则并集成经验，如拦截特殊语法；

第二，集成三方规范。比如大数据包含某些特殊要求，要求每一张表必须有时间字段等特定字段、特定类型和特定索引，以供大数据抽取数据；

第三，广播变更消息，比如提供安全审计，变更大数据订阅结构变更；

第四，日志分析，分析热点表。DBA在日常运维时，需要一定数据统计来总结经验，梳理重点关注的业务，了解拦截最多的规则和问题最多的部门。

2）新增SQL检测

新增SQL检测是指在SQL执行到生产环节之前，将它拦截。实现流程中最重要的一点是，通过数据库代理中间件记录全量测试及生产的全量SQL、然后统一进行消费处理，识别出生产环境新增的SQL。

例如通过指纹计算，对比测试环境下SQL的指纹是否出现在生产最后的指纹库里。若不存在的话，就把它认定为一个新增的SQL，再放到生产环境中进行特征判断，比如它的扫描行数是否太多，是否存在全表扫描，索引特征是否出现index merge、file sort等，也包含执行时长，特殊禁止语法等全方位的特征判断。

但这种实现存在两个问题：

一是据统计，生产环境峰值QPS每秒上百万。由于生产的真实流量比较大，改进后，我们不再转发全量SQL，而是根据SQL指纹进行采样避免流量太大，但即便如此，每天处理的SQL量仍接近5TB。

二是指纹计算。上述SQL通过DBA直观去看，指纹计算应该是一致的。但由于早期我们采用开源的基于正则的SQL指纹计算库存在的不足，无法识别SQL在细微上的差异，导致指纹计算准确度差影结果判断。

改进后的指纹算法则采用语法解析树的方式，将SQL的关键对象抽取出来做特征处理，比如取出查询字段后做排序后再计算指纹，对where条件同样做排序处理，防止sharding表由于表名字不同，造成的计算差异同样进行特殊处理。

3）统计分析

我们统计了最近一个月的拦截量。在TP场景下，SQL问题大部分是索引问题，由上图可知，“索引不合理”和“缺少索引”的情况占比之和达到80%。因此能否通过技术手段进行自动合理索引创建就是解决问题的重点。

4）研发视角新增SQL质量报告

通过上图视角，研发可以了解每个DBA或每个部门，在具体时间范围内新增哪些 SQL，发到生产的新增SQL是否高危SQL，并清晰地记录下来。

质量报告还包含其他重要信息，比如 SQL被认定为高风险的原因。如上图所示，质量报告提示可能存在全表扫描的情况，然后记录其首次出现的时长和时间，使用开源索引工具给出基本建议。

但初期的建设还不太完善，开源工具仅基于单条SQL提出建议，缺少评估意见合理性的全局视角，我们后续会整体改写这部分。

5）不带隐患上生产

实现拦截功能需要与整个研发流程结合。

在CICD环节的准出阶段进行卡口集成，若出现高风险的慢查询或SQL没有处理的情况，会提示“不可上线”，避免隐患SQL上生产。

3.事中兜底

无论防御做得多好，随着数据库容量、QPS的增长，一些SQL会不可避免地逐步恶化为慢SQL，因此要具备兜底能力。

第一，通过中间件进行主动或被动的SQL限流或熔断，比如DBA会主动介入对某个SQL限流或者熔断；

第二，自研数据库管控平台。平台集成数据库自愈系统，通过查杀模块，实时检测每一个数据库实例的健康状况，并根据特征进行相应的SQL查杀等。

我们整体构建在混合云上，包含阿里云、华为云，AWS，Azure。每一家云对于数据库的保护机制存在很大差别且是不可以跨云移植适用的，所以要打造自己统一的、通用的保护能力。

4.事后治理

事后治理主要是慢查询治理。由于混合云上要兼容产品比较多通过云商接口，拉文件的原始分析方式实现比较麻烦，具备全量SQL的能力后实现就较为简洁，后续接入多家云或兼容MySQL协议的产品时，能更好实现慢查询分析、分析及安全审计等能力。

使用单一云较为简单，而在混合云上时，为了某一能力的统一化或标准化，就不得不把管控系统设计得很复杂。

5.后续计划

前文提到，我们统计80%的SQL问题是索引问题。如上图统计，生产环境中单列索引占比77%，复合索引只占了13%，同时复合查询条件占比91%。由此可以看出，用23%的复合索引服务91%的复合查询显然是不够的，意味着可能是存在很多SQL执行计划不佳的情况。

过去的开源建设是针对单个索引、单个SQL的最佳推荐，但具备了全量SQL采集分析能力后，可以做全局性最佳索引的推荐。在数据统计维度，可以基于代价进行评估给出整张表综合索引的建议，进而自动创建和维护索引。

二、深度观测：全量SQL分析与挖掘

1.为什么要做全量SQL分析？

1）应用场景：问题分析定位

全量SQL的应用场景比较多。如上图所示，例如数据库CPU很高或抖了一下是哪些SQL导致的，这些SQL的具体执行情况，包括时间响应、返回行数、扫描行数等。

2）SQL挖掘：深度治理

基于全量SQL分析表、索引是否已废弃，不同db的热点表、热点SQL，单条SQL RT是否稳定，甚至可以分析表的活跃数据情况等治理场景。

3）兼容混合云产品、统一问题排查

由于混合云产品的差异性与企业统一管理的矛盾，给问题分析或日常应用带来很大困扰，因此产品设计时要格外考虑多云兼容性。

有时候，单一云确实提升了某一能力，但混合云下，服务整体功能的设计更加复杂。

2.全量SQL分析实现

全量SQL分析实现的基本流程：SQL请求DAL之后，DAL将SQL转发到Kafka里面，然后再根据业务场景需要进行消费处理。

比如分析某一个字段是否有在用，只需要通过指纹去重，抓取这一段时间内所有这个表的SQL请求，并进行参数解析，就能轻易分析出所需要的字段。

还比如热点表、热点SQL、SQL波动，经过先前处理后，可直接通过原数据查询。

通过对一段时间内的SQL查询返回数据记录，分析出活跃数据量占比，来指导研发合理的规定设置。

上图是做采样的一个样例，它的维度很丰富，我们可以基于SQL的采样，进行大量统计分析的工作。

通过top SQL可以分析某个时间段内SQL执行占比情况，进而推测数据库性能开销情况，对深入问题分析有比较大的帮助作用。

上图是第二个应用场景，通过波动SQL，查询不稳定集群。预处理每条SQL时，我们记录了SQL RT 的p50跟p95时长，把每一个集群下每一条SQL的p95跟p50去做差，然后聚合、排序。波动越大，聚合的差值越大，就大致能推测这个集群是不稳定的。

从上图左下方的图表可知，它的CPU经常具有毛刺。但日常中DBA很难根据经验照顾到每一个集群，所以需要拉取这些数据进行分析或日常治理。

再如DBA发现某个DB TOP 1的SQL执行的次数几乎是TOP 2的10倍，分析这个SQL发现它是一个司机登录场景。由于活跃的司机体量是有限的，司机登录动作达到每秒几万，这显然是不合理的。

与研发沟通后，我们找到了原因：这是典型的业务设计问题，也是一个基础编码问题。

三、容量预测：数据库仿真流量测压

1.数据库容量评估

基于云上技术的红利，从存储计算一体化的架构演进为存储计算分离的架构，具备了快速容量弹性的能力。未来目标是做到ServerLess化，但目前仍需要一些时间和数据进行验证。

虽然实现了存算分离实现快速扩缩容，但是容量评估仍旧是根据DBA经验来判断的，缺乏一些相应的数据支撑。

2.数据库容量压测

1）BenchMark

由于BenchMark压测与真实环境的SQL表现差距巨大，因此不能用于容量评估。

近年来流行的SQLReplay等流量回放工具，核心就是利用抓包的方式将SQL记录下来然后机械能回放。它主要的问题是：一方面抓包容易缺漏，另一方面是SQL维度信息缺失难以支持还原真实场景。

2）全链路压测

现在比较流行的方式是全链路压测。它存在的问题是，在真实的业务场景下，APP ID之间的调用关系极其复杂。理论上使用全链路压测的方式可以进行压测评估，但实际应用中数据上下游调用可能会造失真的问题，会存在压不到、压得过多或过少的情况。

同时，也存在数据热点问题。全链路压测有时会模拟一定的用户、司机数据，通常这个量不会特别大，几百条、上千条就已经算是比较多的。由于数据库有cache，很多查询不回表，所以无法反映真实的数据库的实际负载情况。

3）仿真流量压测

在具备了全量SQL后，我们提出了仿真流量压测。它的大概流程是：高峰期内，SQL将流量录制下来保存至Kafka低峰期内，进行相应的消息处理后进行仿真回放。

主要问题：

压测幂：比如压测1000次理论上这1000次在任意时间点执行SQL的内容、数量以及并发度都要求保持一致，这是非常困难的；

流量缩放：如果是1:1的回放，理论上是可以还原的。但研发可能对放量百分比产生疑问，比如流量增加120%，数据库能否撑得住？这种时候只能通过经验预估容量。成倍放大是容易的，但成比例放大就很麻烦。

主要缺点：

• 使用真实生产环境进行回访，不能执行DML导致失真；
• 不能保证100%的仿真度，只能无限接近。

3.数据库容量评估应用

实际进行压缩时，我们划出一条以CPU为主的基准线，安全的上限值是45%。超过45%之后，无论数据库是否能承受都需要进行扩容。基于这个基线进行压测，CPU压到45%时当前QPS即是容量的上限水位，这就有利于研发和DBA后续直观地看到容量情况。

目前这一块内容并未完全落地，整体处于开发阶段，我们在理论上还原了SQL执行顺序与并发情况。整体并发模型还需要做深入的打磨跟优化。

4.为什么要围绕SQL死磕？

根据真实生产的统计，我们将近70%的数据库实际规格都低于8C。

使用这种小规格的服务器，数据库的弹性能力是非常差的，SQL稍有问题都可能击穿数据库。由于使用的是混合云，无法将数据库稳定性保障交给云商统一解决，因此我们只能在现有能力下，构建兼容多云的统一管理能力。

数据库最大的两个挑战：高并发+大容量。虽然目前货拉拉还没有面临大容量与高并发的场景，但已初显端倪，不到3年，我们数据库QPS流量增长了近10倍。

在可预见的未来，如果我们的订单再增加1倍，流量可能会增加10倍，现存的SQL问题到时将会更加突出，这也是DBA围绕SQL进行能力建设的原因之一。

5.后续规划

目前平台初步具备了DAS功能的雏形，距离比较完善的产品形态还要继续进行打磨。

责任编辑：武晓燕 来源： dbaplus社群 SQL治理防御体系

(责任编辑：休闲)