MySQL分区性能测试

By 成江东, on 八月 31st, 2010

一，测试服务器配置：

Cpu: 2*4核Intel(R) Xeon(R) E5405 @ 2.00GHz

内存：4G

存储：36块1T的7.2K的SATA组成raid0。使用其中的1T

Mysql版本：5.1.45-community-log MySQL Community Server (GPL)

二，建测试表：

Myisam不分区不分表，单表10亿数据量：

create table biguser

(

   ID                   bigint not null auto_increment comment ‘自增ID’,

   Username             varchar(30) not null comment ‘用户名’,

   sex                  tinyint not null comment ‘性别’,

   birthday             datetime not null comment ‘生日’,

   email                varchar(50) not null comment ‘邮箱’,

   tel                  int not null comment ‘手机’,

   intro                varchar(255) not null comment ‘个人简介’,

   primary key (ID)

)

type = MYISAM

default charset=utf8;

Myisam不分区，分10个表，单表1亿数据量：

create table user01

(

   ID                   bigint not null auto_increment comment ‘自增ID’,

   Username             varchar(30) not null comment ‘用户名’,

   sex                  tinyint not null comment ‘性别’,

   birthday             datetime not null comment ‘生日’,

   email                varchar(50) not null comment ‘邮箱’,

   tel                  int not null comment ‘手机’,

   intro                varchar(255) not null comment ‘个人简介’,

   primary key (ID)

)

type = MYISAM

default charset=utf8;

*一共10个表,user01,user02,…,user10

 

 

Myisam用id进行hash分区，分10个区，每个分区1亿数据：

create table puser

(

   ID                   bigint not null auto_increment comment ‘自增ID’,

   Username             varchar(30) not null comment ‘用户名’,

   sex                  tinyint not null comment ‘性别’,

   birthday             datetime not null comment ‘生日’,

   email                varchar(50) not null comment ‘邮箱’,

   tel                  int not null comment ‘手机’,

   intro                varchar(255) not null comment ‘个人简介’,

   primary key (ID)

)

type = MYISAM

default charset=utf8

partition by hash(ID)

partitions 10;  

Innodb用id进行hash分区，分10个区，每个分区1亿数据：

create table iuser

(

   ID                   bigint not null auto_increment comment ‘自增ID’,

   Username             varchar(30) not null comment ‘用户名’,

   sex                  tinyint not null comment ‘性别’,

   birthday             datetime not null comment ‘生日’,

   email                varchar(50) not null comment ‘邮箱’,

   tel                  int not null comment ‘手机’,

   intro                varchar(255) not null comment ‘个人简介’,

   primary key (ID)

)

type = Innodb

default charset=utf8

partition by hash(ID)

partitions 10;

 

 

Innodb不分区不分表，单表10亿数据量：

create table ibiguser

(

   ID                   bigint not null auto_increment comment ‘自增ID’,

   Username             varchar(30) not null comment ‘用户名’,

   sex                  tinyint not null comment ‘性别’,

   birthday             datetime not null comment ‘生日’,

   email                varchar(50) not null comment ‘邮箱’,

   tel                  int not null comment ‘手机’,

   intro                varchar(255) not null comment ‘个人简介’,

   primary key (ID)

)

type = Innodb

default charset=utf8;

三，数据生成

利用power design生成100万不重复的数据的基础表，然后将此表数据用mysqlslap工具重复插入到目标表，可以快速生成海量数量表。最终数据文件大小都在170G左右(分表情况单表17G数据文件)

插入100万记录到目标表的性能如下

单位（秒）：

Myisam不分区	Myisam分区	Innodb不分区	Innodb分区
10.514	9.114	16.258	16.893

可见非并发的大量数据插入myisam分区性能最佳，myisam不分区次之，innodb分区较差。

 

 

四，压力测试

利用mysql5.1自带的压力测试工具mysqlslap对以上四种情况进行了压力测试，测试结果如下(单位：秒)：

	myisam 未分区	myisam 分区	Myisam 分表	Innodb 未分区	innodb 分区
