前言

在某公众号偶尔看到一篇名为大表小技巧的技术文章，文章中提到了将删除转换为插入的技巧，具体是需要将原表的小部分数据插入至一张新表中，这里原博主提到了可以使用select into语句来完成旧表中部分数据插入到新表的操作。作为拥有一腔学习热情的技术小白，自然要了解下select into语句的用法及使用效率。
这里需要说明的是，MySQL 数据库不支持 SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT 。

一、select into语句与insert into select语句

select into 和 insert into select 语句作用几乎一样，均是用于数据表之间的数据复制，即将一张表的数据复制并插入至另一张表中。
select into语句和 insert into select之间最大的区别是select into语句会自动创建目标表，而insert into select语句则要求目标表必须存在。
insert into select语句用法

INSERT INTO table_name_2 SELECT * FROM table_name_1;

select into语句用法

SELECT *
INTO WebsitesBackup
FROM Websites
WHERE country='CN';

二、Insert into select 语句踩坑点

慎用Insert into select语句
在学习这俩语句的过程中浏览了一篇关于Insert into select踩坑点的文章觉得蛮有学习意义，于是记录下来。

事故背景

业务需要将表A的数据迁移到表B做数据备份，或者是公司的数据量较大，每天增量在百万左右，开始并未做分表分库，考虑到性能问题之后做分表分库需要将数据进行迁移。

同事李某接到了这个任务，于是他想出了这两个方案：

先通过程序查询出来，然后插入历史表，再删除原表。
使用 insert into select 让数据库 IO 来完成所有操作。
第一个方案使用的时候发现一次性全部加载，系统直接就 OOM 了，但是分批次做就过多 IO 和时间长，于是选用了第二种方案，测试的时候没有任何问题，开开心心上线，然后被开除。

经过复盘

第一个方案的伪代码如下

// 1、查询对应需要迁移的数据 
List<Object> list = selectData(); 
 
// 2、将数据插入历史表 
insertData(list); 
 
// 3、删除原表数据 
deleteByIds(ids);

从代码可以看出，系统OOM的原因很简单，将所有数据查询出来加载到内存，造成内存溢出。

第二个方案
为了维持表的性能，同时保留有效数据，决定保留10天的数据，差不多在表中保留1kw的数据。于是，该同事做了一个时间筛选的操作，直接执行以下sql语句。

 insert into select ... dateTime < (Ten days ago)...

如此一来，避免了分页查询，自然也就不会OOM。同时在测试环境特意建了1kw的数据进行模拟，测试结果顺利通过。于是决定在晚上8点正式上线，开始进行数据迁移。在大批量迁移的过程中，出现了大批用户支付失败的问题，随后停止了迁移。

事故分析

此处在本地新建一个精简版的数据库，并生成了100w的数据，用来模拟线上发生的事故情况

建立表结构

建立一张原先的今日订单表order_today

CREATE TABLE `order_today` (  
  `id` varchar(32) NOT NULL COMMENT '主键',  
  `merchant_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '商户编号',  
  `amount` decimal(15,2) NOT NULL COMMENT '订单金额',  
  `pay_success_time` datetime NOT NULL COMMENT '支付成功时间',  
  `order_status` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '支付状态  S：支付成功、F：订单支付失败',  
  `remark` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',  
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', 
   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间 -- 修改时自动更新',  
  PRIMARY KEY (`id`) USING BTREE,  
  KEY `idx_merchant_id` (`merchant_id`) USING BTREE COMMENT '商户编号'  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

建立一张新的表，订单记录表

CREATE TABLE order_record like order_today;

查询今日订单表order_today数据，结果如下图：

select count(*) from order_today

模拟数据迁移

将order_today表中8号之前的数据迁移至order_record表中，执行insert into select语句：

INSERT INTO order_record SELECT  
    *   
FROM  
    order_today   
WHERE  
    pay_success_time < '2020-03-08 00:00:00';

迁移的同时，另开一个窗口对今日订单表order_today进行插入数据操作，模拟线上环境用户正常下单访问服务。

如上三张图可看出，一开始模拟的用户下单操作仍能正常插入数据至order_today表中，但在后面出现了插入操作耗时23s的情况。

事故原因分析

在默认的事务隔离级别下，insert into order_record select * from order_today语句的加锁规则是：对order_record表加表锁，对order_today加行锁(逐条加锁). 对迁移sql的执行情况进行分析,发现执行过程中对order_today进行了全表扫描，也就是说在执行insert into order_record select * from order_today语句时，mysql会从上到下扫描order_today表中的所有记录并加锁，也就是对order_today加了表锁。这也就解释了为什么会出现插入失败/（表现在用户支付失败）的情况了。

为什么在测试环境下没有出现该问题？

测试环境下测试时虽然模拟了与正式环境相同的数据量，但是数据量并不代表就是真实的业务场景，显然测试环境的业务场景与线上存在差异，测试环境下缺少了迁移时存在对原表大量数据的插入这一业务场景。

解决方法

由于查询条件会导致order_today全表扫描，那么我们的核心问题变味了如何避免全表扫描。很简单，给查询条件中的字段加索引即可解决，走索引查询便不会出现扫描全表的情况导致锁表了，只会锁定符合条件的记录。最终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 tableA select * from tableB语句进行表的数据复制/迁移时，一定要确保tableB后面where，order或其他条件对应字段需要有对应的索引，从而避免tableB全表扫描被锁表的情况。

关于MySQL中Insert into select的学习及坑点

前言