全部产品
云市场

二级分区表

更新时间:2019-08-13 22:31:30

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

语法

  1. CREATE TABLE table_name (
  2. column_name data_type [NOT NULL][DEFAULT 'default'][COMMENT 'comment'][, …],
  3. primary key (column_name[, ])
  4. )
  5. PARTITION BY HASH KEY(column_name) [PARTITION NUM N]
  6. SUBPARTITION BY LIST (subpart_col long)
  7. SUBPARTITION OPTIONS (available_partition_num = NUM)
  8. TABLEGROUP tablegroup_name
  9. options (updateType='realtime');

参数

绝大多数参数和CREATE TABLE 普通表中介绍一样,二级分区表多了两个参数:

  • subpart_col:二级分区列,该列不在定义的列中需要重新定义,类型必须为long。

  • available_partition_num:二级分区数,即为最大保留的二级分区数,当新的数据装载进来后,若线上存在的二级分区数大于这个值,分析型数据库MySQL版会根据二级分区的值进行排序,下线最小的若干分区的数据。

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

示例

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

  1. CREATE TABLE customer (
  2. customer_id bigint NOT NULL COMMENT '顾客ID',
  3. customer_name varchar NOT NULL COMMENT '顾客姓名',
  4. phone_num bigint NOT NULL COMMENT '电话',
  5. city_name varchar NOT NULL COMMENT '所属城市',
  6. sex int NOT NULL COMMENT '性别',
  7. id_number varchar NOT NULL COMMENT '身份证号码',
  8. home_address varchar NOT NULL COMMENT '家庭住址',
  9. office_address varchar NOT NULL COMMENT '办公地址',
  10. age int NOT NULL COMMENT '年龄',
  11. login_time timestamp NOT NULL COMMENT '登录时间',
  12. PRIMARY KEY (customer_id,phone_num,search_time)
  13. )
  14. PARTITION BY HASH KEY (customer_id)
  15. SUBPARTITION BY LIST (search_time long)
  16. SUBPARTITION OPTIONS (available_partition_num = 30)
  17. TABLEGROUP tablegroup_name
  18. OPTIONS (UPDATETYPE='realtime')
  19. COMMENT '客户信息表';

二级分区使用场景

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

具体使用例子可以参考电子商务行业的实践

最佳实践

如果二级分区过多,则会导致多次索引查询、性能下降。如果二级分区过少,会降低用户导入数据的频率,从而影响数据的实时性,所以需要合理选择二级分区数。最佳实践:

  • 单表二级分区数小于等于90,每个一级分区下的二级分区包含的数据条数在300万到2000万之间。
  • 如果单个分区每日增量数据超过300万,则推荐按天进行二级分区;如需要存储的时间范围更长,则可按周、月进行规划。

动态调整二级分区个数

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