福利贴｜妙手神医瑶池：寻根溯源，哪条SQL导致了性能异常？

阿里云瑶池数据库 03-15 79

前言：

眼前我们对“sql 图片”大概比较着重，同学们都想要知道一些“sql 图片”的相关内容。那么小编在网摘上搜集了一些对于“sql 图片””的相关知识，希望咱们能喜欢，你们一起来了解一下吧！

导读

随着数字江湖的风云变幻，阿里云瑶池数据库不断深化「云原生+Serverless」应用，旨在让数据平台像“搭积木”一样便捷轻巧，提供秒级弹性、开箱即用免运维等服务，打造智能化的一站式数据管理平台。

书接上回，这边刚刚处理完寒霜城数据库存储空间急症，琅风山数据库的DAS生命监测系统就发出“性能指标异常”的警报（阿里云瑶池旗下的数据库自治服务DAS是基于机器学习和专家经验实现数据库自感知、自修复、自优化、自运维及自安全的云服务，为数据库提供7*24h的“生命异常监测”），妙手神医瑶池在一阵急促的鹤唳声中腾云离开，火速奔赴数据纷争中心。

第 1 回｜慢SQL夜袭数据库，妙手神医瑶池诊疗心法大公开

第 2 回｜第 2 回｜妙手神医瑶池诊疗实录：数据库存储空间去哪儿了？

[撒花][撒花][撒花]

参与公众号文章有奖互动

有机会赢取限定好礼

1. 异常SQL搅动风云，精准诊疗化险为夷

远远望去，烽烟缭绕，琅风山数据库的警报声在山间回荡，久久不绝。

经诊断，数据库发生性能指标异常，异常快照中的异常指标分析与性能指标显示：

进一步查看分析与cpu_usage相关的SQL（相关SQL，是将全量SQL的执行趋势图像化并聚类分类，建立了性能指标与SQL执行趋势指标的联系），可以看到：

“瑶池大夫，这个指标异常是哪里出现问题了？该怎么处理啊？”

妙手神医拿起诊疗报告，逐行向大家解释道：“来，看第一行，这里显示cpu_usage相关度接近1.0，说明SQL模板所在的类别与cpu_usage关联度最高，即该类SQL与异常性能指标的关联度最高。我们可以再查看这个类别内的SQL模板，按照执行次数或耗时ranking，就可以看到对这个指标异常贡献度最高的SQL模板。”

为了更加精准定位到某一类或某几类SQL模板，从而对症治疗，妙手神医瑶池在构建的SQL执行趋势以及数据库性能指标的关联关系的基础上，进一步精准定位造成异常的根因SQL和被影响SQL。于是，看到该异常事件的根因分析和建议：

“通过这里，我们可以看到，主要是第一行DELETE SQL模板的大量执行，造成实例CPU类指标、DML操作行数类指标、DML执行次数类指标的升高。针对定位到的异常SQL模板，可以查看对应的SQL样本，通过采取限流、优化等措施进行异常问题的止血与数据库性能的优化。”

2. 异常SQL无处藏身，根因分析对症下药

为了保证数据库的生命体征稳定，除了需要及时发现异常，也需要快速确定异常发生的原因，从而做到“对症下药”。云数据库性能异常的根因分析（Root Cause Analysis，RCA）旨在诊断检测到的数据库异常的根本原因。

因此，分析用户在数据库的SQL请求行为，并能够进行异常SQL的定位，是处理和解决数据库性能异常问题的关键。

在实际应用中，此类问题面临着如下挑战：

随着业务量增长，实际的生产环境中数据量往往很大，在海量SQL的场景下，进行全量SQL的逐条分析与异常SQL的定位难度大，而且搜索耗时长，成本高；单纯对全量SQL使用topN的方法寻找SQL，如只寻找执行耗时长的SQL，会损失大量的潜在问题SQL，异常SQL定位的召回率低；异常检测分析出的异常指标，难以直接建立其与用户SQL请求的联系，无法闭环分析。

凭借多年数据库诊疗经验，妙手神医瑶池已经研制出一套成熟的“异常SQL”诊疗方案——根因SQL分析，能够从容应对各种“性能异常”症状。在琅风山数据库恢复留观期内，妙手神医瑶池为大家详细介绍了这套诊疗方案。

2.1 建立SQL请求与异常性能指标的联系

2.2.1 数据收集&异常监测

首先收集依赖的云数据库中用户的SQL请求日志数据和数据库的性能数据：

▶︎ 其中对于SQL数据，由于全量SQL数据量庞大难以逐一分析，需要对SQL数据进行模板化处理，用于后续对各类SQL模板的聚类分析。例如一条SQL模板SELECT * FROM user table WHERE uid = ? 可能包含以下的SQL请求：

SELECT * FROM user table WHERE uid = 123456 SELECT * FROM user table WHERE uid = 654321SELECT * FROM user table WHERE uid = 123321

？号模板化了具体的uid。

▶︎ 性能数据用于异常检测。分析用户关注和功能支持的性能指标是否发生了异常，关注性能指标的异常特征（spike，trend，level shift），从而确认异常现象，如DML变化，从而开始建立指标与相关SQL请求的联系。

2.1.2 SQL模板聚类

数据收集和异常检测之后，进行SQL模板的聚类，关注的是SQL执行趋势指标的相似度。

