云原生数据仓库AnalyticDB MySQL版支持通过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'|'MIXED' [hot_partition_count=N]

参数

参数 说明
table_name 表名。

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

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

column_name 列名。

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

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

AnalyticDB MySQL版支持的数据类型，请参见基础数据类型。

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

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

  • AUTO_INCREMENT：定义自增列，可选项。

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

column_constraints
  • NOT NULL|NULL：定义了NOT NULL的列不允许值为NULL；定义了NULL（默认值）的列允许值为NULL。
  • PRIMARY KEY：定义主键。

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

table_constraints INDEX|KEY：倒排索引。

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

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

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

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

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

聚集列有以下限制：

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

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

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

通过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天写入的数据。

说明
  • 二级分区不是整张表级，而是每个Shard级别。如果数据分布不均，则会导致整张表保留的二级保存分区数大于N。
  • 二级分区不是实时清理的，是后台异步任务清理的。
storage_policy
说明 目前仅弹性模式集群版（新版）实例支持冷热数据分层存储功能。

指定热（HOT）、冷（COLD）、或混合（MIXED）的存储策略。默认值为HOT。

不同存储策略下数据读写性能不同，存储成本不同。为了降低数据存储成本，同时还要保证查询性能，您可以选择将查询频度高的数据（称为热数据）存储在SSD介质；将查询频度低的数据（称为冷数据）存储在HDD介质。

根据业务需求，您还可以按表粒度、表的二级分区粒度独立选择冷、热存储介质。例如，指定这个表数据全部存储在SSD，或者全部存储在HDD，或者指定这个表的一部分二级分区存储在SSD，另一部分二级分区存储在HDD。

  • HOT、COLD、MIXED大小写兼容。
  • HOT: 所有分区都在SSD。
  • COLD: 所有分区都在HDD。
  • MIXED: 部分分区在SSD，部分分区在HDD，需要通过hot_partition_count指定存在SSD上的分区的数量。
hot_partition_count=N 指定MIXED存储策略时热分区的个数。表示按分区键的值的大小倒序排列，最大N个分区为热分区，其他分区为冷分区。
  • N为非零正整数。
  • 指定MIXED策略时，必须同时指定热分区的个数；其他策略禁止指定hot_partition_count=N。

注意事项

  • 创建表时，AnalyticDB MySQL版集群默认编码格式为utf-8，相当于MySQL中的utf8mb4编码，暂不支持其他编码格式。
  • 目前AnalyticDB 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_time、customer_id、phone_num为组合主键。

MySQL语法兼容性说明

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

  • 如果MySQL表含有主键，AnalyticDB MySQL版默认将主键作为DISTRIBUTED BY COLUMN。 
    CREATE TABLE t (c1 bigint, c2 int, c3 varchar, PRIMARY KEY(c1,c2));
Query OK, 0 rows affected (2.37 sec)

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 MySQL版将添加一个__adb_auto_id__字段作为主键和DISTRIBUTED BY COLUMN。 
    CREATE TABLE t (c1 bigint, c2 int, c3 varchar);
Query OK, 0 rows affected (0.50 sec)

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)

创建表时指定冷热数据存储策略

说明 目前仅弹性模式集群版（新版）实例支持冷热数据分层存储功能。

您可以在创建表时指定冷热存储策略。

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'
   
storage_policy:
   STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]
示例：创建表时指定冷（COLD）存储策略 
CREATE TABLE test_table (
 L_ORDERKEY bigint NOT NULL,
 L_LINENUMBER int NOT NULL,
 L_SHIPDATE date NOT NULL,
 dummy varchar,
 primary key (l_orderkey,l_linenumber,l_shipdate)
) DISTRIBUTE BY HASH(l_orderkey) 
PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
STORAGE_POLICY='COLD';
示例：创建表时指定热（HOT）存储策略 
CREATE TABLE test_table (
 L_ORDERKEY bigint NOT NULL,
 L_LINENUMBER int NOT NULL,
 L_SHIPDATE date NOT NULL,
 dummy varchar,
 primary key (l_orderkey,l_linenumber,l_shipdate)
) DISTRIBUTE BY HASH(l_orderkey) 
PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
STORAGE_POLICY='HOT';
示例：创建表时指定混合（MIXED）存储策略，同时指定热分区数量为16个 
CREATE TABLE test_table (
 L_ORDERKEY bigint NOT NULL,
 L_LINENUMBER int NOT NULL,
 L_SHIPDATE date NOT NULL,
 dummy varchar,
 primary key (l_orderkey,l_linenumber,l_shipdate)
) DISTRIBUTE BY HASH(l_orderkey) 
PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;

创建带向量索引的表

您可以在创建表时同步创建ann索引。定义为：
ann index [index_name] (col_name,...)] [algorithm=HNSW_PQ ] [dis_function=SquaredL2]
示例：
CREATE TABLE fact_tb (  
xid bigint not null,  
cid bigint not null,  
uid varchar not null,  
vid varchar not null,  
wid varchar not null,  
short_feature array < smallint >(32),  
float_feature array < float >(32),  
ann index short_feature_index(short_feature), 
ann index float_feature_index(float_feature),  
PRIMARY KEY (xid, cid, vid)
) DISTRIBUTE BY HASH(xid) PARTITION BY VALUE(cid) LIFECYCLE 4;
参数说明：
  • short_feature/float_feature：向量列的名称，用户自定义。
  • array<float>(32)：向量列的数据类型和向量的维数，用户自定义。必须要指定向量的维度，支持的类型包括： float、byte、short三种数组类型。表示对feature_data的列类型定义为512，示例如下：
    feature_data` array<float>(512)
  • ann：系统关键字。
  • index：系统关键字。
  • short_feature_index/float_feature_index：索引名，用户自定义。对feature_data这一列创建向量索引：
    ann index ecnn_index(`FEATURE_DATA`) algorithm=HNSW_PQ dis_function=SquaredL2

    algorithm、dis_function定义可以省略，默认值分别为HNSW_PQ、SquaredL2。

  • algorithm：向量距离计算公式使用的算法。AnalyticDB MySQL版支持的向量距离计算公式算法如下表所示。
    算法 适用场景 适用数据类型
    HNSW_PQ 适用于单表数据量在百万级别到千万级别之间，对向量维度敏感的中等规模数据量场景。 short[]、byte[]、float[]
  • dis_function：向量距离计算公式，默认值为SquaredL2。AnalyticDB MySQL版支持的向量距离计算公式如下表所示。
    距离计算公式 计算公式 适用数据类型
    SquaredEuclidean（简称SquaredL2） (x1-y1)^2+(x2-y2)^2+… byte[]、short[]或者float[]
使用向量检索查询
  1. 查询与向量'[1,1,1,1]'距离最近的5条记录：
    SELECT id, l2_distance(short_feature, '[1,1,1,1]') FROM fact_tb ORDER BY 2 LIMIT 5;
  2. 带向量索引查询与向量'[1,1,1,1]'距离最近的5条记录，按距离排序：
    SELECT id, l2_distance(short_feature, '[1,1,1,1]') FROM fact_tb WHERE xid = 1 AND cid = 0 ORDER BY 2 LIMIT 5;
  3. 带向量索引查询与向量'[1,1,1,1]'距离最近的5条记录，按距离排序，并控制距离最大范围：
    SELECT id, l2_distance(float_feature, '[1.2,1.5,2,3.0]') FROM fact_tb WHERE l2_distance(float_feature, '[1.2,1.5,2,3.0]') < 50.0 AND xid = 2 ORDER BY 2 LIMIT 5;