MySQL 5.7 Percona如何实现无数据重写的近似等大小范围分区？

created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION foo_1640995200 VALUES LESS THAN (1640995200) ENGINE = InnoDB, # 2022-01-01 00:00:00
PARTITION foo_1641081600 VALUES LESS THAN (1641081600) ENGINE = InnoDB, # 2022-01-02 00:00:00
PARTITION foo_1641168000 VALUES LESS THAN (1641168000) ENGINE = InnoDB # 2022-01-03 00:00:00
);

该方案存在数据分布不均的问题，部分分区有100万行数据，部分则有5000万行，这会导致长范围查询（如`SELECT * FROM foo WHERE created_at > NOW() - INTERVAL 1 YEAR`）时打开过多表。

我希望优化为：当最后一个分区的行数低于阈值时，直接扩展该分区而非新建日分区，示例操作如下：
```sql
SELECT `table_rows`
FROM `information_schema`.`partitions`
WHERE table_schema = DATABASE()
    AND partition_name = 'foo_1641168000';

-- 仅100万行，无需新建分区，扩展现有分区：

ALTER TABLE `foo` REORGANIZE PARTITION `foo_1641168000` INTO (
    PARTITION `foo_1641254400` VALUES LESS THAN (1641254400) ENGINE = InnoDB  # 2022-01-04 00:00:00
);

但此操作虽仅修改范围，却会完全重写foo_1641168000分区的数据，即便现有数据完全符合新分区定义，这会造成表锁和过度I/O消耗，无法接受。

请问是否存在无需重写数据即可实现该需求的方法？

另外，我有一个临时方案：将近期数据写入foo_recent表，当达到一定规模时，通过EXCHANGE PARTITION .. WITHOUT VALIDATION将其作为分区并入foo，但该方案不够规范，且在性能和语法上都存在弊端——查询需处理表联合或分别查询后合并结果。

解决方案 #

核心结论 #

在MySQL 5.7（包括Percona分支）中，不存在无需重写数据即可直接扩展现有分区范围的方法。REORGANIZE PARTITION操作本质是重建分区，无论数据是否匹配新范围，都会触发数据重写，这由该版本的分区实现机制决定。以下是可行的替代优化方案：

1. 优化临时表交换方案（低I/O、原子性） #

针对你提到的foo_recent临时表方案，可通过视图封装解决查询联合的弊端：

• 创建联合视图，对业务透明化查询逻辑：

CREATE VIEW v_foo AS
SELECT * FROM foo
UNION ALL
SELECT * FROM foo_recent;

• 定时监控foo_recent的行数，达到阈值时执行分区交换：

-- 先为foo新增一个对应范围的空分区
ALTER TABLE foo ADD PARTITION (
    PARTITION `foo_new` VALUES LESS THAN (<<目标时间戳>>) ENGINE = InnoDB
);
-- 交换分区与临时表（无数据重写，仅元数据变更）
ALTER TABLE foo EXCHANGE PARTITION foo_new WITH TABLE foo_recent WITHOUT VALIDATION;
-- 清空临时表，准备接收新数据
TRUNCATE TABLE foo_recent;

该操作原子性强，I/O消耗极低，且通过视图屏蔽了多表查询的复杂度。

2. 切换为基于数据量的动态分区策略 #

放弃固定日分区，改为按数据量创建分区，保证每个分区数据规模均匀：

• 预先创建初始分区，保留一个MAXVALUE的弹性分区：

CREATE TABLE `foo` (
  ...
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC
PARTITION BY RANGE (UNIX_TIMESTAMP(`created_at`)) (
    PARTITION `foo_init` VALUES LESS THAN (<<初始时间戳>>) ENGINE = InnoDB,
    PARTITION `foo_elastic` VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

• 定时任务监控information_schema.partitions中foo_elastic的table_rows，当达到阈值（如5000万行）时，执行拆分：

-- 计算新分区的时间范围（比如覆盖未来30天，或根据数据增速调整）
ALTER TABLE foo REORGANIZE PARTITION foo_elastic INTO (
    PARTITION `foo_<<新时间戳>>` VALUES LESS THAN (<<新时间戳>>) ENGINE = InnoDB,
    PARTITION `foo_elastic` VALUES LESS THAN MAXVALUE ENGINE = InnoDB
);

此方案减少了长范围查询时需要打开的分区数量，数据分布更均衡。

内容的提问来源于stack exchange，提问作者Pawel Pabian bbkr