文档

CREATE TABLE

更新时间:

使用CREATE TABLE创建AnalyticDB for MySQL的维度表与事实表。

表的数据分布策略

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,...)]
  [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:
  DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED 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'

参数

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为自增列提供唯一值,但自增列的值不是顺序递增,且不支持设置AUTO_INCREMENT从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不会再为表中其他列自动创建索引。

FULLTEXT INDEX | FULLTEXT KEY

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

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

参数说明:

  • index_name:全文索引名称。

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

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

PRIMARY KEY

定义主键索引。AnalyticDB for MySQL的主键索引可以为单一主键索引(例如PRIMARY KEY (id)),也可以为复合主键索引(例如PRIMARY KEY (id,name))。

使用规则:

  • 每个表只能有一个主键。主键可以是单个列或多个列的组合。推荐使用数值类型的列作为主键,并尽量减少主键包含的列的个数,以获得较好的性能。

    说明

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

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

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

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

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

  • 只有定义了主键的表支持DELETE和UPDATE操作。

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

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

    • 如果建表时,未定义主键,但定义了分布键,AnalyticDB for MySQL不会自动添加主键。此时,该表为无主键表,建表后也无法通过ALTER TABLE设置主键。

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的字符串,则不建议聚集索引采用该字段,避免影响排序性能。

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+…

table_attribute

table_attribute决定了表的类型是普通表还是维度表。

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

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

DISTRIBUTED BY HASH (column_name,...)

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

数据分片示意图

image

分片数说明

执行SQL,查询分片数(Shard数量):

SELECT COUNT(1) FROM information_schema.kepler_meta_shards;

怎样选择分布键

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

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

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

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

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

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

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

  • 创建表后,不能变更分布键。如果需要修改分布键,请重新建表并迁移数据,具体操作请参见更改分区键/分布键

诊断分布键是否合理

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

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。PARTITION BY VALUE (DATE_FORMAT(column_name, '%Y%m%d'))表示将column_name格式化为类似yyyyMMdd的日期格式再进行分区。

注意事项:

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

  • 创建表后,分区的粒度可以变更,即可以将PARTITION BY VALUE (column_name)变更为PARTITION BY VALUE (DATE_FORMAT(column_name, 'format'))PARTITION BY VALUE (DATE_FORMAT(column_name, 'format'))变更为PARTITION BY VALUE (column_name)以及修改DATE_FORMAT函数中的日期格式'format'。具体的修改方法,请参见更改分区函数

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

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

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

  • 分区不是实时构建和清理的,而是后台异步执行的。AnalyticDB for MySQL会异步发起BUILD操作,执行构建分区、清理分区、构建索引等任务。BUILD完成后,分区的构建和清理才完成。您也可以手动发起BUILD操作,详情请参见BUILD

LIFECYCLE n

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

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

取值说明

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

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

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

    冷热混合存储示意图

    image

hot_partition_count=n

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。

说明
  • 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)
);

常见问题

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

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

是否支持unsigned属性?

AnalyticDB for MySQL不支持unsigned属性。

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

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

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

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

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

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

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

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

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

说明

查询分片数(Shard数量):SELECT COUNT(1) FROM information_schema.kepler_meta_shards;。不支持增加或减少分片数。

如何查询表的分区信息?

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

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

LIFECYCLE定义了一个分片上保留多少个分区。当数据分布不均时,可能出现该现象。

例如,由于数据分布不均,按日期分区时,分片1有分区20231201、20231202、依次类推到20231230;分片2有分区20231202、20231203、依次类推到20231231。分片1和分片2上的分区数均为30,满足生命周期的设定。但查询数据时,可以查到日期为20231201~20231231的数据。

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

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

是否支持唯一索引?

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

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

相关文档

  • 本页导读 (1)
文档反馈