【Mysql】创建表分区
-
【Mysql】创建表分区，示例代码：每个taglogging单独一个表，分成十年，每年按照哈希算法分成100份。
-- Partition the table
ALTER TABLE tlg_vid_01_10
PARTITION BY RANGE(YEAR(TIMESTAMP))
SUBPARTITION BY HASH(TO_DAYS(TIMESTAMP))
SUBPARTITIONS 100
(
PARTITION p0 VALUES LESS THAN (2021),
PARTITION p1 VALUES LESS THAN (2022),
PARTITION p2 VALUES LESS THAN (2023),
PARTITION p3 VALUES LESS THAN (2024),
PARTITION p4 VALUES LESS THAN (2025),
PARTITION p5 VALUES LESS THAN (2026),
PARTITION p6 VALUES LESS THAN (2027),
PARTITION p7 VALUES LESS THAN (2028),
PARTITION p8 VALUES LESS THAN (2029),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
-- Show Table Partition
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = 'OS07'
AND TABLE_NAME='tlg_vid_01_10' ;
-- Show Table Directory
SHOW VARIABLES LIKE 'datadir';
-
-
创建一个新的tlg表：
- 创建表
- 主键TableID，自增
- truncat清空表
- 表分区Partition
- Optimize优化表
- 下面代码不完全对，仅复制参考修改
CREATE TABLEos07.tlgxx_tempate(
PRIMARY KEY (TableID,TimeStamp)
) ENGINE = INNODB COLLATE = utf8_general_ci COMMENT = ''
SELECT
TableID,
ValueID,
TimeStamp,
TagValue,
Qaulity,
FROM
os07.tlg13_batchid
WHERE 1 = 0
-- Clear Table, Primary Key from 0;
TRUNCATE TABLEtlgxx_tempate;
-- Partition the table
ALTER TABLE tlgxx_tempate
PARTITION BY RANGE(YEAR(TIMESTAMP))
SUBPARTITION BY HASH(TO_DAYS(TIMESTAMP))
SUBPARTITIONS 100
(
PARTITION p0 VALUES LESS THAN (2021),
PARTITION p1 VALUES LESS THAN (2022),
PARTITION p2 VALUES LESS THAN (2023),
PARTITION p3 VALUES LESS THAN (2024),
PARTITION p4 VALUES LESS THAN (2025),
PARTITION p5 VALUES LESS THAN (2026),
PARTITION p6 VALUES LESS THAN (2027),
PARTITION p7 VALUES LESS THAN (2028),
PARTITION p8 VALUES LESS THAN (2029),
PARTITION p9 VALUES LESS THAN (MAXVALUE)
);
-- Optimize Partition
ALTER TABLE tlgxx_tempate
OPTIMIZE PARTITION p0,p1,p2,p3,p4,p5,p6,p7,p8,p9
- 创建表