MySQL大量資料入庫的效能比較（分割槽）

壹頁書發表於2015-11-22
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個記錄提交一次

  1. create table chat_message(
  2.     id bigint primary key auto_increment,
  3.     src_userid bigint not null,
  4.     target_userid bigint not null,
  5.     message varchar(200),
  6.     ts timestamp not null default current_timestamp,
  7.     s1 int,
  8.     s2 int,
  9.     s3 int,
  10.     s4 int
  11. );
  12. create index inx_1 on chat_message(src_userid,target_userid,s1,ts);
  13. create index inx_2 on chat_message(src_userid,target_userid,s2,ts);
  14. create index inx_3 on chat_message(src_userid,target_userid,s3,ts);
  15. create index inx_4 on chat_message(src_userid,target_userid,s4,ts);
測試結果
每秒 10638 Insert

2.使用Load File介面,使用日期的範圍分割槽

  1. create table chat_message(
  2.     id bigint auto_increment,
  3.     src_userid bigint not null,
  4.     target_userid bigint not null,
  5.     message varchar(200),
  6.     ts timestamp not null default current_timestamp,
  7.     s1 int,
  8.     s2 int,
  9.     s3 int,
  10.     s4 int,
  11.     primary key (id,ts)
  12. )
  13. partition by range(UNIX_TIMESTAMP(ts))
  14. (
  15.     partition p1 VALUES LESS THAN(UNIX_TIMESTAMP('2015-10-01 00:00:00')),
  16.     partition p2 VALUES LESS THAN(UNIX_TIMESTAMP('2015-11-01 00:00:00')),
  17.     partition p3 VALUES LESS THAN(UNIX_TIMESTAMP('2015-12-01 00:00:00')),
  18.     partition p4 VALUES LESS THAN(UNIX_TIMESTAMP('2016-01-01 00:00:00')),
  19.     partition p5 VALUES LESS THAN(UNIX_TIMESTAMP('2016-02-01 00:00:00')),
  20.     partition p6 VALUES LESS THAN(UNIX_TIMESTAMP('2016-03-01 00:00:00')),
  21.     partition p7 VALUES LESS THAN(UNIX_TIMESTAMP('2016-04-01 00:00:00')),
  22.     partition p8 VALUES LESS THAN(UNIX_TIMESTAMP('2016-05-01 00:00:00')),
  23.     partition p9 VALUES LESS THAN(UNIX_TIMESTAMP('2016-06-01 00:00:00')),
  24.     partition p10 VALUES LESS THAN(UNIX_TIMESTAMP('2016-07-01 00:00:00')),
  25.     partition p11 VALUES LESS THAN(UNIX_TIMESTAMP('2016-08-01 00:00:00')),
  26.     partition p12 VALUES LESS THAN(UNIX_TIMESTAMP('2016-09-01 00:00:00'))
  27. );

  29. create index inx_1 on chat_message(src_userid,target_userid,s1,ts);
  30. create index inx_2 on chat_message(src_userid,target_userid,s2,ts);
  31. create index inx_3 on chat_message(src_userid,target_userid,s3,ts);
  32. create index inx_4 on chat_message(src_userid,target_userid,s4,ts);
測試結果
每秒 10989 Insert

3.在日期範圍分割槽基礎上,增加4個子分割槽

  1. create table chat_message(
  2.     id bigint auto_increment,
  3.     src_userid bigint not null,
  4.     target_userid bigint not null,
  5.     message varchar(200),
  6.     ts timestamp not null default current_timestamp,
  7.     s1 int,
  8.     s2 int,
  9.     s3 int,
  10.     s4 int,
  11.     primary key (id,ts,src_userid)
  12. )
  13. partition by range(UNIX_TIMESTAMP(ts))
  14. subpartition by key(src_userid)
  15. subpartitions 4(
  16.     partition p201506 VALUES LESS THAN(UNIX_TIMESTAMP('2015-10-01 00:00:00')),
  17.     partition p201507 VALUES LESS THAN(UNIX_TIMESTAMP('2015-11-01 00:00:00')),
  18.     partition p201508 VALUES LESS THAN(UNIX_TIMESTAMP('2015-12-01 00:00:00'))
  19. );

  21. create index inx_1 on chat_message(src_userid,target_userid,s1,ts);
  22. create index inx_2 on chat_message(src_userid,target_userid,s2,ts);
  23. create index inx_3 on chat_message(src_userid,target_userid,s3,ts);
  24. 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左右.



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1843669/，如需轉載，請註明出處，否則將追究法律責任。

相關文章