sql 时间比较_盒马慢SQL治理

阿里QA导读：很多时候我们会觉得可以用SQL替代冗余的JAVA代码，但是却同时引入新的数据库相关问题。就算通过增加索引和SQL写法优化，「慢SQL」依然是绕不过去的一道坎。今天我们来看看盒马产品技术团队的杨锴(花名：咏寻)过去是如何在该领域实践的。

1. 写在前面

---

「慢SQL」问题是一个一直困扰盒马技术团队的重要风险项，在历史上，频繁起因慢SQL导致的线上问题和故障，甚至在18年双11大促期间，发生过慢SQL问题导致的严重故障。

19年大促期间，我被任命为盒马的「慢SQL治理」的专项负责同学。大促期间，主要通过对线上已存在的慢SQL数据进行人工的风险分析，并邮件推动各域负责人关注风险，逐个跟进解决。整体大促平稳度过，唯一发生和数据库相关的是：分析性数据库有线上抖动的情况(复盘问题后，决定对分析型数据库的治理纳入到未来慢SQL治理规划中)。

进入日常态，专项工作继续展开，期间遇到了许多困难，通过和盒马各域接口同学、研发同学、技术支持同学、DBA同学以及慢SQL治理平台的小伙伴们通力合作，逐渐形成一套比较完整的治理防控策略和产品化能力，过程中的经验和思考与大家分享。

2. 治理思路

---

2.1 定义

首先问自己，我们要治理的对象是什么？到底什么是慢SQL?

对慢SQL的定义，目前集团共识是rt>1s的SQL，根据和DBA同学的沟通，1s这个时间是个经验值，根据历史经验和故障，当存在1s以上的SQL，当qps比较高(150QPS左右)时候，大概率会发生线上问题。(150QPS的估值，个人猜测是来源，在传统机械盘时代，最好的SAS盘，一个IO响应时间正常水平是10ms以内，IOPS可以做到150-200)。

但是有两个问题需要回答：

1. 超过1s的SQL就真的是问题吗？

2. 不到1s的SQL就真的不是问题吗？

两个问题答案都是否定的。

第1个问题：部分SQL的rt超过1s的原因，是当时数据库抖动，或者其他真正慢SQL影响到了数据库导致的。也就是说，rt>1是SQL的表现，而不是根因。要解决的是，实际导致数据性能降低的问题，此SQL很可能是不需要优化的。

第2个问题：某些不到1s的SQL，其实扫描行数是超过几十万行的，在业务初期，或者低QPS的情况下是不「慢」的，但是在大促期间，业务突增情况下，就成了一个真正的慢SQL，导致数据库不可用，发生线上重大问题。

所以，导致SQL的rt>1s的原因有很多，如此一刀切是因为需要一个「抓手」来推动这件事有效进行，采用了这个经验值，但是实际是：用结果去推倒原因，导致很可能是误报。为此，我们希望更有效的进行慢SQL治理，给出更精确的定义：

1. 慢SQL：SQL的执行时长直接影响DB性能，对于MySQL，执行时间超过1秒的SQL(集团统一定义)

2. 危险SQL：SQL在编写规范、索引覆盖、扫描行数 3方面存在间接危险，很容易成为慢SQL

3. 新增SQL：新迭代中代码改动引入的新SQL

4. 存量SQL：已经在线上运行的SQL

2.2 识别

1)新增SQL

集团的慢SQL治理平台(以后简称「树懒平台」)识别新增SQL的方法是，利用集团数据库底层数据，可以T+H的获取到线下和线上的所有SQL数据，通过对比线下的SQL是否是线上没有的判断是否是新增SQL。因为是通过数据清洗的方式，对应用接入是无风险，无影响的。 诊断案例： 如上图所示，主要包含了规范检查，索引检查，SQL的模板和样例以及修复建议。

2)存量SQL

存量的获取方式和新增类似，都是通过数据库底层数据的清洗。

由clouddba采集的rt>1s的SQL会根据用户配置的自定义维度发送工单到对应接口同学。

3)风险维度

风险的维度如上文所说，我们不止希望通过rt这一个维度来判断，还需要更多的维度来定义SQL的风险。

• 执行时间rt：仍然做一个重要维度，且阈值不一定是1s，可以配置

• 平均扫描行数：是重要的维度，扫描行数过高一般是能说明SQL是有优化空间

• 全表扫描：一般是因为没有配置索引导致的，是比较常见却经常发生的问题

• 平均返回行数：返回行数如果过多，对系统逻辑是有一定风险的，有可能导致溢出问题

