全部产品
Search
文档中心

云原生数据仓库AnalyticDB:表结构设计

更新时间:Oct 23, 2024

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

选择表类型

AnalyticDB for MySQL支持复制表和普通表两种类型。在选择表类型时,需要注意如下几点:

  • 复制表会在集群的每个节点存储一份数据,因此建议复制表中的数据量不宜太大,每张复制表存储的数据不超过2万行。

  • 普通表(即分区表)能够充分利用分布式系统的查询优势,提高查询效率。普通表可存储的数据量较大,通常可以存储千万条甚至千亿条数据。

选择分布键

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

  • 语法

    DISTRIBUTED BY HASH(column_name,...)
  • 注意事项

    • 尽可能选择值分布均匀的字段作为分布键,例如交易ID、设备ID、用户ID或者自增列作为分布键。

      说明

      尽量不要选择日期、时间和时间戳类型的字段作为分布键,写入时容易发生倾斜影响写入性能,且多数查询通常是限定了日期或者时间段,如:查询最近一天或者一个月的数据,可能会导致要查询的数据只存在于一个节点上,无法充分利用分布式数据库中所有节点的处理能力。日期、时间类型的字段建议作为二级分区来考虑,具体请参见选择分区键

    • 尽可能将需要Join的字段作为分布键,可以有效减少数据Shuffle。例如,需要按照顾客维度查看历史订单信息,可以选择customer_id作为分布键。

    • 尽可能选择高频率出现查询条件的字段作为分布键,从而实现按分布键做裁剪。

    • 每张表只能选取一个分布键,一个分布键可以包含一个字段或者多个字段,尽可能选取少的字段,使得分布键在各种复杂查询中更加通用。

    • 若在创建表时,未指定分布键,系统会根据MySQL表是否含有主键进行如下处理:

      • 如果MySQL表含有主键,AnalyticDB for MySQL默认将主键作为分布键。

      • 如果MySQL表不含有主键,AnalyticDB for MySQL将添加一个__adb_auto_id__字段作为主键和分布键。

选择分区键

如果设置了分布键后,单个分片的数据量较大,您可以通过分区键在分片内进一步设置分区,以提高数据访问的性能。您可以在创建表时,通过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 for MySQL集群性能。如何进行分区字段合理性诊断,请参见分布字段合理性诊断

    • 目前切分粒度只支持年、月、日或原始值。切分粒度太大或太小都会影响查询性能和写入性能,甚至影响AnalyticDB for MySQL集群的稳定性。

    • 尽量使二级分区维持静态状态,不建议频繁更新二级分区,例如,如果有每天频繁更新多个历史二级分区场景,应考虑使用的二级分区字段是否合理。

    • 您可以通过LIFECYCLE N关键字实现表生命周期管理,即对分区进行排序,超出N的分区会被过滤。

      重要

      每张表中支持的最大分区数存在上限,因此分区表中的数据无法永久保留。关于分区数量限制的详情,请参见使用限制

选择主键

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

  • 语法

    PRIMARY KEY (column_name,...)
  • 注意事项

    • 只有定义过主键的表支持数据更新操作(包括DELETE和UPDATE)。

    • AnalyticDB for 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:集群变配时,不会影响分片数。

  • Q:AnalyticDB for MySQL是否支持修改分布键/分区键?

    A:不支持。如果需要修改分布键/分区键,请参见ALTER TABLE