Topik ini menjelaskan cara membuat tabel partisi dan tabel replikasi menggunakan pernyataan CREATE TABLE di AnalyticDB for MySQL, serta cara menentukan kunci distribusi, kunci partisi, indeks, siklus hidup partisi, dan kebijakan penyimpanan bertingkat untuk data panas dan dingin pada tabel.
Sintaks
CREATE TABLE [IF NOT EXISTS] table_name
({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'column_comment']
| table_constraints}
[, ... ])
[table_attribute]
[partition_options]
[index_all]
[storage_policy]
[block_size]
[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|column_name->'$.json_path'|column_name->'$[*]')][,...]
[FULLTEXT [INDEX|KEY] [index_name] (column_name) [index_option]] [,...]
[PRIMARY KEY [index_name] (column_name,...)]
[CLUSTERED KEY [index_name] (column_name[ASC|DESC],...) ]
[[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')) | VALUE(FROM_UNIXTIME(column_name, 'format'))}
LIFECYCLE N
index_all:
INDEX_ALL= 'Y|N'
storage_policy:
STORAGE_POLICY= {'HOT'|'COLD'|'MIXED' {hot_partition_count=N}}
block_size:
BLOCK_SIZE= VALUE
engine:
ENGINE= 'XUANWU|XUANWU_V2'Secara default, tabel internal AnalyticDB for MySQL menggunakan algoritma kompresi zstd.
Parameter
table_name, column_name, column_type, dan COMMENT
partition_options (Kunci partisi dan siklus hidup)
INDEX_ALL (Indeks pada semua kolom)
Contoh
Buat tabel partisi yang dipartisi otomatis berdasarkan tanggal
Buat tabel partisi bernama sales yang dipartisi otomatis berdasarkan nilai tanggal dari volume sale_time.
CREATE TABLE sales (
sale_id BIGINT NOT NULL COMMENT 'Order ID',
customer_id VARCHAR NOT NULL COMMENT 'Customer ID',
phone_num BIGINT NOT NULL COMMENT 'Phone number',
revenue DECIMAL(15, 2) COMMENT 'Total amount',
sale_time TIMESTAMP NOT NULL COMMENT 'Order time',
PRIMARY KEY (sale_time,sale_id)
)
DISTRIBUTED BY HASH(sale_id)
PARTITION BY VALUE(DATE_FORMAT(sale_time, '%Y%m%d')); Buat tabel partisi yang dikonfigurasi dengan siklus hidup partisi
Buat tabel partisi bernama customer. Tentukan login_time, customer_id, dan phone_num sebagai kunci primer gabungan, customer_id sebagai kunci distribusi, dan login_time sebagai kunci partisi. Atur siklus hidup partisi ke 30.
Semua partisi diurutkan dalam urutan menurun berdasarkan nilai kunci partisi login_time. Hanya 30 partisi pertama yang dipertahankan. Saat data ditulis ke partisi ke-31, partisi dengan nilai kunci partisi terkecil secara otomatis dihapus.
Misalkan data dari tanggal 1 (dengan nilai login_time 20231201) hingga tanggal 30 (dengan nilai login_time 20231230) ditulis ke partisi yang sesuai dari Partisi 20231201 hingga Partisi 20231230. Saat data dengan nilai login_time 20231231 ditulis ke database pada tanggal 31, partisi dengan nilai login_time terkecil (Partisi 20231201) secara otomatis dihapus. Dengan cara ini, hanya data dalam 30 hari terakhir yang dipertahankan.
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'; Buat tabel non-partisi
Buat tabel non-partisi tanpa kunci distribusi atau partisi
Jika Anda membuat tabel yang memiliki kunci primer tetapi tidak memiliki kunci distribusi, AnalyticDB for MySQL secara otomatis menggunakan kunci primer sebagai kunci distribusi.
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 'Total amount',
order_date DATE NOT NULL COMMENT 'Order date',
PRIMARY KEY(order_id,order_date)
);Kueri pernyataan yang digunakan untuk membuat tabel dan verifikasi bahwa kolom kunci primer order_id dan order_date digunakan sebagai kunci distribusi.
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 'Total amount', |
| | 'order_date' date NOT NULL COMMENT 'Order date', |
| | PRIMARY KEY (`order_id`,`order_date`) |
| | ) DISTRIBUTED 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)Buat tabel non-partisi tanpa kunci primer atau distribusi
Jika Anda membuat tabel yang tidak memiliki kunci primer atau kunci distribusi, AnalyticDB for MySQL menambahkan kolom __adb_auto_id__ ke tabel dan menggunakan kolom tersebut sebagai kunci primer dan kunci distribusi.
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 'Total amount',
order_date DATE NOT NULL COMMENT 'Order date'
);Kueri pernyataan yang digunakan untuk membuat tabel dan verifikasi bahwa kolom auto-increment bernama __adb_auto_id__ secara otomatis ditambahkan ke tabel dan digunakan sebagai kunci primer dan kunci distribusi.
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 'Total amount', |
| | 'order_date' date NOT NULL COMMENT 'Order date', |
| | PRIMARY KEY (`__adb_auto_id__`) |
| | ) DISTRIBUTED BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' STORAGE_POLICY='HOT' ENGINE='XUANWU' TABLE_PROPERTIES='{"format":"columnstore"}' |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)Buat tabel non-partisi dengan kunci primer dan distribusi tetapi tanpa kunci partisi
Buat tabel bernama supplier yang menggunakan kolom auto-increment supplier_id sebagai kunci distribusi dan di-shard berdasarkan nilai hash supplier_id.
CREATE TABLE supplier (
supplier_id BIGINT AUTO_INCREMENT PRIMARY KEY,
supplier_name VARCHAR,
address INT,
phone VARCHAR
)
DISTRIBUTED BY HASH(supplier_id);Tentukan kebijakan penyimpanan
Tentukan kebijakan penyimpanan dingin
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)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='COLD';Tentukan kebijakan penyimpanan panas
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)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='HOT';Tentukan kebijakan penyimpanan bertingkat dan atur jumlah partisi panas ke 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)
)
DISTRIBUTED BY HASH(item_id)
PARTITION BY VALUE(date_format(shipdate, '%Y%m')) LIFECYCLE 200
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;Buat indeks biasa pada kolom tertentu
Buat indeks biasa pada kolom id dan date.
CREATE TABLE index_tb (
id INT,
sales DECIMAL(15, 2),
date DATE,
INDEX (id),
INDEX (date),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);Tentukan indeks terkluster
Buat indeks terkluster bernama clustered_index pada kolom quantity.
CREATE TABLE clustered (
product_id INT,
product_name VARCHAR,
quantity INT,
price DECIMAL(10, 2),
CLUSTERED KEY INDEX clustered_index(quantity)
)
DISTRIBUTED BY HASH(product_id);Tentukan indeks teks penuh
Buat indeks teks penuh bernama fidx_c pada kolom content.
CREATE TABLE fulltext_tb (
id INT,
content VARCHAR,
keyword VARCHAR,
FULLTEXT INDEX fidx_c(content),
PRIMARY KEY (id)
)
DISTRIBUTED BY HASH(id);Untuk informasi tentang cara membuat dan mengubah indeks teks penuh, lihat Buat indeks teks penuh.
Untuk informasi tentang pencarian teks penuh, lihat Pencarian teks penuh.
Tentukan indeks vektor
Buat tabel yang memiliki kolom vektor empat dimensi bertipe ARRAY<SMALLINT> bernama short_feature dan kolom vektor empat dimensi bertipe ARRAY<FLOAT> bernama float_feature.
Buat indeks vektor bernama short_feature_index dan float_feature_index pada kolom vektor untuk tabel tersebut.
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)
)
DISTRIBUTED BY HASH(xid) PARTITION BY VALUE(cid) LIFECYCLE 4;Untuk informasi lebih lanjut tentang pengindeksan vektor dan pencarian vektor, lihat Pencarian vektor.
Tentukan indeks kunci asing
Buat tabel bernama store_returns. Gunakan klausa FOREIGN KEY untuk mengaitkan kolom sr_item_sk tabel store_returns dengan kolom kunci primer customer_id tabel customer.
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)
);Tentukan indeks array JSON
Buat tabel dan buat indeks array JSON bernama idx_vj pada kolom vj.
CREATE TABLE json(
id INT,
vj JSON,
INDEX idx_vj(vj->'$[*]')
)
DISTRIBUTED BY HASH(id);Untuk informasi tentang cara membuat dan mengubah indeks array JSON, lihat bagian "Buat indeks array JSON" dalam topik Indeks JSON dan bagian "Indeks array JSON" dalam topik ALTER TABLE.
FAQ
Atribut dan kendala kolom
Kunci distribusi, kunci partisi, dan siklus hidup
Indeks
Penyimpanan berorientasi kolom
Lainnya
Error umum dan troubleshooting
Referensi
Untuk informasi tentang cara menulis data ke tabel, lihat INSERT INTO.
Untuk informasi tentang cara memasukkan hasil kueri ke tabel atau menimpa data tertentu dalam tabel dengan hasil kueri, lihat INSERT SELECT FROM atau INSERT OVERWRITE SELECT.
Untuk informasi tentang cara mengimpor data dari sumber data, seperti ApsaraDB RDS, MaxCompute, dan OSS, ke AnalyticDB for MySQL, lihat Impor data.