全部产品
Search
文档中心

云原生数据仓库AnalyticDB MySQL版:CREATE TABLE创建分区表与维表

更新时间:Jun 11, 2024

本文介绍AnalyticDB for MySQLCREATE TABLE建表语法。您将了解到如何创建分区表和维表,以及如何定义表的分布键、分区键、索引、生命周期、冷热数据分层等。

表的数据分布策略

建表前,您可以通过下图中的示例,了解关于表的几个重要概念,包括分片、分区、聚集索引。

image

语法

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
  | table_constraints}
  [, ... ])
  [table_attribute]
  [partition_options]
  [storage_policy]
  [block_size]
  [engine]
  [rt_engine]
  [table_properties]
  [AS query_expr]
  [COMMENT 'table_comment']

column_attributes:
  [DEFAULT {constant | CURRENT_TIMESTAMP}]
  [AUTO_INCREMENT]

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

table_constraints:
  [{INDEX|KEY} [index_name] (column_name,...)]
  [{INDEX|KEY} [index_name] (column_name->'$[*]')]
  [FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]] [,...]
  [PRIMARY KEY [index_name] (column_name,...)]
  [CLUSTERED KEY [index_name] (column_name,...)]
  [[CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES pk_table_name (pk_column_name)][,...]
  [ANN INDEX [index_name] (column_name,...) [index_option]] [,...]

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

partition_options:
  PARTITION BY 
        {VALUE(column_name) | VALUE(date_format(column_name, 'format'))}
  LIFECYCLE N

storage_policy:
  STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' {hot_partition_count=N}}

block_size:
  BLOCK_SIZE= VALUE

engine:
  ENGINE= 'XUANWU|XUANWU_V2'

参数

table_name、column_name、column_type、COMMENT

参数

说明

table_name

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

您可以使用db_name.table_name,指定在某个数据库下创建表。

column_name

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

column_type

列的数据类型。AnalyticDB MySQL版支持的数据类型,请参见基础数据类型复杂数据类型

COMMENT

为列或表添加备注信息。

column_attributes(默认值与自增列)

DEFAULT {constant | CURRENT_TIMESTAMP}

定义列的默认值。仅支持常量CURRENT_TIMESTAMP函数。不支持其他函数和变量表达式。

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

AUTO_INCREMENT

定义自增列。 自增列的数据类型必须是BIGINT类型。

AnalyticDB for MySQL为自增列提供唯一值,但自增列的值不是顺序递增,且不支持从1开始递增

column_constraints(非空与主键)

NOT NULL

定义了NOT NULL的列,值不能为NULL。不定义NOT NULL或定义了NULL时,值可以为NULL

PRIMARY KEY

定义主键。在列约束(column_constraints)中,只能定义单一列作为主键,例如id BIGINT NOT NULL PRIMARY KEY。如需多个列作为主键,请在表约束(table_constraints)中定义复合主键。

table_constraints(索引)

AnalyticDB for MySQL支持多种索引,包括INDEX索引、主键索引、聚集索引、外键索引、全文索引、向量索引等。一个表可以有一种或多种索引。

INDEX | KEY

定义普通索引。INDEX和KEY作用相同。

默认情况下,AnalyticDB for MySQL自动为全表所有列创建索引。但是,如果您在建表时手动指定为某一列或某几列创建索引(例如INDEX (id)),则AnalyticDB for MySQL不会再为表中其他列自动创建索引。

PRIMARY KEY

定义主键索引。

基本使用:

  • 每个表只能有一个主键。

  • 主键可以是单个列或多个列的组合,例如PRIMARY KEY (id)PRIMARY KEY (id,name)

  • 主键中必须包含分布键分区键,并且建议将分布键分区键放在主键的前部

注意事项

  • 无主键的表,不能执行DELETE和UPDATE操作。

  • 未定义主键,会有以下行为:

    • 如果未定义主键和分布键,AnalyticDB for MySQL自动添加一个列__adb_auto_id__作为表的主键和分布键

    • 如果未定义主键,但定义了分布键,AnalyticDB for MySQL不会自动添加主键

  • 建表后,不能增加、减少或变更主键列。

调优建议:推荐使用数值类型的列作为主键,并尽量减少主键包含的列的个数,以获得较好的性能。

说明

主键包含的列过多,可能导致:

  • 数据写入时,AnalyticDB for MySQL检查主键是否重复,将消耗更多的CPU和IO资源。

  • 主键索引将占用更多的磁盘空间。您可以使用空间分析功能,查看主键索引占用的磁盘空间。

  • 主键包含的列越多,BUILD任务会越慢。

CLUSTERED KEY

定义聚集索引。聚集索引是分区级别的,它决定了数据的物理存储顺序,即分区内的数据会按聚集索引的键值进行排序,按顺序存储。聚集索引的键值相同或相近的数据存储在相同或相近数据块。在范围查询或等值查询中,如果查询条件与聚集索引列一致,存储引擎可快速读取连续的数据块,这样可以减少磁盘的I/O,加快数据读取的速度。

聚集索引示意图

image

适用场景:

聚集索引既适用于范围查询,也适用于等值查询。高频出现在范围查询条件和等值查询条件的列,可以作为聚集索引。

当查询条件涉及的列与聚集索引列完全一致或部分一致时,可以加快数据读取的效率。例如,在SaaS类应用中,用户通常只访问自己的数据,用户ID可以作为聚集索引,保证同一用户ID的数据连续存储在相同或相邻的数据块中,数据读取更快。

基本使用:

  • 每个表中只能有一个聚集索引。

  • 聚集索引可以基于单个列创建(例如CLUSTERED KEY index(id)),也可以基于多个列创建(例如CLUSTERED KEY index(id,name))。当聚集索引键涉及多个列时,数据会先根据第一个列的值排序,在第一个列的值相同时,按第二个列的值进行次级排序。所以CLUSTERED KEY index(id,name)CLUSTERED KEY index(name,id)是不同的聚集索引。

  • 如果字段值较长,例如长达十几KB或几十KB的字符串,则不建议聚集索引采用该字段,避免影响排序性能。

FULLTEXT INDEX | FULLTEXT KEY

定义全文索引。FULLTEXT INDEX与FULLTEXT KEY作用相同。关于全文索引的更多介绍,请参见创建全文索引

语法与参数说明

语法[FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]] [,...]

