当前位置：首页 > 资讯 > 网络资讯

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

网络资讯
来源：csdn
编辑：nanost
时间：2023-02-05 19:53
阅读：227

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

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

环境：

服务器：腾讯云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%解决历程

初步怀疑是频繁创建临时表导致，这种操作在数据库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也没有了。

全部评论(0)