MySQL分区表Range方式

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，分区表的限制和注意事项：

• 如果表里有主键必须包含分区表的分区键
• 很多时候，使⽤分区表就不要使用主键，建立主键后可能会影响性能
• 不要建立过多的分区
• 分区表不⽀持外键
• 分区规则必须要提前设立好，否则修改很麻烦

分区表的最佳实践

• 记录⾮常的多，⾮常的大
• 明显的时间区间特征
• 查询频次低，就近时间点查询频次⾼
• 需要定期归档转储