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

基本語法,代碼如下:

1. load data  [low_priority] [local] infile 'file_name txt' [replace | ignore] 
2. into table tbl_name 
3. [fields 
4. [terminated by't'] 
5. [OPTIONALLY] enclosed by ''] 
6. [escaped by'' ]] 
7. [lines terminated by'n'] 
8. [ignore number lines] 
9. [(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分鐘,m.survivalescaperooms.com然后發現數據源有問題,需要取消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,作用是預熱,這個時間數據竟然丟失了...

1. innodb t1.sh 再次直接load. 
2.  
3. time mysql   test -e "load data local infile '/d01/huarong/cfi.txt' into table cfi" 
4. [root@yw-0-0 huarong]# ./t1.sh  
5. real    6m4.720s 
6. user    0m0.492s 
7. sys     0m2.213s 
8.  
9. innodb t2.sh不寫binlog. 
10. 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" 
11. [root@yw-0-0 huarong]# ./t2.sh  
12. real    5m3.964s 
13. user    0m0.586s 
14. sys     0m2.788s 
15.  
16. innodb t3.sh fifo，每次load 1M行數據。 
17. wget http://www.maatkit.org/get/mk-fifo-split 
18. perl ./mk-fifo-split ./cfi.txt --fifo /tmp/cfi.fifo --lines 1000000 
19. while [ -e /tmp/cfi.fifo ]; do 
20.         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" 
21.    sleep 1; 
22. done 
23.  
24. real: 5m25.84s 
25. user: 0m2.197s 
26. sys: 0m11.244s 
27.  
28.  
29. myisam: t2.sh不寫binlog 
30. real    3m24.838s 
31. user    0m0.626s 
32. sys     0m2.939s