作者介绍

分享概要

上图是SQL相关的线上数据库故障诊断流程。如何在业务上线或业务流量变化时，快速识别和处理SQL执行恶化的问题？

当我们上线新业务或者一系列数据库事件执行时，可能会发生SQL执行恶化，具体表现为高流量SQL、坏SQL以及锁阻塞SQL的出现。这些问题进一步引发了性能指标的异常，无论是系统性能指标还是数据库性能指标都可能受到影响，最终可能导致端到端甚至业务的报错。

面对这种情况，我们需要迅速进行告警处理和信息采集。首先，我们需要确保告警信息能够及时周知到相关负责人；接着，进行故障数据的采集，以便全面了解问题的范围和影响；在信息预处理阶段，我们会进行SQL指标的聚合，随后展开SQL行为分析，并对根因SQL进行排序，以准确定位问题的根源。

在识别出问题后，我们必须迅速采取业务止损和优化措施。通过业务根因定位，我们可以制定预案，及时进行止损。此外，我们还需要进行优化与重构，以确保系统能够恢复到正常稳定的状态。

根因SQL诊断的来源主要包括业务SQL和数据库事件。业务SQL可能因新上线的业务或业务变更而引发变化，而数据库事件通常与其他任务有关，如ETL任务、定时任务，甚至是数据库备份和迁移等操作。

为应对这些问题，需要采取了针对性的止损和修复措施。对于SQL本身的止损，我们可以使用kill命令终止问题SQL的执行，或通过中间件限流来控制SQL流量，减轻系统负担。

此外，针对任务的止损，我们可以通过调整任务参数或直接暂停任务来降低负载，或者通过业务端限流。同时，我们还实施其他优化措施，如添加索引以提高查询效率，或重写SQL查询逻辑以优化执行性能。

找到根因SQL是解决问题的关键，因为明确问题根源后，我们才能制定针对性的解决方案，实现系统的快速恢复和优化。

业界最常见的方法是基于规则的诊断方案。这种方案依赖于预设的诊断规则树或图谱，通过多分支的分析流程来识别出导致问题的根因SQL。

首先，基于规则的诊断方案具有多种数据来源。大的自动化诊断系统可以从性能指标监控系统、数据库process list、慢查询日志、全量SQL信息以及其他系统表信息中获取数据。这些丰富的数据来源展示了全面的故障时数据库的运行状态。

然而，诊断过程中也存在挑战。一个显著的问题是诊断阈值的多样性。多个分支在判断时依赖经验性的阈值，这在处理边缘情况时可能遇到困难。虽然经验性的阈值在大多数情况下有效，但在某些特殊情况下可能无法准确判断。

此外，诊断路径的复杂性也是一个需要关注的问题。由于多个分支可能命中不同的SQL，又需要更复杂的整合逻辑。多数诊断路径过于定制化，不仅影响了诊断的通用性，也增加了维护的难度。特别是在不同MySQL版本之间，规则树可能变化很大，增加了维护的复杂性。

基于统计模型的根因SQL诊断方法在学术界和工业界都有广泛的应用，并为我们提供了不同的视角来分析和解决根因SQL诊断问题。

在学术界，PinSQL是一种通过分析活跃会话与SQL实时执行数多种趋势的相关性的方法。它通过活跃会话的变化和SQL执行数之间的关系，识别可能导致性能问题的SQL。另一个方法是BALANCE，它基于少量KPI与全量SQL执行数建立贝叶斯线性模型，通过统计模型评估SQL与关键性能指标之间的关系，找出潜在的性能瓶颈。

在工业界，常用SQL与性能指标的关联分析。通过直接对比SQL执行情况与系统性能指标，识别问题SQL。此外，我们利用基于时间序列的方法进行历史趋势分析，识别异常波动的SQL行为。评分模型则采用多种分析方法对根因SQL进行打分，给予不同路径不同权重，形成综合得分，全面评估SQL的影响。

这些方法也面临挑战。首先，它们通常不支持多数据源，往往针对单一数据源建模，缺乏普遍适用性。其次，分析方法相对单一，只支持某个角度的SQL行为分析，缺乏可拓展性。最重要的是，这些模型缺乏学习能力，无法根据新的诊断案例进行主动学习。

介绍我们提出的基于学习的创新方法。首先来看时间区间选取这个关键问题。

