# 一分钟精华速览 #

多点 DMALL 的数据库规模庞大，有数千个集群，实例数量超过了 10000，另外，DBA 团队要支撑千人规模研发人员的各类数据库需求，其中以 SQL 性能相关居多，业务增长快速迭代的过程中，靠堆人的方式力不从心。为了突破这一瓶颈，多点 DMALL 数据库团队着力实现大规模数据库自助化服务能力，为研发团队提供更高效的支撑，为此研发了 TopSQL 工具，弥补慢 SQL 在覆盖不全、指标有限、配置复杂等方面的局限性，它提升了 SQL 性能的洞察能力，通过输出直观的图表，实现了研发自助发现和处理问题 SQL，成功将 DBA 支撑研发 SQL 性能优化的工作量减少了 90%，使得团队能够管理更大规模数据库集群、支撑更大规模研发体系。详细的解决策略和方法，请参阅文章正文。

万+数据库实例：多点DMALL如何通过TopSQL提升MySQL性能观测效率？"

作者介绍

多点 DMALL 数据库团队负责人——冯光普

TakinTalks 稳定性社区专家，多点 DMALL 数据库团队负责人，专长于：

大规模 MySQL、TiDB、OB 等数据库稳定性保障；
DB PaaS 平台建设，为研发体系输出标准化数据库服务流程；
多活架构，DB 中间件开发；

曾任职于阿里巴巴 AliSQL 团队，致力于数据库性能优化。

温馨提醒：本文约6000字，预计花费10分钟阅读。

后台回复 “交流” 进入读者交流群；回复“Q103”获取课件；

背景

多点 DMALL 专注于零售数字化解决方案赛道，我所在的数据库团队，负责保障 MySQL 集群的稳定高效。我们的集群规模相当庞大，实例数量超过了一万。在业务快速迭代的背景下，SQL 性能问题不可避免，一旦在高峰期发生，类似高速公路上的堵车，会引发一连串连锁反应。

我们的经验，生产数据库崩溃往往源于 SQL 性能问题，慢 SQL 会导致应用侧新建大量连接，数据库 CPU 利用率激增，大量正常 SQL 被阻塞，若应用超时后再发起重试，数据库最终将崩溃。

我们的目标，是实现对数据库 SQL 性能的全面洞察，支持研发直接参与，精确定位和优化问题 SQL，最大程度避免 SQL 性能问题导致的系统崩溃。

一、多点DMALL的SQL性能洞察遇到了哪些挑战？

我们 MySQL 规模庞大，要深入洞察每个数据库实例中的问题 SQL，难度很大；

DBA 团队需要支撑千人规模研发体系，若所有性能问题的解决都依赖于 DBA，显然是不现实的；

我们迫切需要一套工具，能让研发团队快速感知并定位到问题 SQL，这样才能真正释放 DBA 的生产力。

方法一：流程管控

目前，大家普遍采用的方法之一是在流程上进行管控。在代码上线之前，在开发、测试或 UAT 等线下环境中找出并优化所有可能的问题。这是一个好的想法，但在实际生产环境中，我们仍会遇到 SQL 性能问题。这是因为开发和测试环境中的 SQL 可能与生产环境中的不一致，且数据规模也不同。流程管控虽然能够避免大约 50%到 60%的问题，但仍存在一些难以发现的问题在生产环境引发故障。

方法二：监控告警

监控告警当然是一个众所周知的方式，系统资源层面、数据库层面，都有非常成熟的工具，这里就不做赘述了。

方法三：慢 SQL 日志分析

慢 SQL 日志分析，是定位问题 SQL 至关重要的工具，它记录了所有执行时间超过一定阈值的 SQL，帮助我们精确地找到问题所在，基本能解决 70%到 80%的问题。然而，慢 SQL 有其局限性，它只能记录超过阈值的 SQL，而且对于大规模集群和研发团队的日常维护来说，挑战依然很大。

因此，我们的思路是构建一个全面的分析和观测体系，它能够无遗漏地分析所有数据，并通过加工，以直观的方式展示问题。我们希望开发出的工具不仅仅是 DBA 使用，而是能够让更多的研发人员参与到性能问题的感知、分析、优化中来，基于研发自助的可观测能力，DBA 就能够更有效地管理更大规模数据库集群，保障稳定性。

