转载

MySQL大量数据入库的性能比较（分区）

测试程序还用之前的
http://blog.itpub.net/29254281/viewspace-1841299/

这次测试,使用的是家里的电脑,性能比单位工作的电脑配置要好一些.

MySQL配置
innodb_buffer_pool_size=512m
innodb_flush_log_at_trx_commit =0
sync_binlog=0
innodb_support_xa=0
log_bin=master

版本 5.6.14

每次测试,Insert 200w记录.

1.使用Load File接口,普通表,4个索引,每100个记录提交一次

create table chat_message(
id bigint primary key auto_increment,
src_userid bigint not null,
target_userid bigint not null,
message varchar(200),
ts timestamp not null default current_timestamp,
s1 int,
s2 int,
s3 int,
s4 int
);
create index inx_1 on chat_message(src_userid,target_userid,s1,ts);
create index inx_2 on chat_message(src_userid,target_userid,s2,ts);
create index inx_3 on chat_message(src_userid,target_userid,s3,ts);
create index inx_4 on chat_message(src_userid,target_userid,s4,ts);

测试结果
每秒 10638 Insert

2.使用Load File接口,使用日期的范围分区

create table chat_message(
id bigint auto_increment,
src_userid bigint not null,
target_userid bigint not null,
message varchar(200),
ts timestamp not null default current_timestamp,
s1 int,
s2 int,
s3 int,
s4 int,
primary key (id,ts)
)
partition by range(UNIX_TIMESTAMP(ts))
(
partition p1 VALUES LESS THAN(UNIX_TIMESTAMP('2015-10-01 00:00:00')),
partition p2 VALUES LESS THAN(UNIX_TIMESTAMP('2015-11-01 00:00:00')),
partition p3 VALUES LESS THAN(UNIX_TIMESTAMP('2015-12-01 00:00:00')),
partition p4 VALUES LESS THAN(UNIX_TIMESTAMP('2016-01-01 00:00:00')),
partition p5 VALUES LESS THAN(UNIX_TIMESTAMP('2016-02-01 00:00:00')),
partition p6 VALUES LESS THAN(UNIX_TIMESTAMP('2016-03-01 00:00:00')),
partition p7 VALUES LESS THAN(UNIX_TIMESTAMP('2016-04-01 00:00:00')),
partition p8 VALUES LESS THAN(UNIX_TIMESTAMP('2016-05-01 00:00:00')),
partition p9 VALUES LESS THAN(UNIX_TIMESTAMP('2016-06-01 00:00:00')),
partition p10 VALUES LESS THAN(UNIX_TIMESTAMP('2016-07-01 00:00:00')),
partition p11 VALUES LESS THAN(UNIX_TIMESTAMP('2016-08-01 00:00:00')),
partition p12 VALUES LESS THAN(UNIX_TIMESTAMP('2016-09-01 00:00:00'))
);
create index inx_1 on chat_message(src_userid,target_userid,s1,ts);
create index inx_2 on chat_message(src_userid,target_userid,s2,ts);
create index inx_3 on chat_message(src_userid,target_userid,s3,ts);
create index inx_4 on chat_message(src_userid,target_userid,s4,ts);

测试结果
每秒 10989 Insert

3.在日期范围分区基础上,增加4个子分区

create table chat_message(
id bigint auto_increment,
src_userid bigint not null,
target_userid bigint not null,
message varchar(200),
ts timestamp not null default current_timestamp,
s1 int,
s2 int,
s3 int,
s4 int,
primary key (id,ts,src_userid)
)
partition by range(UNIX_TIMESTAMP(ts))
subpartition by key(src_userid)
subpartitions 4(
partition p201506 VALUES LESS THAN(UNIX_TIMESTAMP('2015-10-01 00:00:00')),
partition p201507 VALUES LESS THAN(UNIX_TIMESTAMP('2015-11-01 00:00:00')),
partition p201508 VALUES LESS THAN(UNIX_TIMESTAMP('2015-12-01 00:00:00'))
);
create index inx_1 on chat_message(src_userid,target_userid,s1,ts);
create index inx_2 on chat_message(src_userid,target_userid,s2,ts);
create index inx_3 on chat_message(src_userid,target_userid,s3,ts);
create index inx_4 on chat_message(src_userid,target_userid,s4,ts);

测试结果
每秒 8810 Insert

3.在日期范围分区基础上,增加16个子分区

每秒 6688 Insert

4.在日期范围分区基础上,增加64个子分区

每秒 8368 Insert

结论:
1.在日期的范围分区上,再增加Hash分区,显著降低每秒Insert数量
2.随着数据量的增加,每秒Insert数量显著下降. 比如表中已经有200w数据,再增加200w数据,每秒Insert从1w左右直接掉到1k左右.

正文到此结束