partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list) }
[SUBPARTITIONS num]
]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES
{LESS THAN {(expr | value_list) | MAXVALUE}
|
IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
分区类型
[LINEAR] HASH(expr)
根据值的哈希分区[LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
RANGE{(expr) | COLUMNS(column_list)}
根据值得范围分区LIST{(expr) | COLUMNS(column_list)}
根据不同的值分区
COLUMNS 不限于整数
创建分区
PARTITION BY LIST(column) (
PARTITION a VALUES IN (a1, a2, a3),
PARTITION b VALUES IN (b1, b2, b3),
PARTITION c VALUES IN (c1, c2, c3)
)
PARTITION BY RANGE(column) (
PARTITION 2012q1 VALUES LESS THAN('2012-04-01'),
PARTITION 2012q2 VALUES LESS THAN('2012-07-01'),
PARTITION 2012q3 VALUES LESS THAN('2012-10-01'),
PARTITION 2012q4 VALUES LESS THAN('2013-01-01')
)
PARTITION BY HASH(column) PARTITIONS 128
PARTITION BY HASH(dayofmonth(date)) PARTITIONS 31
查看分区信息
SELECT * FROM `information_schema`.`PARTITIONS`;
子分区
PARTITION
关键字换成SUBPARTITION
,PARTITIONS
关键字换成SUBPARTITIONS
,接在分区语句后面。- 可以是不同类型。
比如:
PARTITION BY HASH (prod_id) SUBPARTITION BY HASH (cust_id)
PARTITIONS 4 SUBPARTITIONS 4;
脚本
如果是 By Range 分区,一般需要自动创建新的分区,删除久的分区。
比如:
CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT,
`date` DATE NOT NULL,
`key` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
`value` VARCHAR(300) NOT NULL COLLATE 'utf8mb4_general_ci',
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`, `date`) USING BTREE,
UNIQUE INDEX `key` (`date`, `key`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
/*!50100 PARTITION BY RANGE (to_days(`date`))
(PARTITION p20230123 VALUES LESS THAN (738909) ENGINE = InnoDB,
PARTITION p20230124 VALUES LESS THAN (738910) ENGINE = InnoDB,
PARTITION p20230125 VALUES LESS THAN (738911) ENGINE = InnoDB) */;
然后,通过下面这个 cron 任务自动更新分区:
#!/bin/bash
# 开启调试模式,输出每条执行的命令及其执行结果
set -x
# 检查当前机器 IP 地址中是否包含指定的 VIP(虚拟 IP)
# 确认在主 MySQL 上执行
vip_w="192.168.12.34"
if [ $(/sbin/ip a | grep "${vip_w}" | wc -l) -eq 0 ]; then echo 'WARN: Wrong Machine!!!'; exit 1; fi
# 删除 90 天前的分区
# PS:如果分区不存在,TRUNCATE 不会报错。
delete_date=$(date -d '90 days ago' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test TRUNCATE PARTITION p$delete_date;" # DROP
# 创建未来分区
create_date=$(date -d '7 days' +%Y%m%d)
mysql -uroot -p123456 -e "USE test; ALTER TABLE test ADD PARTITION (PARTITION p$delete_date VALUES LESS THAN (TO_DAYS("$delete_date")));"