现在请大家看一下这张图。它展示了在一个真实故障案例中，不同时间窗口下根因SQL的执行比例变化。当以告警前15分钟为基准时，根因SQL的执行比例为1.26%；而当窗口缩短到5分钟时，这一比例显著提升至3.84%。这个差异充分说明了选择合适的时间窗口对准确诊断的重要性。

在实践中我们发现，过长的时间窗口会引入大量干扰数据，而过短的窗口则可能导致信息缺失。更关键的是，统一的时间窗口难以适应不同的故障场景。接下来，我们来看图2，这展示了一个线上故障案例中某实例的活跃会话和负载变化趋势。我们发现二者之间存在明显的延迟和不同的波动趋势。这表明，单靠某一性能指标可能无法准确反映系统的整体性能变化，可能导致延迟、偏移或异常值的出现。我们很难通过固定的一个或者两个指标就确定故障的根因大概是什么时候出现的。为了解决这些问题，我们引入了变点检测技术。

变点检测的第一步是快速、准确地挖掘性能指标序列中的变化点。它帮助我们识别出哪些时刻系统性能发生了显著变化，这些变化点可能对应着潜在的故障或异常。

第二步是突出告警指标的变点权重。告警的指标通常更需要关注，因此需要对告警指标的变化点加大权重。

第三步是多指标序列的聚合变点分析。我们将多个性能指标的变化点进行密度聚类，以便全面了解系统在不同维度上的变化。

这种方法让我们能够更准确地定位故障发生的时间窗口。

在SQL分析方面，传统诊断方法面临几个典型挑战。首先是实时快照数据的缺失，这些缺失来自于数据采集端，这导致我们无法获取完整的SQL或者性能指标的执行上下文。其次，简单的统计值往往掩盖了SQL的真实分布特征。

更重要的是，如图所示，传统的相关系数分析难以准确描述性能指标和SQL执行之间的复杂关系（图中负载load（注释：这里的load负载指的是一定时间段内例如1min正在运行和等待CPU时间的平均进程数）和SQL执行次数总体趋势上不相似）。在复杂的数据库环境中，SQL的执行和性能之间的关系往往不是线性的，传统的相关性分析方法难以捕捉这种复杂性。我们融入了更多具有领域知识的指标对，例如qps指标与SQL执行次数一对，活跃会话数与正运行该模板SQL的线程数一对。

另外，SQL相关文本信息繁杂。SQL语句中包含了大量的结构化和非结构化信息，特别是当SQL语句中包含复杂的查询计划和优化提示时，简单的文本分析方法难以处理这些多样化的信息。

为此，我们创新性地提出将每个SQL模板转化为多维向量，融合了多种分析维度的信息。同时通过之后的机器学习方法，也可以从中学到这些方法互联之间的联系。

首先，我们使用统计指标对SQL模板进行聚合，并从中提取特征。通过稳健的中心趋势度量，如中位数和90百分位数，我们能够更准确地描述SQL执行的典型情况。分布特征指标，如偏度、峰度和方差，帮助我们更全面地理解SQL的性能波动。对于缺失值的处理我们采用了快速经典的滑动窗口平滑进行处理。

接着，我们进行SQL指标与性能指标的交叉特征分析。为此，我们引入了动态时间规整算法（DTW），用于刻画SQL执行与性能指标之间的相关性。这种方法能够识别不同时间序列之间的相似性，即使它们在时间轴上存在偏移和尺度差异。

在处理SQL文本信息时，我们将SQL的select_type和查询计划等信息转化为类别编码。这种编码方式帮助我们在分析中更好地处理SQL的结构化信息。此外，我们还对SQL文本中的HINT进行抽取并编码，以便在分析中考虑SQL优化提示的影响。

通过这些方法的融合，我们不仅提高了SQL分析的准确性，也为我们在优化SQL性能方面提供了新的视角。

学习型根因SQL定位是我们在SQL性能诊断中的一个重要创新。这项技术通过机器学习，帮助我们更精准地识别和优先处理SQL性能问题。

首先，我们从600多个线上真实诊断案例中整理数据，构建了一个机器学习数据集。这些案例为我们提供了丰富的训练素材，使我们的模型能够从中学习到大量的诊断经验。