二、慢SQL有哪些局限性？

慢 SQL 作为一个被广泛使用的工具，它是很有效的，但在实际应用中，我们也发现了它的一些不足之处：

第一，慢 SQL 仅记录超过阈值的SQL，记录的指标也有限，并不能发现所有的问题；
第二，当我们运维的集群规模很庞大时，我们需要确定一个合适的阈值，但这个阈值应该是怎样的呢？实际上，这个阈值与业务系统紧密相关，我们很难用一个标准的配置来适应所有的业务库。

2.1 慢 SQL 分析工具

大家熟悉的 pt-query-digest，它可以分析慢 SQL 日志，并提供了易于理解的 UI 界面，能够告诉我们有哪些慢 SQL，以及相应的时间、次数等统计信息，这在分析 MySQL 性能问题时是非常有用的。

此外，还有一个比较新的工具 PMM Query Analytics，它会提供慢 SQL 并发度相关的一些信息，通过直观的排序展示，帮助我们精确地定位问题，目前这个工具在实际使用中也是比较多的。

2.2 慢 SQL 的局限性

2.2.1 有限的指标

2.2.1 图 – 一条慢 SQL 示例

DBA 对这条慢 SQL 示例应该很熟悉，其中的指标其实非常有限，它仅记录了查询时间、锁时间、以及发送和检查的数据行这几个指标，这些虽然有助于定位一些性能问题，但并不能全面分析出 SQL 性能瓶颈。

2.2.2 非全量 SQL 流量

如果我们不对慢 SQL 进行配置，或者配置的阈值过低，那么生成的日志文件将会非常庞大，这不仅难以管理，还会引入 IO 开销。因此，通常情况下，我们只会配置仅记录一部分超过特定阈值的 SQL 语句，这就导致了它无法覆盖所有的 SQL 流量，遗漏一些潜在的问题 SQL。

2.2.3 阈值配置与业务相关

对于大规模的集群，很难设定一个适用于所有场景的标准阈值。即便是对于单个数据库系统，当前配置的阈值也不一定持续有效，随着业务的持续迭代，新 SQL 模式上线，可能就需要对阈值进行调整。因此，面对变化的业务，如何动态合理地调整这个阈值，是一个挑战。

三、TopSQL 原理是什么，怎么实现的？

3.1 TopSQL 原理

针对前面提到的慢 SQL 日志的局限性，我们开发了 TopSQL。它的核心功能是全量统计 SQL 流量，并对这些数据进行深入的分析和排序。我们期望通过直观的图表，清晰地展示出问题所在，帮助研发团队，特别是大规模的研发体系，高效地定位和解决问题。

TopSQL 的原理并不复杂，简单来说，它分为三个主要部分：数据输入、分析处理、图表输出。在数据输入方面，关注两个关键的数据源，首先是 MySQL 网络流量，包括时间、流量次数等数据；另外，我们还需要关注磁盘 IO 相关因素，因此也将 binlog 纳入分析，有了这两个数据源，我们的分析就能更全面。

在处理阶段，我们进行协议解析，对于捕获到的 SQL 流量和 binlog event，我们会提取关键信息，比如 SQL 指纹（简单的理解：即将原始 SQL 语句中的变量替换成问号），并针对特定指标进行统计，这一步是识别问题的关键，处理完毕后，将统计数据存储起来，提供多维度排序查询，以直观的图表展示这些数据，研发可理解，问题 SQL 可一目了然。

3.2 TopSQL 实现

在 TopSQL 的实现中，我们使用了两个开源组件，它们对我们的工作至关重要。parser 组件负责语法解析，它能够将 SQL 语句转换成语法树，并从中提取出 SQL 模式。sniffer-agent 组件则负责 MySQL 协议解析，帮助我们从网络流量中提取出 MySQL 协议对应的 SQL 语句（MySQL 服务器/客户端协议是一问一答的模式，客户端发起请求，服务器返回响应）。

