慢 SQL 是如何拖垮数据库的？

案发现场

上午9:49，应用报警：错误码4103.ERR_ATOM_CONNECTION_POOL_FULL，提示数据库连接池已满。

在上午9:49至10:08的这段时间内，又陆续接收到了多个数据库异常报警，包括4200.ERR_GROUP_NOT_AVAILABLE、4201.ERR_GROUP_NO_ATOM_AVAILABLE以及4202.ERR_SQL_QUERY_TIMEOUT等。

鉴于数据库承载着销售核心业务的用户组织权限功能，因此在故障发生的这段时间内，销售工作台无法正常打开，客服纷纷反馈并咨询相关情况。

上午10:08，基本定位到问题的原因可能与某个应用基础缓存包的升级发布有关。据了解，上午9点40分刚刚完成了最后一批的发布，这个时间点与故障发生的时间点相吻合。

先打开缓存开关来恢复系统的正常运行。

初步结论

对于此次升级缓存包应用发布的内容进行深入分析后，发现其中一个重要的变更点是：升级的一个二方包中移除了原有的本地缓存逻辑，改为直接对数据库发起请求。

然而，这次升级过程中并未针对数据库查询的SQL语句进行必要的优化。

在由Oracle迁移到MySQL后，由于两个数据库的性能差异，这条SQL语句在MySQL中执行变得非常缓慢，平均每次执行都需要超过2秒的时间。这种大量的慢查询最终导致了数据库的性能严重下降。

SQL如下：

SELECT CRM_USER_ID AS LOGIN_ID, CRM_ROLE_ID AS ROLE_NAME, CRM_ORG_ID AS ORG_ID
  , CONCAT(CRM_USER_ID, '#', CRM_ROLE_ID, '#', CRM_ORG_ID) AS URO
  , CONCAT(CRM_ORG_ID, '#', CRM_ROLE_ID) AS ORG_ID_ROLE_NAME
FROM CRM_USER_ROLE_ORG
WHERE IS_DELETED = 'n'
  AND CONCAT(CRM_ORG_ID, '#', CRM_ROLE_ID) = '123#abc'
ORDER BY ID DESC;

 其他疑问

以下几个疑问需要进一步探究：

这条SQL语句为什么会成为慢查询？

虽然这次发布的应用并不是核心应用，并且它与登录权限共用了同一个数据库，但在发布时应用的QPS仅为0.几，这样的低流量为何能够导致整个数据库崩溃？

之前我们已经对数据库连接池进行了扩容，从10增加到了15，而且线上有流量的机器数量也仅有7台，这样的配置为何仍然无法承受住应用的压力，导致数据库崩溃？

在事后进行复盘时，我们发现发布前一天在灰度测试阶段就已经出现了慢查询的情况，但当时并没有导致数据库崩溃，这又是为什么呢？

分析原理

慢SQL分析

CONCAT(CRM_ORG_ID, '#', CRM_ROLE_ID) = '123#abc' 

该SQL由工具直接从Oracle翻译过来的

• 虽然两个拼接的字段各自都有索引，但是使用函数后，MySQL是不会使用索引的，退化为了普通查询。

• 由于表数据量较大，全表40W+数据，导致扫描行数很多，平均扫描16W行、逻辑读38W行，执行2s左右

业务代码排查

故障发生后的第二天，有销售人员反映页面加载速度变慢，有时甚至需要好几秒的时间。

初步怀疑是故障处理时切换到了tair缓存而不是回退到本地缓存逻辑所导致的。然而，这里仍然存在疑问，为何单个页面的加载会如此缓慢，似乎像是每次请求都在大量循环调用缓存。

经过进一步定位，发现某个代理账号的行为确实符合上述假设。业务代码在查找指定角色下的组织时，会递归遍历所有的子组织。在最坏的情况下，一次页面请求可能导致多达1000+次的缓存或数据库访问。

结合当时数据库慢查询的趋势来看，这一猜测得到了验证。尽管业务流量并不大，但每次请求都相当于放大了1000倍，最终导致问题SQL被执行了1.5万次以上。同时，由于系统繁忙，其他正常的SQL查询也被排队等待，导致响应速度变慢。这些受影响的查询大多与基础用户组织权限相关，因此整个业务系统变得不可用。

数据库连接池？

之前提到应用连接池配置为15个连接，且应用本身的流量并不大。那么，究竟是什么原因导致整个数据库性能下降呢？

要解答这个问题，需要从SQL请求的链路入手进行分析。应用层通过tddl访问MySQL数据源，其中连接管理是在atom层完成的，利用druid进行连接池的管理。我们平时所说的tddl线程池，实际上指的就是druid连接池，这个配置是在diamond中维护的，通常由dba来设置。

对于应用而言，每台机器上运行着单个应用实例，maxPoolSize配置为15。由于数据库是单库单表结构，因此单个应用实例的最大连接数就是1*15。考虑到共有7台机器，所以所有应用实例的总连接数就是7*1*15=105。

上述计算仅是从应用维度出发推导出的连接数。在正常工作情况下，连接池的使用并不会达到maxPoolSize的上限。如果达到了这个上限，TDDL会抛出4103.ERR_ATOM_CONNECTION_POOL_FULL错误，这与我们之前观察到的现象是一致的。

那么问题又来了，为什么这么少的连接数会导致整个数据库性能下降呢？要知道，数据库server端的内部处理线程池与druid连接池是两个独立的概念，它们之间没有直接的关系。因此，我们需要深入研究数据库server端的处理逻辑。

