每天进步一点点——load data infile的相关优化

对MyISAM引擎

         通过DISABLE KEYS和ENABLE KEYS用来打开或者关闭唯一索引的更新，在导入大量数据到一个非空的MyISAM表时，通过设置这两条命令可以提高导入效率。但是导入一个空的MyISAM表，就不需要此设置：

ALTER TABLE 表名 DISABLE KEYS;

loading the data

ALTER TABLE 表名 ENABLE KEYS;

首先通过直接导入的方式进行导入

（film_text2相当于没有parmary key的film_text，并且内容为以前的14倍）

mysql>load data infile '/tmp/film_text2.txt' into table film_text2;

Query OK, 14000 rows affected (5.59 sec)

Records: 14000  Deleted: 0 Skipped: 0  Warnings: 0

 

通过disable keys和enable keys来进行导入

mysql>alter table film_text2 disable keys;

Query OK, 0 rows affected (0.00 sec)

 

mysql>load data infile '/tmp/film_text2.txt' into table film_text2;

Query OK, 14000 rows affected (0.11 sec)

Records: 14000  Deleted: 0 Skipped: 0  Warnings: 0

 

mysql>alter table film_text2 enable keys;

Query OK, 0 rows affected (4.54 sec)

对于InnoDB引擎

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据效率。

film_text2是有主键的，顺序的。film_text5是没有主键的，乱序的。但是行数是一样的。

mysql>load data infile '/tmp/film_text2' into table film_text3;

Query OK, 56000 rows affected (1.10 sec)

Records: 56000  Deleted: 0 Skipped: 0  Warnings: 0

 

mysql>load data infile '/tmp/film_text5' into table film_text3;

Query OK, 56000 rows affected (1.56 sec)

Records: 56000  Deleted: 0 Skipped: 0  Warnings: 0

 

在导入数据前执行SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SETUNIQUE_CHECKS=1恢复唯一性校验，可以提高效率。

 

当SETUNIQUE_CHECKS=0时

mysql>Load data infile '/tmp/film_text5' into table film_text3;

Query OK, 56000 rows affected (1.05 sec)

Records: 56000  Deleted: 0 Skipped: 0  Warnings: 0

 

当SETUNIQUE_CHECKS=1时

mysql>Load data infile '/tmp/film_text5' into table film_text3;

Query OK, 56000 rows affected (1.30 sec)

Records: 56000  Deleted: 0 Skipped: 0  Warnings: 0

关闭自动提交也可以提高导入效率

mysql>set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

 

mysql>load data infile '/tmp/film_text2' into table film_text3;

Query OK, 56000 rows affected (1.08 sec)

Records: 56000  Deleted: 0 Skipped: 0  Warnings: 0

 

mysql>set autocommit=1;

Query OK, 0 rows affected (0.16 sec)

 

mysql>load data infile '/tmp/film_text2' into table film_text3;

Query OK, 56000 rows affected (1.38 sec)

Records: 56000  Deleted: 0 Skipped: 0  Warnings: 0