100连续随机查询	4.55	4.11	5.389	4.445	5.893
并发50随机查询	0.052	0.049	0.075	0.07	0.038
并发100随机查询	0.071	0.078	0.11	0.088	0.055
并发250随机查询	0.137	0.153	0.239	0.175	0.124
并发500随机查询	0.266	0.273	0.447	0.283	0.24
并发1000随机查询	0.5	0.533	0.849	0.485	0.46
并发50随机插入	0.037	0.03	0.04	0.042	0.025
并发100随机插入	0.071	0.06	0.057	0.06	0.049
并发250随机插入	0.179	0.159	0.144	0.135	0.132
并发500随机插入	0.384	0.327	0.332	0.248	0.25
并发1000随机插入	0.82	0.73	0.737	0.542	0.526
并发50随机删除	1.204	1.536	0.196	0.041	0.039
并发100随机删除	2.333	2.945	0.362	0.062	0.063
并发250随机删除	5.568	5.718	0.831	0.14	0.173
并发500随机删除	9.663	9.705	1.42	0.275	0.308
并发1000随机删除	19.877	17.254	2.655	0.589	0.634
并发50随机更新	1.008	0.887	0.298	0.044	0.046
并发100随机更新	1.789	1.688	0.533	0.064	0.062
并发250随机更新	4.291	3.986	1.029	0.134	0.145
并发500随机更新	8.357	7.872	1.783	0.306	0.288
并发1000随机更新	15.963	15.126	3.33	0.575	0.563

 

*运行了一个100条语句的批查询，看非并发情况下的查询效率，查询语句见附一。

*利用存储过程进行一个hash处理，去对不同的表进行数据操作，见附二。

五，结论：

一，在高并发情况下，innodb的分区表的增，删，改，查的性能都优于myisam引擘，特别是在高并发删除和更新的场合下，两者相差可以达到20-30倍。

二，myisam的分区表(10个分区)和未分区表的性能相差不大，略优于未分区表。

三，myisam分区表(10个分区)的插入性能与分表(10个表)差不多，但查询性能要好30%左右。但删除与更新的性能分表要远远好于分区。

四，innodb分区表和未分区表查询和插入在低并发下，相差40%。但在并发性不断增加的情况下，性能相差不大。删除和更新性能则一直相近。
附一：

select sql_no_cache * from biguser where id=       14988469         ;

select sql_no_cache * from biguser where id=       57611709         ;

select sql_no_cache * from biguser where id=       88737417         ;

select sql_no_cache * from biguser where id=       5487431  ;

select sql_no_cache * from biguser where id=       75912772         ;

select sql_no_cache * from biguser where id=       39680341         ;

select sql_no_cache * from biguser where id=       33444232         ;

select sql_no_cache * from biguser where id=       53937959         ;

select sql_no_cache * from biguser where id=       29031225         ;

select sql_no_cache * from biguser where id=       1256108  ;

select sql_no_cache * from biguser where id=       25314369         ;

select sql_no_cache * from biguser where id=       24296361         ;

select sql_no_cache * from biguser where id=       65966713         ;

select sql_no_cache * from biguser where id=       14395018         ;

select sql_no_cache * from biguser where id=       69197350         ;

select sql_no_cache * from biguser where id=       90962562         ;

select sql_no_cache * from biguser where id=       67908690         ;

select sql_no_cache * from biguser where id=       34352967         ;

select sql_no_cache * from biguser where id=       76458078         ;

select sql_no_cache * from biguser where id=       9986401  ;

select sql_no_cache * from biguser where id=       92969693         ;

select sql_no_cache * from biguser where id=       21688034         ;

select sql_no_cache * from biguser where id=       23534033         ;

select sql_no_cache * from biguser where id=       49682571         ;

select sql_no_cache * from biguser where id=       70230582         ;

select sql_no_cache * from biguser where id=       23174504         ;

select sql_no_cache * from biguser where id=       54671332         ;

select sql_no_cache * from biguser where id=       60255403         ;

select sql_no_cache * from biguser where id=       33064209         ;

select sql_no_cache * from biguser where id=       30657185         ;

select sql_no_cache * from biguser where id=       98478707         ;

select sql_no_cache * from biguser where id=       27247625         ;

select sql_no_cache * from biguser where id=       20287058         ;

select sql_no_cache * from biguser where id=       67371577         ;

select sql_no_cache * from biguser where id=       18519687         ;

select sql_no_cache * from biguser where id=       55236080         ;

select sql_no_cache * from biguser where id=       21413273         ;

select sql_no_cache * from biguser where id=       2928926  ;

select sql_no_cache * from biguser where id=       82738229         ;

select sql_no_cache * from biguser where id=       47525876         ;