参数说明:

  • index_name:全文索引名称。

  • column_name:全文索引的列。列的类型必须是VARCHAR类型。

  • index_option:指定全文索引的分词器和自定义词典。非必填。

FOREIGN KEY

定义外键索引。外键索引用于消除不必要的JOIN,关于JOIN消除的详情,请参见通过主外键约束消除多余的JOIN

语法与参数说明

版本说明:

AnalyticDB for MySQL集群内核版本需为3.1.10或以上

说明 如何查看集群内核版本,请参见如何查看实例版本信息。如需升级内核版本,请联系技术支持。

语法[[CONSTRAINT [symbol]] FOREIGN KEY (fk_column_name) REFERENCES pk_table_name (pk_column_name)][,...]

参数说明:

  • symbol:可选项,外键约束名,在表内唯一。不指定时,解析器将会在外键列名后面自动补充后缀_fk用作外键约束名。

  • fk_column_name:指定外键列。外键列需要在建表语句中定义。

  • pk_table_name:指定主表名。主表必须已存在。

  • pk_column_name:指定外键约束列,该列必须存在且为主表的主键列。

基本使用:

  • 每个表可以有多个外键索引。

  • 不支持复合的外键索引,即不支持多个列组成的外键索引,例如:FOREIGN KEY (sr_item_sk, sr_ticket_number) REFERENCES store_sales(ss_item_sk,d_date_sk)

  • AnalyticDB for MySQL不会进行数据的约束检查。您需要自行确保主表的主键和从表的外键之间的数据约束关系。

  • 外表不支持创建外键约束。

ANN INDEX

定义向量索引。关于向量索引和向量检索的更多介绍,请参见向量检索

语法与参数说明

语法[ANN INDEX [index_name] (column_name,...) [index_option]] [,...]

参数说明:

  • index_name:向量索引的名称。

  • column_name:向量列的列名。向量列的类型需为array<float>、array<smallint>、array<byte>,且需指定向量列的维数,向量列的定义示例:feature array<float>(4)

  • index_option:向量索引的属性。

    • algorithm:向量距离计算公式使用的算法。取值仅支持HNSW_PQ,适用于单表数据量在百万级别到千万级别之间,对向量维度敏感的中等规模数据量场景。

    • dis_function:向量距离计算公式。取值仅支持SquaredL2。计算公式:(x1-y1)^2+(x2-y2)^2+…

