合理使用mysql中的load data infile导入数据

萧红SEO SEO爱好者,分享SEO经验~

mysql中load data infile用来加载外部文件导入到mysql数据表中,针对于一般的小数据量的load data infile是没有问题,但如果碰到上亿级别数据怎么处理呢,下面我们一起来看看.

基本语法,代码如下:

  1. loaddata[low_priority][local]infile'file_nametxt'[replace|ignore]
  2. intotabletbl_name
  3. [fields
  4. [terminatedby't']
  5. [OPTIONALLY]enclosedby'']
  6. [escapedby'']]
  7. [linesterminatedby'n']
  8. [ignorenumberlines]
  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分钟,www.phpfensi.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. innodbt1.sh再次直接load.
  2. timemysqltest-e"loaddatalocalinfile'/d01/huarong/cfi.txt'intotablecfi"
  3. [root@yw-0-0huarong]#./t1.sh
  4. real6m4.720s
  5. user0m0.492s
  6. sys0m2.213s
  7. innodbt2.sh不写binlog.
  8. timemysqltest-e"setforeign_key_checks=0;setsql_log_bin=0;setunique_checks=0;loaddatalocalinfile'/d01/huarong/cfi.txt'intotablecfi"
  9. [root@yw-0-0huarong]#./t2.sh
  10. real5m3.964s
  11. user0m0.586s
  12. sys0m2.788s
  13. innodbt3.shfifo,每次load1M行数据。
  14. wgethttp://www.maatkit.org/get/mk-fifo-split
  15. perl./mk-fifo-split./cfi.txt--fifo/tmp/cfi.fifo--lines1000000
  16. while[-e/tmp/cfi.fifo];do
  17. timemysqltest-e"setforeign_key_checks=0;setsql_log_bin=0;setunique_checks=0;loaddatalocalinfile'/tmp/cfi.fifo'intotablecfi"
  18. sleep1;
  19. done
  20. real:5m25.84s
  21. user:0m2.197s
  22. sys:0m11.244s
  23. myisam:t2.sh不写binlog
  24. real3m24.838s
  25. user0m0.626s
  26. sys0m2.939s

相关广告
  • 合理使用mysql中的load data infile导入数据 合理使用mysql中的load data infile导入数据 合理使用mysql中的load data infile导入数据
相关阅读

合理使用mysql中的load data infile导入数据

2019/10/10 17:37:12 | 谷歌SEO算法 | 夫唯SEO视频教程