基于上述两个组件，针对第一个输入，我们以旁路方式在网卡上监听数据库端口上的全部流量，实时解析 MySQL 的客户端和服务器协议，提取出 SQL 文本，进行 SQL 语法解析后，得到 SQL 指纹。针对第二个输入，要从 binlog 中获取原始 SQL，需要开启参数 binlog_rows_query_log_events（否则只能统计出每个表上发生了多少行变更，无法将这些变更与具体的 SQL 语句关联起来，这样就无法达到 TopSQL 设计的效果），binlog 解析这块，目前社区中已经有许多优秀的组件可以使用，无需再造轮子。

分析 binlog 过程中，除了统计影响行数相关的指标，我们还会进行一些额外的计算，比如对于 update 操作，我们会计算 before 和 after 的差异，识别出 SQL 到底影响了哪些字段。有些 SQL 虽然很长，但实际上只更新了几个字段，这样的情况我们会识别出来，并进行数据统计。

通过一系列的处理分析，我们得到了多维度数据统计结果，并将其存储起来，最后再实现一个查询聚合排序的接口，将数据返回给前端，直观地以排序图表、热力图等形式展现给用户。

四、TopSQL 有哪些能力？有哪些性能诊断实践？

4.1 TopSQL 能力

TopSQL 设计之初的定位，就是要给研发团队提供 SQL 性能观测和优化支持，而不仅是 DBA 的专属工具，它的能力输出，主要包含四个方面：

第一，全量 SQL 模式。TopSQL 能够展示数据库中所有的业务 SQL 模式，这对于新加入的研发人员尤其有帮助，他们可以快速掌握业务在数据层的全景图。

第二，Top 排序功能。这对于我们定位问题至关重要。通过分析 SQL 的出现次数、总耗时、平均耗时以及流量，我们可以识别出业务中最高频、最耗时的模式。特别是出现次数这个关键指标排序，能帮我们快速找到哪些业务 SQL 模式最需要关注和优化。

第三，热力图功能。可以非常直观地展示出数据库中的热点表、热点 DML，让研发人员轻松观测到 IO 层面问题瓶颈，降低对 DBA 的依赖。

第四，由于 TopSQL 基于 binlog 进行了大量的分析和数据统计，它还能够定位到一些特定的问题，比如分析出 update 语句真实更新的字段，分析出无效的更新。这对于磁盘 IO 层面的问题分析尤其有效。

接下来，我将分享一些具体的应用案例，让大家更直观地感受 TopSQL 在实际工作中的效果。

4.2 Top 排序展示-应用案例

4.2.1 TopSQL-总耗时

TopSQL 通过对 SQL 指纹进行排序，可以突出展示某一个维度上的问题，TopSQL 可输出时间、流量、频次这几个维度上的 SQL 指纹统计排序。

4.2.1 图 – TopSQL 的前端展示

上述页面中，我们可以看到 SQL 指纹执行的总耗时排序，这是一个非常重要的指标，它可以帮助我们快速识别出哪些 SQL 模式消耗了最多的资源和时间。

有了这样的排序，哪些 SQL 模式是 MySQL 响应和处理客户端请求的主要负担，一目了然。我们遇到过这样一类问题：业务数据库的 CPU 利用率很高，但是在慢 SQL 中却没有几条记录，很难定位，若使用 TopSQL 我们就可以根据总耗时来快速定位出相关 SQL，比如：上图中排名第一的 SQL 模式的平均执行时间是 1.68 秒，而如果我们将慢 SQL 的阈值设置为 3 秒，这个慢 SQL 根本就不会被记录下来，而通过 TopSQL 总耗时排序，我们就可以快速定位问题，针对性优化。

基于 TopSQL-总耗时的排序展示，我们能够更加高效地观测和优化数据库性能，达到事半功倍的效果。

4.2.2 TopSQL-平均耗时

第二个关键的维度是平均耗时。SQL 模式的平均耗时，对于我们更好地发挥慢 SQL 工具的效果至关重要，它能够回答一个关键问题：业务数据库慢 SQL 阈值到底应该设置为多少？

