Myisam与innodb数据导入速度对比测试

           Myisam与innodb数据导入速度对比测试

在维护数据库时,经常需要装载大量数据,按照Oracle的经验,一般都是先建个空表,导数据,再建约束和索引,再调用dbms_stat或analyze命令获得表与索引上的statistics data

 

在mysql中是不是仍然如此呢? 以下测试一定程度上给出了答案: 与oracle最相似的innodb引擎实际上直接导入更快,导入后再创建索引并没有好处:-)

 

对于myisam来说,则建议导入之后再建索引

 

分别从两个维度来测试

1)      不同表引擎(myisam和innodb)

2)      索引在导入后创建,索引在导入前创建

 

Mysql5.0.45, 设置autocommit=1

通过下列简单的存储过程批量插入不同条数记录以获得总的花费时间

 

测试存储过程

delimiter //

create procedure pload()

BEGIN

declare j int default 1;

while j<=n

do

  insert into test(id2,name) values(j,'aaaaaaaaaaaaaaaaaaaaaaa');

  set j=j+1;

end while;

end;

//

 

--创建空表,带一个unique key

CREATE TABLE `test` (

  `id1` int(11) NOT NULL AUTO_INCREMENT,

   id2 int(11) not null default 0,

   name varchar(30),

  PRIMARY KEY (`id1`),

  unique key (id2)

) ENGINE=myisam DEFAULT CHARSET=latin1;

 

delimiter ;

call pload();

 

 

--重新建表,不带unique

CREATE TABLE `test` (

  `id1` int(11) NOT NULL AUTO_INCREMENT,

   id2 int(11) not null default 0,

   name varchar(30),

  PRIMARY KEY (`id1`)

) ENGINE=myisam DEFAULT CHARSET=latin1;

 

--调用过程

call pload();

 

 

--单独创建unique index

create unique index id2 on test(id2);

 

n分别改成5000,500000,1000000,5000000测试4次

然后将引擎改为innodb重复上述过程4次

 

然后在name列上加一个btree重复测试

 

得到下列数据

 

(单位:sec)

		5w	50w	100w	500w
Myisam Unique	一次性导入	2.45	25.30	51.56	259.10
	导完数据建索引	2.50	25.96	52.89	267.59
Myisam Unique+normal	一次性导入	8.14	89.15	183.46	1005.29
	导完数据建索引	2.99	32.87	67.26	351.79
innodb禁止unique check	set unique_checks=0	9.27	91.98	184.55
Innodb Unique	一次性导入	9.25	92.21	183.64
	导完数据建索引	9.72	97.29
Innodb Unique+normal	一次性导入	9.53	94.49	194.51	946.39
	导完数据建索引	10.63	104.66	209.16	1050.04

 

 

结论:

1, 对于myisam, 如果只有pk, unique key; 导入前或后创建索引区别不大

  建议选择不要分开创建

2, 对于myisam, 如果有secondary index; 导入后创建索引性能远好于导入前

  建议导入数据后,再创建索引

3, 对于innodb, 无论是否存在secondary index, 导入后创建索引性能都要差些

  这与oracle的区别很大,Oracle建议装载数据后再创建索引!

建议不要选择导入后创建

 

 

对于myisam, 导入数据后再创建索引

 对于innodb,则直接导入,如果有unique key,先禁止约束检查,导完后再启用

 

用其它方式装载数据时可能有所差异

 

手册相关章节见15.7.2. Converting MyISAM Tables to InnoDB

 

InnoDB does not have a special optimization for separate index creation the way the MyISAM storage

engine does. Therefore, it does not pay to export and import the table and create indexes afterward.

The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table.

That is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical

definitions and insert the rows with INSERT INTO ... SELECT * FROM ....

If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52, you can speed

up a table import by turning off the uniqueness checks temporarily during the import session: SET

UNIQUE_CHECKS=0; For big tables, this saves a lot of disk I/O because InnoDB can then use its

insert buffer to write secondary index records in a batch.

 

附原始数据

Myisam引擎,  按数据量级分隔,每个量级的前面部分是带第三个索引的数据,后面是不带name上索引的数据

 

500w

Query OK, 0 rows affected (16 min 45.29 sec)  Query OK, 0 rows affected (4 min 19.10 sec)

vs

Query OK, 0 rows affected (3 min 44.54 sec)   Query OK, 0 rows affected (3 min 42.93 sec)

Query OK, 5000000 rows affected (47.65 sec)   Query OK, 5000000 rows affected (44.66 sec)

Query OK, 5000000 rows affected (1 min 19.60 sec)

 

100w

Query OK, 0 rows affected (3 min 3.46 sec) Query OK, 0 rows affected (51.56 sec)

vs

Query OK, 0 rows affected (44.68 sec)     Query OK, 0 rows affected (44.44 sec)

Query OK, 1000000 rows affected (8.35 sec) Query OK, 1000000 rows affected (8.45 sec)

Query OK, 1000000 rows affected (14.23 sec)

 

50w

Query OK, 0 rows affected (1 min 29.15 sec) Query OK, 0 rows affected (25.30 sec)

vs

Query OK, 0 rows affected (22.15 sec)       Query OK, 0 rows affected (22.05 sec)

Query OK, 500000 rows affected (3.95 sec)   Query OK, 500000 rows affected (3.91 sec)

Query OK, 500000 rows affected (6.77 sec)

 

5w

Query OK, 0 rows affected (8.14 sec)   Query OK, 0 rows affected (2.45 sec)

vs

Query OK, 0 rows affected (2.16 sec)   Query OK, 0 rows affected (2.16 sec)

uery OK, 50000 rows affected (0.34 sec) Query OK, 50000 rows affected (0.34 sec)

Query OK, 50000 rows affected (0.49 sec)

 

innodb引擎,  按数据量级分隔,每个量级的前面部分是带第三个索引的数据,后面是不带name上索引的数据

 

5w

Query OK, 1 row affected (9.53 sec)   Query OK, 1 row affected (9.25 sec)

vs

Query OK, 1 row affected (8.92 sec)   Query OK, 1 row affected (9.00 sec)

Query OK, 50000 rows affected (0.72 sec) Query OK, 50000 rows affected (0.72 sec)

Query OK, 50000 rows affected (0.99 sec)

 

 

50w

Query OK, 1 row affected (1 min 34.49 sec)  Query OK, 1 row affected (1 min 32.21 sec)

vs

Query OK, 1 row affected (1 min 29.65 sec)  Query OK, 1 row affected (1 min 29.69 sec)

Query OK, 500000 rows affected (6.36 sec)   Query OK, 500000 rows affected (7.60 sec)

Query OK, 500000 rows affected (8.65 sec)

 

100w

Query OK, 1 row affected (3 min 14.51 sec)   缺

vs

Query OK, 1 row affected (2 min 59.43 sec)  缺

Query OK, 1000000 rows affected (12.61 sec) 缺

Query OK, 1000000 rows affected (17.12 sec) 缺

 

500w

Query OK, 1 row affected (15 min 46.39 sec) 缺

vs

Query OK, 1 row affected (14 min 56.04 sec) 缺

Query OK, 5000000 rows affected (1 min 6.38 sec) 缺

Query OK, 5000000 rows affected (1 min 27.62 sec) 缺