mysql中常用的分区/分表

一个数据表里边可以存储许多记录信息,如果一个数据表里边存储的数据非常多(例如 淘宝商城 的商品表),这样该商品表的相关工作量就很多(数据的增、删、改、查)

负载(工作量)高到一定程度,会造成把表锁死的情况发生。为了降低商品表的负载/工作量,可以给该表拆分为多个数据表。这样每个数据表的工作量会有多降低。

Mysql5.1版本之后就支持分表分区的设计。

宏观拆分可以如下:

Goods数据表需要拆分:Goods_1 Goods_2 Goods_3…..Goods_10

数据表拆分为以后,需要考虑php如何操作这些数据表。

php————([手动/mysql]算法)————–数据表(分表)

手动算法:需要在php语言里边设计操作逻辑,增加php语言的代码工作量

mysql算法:php语言不需要做额外操作就可以像以往一样操作同一个数据表的不同分区,是mysql分表推荐的方式

一、四种分表分区算法

各种分区设计关联的字段必须是主键的一部分
或者是主键本身、或者是复合主键索引的从属主键部分 ,Myisam与InnoDB匀可以实现分区

分区分类:

  • 求余:
    key 根据指定的字段 进行分区
    hash 根据指定的表达式进行分区
  • 条件:
    range 根据字段/表达式 符合某个条件范围分区
    list 根据字段/表达式 符合某个列表范围分区

1、key分区算法

create table goods(
    id int auto_increment,
    name varchar(32) not null default '',
    price int not null default 0,
    pubdate date not null default '1000-01-01',
    primary key (id)
) engine=Myisam charset=utf8 
partition by key(id) partitions 10;

上述根据主键ID创建10分区

2、hash分区算法

--hash 表达式分区
--month()函数可以获得时间信息的“月份”信息
create table goods_HH(
    id int auto_increment,
    name varchar(32) not null default '',
    price int not null default 0,
    pubdate date not null default '1000-01-01',
    primary key (id,pubdate)
) engine=Myisam charset=utf8 
partition by hash(month(pubdate)) partitions 12;

insert into goods_HH values (null,'apple',5000,'2015-9-23');
insert into goods_HH values (null,'apple',5000,'2015-1-23');
insert into goods_HH values (null,'apple',5000,'2015-2-23');
insert into goods_HH values (null,'apple',5000,'2015-3-23');
insert into goods_HH values (null,'apple',5000,'2015-12-23');

#给数据表写入数据,数据会根据“月份”添加到对应的分区表中:

3、range分区算法

--range 条件范围分区
--根据年代给数据包进行分区设计
--range(字段/表达式)
partition by range(字段/表达式)(
    partition 分区名字  values less than (常量),
)
create table goods_RR(
    id int auto_increment,
    name varchar(32) not null default '',
    price int not null default 0,
    pubdate date not null default '1000-01-01',
    primary key (id,pubdate)
) engine=Myisam charset=utf8 
partition by range(year(pubdate))(
    partition hou70 values less than(1980),
    partition hou80 values less than(1990),
    partition hou90 values less than(2000),
    partition hou00 values less than(2010)
);

insert into goods_RR values (null,'apple',5000,'1975-3-23');
insert into goods_RR values (null,'apple',5000,'1998-12-23');

#数据添加的时候根据年份,写入到对应的分区

4、list分区算法

--list 列表范围分区
--根据 月份所属季节分区设计
partition by range(字段/表达式)(
    partition 分区名字  values in (n1,n2,n3..),
)
create table goods_LL(
    id int auto_increment,
    name varchar(32) not null default '',
    price int not null default 0,
    pubdate date not null default '1000-01-01',
    primary key (id,pubdate)
) engine=Myisam charset=utf8 
partition by list(month(pubdate))(
    partition spring values in(3,4,5),
    partition summer values in(6,7,8),
    partition autumn values in(9,10,11),
    partition winter values in(12,1,2)
);
#四种分区特点分析:key:该方式区分不明显(不一定会严格平均给分区分配数据),

#但是大方向明显hash/range/list:会根据业务特点把数据写入到对应的分区表里边

二、管理分区

1、求余算法(key、hash)

增加分区:alter table 表名 add partition partitions 数量;
减少分区:alter table 表名 coalesce partition 数量;

注意:减少分区,对应分区数据要丢失

2、条件算法(range、list)

增加分区:
     alter table 表名 add partition(
         partition 分区名  values less than [in](常量|列表),
         partition 分区名  values less than [in](常量|列表),
     )
 减少分区:
     alter table 表名 drop  partition 分区名称;
     减少分区,会丢失对应分区的数据。

小伙伴们多多尝试!

发表评论

电子邮件地址不会被公开。 必填项已用*标注