MySQL 8.0 分区表与数据生命周期管理：RANGE、LIST、HASH 实战与性能验证

## 背景

对海量数据进行生命周期管理（冷热分层），分区表可显著降低查询扫描范围与维护成本。MySQL 8.0 提供 RANGE/LIST/HASH/KEY 等分区方式，InnoDB 原生支持。

## 关键约束（需严格遵守）

• 唯一键（含 `PRIMARY KEY` 与 `UNIQUE`）必须包含所有分区列。
• 全局索引不支持；索引在分区内维护。
• 分区表达式需为确定性，且遵循类型限制（如 RANGE/LIST 常用日期/枚举列）。

## 典型建模：按日期 RANGE 分区

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL,
  order_date DATE NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (id, order_date), -- 唯一键包含分区列
  KEY (order_date),
  KEY (user_id)
)
PARTITION BY RANGE COLUMNS(order_date) (
  PARTITION p2024q4 VALUES LESS THAN ('2025-01-01'),
  PARTITION p2025q1 VALUES LESS THAN ('2025-04-01'),
  PARTITION p2025q2 VALUES LESS THAN ('2025-07-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

## 验证分区裁剪（Pruning）

EXPLAIN SELECT * FROM orders WHERE order_date BETWEEN '2025-02-01' AND '2025-03-31';

• 观察 `partitions` 字段应仅命中 `p2025q1`。
• 进一步验证：

SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'orders';

## LIST 与 HASH 分区示例

-- LIST：按国家分层
CREATE TABLE user_profiles (
  uid BIGINT UNSIGNED NOT NULL,
  country_code CHAR(2) NOT NULL,
  PRIMARY KEY (uid, country_code)
)
PARTITION BY LIST COLUMNS(country_code) (
  PARTITION p_us VALUES IN ('US'),
  PARTITION p_eu VALUES IN ('DE','FR','ES','IT'),
  PARTITION p_ap VALUES IN ('CN','JP','KR'),
  PARTITION p_other VALUES IN (DEFAULT)
);

-- HASH：均衡分布（注意：唯一键包含分区列）
CREATE TABLE events (
  id BIGINT UNSIGNED NOT NULL,
  bucket INT NOT NULL,
  payload JSON,
  PRIMARY KEY (id, bucket)
)
PARTITION BY HASH(bucket)
PARTITIONS 32;

## 生命周期管理：滚动归档与扩容

• 新周期上线：

ALTER TABLE orders ADD PARTITION (
  PARTITION p2025q3 VALUES LESS THAN ('2025-10-01')
);

• 归档旧数据：

ALTER TABLE orders REORGANIZE PARTITION p2024q4 INTO (
  PARTITION p2024_archive VALUES LESS THAN ('2025-01-01')
);
-- 归档后可通过交换分区到归档库（且保持元数据一致性）

• 删除过期分区：

ALTER TABLE orders DROP PARTITION p2024_archive;

## 查询优化与索引建议

• 过滤条件包含分区列以充分触发裁剪。
• 二级索引仍需维护成本，按访问模式选择必要索引。
• 大表统计建议启用 `innodb_stats_persistent = ON` 并计划性 `ANALYZE TABLE`。

## 性能验证流程（可复现）

1. 构造 1 亿行数据（`INSERT ... SELECT` 或批量导入）。

2. 对比有/无分区的相同查询：记录 `ROWS` 扫描量与执行时长。

3. 使用 `EXPLAIN` 与 `information_schema.PARTITIONS` 验证仅命中预期分区。

4. 对维护动作（`ADD/DROP PARTITION`）测量耗时与对线上影响（建议离峰窗口）。

## 维护与告警

• 监控 `information_schema.PARTITIONS.TABLE_ROWS` 的增长趋势，超阈值预警进行分区滚动。
• 建立分区时间线与脚本，保证新周期提前上线，避免写入落在 `MAXVALUE`。

## 总结

分区表在生命周期管理与扫描裁剪方面极具价值。遵循唯一键包含分区列等关键约束，并以 EXPLAIN/信息_schema 进行持续验证，可在保障正确性的前提下获得稳定的性能收益与可维护性。