JSON INDEX

定义JSON索引或JSON Array索引。更多介绍,请参见JSON索引

语法与参数说明

JSON索引

版本说明:

  • 3.1.5.10及以上内核版本的集群,创建表后不会自动创建JSON索引,您需手动创建JSON索引。

  • 3.1.5.10以下内核版本的集群,创建表后会自动为JSON列创建JSON索引。

说明 如何查看集群内核版本,请参见如何查看实例版本信息。如需升级内核版本,请联系技术支持。

语法[INDEX [index_name] (column_name|column_name->'$.json_path'.)]

参数说明:

  • index_name:索引的名称。

  • column_name|column_name->'$.json_path':

    • column_name:JSON索引的列。

    • column_name->'$.json_path':JSON索引的列及其指定的属性键。每一个JSON索引只能有一个JSON列的一个属性键。

      重要
      • 仅3.1.6.8及以上内核版本的集群支持column_name->'$.json_path

        如何查看集群内核版本,请参见如何查看实例版本信息。如需升级内核版本,请联系技术支持。

      • 为JSON列中的指定属性键创建索引时,若该JSON列已存在INDEX索引,需先删除该列的INDEX索引,否则会报错。

JSON Array索引

版本说明:

3.1.10.6及以上内核版本的集群支持创建JSON Array索引。

说明 如何查看集群内核版本,请参见如何查看实例版本信息。如需升级内核版本,请联系技术支持。

语法[INDEX [index_name] (column_name->'$[*]')]

参数说明:

  • index_name:索引的名称。

  • column_name->'$[*]':column_name为JSON Array索引的列。例如:vj->'$[*]'表示为vj列创建JSON Array索引。

table_attribute(分布键)

table_attribute决定了表的类型是普通表还是复制表。

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

  • DISTRIBUTED BY BROADCAST,定义表为复制表。复制表会在集群的每个分片存储一份数据,因此建议每个复制表中的数据量不宜太大,最好不超过2万行。

DISTRIBUTED BY HASH (column_name,...)

定义表的分布键。定义了分布键的表,又称普通表。AnalyticDB for MySQL对分布键的值进行哈希计算,根据计算得出的哈希值,将不同行的数据分散到不同分片(Shard),有利于提高可扩展性和查询性能。

数据分片示意图

image

基本用法:

  • 每个表只能有一个分布键

  • 一个分布键可以包含一个列或者多个列。

  • 分布键中的列,必须包含在主键中。例如,分布键为customerid,那么主键也需要包含customerid。

注意事项

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

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

    • 如果MySQL表不含有主键,AnalyticDB for MySQL自动添加一个__adb_auto_id__作为主键和分布键

  • 建表后,不能增加、减少或变更分布键列。如果需要修改分布键,需重新建表并迁移数据,具体操作请参见ALTER TABLE

调优建议:

  • 建议分布键包含尽可能少的列,使得分布键在各种复杂查询中更加通用。

  • 尽可能选择高频率出现在查询条件中,且值分布均匀的列作为分布键,例如交易ID、设备ID、用户ID或者自增列作为分布键。但如果查询条件非常局限,例如列a虽然值分布均匀且高频出现在查询条件中,但总是以a=3的形式出现在查询条件中,那么列a作为分布键会造成数据热点,列a就不适合作为分布键。

  • 尽可能将需要Join的列作为分布键。参与Join的两个表,按相同的分布键(Join列)进行数据分布,使得两个表相同键值的数据被分布到同一分片,可直接在同一分片进行Join操作,无需在分片之间进行数据传输,能够有效减少查询过程中的数据重分布,提升查询性能。例如,需要按照顾客维度查看历史订单信息,可以选择customer_id作为分布键。

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

  • 您可以利用数据建模诊断功能查看表的分布键是否合理、数据是否倾斜。详情请参见数据建模诊断

DISTRIBUTED BY BROADCAST

定义复制表。复制表会在集群的每个节点存储一份该表的全量数据,因此建议复制表的数据量不宜太大。

优点:Join查询时,无需将复制表的数据在不同节点之间传输。当查询并发量较大时,可以有效降低网络传输的开销,提高集群稳定性。

缺点:当复制表数据发生变更(插入、变更或删除)时,变更会被广播到集群的所有节点,确保所有节点上具有一致的数据副本,会影响整体写入性能。因此不建议对复制表进行频繁的增删改等操作。

