MySQL分区表使用场景及特点介绍
程序员文章站
2022-06-08 22:05:26
...
MySQL分区表使用场景及特点介绍
本文旨在介绍MySQL分区表的运用场景及其特点,MySQL分区表主要有
- Hash分区表
- Range分区表
- LIST分区表
一、Hash分区表
Hash分区表特点:逻辑上为一个表,但在物理上存储在多个文件中,以用户登陆日志表为例,
user_login_log(
log_id int '日志ID',
login_id int '登陆用户ID',
login_time datetime '用户登陆时间',
login_ip int '登陆IP',
login_result int 登陆结果:0未成功,1成功',
PRIMARY KEY pk_loginid(log_id)
) ENGINE = innodb COMMENT '用户登陆日志表'
PARTITION BY HASH(login_id) PARTITIONS 4;
与一般创建表的区别就在于加了 PARTITION这个命令。
文件结构上的区别
普通表结构:
user_login_log.frm
user_login_log.ibd
Hash分区表结构:
user_login_log.frm
user_login_log#P#p0.ibd
user_login_log#P#p1.ibd
user_login_log#P#p2.ibd
user_login_log#P#p3.ibd
Hash分区表的特点:
- 根据MOD(分区建,分区数)的值把数据行存储到表的不同分区
- 数据可以平均的分布在各个分区中
- HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型比如 UNIX_TIMESTAMP(login_time)
二、Range分区表
Range分区表的特点:根据分区键值的范围把数据行存储到表的不同分区中
多个分区的范围要连续,但是不能重复
默认情况下使用VALUES LESS THAN属性,即每个分区不包括指定的那个值
user_login_log(
log_id int '日志ID',
login_id int '登陆用户ID',
login_time datetime '用户登陆时间',
login_ip int '登陆IP',
login_result int 登陆结果:0未成功,1成功',
PRIMARY KEY pk_loginid(log_id)
) ENGINE = innodb COMMENT '用户登陆日志表'
PARTITION BY RANGE (login_id ) (
PARTITION p0 VALUES LESS THAN (10000),
PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (10000),
PARTITION p3 VALUES LESS THAN MAXVALUE
);
Range分区表的特点:
- 分区键为日期或是时间类型
- 所有SELECT查询中都包括分区键
三、LIST分区表
LIST分区表的特点:
- 按分区键取值的列表进行分区
- 同范围分区一样,各分区的列表值不能重复
- 每一行数据必须能找到对应的分区列表,否则数据插入失败
user_login_log(
log_id int '日志ID',
login_id int '登陆用户ID',
login_time datetime '用户登陆时间',
login_ip int '登陆IP',
login_result int 登陆结果:0未成功,1成功',
PRIMARY KEY pk_loginid(log_id)
) ENGINE = innodb COMMENT '用户登陆日志表'
PARTITION BY LIST (login_result) (
PARTITION p0 VALUES (1,3,5,7,9),
PARTITION p1 VALUES (2,4,6,8)
);
使用分区表的注意事项
- 结合业务场景选择分区键,避免跨分区查询
- 对分区表进行查询最好在WHERE从句中包含分区键
- 具有主键或唯一索引的表,主键或唯一索引必须是分区键的一部分