作者介绍

蒋健，云趣网络科技联合创始人，11g OCM，多年Oracle设计、管理及实施经验，精通数据库优化，Oracle CBO及并行原理。云趣鹰眼监控核心设计和开发者，资深Python Web开发者。

 

多行业SQL审核落地总结

 

近年来落地了数十个行业（包含银行、制造业、保险等）的SQL审核项目，在项目对接需求，直到后期验收，完成优化目标的过程中，有一些感悟和总结，本文做一个分享。

 

首先要明确一下SQL审核的对象范围是针对数据库层面的，涉及性能、安全风险的SQL，而非业务逻辑上的风险SQL（常见的如敏感信息的查询、删除、变更等）。

 

从应用场景上主要是4个核心的场景：生产环境优化具体业务，生产环境降低业务高峰期CPU/IO，预生产（或测试）环境拦截低效SQL，开发环境减少不合规SQL。

 

生产场景

 

 

优化具体业务

 

实施案例中以制造业为主，具体需求为对应的业务系统（OA、SAP、MES等）操作慢，优化验收目标也比较简单，实际业务操作变快达到验收目标即可。

 

这类优化大多比较简单，系统的问题基本为常见优化问题，且访问生产库，主机基本没限制，通过系统自身的优化建议报告，建索引后，收集统计信息后，也方便验证，项目进度快。

 

项目难点为完成业务操作与数据库中SQL的对应。通过业务穿特定参数，结合ASH历史进行模糊查询可完成定位。得到SQL语句后，带入绑定变量，统计运行消耗时间，与业务操作时间对比，确认出是否优化SQL能到达预期效果，再实施优化。

 

降低业务高峰期CPU/IO

 

该场景案例大多对应银行、保险行业，具体需求为降低整个系统的CPU/IO负载。这种场景难度相对较高（特别是CPU），通常有以下难点：

 

• 系统无明显TOPSQL，TOPONE SQL比例（按占DB TIME百分比计算）小于4%；

• TOPSQL逻辑复杂，存在大量复杂逻辑PLSQL；

• 目标数据库对应多个业务系统，数据库JOB，操作系统CRONTAB设置JOB多；

• 业务情况复杂，一周中每天TOP10 SQL都有巨大变化。

 

生产环境SQL审核基本流程

 

以下流程生产环境的两个主要场景都适用的：

 

• 确认优化目标（优化降低CPU/IO）确认优化时间段；

• 通过工具生成优化报告；

• 在测试环境运行相关SQL语句，收集逻辑读，运行时长等信息，实施优化报告中的建议，再次运行SQL，记录优化前后对比效果；

• 提交有效优化方案给开发确认，评估变更开发层面认为是否合理，(交付格式参考excel)；

• 开发评估通过后有UAT环境，可再上UAT环境测试；无UAT环境，可直接上生产；

• 优化上线后，记录主机，数据库相关指标，确认是否达到优化目标。

 

 

非生产场景

 

 

预生产环境拦截低效SQL

 

该场景的案例具体需求有两类：

 

• 分析SQL语句合规性；

• 发现存在性能瓶颈的且语义上需要改写的SQL。

 

语句合规性比较简单（通过静态规则如select *；where 后无实际过滤，连接条件；含有笛卡尔集等能直接识别），而存在性能瓶颈的且语义上需要改写的SQL则算是非生产环境的SQL审核的核心。

 

因为不能自动确认SQL语句执行频率，以及表上的数据量，数据分布可能与实际情况有较大出入，所以这个阶段主要是识别那些需要改写的来完成优化的SQL，毕竟这种SQL上线后要修复问题，难度较大。

 

测试环境SQL审核流程图：

 

• SQL审核测试在功能性测试完成后进行，审核数据库为功能性测试连接的数据库；

• 系统中生成审核报告，提交开发评估修改；

• 开发批量修改完成后，再次生成审核报告，重复以上流程，直至无严重级别规则命中。

 

 

开发环境减少不合规SQL

 

该场景主要在大型企业中遇到，实施以培训为主，配合开发规范文档及静态审核（合规性）。强制实施后，对开发源头的烂SQL有较好的控制，极大减轻了测试后需要大面积返工的风险。

 

开发环境SQL审核流程：

 

• 开发人员抽取开发功能中的SQL语句；

