Mysql LOAD DATA INFILE 导入大文件的方法

缘起：

目前有一个5000千万行的数据文件data.csv，需要导入mysql进行测试。

直接运行

LOAD DATA INFILE ‘data.csv’ INTO TABLE test.mytab FIELDS TERMINATED BY ‘\t’ ENCLOSED BY ” LINES TERMINATED BY ‘\n’;

N个小时都没有完成。使用pstack以及vmstat发现mysql还是在卖力的工作。而取消操作之后，很长时间都无法使用SQL操作这个表。

通过执行

查看mysql一直有一个线程执行一直在回滚操作，回滚操作锁定了这个表，
这个大数据量的LOAD DATA INFILE操作是一个巨大的事务。

以此推理，使用load data语句导入大数据量文件，会有以下问题：

1、在binlog文件中写大量的binary log，会严重影响到主从复制，导致备库复制严重滞后。

2、表空间积累一堆undo日志，清理线程不能清理，大部分的操作都会变慢，包括简单的select操作。

3、如果导入发生异常，innoDB引擎的回滚速度很慢，那么回滚过程会很长，还不如清理数据之后重新导入耗时短。

解决措施：

1、使用perl脚本将文件按10万进行分割，写入管道文件：

perl mk-fifo-split data.csv  –fifo /tmp/fifo –lines 100000

2、用脚本不断读取管道，进行每批次10万行的导入操作：

while [ -e /tmp/fifo ];

   do time mysql -u root -phitv -e "LOAD DATA INFILE '/tmp/fifo' INTO TABLE test.mytab FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n';"

  sleep 1;

done

每10万条插入时间缩减为10秒左右，且每批次导入时间增长较缓慢。

继续优化：

while [ -e /tmp/fifo ];

   do time mysql -u root -phitv -e "set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0;LOAD DATA INFILE '/tmp/fifo' INTO TABLE test.mytab FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n';"

   sleep 1;

done

速度还是很慢，数据表有多个索引字段，删除表中索引约束之后，导入每10万条变成0.4秒左右。