还有一点，由于系统中有多个应用都在连接数据库，因此需要评估数据库的最大连接数是否能够满足所有应用实例的需求。换句话说，我们需要确保应用服务器总数乘以每个数据库实例上的分库数（atom）再乘以maxPoolSize的结果小于数据库的最大用户连接数（max_user_connections）。

数据库性能分析

案发时，数据库的CPU使用率达到了满负荷状态，与此同时，活跃连接数也在不断增加，数据库的响应时间也在逐渐增长。

活跃连接数是一个关键的指标，它表示当前数据库中正在执行SQL语句的会话数量，能够反映出数据库的繁忙程度。根据执行时间来判断，这个阈值通常设置得比较低，因为在正常情况下，一条SQL语句的执行速度应该很快，活跃会话数也会保持在较低水平。

通过深入分析，并结合慢SQL的详细记录，最终确定了问题的根源在于慢SQL的执行，这导致了大量的逻辑读操作。在服务器端，为了保护数据库实例的稳定运行引入了线程池机制，通过特定的参数进行控制，通常线程池中的线程数量维持在大约300个左右。

在正常情况下，由于SQL语句的执行速度较快，活跃会话数并不会很高，数据库能够保持正常的运行状态。然而，在出现慢SQL的情况下，由于每一条慢SQL语句都需要进行超过30万行的逻辑读操作，并且执行时间超过2秒，这导致线程执行速度变慢，线程池中的线程很快就被全部占用。换句话说，活跃会话数急剧上升，如上图所示，几乎达到了280个。

随着数据库CPU忙于执行慢SQL的逻辑读和排序等操作，活跃会话数的大幅增长以及数据库响应时间的飙升，其他应用的请求无法与数据库建立新的会话，导致应用请求超时。最终，TDDL层进入了快速失败状态，并抛出了4201.ERR_GROUP_NO_ATOM_AVAILABLE异常。

慢 SQL是如何导致CPU打满的

慢SQL之所以能够使数据库性能显著下降，其核心原因在于它引发的逻辑读操作过于频繁，进而导致了CPU资源几乎被完全占用。

这里有一个关键的疑问点，那就是读写操作通常被视为IO操作，但为何它们会导致CPU负载升高呢？在InnoDB的存储层中，存在一个专门用于缓存数据和索引信息的内存区域，称为buffer pool。这个buffer pool会将最近访问过的数据缓存到内存中，以提高访问速度。

通常所说的逻辑读，其实是指SQL语句在某一时刻需要访问多少个buffer pool中的内存页。与之相对应的，物理读则是指同一时间内需要从磁盘上读取多少个数据块。

理想情况下，buffer pool的大小设置得尽可能大，这样就能够减少进程因内存不足而引发的额外分页操作。当buffer pool的大小足够大时，整个数据库的大部分数据都可以被存储在内存中，从而大大提高读取效率。一旦数据被加载到buffer pool中，后续的读操作就无需再从磁盘上进行读取。

通过监控数据，确实发现InnoDB的缓存命中率达到了100%，这意味着并没有发生物理读的情况，也就是说数据库的高频访问数据都已经被完整地存储在内存中。

通过查看数据库的容量信息，也可以进一步佐证这一观点，数据加索引的总大小大约为6G，并没有达到数据库实例配置的8G内存上限。

因此，实际情况是数据库的所有数据都已经存在于内存中，并不会产生额外的IO操作。然而，CPU的性能却主要被消耗在了大量的内存数据扫表（逻辑读）操作上，尤其是在现场平均每次扫描都需要处理16万行数据的情况下。

这个结论让我想起了另一个类似的场景，那就是在线上环境中使用vi编辑器打开大文件时，会导致系统负载急剧升高，进而影响应用的正常运行。

这是因为vi在将文件加载到内存后，还需要进行一系列的转换和处理，比如将文件内容转换为内部的数据结构（如行、单词等），以及使用内部的脚本语言进行语法高亮显示等。这些操作都会消耗大量的内存和CPU时间。

虽然慢SQL只有一种类型，并且QPS（每秒查询率）也不是特别高（现场峰值约为100，平均约为20），但由于数据库采用了线程池机制，这些慢SQL语句很快就将活跃会话（即线程池中的线程）占满。

考虑到业务的整体QPS水平维持在3000左右，一旦线程池被慢SQL占满，后续即使是那些使用索引甚至是主键进行的正常SQL查询也不得不排队等待执行，最终导致了整个系统的性能雪崩。

升级数据库？

在针对慢SQL进行优化的同时，同时考虑到数据库实例的配置相对较低（8核CPU、8G内存、100G磁盘），考虑升级配置的可能性。

经过评估，判断升级配置并不会带来显著的性能提升。主要的理由如下：

首先，虽然当前实例配置了8核CPU，但MySQL集群的CPU隔离策略是开放的，这意味着在实际运行中，该实例可以充分利用到物理机上最多64核的CPU资源，因此无需进一步升级CPU配置。

其次，当前实例配置了8G内存。考虑到内存大小直接影响到了InnoDB的buffer pool大小，而根据前面的分析，当前的数据量并未达到8G的上限，因此升级内存也不会带来明显的性能提升。

最后，当前实例配置了100G的磁盘空间。根据业务的发展速度和当前的数据存储需求来看，这个配置已经足够使用一段时间，因此也无需进行磁盘升级。