This topic describes how to create partitioned tables and replicated tables by using the CREATE TABLE statement in AnalyticDB for MySQL and how to specify distribution keys, partition keys, indexes, partition lifecycle, and tiered storage policies for hot and cold data of tables.
The following figure shows the concepts that you need to familiarize yourself with before you create a table, such as shards, partitions, and clustered indexes.
Syntax
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'Parameters
table_name, column_name, column_type, and COMMENT
table_constraints (Indexes)
AnalyticDB for MySQL supports a variety of indexes, including INDEX, PRIMARY KEY, CLUSTERED KEY, FOREIGN KEY, FULLTEXT INDEX, and ANN INDEX. A table can have one or more types of indexes.
INDEX | KEY
Specifies a regular index. INDEX and KEY can be used interchangeably.
By default, AnalyticDB for MySQL automatically creates indexes on all columns of the table. However, if you create an index on one or more columns when you create a table, such as an index on the id column by using INDEX (id), AnalyticDB for MySQL does not automatically generate indexes on other columns of the table.
PRIMARY KEY
Specifies the primary key index.
Overview
Each table can have only one primary key.
The primary key can consist of a single column, such as
PRIMARY KEY (id), or multiple columns, such asPRIMARY KEY (id,name).A composite primary key must include a distribution key and a partition key. We recommend that you place distribution and partition keys in the front section of a composite primary key.
Usage notes
You cannot perform the DELETE or UPDATE operation on tables that do not have primary keys.
If no primary key is specified, the following rules apply:
If no distribution key is specified, AnalyticDB for MySQL automatically adds the
__adb_auto_id__column to the table and uses the column as the primary key and the distribution key.If a distribution key is specified, AnalyticDB for MySQL does not automatically add a primary key.
After a table is created, you cannot add, remove, or change primary key columns.
To ensure high performance, we recommend that you select one or a small number of numeric columns as the primary key.
If a table has excessive primary key columns, the following issues may occur:
Higher consumption of CPU and I/O resources. This is because AnalyticDB for MySQL checks whether duplicate primary key values exist when data is written.
Higher disk usage of primary key indexes. To view the disk usage of primary key indexes, use the Storage Analysis feature.
Slower partition rebuilding speed of BUILD jobs.
CLUSTERED KEY
Specifies a clustered index. A clustered index is configured at the partition level. It determines the physical order in which data is stored. Data in a partition is sorted and stored sequentially based on the values of the clustered index. Data records with the same or similar key values of the clustered index are stored in the same or adjacent data blocks. In range queries or equivalence filtering, using a clustered index can reduce disk I/O and speed up data reads. This is because when query conditions are the same as clustered index columns, the storage engine can read contiguous data blocks.
Applicable scenarios
A clustered index works well with range queries and equivalence filtering. A column that is frequently used in the conditions of range queries or equivalence filtering is an ideal clustered index column.
When query conditions match or partially match a clustered index column, read efficiency significantly improves. For example, you can use the user ID as the clustered index in software-as-a-service (SaaS) applications. This ensures that the records of a specific user ID are stored in the same or contiguous data blocks and improves data query performance.
Usage notes
Each table can have only one clustered index.
A clustered index can be created on a single column, such as
CLUSTERED KEY index(id), or on multiple columns, such asCLUSTERED KEY index(id,name). When the clustered index involves two columns, data is first sorted based on the values of the first clustered index column. If the values of the first clustered index column are the same, data is sorted based on the values of the second clustered index column. Therefore,CLUSTERED KEY index(id,name)andCLUSTERED KEY index(name,id)are different clustered indexes.To prevent reduced sorting performance, we recommend that you do not use a column that has lengthy values (such as strings larger than 10 KB) in a clustered index.
FULLTEXT INDEX | FULLTEXT KEY
Specifies a full-text index. FULLTEXT INDEX and FULLTEXT KEY can be used interchangeably. For more information about full-text indexes, see Create a full-text index.
FOREIGN KEY
Specifies a foreign key index. Foreign key indexes are used to eliminate unnecessary joins. For more information, see Use primary and foreign key constraints to eliminate unnecessary joins.
ANN INDEX
Specifies a vector index. For information about vector search, see Vector search.
JSON INDEX
Specifies a JSON index or a JSON array index. For more information, see JSON indexes.
table_attribute (Distribution key)
Determines whether a table is a standard or replicated table.
DISTRIBUTED BY HASH: specifies the table as a standard table. A standard table can make full use of the query capability of a distributed system to improve query efficiency. Each standard table can store up to hundreds of billions of data records.
DISTRIBUTED BY BROADCAST: specifies the table as a replicated table. A replicated table stores a data replica on each shard of the AnalyticDB for MySQL cluster to which the table belongs. We recommend that you store up to 20,000 rows in each replicated table.
DISTRIBUTED BY HASH (column_name,...)
Specifies the distribution key of a table. A table that has a distribution key is a standard table. AnalyticDB for MySQL calculates the hash values of the distribution key values and divides the table into shards based on the hash values. Sharding improves scalability and query performance.
Overview
Each table can have only one distribution key.
Each distribution key can contain one or more columns.
Distribution key columns must be included in the primary key columns. For example, if the distribution key is the customer_id column, the primary key must include the customer_id column.
Usage notes
If you do not specify a distribution key when you create a table, the following rules apply:
If the table has a primary key, AnalyticDB for MySQL uses the primary key as the distribution key.
If the table does not have a primary key, AnalyticDB for MySQL automatically adds the
__adb_auto_id__column to the table and uses the column as the primary key and the distribution key.
After a table is created, you cannot add, remove, or change distribution key columns. To change a distribution key, you must create a table that has the desired distribution key and migrate your data to the new table. For more information, see ALTER TABLE.
Recommendations
Select fewer columns to make the distribution key more suitable for a variety of complex queries.
Select columns whose values are evenly distributed as the distribution key, such as the transaction ID, device ID, user ID, or auto-increment columns. However, these columns are not ideal distribution keys when query conditions are limited to a small number of column values. For example, if Column A has evenly distributed values but the query condition is always A=3, setting Column A as the distribution key causes data hotspot.
Select columns that can be used to join tables as the distribution key. This way, data with the same distribution key values in two joined tables is distributed to the same shard. Join operations are performed on the same shard without the need of data transmission among shards. This minimizes data redistribution and improves query performance. For example, if your use scenario involves querying the order history of customers, you can specify the
customer_idcolumn as the distribution key.Do not select columns of the date, time, and timestamp types as the distribution key. The preceding columns are prone to data skew during data writes and may degrade write performance. Most queries are limited to a period of time such as a day or a month. In this case, the data that you want to query may exist on only one node, and queries cannot leverage the processing capabilities of all nodes in a distributed database system. We recommend that you select columns of the date and time types as the partition key. For more information, see the "partition_options (Partition key and lifecycle)" section of this topic.
You can use the data modeling diagnostics feature to check whether a column is a suitable distribution key and whether data skew occurs. For more information, see Storage diagnostics.
DISTRIBUTED BY BROADCAST
Specifies a replicated table. A replicated table stores a data replica on each shard of the AnalyticDB for MySQL cluster to which the table belongs. We recommend that you do not store a large amount of data in each replicated table.
Advantages: When you perform a JOIN query, you do not need to transmit the data of a replicated table across different nodes. This significantly reduces network transmission overheads and improves cluster stability in high-concurrency scenarios.
Disadvantages: When data is changed after you perform the INSERT, UPDATE, and DELETE operations in a replicated table, these changes are broadcast to all nodes of the cluster to ensure data consistency. However, this affects the overall write performance. Therefore, we recommend that you do not frequently create, delete, or modify replicated tables.
partition_options (Partition key and lifecycle)
If a single shard contains a large amount of data after you specify a distribution key for a table, you can divide the shard into partitions to speed up data filtering and improve query performance.
PARTITION BY
Specifies a partition key.
Syntax: PARTITION BY VALUE {(column_name) | (DATE_FORMAT(column_name, 'format'))} LIFECYCLE n
Parameters:
column_name: the name of the partition key. In the
PARTITION BY VALUE(column_name)syntax, partitioning is based on the values of thecolumn_namecolumn. The partition key can be one of the following types: numeric, datetime, or a string that specifies a number.DATE_FORMAT(column_name, 'format'): converts a datetime column to a specified date format and partitions the data. The specified date format can only contain year, month, or day. The following date formats are supported: %Y, %y, %Y%m, %y%m, %Y%m%d, and %y%m%d. You can change the format after a table is created. For more information, see ALTER TABLE.
Usage notes
For AnalyticDB for MySQL clusters earlier than V3.2.1.0, you must use the
LIFECYCLE nparameter to specify the lifecycle of partitions when you use thePARTITION BYclause to specify a partition key. Otherwise, an error is returned.For AnalyticDB for MySQL clusters of V3.2.1.0 or later, the
LIFECYCLE nparameter is optional when you use thePARTITION BYclause to specify a partition key. If you do not specify the LIFECYCLE n parameter, partition data is not deleted.After a table is created, you cannot add partition keys, or add, remove, or change partition key columns. To add or modify a partition key, create a table that has the desired partition key and migrate data to the new table. For more information, see ALTER TABLE.
Recommendations
We recommend that you use a datetime column as the partition key.
A partition that is excessively large or small can affect read and write performance, and may even affect cluster stability. For information about the recommended partition size and the criteria for partition field reasonability, see the "Partitioned table diagnostics" section of the Storage diagnostics topic.
We recommend that you do not frequently update data in historical partitions. If you frequently update data in historical partitions, you may need to change the partition key.
LIFECYCLE n
The LIFECYCLE n parameter must be used together with the PARTITION BY clause. You can use this parameter to manage the lifecycle of partitions. AnalyticDB for MySQL sorts partitions in descending order based on partition key values. The first n partitions are retained and the other partitions are deleted.
For AnalyticDB for MySQL clusters earlier than V3.2.1.1, the
LIFECYCLE nparameter specifies that up to n partitions can be retained on each shard. The partition lifecycle is managed at the shard level. However, if data is unevenly distributed or the amount of data is excessively small, more than n partitions may be retained on specific shards.For AnalyticDB for MySQL clusters of V3.2.1.1 or later, the partition lifecycle is managed at the table level. The
LIFECYCLE nparameter specifies that up to n partitions can be retained in each table. However, for tables that are created before AnalyticDB for MySQL clusters are updated to V3.2.1.1 or later, the partition lifecycle is managed at the shard level. TheLIFECYCLE nparameter specifies that up to n partitions can be retained on each shard.
Example
PARTITION BY VALUE (DATE_FORMAT(date, '%Y%m%d')) LIFECYCLE 30 specifies that during partitioning, data in the date column is converted into the yyyyMMdd format and up to 30 partitions are retained. Assume that data from the 1st to the 30th day is written to corresponding partitions from Partition 20231201 to Partition 20231230. On the 31st day when data is written to Partition 20231231, the partition with the smallest partition key value (in this case, Partition 20231201) is automatically deleted because only up to 30 partitions can be retained.
STORAGE_POLICY (Storage policy)
Data Lakehouse Edition and Data Warehouse Edition in elastic mode for Cluster Edition allow you to specify a data storage policy. Storage policies vary in read/write performance and storage costs.
Valid values:
hot (default): hot storage. Data in all partitions of the entire table is stored on SSDs. Hot storage has the best read/write performance, but incurs the highest storage cost.
cold: cold storage. Data in all partitions of the entire table is stored on Object Storage Service (OSS). Compared with hot storage, cold storage has lower read/write performance, but is the least costly option.
mixed: a combination of hot storage and cold storage, also called tiered storage. This policy reduces storage costs and ensures query performance by storing frequently accessed data (hot data) on SSDs and infrequently accessed data (cold data) on OSS. If you set the STORAGE_POLICY parameter to mixed, you must use the
PARTITION BYclause to specify a partition key and thehot_partition_countparameter to specify the number of hot partitions. If you do not specify a partition key, tiered storage does not take effect and data is stored on SSDs.
Examples
Create a partitioned table and configure the partition lifecycle
Create a standard table named customer. Specify login_time, customer_id, and phone_num as the composite primary key, customer_id as the distribution key, and login_time as the partition key. Set the partition lifecycle to 30.
All partitions are sorted in descending order based on the values of the login_time partition key. Only the first 30 partitions are retained. When data is written to the 31st partition, the partition with the smallest partition key value is automatically deleted.
Assume that data from the 1st (with login_time value 20231201) to the 30th (with login_time value 20231230) day is written to corresponding partitions from Partition 20231201 to Partition 20231230. When data with login_time value 20231231 is written to the database on the 31st day, the partition with the smallest login_time value (Partition 20231201) is automatically deleted. This way, only data within the last 30 days is retained.
CREATE TABLE customer (
customer_id BIGINT NOT NULL COMMENT 'Customer ID',
customer_name VARCHAR NOT NULL COMMENT 'Customer name',
phone_num BIGINT NOT NULL COMMENT 'Phone number',
city_name VARCHAR NOT NULL COMMENT 'City',
sex INT NOT NULL COMMENT 'Gender',
id_number VARCHAR NOT NULL COMMENT 'ID card number',
home_address VARCHAR NOT NULL COMMENT 'Home address',
office_address VARCHAR NOT NULL COMMENT 'Office address',
age INT NOT NULL COMMENT 'Age',
login_time TIMESTAMP NOT NULL COMMENT 'Logon time',
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 information table'; Create a table without a distribution key
Create a table with a primary key
If you create a table that has a primary key but not a distribution key, AnalyticDB for MySQL automatically uses the primary key as the distribution key.
CREATE TABLE orders (
order_id BIGINT NOT NULL COMMENT 'Order ID',
customer_id INT NOT NULL COMMENT 'Customer ID',
order_status VARCHAR(1) NOT NULL COMMENT 'Order status',
total_price DECIMAL (15, 2) NOT NULL COMMENT 'Price',
order_date DATE NOT NULL COMMENT 'Order date',
PRIMARY KEY(order_id,order_date)
);Query the statement that is used to create the table and verify that the primary key columns order_id and order_date are used as the distribution key.
SHOW CREATE TABLE orders;+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| orders | CREATE TABLE `orders` ( |
| | 'order_id' bigint NOT NULL COMMENT 'Order ID', |
| | 'customer_id' int NOT NULL COMMENT 'Customer ID', |
| | 'order_status' varchar(1) NOT NULL COMMENT 'Order status', |
| | 'total_price' decimal(15, 2) NOT NULL COMMENT 'Price', |
| | 'order_date' date NOT NULL COMMENT 'Order date', |
| | 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)Create a table without a primary key
If you create a table that does not have a primary or distribution key. AnalyticDB for MySQL adds the __adb_auto_id__ column to the table and uses the column as the primary key and the distribution key.
CREATE TABLE orders_new (
order_id BIGINT NOT NULL COMMENT 'Order ID',
customer_id INT NOT NULL COMMENT 'Customer ID',
order_status VARCHAR(1) NOT NULL COMMENT 'Order status',
total_price DECIMAL (15, 2) NOT NULL COMMENT 'Price',
order_date DATE NOT NULL COMMENT 'Order date'
);Query the statement that is used to create the table and verify that the auto-increment column named __adb_auto_id__ is automatically added to the table and used as the primary key and the distribution key.
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 'Order ID', |
| | 'customer_id' int NOT NULL COMMENT 'Customer ID', |
| | 'order_status' varchar(1) NOT NULL COMMENT 'Order status', |
| | 'total_price' decimal(15, 2) NOT NULL COMMENT 'Price', |
| | 'order_date' date NOT NULL COMMENT 'Order date', |
| | 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)Create a table without a partition key
Create a table named supplier that uses the supplier_id auto-increment column as the distribution key and is sharded based on the hash values of supplier_id values.
CREATE TABLE supplier (
supplier_id BIGINT AUTO_INCREMENT PRIMARY KEY,
supplier_name VARCHAR,
address INT,
phone VARCHAR
)
DISTRIBUTED BY HASH(supplier_id);Specify storage policies
Specify a cold storage policy
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';Specify a hot storage policy
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';Specify a tiered storage policy and set the number of hot partitions to 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;Specify a full-text index
Create a full-text index named fidx_c on the content column.
CREATE TABLE fulltext_tb (
id INT,
content VARCHAR,
keyword VARCHAR,
FULLTEXT INDEX fidx_c(content),
PRIMARY KEY (id)
)
DISTRIBUTE BY HASH(id);For information about how to create and change a full-text index, see Create a full-text index.
For information about full-text search, see Full-text query.
Specify a vector index
Create a table that has a four-dimensional vector column of the ARRAY<SMALLINT> type named short_feature and a four-dimensional vector column of the ARRAY<FLOAT> type named float_feature.
Create vector indexes named short_feature_index and float_feature_index based on the vector columns for the table.
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;For information about vector indexes and vector search, see Vector search.
Specify a foreign key index
Create a table named store_returns. Use the FOREIGN KEY clause to associate the sr_item_sk column of the store_returns table with the primary key column customer_id of the customer table.
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)
);Specify a JSON array index
Create a table and create a JSON array index named idx_vj on the vj column.
CREATE TABLE json(
id INT,
vj JSON,
INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);For information about how to create and change JSON array indexes, see the "Create a JSON array index" section of the JSON indexes topic and the "JSON array indexes" section of the ALTER TABLE topic.
FAQ
Column attributes and constraints
Distribution key, partition key, and lifecycle
A distribution key is used in sharding. Data in a table is distributed across different shards based on the hash values of the distribution key values. A partition key is used in partitioning. Within a shard, data is further distributed across different partitions based on the values of the partition key. The following figure shows how sharding and partitioning work.
Indexes
Column-oriented storage
Others
Common errors and solutions
References
For information about how to write data to tables, see INSERT INTO.
For information about how to copy records from one table to another, and clear existing data in a partition and then batch write data to the partition, see INSERT SELECT FROM and INSERT OVERWRITE SELECT.
For information about how to import data from data sources, such as ApsaraDB RDS, MaxCompute, and OSS, into AnalyticDB for MySQL, see Data import.