记一次MySQL CPU被打满的SQL优化案例分析

2023-08-22 346

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

可观测可视化 Grafana 版，10个用户账号 1个月

任务调度 XXL-JOB 版免费试用，400 元额度，开发版规格

函数计算FC，每月15万CU 3个月

简介： 记一次MySQL CPU被打满的SQL优化案例分析

背景介绍

系统中有个公告模块，当用户登录后，根据用户所属机构查询公告列表，同时公告列表中需要展示出该用户对公告的阅读状态及阅读时间。公告（bulletin）、公告接收者（bulletin_receiver）、公告阅读者（bulletin_reader）定义及关联关系如下：

应用使用的数据库连接池是druid，数据库是阿里云RDS MySQL 5.6（16c64g）

问题梳理

经过梳理，事件时间线大概是这样的：

14:45 GetConnectionTimeoutException

14:47 钉钉报警

14:59 CommunicationsException

问题分析

应用分析

GetConnectionTimeoutException

arthas vmtool

我们分析问题，通常会碰到日志记录不够排查问题的情况，此时可以通过arthas相关命令进行分析。比如我们想看一下druid连接池统计信息。

CommunicationsException

出现该异常几个典型场景是：

数据库连接空闲时间超过了MySQL服务器配置的wait_timeout
MySQL server versions like 5.6.25 and earlier or 5.7.5 and earlier，客户端连接属性useSSL默认是false；MySQL server versions like 5.6.25+ or 5.7.5+，客户端连接属性useSSL默认是true。默认useSSL=true的MySQL server版本，客户端连接属性还需要配置其他额外的连接属性，如果没有配置会抛出_CommunicationsException异常。_
客户端发送请求后，服务端比较忙，一直没有回复客户端的请求导致超时。

从上面时间线看，当出现该异常的时候，MySQL CPU已经被打满，无法及时处理客户端请求，导致客户端请求超时。

系统负载

业务指标

业务入口请求量比较平缓，没有波动
系统GC情况正常… …

数据库分析

数据库侧没有打开performance_schema，主要排查操作：

show processlist：查看数据库会话信息
select * from information_schema.innodb_trx
查询慢SQL：没有慢SQL

可疑SQL：

SELECT A.ID AS ID,
   A.BULLETIN_TYPE AS BULLETIN_TYPE,
   A.BULLETIN_CONTENT AS BULLETIN_CONTENT,
   A.STATUS_CODE AS REMARKS,
   B.READ_STATUS AS STATUS_CODE,
   B.READ_TIME AS GMT_MODIFIED
FROM(
    SELECT C.*
      FROM BULLETIN C
     WHERE C.ID IN(
                SELECT D.BULLETIN_ID
                  FROM BULLETIN_RECEIVER AS D
                 WHERE D.RECV_CODE IN('45346600', '45302600')
                 GROUP BY D.BULLETIN_ID)
 AND C.STATUS_CODE= 'RELEASE'
 AND C.BULLETIN_TYPE= 2
 AND C.VALID_DATE_END> '2022-11-16 00:00:00'
 AND C.VALID_DATE_BEGIN<= '2022-11-17 00:00:00') AS A
LEFT JOIN BULLETIN_READER AS B ON A.ID= B.BULLETIN_ID
 AND B.READER_ID= 1990234
WHERE B.IS_DELETED IS NULL OR B.IS_DELETED= '0'
LIMIT 0,10

explain 执行计划：

似乎没有啥问题，而且SQL执行的还可以。

一时没有好的办法快速解决，扩容了两台从库用来分担读的流量，从库上线后一切还算正常，于是恢复了部分数据，哪知过了两天CPU再次被打满… …

问题分析到这里，似乎已经不是资源的问题了… …

解决办法

SQL优化

话说SQL里尽量不要使用IN，可以使用EXISTS或JOIN替代，于是我们改了下SQL。

EXISTS

SELECT A.ID AS ID,
   A.BULLETIN_TYPE AS BULLETIN_TYPE,
   A.BULLETIN_CONTENT AS BULLETIN_CONTENT,
   A.STATUS_CODE AS REMARKS,
   B.READ_STATUS AS STATUS_CODE,
   B.READ_TIME AS GMT_MODIFIED
FROM(
    SELECT C.*
      FROM BULLETIN C
     WHERE EXISTS (
                SELECT 1
                  FROM BULLETIN_RECEIVER AS D
                 WHERE D.BULLETIN_ID=C.ID AND D.RECV_CODE IN('45346600', '45302600')
                 )
 AND C.STATUS_CODE= 'RELEASE'
 AND C.BULLETIN_TYPE= 2
 AND C.VALID_DATE_END> '2022-11-16 00:00:00'
 AND C.VALID_DATE_BEGIN<= '2022-11-17 00:00:00') AS A
LEFT JOIN BULLETIN_READER AS B ON A.ID= B.BULLETIN_ID
 AND B.READER_ID= 1990234
WHERE B.IS_DELETED IS NULL OR B.IS_DELETED= '0'
LIMIT 0,10

JOIN

除了将IN改为JOIN，同时将select中的公告内容字段去掉，因为多数场景下公告列表不需要内容字段。

SELECT A.ID AS ID,
   A.BULLETIN_TYPE AS BULLETIN_TYPE,
   A.STATUS_CODE AS REMARKS,
   B.READ_STATUS AS STATUS_CODE,
   B.READ_TIME AS GMT_MODIFIED
FROM(
    SELECT C.ID AS ID,C.BULLETIN_TYPE AS BULLETIN_TYPE,C.STATUS_CODE AS STATUS_CODE
      FROM BULLETIN C
      JOIN (
              SELECT D.BULLETIN_ID AS ID
                FROM POR_BULLETIN_RECEIVER AS D
               WHERE D.RECV_CODE IN('45346600', '45302600') 
               GROUP BY D.BULLETIN_ID
              ) AS E ON C.ID=E.ID
 AND C.STATUS_CODE= 'RELEASE'
 AND C.BULLETIN_TYPE= 2
 AND C.VALID_DATE_END> '2022-11-16 00:00:00'
 AND C.VALID_DATE_BEGIN<= '2022-11-17 00:00:00') AS A
LEFT JOIN BULLETIN_READER AS B ON A.ID= B.BULLETIN_ID
 AND B.READER_ID= 1990234
WHERE B.IS_DELETED IS NULL OR B.IS_DELETED= '0'
LIMIT 0,10

验证

无论执行计划怎么样，还是实际验证下比较放心。

结论

IN、EXISTS性能几乎无差别，每次请求平均耗时56ms，JOIN每次请求平均耗时1ms。

服务拆分

公告模块目前与核心模块耦合有点紧，当公告模块出现问题的时候会直接影响到核心模块，需要将公告模块进行拆分。

公告内容字段存储优化

方案一

将公告内容字段独立到一个表中

方案二

目前公告字段是存储在MySQL中，大小从60B 到 25KB不等，从上面分析看这个字段对系统的伤害非常大，可以考虑将该字段放到OSS存储，数据库中保存OSS路径的方式。

完善监控

当发现我们掌握的信息不足以分析问题的时候，说明需要完善现有的监控指标

数据清理

根据数据保存规定，将过期数据及时清理出去。