对于事务处理类（TP）业务，我们通常会将阈值设置得较低，一般不会超过3秒。因为在高并发事务处理的情况下，超过3秒的 SQL 可能会导致业务超时中断。然而，对于分析处理类（AP）业务，情况就不一样，查询耗时达到10秒或数十秒也是正常的，因为它们通常是执行统计类的需求，并发度也不会很高。

TopSQL 平均耗时排序，可以输出 SQL 模式的平均耗时分布。上图中，我们发现前 10 的查询模式平均耗时都超过了七秒，它大概率是 AP 类业务，那么我们就可以针对性调高慢 SQL 阈值配置，30 秒或以上。更进一步，我们可以实现自动地去调整这个值，减少 DBA 运维工作量。

此外，TopSQL 还记录了 SQL 模式的最大耗时以及最大耗时发生的时间点，这一点对于确认数据库引发的问题非常有帮助，我们可以将这个时间点与业务出现问题或客户反馈的时间点进行对比交叉验证，若时间点一致，那么我们就可以猜测问题原因，大概率是与 SQL 性能相关。

4.2.3 TopSQL-网络流量（出）

这个维度上的排序，我们关注的是 SQL 的网络流量，或者说执行哪些 SQL 模式，MySQL 返回了更多的数据。

有一个案例，通过 TopSQL 的网络流量排序，我们发现有一个 SQL 模式传输了大量的数据，几乎占了整个 MySQL 输出流量的 30%到 40%，最终我们确认这个 SQL 是动态生成的，返回包含了所有字段，我们针对性地优化，裁剪掉一些不必要的字段返回后，尤其是那些的 TEXT 或 BLOB 字段，通过减少这些不必要的网络 IO，我们显著提升了 SQL 性能，同时大幅降低了网络流量。

4.2.4 TopSQL-执行次数

这个指标能告诉我们：一个 SQL 模式在一定时间内被执行了多少次。有一个比较有意思的案例，我们发现某个旧版本的 JDBC 驱动在执行更新操作时，每次都会附带执行一次 select @@session.trx_read_only 操作，TopSQL 里这个 SQL 模式执行次数高居榜首。每个 DML 操作都要进行额外的检查，这将增加 1 次不必要的网络开销，通过升级驱动版解决问题后，DML 性能提升了 30%到 40%，对于那些高频操作，优化的效果非常明显。

4.3 热力图-应用案例

基于 MySQL 网络流量数据，TopSQL 按不同维度进行排序并输出图表，但 TopSQL 的能力不止于此，它还能帮助我们找出热点表，以非常直观的方式定位 IO 瓶颈，因为我们也将 binlog 作为数据源，分析和统计了 DML Event。

对于数据库来说，优化磁盘 IO 可以显著提升性能，基于 binlog 的统计分析，可以帮我们定位下述 IO 相关问题：

DML 操作是如何分布的？
是否存在被频繁更新的热点表？
分表之后，压力是否均衡，分表是否合理，符合预期？
SQL 模式，物理上更新了哪些字段，业务上是否存在无效更新？
SQL 模式实际更新了多少行数据，哪些 SQL 是 IO 消耗大户？

4.3 图 1 – 用热力图判断哪些表新增了更多数据

热力图，是发现热点表的一个非常直观的工具。图中，纵坐标代表了表，横坐标代表了时间，而颜色的深浅则表示了操作的行数。通过这样的视觉展示，我们可以迅速识别出在特定时间段内哪些表上的写入更频繁。图中清晰展示了 wm_ware_hit_promotion 这个表在一分钟内写入了 25.7w 行，远远高于其他的表，这明显是一个热点表。

4.3 图 2 – 用热力图判断哪些表执行了更多更新

通过热力图 2，可以清晰地看到哪些表上执行了最多的更新，研发团队可以直接利用这个工具，快速地感知到可能存在的问题，结合业务逻辑进行进一步的分析、定位、优化。

4.3 图 3 – 用热力图辅助判断负载是否均衡

通过热力图 3，我们可以快速评估分表策略的效果，通过颜色深浅观察不同分表上的更新频次，判断负载是否均衡。图上 wm_order_00 这个表，在某个时间段内比其他表的颜色更深，这意味着我们需要进一步分析，结合业务去具体定位这个表在这个时间段发生了什么，不过，从这张热力图可以看出，我们分表后的负载基本是均衡的，倾斜可控。

