MySQL分区表

MySQL分区表

分区表的特点

在逻辑上为一个表，在物理上表数据存储于多个文件中。

分区键

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

分区类型

• RANGE分区
• LIST分区
• HASH分区

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

添加分区

alter table tb_name(表名) add partition (partition p_name(分区名) values less than(字段值))

删除分区

alter table tb_name(表名) drop partition p_name(分区名);

使用分区表的注意事项

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

RANGE分区

RANGE分区特点

• 根据分区键值的范围把数据行存储到表的不同分区中
• 多个分区的范围要连续，但是不能重叠
• 分区取不到上限值

建立RANGE分区表

create table user(
id int(10) primary key not null,
name varchar(10)
)engine=innodb
partition by range(id)(
partition p0 values less than(1000), # 实际范围0-999
partition p1 values less than(2000), # 实际范围1000-1999
partition p2 values less than(3000)  # 实际范围2000-2999
)

创建表之后自动生成三个分区数据文件

RANGE分区使用场景

• 分区键为日期或是时间类型
• 经常运行包含分区键的查询，MySQL可以很快的确定只有某一个或某些分区需要扫描
• 定期按分区范围清理历史数据

HASH分区

HASH分区的特点

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

建立HASH分区表

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

因为u_time是一个timestamp类型，所以以它为分区键时必须转为一个INT类型的数据

LIST分区

LIST分区特点

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

建立LIST分区表

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