MySQL分区表

分区表的特点

在逻辑上为一个表,在物理上存储在多个文件中

分区键

分区引入了分区键的概念,分区键用于根据某个区间值、特定值、或者HASH函数值执行数据的聚集,让数据根据规则分布在不同的分区中。

分区类型

RANGE分区
LIST分区
HASH分区

无论那种分区类型,要么分区表上没有主键/唯一键,要么分区表的主键/唯一键都必须包括分区键,也就是说不能使用主键/唯一字段之外的其他字段分区

RANGE分区

特点

1. 根据分区键值的范围把数据行存储到表的不同分区中
2. 多个分区的范围要连续,但是不能重叠
3. 分区不包括上限,取不到上限值

建立分区前我们应先查看自己的MySQL是否支持分区

show plugins;

看到表中最下面一行的partition就是代表的分区

建立RANGE分区

create table `range_table`(
	id int(10) unsigned not null comment '登录用户ID',
	time timestamp not null default CURRENT_TIMESTAMP,
	ip int(10) unsigned not null comment '登录ip'
)engine=innodb
partition by range(id)(
	partition p0 values less than(10000), --实际范围0-9999
	partition p1 values less than(20000), --实际范围10000-19999
	partition p2 values less than(30000),
	partition p3 values less than maxvalue --存储大于30000的数据
);

insert into range_table values(9999,now(),122);
insert into range_table values(10000,now(),122);
insert into range_table values(19999,now(),122);
insert into range_table values(20000,now(),122);
insert into range_table values(30000,now(),122);

我们要查看一个数据处于哪个分区的话可以在 explain 语句中加入partitions

如上图就代表着id=19999的数据在p1分区中。

RANGE分区使用场景

1. 分区键为日期或是时间类型
2. 经常运行包含分区键的查询,MySQL可以很快的确定只有某一个或某些分区需要扫描,例如检索商品login_id小于10000的记录数,MySQL只需要扫描p0分区即可
3. 定期按分区范围清理历史数据

HASH分区

特点

1. 根据MOD(分区键,分区值)的值把数据行存储到表的不同分区内
2. 数据可以平均的分布在各个分区中
3. HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型

如何建立HASH分区表

create table `hash_table`(
	id int(10) unsigned not null comment '登录用户ID',
	time timestamp not null default CURRENT_TIMESTAMP,
	ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(id) partitions 4;


create table `hash_table`(
	id int(10) unsigned not null comment '登录用户ID',
	time timestamp not null default CURRENT_TIMESTAMP,
	ip int(10) unsigned not null comment '登录ip'
	)engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(time)
) partitions 4;

LIST分区

特点

1. 按分区键取值的列表进行分区
2. 同范围分区一样,各分区的列表值不能重复
3. 每一行数据必须能找到对应的分区列表,否则数据插入失败

建立LIST分区

create table `list_table`(
	id int(10) unsigned not null comment '登录用户ID',
	time timestamp not null default CURRENT_TIMESTAMP,
	ip int(10) unsigned not null comment '登录ip',
	type int(10) not null
)engine=innodb
partition by list(type)(
	partition p0 values in(1,3,5,7,9),
	partition p1 values in(2,4,6,8)
);

查询表的分区

select table_name,partition_name,partition_description,table_rows from
information_schema.`partitions` where table_name = 表名;

修改分区-添加分区

alter table 表名 add partition (partition 分区名 values 分区条件);
alter table range_table add partition (partition p4 values less than(40000));

此时如果最后一个分区已经是到 MAXVALUE 则无法修改。

分区删除

alter table 表名 drop partition 分区名;
alter table range_table drop partition p3;

使用分区表的注意事项

1. 结合业务场景选择分区键,避免跨分区查询
2. 对分区表进行查询最好在where从句中包含分区键
3. 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分