在实际应用中，我们关注的是异常时间段，因此可以选择异常开始发生时间段内执行的SQL模板，分析这些模板在异常前一段时间（如30～60min）不同SQL指标的执行趋势的相似度。

接下来，基于图分析的算法，对SQL执行趋势的不同指标分别进行相似度聚类。即当业务存在大量请求的时候，按照SQL请求行为对SQL模板进行分类。聚类后，全量SQL的分析种类大大减少，2000页的SQL可以按类归为10页，解决了通过排序很难一个个去筛选问题SQL的问题，帮助用户将大量的SQL模板聚类，由此提高问题定位的效率，极大的缩短了分析时间。

2.1.3 性能指标关联的SQL请求行为分析

接着，对聚类分类后的SQL模板，进行性能指标关联的SQL请求行为分析。主要是计算SQL执行趋势聚类后的簇与性能指标的相似度，将相同时间范围的不同指标的性能数据，与聚类结果中不同簇的SQL执行趋势关联起来。

在应用中，可以ranking分析SQL执行趋势聚类后的簇与性能指标的相似度。如上图中的性能指标尖刺，对应可以找到执行趋势相关的问题SQL。通过这些分析，可以实现根据指标的异常时间段来排查与异常指标相关的问题。

2.2 定位根因SQL

2.2.1 指标相关的聚簇筛选

异常SQL定位的实现基于SQL模板聚类。

在对SQL模板聚类分类后，繁杂且大量的SQL模板已经按照相应的执行趋势与性能指标的相关程度聚类分类。为了进一步精准定位异常SQL，首先需要进行聚簇筛选，进一步缩小目标范围。

筛选主要分两个条件：

a. 对于一个聚簇c∈D，我们认为该聚簇对异常指标的贡献度，就是该聚簇里所有SQL模板中对异常指标最大的影响值。

b. 为了能尽可能多的将异常SQL所在的聚簇筛选出来，因此除了第一个条件，我们还计算同一个聚簇中的同一类SQL模板的累计影响值。

然后综合第一条件中的最大影响值和第二个条件中的累计影响值是否达到阈值，判断是否选择某个聚簇作为根因SQL候选聚簇。

2.2.2 根因SQL&被影响SQL定位

通过聚簇筛选，获得了用于定位根因SQL和被影响SQL的candidates，进行最后的根因SQL和被影响SQL的定位。

我们的先验知识是，平稳的流量通常是不会造成指标的异常现象的。所以在定位时，可以将异常事件的异常执行趋势，与异常前一段时间的历史执行趋势做比较，辅助定位根因SQL。然后综合不同指标和异常现象间的传播关系，以及使用SQL模板的执行趋势指标和实例性能指标之间的相关性进行分析，可以精准定位根因SQL和被影响SQL。

这里举一个分析过程的示例：一种SQL模板造成了锁等待现象，就会影响锁等待相关的指标。锁等待现象的发生，会造成某模板集合的SQL被阻塞，就会造成session突增。被阻塞的集合中CPU密集型SQL又会被阻塞，造成CPU突增。通过这样的传播关系，就可以定位到具体的根因SQL和被影响SQL。

异常SQL定位可以支持数据库多种根因定位场景的动态扩展，并辅助进行数据库性能的优化，用于后续多种自动修复场景，包括SQL限流、SQL优化等。

3. 根因SQL分析使用场景

萦绕在山间的烽烟渐渐退去，琅风山数据库生命体征已趋于平稳，妙手神医瑶池对众人叮嘱道：

“「根因SQL分析」是瑶池派一套成熟的‘异常SQL’诊疗方案，在后期的恢复中，大家可以通过SQL洞察功能持续关注身体情况。”

随后，妙手神医瑶池开出一张诊疗单，详细说明了「根因SQL分析」功能的支持场景与指标：

支持的引擎与场景包括：MySQL支持10类30多种性能指标的关联，PolarDB MySQL支持6类近20种性能指标的关联。当前分析依赖全量SQL汇聚数据（SQL洞察）、基于performance_schema能力汇聚的SQL数据（性能洞察）。

支持的监控指标包括：DML执行次数类指标、DML操作行数类指标、线程池指标、实例CPU类指标、实例内存指标、缓冲池指标、逻辑IO类指标、物理IO类指标、实例磁盘空间类指标、其他。

被关联的SQL指标包括：按照SQL的执行次数、按照SQL的总执行耗时、按照SQL的总扫描行数、按照SQL执行的锁等待时间、按照SQL执行的逻辑读、按照估算的SQL执行的CPU消耗，共6类执行趋势指标。

不久后，琅风山恢复了往日的有序和平静。妙手神医瑶池依然行走于风云之中，在纷争林立的数据江湖书写着一个又一个新的故事。

数据库自治服务DAS是一种基于机器学习和专家经验实现数据库自感知、自修复、自优化、自运维及自安全的云服务，让数据库实现了“自动驾驶”，能够帮助用户消除数据库管理的复杂性及人工操作引发的服务故障，有效保障数据库服务的稳定、安全及高效。

点击【在SQL洞察和审计中进行SQL洞察的操作_数据库自治服务 DAS(DAS)-阿里云帮助中心】了解数据库自治服务DAS 更多信息。