记录一次MySQL CPU持续100%解决历程

2021年3月1日16:01:52 发表评论 4,022 ℃

环境：

服务器：腾讯云2H4G

数据库：腾讯云TDSQL-C MySQL 1H1G

前段时间对某个网站进行了改版，然后某天发现网站内页访问有时候会等待很久，阿汤博客就分享一下解决这一问题的历程。

首先登录监控查看服务器各项指标都正常，然后怀疑是数据库的问题，登录数据库管理平台，实时查看各项指标都比较正常，但是每隔一段时间就会出现CPU100%的情况。

腾讯云的DBbrain也一直告警：致命高并发/压力请求

但是当MySQL CPU100%时，我查看了QPS、TPS没有明显变化，慢sql也无异常。

当我查看innoDB监控指标时，有大量的innoDB行插入和行读取和逻辑读和逻辑写相对于前几天有明显的上升趋势。

初步怀疑是频繁创建临时表导致，这种操作在数据库CPU 100%时应该有大量的慢sql才正常，于是去查看数据库配置文件，发现long_query_time设置的是10s，马上改成了0.5S，过了几分钟大量的慢sql就出来了。

然后查看这些慢sql列表，主要就是四类sql。

1、UNION多表连接sql

SELECT
t1.aa,
t1.bb,
t1.cc,
c.dd,
c.ee,
FROM
tabxx1 t1
INNER JOIN tt c ON c.xx = ?
UNION
SELECT
t2.aa,
t2.bb,
t2.cc,
c.dd,
c.ee
FROM
tabxx2 t2
INNER JOIN tt c ON c.xx = ?
UNION
SELECT
t3.aa,
t3.bb,
t3.cc,
c.dd,
c.ee
FROM
tabxx3 t3
INNER JOIN tt c ON c.xx = ?
ORDER BY
cc DESC
LIMIT ?

2、使用了MySQL DATE_SUB() 函数的sql

SELECT
aa,
bb
FROM
xx
WHERE
t = ?
AND date_sub(curdate(), INTERVAL ? DAY) <= date(creat_time)
ORDER BY
cc DESC
LIMIT ?

3、使用了!=的sql

SELECT
a
FROM
xx
WHERE
id != ?
ORDER BY
id DESC
LIMIT ?

4、sql简单，返回字段过多和未创建索引的sql

这些慢sql单次执行的时候，并不会慢，但是并发执行的时候，就会消耗数据库大量的CPU资源。既然找到的罪魁祸首，就开始优化。

慢sql一：

第一个sql 通过EXPLAIN 查看结果，发现全部都没有走索引，全部都是全表扫描。

网上查询说，除非确实需要去掉重复的行，否则尽量使用union all而不是union。因为union会自带distinct操作，代价很大。所以改成union all。

然后把sql语句简化，去掉INNER JOIN ，每张表只返回需要的逻辑条目，优化以后如下：

(
SELECT
id,
bb,
cc,
'xx1' AS c1,
'yy1' AS c2
FROM
tabxx1
ORDER BY
id DESC
LIMIT ？
)
UNION ALL
(
SELECT
id,
bb,
cc,
'xx2' AS c1,
'yy2' AS c2
FROM
tabxx2
ORDER BY
id DESC
LIMIT ？
)
UNION ALL
(
SELECT
id,
bb,
cc,
'xx3' AS c1,
'yy3' AS c2
FROM
tabxx3
ORDER BY
id DESC
LIMIT ？
)
ORDER BY
cc DESC
LIMIT ？

慢sql二：

因为使用了DATE_SUB函数，没办法走索引，每次都是全表扫描，设置了where第一个参数的索引，虽然EXPLAIN 显示走了索引，但是慢sql详情显示也是全表查询了。

于是就只能不使用DATE_SUB()函数，通过程序就行判断和排序。

因为需要进行两个字段排序。首选通过sql查询id字典和需要排序的字段一，筛选符合条件得到的结果，再通过程序进行第2个字段进行排序获取需要的条目，再通过ID去获取。

当然网上也有其他方案，但是我考虑尽量让sql语句简单化，因为本身mysql只有1核CPU，把更多逻辑运行交给程序。

慢sql三：

网上说尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

所以这里删除了id != ?条件，然后得到的数组，再去数组中剔除id=?的值。

慢sql四：

把一些简单sql where条件加上索引。

然后把一些返回字段多的sql，精简只获取id和需要进行逻辑运行的字段，通过程序逻辑运行以后获取符合条件的条目，再通过id去获取这些条目其他字段。

所有优化完以后，观察了半小时，发现MySQL CPU已经正常，慢sql也没有了。