Mysql-Insert插入过慢的原因记录和解决

场景

先来说下业务场景, A单位每天下发全量数据5万数据左右,我们需要将这5万条数据处理到主表中以及日志表中

我们现在的逻辑是,先将数据每次100条批量修改或者插入到主表中, 然后我们通过事件监听将每次插入或者修改的动作都记录到日志表中

问题: 因为我们插入日志表的时候是每次处理一条这样就导致了5万条日志数据,一个一个插入非常耗时,抱着能提升一点就提升一点的想法,我们看看插入还能优化吗?

先来说一下: Mysql中单条语句原子性的,所以高并发情况下不会有安全性问题

说说插入会慢的影响因素:

1. Mysql插入缓存(bulk_insert_buffer_size 默认是8M)
2. 每条数据在插入的时候都要验证是否违反表中的约束条件(主键,唯一等)
3. 构建新数据的索引
4. innodb中，数据通常都是先写缓存，再写事务日志，再写入数据文件
5. Mysql 每秒最大支持16384并发 ,但是默认只有151可以设置max_connections数量
6. 服务器性能,cpu,硬盘,网络, 网络数据传输 ,代码解析
7. 数据库事务影响

解决办法

先设置mysql的插入缓存bulk_insert_buffer_size=100M,确保一次能插入1000条数据
然后使用List方式记录需要插入的数据当达到1000条之后我们批量一次性插入insert into tablename values('xxx','xxx'),('yyy','yyy'),('zzz','zzz')...... 然后在清空List ,插入的过程中我们可以异步,不用等待结果的返回 ,通过上面的方案, 插入100万数据几十秒就完事了

为什么要这样解决呢?

因为批量插入其实就是访问一次数据量告诉数据库我需要执行这1000条的插入,而不是访问1000次数据库告诉数据库我要执行1条的插入所以使用批量插入大大的缩减客户端与数据库之间的连接、关闭等消耗 , 所以我们在上面的方案中使用一次提交批量数据的更新的语句,那么还有没其他的方式呢?

MySQL 的事务自动提交模式默认是开启的，其对 MySQL 的性能也有一定得影响。也就是说如果你执行1000 条插入语句，MySQL 就会提交 1000 次事物，这大大影响了插入数据的速度。我们还可以在代码中,手动开启事物也就是相当于希望事物内执行的语句要作为一个“不可分割”的整体去执行的任务当所有任务完毕后在提交事物,当然这样是没有批量插入快的但是也能节约不少时间的

来源地址：https://blog.csdn.net/weixin_45203607/article/details/126402379