4.4 物理更新统计-应用案例

4.4.1 物理更新识别

我们遇到过这样的情况：数据库更新量很大，产生的大量 binlog，但实际上业务字段并没有改变，却导致了数据同步到下游系统出现延迟。

通过物理更新识别和分析，我们快速定位到了问题源头：高频的 update SQL 实际上只更新了两个对业务逻辑影响不大的时间字段。通过开启 binlog_rows_query_log_events 记录了原始 SQL 语句的方法，我们追踪了每条语句的更新细节，有效识别了无效的物理更新，显著提升了问题定位效率。

4.4.2 TopSQL-IO 大户诊断

通过 TopSQL 影响行数的排序，我们可以轻松地识别出那些消耗大量 IO 资源的操作，有一个具体的例子，由于程序 bug 导致的问题，SQL 基本上更新了整个表，这个 update 操作是更新某个 ID 字段，但漏掉了 where 条件，导致了全表约八百多万条数据被更新，服务器 IO 100%，MySQL 响应慢。

虽然其他业务 SQL 操作本身并没有问题，大多数其他 SQL 只更新少数几条数据，但由于这个 bug SQL 的全表更新操作，整个 MySQL 数据库的性能受到了影响，处于一种被拖累的状态。通过 TopSQL，我们快速准确地定位到了这个问题 SQL。

4.5 MySQL 性能观测组合拳

当我们只有慢 SQL 这一个工具时，可能只能支持业务发展的早期阶段，这时数据库规模较小，性能问题不多。DBA 可以手动管理阈值参数，并支持研发优化 SQL。

但随着业务的增长，这种模式就变得不可持续。这时，TopSQL 的作用就变得尤为重要，因为它赋予了研发团队性能诊断的能力，不再完全依赖 DBA。通过 TopSQL 多维度排序，热力图，以及 IO 相关的诊断，研发团队可以自己定位问题：哪种 SQL 模式、发生时间、执行频率等，绝大绝大部分问题能够被识别和优化，只有少数比较棘手的问题才会依赖 DBA 的帮助。DBA 的工作变得更加轻松，可以更多精力聚焦于重要但不紧急的问题，支持更大规模的研发体系，将 DBA 人均服务研发的人数，提升 2 倍以上。

结合慢 SQL 工具，TopSQL 提供的全量 SQL 性能洞察能力，让大规模集群慢 SQL 阈值的动态管理成为可能，我们可以根据不同的业务场景需求快速调整阈值，并且可以实现一定程度的自动化，基于这两个工具，DBA 可以运维管理更大规模的 MySQL 集群。

五、总结展望

虽然慢 SQL 是一个非常有用的工具，但它存在局限性，无法覆盖所有 SQL 查询、指标有限、大规模集群阈值配置难度大。

为了突破这些局限，我们开发了 TopSQL 工具。TopSQL 捕获全量的 MySQL 网络流量，并结合 binlog 文件，对 SQL 性能进行深入的洞察，识别并统计各种 SQL 模式，按照多个维度进行排序和可视化，输出热力图，基于这些直观的图表，研发团队可自助感知和优化 SQL 性能问题，不再依赖 DBA 的介入。

结合慢 SQL 和 TopSQL，我们能够构建一个完整的 SQL 性能监控、观测、优化体系，这个体系可以有效地支持问题 SQL 告警，能够帮助全面深入洞察 SQL 性能问题，支持 DBA 动态合理地更新慢 SQL 阈值配置，真正形成正向循环。

最终的收益：研发团队自助定位和优化 SQL 性能问题，DBA 更高效地运维和管理更大规模的 MySQL 集群，真正实现降本增效。（全文完）

Q&A:

1、数据采集依赖于 MySQL 版本或特定的存储引擎吗？

2、老师好，请问开启 TopSQL 监控对数据库性能有多大影响？

3、写操作影响的行数 IO 大小、原始语句，是怎么收集到的呢？是在客户端 SDK 埋点吗？

4、老师好，TopSQL 有计划开源么？

以上问题答案，欢迎点击“阅读全文”，观看完整版解答！

相关活动推荐：