MySQL数据表分区手记

当数据已存,对现有的表进行分区,分区算法使用list算法,根据type字段进行分区

ALTER TABLE `articles`
PARTITION BY list (type)
(
PARTITION p1 VALUES in(1),
PARTITION p2  VALUES in(2),
PARTITION p3  VALUES in(3),
PARTITION p4  VALUES in(4)
);

由于type不是主键,mysql报错信息如下

A PRIMARY KEY must include all columns in the table's partitioning function
分区的字段必须是要包含在主键当中。这时候分区的字段要么是主键,要么把分区字段加入到主键中,从而形成复合主键。

注意:如果表中有主键或唯一索引,则创建分区的字段必须是主键 或 唯一索引 的一部分!

1. MySQL分区之 list分区

list :条件值为一个数据列表。
算法:根据“字段的内容值”是否在某个“列表”中进行分区,通过预定义的列表的值来对数据进行分割。

例子:假如你创建一个如下的一个表,该表保存有全国20家分公司的职员记录,这20家分公司的编号从1到20.而这20家分公司分布在全国4个区域,如下表所示:

职员表:emp
id name store_id(分公司的id)

北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
东部 3,12,19,20
西部 8,14,15,16

create table emp(
    id int,
    name varchar(32),
    store_id int
)engine myisam charset utf8
partition by list (store_id)(
    partition p_north values in (1,4,5,6,17,18),
    partition p_east values in(2,7,9,10,11,13),
    partition p_south values in(3,12,19,20),
    partition p_west values in(8,14,15,16)
);
#插入数据
insert into emp values(12,’xiaobao’,14)

查看执行计划得知,只有在条件语句中带有分区条件值,才会在设置的分区进行查找,否则会在所有分区中查找。

MySQL分区的实测手记

2. MySQL分区之 Range 范围

算法:根据字段内容的值是否在某个范围进行分区,通过预定义的范围值来对数据进行分割。

这种模式允许将数据划分不同范围。例如可以将一个表通过月份划分成若干个分区

create table p_range(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by range (month(birthday))(
    partition p_1 values less than (3),
    partition p_2 values less than(6),
    partition p_3 values less than(9),
    partition p_4 values less than MAXVALUE
);

less than 小于;
MAXVALUE可能的最大值

3. MySQL分区之 Hash 哈希

分区字段的值对 分区个数 取模(%)运算,根据余数将数据分配到不同的分区。

注意:hash分区只能针对整数进行hash!

这种模式允许通过对表的一个或多个列的Hash Key进行计算,最后通过这个Hash码不同数值对应的数据区域进行分区。

说白了就是:基于给定的分区个数,将数据分配到不同的分区。

例如可以建立一个对表主键进行分区的表。

# 按照生日的月份hash值,将数据记录划分到5个区内:
create table  p_hash(
    id  int,
    name varchar(20),
    birthday date
)engine myisam charset utf8
partition by hash(month(birthday)) partitions 5;

4. MySQL分区之 Key 键值

上面Hash模式的一种延伸(KEY分区支持除text和BLOB之外的所有数据类型的分区),这里的Hash Key是MySQL系统产生的。

对分区字段的值 进行HASH运算(系统自行运算),根据得到的 Hash key,将数据分配到对应的分区中。

#根据id分区,分五个区
create table p_key(
    id int,
    name varchar(32),
    birthday date
)engine myisam charset utf8
partition by key (id) partitions 5;

注意:KEY分区不允许使用用户自定义的表达式进行分区,
例如:key(month(birthday))是不允许的。

5. 删除分区

① 在key/hash领域不会造成数据丢失(删除分区后数据会重新整合到剩余的分区去,即合并分区)
② 在range/list领域会造成数据丢失(真实删除数据)

求余方式(key/hash):

alter table 表名 coalesce partition 数量;

范围方式(range/list):

alter table 表名 drop partition 分区名称;

MySQL分区的实测手记

MySQL分区的实测手记

MySQL分区的实测手记

6. 增加分区

增加分区

求余方式: key/hash

alter table 表名 add partition partitions 数量;

范围方式: range/list

>  alter table 表名 add partition(
           partition 名称 values less than (常量)
           或
           partition 名称 in (n,n,n)
       );

MySQL分区的实测手记

本作品采用《CC 协议》,转载必须注明作者和本文链接
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!