mysql占用cpu利用率高-优化记录
场景描述:使用top命令发现mysql占用cpu利用率非常高,相关表的数据量已经达到百万级别;
解决方法
1、show full processlist;
发现存在大量sleep线程;而大量sleep线程会占用连接数,当超过max_connections后无法建立新连接、并且可能仍有一些内存未释放,数量太多的话,是会消耗大量无谓的内存的,从而影响性能;
// 设置最大连接数以及最长等待时间 (注这部分配置可以在代码里也可以在mysql配置文件里)
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections=5000;
show global variables like 'wait_timeout'
SET GLOBAL wait_timeout=6000;
2、发现有些sql一直在执行,虽然执行时间很短
需要在代码中定位到该SQL语句,使用explain 关键字对其分析,看是否是索引失效了;后续再看如何优化SQL
https://www.cnblogs.com/ReturnOfTheKing/p/17843900.html
3、慢查询SQL日志
// 检查是否开启了慢查询
show variables like '%slow_query_log%'
set GLOBAL slow_query_log = ON/OFF
如何后台程序使用了druid数据源,也可以通过后台可视化界面更直观查看
4、去业务逻辑代码里找出最频繁执行的相关sql,进行分析
本场景里业务平台没有任何操作mysql占用cpu的利用率就高飙,结合实际场景,发现大概率是数据上报相关代码出了问题,因为只有数据上报相关业务会一直执行;
然后在顺着这个思路在继续细分,看具体是哪个业务逻辑里的相关sql出现了问题;
5、SQL优化
// 原SQL
SELECT
id,
device_no,
type,
count,
create_time,
update_time
FROM
t_device_warning
WHERE
device_no = '4654987456489'
AND type = 3
AND date_add( update_time, INTERVAL 1 MINUTE ) > '2023-01-11 21:30:41'
ORDER BY
update_time DESC
LIMIT 1
注意查询条件中的时间比较:它是将表中update_time这一列的值加一分钟后再与某一个某一个常量值进行比较,这样明显效率更低,更何况当数据量非常大的时候;(违法了索引列不要参与计算原则)
所以建议是在进行比较时尽量不要采取修改表中数据的策略,而是后端程序将'常量值'做相应的修改后再进行传输,本例修改如下
SELECT
id,
device_no,
type,
count,
create_time,
update_time
FROM
t_device_warning
WHERE
device_no = '4654987456489'
AND type = 3
-- 后端处理完常量值后再进行传输
AND update_time > '2023-01-11 21:30:41'
ORDER BY
update_time DESC
LIMIT 1
后续可以再使用explain关键字判断是否走了相应索引;此处查询条件有多个,故可以尝试使用联合索引,但需注意字段的先后顺序(最左前缀匹配原则);
在建立联合索引时对字段进行先后排序时,原本应该是需要将对目标表的所有相关SQL都找出来进行分析的,以便达到最优效果;(本次实战是本人第一次进行相关优化,有些运气成分)
尽量使用扩展索引(修改原有索引),取代新建索引
索引相关操作
// 新建单列索引
CREATE INDEX idx_update_time ON t_device_warning (update_time);
// 联合索引
CREATE INDEX idx_devNo_type_update ON t_device_warning (device_no,type,update_time);
// 显示索引
show index from t_device_warning
// 删除索引
DROP INDEX idx_update_time ON t_device_warning
参考文章:
【1】https://blog.csdn.net/vip_linux/article/details/9209485
【2】https://blog.csdn.net/qq_43493747/article/details/121577734
【3】https://app.yinxiang.com/fx/bf7839b3-5f7b-4212-9f7d-5f5577e952ea