INT / BIGINT), such as a user ID or a purely numeric order number.BLOB / TEXT. The system uses an internal hash function to process data.VARCHAR / CHAR), such as a UUID, ID card number, or business serial number.DROP PARTITION.CREATE TABLE `mall`.`orders` (`order_id` bigint NOT NULL,`user_id` bigint NOT NULL,`amount` decimal(10,2),`create_time` datetime DEFAULT CURRENT_TIMESTAMP,-- The primary key must include the partition keyPRIMARY KEY (`order_id`))PARTITION BY HASH(`order_id`)PARTITIONS 64;
CREATE TABLE `user_center`.`user_profiles` (`user_uuid` varchar(36) NOT NULL,`nick_name` varchar(50),`register_time` datetime,PRIMARY KEY (`user_uuid`))PARTITION BY KEY(`user_uuid`)PARTITIONS 64;
RANGE, RANGE COLUMNS, LIST, LIST COLUMNS, HASH, and KEY.CREATE TABLE `sys`.`audit_logs` (`log_id` bigint NOT NULL,`content` text,`log_time` datetime NOT NULL,PRIMARY KEY (`log_id`, `log_time`))PARTITION BY RANGE COLUMNS(`log_time`) (PARTITION p202601 VALUES LESS THAN ('2026-02-01'),PARTITION p202602 VALUES LESS THAN ('2026-03-01'),PARTITION p202603 VALUES LESS THAN ('2026-04-01'));
PARTITION p_future VALUES LESS THAN (MAXVALUE) as a catch-all partition.MAXVALUE is a static catch-all partition and does not automatically split on a monthly basis over time. Once data is written that exceeds the upper bound of the last partition, all new data will continuously flow into p_future, resulting in:p_future expands indefinitely and loses the ability to be cleaned up on a monthly basis;ADD PARTITION p202304, the operation will fail directly because MAXVALUE already occupies the end position. You must first REORGANIZE PARTITION p_future, which is a complex and costly operation.ADD PARTITION in advance at the end of each month to pre-create the partition for the upcoming month.Scenarios | SQL Sample | Description |
Deleting expired partitions | ALTER TABLE sys.audit_logs DROP PARTITION p202601; | Cleans up expired data in seconds, with performance far superior to DELETE. |
Clearing partition data | ALTER TABLE sys.audit_logs TRUNCATE PARTITION p202601; | Retains the partition structure and only clears the data. |
Pre-creating partitions for the next month | ALTER TABLE sys.audit_logs ADD PARTITION (PARTITION p202604 VALUES LESS THAN ('2026-05-01')); | Can only be added at the end, and the values must be strictly increasing. |
Splitting the MAXVALUE partition | ALTER TABLE sys.audit_logs REORGANIZE PARTITION p_future INTO (PARTITION p202603 VALUES LESS THAN ('2026-04-01'), PARTITION p_future VALUES LESS THAN (MAXVALUE)); | A remedial measure after MAXVALUE is misused |
Viewing the partition list | SELECT PARTITION_NAME, PARTITION_DESCRIPTION, TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA='sys' AND TABLE_NAME='audit_logs'; | Querying the current partition status and data volume |
Phase | Recommended Solution | Description |
Pre-creating future partitions | Executing ALTER TABLE ... ADD PARTITION via an external scheduling task. | The system does not automatically expand partitions based on time; it must be triggered periodically by an external source. |
CREATE TABLE `crm`.`customers` (`cust_id` bigint NOT NULL,`city_id` int NOT NULL,`name` varchar(50),PRIMARY KEY (`cust_id`, `city_id`))PARTITION BY LIST(`city_id`) (PARTITION p_bj VALUES IN (10),PARTITION p_sh VALUES IN (20),PARTITION p_others VALUES IN (30));
CREATE TABLE `sys`.`massive_logs` (log_id bigint NOT NULL,user_id bigint NOT NULL,content text,log_time datetime NOT NULL,PRIMARY KEY (log_id, log_time, user_id))PARTITION BY RANGE COLUMNS(log_time)SUBPARTITION BY HASH(user_id)SUBPARTITIONS 4(PARTITION p202601 VALUES LESS THAN ('2026-02-01'),PARTITION p202602 VALUES LESS THAN ('2026-03-01'),PARTITION p202603 VALUES LESS THAN ('2026-04-01'));
-- Error: The partition key user_id is not included in the primary key.CREATE TABLE `error_table` (`id` bigint NOT NULL PRIMARY KEY,`user_id` bigint NOT NULL) PARTITION BY HASH(`user_id`);
-- Correct: Add user_id to the composite primary key.CREATE TABLE `correct_table` (`id` bigint NOT NULL,`user_id` bigint NOT NULL,PRIMARY KEY (`id`, `user_id`)) PARTITION BY HASH(`user_id`);
UPDATE statements, as this can cause data rows to migrate between physical nodes (via DELETE + INSERT operations), which is a high-cost operation.WHERE clause (for example, WHERE user_id = ?). This allows TDSQL Boundless to route directly to a specific node, resulting in extremely high performance.PARTITION BY HASH(col1 + col2) or KEY(col1, col2). However, this increases complexity. It is generally recommended to use a single high-cardinality column as the partition key.MAXVALUE as the Fallback?MAXVALUE partition is a static catch-all partition. New data will only flow into this partition and is not automatically split on a monthly basis. The TDSQL Boundless system does not currently support automatically pre-creating future partitions. You need to periodically execute ALTER TABLE ... ADD PARTITION via an external scheduled task to pre-create partitions for the next time period.ALTER TABLE ... REMOVE PARTITIONING, or directly rebuild the partitioning policy using ALTER TABLE ... PARTITION BY RANGE COLUMNS(...) (...). Note: Rebuilding partitions involves data reorganization. For large tables, evaluate the cost and perform this operation during off-peak business hours.MAXVALUE catch-all partition is not defined when a table is created, a write operation will report error ER_NO_PARTITION_FOR_GIVEN_VALUE, with the message "Table has no partition for value xxx". This is expected behavior. It serves as a catch-all alarm signal for "forgetting to pre-create partitions", preventing data from silently falling into the MAXVALUE partition and causing subsequent cleanup difficulties.Apakah halaman ini membantu?
Anda juga dapat Menghubungi Penjualan atau Mengirimkan Tiket untuk meminta bantuan.
masukan