ADB支持通过CREATE TABLE创建表,也支持通过CTAS将查询到的数据写入新表中。

语法

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  | table_constraints}
   [, ... ]  )
   table_attribute
   [partition_options]
   [storage_policy]
   [AS] query_expression
   COMMENT 'string'

column_attributes:
   [DEFAULT default_expr]
   [AUTO_INCREMENT]

column_constraints:
   [{NOT NULL|NULL} ]
   [PRIMARY KEY]

table_constraints:
   [{INDEX|KEY} [index_name] (column_name,...)]
   [PRIMARY KEY [index_name] (column_name,...)]
   [CLUSTERED KEY [index_name] (column_name,...)]

table_attribute:
   DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST

partition_options:
  PARTITION BY 
        {VALUE(column_name) | VALUE(date_format(column_name, ?))}
  LIFECYCLE N
storage_policy:
   STORAGE_POLICY= HOT|COLD

参数

参数 说明
table_name 表名。

表名以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1到127个字符。

支持db_name.table_name格式,区分不同数据库下相同名字的表。

column_name 列名。

列名以字母或下划线(_)开头,可包含字母、数字以及下划线(_),长度为1到127个字符。

column_type 要添加的列的数据类型。

AnalyticDB for MySQL支持的数据类型请参见数据类型

column_attributes
  • DEFAULT default_expr:设置列的默认值,DEFAULT为无变量表达式,例如current_timestamp

    如果未指定默认值,则列的默认值为NULL

  • AUTO_INCREMENT:定义自增列,可选项。

    自增列的数据类型必须是BIGINT类型,AnalyticDB for MySQL为自增列提供唯一值,但自增列的值不是顺序递增。

column_constraints
  • NOT NULL|NULL:定义了NOT NULL的列不允许值为NULL;定义了NULL(默认值)的列允许值为NULL
  • PRIMARY KEY:定义主键。

    如果有多个主键,语法为PRIMARY KEY(column_name [, ... ])

table_constraints INDEX|KEY:倒排索引。

AnalyticDB for MySQL默认为表创建全索引,一般情况下无须手动创建索引。

PRIMARY KEY 主键索引。
  • 只有定义过主键的表支持DELETE和UPDATE操作。
  • 主键中必须包含分区键,建议把分区键放到组合主键之前。
CLUSTERED KEY 聚集索引,定义表中的排序列,聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。

例如,clustered key col5_col6_cls_index(col5,col6)定义了col5 col6的聚集索引,col5 col6col6 col5是不同的聚集索引。

聚集索引会将该列或者多列进行排序,保证与该列相同或者相近的数据存储在磁盘的相同或相近位置。当以聚集列做为查询条件时,查询结果存储在磁盘的相同位置,这样可以减少磁盘的IO,提高查询性能。

如何判断是否需要聚集索引:查询一定会携带的字段可以作为聚集索引。例如,SAAS类应用中,用户通常只访问自己的数据,用户ID可以定义为聚集索引,保证数据的局部性,提升数据查询性能。

聚集列有以下限制:

  • 每张表中只支持创建一个聚集列索引。
  • 由于聚集索引会进行全表排序,导致数据写入性能下降、CPU占用较高,因此一般不建议使用聚集索引。
DISTRIBUTED BY HASH(column_name,...) 在普通表中定义表的分布键,按照column_name的HASH值进行分区。

AnalyticDB for MySQL支持将多个字段作为分区键。

DISTRIBUTED BY BROADCAST 用于定义维度表,维度表会在集群的每个节点存储一份数据,因此建议维度表的数据量不宜太大。
partition_options 普通表中定义分区。

AanlyticDB for MySQL通过LIFECYCLE N方式实现表生命周期管理,即对分区进行排序,超出N的分区将被过滤掉。

例如,PARTITION BY VALUE(column_name)表示使用column_name的值来做分区,PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m%d'))表示将column_name格式化为类似20190101的日期格式做分区。LIFECYCLE 365表示每个节点最多保留的分区个数为365,即如果数据保存天数为365天,则第366天写入数据后,系统会自动删除第1天写入的数据。

storage_policy 冷热数据存储参数,仅限弹性模式-集群版(新版)实例支持。指定该表数据是存储在SSD介质还是存储在HDD介质,不同介质数据读写性能不同,存储成本不同。

storage_policy = HOT,表示该表数据存储在SSD介质。

storage_policy = COLD,表示该表数据存储在HDD介质。

注意事项

  • 创建表时,AnalyticDB for MySQL集群默认编码格式为utf-8,相当于MySQL中的utf8mb4编码,暂不支持其他编码格式。
  • 目前AnalyticDB for MySQL集群支持创建的最大表数目如下所示:
    • 集群版:min(节点组数量*256,10000)。
    • 基础版:
      • T8,500。
      • T16和T32,1500。
      • T52,2500。

示例

  • 新建TEST表。
    create table test (
           id bigint auto_increment,
           name varchar,
           value int,
           ts timestamp
    )
    DISTRIBUTED BY HASH(id)                  

    TEST为普通表,id为自增列,分布键为id,按照id值进行HASH分区。

  • 新建CUSTOMER表。
    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 '客户信息表';                   

    CUSTOMER表为普通表,customer_id为分布键,login_time为分区键,login_timecustomer_idphone_num为组合主键。

MySQL语法兼容性说明

AnalyticDB for MySQL标准建表语法中必须包含DISTRIBUTED BY ...,而MySQL建表语法中没有DISTRIBUTED BY ...。AnalyticDB for MySQL默认兼容MySQL建表语法,您可以根据实际情况通过以下两种方式处理DISTRIBUTED BY ...不兼容问题。

  • 如果MySQL表含有主键,AnalyticDB for MySQL默认将主键作为DISTRIBUTED BY COLUMN
    mysql> create table t (c1 bigint, c2 int, c3 varchar, primary key(c1,c2));
    Query OK, 0 rows affected (2.37 sec)
    mysql> show create table t;
    +-------+-------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                  |
    +-------+-------------------------------------------------------------------------------------------------------------------------------+
    | t     | Create Table `t` (
     `c1` bigint,
     `c2` int,
     `c3` varchar,
     primary key (c1,c2)
    ) DISTRIBUTED BY HASH(`c1`,`c2`) INDEX_ALL='Y' |
    +-------+-------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.04 sec)
  • 如果MySQL表不含主键,AnalyticDB for MySQL将添加一个__adb_auto_id__字段作为主键和DISTRIBUTED BY COLUMN
    mysql> create table t (c1 bigint, c2 int, c3 varchar);
    Query OK, 0 rows affected (0.50 sec)
    mysql> show create table t;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                              |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | t     | Create Table `t` (
     `c1` bigint,
     `c2` int,
     `c3` varchar,
     `__adb_auto_id__` bigint AUTO_INCREMENT,
     primary key (__adb_auto_id__)
    ) DISTRIBUTED BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.04 sec)

冷热数据分层存储

为了降低数据存储成本,同时保证查询性能,将查询频度高的数据称为热数据,存储在SSD介质;将查询频度低的数据称为冷数据,存储在HDD介质。

通过建表语句指定该表是热数据还是冷数据,示例如下:

create table t1
(a int
, b datetime) 
distribute by hash(a) 
partition by value(date_format('%Y%m') lifecycle 30 
storage_policy = HOT|COLD;

可以通过alter table语句变更该表冷热数据存储属性,示例如下:

alter table t1 storage_policy = HOT|COLD;
说明 目前仅弹性模式集群版(新版)实例支持冷热数据分层功能。