41 | 怎么最快的复制一张表

如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用insert ... select 语句即可实现。

为了避免对源表加读锁，更稳妥的方案是先将数据写到外部文件，然后再写回目标表。有两种方法。

创建一个表db1.t，插入1000行数据，同时创建一个相同结构的表db2.t。

如果要把db1.t中a>900的数据导出来，插入db2.t中。

mysqldump方法

使用mysqldump命令将数据导出成一组insert语句。可以使用下面的命令：

mysqldump -h$host -p$port -u$user --add-locks=0 --no-create-info --single-transaction  --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql

把结果输出到临时文件。

命令中主要参数含义如下：

1. --single-transaction的作用是，在导出数据的时候不需要对表db1.t加锁，而是使用START TRANSACTION WITH CONSISTENT SANPSHOT的方法；
2. --add-locks设置为0，表示在输出文件结果里，不增加“LOCK TABLES t WRITE;”;
3. --no-create-info的意思是，不需要导出表结构；
4. --set-gtid-purged=off表示的是，不输出GTID相关的信息；
5. --result-file指定了输出文件的路径，其中client表示生成的文件是在客户端机器上的。

导出结果类似下面：

如果希望生成的文件中一条insert语句直插入一行的话，可以在执行mysqldump命令的时候，加上参数--skip-extended-insert。

然后，通过下面命令，将这些insert语句放到db2库里执行。

mysql -h127.0.0.1 -p13000 -uroot db2 -e "source /client_tmp/t.sql"

source是一个客户端命令，mysql客户端执行这个命令的流程是这样的：

1. 打开文件，默认以分号为结尾读取一条条的SQL语句；
2. 将SQL语句发送到服务端执行。

导出csv文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

使用这条语句，需要注意：

1. 这条语句会将结果保存在服务端。如果执行命令的客户端和MySQL服务端不在同一个机器上，客户端机器的临时目录下是不会生成t.csv文件的。
2. into outfile指定了文件的生成位置（/server-tmp/），这个位置必须受参数secure_file_priv的限制。参数secure_file_priv的可选值和作用分别是：

         2.1 如果设置为empty，表示不限制文件生成的位置，这是不安全的设置；

         2.2 如果设置为一个表示路径的字符串，就要求生成的文件只能放在这个指定的目录，或者它的子目录；

         2.3 如果设置为NULL，就表示禁止这个MySQL实例上执行select ... into outfile操作。

     3.这条命令不会帮你覆盖文件，因此你要确保/server_tmp/t.csv这个文件不存在，否则执行语句时就会因为有同名文件的存在而报错。

     4.这条命令生成的文本文件中，原则上一个数据行对应文本文件的一行。但是，如果字段中包含换行符，在生成的文本中也会有换行符。不过类似换行符、制表符这类符号，前面都会跟上“\”这个转义字符，这就可以跟字段之间、数据行之间的分隔符区分开。

得到.csv文件后，可以用下面的load data命令将数据导入到目录表db2.t中。

load data infile '/server_tmp/t.csv' into table db2.t;

这条语句的执行流程如下：

1. 打开文件/server_tmp/t.csv，以制表符（\t）作为字段间的分隔符，以换行符（\n）作为记录之间的分隔符，进行数据读取；
2. 启动事务；
3. 判断每一行的字段数与表db2.t是否相同：

          若不相同，直接报错，事务回滚；

          若相同，则构造成一行，调用InnoDB引擎接口，写入到表中。

      4.重复步骤3，直到/server_tmp/t.csv整个文件读入完成，提交事务。

物理拷贝法

前面都是逻辑导数据方法。

物理导数据方法？比如，直接把db1.t表的.frm文件和.ibd文件拷贝到db2目录下，是否可行呢？

答案是不行。

因为，一个InnoDB表，除了包含这两个物理文件外，还需要再数据字段中注册。直接拷贝，数据字典中没有db2.t这个表，系统是不会识别和接受它们的。

在MySQL 5.6版本引入了可传输表空间的方法，可以通过导出+导入表空间的方式，实现物理拷贝的功能。

假设现在的目标是在db1库下，复制一个跟表t相同的表r，具体的执行步骤如下：

1. 执行create table r like t，创建一个相同表结构的空表；
2. 执行alter table r discard tablespace，这时候r.ibd文件会被删除；
3. 执行flush table t for export，这时候db1目录下会生成一个t.cfg文件；
4. 在db1目录下执行cp t.cfg r.cfg; cp t.idb r.ibd; 这两个命令（需要注意，拷贝得到的两个文件，MySQL进程要有读写权限）；
5. 执行unlock tables，这时候t.cfg文件会被删除；
6. 执行alter table r import tablespace，将这个r.ibd文件作为表r的新的表空间，由于这个文件的数据内容和t.idb是相同的，所以表r中就有了和表t相同的数据。

这个流程，需要注意：

1. 在第三步执行完flush table命令之后，db1.t整个表处于只读状态，直到执行unlock tables命令后才释放读锁；
2. 在执行import tablespace的时候，为了让文件里的表空间id和数据字典中的一致，会修改r.ibd的表空间id。而这个表空间id存在于每一个数据页中。因此，如果是一个很大的文件（比如TB级别），每个数据页都需要修改，所以你会看到这个import语句的执行时需要一些时间的。当然，如果是相比于逻辑导入的方法，import语句的耗时是非常短的。

 

上一篇：40 | insert语句的锁为什么这么多？

下一篇：42 | grant之后要跟着flsh privileges吗？