partition_options(分区键与生命周期)

如果设置了分布键后,单个分片的数据量较大,您可以定义分区键将分片上的数据划分为不同的分区,加快数据过滤速度,提高查询性能。

为什么要定义分区

  • 分区可以加快数据过滤速度,提高查询性能。

    • 分区裁剪。只查询相关数据的分区,跳过无关分区,减少数据扫描,提高查询速度。

    • 索引的扫描性能较好。当索引的行数过大,例如超过5000万行,索引的扫描效率就会下降。索引是分区级别的,即每个分区有一个独立的索引。如果表没有定义数据分区,那么表的所有数据都在一个分区中,数据量超过千万时,索引扫描效率下降。当表定义了数据分区,数据分散在不同分片的不同分区时,每个分区索引的行数可以控制在千万行内,可以保证扫描的性能。

    • 提高Build的效率。Build用于将实时数据转换成历史数据,过程中会构建分区、构建索引、清理冗余数据等。Build任务完成,新的索引才能生效。当表没有定义分区时,每次Build任务都是全表Build,数据越多,Build越慢,新的索引就会越晚生效,从而影响查询性能。当表定义了分区,每次只Build有数据变更的分区,Build的时间就会缩短。

  • 分区结合生命周期(LIFECYCLE),可以实现数据的生命周期管理。

  • 分区结合存储策略(storage_policy),可以实现冷热数据分层。

数据分区与生命周期的示意图

image

PARTITION BY

指定分区键。

语法PARTITION BY VALUE {(column_name) | (DATE_FORMAT(column_name, 'format'))} LIFECYCLE n

参数说明

  • column_name:分区键。PARTITION BY VALUE(column_name)表示使用column_name的值来分区。分区键的数据类型可以是数值类型、日期时间类型或表示数字的字符串类型。

  • DATE_FORMAT(column_name, 'format'):使用DATE_FORMAT函数将日期时间类型的列转换成指定的日期格式,再对数据分区。format仅支持年、月、日,即%Y、%y、%Y%m、%y%m、%Y%m%d、%y%m%d。建表后,format支持修改,修改方法请参见ALTER TABLE

注意事项

  • 使用PARTITION BY定义分区时,必须同时定义生命周期LIFECYCLE N),否则会报错。若不定义分区,数据不会被清理。

  • 建表后,不能增加分区键,也不支持增加、减少或变更修改分区键中的列。如果需要增加或修改分区键,请重新建表并迁移数据,具体操作请参见ALTER TABLE

调优建议:

  • 建议使用日期时间类型的字段作为分区键。

  • 分区太大或太小都会影响查询性能和写入性能,甚至影响集群的稳定性。建议的分区内数据行数以及查看分区是否合理,请参见分区字段合理性诊断

  • 不建议频繁更新历史分区的数据,例如,如果每天频繁更新多个历史分区,应考虑使用的分区键是否合理。

LIFECYCLE n

LIFECYCLE需要与PARTITION BY一起使用。定义每个分片最多保留n个分区,用于管理分区的生命周期。AnalyticDB for MySQL会根据分区键的值,从大到小对分区排序,保留前n个分区,超出n的分区将被删除。您可以利用LIFECYCLE定义数据的保留时长。

例如,PARTITION BY VALUE (DATE_FORMAT(date, '%Y%m%d')) LIFECYCLE 30表示,将date列转换为yyyyMMdd的格式并对数据分区,最多保留30个分区。假设第1天的数据写入分区20231201,第2天的数据写入分区20231201,依次类推,第30天的数据写入分区20231230。当第31天的数据写入分区20231231时,因为最多只能保留30个分区,所以最小的分区(即分区20231201)将会被自动删除。

storage_policy(存储策略)

湖仓版和数仓版弹性模式集群版(新版)支持指定数据的存储策略。不同的存储策略,数据的读写性能不同,数据的存储成本也不同。

取值说明

  • hot(默认值): 热存储,即全表所有分区的数据都存储在SSD。性能最好,但存储成本也最高。

  • cold: 冷存储,即全表所有分区的数据都在OSS。性能比热存储差,但存储成本最低。

  • mixed: 冷热混合存储,又叫冷热分层存储,即查询频率高的分区数据(又称热数据)存储在SSD,查询频率低的分区数据(又称冷数据)存储在OSS,既降低存储成本,又保证查询性能。选择mixed时,必须同时使用PARTITION BY定义分区并使用hot_partition_count指定热分区的数量。如未定义分区,mixed不生效,数据实际会存储在SSD。

    冷热混合存储示意图

    image

