MySQL分区表
MySQL分区表
分区表的特点
在逻辑上是一个表，在物理上存储在多个文件中
create table `login_log`(
login_id int(10) unsigned not null comment '登录用户id',
login_time timestamp not null default current_timestamp,
login_ip int(10) unsigned not null comment '登录类型'
)engine=innodb default charset=utf8 partition by hash(login_id) partitions 4;
分区键
分区引入了分区键的概念，分区键用于根据某个区间值、特定值或者hash函数值执行数据的聚集，让数据根据规则分布在不同的分区中。
分区类型
- range分区
- list分区
- hash分区
无论哪种分区类型，要么分区表上没有主键/唯一键，要么分区表的主键/唯一键都必须包括分区键，也就是说使用主键/唯一字段外的其他字段分区。
range分区
range分区特点
- 根据分区键值的范围把数据行存储到表的不同分区中
- 多个分区的范围要连续，但是不能重叠
- 分区不包括上限，取不到上限值
建立range分区
create table `login_log_range`(
login_id int(10) unsigned not null comment '登录用户ID',
login_time timestamp not null default CURRENT_TIMESTAMP,
login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb
partition by range(login_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的数据
);
range分区使用场景
- 分区键为日期或者时间类型
- 经常运行包括分区键的查询，MySQL可以很快确定只有某一个或某些分区需要扫描，例如检索商品login_id小于10000的记录数，MySQL只需要扫描p0分区即可
- 定期按分区范围清理历史数据
hash分区
hash分区特点
- 根据MOD(分区键，分区值)的值把数据行存储到表的不同分区中
- 数据可以平均的分布在各个分区中
- hash分区的键值必须是一个int类型的值，或是可以通过函数转化为int类型
如何建立hash分区表
create table `login_log`(
login_id int(10) unsigned not null comment '登录用户ID',
login_time timestamp not null default CURRENT_TIMESTAMP,
login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(login_id) partitions 4;
create table `login_log`(
login_id int(10) unsigned not null comment '登录用户ID',
login_time timestamp not null default CURRENT_TIMESTAMP,
login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(login_time)) partitions 4;
list分区
list分区特点
- 按分区键取值的列表进行分区
- 同范围分区一样，各分区的列表值不能重复
- 每一行数据必须找到对应的分区列表，否则数据插入失败
建立list分区
create table `login_log_list`(
login_id int(10) unsigned not null comment '登录用户ID',
login_time timestamp not null default CURRENT_TIMESTAMP,
login_ip int(10) unsigned not null comment '登录ip',
login_type int(10) not null
)engine=innodb
partition by list(login_type)(
partition p0 values in(1,3,5,7,9),
partition p1 values in(2,4,6,8)
);
如何选择合适的分区方式
业务场景
- 用户每次登录都会记录到日志表中
- 用户登录日志保存一年，一年后可以删除
create table `login_log_range`(
login_id int(10) unsigned not null comment '登录用户id',
login_time datetime not null default current_timestamp,
login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb
partition by range(year(login_time))(
partition p0 values less than(2015),
partition p1 values less than(2016),
partition p2 values less than(2017)
);
插入数据
insert into login_log_range values
(1,'2015-01-25',1),
(2,'2015-07-25',2),
(3,'2015-06-25',3),
(4,'2016-03-25',2),
(5,'2016-02-25',1);
查询表
select table_name,partition_name,partition_description,table_rows from
information_schema.`partitions` where table_name = 'login_log_range'
修改分区—添加分区
alter table login_log_range add portition (partition p4 values less than(2018));
分区删除
alter table login_log_range drop partition p0;
使用分区表注意事项
- 结合业务场景使用分区键，避免跨分区查询
- 对分区表进行查询最好在where从句中包含分区键
- 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分
