MySQL分区和分布性能测试[转]
一，测试服务器配置：
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