hot_partition_count(热分区)

STORAGE_POLICY='mixed'时,需要通过hot_partition_count=n(n为正整数)定义热分区的数量。AnalyticDB for MySQL将根据分区键的值,从大到小对分区排列,最大的n个分区为热分区,其他分区为冷分区。

说明

如果存储策略(STORAGE_POLICY)的取值不是mixed,则不支持指定hot_partition_count=n,否则会报错。

block_size(数据块)

Block,又叫数据块,是数据读写的最小IO单元。block_size用于指定列式存储中每个block存储的数据行数。调整block_size会增加或减少每次IO读取的行数,具体的影响需要结合查询特征,例如点查询时,若block_size较大,存储读block的效率会降低,此时可以适当调小block_size。

默认值说明:

  • 复制表的block_size默认值为4096。

  • 弹性模式集群版(新版)单机版(计算资源为32核以下),block_size默认值为8192。

  • 其它情况下,block_size默认值为32760。当block_size为32760时,在SHOW CREATE TABLE 时,不显示block_size。

重要

若不熟悉列式存储原理,建议不要更改block_size。

engine(存储引擎)

指定AnalyticDB for MySQL内表的存储引擎类型,用于历史数据分析。

取值说明:

  • XUANWU(默认值):若建表时未显式指定ENGINE,则默认选择该值。

  • XUANWU_V2:XUANWU引擎基础上研发的新一代存储引擎。仅V3.2.0及以上内核版本支持XUANWU_V2。需提前开启XUANWU_V2引擎,方法请参见XUANWU_V2引擎介绍

说明
  • 3.1.9.5以下内核版本的集群,如果在创建内表时显式指定了ENGINE='XUANWU',则需同时显示指定table_properties='{"format":"columnstore"}',否则建表会失败。

  • 了解玄武存储引擎的介绍,请参见玄武分析型存储

AS query_expr(CTAS)

CREATE TABLE AS query_expr表示创建表并将SELECT查询结果写入新创建的表。具体用法,请参见CREATE TABLE AS SELECT(CTAS)

示例

新建分区表并设置生命周期

新建普通表customer,login_timecustomer_idphone_num为复合主键, customer_id为分布键,login_time为分区键,分区的生命周期为30。

所有分区,按分区键login_time的值(例如,20231202,20231201等)从大到小排序,仅保留分区键值最大的30个分区,当第31个分区数据写入时,自动删除最小的第1个分区。

假设,第1天login_time的值为20231201,第二天login_time的值为20231202,依次类推,第30天login_time的值为20231230。当第31天login_time为20231231的数据写入时,最小的分区(即'20231201'分区)数据将会被自动删除,从而实现只保留最近30天的数据。

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 '客户信息表';                   

新建表(未定义分布键)

未定义分布键,自动将主键作为分布键

表定义了主键但未定义分布键,AnalyticDB for MySQL默认将主键作为分布键。

CREATE TABLE orders (
  order_id BIGINT NOT NULL COMMENT '订单ID',
  customer_id INT NOT NULL COMMENT '顾客ID',
  order_status VARCHAR(1) NOT NULL COMMENT '订单状态',
  total_price DECIMAL(15, 2) NOT NULL COMMENT '订单金额',
  order_date DATE NOT NULL COMMENT '订单日期',
  PRIMARY KEY(order_id,order_date)
);

查询建表语句,可以看到主键order_id和order_date被采纳为分布键。

SHOW CREATE TABLE orders;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                  | 
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders  | CREATE TABLE `orders` (                                                                                                                       |
|         | `order_id` bigint NOT NULL COMMENT '订单ID',                                                                                                   |
|         | `customer_id` int NOT NULL COMMENT '顾客ID',                                                                                                   |
|         | `order_status` varchar(1) NOT NULL COMMENT '订单状态',                                                                                         | 
|         | `total_price` decimal(15, 2) NOT NULL COMMENT '订单金额',                                                                                      |
|         | `order_date` date NOT NULL COMMENT '订单日期',                                                                                                 |
|         | PRIMARY KEY (`order_id`,`order_date`)                                                                                                         |
|         | ) DISTRIBUTE BY HASH(`order_id`,`order_date`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}'  |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

