MySQL分区表Range方式

  • Z时代
  • 2024-01-10
  • 分类：综合

database

Range分区的特点：

  • 根据分区键值保存到不同表中

  • 多个分区要连续，不能重叠

  • 要完全封口则需要使用MAXVALUE

 

CREATE TABLE `user_login_log` (
`user_id` int(10) unsigned NOT NULL COMMENT "登录⽤用户ID", 
`login_time` datetime NOT NULL COMMENT "⽤用户登录时间", 
`login_ip` int(10) unsigned NOT NULL COMMENT "登录IP", 
`login_type` tinyint(4) NOT NULL COMMENT "登录类型:0未成功 1成功") ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
  PARTITION p0 VALUES LESS THAN (2017),
  PARTITION p1 VALUES LESS THAN (2018),
  PARTITION p2 VALUES LESS THAN (2019),
  PARTITION p3 VALUES LESS THAN (2020),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

 

应⽤场景 

CREATE TABLE `user_login_log` (
`user_id` int(10) unsigned NOT NULL COMMENT "登录⽤用户ID", 
`login_time` datetime NOT NULL COMMENT "⽤用户登录时间", 
`login_ip` int(10) unsigned NOT NULL COMMENT "登录IP", 
`login_type` tinyint(4) NOT NULL COMMENT "登录类型:0未成功 1成功") ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(login_time))(
  PARTITION p0 VALUES LESS THAN (2017),
  PARTITION p1 VALUES LESS THAN (2018),
  PARTITION p2 VALUES LESS THAN (2019),
  PARTITION p3 VALUES LESS THAN (2020),
  PARTITION p3 VALUES LESS THAN (2020)
);

不实用封口的好处是，需要增加分区时执行：

ALTER TABLE user_login_log ADD PARTITION(PARTITION p4 VALUES LESS
THAN(2021))

⽇志数据进⾏备份的时候，在MySQL v5.7版本后加⼊了一个分区交换的概念

  • 表结构要相同

  • 归档的这个表不能是分区表

  • 归档表不能有外键约束

  • 引擎是ARCHIVE

 

#归档表
CREATE TABLE `arch_user_login_log_2016` (
`user_id` int(10) unsigned NOT NULL COMMENT "登录⽤用户ID", `login_time` datetime NOT NULL COMMENT "⽤用户登录时间", `login_ip` int(10) unsigned NOT NULL COMMENT "登录IP", `login_type` tinyint(4) NOT NULL COMMENT "登录类型:0未成功 1成功"
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE user_login_log exchange PARTITION p0 WITH TABLE
arch_user_login_log_2016;
ALTER TABLE arch_user_login_log_2016 ENGINE=ARCHIVE;

  再删除分区文件 

ALTER TABLE user_login_log DROP PARTITION p0;

如果一个表不是分区表并已经有数据了，还可以变成分区表 

ALTER TABLE user_login_log PARTITION BY RANGE(YEAR(login_time))(
  PARTITION p0 VALUES LESS THAN (2017),
  PARTITION p1 VALUES LESS THAN (2018),
  PARTITION p2 VALUES LESS THAN (2019),
  PARTITION p3 VALUES LESS THAN (2020)
);

变成分区表后， 会重排数据，并且数据量大的情况下I/O消耗很⼤，这种数据量⼤的表，要按照修改表结构的方式来进行分区表操作

  • 建立新结构相同的分区表

  • 数据导入

  • 表名切换  

分区表最⼤大有多少个:1024，分区表的限制和注意事项：

  • 如果表里有主键必须包含分区表的分区键

  • 很多时候，使⽤分区表就不要使用主键，建立主键后可能会影响性能

  • 不要建立过多的分区

  • 分区表不⽀持外键

  • 分区规则必须要提前设立好，否则修改很麻烦

分区表的最佳实践

  • 记录⾮常的多，⾮常的大

  • 明显的时间区间特征

  • 查询频次低，就近时间点查询频次⾼

  • 需要定期归档转储

 

以上是 MySQL分区表Range方式 的全部内容， 来源链接： utcz.com/z/533933.html

回到顶部