作者：药糖

在过去两年的工作中，我们团队曾负责大淘宝技术的慢 SQL 治理工作，作为横向的数据安全治理平台，如何快速准确地发现部门内所有应用的慢 SQL，并进行高效的推动治理，同时覆盖多个开发、生产环境，是一个很大的挑战。以下是一些经验分享，我们通过持续的慢 SQL 推动治理，有效降低了 DB 相关的线上问题，极大提高了系统稳定性。

一、关于慢 SQL 集团对于慢 SQL 的定义，执行超过 1s 的 SQL 为慢 SQL。

慢 SQL 由于执行时间长，会导致：系统的响应时间延迟，影响用户体验资源占用增加，增高了系统的负载，其他请求响应时间也可能会收到影响。慢 SQL 占用数据库连接的时间长,如果有大量慢 SQL 查询同时执行，可能会导致数据库连接池的连接被全部占用，导致数据连接池打满、缓冲区溢出等问题，使数据库无法响应其他请求。还有可能造成锁竞争增加、数据不一致等问题

由此可见，需要及时发现和优化慢 SQL，对保障系统稳定性是非常重要的。

二、慢 SQL 是如何引入的

产生慢 SQL 的原因可能有很多，以下是一些常见的原因：缺乏索引/索引未生效，导致数据库全表扫描，会产生大量的 IO 消耗，产生慢 SQL。单表数据量太大，会导致加索引的效果不够明显。SQL 语句书写不当，例如 join 或者子查询过多、in 元素过多、limit 深分页问题、order by 导致文件排序、group by 使用临时表等。数据库在刷“脏页”，redo log 写满了，导致所有系统更新被堵住，无法写入了。执行 SQL 的时候，遇到表锁或者行锁，只能等待锁被释放，导致了慢 SQL。

三、如何发现慢 SQL 及高危 SQL

3.1 发现慢 SQL

执行时常超过 1s 的为真实存在的慢 SQL，阿里集团内部提供了很多可以方便获取应用慢 SQL 的方法，例如通过 TDDL 连接数据库，TDDL 会将慢 SQL 日志统一记录到机器的 tddl-slow.log 文件中。集团内部的数据库服务中心也会提供相关的慢 SQL 数据查询记录以及接口。

对于外部用户，可以在数据库配置中启用慢查询日志功能，数据库会将执行时间超过一定阈值的慢 SQL 语句记录到日志中，也可以方便地获取慢查询日志。或者使用其他的数据库性能监控工具、SQL 性能分析工具等。

3.2 发现全量 SQL

除了执行时长超过 1s 的慢 SQL 之外，我们还额外关注了未来可能劣化的慢 SQL，这样就需要获取全量 SQL，再对其进行分析，筛选出其中风险较大的 SQL。我们采取了如下方法：

基于 JVM Sandbox 进行 SQL 流水记录的采集关于 JVM Sandbox：「JVM-Sandbox 提供动态增强你所指定的类，获取你想要的参数和行信息；提供动态可插拔容器，管理基于 JVM-Sandbox 的模块。」

简单来说，JVM Sandbox 可以动态地将你要实现的代码模板打包编织到目标代码中，实现事件的监听、切入与代码增强。将目标代码的 Java 方法的调用分解为 BEFORE、RETURN 和 THROWS 三个环节，由此在三个环节上引申出对应环节的事件探测和流程控制机制。不仅如此，还有 Line 事件，可以完成代码行的记录。

