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

mysql索引开启和关闭

#关闭：
ALTER TABLE `test` DISABLE KEYS ;
#开启：
ALTER TABLE `test` ENABLE KEYS;

1.建表

create table goods(
id int primary key,
name varchar(20),
key(name)
);

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)
);

 直接复制数据。

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.结论

在大批量导入数据时，先禁用索引, 在完全导入后, 再启用索引。比一次性完成效率高。所以对于非空的MyISAM表，需要禁用索引，导入完成之后再打开。对于innodb，从测试的结果看无论表中是否已有记录，索引都是单独处理的，所以无需禁用索引。