select sql_no_cache * from biguser where id=       19732304         ;

select sql_no_cache * from biguser where id=       63153463         ;

select sql_no_cache * from biguser where id=       1685048  ;

select sql_no_cache * from biguser where id=       4675229  ;

select sql_no_cache * from biguser where id=       14525956         ;

select sql_no_cache * from biguser where id=       86972953         ;

select sql_no_cache * from biguser where id=       21576455         ;

select sql_no_cache * from biguser where id=       5341705  ;

select sql_no_cache * from biguser where id=       66182739         ;

select sql_no_cache * from biguser where id=       54662774         ;

select sql_no_cache * from biguser where id=       59012070         ;

select sql_no_cache * from biguser where id=       52293127         ;

select sql_no_cache * from biguser where id=       29983640         ;

select sql_no_cache * from biguser where id=       21078779         ;

select sql_no_cache * from biguser where id=       14937593         ;

select sql_no_cache * from biguser where id=       17458594         ;

select sql_no_cache * from biguser where id=       7928885  ;

select sql_no_cache * from biguser where id=       73174679         ;

select sql_no_cache * from biguser where id=       21741457         ;

select sql_no_cache * from biguser where id=       86001336         ;

select sql_no_cache * from biguser where id=       52555158         ;

select sql_no_cache * from biguser where id=       23325556         ;

select sql_no_cache * from biguser where id=       63580254         ;

select sql_no_cache * from biguser where id=       83699681         ;

select sql_no_cache * from biguser where id=       10742003         ;

select sql_no_cache * from biguser where id=       81051582         ;

select sql_no_cache * from biguser where id=       45963004         ;

select sql_no_cache * from biguser where id=       95947261         ;

select sql_no_cache * from biguser where id=       19606015         ;

select sql_no_cache * from biguser where id=       24078829         ;

select sql_no_cache * from biguser where id=       676655     ;

select sql_no_cache * from biguser where id=       30713754         ;

select sql_no_cache * from biguser where id=       29905848         ;

select sql_no_cache * from biguser where id=       99763851         ;

select sql_no_cache * from biguser where id=       1495837  ;

select sql_no_cache * from biguser where id=       71316631         ;

select sql_no_cache * from biguser where id=       88798686         ;

select sql_no_cache * from biguser where id=       94214594         ;

select sql_no_cache * from biguser where id=       54310537         ;

select sql_no_cache * from biguser where id=       81828596         ;

select sql_no_cache * from biguser where id=       89927714         ;

select sql_no_cache * from biguser where id=       72684768         ;

select sql_no_cache * from biguser where id=       50764835         ;

select sql_no_cache * from biguser where id=       50720945         ;

select sql_no_cache * from biguser where id=       63690819         ;

select sql_no_cache * from biguser where id=       14890423         ;

select sql_no_cache * from biguser where id=       94762181         ;

select sql_no_cache * from biguser where id=       45487268         ;

select sql_no_cache * from biguser where id=       50795204         ;

select sql_no_cache * from biguser where id=       43415976         ;

select sql_no_cache * from biguser where id=       44953453         ;

select sql_no_cache * from biguser where id=       83626479         ;

select sql_no_cache * from biguser where id=       42730160         ;

select sql_no_cache * from biguser where id=       15229715         ;

select sql_no_cache * from biguser where id=       22058075         ;

select sql_no_cache * from biguser where id=       35277893         ;

select sql_no_cache * from biguser where id=       90205735         ;

select sql_no_cache * from biguser where id=       12501310         ;

select sql_no_cache * from biguser where id=       24653162         ;

select sql_no_cache * from biguser where id=       34248456         ;

附二：

use nopart ;

drop procedure p_hash_select;

delimiter //

create procedure p_hash_select(vid bigint)

begin

set @i=mod(vid,10);

if @i=1 then

  select * from user01 where;

end if;

if @i=2 then

  select * from user02 where;

end if;

if @i=3 then

  select * from user03 where;

end if;

if @i=4 then

  select * from user04 where;

end if;

if @i=5 then

  select * from user05 where;

end if;

if @i=6 then

  select * from user06 where;

end if;

if @i=7 then

  select * from user07 where;

end if;

if @i=8 then

  select * from user08 where;

end if;

if @i=9 then

  select * from user09 where;

end if;

if @i=0 then

  select * from user10 where;

end if;

end//

d