二级分区表是分析型数据库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版支持动态调整二级分区个数。当数据存储时间周期发生变化时，需要动态调整二级分区个数。具体语法参见修改二级分区数。