开发过程中需要把一个已有500万条记录的表数据同步到另一个新表中，刚好体验下Mysql官方推荐的大数据迁移的方案：SELECT INTO OUTFILE，LOAD DATA INFILE

Mysql 关于导出-导入文件的方式处理数据的官方文档地址：Mysql官方文档

---

本机配置：

Win7 64位系统

内存： 16G

处理器：Intel i5-4460  3.2GHz

Mysql 5.7

导出到txt的语法：

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'

  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

  LINES TERMINATED BY '\n'

  FROM test_table;

首先把现有表数据导出到txt文件中：

SELECT * INTO OUTFILE 'test_9.txt' FIELDS TERMINATED BY ';' FROM f_item s WHERE s.status=1;



我们看到导出510W+的数据到txt，耗时53秒，导出的txt文件大小1.18G左右

导入的命令语法：

LOAD DATA INFILE Syntax

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

    [REPLACE | IGNORE]

    INTO TABLE tbl_name

    [PARTITION (partition_name [, partition_name] ...)]

    [CHARACTER SET charset_name]

    [{FIELDS | COLUMNS}

        [TERMINATED BY 'string']

        [[OPTIONALLY] ENCLOSED BY 'char']

        [ESCAPED BY 'char']

    ]

    [LINES

        [STARTING BY 'string']

        [TERMINATED BY 'string']

    ]

    [IGNORE number {LINES | ROWS}]

    [(col_name_or_user_var

        [, col_name_or_user_var] ...)]

    [SET col_name={expr | DEFAULT},

        [, col_name={expr | DEFAULT}] ...]

然后新建一个表来转存储这些数据，使用LOAD DATA INFILE 的方式导入：

LOAD DATA INFILE 'fxxxx_9.txt' INTO TABLE fxxxxtest FIELDS TERMINATED BY ';' (id, info_id, field_id, element_id, TYPE, @a, @a, @a, @a, @a, VALUE, @a, @a, create_user_id, create_time, @a, @a, @a, @a);

我们看到导入总共510W+的数据，总共耗时6分51秒。

注意到上面导入的时候，过滤了一些字段，这些字段在导出的原始表中有，但是新表中不需要，所有通过这种声明字段列表的方式，以@的方式过滤掉对应的字段。

这里还有个小插曲：

在我这个需求中还不是单纯的转移数据，还需要把新表里面的新增的几个字段内容填充，依赖的是另外一个有150多万条记录的关联表里面的字段内容。我一开始是按照上面的方式迁移原始数据过去新表后，在SQLyog工具里面直接通过命令来同步的，类似以下命令：

update table_pre pt, table_union ut set pt.A1 = ut.A1, pt.A2=ut.A2 where pt.union_id=pt.id and pt.status=1;

结果就这个同步过程（table_pre表数据510万+，table_union数据150万+）总共耗时1个多小时…

前面这方案总共耗时将近1.5 小时

后来想了下，如果直接在SELECT INTO OUTFILE 的时候就把相关联的字段一起保存到txt文件，然后再通过LOAD DATA INFILE 的方式一次性导入，岂不是可以一步到位？那这种实现耗时会不会更高效？

结果按照这种思路重新试了一次结果如下：

调整优化方案耗时：

导出到txt：约23分钟

导入表中：约7分钟

总共耗时：约0.5小时

相比上面最开始的方法，最后这个方式效率提高了，总耗时只是原来的30%

看来使用第三方工具倒腾数据还是效率比较慢的，当数据量超过1000W以后基本不可取，还是按照Mysql官方推荐的这种方式处理会好点。如果你有更好的方式，欢迎指点一二，谢谢。