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

淡淡的烟草味﹌ 2024-04-17 23:40 153阅读 0赞

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

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

# \-------------坑1 #

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70]

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

好像没毛病是不是?

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

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

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 1]

我一下子也是懵逼的, 没道理呀, 我明明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**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 2]  
但是就是出不来, 一直卡着,  
后来大佬突然说是不是没有索引? 我的天呐…我仿佛看到了mysql在呻吟  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 3]  
但是这个时候, 服务器开始出现异常了

*  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**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 4]

现象重现了，一直出不来， 由于我不敢运行多个， 这里只跑了一条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，瞬间出来了。。。

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

改进之后的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

*  大佬提供的思路：主要想实现的就是**先单表分页出10条数据，然后再left join**

这里主要看业务, 这里呢, 如果使用改进后的sql, 那就意味着, 无法通过t2 t3的where 赛选查询, 只能是单表操作

但是，后来我发现， 这两条sql速度微乎其微, 都**没相差50毫秒**, 也不知道是不是数据量的问题(6.8万)还是执行计划的问题，

ps,我不太会查看sql执行计划, 看不懂…

### 改进方案: ###

*  线上数据库必须添加索引，否则你都不需要程序睡眠都可以叫用户加钱优化，【捂脸】
 *  这个问题一般也是线上环境才会出现， 本地那几条数据根本不会慢的【奸笑】

至此…

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

今天收获挺大的， 每天进步一点点…

睡觉啦~~~~~~~~晚安

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/d167b7e454ec404885bd698adedf1f80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/cc98848a0b374680a19f00ebec173ed1.jpg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/c8d6b1a6c5144fb086ab1d72c471cdc9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/be0459f1394e40f9a9d04a89b54198d5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/4b306494a68e4af4870e04de9d9681a8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppbmdsb25nU291cmNl_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/2ecd59eaa52841f49f410c814702339a.png