一次紧张又刺激的线上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
- 大佬提供的思路:主要想实现的就是先单表分页出10条数据,然后再left join
这里主要看业务, 这里呢, 如果使用改进后的sql, 那就意味着, 无法通过t2 t3的where 赛选查询, 只能是单表操作
但是,后来我发现, 这两条sql速度微乎其微, 都没相差50毫秒, 也不知道是不是数据量的问题(6.8万)还是执行计划的问题,
ps,我不太会查看sql执行计划, 看不懂…
改进方案:
- 线上数据库必须添加索引,否则你都不需要程序睡眠都可以叫用户加钱优化,【捂脸】
- 这个问题一般也是线上环境才会出现, 本地那几条数据根本不会慢的【奸笑】
至此…
一次紧张又刺激的线上sql引发的惨案(CPU:100%)
今天收获挺大的, 每天进步一点点…
睡觉啦~~~~~~~~晚安