未定义主键,自动增加主键

表未定义主键,也未定义分布键,AnalyticDB for MySQL将添加一个列__adb_auto_id__作为主键和分布键。

CREATE TABLE orders_new (
  order_id BIGINT NOT NULL COMMENT '订单ID',
  customer_id INT NOT NULL COMMENT '顾客ID',
  order_status VARCHAR(1) NOT NULL COMMENT '订单状态',
  total_price DECIMAL(15, 2) NOT NULL COMMENT '订单金额',
  order_date DATE NOT NULL COMMENT '订单日期'
);

查询建表语句,可以看到表中自动增加一个自增列__adb_auto_id__,该自增列作为表的主键和分布键。

SHOW CREATE TABLE orders_new;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                  | 
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders_new  | CREATE TABLE `orders_new` (                                                                                                                   |
|             | `__adb_auto_id__` bigint AUTO_INCREMENT,                                                                                                      |
|             | `order_id` bigint NOT NULL COMMENT '订单ID',                                                                                                   |
|             | `customer_id` int NOT NULL COMMENT '顾客ID',                                                                                                   |
|             | `order_status` varchar(1) NOT NULL COMMENT '订单状态',                                                                                         | 
|             | `total_price` decimal(15, 2) NOT NULL COMMENT '订单金额',                                                                                      |
|             | `order_date` date NOT NULL COMMENT '订单日期',                                                                                                 |
|             | PRIMARY KEY (`__adb_auto_id__`)                                                                                                               |
|             | ) DISTRIBUTE BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}'        |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

新建表(未定义分区键)

新建表supplier,supplier_id为自增列,分布键为supplier_id,按照supplier_id值进行HASH分片。

CREATE TABLE supplier (
  supplier_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  supplier_name VARCHAR,
  address INT,
  phone VARCHAR
) 
DISTRIBUTED BY HASH(supplier_id);

定义冷热数据存储策略

定义冷(COLD)存储策略

CREATE TABLE item (
  order_id BIGINT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(15, 2) NOT NULL,
  discount DECIMAL(15, 2) NOT NULL,
  shipdate DATE NOT NULL,
  PRIMARY KEY (order_id,item_id,shipdate)
) 
DISTRIBUTE BY HASH(item_id) 
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200 
STORAGE_POLICY='COLD';

定义热(HOT)存储策略

CREATE TABLE item (
  order_id BIGINT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(15, 2) NOT NULL,
  discount DECIMAL(15, 2) NOT NULL,
  shipdate DECIMAL NOT NULL,
  PRIMARY KEY (order_id,item_id,shipdate)
) 
DISTRIBUTE BY HASH(item_id) 
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200 
STORAGE_POLICY='HOT';

定义混合(MIXED)存储策略,同时指定热分区数量为16个

CREATE TABLE item (
  order_id BIGINT NOT NULL,
  item_id INT NOT NULL,
  quantity DECIMAL(15, 2) NOT NULL,
  discount DECIMAL(15, 2) NOT NULL,
  shipdate DATE NOT NULL,
  PRIMARY KEY (order_id,item_id,shipdate)
) 
DISTRIBUTE BY HASH(item_id) 
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200  
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;

定义全文索引

为content列创建全文索引,索引名称为fidx_c。

CREATE TABLE fulltext_tb (
  id INT,
  content VARCHAR,
  keyword VARCHAR,
  FULLTEXT INDEX fidx_c(content),
  PRIMARY KEY (id)
) 
DISTRIBUTE BY HASH(id);

关于创建和变更全文索引的更多内容,请参见创建全文索引

关于全文检索,请参见全文检索

定义向量索引

定义short_feature、float_feature为向量列,类型是array<float>,向量维数为4。

根据short_feature创建向量索引short_feature_index,根据float_feature创建向量索引float_feature_index。

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>(4),  
  float_feature array<float>(4),  
  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;

更多关于向量索引和向量检索的内容,请参见向量检索

定义外键索引

新增一个名为store_returns的表,通过使用外键语法FOREIGN KEYsr_item_sk列和customer表的主键列customer_id关联起来。

CREATE TABLE store_returns (
  sr_sale_id BIGINT NOT NULL PRIMARY KEY,
  sr_store_sk BIGINT,
  sr_item_sk BIGINT NOT NULL,
  FOREIGN KEY (sr_item_sk) REFERENCES customer (customer_id)
);

