本文介绍如何设计AnalyticDB MySQL版的表结构(包括选择表类型、分布键、分区键、主键和聚集索引键等),从而实现表性能的优化。

选择表类型

AnalyticDB MySQL版支持复制表和普通表两种类型。在选择表类型时,需要注意如下几点:
  • 复制表会在集群的每个节点存储一份数据,因此建议复制表中的数据量不宜太大,每张复制表存储的数据不超过2万行。
  • 普通表(即分区表)能够充分利用分布式系统的查询优势,提高查询效率。普通表可存储的数据量较大,通常可以存储千万条甚至上亿条数据。

选择分布键

如果业务明确有增量数据导入需求,创建普通表时可以同时指定分布键和分区键,来实现数据的增量同步。您可以在创建表时,通过DISTRIBUTED BY HASH(column_name,...)指定分布键,按照column_name的Hash值进行分区。更多详情,请参见CREATE TABLE

  • 语法
    DISTRIBUTED BY HASH(column_name,...)
  • 注意事项
    • 尽可能将需要Join的字段作为分布键。例如,需要按照顾客维度查看历史订单信息,可以选择customer_id作为分布键。

      AnalyticDB MySQL版支持将多个字段作为分布键。

    • 尽可能选择值分布均匀的字段作为分布键,例如交易ID、设备ID、用户ID或者自增列作为分布键。
    • 若在创建表时,未指定分布键,系统会根据MySQL表是否含有主键进行如下处理:
      • 如果MySQL表含有主键,AnalyticDB MySQL版默认将主键作为分布键。
      • 如果MySQL表不含有主键,AnalyticDB MySQL版将添加一个__adb_auto_id__字段作为主键和分布键。
    • AnalyticDB MySQL版 3.0弹性模式集群版(新版)8核和16核规格的集群,默认分片数为32。
    • AnalyticDB MySQL版 3.0弹性模式集群版(新版)32核及以上规格的集群,默认分片数与EIU数量有关,对应关系如下:
      EIU数量 分片数
      EIU=1 32
      2<=EIU<4 64
      4<=EIU<8 128
      8<=EIU<16 192
      16<=EIU<32 256
      32<=EIU<64 512
      EIU>=64 1024
    • AnalyticDB MySQL版 3.0预留模式集群版的默认分片数与节点组数量有关,对应关系如下:
      节点组数 分片数
      节点组数=1 16
      2<=节点组数<4 32
      4<=节点组数<8 64
      8<=节点组数<16 128
      16<=节点组数<32 256
      32<=节点组数<64 512
      节点组数>=64 1024
      说明 关于默认分片数的常见问题,请参见常见问题

选择分区键

如果设置了分布键后,单个分片的数据量较大,您可以通过分区键在分片内进一步设置分区,以提高数据访问的性能。您可以在创建表时,通过PARTITION BY 来定义二级分区,数据会将按照指定方式进行切分。更多详情,请参见CREATE TABLE

  • 语法
    • 使用column_name的值做分区,语法如下:
      PARTITION BY VALUE(column_name)
    • column_name的值转换为%Y%m%d的日期格式(类似20210101)做分区,语法如下:
      PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m%d'))
    • column_name的值转换为%Y%m的日期格式(类似202101)做分区,语法如下:
      PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m'))
    • column_name的值转换为%Y的日期格式(类似2021)做分区,语法如下:
      PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y'))
  • 注意事项
    • 当数据量较大时,二级分区的选择至关重要,如果数据量大的表中没有二级分区或者二级分区切分不合理,将严重影响AnalyticDB MySQL版集群性能。
    • 目前切分粒度只支持年、月、日或原始值。切分粒度太大或太小都会影响查询性能和写入性能,甚至影响AnalyticDB MySQL版集群的稳定性。
    • 一个二级分区的数据量在3亿~10亿之间视为合理切分。如果小于3亿,表示切分粒度太小,可以增大切分粒度(例如将切分粒度由日改为月);如果大于10亿,表示切分粒度太大,可以减小切分粒度(例如将切分粒度由月改为日)。
    • 尽量使二级分区维持静态状态,不建议频繁更新二级分区,例如如果有每天频繁更新多个历史二级分区场景,应考虑使用的二级分区字段是否合理。
    • 您可以通过LIFECYCLE N关键字实现表生命周期管理,即对分区进行排序,超出N的分区会被过滤。
      说明 由于每张表中支持的最大分区数存在上限,因此分区表中的数据无法永久保留。关于分区数量限制的详情,请参见约束和限制

选择主键

主键可以作为每一条记录的唯一标识。您可以在创建表时,通过PRIMARY KEY来定义主键。更多详情,请参见CREATE TABLE

  • 语法
    PRIMARY KEY (column_name,...)
  • 注意事项
    • 只有定义过主键的表支持数据更新操作(包括DELETE和UPDATE)。
    • AnalyticDB MySQL版的主键可以是单个字段或多个字段的组合。推荐使用数值类型的单个字段作为主键,以获得较好的表性能。
    • 主键中必须包含分布键和分区键,建议将分布键和分区键放在组合主键的前部。

选择聚集索引键

聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。选择聚集索引键时需要注意如下几点:
  • 每个表仅支持创建一个聚集索引。创建方式,请参见CREATE TABLE
  • 建议将查询一定会携带的字段作为聚集索引键。例如,每个学生在学校教务系统中,只需查看自己的期末成绩,那么可以将学生的学号ID定义为聚集索引,来保证数据的局部性,提升数据查询性能。
  • 由于聚集索引会进行全表排序,导致数据写入性能下降、CPU占用较高,因此一般不建议使用聚集索引。

示例

创建一张customer表,需要满足如下要求:
  • 根据顾客的登录时间(即login_time列)进行数据分区,且需要将登录时间转换为%Y%m%d日期格式。
  • 仅保留最近30个分区(即生命周期为30)的数据。
  • 根据顾客ID(即customer_id列)进行数据分布。
  • login_time, customer_id, phone_num设置为组合主键。
建表语句如下:
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 '登录时间',
PRIMARY KEY (login_time, customer_id, phone_num)
 )
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT '客户信息表';

常见问题

  • Q:进行二级分区后,如何查看表的所有二级分区及分区的统计信息?
    A:执行以下SQL,可以查看表的所有二级分区及分区的统计信息:
    SELECT partition_id, --分区名
              row_count, -- 分区总行数
              local_data_size, --分区本地存储所占用空间大小
              index_size, -- 分区的索引大小
              pk_size, --分区的主键索引大小
              remote_data_size --分区的远端存储所占用空间大小
    FROM information_schema.kepler_partitions
    WHERE schema_name = '$DB'
     AND table_name ='$TABLE' 
     AND partition_id > 0;
    注意 针对增量数据中还未触发compaction的分区不予展示。如果需要查看实时的所有二级分区列表,可以通过select distinct $partition_column from $db.$table;进行查询。
  • Q:分片数跟什么因素有关系?用户是否可以自己调整?

    A:分片数是创建集群时根据集群初始规格自动计算得出的。不支持用户调整分片数。

  • Q:在集群变配时,是否会对分片数产生影响?

    A:集群变配时,不会影响分片数。