• 执行次数：这是一个比较有争议的维度，日常执行少的SQL不一定表示是无风险的SQL，因为有大量的线上故障是在特殊场景下流量激增，导致一个日常执行次数不高的SQL成为拖垮系统的罪魁祸首。但是考虑到整体的风险考虑和治理的节奏，可以酌情配置此维度来逐步修复问题。

• SQL规约：类似代码规约，对SQL的写法进行诊断，每一个规约的背后都是线上故障的总结。

• 索引覆盖：代表当前SQL走的不是最优索引，是否采纳需要开发综合分析业务情况、SQL的TPS、以及调整索引对已有索引的影响等来决策。(由clouddba的底层能力提供)

4)OLAP

目前了解的集团的慢SQL治理主要是针对MySQL数据库，实际阿里数据库已经形成了完整的数据库产品体系。从分类上，包括了OLTP产品、OLAP在线分析产品、HTAP混合负载(OLTP+OLAP)产品、NoSQL产品、数据库生态工具。

在盒马，因为有大量的经营管理类的业务诉求，采用OLAP数据库进行业务分析，有部分核心看板的稳定性会影响到生产作业的效率，且在历史上，甚至大促期间出现过多起在线分析型数据问题导致的线上问题。

分析型数据库的慢SQL治理，目前了解集团没有团队在做专项治理，且对OLAP数据库的慢SQL的没有明确定义。因为分析型数据库的使用场景比较特殊，对实时性要求，介于生产数据库和离线数据库之间，且有大量数据查询。如果按照OLTP数据库的标准，几乎所有的查询都是慢SQL。

所以，根据实际线上运行的SQL和历史发生问题的特征，以及和ADB的同学沟通，确定扫描行占用内存大小和扫描行数是可以比较精确定义慢SQL的维度，最终阈值定为：占用2g内存 && 扫描行数1000w行。

2.3 投递

问题识别后，最主要的是准确找到问题处理人，这是治理的一个难点。难在SQL是没有和人精确的对应关系的。

下图大体描述了人和SQL上线之间的关系，由某个同学拉取了某个迭代，某个同学在此迭代上做了代码变更，在应用中产生SQL，调用数据库，开发者和代码模块是多对多的关系，应用和数据库之间也是多对多的关系，再由某个同学发布上线，以上各个角色，有可能是同一个人，也可能各是不同的人。我们清洗的原始数据，只有数据库和SQL模板之间的关系，如何找到这条SQL的处理同学是一个难题。

可追溯的人员角色有：

1. 代码的开发人

2. aone应用设置的owner

3. DB的owner

4. 迭代创建人

5. 迭代发布人。

各个角色特点：

1. 对于代码提交同学是最准确的，但是代码和SQL之间关系的匹配有一定难度。

2. 应用owner是一个相对准确的维度，owner一般也能把问题分发到相关的人员。

3. DB的负责人一般是大团队负责人，承载的业务比较杂，对问题的分发效果比较差。

4. 迭代和发布owner比较适合针对新增SQL卡点感知人员。

所以，我们考虑的策略是：

1. 对于线上存量的SQL工单问题和线上预警，触达到应用owner

2. 新增迭代引入的新增SQL问题，触达到迭代owner(如果有专项群的团队，会通过钉钉机器人触达到团队专项群)

3. 发布时刻的卡点，触达到发布owner

4. 风险预警的审批，因为考虑到SQL限流操作的本身的风险，触达到DB owner

因为数据库和应用是多对多的关系(一个数据库多个应用使用，一个应用使用多个数据库)，目前线上存量的SQL表只有数据库和SQL的关系，通过分析数据库数据 + 应用调用数据 + clouddba的慢sql数据，清洗出最大概率的应用，最终找到对应的处理同学。

2.4 修复

问题找人，成功投递后，下一步也是最关键的是修复问题。许多质量效能相关的平台或者专项可以帮助找到问题，不管是接口测试，自动化，资损，但是进一步最终解决问题，一般是需要研发同学最终解决。这一环的效率，决定了平台或者专项是否能持续落地的决定因素。

在建设树懒慢SQL平台，同时负责盒马治理专项的过程中，我非常有体感，很多研发同学接收到平台的风险提示，也认可这是一个需要解决的问题，但是如何解决，比较依赖同学的个人经验，整体的修复周期常常是以「周」为单位(当然也有部分比较有经验的同学，能够快速的修复问题)。

