MySQL大数据导出导入

转自  http://blog.youkuaiyun.com/leamonjxl/article/details/6454074

上班过程中，经常遇到这样的一种情况:一个表有600多MB,就来层数据表，都要花一个小时的时间。其实我自己也知道通过纯文本的方式来导入数据是直接导入SQL语句文件速度的三倍，但是自己一直都没有去使用。今天就试用一下并且进行了一次总结:

第一种方式：通过SELECT 语句以及 LOAD FILE来导出导入文本文件

命令：

SELECT *  INTO OUTFILE 'D:/gdb_analytics_querys1.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '"'
LINES TERMINATED BY '/n'
FROM  gdb_analytics_querys;
说明: 
-- FIELDS 指定字段的参数 
-- FIELDS TERMINATED BY 字段之间的分割符，默认是/t,这里改成逗号 
-- ESCAPED BY 对特殊字符的转义，默认是使用右斜杠/, 这里改成双引号，这样的话一个双引号就会被转义成2个双引号了 
-- LINES 指定行的参数 
-- STARTING BY 行最开始附加的内容，我们不需要加，所以设置为空即可 
-- TERMINATED BY 指定行结束附加的内容，我们加上换行符，这也是默认的配置 

所形式的文件格式为:

64,"dnf",1,1266768001,"221.232.128.170",3006
65,"macbook",1,1266768001,"219.140.248.161",3006

说明： 1.现在是以”,”来作为分割符。

2.根据不同的数据类型，来进行了转义以及加上了引号。

进行导入

load data infile 'd://test.sql' into table 表名 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
ESCAPED BY '"' 
LINES STARTING BY ' ' TERMINATED BY '/n'; 

这样的话，导入数据的速度会提高三倍左右，不过要注意的是它进行导出时的速度与mysqldump的方式来导出的速度没有太大的区别。

不过要注意的地方:

1. 用 load data 是较快的方法
2. 大数据量情况下, 最好是创建好表之后, 同时也要创建好相关的索引. 虽然说没有索引时导入更快, 但是数据导入完成之后再创建索引总共的耗时比事先创建好了再导入要来的多多了.

另外,如果是myisam表,则最好是导入之前先禁用表的索引,导完之后再启用;或者是一开始不创建索引,导完之后再创建,都会比导入的同时更新索引来的快很多.



mysql>ALTER TABLE tbl2 DISABLE KEYS;
Query OK, 0 rows affected (0.00 sec)

mysql>INSERT INTO tbl2 SELECT * FROM tbl1;
Query OK, 2000000 row affected (36.30 sec)
Records: 2000000  Duplicates: 0  Warnings: 0

mysql>ALTER TABLE tbl2 ENABLE KEYS;
Query OK, 0 rows affected (44.55 sec)

在大批量导入时先禁用索引, 在完全导入后, 再开启索引, 一次性完成重建索引的效率会相对高很多, 经过反复几次测试, 感觉后者基本能比前者快 1.2 倍左右. 这也就是 LOAD DATAL INFILE 相对较快的原因之一.

所以对于非空的MyISAM表，需要用alter table xxx disable keys禁用索引，导入完成之后再用alter table xxx enable keys打开。

对于innodb，从测试的结果看无论表中是否已有记录，索引都是单独处理的，所以我们不需要禁用索引。