二级分区表是分析型数据库MySQL版向用户提供的高级功能,用于实现数据的增量同步。

语法

CREATE TABLE table_name (
column_name data_type [NOT NULL][DEFAULT 'default'][COMMENT 'comment'][, …],
    primary key (column_name[, … ])
)
PARTITION BY HASH KEY(column_name) [PARTITION NUM N]
SUBPARTITION BY LIST (subpart_col long)
SUBPARTITION OPTIONS (available_partition_num = NUM)
TABLEGROUP tablegroup_name
options (updateType='realtime');

参数

绝大多数参数和创建普通表语法中介绍一样,详情请参见CREATE TABLE,二级分区表多了两个参数:

  • subpart_col:二级分区列,该列不在定义的列中需要重新定义,类型必须为long。
  • available_partition_num:二级分区数,即为最大保留的二级分区数,当新的数据装载进来后,若线上存在的二级分区数大于这个值,分析型数据库MySQL版会根据二级分区的值进行排序,下线最小的若干分区的数据。

另外,primary key的定义也是要强调下,primary key中必须包含二级分区列。

示例

新建customer表,以search_time为二级分区列,二级分区保留个数为30。

CREATE TABLE customer (
  customer_id bigint NOT NULL COMMENT '顾客ID',
  customer_name varchar NOT NULL COMMENT '顾客姓名',
  phone_num bigint NOT NULL COMMENT '电话',
  city_name varchar NOT NULL COMMENT '所属城市',
  sex int NOT NULL COMMENT '性别',
  id_number varchar NOT NULL COMMENT '身份证号码',
  home_address varchar NOT NULL COMMENT '家庭住址',
  office_address varchar NOT NULL COMMENT '办公地址',
  age int NOT NULL COMMENT '年龄',
  login_time timestamp NOT NULL COMMENT '登录时间',  
  search_time timestamp NOT NULL COMMENT '搜索时间',
  PRIMARY KEY (customer_id,phone_num,search_time)
)
PARTITION BY HASH KEY (customer_id) 
SUBPARTITION BY LIST (search_time long)
SUBPARTITION OPTIONS (available_partition_num = 30)
TABLEGROUP tablegroup_name
OPTIONS (UPDATETYPE='realtime')
COMMENT '客户信息表';

二级分区使用场景

一般情况下,当一级分区数据量随时间增大到超过单个一级分区记录数最佳值(2000万~3000万)时,可以考虑设计二级分区。二级分区可以理解为按队列方式管理分区个数,当超过最大定义数,最小值分区自动删除,循环使用空间,所以二级分区是自动清除历史数据。

具体使用例子,请参见电子商务行业的实践

最佳实践

如果二级分区过多,则会导致多次索引查询、性能下降。如果二级分区过少,会降低用户导入数据的频率,从而影响数据的实时性,所以需要合理选择二级分区数:
  • 单表二级分区数小于等于90,每个一级分区下的二级分区包含的数据条数在300万到2000万之间。
  • 如果单个分区每日增量数据超过300万,则推荐按天进行二级分区;如需要存储的时间范围更长,则可按周、月进行规划。

动态调整二级分区个数

分析型数据库MySQL版支持动态调整二级分区个数。当数据存储时间周期发生变化时,需要动态调整二级分区个数。具体语法参见修改二级分区数