MySql 大批量导入数据时关闭索引

1.建表

create table goods(
id int primary key,
name varchar(20),
key(name)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

2.插入数据

delimiter $$
create procedure p1(in num int)
begin
  declare name varchar(20) default '';
   declare i int default 0;
   while i<= num do
      set name = concat('April',i);
      insert into goods values(i,name);
      set i = i+1;
   end while;
end $$
delimiter ;

向goods表中插入1百万条数据。直接插入

mysql>call p1(1000000);
Query OK, 1 row affected (2 min 3.33 sec)

先关闭索引，插入数据，再打开索引

mysql> ALTER TABLE goods DISABLE KEYS;
Query OK, 0 rows affected (0.00 sec)
 
mysql> call p1(1000000);
Query OK, 1 row affected (1 min 14.81 sec)
 
mysql> ALTER TABLE goods ENABLE KEYS;
Query OK, 0 rows affected (26.47 sec)

3.复制数据

    新建表goods2，将goods表中的数据导入goods2。

create table goods2(
id int primary key,
name varchar(20),
key(name)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

直接复制数据。

mysql> INSERT INTO goods2 SELECT * FROM goods;
Query OK, 1000001 rows affected (30.35 sec)
Records: 1000001  Duplicates: 0  Warnings: 0

先关闭索引，复制数据，再打开索引

mysql> ALTER TABLE goods2 DISABLE KEYS;
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO goods2 SELECT * FROM goods;
Query OK, 1000001 rows affected (3.57 sec)
Records: 1000001  Duplicates: 0  Warnings: 0
 
mysql> ALTER TABLE goods2 ENABLE KEYS;
Query OK, 0 rows affected (26.73 sec)

4.结论

    在对空表大批量数据写数据时，先禁用索引, 在完全导入后, 再启用索引。比一次性完成效率高。上面只是对这个mysiam类型的表进行导入数据的一个优化，但是对于innodb这样的表，这种方式并不能提高导入数据的效率，可以有以下几种方式来提高导入数据的效率

• 因为innodb的表是按照主键的顺序来保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
• 在导入数据之前关闭唯一性校验，及设置set unique_checks=0,就是对它进行一个关闭，等插入完之后，再进行一个恢复即可。
• 在设置的时候关闭自动提交，set autocommit=0,然后当插入完成之后，再进行设置为1。