如何设计AnalyticDBMySQL表结构,分布键,分区键 - 云原生数据仓库AnalyticDB

本文介绍如何设计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。