一次紧张又刺激的线上sql引发的惨案(CPU:100%)

最新推荐文章于 2024-06-03 15:15:02 发布

IT加载中

最新推荐文章于 2024-06-03 15:15:02 发布

阅读量377

点赞数

分类专栏： sql 优化文章标签： mysql 线上环境优化索引

本文链接：https://blog.csdn.net/jinglongsource/article/details/100430659

版权

sql 同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

优化

1 篇文章 0 订阅

订阅专栏

一次紧张又刺激的线上sql引发的惨案(CPU:100%)

故事的开始是这样子的，系统需要导入一批量数据，大概单表的话6万8左右，数据量不算大，由于还有关联关系，所以还在两张表中存储对应的关联

-------------坑1

由于使用的是程序导入，批量插入， for循环中构建对应到三张表的依赖关系, 外层使用3个List< Entity >存储, 当数据量达到1000, saveBatch一下, 程序看着好像都没问题，但是由于省事嘛，直接在一个方法体处理完了所有的逻辑，包括saveBatch的操作, 然后写完了整体代码, 检查一遍, 发现是执行多条(更新 / 添加)的事务, 因此，很负责得在service对应的方法添加了一个注解@Transaction(rollbackFor = Exception.class)

在这里插入图片描述

在这里补充一点, 因为Spring的默认的事务规则是遇到运行异常（RuntimeException）和程序错误（Error）才会回滚。也就是说只有是发生了RuntimeException或者Error级别的报错, 事务才会生效，否则事务不生效的，所以这里添加了rollbackFor = Exception.class，这是之前很早踩过的一个坑 Transaction不生效

好像没毛病是不是?

代码提交给了大佬，大佬在本地测试了一遍，没问题，一切好像还很正常。。。
大佬没时间细看, 所以本地测试通过就是干了[奸笑]

直到部署到线上环境，问题就来了。。。。

大佬说一直没有插入数据…我的天什么情况? {导入过去了5分钟}
什么时候有一个结果? 呜呜呜~~欲哭无泪啊!!!

在这里插入图片描述

我一下子也是懵逼的, 没道理呀, 我明明1000条插入一次呀!!! 后来看了一下代码发现，在外层添加了事务Transaction，导致了必须是6万8要么成功。要么失败，这就有点吓人了。

时间又过了十几分钟，数据库的数量依旧没有动静，难道崩了？

还好导入数据的时候添加了日志记录，发现日志打印正常，一直在输出，

这个时候内心是崩溃的，只能在心里默默祈祷不要出错了

虽然最终的结果正常导入了，但是真的非常不建议这样子玩，太恐怖了…

总结坑一：

事务范围过大，导致代码锁，以及表锁
数据库连接池的消耗殆尽，因为启用了事务，连接池一起占用着。。。（我记得我看过一篇文章是这样子说的，标题是：你的接口真的支持高并发吗？）
数据一致没有动静，不知道执行情况（幸好多写了几行代码，记录了一下日志，可以查看日志得知程序还在正常跑）
数据量过大，可能直接导致服务器(或者mysql)崩溃，事务的执行是在数据库级别，因此，所以的插入都缓存在mysql中，可能随时崩啊 (这个是我个人目前的知识观点，可能具体知识点不正确，但是你想一下，要么成功要么失败，数据没进入库？哪去哪了？肯定有地方缓存了，因此数据量过大会有崩溃的危险…)

改进方案:

缩小事务范围即可, 比如说, 一千条批量插入一次的, 抽取出来一个方法, 在这个方法添加事务, 然后就可能看到1000 1000的入库, 心里踏实很多的…

题外话, 这个东西还真的实在实际生产上才会发现，除非你有类似的经历, 请说出你的故事…

-------------坑2

数据库导入数据了, 关联关系也起来了，但是页面的数据一致出不来，什么情况呢，查看代码，也没发现有什么问题呀，就是一个简单分页的sql, 在这里我贴了类似的sql出来

SELECT t1.*, t3.binding_time FROM yycpark_member_import_temp1 t1
LEFT JOIN yycpark_member_import_temp2 t2 ON t2.member_number = t1.member_number
LEFT JOIN yycpark_member_import_temp3 t3 ON t3.member_number = t2.member_number limit 100, 10