定义JSON Array索引

为vj列创建JSON Array索引,索引名称为idx_vj。

CREATE TABLE json(
  id INT,
  vj JSON,
  INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);

关于创建和变更JSON Array索引的更多内容,请参见创建JSON Array索引JSON索引

常见问题

列属性和列约束

自增列是从1开始递增吗?值是唯一的吗?

自增列的值不是顺序递增,也不支持从1开始递增。但自增列的值都是唯一值。

分布键、分区键与生命周期

分布键和分区键有什么区别?

根据分布键值的HASH结果不同,数据被分散到不同的分片上。在分片上,根据分区键值的不同,数据被划分为不同的分区。示意图如下。

image

建表时,是否必须指定分布键?

  • 创建分区表,不是必须手动指定分布键。如果未手动指定分布键,AnalyticDB for MySQL会采纳主键作为分布键。无主键时,自动生成一列__adb_auto_id__作为分布键和主键。

  • 创建复制表,不需要指定分布键,但需要指定DISTRIBUTED BY BROADCAST,表示每个存储节点都存储一份全量数据。

在集群变配时,是否改变分片数?

变配不会改变集群的分片数(Shard数量)。

如何查询表的分区信息?

执行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;

创建分区表后,为什么查不到分区信息?

创建分区表后,查不到分区信息主要有两个原因:

  • 建表时,只是通过定义分区键设置了数据分区的规则,并没有开始创建分区。分区由分区键的键值决定。如果表还没写入数据,分区键值为空,不会创建分区。

  • 分区不是实时构建的。当写入的数据BUILD完成后,才能查看到分区信息。

解决方法:

请先写入数据,并等待BUILD任务完成。BUILD任务完成后,可以查看分区信息。

说明

BUILD的详细介绍以及查询BUILD进度,请参见BUILD

分区键的数据类型有什么要求?

分区键的数据类型可以是数值类型、日期时间类型或表示数字的字符串类型。除此以外的数据类型,可能导致数据写入报错。

如果写入数据时,遇到报错partition format function error,说明写入分区键的值不符合数据类型的要求。

指定分区键时,能否使用除DATE_FORMAT以外的其他函数,例如PARTITION BY VALUE(FROM_UNIXTIME(col,'format'))?

不能。定义分区键仅支持两种方法:PARTITION BY VALUE(column)和PARTITION BY VALUE(DATE_FORMAT(column,'format'))。使用其他函数会报错。

如何查看分区表的生命周期?

通过SHOW CREATE TABLE <table_name>,查看建表语句。建表语句中显示分区表的生命周期。

已设置数据只保留30天(即设置LIFECYCLE为30),为什么还是可以查到30天以前的数据?

可能有两个原因:

  • 分区刚刚过期,尚未被删除。过期的分区数据不会被立即删除。当该表的BUILD任务完成后,过期的分区数据才会被删除。

  • LIFECYCLE定义了一个分片上保留多少个分区。当一个分片上,实际分区数小于LIFECYCLE设定值时,可能出现该现象。

    例如:

    • 数据分布不均。假设按日期分区,分片1有分区20231201、20231202、依次类推到20231230;分片2有分区20231202、20231203、依次类推到20231231。分片1和分片2上的分区数均为30,没有超过LIFECYCLE的设定值(30),因此分片1和分片2都不会删除分区。查询数据时,可以查到日期为20231201~20231231的数据。

    • 长时间无数据写入。假设按日期分区,分片1已有分区20231201、20231202、20231203和20231204,并且在20231204后该表没有新的分区数据写入。此时分片1仅有4个分区,没有超过LIFECYCLE的设定值(30),因此不会删除分区。在20231231后,仍然可以查到日期为20231201的数据。

超过生命周期的分区数据,会被立即清理吗?

不会。分区不是实时构建和清理的。表的某个分区过期后,该表需要完成BUILD,分区才会被清理。

索引

如何查询表的聚集索引?

通过SHOW CREATE TABLE查询建表语句中定义的聚集索引。

是否支持唯一索引(UNIQUE INDEX)?

AnalyticDB for MySQL不支持唯一索引(UNIQUE INDEX)。但AnalyticDB for MySQL的主键索引就是唯一索引,可以确保主键值在表中是唯一的。

列存

