Mysql数据表分区
分区步骤
1.新增分区键:
这个命令向表 hf_tran_info 中添加一个名为 tran_partition 的 BIGINT 类型列,以用作分区键。
ALTER TABLE hf_tran_info ADD COLUMN tran_partition BIGINT;
2.删唯一索引:
分区键是主键一部分及包含唯一索引(删除原表索引并添加主键)
ALTER TABLE hf_tran_info DROP INDEX message_id;
3.备注原表索引:
恢复先前删除的索引
ALTER TABLE hf_tran_info ADD INDEX idx_tran_partition (id);
4.更新分区值:
更新表中的 tran_partition 列的值(10分钟取一次时间戳)
UPDATE hf_tran_info SET tran_partition = UNIX_TIMESTAMP(STR_TO_DATE(tran_time, '%m%d %H:%i:%s.%f')) DIV (10*60);
5.新增分区:
在 ALTER TABLE 语句中,想要根据 tran_partition 列的值来进行分区,并创建一个名为 p2859568 的分区,该分区包含了 tran_partition 列中值为 2859568 的行。
ALTER TABLE hf
PARTITION BY LIST(tran_partition )
(
PARTITION p2859568 VALUES IN (2859568)
);
这个语句做了以下几件事情:
使用 ALTER TABLE 命令来修改表 hf 的结构。
使用 PARTITION BY LIST(tran_partition ) 指定了根据 tran_partition 列的值进行分区。
使用 PARTITION p2859568 VALUES IN (2859568) 创建了一个名为 p2859568 的分区,并指定了该分区包含了 tran 列中值为 2859568 的行。
确保在执行任何对表结构的修改之前备份数据,并确保已经仔细测试了这个 ALTER TABLE 语句,以确保它符合你的预期并且不会导致数据丢失或其他不良影响。
-- 其他新增分区策略 hash、list、range
-- 对 tran 列使用 HASH 分区,创建 221 个分区
ALTER TABLE hf
PARTITION BY HASH(tran)
PARTITIONS 221;
-- 对 tran 列使用 RANGE 分区,创建一个名为 pl 的分区,包含值小于 2859568 的行:
ALTER TABLE hf
PARTITION BY RANGE(tran)
(
PARTITION pl VALUES LESS THAN (2859568)
);
-- 对 tran 列使用 LIST 分区,创建一个名为 pl 的分区,包含值为 2859568 的行:
ALTER TABLE hf
PARTITION BY LIST(tran)
(
PARTITION pl VALUES IN (2859568)
);
请注意,每个语句都使用了 ALTER TABLE 命令来修改表结构,并在其中指定了不同的分区策略和参数。在执行这些语句之前,请务必备份数据并确保语法正确以及符合你的预期。
6.查询分区:
show create table hf_tran_info
7.根据分区值查询分区名称:
SELECT partition_name
FROM information_schema.PARTITIONS
WHERE table_schema = 'hf'
AND table_name = 'hf_tran_info'
AND partition_description > ((UNIX_TIMESTAMP(STR_TO_DATE('2024-05-15 07:10:00.560', '%Y-%m-%d %H:%i:%s.%f')) DIV (10*60)))
AND partition_description < ((UNIX_TIMESTAMP(STR_TO_DATE('2024-05-16 07:10:00.560', '%Y-%m-%d %H:%i:%s.%f')) DIV (10*60)))
LIMIT 1;
这个查询会从信息模式中选取分区名,其中库为 ‘hf’,表名为 ‘hf_tran_info’,并且分区描述在指定的时间范围内。最后,使用 LIMIT 1 限制结果集只返回第一个匹配的分区名称