这里的关系是如下图===>>A大概数据量6万8, B数据量大概8万, C大概数据量6万8
在这里插入图片描述
但是就是出不来, 一直卡着,
后来大佬突然说是不是没有索引? 我的天呐…~~我仿佛看到了mysql在呻吟~~

但是这个时候, 服务器开始出现异常了

CPU%,居高不下,
mysql占用率极高
打不开A表，其他表可以打开

因此表都打不开, 添加索引失败,

在这个过程中, 我们重启了很多次微服务, 发现问题依然存在，甚至都怀疑是不是坑1，事务没释放~~

题外话, 这里说一下公司有金主爸爸…买的是阿里云的数据库, ，这个时候价值就体现了，阿里云后台数据库监测中心，超级流弊，直接定位到了sql位置，然后发现问题的sql是 ( 类似于下面sql [ 我本地测试的sql ] ，在这里不可能贴实际代码的呢~)

SELECT COUNT(1) FROM yycpark_member_import_temp1 t1
LEFT JOIN yycpark_member_import_temp2 t2 ON t2.member_number = t1.member_number
LEFT JOIN yycpark_member_import_temp3 t3 ON t3.member_number = t2.member_number

发现这个会话一直没有关闭，由于之前还以为没请求到后端，然后还猛点了一波，最终发现了大概60 70这这样子的会话…

也就是问题就是这个了sql，但是这个这么简单的sql, 为什么会执行那么久呢?
这个就是一个分页的SQL，然后执行查询count的操作呀, 为什么卡住呢?

~~这个时候，系统远方现场的大佬又发话，怎么回事呀，数据还没出来。。。用户等着用呢~~

在这里，我使用了本地测试了一下，没有索引的三张表

A大概数据量6万8, B数据量大概8万, C大概数据量6万8
在这里插入图片描述

现象重现了，一直出不来，由于我不敢运行多个，这里只跑了一条sql, 我担心我的小本本炸…
服务器上可是有60 70这个会话, 因此CPU100%,一直没有下降趋势

后来的处理方式是:

重启mysql服务, 添加对应的索引

然后我本地测试添加索引

可以参考
https://www.cnblogs.com/daimaxuejia/p/7865300.html
https://www.cnblogs.com/sweet521/p/6203360.html

使用CREATE 语句创建索引
普通索引
CREATE INDEX index_name ON table_name(column_name1,column_name2);

非空索引
CREATE UNIQUE INDEX index_name ON table_name (column_name);

主键索引
CREATE PRIMARY KEY INDEX index_name ON table_name (column_name);

使用ALTER TABLE语句创建索引
alter table table_name add index index_name (column_list);
alter table table_name add unique (column_list);
alter table table_name add primary key (column_list);

删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

CREATE UNIQUE INDEX idx_t1_member_number ON yycpark_member_import_temp1 (member_number);
CREATE UNIQUE INDEX idx_t2_member_number ON yycpark_member_import_temp2 (member_number);
CREATE UNIQUE INDEX idx_t3_member_number ON yycpark_member_import_temp3 (member_number);

然后再次执行sql，瞬间出来了。。。

SELECT COUNT(1) FROM yycpark_member_import_temp1 t1
LEFT JOIN yycpark_member_import_temp2 t2 ON t2.member_number = t1.member_number
LEFT JOIN yycpark_member_import_temp3 t3 ON t3.member_number = t2.member_number

如下图
在这里插入图片描述
速度就上来啦 ,问题貌似就解决了，但是这里其实还可以优化sql。。。
业务的sql是(类似于如下)

SELECT t1.*, t3.binding_time FROM yycpark_member_import_temp1 t1
LEFT JOIN yycpark_member_import_temp2 t2 ON t2.member_number = t1.member_number
LEFT JOIN yycpark_member_import_temp3 t3 ON t3.member_number = t2.member_number limit 100, 10

改进之后的sql

SELECT t1.*, t3.binding_time FROM ( SELECT * FROM yycpark_member_import_temp1 t1 LIMIT 100, 10 ) t1
LEFT JOIN yycpark_member_import_temp2 t2 ON t2.member_number = t1.member_number
LEFT JOIN yycpark_member_import_temp3 t3 ON t3.member_number = t2.member_number