在开发过程中,可能会遇到这样的情况,需要将表A的数据迁移到表B中去做一个备份。我们可以使用insert into select实现,这样就可以避免使用网络I/O,直接使用SQL依靠数据库I/O完成,这样简直不要太棒了。但是,这样也会有一个问题:那就是锁表。
模拟迁移
把order_today表8号之前的数据都迁移到order_record表中去。
INSERT INTO order_record
SELECT
*
FROM
order_today
WHERE
pay_success_time < '2020-03-08 00:00:00';
在默认的事务隔离级别下:insert into order_record select * from order_today 加锁规则是:order_record表锁,order_today逐步锁(扫描一个锁一个)。通过观察迁移sql的执行情况发现order_today是全表扫描,也就意味着在执行insert into select from 语句时,mysql会从上到下扫描order_today内的记录并且加锁,这样一来不就和直接锁表是一样了。
解决方案
由于查询条件会导致order_today全表扫描,什么能避免全表扫描呢,很简单嘛,给pay_success_time字段添加一个idx_pay_suc_time索引就可以了,由于走索引查询,就不会出现扫描全表的情况而锁表了,只会锁定符合条件的记录。
最终的sql
INSERT INTO order_record SELECT
*
FROM
order_today FORCE INDEX (idx_pay_suc_time)
WHERE
pay_success_time <= '2020-03-08 00:00:00';
总结
使用insert into tablA select * from tableB语句时,一定要确保tableB后面的where,order或者其他条件,都需要有对应的索引,来避免出现tableB全部记录被锁定的情况。
force index()指令可以指定本次查询使用哪个索引!一条sql只会用到一个索引,mysql优化器会计算出一个合适的索引,但是这个索引不一定是最好的。force index()指令可以避免MySql优化器用到了一个低效的索引。