MySQL分区和分布性能测试[转]

最新推荐文章于 2023-10-18 10:14:57 发布
最新推荐文章于 2023-10-18 10:14:57 发布
阅读量175 收藏
点赞数
文章标签： 数据库
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.5149.11416.25816.893

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

 

 

四，压力测试

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

　myisam
未分区		myisam
分区		Myisam
分表		Innodb
未分区		innodb
分区
100连续随机查询4.554.115.3894.4455.893
　　　　　　
并发50随机查询0.0520.0490.0750.070.038
并发100随机查询0.0710.0780.110.0880.055
并发250随机查询0.1370.1530.2390.1750.124
并发500随机查询0.2660.2730.4470.2830.24
并发1000随机查询0.50.5330.8490.4850.46
　　　　　　
并发50随机插入0.0370.030.040.0420.025
并发100随机插入0.0710.060.0570.060.049
并发250随机插入0.1790.1590.1440.1350.132
并发500随机插入0.3840.3270.3320.2480.25
并发1000随机插入0.820.730.7370.5420.526
　　　　　　
并发50随机删除1.2041.5360.1960.0410.039
并发100随机删除2.3332.9450.3620.0620.063
并发250随机删除5.5685.7180.8310.140.173
并发500随机删除9.6639.7051.420.2750.308
并发1000随机删除19.87717.2542.6550.5890.634
　　　　　　
并发50随机更新1.0080.8870.2980.0440.046
并发100随机更新1.7891.6880.5330.0640.062
并发250随机更新4.2913.9861.0290.1340.145
并发500随机更新8.3577.8721.7830.3060.288
并发1000随机更新15.96315.1263.330.5750.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

