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

2021年3月1日16:01:52 发表评论 434 ℃

环境:

服务器:腾讯云2H4G

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

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

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

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

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

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

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

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

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

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

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

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

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

然后查看这些慢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也没有了。

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

innoDB相关监控指标也恢复了正常。

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

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

其实优化方案不是唯一的,需要根据实际情况,因为我的MySQL CPU只有1核,所以只能尽量把sql简单,少做一些逻辑运行,把逻辑交给程序,这样最大程度降低数据库的CPU消耗。

推荐阅读:

五条SQL语句优化法则,提高SQL执行效率

精选全网爆款产品,爆品销量榜单,全场低至0元起,还有更多云产品免费试用,带你0门槛上云

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: