Proper use of load data infile in MySQL for import

Basic syntax:

Load data [low_priority] [local] infile ' file_name txt ' [replace | ignore]
into table Tbl_name
[Fields
[Terminated by ' t ']
[optionally] enclosed by ']
[escaped by ']]
[Lines terminated by ' n ']
[Ignore number lines]
[(Col_name,)]

The load Data infile statement reads from a text file into a table at a high speed. Before using this command, the MYSQLD process (service) must already be running. For security reasons, when reading a text file located on a server, the file must be in the database directory or readable by everyone. In addition, in order to use the load data infile on the server files, you must have file permissions on the server host.

It is a headache to write tens and even billions of data to MySQL.
The load data local infile seems to be the fastest way, but the load of a billion-level file, still need to count 10 minutes.

If there is a master-slave structure, the load on the main server for 10 minutes, the file data will be written to binary log, and then transferred to the server,
Then it takes 10 minutes from the server to do the load operation, and if there is only one sql_thread from the server to execute the binary log, then within 10 minutes,
The server can only do this load operation, can not respond to subsequent SQL statements, resulting in a delay between master and slave for 10 minutes.

Also, if the load was 20 minutes, www.111cn.net then found that there was a problem with the data source, need to cancel the load, then MySQL at least 20 minutes to rollback success ...
This time is very helpless.

So it is necessary to split this large file into several small files, and then load separately.

Some test data are given below:

[Root@yw-0-0 huarong]# wc-l Cfi.txt
20894227 Cfi.txt, line number 20M
There are nine fields, varchar (255), no key.
File size 4,078,099,848 (3.8G), averaging 195 bytes per line.

t1.sh direct load, the role is preheating.
This time data unexpectedly lost ...

InnoDB t1.sh again direct load
Time MySQL test-e "load data local infile '/d01/huarong/cfi.txt ' into table CFI"
[root@yw-0-0 huarong]#./t1.sh
Real 6m4.720s
User 0m0.492s
SYS 0m2.213s

InnoDB t2.sh not write Binlog.
Time MySQL test-e "set foreign_key_checks=0; Set sql_log_bin=0; Set unique_checks=0; Load data local infile '/d01/huarong/cfi.txt ' to table CFI
[root@yw-0-0 huarong]#./t2.sh
Real 5m3.964s
User 0m0.586s
SYS 0m2.788s

InnoDB t3.sh FIFO, each load 1M row of data.
wget Http://www.maatkit.org/get/mk-fifo-split
Perl./mk-fifo-split./cfi.txt--fifo/tmp/cfi.fifo--lines 1000000
while [-e/tmp/cfi.fifo]; Todo
Time MySQL test-e "set foreign_key_checks=0; Set sql_log_bin=0; Set unique_checks=0; Load data local infile '/tmp/cfi.fifo ' to table CFI
Sleep 1;
Done

real:5m25.84s
user:0m2.197s
sys:0m11.244s


Myisam:t2.sh not write Binlog
Real 3m24.838s
User 0m0.626s
SYS 0m2.939s