weixin_33973609
关注
MySQL 分区表介绍与测试
For-的博客
05-26 695
一、基本介绍： 分区表是一个独立的逻辑表，底层由多个物理的文件组成，分区对于 SQL 层来说是透明的； 下图可以看出，表 em_elec_meter_data 的多个分区是使用了 # 进行标识。 分区表的使用场景： 表非常大以至于无法全部都放在内存中，或者热点数据只有在表的最后部分，其他都是历史数据。 分区表的数据作用在不同的设备上，高效地利用多个硬件设备；例如有 1TB 的数据，服务器的硬盘容量只有 500 GB，那么可以使用其他设备平摊未能放下的数据。 需要独立备份和恢复分区，这在非常大数据集的场景
mysql分区分表对比测试过程
不忘初心
12-22 3183
一，故事背景。某一天，几位大哥找小Q说：小Q，最近我们要做数据分区和分表的测试，这个任务就交给你了。小Q内心是紧张的：mysql学习还没开始，分区分表的测试居然交给了我。不过，想着反正也没学过，趁着机会学学看。小Q就答应下来了。开始项目分区分表测试。 二，测试阶段。明确了测试任务后，小Q就开始屁颠屁颠地准备测试阶段 1，第一阶段。 1）从DAO层的sql语句开始。测试分区和不分区之间的性能区别。比
MySQL 5.5.8 分区表性能测试
03-20
测试结果 博文链接：https://hawthorstein.iteye.com/blog/888755
MYSQL分区表测试
热门推荐
08-20 2万+
一、       mysql分区简介 数据库分区 数据库分区是一种物理数据库设计技术。虽然分区技术可以实现很多效果，但其主要目的是为了在特定的SQL操作中减少数据读写的总量以缩减sql语句的响应时间，同时对于应用来说分区完全是透明的。 MYSQL的分区主要有两种形式：水平分区和垂直分区   水平分区（HorizontalPartitioning） 这种形式的分区是对根据表的行进行分区，
高性能MySql进化论(十二):Mysql中分区表的使用总结
白雾霾
12-17 182
当数据量非常大时（表的容量到达GB或者是TB），如果仍然采用索引的方式来优化查询，由于索引本生的消耗以及大量的索引碎片的产生，查询的过程会导致大量的随机I/O的产生，在这种场景下除非可以很好的利用覆盖索引，否则由于在查询的过程中需要根据索引回数据表查询，会导致性能受到很大的影响，这时可以考虑通过分区表的策略来提高查询的性能。 不同的数据库管理系统对分区的实现可能有所区别，本文主要以MYSQL为基...
mysql检验分区性能的操作
最新发布
xzy的博客
10-18 402
如上图我们给part_tab5创建的分区为1024个，因为mysql中允许最多有1024个分区；之前我测试的是创建8个分区，然后插入500万条数据，然后按照id查询（非索引），part_tab5表的查询速率大约是part_tab6表的查询速率的9倍；注意这里测试的时候不要按照主键查询，因为主键是索引，按照索引查询的时候，即便没有分区，也是非常快的。现在我很好奇如果我创建了1024个分区，然后插入5000万条数据，带分区的查询速率会比不带分区的快多少呢？
MySql数据分区操作之新增分区操作
12-15
在设计分区策略时，应考虑数据的分布、访问模式以及业务需求，以实现最佳的性能和存储效率。在MySQL中，支持多种分区类型，包括范围、列表、哈希和线性哈希分区，每种都有其适用场景。例如，时间序列数据通常适合...
MySQL分区字段列有必要再单独建索引吗？
12-16
在MySQL数据库中，分区是一种优化查询性能的技术，它将大表的数据分成多个逻辑部分...在实践中，应通过实际的性能测试和查询分析来确定最佳策略。使用EXPLAIN分析查询执行计划可以帮助评估索引的效果，并据此做出决策。
数据库表中数据分表测试
qq_43093448的博客
03-18 544
测试数据的问题，数据库每次加减数据，带来的是每次都要调整数据，比较繁琐，可以从数据库里备份不同的数据，这样以后用到什么数据的话可以直接换表或者库。以mysql为例： 1.我需要测试表里有1W,10W,20W的数据，我把他们分别放置在3张表里（表结构相同），表名不要重复，每次测试需要的数据，只要改表名，就可以了。 ...
MySQL分表和分区最佳攻略
10-12
常开发中我们经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。
mysql 分区 性能,高性能mysql之分区表
weixin_34984850的博客
03-17 199
mysql在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候优化器会根据分区定义过滤那些没有我们需要数据的分区，这样只需要查找包含需要数据的分区就可以了以下场景适合运用表分区技术：1，表非常大以至于无法全部放在内存中。2，分区表的数据更容易维护，可以对一个独立的分区进行优化，检查，修复等操作。3，分区表的数据可以在不同的无力设备上，从而高效的利用多个硬件设备4，可以...
MYSQL 分表测试
小倪粑粑的BLOG
05-26 373
因为MYSQL需要5.5以上,所以在192.168.168.243上单独装了一个MYSQL Mycat 数据库分库分表中间件 192.168.168.243  user : root  pwd :123456 port: 8066 1.schema.xml 定义tabe 2.rule.xml 定义 规则 测
mysql 分区性能_MySQL 分区性能探究(持续更新中)
weixin_29890207的博客
01-18 319
数据准备1.1 创建分区表part_tabcreatetablepart_tab(c1intDEFAULTnull,c2varchar(30)DEFAULTnull,c3datedefaultnull)engine=myisamPARTITIONBYRANGE(year(c3))(PARTITIONp0VALUESLESSTHAN(1995),PAR...
mysql分表分区方案实测：MyISAM引擎分表 or mysql表分区
shandalue的专栏
07-26 5445
mysql分表分区方案实测：MyISAM引擎分表 or mysql表分区
mysql 分表 性能提升_MYSQL性能优化--分库分表
weixin_29554849的博客
01-19 353
1、分库分表1>纵向分表将本来可以在同一个表的内容，人为划分为多个表。(所谓的本来，是指按照关系型数据库的第三范式要求，是应该在同一个表的。)分表理由：根据数据的活跃度进行分离，(因为不同活跃的数据，处理方式是不同的)案例：对于一个博客系统，文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。而博客的浏览量，回复数等，类似的统计信息，...
高性能 MySQL（十二）：分区表
水滴的博客
08-07 839
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中，这样做可以将相关的数据存放在一起。在执行查询时，优化器会根据分区定义过滤掉那些没有我们需要数据的子表，这样，查询就无需扫描所有子表了（只会查找包含需要数据的子表）。......
MySql分区表性能测试及切换案例
weixin_34258078的博客
05-03 194
背景 互联网公司的业务变化很快，数据库表结构设计相对比较直接，很少会在前期设计的很完善。当业务存活并发展起来后，就需要在扩展性、安全性等方面进行改进。 比如，我们一张记录用户状态的表，存储在RDS for MySql（InnoDB存储引擎）中。此业务表最近膨胀到1.5亿条记录，存储占用30多G，且数据还在不断增长。 虽然目前整体性能表现尚可，但部分操作耗...
mysql分表自测
散尽浮华
01-05 529
最近公司还好，但一想到过年就到了最头疼的时候了，现在先写一下分区，回忆一些，到时候老大没准就让我分了： 拿用户表(user)举例：如果它有1000w数据，肯定是要考虑优化的，那就分表吧，分区下一篇讲解，首先我是用pdo写了一个脚本是pdo.php内容是：(只是连接数据库用的) $dsn = 'mysql:dbname=库名;host=主机号'; $user = '用户名'; $pass
MySQL分区表详解与测试实践
"MYSQL分区表测试" 在MySQL数据库中，分区表是一种高级的物理数据库设计技术，旨在提升查询性能和简化数据管理。分区主要是通过将一个大的表逻辑上划分为更小、更易管理的部分，而对应用程序来说，这些分区是透明的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值