上周五HaC我要上线，有一个脚本需要执行，执行前需要备份一个表。 运维 大佬：“这个表的备份为什么要这么久，，？？” 1秒过去了……2秒过去了…… 期间运营反馈系统出现大量订单超时情况。 大佬找到我，问：“你怎么备份的？” 我：“ insert into select * from 呀 ！” 大佬：“？？你是不是不想混了？”

又是被大佬嫌弃的一天，为了不卷铺盖走人，我决定去学习一下表备份的常见方法。

MySQL 一般我们在生产上备份数据通常会用到 这两种方法：

1. INSERT INTO SELECT
2. CREATE TABLE AS SELECT

注：本文仅针对MySQL innodb引擎，事务是可重复读RR， 数据库 版本为5.5

1.INSERT INTO SELECT

insert into Table2(field1,field2,...) select value1,value2,... from Table1

注意

（1）要求目标表Table2必须存在，并且字段field,field2…也必须存在

（2）注意Table2的主键约束，如果Table2有主键而且不为空，则 field1， field2…中必须包括主键

在执行语句的时候，MySQL是逐行加锁的（扫描一个锁一个） ，直至锁住所有符合条件的数据，执行完毕才释放锁。所以当业务在进行的时候，切忌使用这种方法。

在RR隔离级别下，还会加行锁和间隙锁

举个栗子吧：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;

执行

begin;
insert into t2(c,d) select c,d from t;

先不commit；这个语句对表 t 主键索引加了 (-∞,1] 这个 next-key lock

新开一个Navicat窗口，模拟新事务进入，此时执行下面这句sql就需要等待

insert into t values(-1,-1,-1);

锁住了

真就锁表了~无法写进去了，我终于知道为什么订单超时了。

背锅背锅。

如果实在要使用 INSERT INTO SELECT 这种方法，可以使用下面的方法进行优化：

1. 加条件，强制走索引，不要全表扫描，例如

INSERT INTO Table2 SELECT
    Table1 FORCE INDEX (create_time)
WHERE
    update_time <= '2020-03-08 00:00:00';

1. 加上limit 100,100 这种，限制数量

2. CREATE TABLE AS SELECT

create table as select 会创建一个不存在的表，也可以用来复制一个表。

1. create table t3 as select * from t where 1=2;
-- 创建一个表结构与t一模一样的表，只复制结构不复制数据；
2.create table t3 as select * from t ;
-- 创建一个表结构与t一模一样的表,复制结构同时也复制数据；(索引不会创建)