• 提交SQL文本生成静态审核报告；

• 如静态审核报告中显示有问题，开发修改SQL文本后，再次生成静态审核报告，重复以上流程，直至无严重级别规则命中。

 

 

SQL审核痛点

 

 

海量的审核结果

 

在最早期版本的SQL审核中，SQL审核出来的报告常常是列出了海量的问题SQL，即便是增加了规则优先级别后，依然因为找出的问题SQL过多，而难以实施。

 

在一次次的功能调整，理顺流程中，我终于明白SQL审核的目标是发现并解决问题，而不是带来更多的问题。如果通过审核找出了海量的问题SQL语句、表、索引等，以至于开发及DBA无法完全修复找出的全部问题，很可能在实施人员眼里有工具不如没工具，最终工具跟流程还是脱节，推行不下去。

 

所以在找出问题这个层面，其实有个隐形的条件，即有多少时间留给开发？运维去确认及修复，转换成需求即需要动态的圈定问题对象的范围。

 

在SQL审核大部分的场景中，不论是在上线前的性能验收，还是日常的优化计划，单次SQL审核的目标基本可以归结为：找到一定量可修复的（甚至是有修复建议的）问题，修复问题，并能获取直观的对比效果。

 

在划分范围时，我们需要确定出命中高风险级别的规则的对象（SQL、表、索引等），此时生产场景跟非生产场景则有较大区别。生产场景更多是希望尽可能少的变更，达到预定的目标。非生产场景则是尽可能全面的识别出潜在高风险的对象。

 

不明显的Top SQL

 

在生产环境中审核SQL的常见的一个场景是OLTP类的应用没有使用绑定变量，此类场景通过按照执行计划聚合SQL，或是按照`FORCE_MATCHING_SIGNATURE` 聚合SQL可能取得一定的效果。

 

然而也有复杂些的场景，即使完成了相关的聚合后，依然找不到占比高的TOP SQL。换个角度来看问题，SQL审核大部分时候，我们审核的对象是SQL语句。这种视角在处理SQL语句变种多，有一定关联相似性的场景时，就比较乏力。

 

这种场景其实切换成对象视角，即抽出数据库中表的访问条件路径及访问条件，按照dbtime 占比排序，可大幅度聚合访问路径层面的优化需求，并实现自动化优化建议。

 

SQL审核实施人员能力要求高

 

初期的生产环境的SQL审核对实施人员的要求较高，需要实施人员深入理解SQL审核规则，并能灵活应用优化的技能才能完成SQL审核的全流程，这样甲方爸爸想培养人员自主掌握这套流程的实施就相对困难。在我们的实践中，对这个痛点也开了处方。

 

在谈处理思路前，我们先通过是否涉及到SQL的改写将问题分为两个大类：

 

• 不需要改写（数据库层面优化DBA主导）；

• 需要改写（SQL语义层面优化需开发配合）。

 

需要改写的相对较复杂，其实一般偏AP的系统更多是这种需求。不需要改写的SQL，其实在统计过优化手段后发现，占比最高的优化方式还是访问路径层面的优化，大白话就是建合适的索引。

 

而这种优化手段对于SAP、ERP、DRM、HIS等等偏TP的系统都有非常好的优化效果，大部分类似系统可能仅仅通过索引的优化就能达到客户的优化预期。

 

这部分，我们目前也已经通过自动化的优化建议降低了对实施人员能力的要求，而改写部分则依然依赖人工的参与，这种细分场景还在自动化攻关中。 

 

开发确认周期长

 

目前实施的审核项目中，一般涉及开发确认的步骤都较慢，有时项目周期大幅拖长就是由于频繁需要开发确认，比如有的实施人员习惯通过awr报告，再次确认SQL优化级；或是希望分步走，少量多次稳步上生产，最终导致项目的延期。这个需求也转换了我们对SQL审核的预期，也就是单次的SQL审核需要在实施前有个明确的收效预期。

 

总结

 

各行各业IT部门对SQL审核的需求日益旺盛，导致SQL审核细分场景较多，不同场景的关注重点差异也较大，自动优化建议配合人工测试/优化是我们目前落地的主要方式。通过审核的规则准确识别风险是项目的技术关键，我们的知识库也在项目落地中不断地校验更新，关于规则这块后期的文章中再做交流。