You can create a hybrid partitioned table to access data stored in different engines.
The following figure shows how hybrid partitioning works.
After you create a hybrid partitioned table, data in different partitions in the partitioned table is stored on different storage media to separate hot data from cold data. For example, you can store hot data in the local PolarFileSystem and store cold data in OSS. This greatly reduces the storage costs of cold data without affecting the query performance of hot data or affecting DML operations on hot data.
This feature is in the canary release phase. To use this feature, go to Quota Center. Find the Quota Name value corresponding to the Quota ID value of
polardb_mysql_hybrid_partition
. Click Apply in the Actions column.For more information about creating a hybrid partitioned table, join the DingTalk group 24490017825.
Prerequisites
A cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.5 or later is used. You can check the cluster version as specified in Query the engine version.
The data file of the partition to be created already exists in OSS. The naming rules for the data file:
The extension and partition sign must be in uppercase.
The table name and partition name are the same as those in the database.
Example: If the
p1
partition of thet1
table uses the CSV engine, the data file for the partition is namedt1#P#p1.CSV
.
Precautions
A hybrid partitioned table must contain at least one partition that uses the InnoDB engine.
When you create a hybrid partitioned table that contains subpartitions, the partition must use the same engine as that of the table and at least one subpartition must use the InnoDB engine.
Hybrid partitioned tables support only RANGE and
LIST
data types.The following DML statements are not supported on a hybrid partitioned table with partitions that use the OSS engine:
INSERT
,UPDATE
,DELETE
, andLOAD
.The following statements are supported on a hybrid partitioned table in a cluster of PolarDB for MySQL 8.0.2.2.17 or later: ADD and DROP. These statements are not supported on a hybrid partitioned table in a cluster of other PolarDB for MySQL versions.
Parameters
You can set the following parameters on the Parameters page of the PolarDB cluster based on your business requirements.
Parameter | Description |
hybrid_partition_query_mix_engine_enabled | Specifies whether to query data of the partitions that do not use the InnoDB engine in a hybrid partitioned table. Valid values:
|
Create a hybrid partitioned table
You can create a hybrid partitioned table by using an existing OSS server. If no OSS server exists, you can execute the following statement to create one:
CREATE SERVER oss_server_name
FOREIGN DATA WRAPPER oss
OPTIONS(EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>",
"oss_bucket": "<my_oss_bucket>", "oss_access_key_id": "<my_oss_access_key_id>",
"oss_access_key_secret": "<my_oss_access_key_secret>", "oss_prefix":"<my_oss_prefix>", "oss_sts_token": "<my_oss_sts_token>"}');
The my_oss_sts_token
parameter is supported on a cluster of PolarDB for MySQL 8.0.2 whose revision version is 8.0.2.2.6 or later.
The following table describes the parameters in the statement.
Parameter | Type | Description |
oss_server_name | String | The name of the OSS server. Note The name must be globally unique. It is not case-sensitive. The name can be up to 64 characters in length. A name that contains more than 64 characters is automatically truncated. You can specify the OSS server name as a quoted string. |
my_oss_endpoint | String | The endpoint of the OSS server. Note If you access your database from an Alibaba Cloud server, use an internal endpoint to avoid incurring Internet traffic. An internal endpoint contains keyword internal. |
my_oss_bucket | String | The bucket where the data file is stored. You must create OSS buckets before you import data. |
my_oss_access_key_id | String | The AccessKey ID of the account used to access OSS. |
my_oss_access_key_secret | String | The AccessKey secret of the account used to access OSS. |
my_oss_prefix | String | The prefix of the OSS path. |
my_oss_sts_token | String | The temporary credential used to access OSS. For information about how to obtain temporary credentials used to access OSS, see Use temporary credentials provided by STS to access OSS. Note The
|
Create a hybrid partition table. Example:
CREATE TABLE t2(a1 INT, a2 VARCHAR(30), a3 VARCHAR(256))
CONNECTION = "oss_server_name"
PARTITION BY RANGE(a1)
(
PARTITION p1 values less than (1000) ENGINE = CSV,
PARTITION p2 values less than (2000) ENGINE = CSV,
PARTITION p3 values less than (3000) ENGINE = INNODB
);
Query data in a hybrid partition table
You can query data of partitions that use different engines by using one of the following methods:
If you set the
hybrid_partition_query_mix_engine_enabled
parameter to ON in the console, query results contain data in the partitions that do not use the InnoDB engine. Example:SELECT * FROM t1;
t1
is the hybrid partitioned table.If you set the
hybrid_partition_query_mix_engine_enabled
parameter to OFF in the console, you can query data in a specified partition. Example:SELECT * FROM t1 partition (p1);
t1
is the hybrid partitioned table.p1
is the partition.