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.Esta página foi útil?
Você também pode entrar em contato com a Equipe de vendas ou Enviar um tíquete em caso de ajuda.
comentários