基于这样的情况，我们计划一是在域内进行经验沉淀培训，以及文化宣导，同时能够在平台上沉淀经验库能力，并且通过产品化的方式，传递给最终需要修复慢SQL问题的同学。

2.5 卡点

对于新增SQL，风险较高的危险SQL，是不希望发布上线的，所以除了树懒平台的每日的诊断通知外，我们还希望在发布过程中进行卡点，推动风险左移。

我们和集团的「布谷鸟」风险控制平台合作，可以在发布前进行卡点，并根据风险的危险程度透出不同的风险等级，如图：

2.6 恢复

对新增SQL的防范，和存量SQL治理分别对应事前和事中的风险，因为流程依赖人的执行，当执行不到位的情况，或者因为评估不足，线上业务的突然引起的线上风险问题，需要做事后的应急方案。

在线上已经发生慢SQL导致的业务问题，线上应急达到1-5-10的标准比率非常低，主要的痛点有：

1. 一般是业务监控发现问题，定位到是数据库问题需要一段时间。

2. 确定是数据库问题，定位到哪条或者哪几条SQL是主要原因，需要比较长的时间。

3. 其他手段无法止血的情况下，可以考虑SQL限流，但是业务域同学无权限需要联系DBA。

基于以上问题，慢SQL治理专项小组和GOC以及DBA同学合作，建设了慢SQL快恢能力，当线上出现问题时候，DBA提供自动风险预警能力，提示数据库的风险预警，并异步发起诊断，在2-3分钟内采集并诊断出最主要的风险SQL，并提供新流入口，在风险预警的卡片中，如下图所示：

3. 运营策略

---

3.1 分级策略

在盒马这样相对比较大的BU横向推动慢SQL治理，遇到很多运营上的问题，各域对慢SQL风险重视的程度不同，有的域因为历史上相关故障较多比较重视，已经有了自己域的治理策略；有的域重视程度不够，域内没有相关角色同学。

我作为一个横向专项负责同学，在推进统一的治理方案时候，对于比较重视的域，会有反馈和已有的治理策略冲突，开发同学收到重复的任务。对于不太重视的域，又没有开发和测试的专项角色，且对慢SQL缺乏相关概念，有一定沟通难度。

我的策略是，对于质量同学确定各域的接口人，开发同学主要通过各域的稳定性负责人推动。在横向的平台，做文化宣导和专项讲解，让各域对慢SQL风险有意识上的提升，以及对治理方案的逻辑能够认可。在我们的方案推行一段时间后，成熟域的同学也认可了我们的治理策略，把他们的域内治理合并到我们统一的方案中。对于之前无相关经验的团队，在横向整体节奏中也逐步建立起全流程的防控。

在各域有聚焦的小群进行运营，并且开发配置入口，各域负责人根据自己的诉求配置风险维度。

3.2 处理闭环

在盒马这样相对比较大的BU横向推动慢SQL治理，遇到很多运营上的问题，各域对慢SQL风险重视的程度不同，

3.3 快反演练

线上风险预警，进行应急快反是一个非常好的能力，但是可见的问题是线上问题是频率较低的，当真实问题发生时候，应急同学是否会使用，能够准确使用是落地的关键点。

对此，首先进行了团队的宣导，以及操作视频制作和分享，并且在业务域通过测试库配置到对应的服务组，进行内部演练，此方案是让技术同学熟悉操作流程。

但是，此方式目前效果比较一般，主要原因是不是自己业务的数据库体感比较弱，且因为操作频率低，当真有问题发生时候多半也忘了。目前主要通过推送风险预警到各域风险预警群以及整个盒马BU的消防群，我作为负责人之一，指导对应同学处理。不过这样我变成了单点，长期来看还是要解决能实际演练真实数据库，不影响业务，且能定期演练的达到业务团队熟练掌握应急手段的长效机制。

4. 总结

---

4.1 全流程防控

大家对事前，事中，事后的理解各有不同，本文暂时把未发布上线的SQL问题定义成「事前」，在线上运行的SQL问题定义成「事中」，已经影响到业务的SQL问题定义成「事后」。

我们的防控策略，是在每个阶段，都进行布防，并尽量做到左移，让风险在最初阶段就能够被拦截。

4.2 防范效果

经过一年不到时间的治理，整体故障和线上问题都有了明显的收敛，当然，最重要原因是各域的技术开发和测试同学的共同努力。专项是辅助的作用，让域内的治理更有针对性和更加高效。

在未来的项目，我们仍然需要敬畏每一次变更发布，如履薄冰，磨砺前行！

---

关注「阿里巴巴技术质量」阅读更多