数据库分区表实战：Range/List/Hash 分区、分区裁剪与归档

逻辑视图：                         物理存储：
  users_log                        users_log#p20250101
  ├── 分区 p20250101  ←--分区间隔-->  ├── 数据文件
  ├── 分区 p20250201                users_log#p20250201
  ├── 分区 p20250301                  └── ...
  └── ...
  (对应用 SQL 透明)

CREATE TABLE order_log (
    id BIGINT,
    created_at DATETIME,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, created_at)     -- 分区键必须包含在唯一键/主键里
) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p2024q1 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p2024q2 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p2024q3 VALUES LESS THAN (TO_DAYS('2024-10-01')),
    PARTITION p2024q4 VALUES LESS THAN (TO_DAYS('2025-01-01')),
    PARTITION pFuture  VALUES LESS THAN MAXVALUE
);

CREATE TABLE region_stat (
    region_code CHAR(2),
    data JSON,
    PRIMARY KEY (region_code, id)
) PARTITION BY LIST (region_code) (
    PARTITION p_north VALUES IN ('BJ','TJ','HB'),
    PARTITION p_east  VALUES IN ('SH','JS','ZJ'),
    PARTITION p_other VALUES IN ('GZ','SC','GD')
);

CREATE TABLE metric (
    id BIGINT,
    device_id BIGINT,
    value DOUBLE,
    PRIMARY KEY (id, device_id)
) PARTITION BY HASH (device_id) PARTITIONS 8;

-- ✅ 命中 p2024q3，只扫该分区（裁剪生效）
SELECT * FROM order_log
WHERE created_at >= '2024-07-01' AND created_at < '2024-10-01';

-- ❌ 裁剪失效：分区键上没有可推导的条件
SELECT * FROM order_log WHERE status = 'PAID';

□ WHERE 中直接对分区键写范围/等值条件
□ 避免对分区键使用函数（TO_DAYS(created_at) 在列上 → 裁剪失效）
□ 传参尽量用具体的日期字面量/强类型，少用隐式转换
□ JOIN/子查询中涉及分区键时，条件尽量下推
□ 用 EXPLAIN PARTITIONS 验证实际命中了哪些分区

EXPLAIN PARTITIONS SELECT * FROM order_log WHERE created_at >= '2024-08-01'\G
-- 期望看到 partitions: p2024q3,p2024q4

分区内索引（默认）：
  p2024q1: idx_user (B+树)  + 数据
  p2024q2: idx_user (B+树)  + 数据

唯一性约束：
  - 唯一索引必须包含分区键（否则全局唯一无法保证）
  - 所以 username 全局唯一 + 按 created_at 分区 → 做不到！

-- 手机号唯一 + 时间分区：把 phone 放进主键/唯一键
CREATE TABLE user_account (
    phone CHAR(11),
    created_at DATETIME,
    ...
    PRIMARY KEY (phone, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (...);

-- 新增分区（为未来数据预留）
ALTER TABLE order_log ADD PARTITION (PARTITION p2025q1 VALUES LESS THAN (TO_DAYS('2025-04-01')));

-- 删除历史分区（比 DELETE 快得多）
ALTER TABLE order_log DROP PARTITION p2024q1;

-- 清空分区（保留结构）
ALTER TABLE order_log TRUNCATE PARTITION p2024q2;

-- 合并/重建分区（InnoDB 需重建以回收空间）
ALTER TABLE order_log REBUILD PARTITION p2024q3;

-- 交换分区（把数据快速挪入/挪出）
ALTER TABLE order_log EXCHANGE PARTITION p2024q1 WITH TABLE order_log_archive;

推荐组合：
  1. 按天/按季建分区
  2. 定时任务提前建未来 N 个分区（如提前 30 天）
  3. 到期的历史分区 DETACH/导出到归档库
  4. 归档完成后再 DROP，避免长时间占用空间

用分区实现"秒删历史数据"，比 DELETE 大表高效一个数量级。