建表语句中的TABLE_PROPERTIES='{"format":"columnstore"}'是什么意思?

TABLE_PROPERTIES='{"format":"columnstore"}'是固定取值,表示ENGINE中的数据是列存格式。建表时您无需手动指定该属性。

其他

建表后,哪些参数可以通过ALTER TABLE变更?

ALTER TABLE支持变更以下参数:

  • table_name、column_name、column_type、COMMENT

  • 增加和删除列(主键列除外)

  • 列的默认值

  • NOT NULL变更为NULL

  • 增加和删除INDEX索引

  • 分区函数的日期格式

  • 生命周期

  • 存储策略

具体用法请参见ALTER TABLE

其他参数在建表后无法变更。

一个集群最多能够创建多少个表?

一个AnalyticDB for MySQL集群的表数量上限如下:

  • 数仓版预留模式集群(具备1~20个节点组):80000/(分片数/节点组数量)分片数/节点组数量向上取整。

    增加节点组数量,可提高内表数量的上限,增加节点组数量请参见数仓版扩缩容

  • 数仓版弹性模式集群的内表数量上限:[80000/(分片数/EIU数量)]*2分片数/EIU数量向上取整。

    EIU又叫弹性IO资源。增加EIU数量,可提高内表数量的上限,增加EIU数量请参见EIU详解

  • 湖仓版集群的内表数量上限:[80000/(分片数/存储预留资源的组数)]*2。一组存储预留资源为24 ACU。假设集群的存储预留资源为48 ACU,则存储预留资源的组数为2。

    扩容存储预留资源,可提高内表数量的上限,扩容请参见湖仓版扩缩容

  • 数仓版弹性模式集群和湖仓版集群的外表数量上限:50万。

说明

查询分片数(Shard数量):SELECT COUNT(1) FROM information_schema.kepler_meta_shards;。不支持增加或减少分片数。关于数据分片的详细介绍,请参见分片(Shard)

AnalyticDB for MySQL的默认字符集是什么?

AnalyticDB for MySQL默认的字符集为utf-8,相当于MySQL的utf8mb4字符集,暂不支持其他字符集。

常见报错

partition number must larger than 0

原因:建表语句中定义了分区,但未设置分区的生命周期。

报错的建表语句示例如下:

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT '',
  PRIMARY KEY (id, name)
) 
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name);

解决方法:在建表语句中定义分区的生命周期,正确的示例如下。

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT '',
  PRIMARY KEY (id, name)
) 
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name) LIFECYCLE 30;

Only 204800 partition allowed, the number of existing partition=>196462

原因:AnalyticDB for MySQL集群分区数量的上限默认为102400。分区数量超过上限,会出现该报错。

查询集群的分区数量,方法如下。

SELECT count(partition_id)
FROM information_schema.kepler_partitions
WHERE partition_id > 0;

解决方法:您可以调整分区的粒度。例如,按天分区改为按月分区。修改分区粒度的操作,请参见ALTER TABLE

partition column 'XXX' is not found in primary index=> [YYY]

原因:主键需包含分布键和分区键。如果主键未包含分区键,会出现该报错。

SQL错误示例1:

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT '',
  PRIMARY KEY (id)
) 
DISTRIBUTED BY HASH(id) PARTITION BY VALUE(name) LIFECYCLE 30;

如果未指定主键和分布键,也会出现该报错。因为建表未指定主键和分布键时,AnalyticDB for MySQL会自动生成一列__adb_auto_id__,作为主键和分布键。此时主键只有__adb_auto_id__,因为不包含分区键,所以报错。

SQL错误示例2:

CREATE TABLE test (
  id INT COMMENT '',
  name VARCHAR(10) COMMENT ''
) 
PARTITION BY VALUE(name) LIFECYCLE 30;

解决方法:请将分区键添加到主键中。

SemanticException:only 5000 table allowed

原因:AnalyticDB for MySQL集群有表数量上限,超过上限,会出现该报错。不同产品系列不同产品规格的表数量上限不同,具体请参见内表数量的最大值

解决方法:

  • 删除无用的表。

  • 将多张表合并为一张表。

unsigned expr not supported

原因:AnalyticDB for MySQL不支持UNSIGNED属性,即不支持无符号数。

解决方法:建表语句的列属性中不定义UNSIGNED属性。您需要自己在业务代码中实现非负数的约束。

相关文档