合理使用mysql中的load data infile導入數據

mysql中load data infile用來加載外部文件導入到mysql數據表中，針對於一般的小數據量的load data infile是沒有問題，但如果碰到上億級別數據怎麼處理呢，下面我們一起來看看。

基本語法：

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,   )]
 
load data infile語句從一個文本文件中以很高的速度讀入一個表中。使用這個命令之前，mysqld進程（服務）必須已經在運行。為了安全原因，當讀取位於服務器上的文本文件時，文件必須處於數據庫目錄或可被所有人讀取。另外，為了對服務器上文件使用load data infile，在服務器主機上你必須有file的權限。

把千萬級甚至億級的數據寫入mysql，實在是一件很讓人頭痛的事情。
load data local infile貌似是最快的方法了， 可是load一個億級的文件，仍然需要數十分鐘。

如果有主從結構的話，在主服務器上面load數十分鐘，文件數據會寫入binary log，再傳輸到從服務器，
然後從服務器也需要數十分鐘來做load操作，如果從服務器只有一個sql_thread來執行binary log，那麼在這數十分鐘內，
從服務器就只能做這一個load操作，無法對後續的sql語句做出響應，導致主從之間的延遲有數十分鐘。

而且，如果load了20分鐘，www.111cn.net然後發現數據源有問題，需要取消load，那麼mysql至少還要20分鐘才能回滾成功…
這個時候非常無奈。

所以有必要把這種大文件拆分成若干個小文件，然後分別load.

下面給出一些測試數據：

[root@yw-0-0 huarong]# wc -l cfi.txt
20894227 cfi.txt, 行數20M
有九個字段，varchar(255)，沒有key。
文件大小  4,078,099,848 （3.8G），每行平均195字節。
 
t1.sh 直接load，作用是預熱。
這個時間數據竟然丟失了...
 
innodb t1.sh 再次直接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不寫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' into table cfi"
[root@yw-0-0 huarong]# ./t2.sh
real    5m3.964s
user    0m0.586s
sys     0m2.788s
 
innodb t3.sh fifo，每次load 1M行數據。
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 ]; do
        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' into table cfi"
   sleep 1;
done
 
real: 5m25.84s
user: 0m2.197s
sys: 0m11.244s
 
 
myisam: t2.sh不寫binlog
real    3m24.838s
user    0m0.626s
sys     0m2.939s