在模型训练中，我们采用了LambdaRank，这是一种用于排序任务的机器学习方法。LambdaRank的输入包括SQL特征和性能指标，输出则是SQL的优先级得分。通过这种方法，我们能够训练模型来对SQL问题进行排序，识别出最可能影响系统性能的SQL语句。

在根因优先级得分计算方面，我们利用LambdaRank学到的参数权重来预测每个SQL的得分。不可否认的是，有一些在我们一开始的SQL向量表示中没有学习到的内容，例如SQL模板锁冲突关系，我们需要根据一些锁相关的快照进行重排，以优化优先级。（保留重排的原因：锁事务表的SQL查询结果存在非常多的缺失）这一过程确保我们能够快速识别出最重要的SQL问题，并及时采取措施。

机器学习可解释性是非常重要又难得的一点。我们利用 SHAP (SHapley Additive exPlanations) 技术,为预测结果提供可解释的特征重要性分析。这确保了系统的透明性和可解释性,让用户能够理解模型的判断依据。

我们结合规则和大模型技术,实现了将SQL与其产生来源的关系进行映射。这样不仅能定位问题SQL,还能确定其根源所在,为后续优化提供线索。

最后,我们针对不同类型的根因SQL,提供了相应的止损和优化预案。根据SQL的性质和来源,系统会给出具体的应对措施,帮助用户快速解决性能异常问题。

同时,我们还建立了诊断案例库,定期对历史复盘结果进行归纳积累。这些案例可用于持续优化模型,通过重新训练不断提升系统的诊断能力。

我们处理和采集了美团线上400多个经由DBA标注根因SQL的案例，作为最终的案例库，最终模型的效果。

我们的模型在多个方面表现优异。首先，LESSON在推荐数量和召回率上表现出色，能够以更少的推荐次数精准地命中更多的根因。此外，模型在识别最重要根因方面展现了更高的精准度，能够准确地命中问题的首要原因。在平均倒数排名（MRR）指标上，LESSON模型能够在更靠前的排名位置命中第一根因，显示出其在排序准确性上的优势。最后，LESSON不仅能够命中多个根因，还能正确地排序这些根因的重要性，确保根因之间的优先级排列符合实际诊断经验。通过这些指标的综合比较，LESSON模型在多个维度上都展示了更优的性能，有效提升了根因分析的效率和准确性。

接下来，我将分享一些在线上的诊断案例，展示我们的诊断算法如何在实际场景中发挥作用。

首先是一个常见的故障类型：流量突增导致数据库CPU、活跃会话等多个指标告警。我们的诊断流程在10秒钟以内完成诊断，并生成诊断报告进行推送。可以看到，算法选择了一个更合理的时间区间，08:29:40-08:31:52，这个区间内多个性能指标的变化趋势有明显的滞后和偏离，而这个起始点附近正是根因SQL的突增点。

右侧图表展示了案例发生时的多个活跃会话快照，不同颜色表示多个SQL模板。我们的模型不仅考虑了执行次数，还分析了指标趋势相似度和时序变化趋势。这些可解释性特征在诊断报告中得到体现，最终给出了多个模板的综合评分。

然后是慢查询突增引起CPU Load负载告警的场景。我们可以看到在15:56分左右活跃会话（看指标图）、CPU利用率、发送字节数等指标都发生了不同程度的突增或者波动。在告警时我们看到慢SQL的聚合列表我们可以看到，有些SQL模板数量很多，有些SQL模板虽然数量不多但是执行时间特别慢。可以看到我们的模型给了后者0.91的高分，按照累计阈值0.9，我们推荐其作为唯一的根因。可以看到通过可解释性特征改写后的一些信息，例如扫描行数、返回字节数、全表扫描等信息都出现在了诊断报告中。模型的这一特性都是从过往案例类似的诊断经验中学到的。

在我们的诊断系统中，除了处理常见的流量突增问题外，我们还具备应对更复杂场景的能力。

此外，我们的系统还能够处理活跃会话中出现的异常波形。这些波形可能由多个业务逻辑或任务的叠加引起，导致系统性能波动。例如多慢查询突增、大量锁冲突，甚至实例宕机实时指标缺失的情况下，我们依旧可以靠采集保存下来历史监控数据来进行有效的诊断。

我们的模型具备不断学习和进步的能力，类似于DBA在各种案例中积累经验。通过不断更新的案例库和实时数据分析，我们的系统能够在不断变化的业务环境中保持高效的诊断能力。