MySQL 中分区表的使用场景

FreeGuideOnline 最新 2026-07-06

sql CREATE TABLE order_logs ( id BIGINT NOT NULL AUTO_INCREMENT, order_date DATE NOT NULL, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY (id, order_date) ) PARTITION BY RANGE (TO_DAYS(order_date)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')), PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')), PARTITION p_future VALUES LESS THAN MAXVALUE );


**带来的优势**：
- 当执行 `SELECT * FROM order_logs WHERE order_date BETWEEN '2023-02-01' AND '2023-02-28'` 时，MySQL 会直接跳过无关分区，只扫描 **p202302** 分区，实现分区剪裁。
- 需要删除过期数据时，可直接 `ALTER TABLE order_logs TRUNCATE PARTITION p202301`，瞬间完成，不会产生大量 `DELETE` 的锁和事务日志开销，比 `DELETE` 快数个数量级。
- 可定期通过 `REORGANIZE PARTITION` 将 `p_future` 拆分为新月份分区，实现自动化管理。

### 2. 热点数据集中与冷热分离

**典型业务**：社交动态、新闻资讯、商品列表等，访问集中在最新一部分数据。

**场景特征**：
- 80% 以上的查询只命中最近一周或一个月的数据。
- 全表扫描成本极高，但仅依靠索引在超大规模下仍会带来随机 I/O 压力。

**分区方案**：同样采用 RANGE 分区，但意识上强化“按活跃度”分块。通过将近期数据放在单独分区，与历史冷数据物理隔离，减少 InnoDB 缓冲池污染。

**优势**：
- 热数据分区会频繁命中内存，访问速度快。
- 冷数据即使物理存储在慢速磁盘上，也不会影响主要业务。
- 维护操作（如备份、统计）可以只针对近期分区，提高效率。

### 3. 结合地理或业务类别实现分区

**典型业务**：多租户 SaaS 平台（按租户 ID 分区）、全国性业务（按省份或区域分区）。

**场景特征**：
- 不同租户/区域的数据在查询时几乎不会交叉，天然隔离。
- 某个特定类别的数据需要独立管理（如 VIP 用户数据、特定渠道数据）。

**分区方案**：**LIST 分区** 或 **KEY 分区**。

```sql
CREATE TABLE tenant_stats (
    tenant_id INT NOT NULL,
    stat_date DATE,
    metric_value DECIMAL(10,2),
    PRIMARY KEY (tenant_id, stat_date)
) PARTITION BY LIST (tenant_id) (
    PARTITION p_tenant1 VALUES IN (1),
    PARTITION p_tenant2 VALUES IN (2),
    PARTITION p_tenant3 VALUES IN (3),
    PARTITION p_others VALUES IN (4,5,6,7,8)
);

优势：

查询某个租户的数据时，只扫描该租户所在分区。
当某个大租户需要搬迁或单独优化时，可以方便地通过 EXCHANGE PARTITION 与其他表交换分区数据，实现数据迁移。
可为不同分区指定不同的存储引擎或表空间路径，实现物理隔离。

4. 解决大表索引膨胀与写入瓶颈

场景特征：

表行数达亿级别，即使使用 B+Tree 索引，索引本身也极其庞大，维护开销高。
写入操作频繁，维护全局索引带来的页分裂和锁竞争成为瓶颈。

分区方案：使用 HASH 分区（或 KEY 分区）将数据均匀打散到多个分区。分区数量通常是 2 的幂次（如 8, 16, 32）。

CREATE TABLE large_events (
    event_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    payload TEXT,
    PRIMARY KEY (event_id, user_id)
) PARTITION BY HASH (user_id) PARTITIONS 16;

优势：

每个分区变为一张较小的表，其索引大小、写入热点都被分散到不同物理段上，整体写入吞吐提升。
虽然 HASH 分区不支持显式的范围查询优化，但对于通过 user_id 等值查询的场景，仍然能精准路由到唯一分区，实现约束检索。

5. 快速离线处理与数据交换

场景特征：

ETL 任务需要将原始数据导入临时表清洗后，再并入正式表。
需要将某个全量的历史分区替换为新的结果。

分区方案：利用分区交换功能 ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE。

例如，将一张与分区结构相同的外部表 tmp_orders 的数据瞬间交换到 orders 表的 p202302 分区，仅涉及元数据修改，无需数据移动。

优势：

极速数据迁移，对线上业务几乎无影响。
方便实现“影子表”更新方案。

分区表的限制与注意事项

分区键与唯一索引约束：所有用于唯一约束的列（主键、唯一键）必须包含分区表达式使用的所有列。这是最常见的建表失败原因。
不支持外键：分区表无法定义或引用外键约束。
不支持全文索引和空间索引。
分区数量不宜过多：当分区数达到数百上千时，打开表的文件句柄数会增加，查询优化器分析分区也会付出开销。建议单表分区数控制在 1024 以内。
NULL 值处理：RANGE 分区中 NULL 会被视为最小值放入第一个分区；LIST 分区需要显式声明包含 NULL 的分区值列表，否则插入 NULL 会报错。
从 MySQL 8.0 起，分区表可以单独指定 DATA DIRECTORY 和 INDEX DIRECTORY，达到物理分离。

如何选择一个合适的分区策略

最优先考虑查询模式：分区键必须是查询中最频繁使用的过滤条件。
时间序列数据：首选 RANGE 分区，配合定期分区管理脚本。
等值查询且没有范围需求：HASH/KEY 分区能均匀打散，适合写入密集型。
离散的类目数据：LIST 分区。
永远为未来预留分区：如使用 MAXVALUE 或定期重组分区。
测试后再上线：在不同分区方案下使用 EXPLAIN PARTITIONS 查看实际是否发生分区剪裁。

分区表维护示例

新增分区：

ALTER TABLE order_logs ADD PARTITION (
    PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01'))
);

清空分区数据：

ALTER TABLE order_logs TRUNCATE PARTITION p202301;

删除分区及数据：

ALTER TABLE order_logs DROP PARTITION p202301;

重组分区（拆分 MAXVALUE）：

ALTER TABLE order_logs REORGANIZE PARTITION p_future INTO (
    PARTITION p202305 VALUES LESS THAN (TO_DAYS('2023-06-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);