// BEFORE-EVENTtry {    /*    * do something...    */
    //LINE-EVENT
    a(); 
    // RETURN-EVENT    return;
} catch (Throwable cause) {    // THROWS-EVENT}

jvm-sandbox-repeater 是 JVM-sandbox 生态体系下的重要模块，具备了 JVM-Sandbox 所有特点， 封装请求录制/回放基础协议，也提供了通用可拓展的丰富 API。

repeator 模块可以无侵入式地录制 HTTP/Java/Dubbo 入参/返回值，业务系统无感知。基于这个能力，我们可以方便的采集和 SQL 执行相关的 Java 方法参数以及返回值。通过配置采集点，来采集执行 sql 的 java 代码的相关方法、参数和返回值，辅助实现 sql 采集功能。jvm-sandbox-repeater 地址：https://github.com/alibaba/jvm-sandbox-repeater?spm=ata.21736010.0.0.3e5975362i3rJi

确认采集点根据对 MyBatis 源码分析，我们确认了如下采集点：

JVMSandbox 完成数据采集后，通过发送 metaq 消息的方式，与系统进行对话。对于不同种类的采集消息，我们通过不同的字段加以匹配，最终可以获得每一条 SQL 流水对应的 SQL 文本、执行时长、sql 参数、db 名称、ip 端口、sql_mapper 资源文件等全部信息，具体如图所示：

以上可以采集到应用的全部 SQL，量级是很大的。我们采用了 Blink 创建时间窗口，进行数据聚合，实时数据处理，减少回流的在线数据量，在此就不展开说明了。

3.3 如何识别高危 SQL

根据历史慢 SQL 治理经验，我们把高危 SQL 分为以下几类：

1. 不符合集团 SQL 规约的 SQL，可能会埋坑，造成线上问题，影响执行效率等。

2. 通过对 SQL 语句分析，发现 SQL 索引使用不当、造成全表扫描，或者 SQL 扫描行数过多、出现文件排序等。这种 SQL 即使当前不是慢 SQL，随着表数据量的膨胀，未来也可能发展为慢 SQL。

3. SQL 执行时间过长，比较容易理解。对慢 SQL 来说，执行时间越长，风险越高。

SQL 规约集团重点强制 SQL 规约如下：

• 【强制】不要使用 count(列名)或 count(常量)来替代 count()，count()就是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

• 【强制】count(distinct col) 计算该列除 NULL 之外的不重复数量。注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

• 【强制】当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题。

• 【强制】使用 ISNULL()来判断是否为 NULL 值。

• 【强制】对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。

• 【强制】在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

• 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

• 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

• 【强制】IDB 数据订正（特别是删除或修改记录操作）时，要先 select，避免出现误删除，确认无误才能提交执行。

我们使用了 Druid SQL Parser 进行 SQL 解析，Druid SQL Parser 是阿里巴巴的开源项目，可以将 SQL 语句解析为语法树，可以解析 SQL 的各个部分，如 SELECT 语句、FROM 语、WHERE 语句等，并且可以方便获取 SQL 语句的结构信息，如表名、列名、操作符等。通过分析 SQL，可以轻松判断 SQL 是否符合规约。

SQL 索引 SQL explain 语句可以提供关于 SQL 查询执行的详细信息和执行计划，并且可以了解 sql 的索引使用情况以及数据访问方式。通过使用 Explain 语句，可以了解 SQL 是如何执行的，并且可以看出其可能存在的性能问题。

一个常见的返回结果示例如下：

返回结果解析：

我们重点关注的点如下：

1. 使用全表扫描，性能最差，即 type="ALL"

2. 扫描行数过多，即 rows>阈值

3. 查询时使用了排序操作，也比较耗时，即 Extra 包含"Using filesort"

4. 索引类型为 index，代表全盘扫描了索引的数据，Extra 信息为 Using where，代表要搜索的列没有被索引覆盖，需要回表，性能较差。

以上几点都可能造成 SQL 性能的劣化，是我们需要额外关注的高风险 sql。

四、如何推动治理慢 SQL

作为横向的数据安全治理团队，为了对大淘宝技术部门进行慢 SQL 治理，我们建立了统一的问题发现-追踪-治理机制。慢 SQL 治理中涵盖了生产环境、开发环境的慢 SQL，区别在于：生产环境中为已经上线的存量慢 SQL，开发环境中为新引入的慢 SQL，对开发环境引入的慢 SQL，修复代价要小于生产环境。接下来分别介绍。

4.1 存量慢 SQL 治理

存量慢 SQL 治理的难点在于，历史遗留下的慢 SQL 可能量级很大，所以要区分慢 SQL 治理的优先级。我们制定了健康分机制，对 SQL 分批分级治理。

对慢 SQL 来说，健康分主要受 SQL 的执行次数、扫描行数、执行时长影响。另外根据应用中包含慢 SQL 的数量、平均 SQL 执行数据等，给应用打出健康分。再根据部门维度汇总，根据应用等级、应用健康分情况等，计算出部门维度的健康分。

整体流程大致如下：

在慢 SQL 推动治理方面，高危慢 SQL，会建立 Issue 持续追踪，Issue 存在超期时间，超期后会影响团队健康分。另外，提供应用维度、部门维度的整体慢 SQL 风险大盘以及排名，针对重点业务、慢 SQL 高发团队等，进行集中的推进治理。

4.2 增量慢 SQL 治理

我们希望增量慢 SQL 能在上线前得到解决，即分支内不要引入慢 SQL 或者风险 SQL，所以结合 3.2 和 3.3，我们建立了开发环境下增量慢 SQL 发现机制，并建立发布前卡点能力。整体流程如下：

增量慢 SQL 的修复代价是小于存量慢 SQL 的，因此这里我们添加了分支定位的能力。同一应用存在多个同学共同开发的情况，有效的分支定位，可以准确指派慢 SQL 引入人，实现快速推动治理。这里以 git 上代码改动为切入点，完成了引入慢 SQL 的 sql_map 与修改人之间的关系映射，大致逻辑如下：

a. 监听应用部署消息

b. 获取应用信息，拿到 git 地址

c. 将本次部署分支与 master 分支做分支 diff

d. 解析 sql_map 文件，获取本次修改的 sql 内容

e. 记录被修改 sql_id 与分支的对应关系

f. 根据 sql_id 查询对应分支……

这样就可以精准匹配到增量 SQL 的引入分支，从而指派到开发者，实现了定向问题指派和追踪，并且可以方便完成分支发布前的管控能力。如果存在增量慢 SQL，分支发布，合并到 master 之前，会触发卡点，需要问题解决才能发布。

4.3 安全生产环境慢 SQL 治理

安全生产环境(SPE 环境)，是集团层面为保障线上稳定性的灰度流量生产环境，安全生产环境执行过的慢 SQL，在线上流量放大后，可能会对 DB 造成过大的压力。我们额外新增了安全生产环境慢 SQL 的管控，作为开发环境下 SQL 被引入到线上的最后一道防线。

整体方案与上面慢 SQL 治理方案类似，在此就不赘述了。

五、总结

慢 SQL 可能引起很严重的系统性能问题，影响系统可用性和稳定性，因此，及时发现和治理慢 SQL 是十分重要的。我们建了一套完整的慢 SQL 发现-分析-推动治理的机制，极大减少了由慢 SQL 引发的系统问题。同时，在 db 稳定性上，我们还额外关注数据库 CPU 使用情况、活跃会话数情况等，建立及时的风险预警和快恢机制，第一时间解决数据库风险。