在进行MySQL数据备份迁移时,很多人为了避免网络IO的开销而选用insert into ...... select 进行数据迁移备份,但你是否知道这种做法会存在那些隐患呢? 所以本文就以一个简单的功能示例为大家演示一下insert into ...... select 可能引发的问题和解决方案。
问题复现
这里为了演示问题,笔者生成了一张带有500w数据的数据表,对应DDL语句如下:
使用count语句查看数据量:
稍微久等了一小会,输出语句如下,可以看到一张表数据刚刚好达到500w:
同样的我们给出备份迁移表的DDL,表结构是一样的,唯一区别就是表名后缀多了个bak:
此时我们使用数据库连接工具在会话窗口,执行如下迁移语句:
然后我们再写一段程序模拟插入:
从输出结果来看,一开始插入并不是很耗时,基本都是毫秒级完成,但是随着时间的推移,插入的耗时逐渐增加,最慢的一次数据插入竟然花费了1分多钟:
原因剖析
我们不妨使用如下语句查看一下执行计划:
可以看出无论是insert还是select都是走全表扫描,因为select查询没有走索引导致select子句的执行过程会针对整张表从上到下的扫描进行一个逐步锁(S锁)的过程,随着时间的推移它最终就会变为全表锁。
而insert语句也因为对于插入数量未知而上全表锁,进而长期持有auto-inc锁,当然因为insert的表是用于迁移备份数据的,auto-inc锁的长时间持有对于业务来说影响不大。
而select则不同,select查询操作上的是读锁也就是S锁,这使得其他事务针对扫描到的数据只能上S锁不能上X锁即写锁。
通过执行计划可以看到我们的操作是全表扫描ALL,这也就意味着该查询逐步上S锁,导致一段时间后,整张表都被锁住,使得我们的新的会话的插入语句的事务无法提交。进而导致大量连接数阻塞积压,各种超时问题也就随之诞生,严重一点就很可能导致整个业务线瘫痪:
解决方案
所以如果我们希望迁移时不锁住全表,可以在指定在每次迁移时指定一个范围,所以我们针对时间字段增加索引,通过缩小范围加索引查询避免全表锁:
然后迁移的sql改为:
查看执行计划发现,select走了range索引,避免全表扫描,解决了上述的风险:
小结
由此可以得出,再使用数据insert into ...... select进行数据迁移时,无比考虑读写锁的工作机制,以及迁移可能导致的锁的粒度和范围,只有精确的评估风险点才能保证功能上限不影响正常业务的工作。