2021年3月1日16:01:52 发表评论 3,155 ℃
环境:
服务器:腾讯云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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | 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
1 2 3 4 5 6 7 8 9 10 11 | SELECT aa, bb FROM xx WHERE t = ? AND date_sub(curdate(), INTERVAL ? DAY ) <= date (creat_time) ORDER BY cc DESC LIMIT ? |
3、使用了!=的sql
1 2 3 4 5 6 7 8 9 | 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 ,每张表只返回需要的逻辑条目,优化以后如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | ( 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也没有了。