0 条评论

开发过程中需要把一个已有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官方推荐的这种方式处理会好点。如果你有更好的方式，欢迎指点一二，谢谢。