分区表设计原则：粒度、键选择与跨分区规避

分区表设计，说白了就是给大表“分家”。分得好，查询快、维护省；分得不好，比没分区还慢。我见过太多人一上来就分几百个区，结果查询反而变慢。今天咱们就聊聊分区设计的三个核心原则。

一、分区粒度选择：别太细，也别太粗

分区粒度，就是每个分区里放多少数据。这个度怎么把握？我个人习惯遵循一个原则：单个分区数据量在 100 万到 500 万行之间。当然，这跟你的硬件和业务有关。

核心原则：分区粒度 = 业务查询粒度 × 数据增长速度

举个例子。我做过一个订单系统，每天产生 50 万条订单。按天分区，每个分区 50 万行，查询一个月的数据要扫描 30 个分区。后来改成按月分区，每个分区约 1500 万行，查询一个月的数据只扫 1 个分区。性能提升了 3 倍。

为什么？因为分区裁剪（Partition Pruning）的效率跟分区数量成反比。分区太多，优化器要花时间判断哪些分区需要扫描。

业务场景	推荐粒度	单分区数据量	说明
日志类（写入多，查询少）	按天	100 万 - 500 万	便于定期清理历史数据
交易类（查询频繁）	按月	500 万 - 2000 万	减少分区数量，提升查询效率
历史归档类	按年	2000 万以上	数据几乎不查询，只做存储
实时监控类	按小时	10 万 - 50 万	需要快速写入和近实时查询

注意：分区数量不要超过 1024 个（MySQL 限制）。超过这个数，元数据管理会变得很慢。我曾经接手过一个系统，分了 2000 多个分区，每次 DDL 操作都要等几分钟。

二、分区键选择策略：选对键，事半功倍

分区键是分区的依据。选错了，分区表就是个摆设。我总结了三类常用的分区键策略：

2.1 时间类型分区键

这是最常见的。适合日志、订单、流水等有时间属性的数据。我建议用 created_at 或 event_time 这类字段。

-- MySQL 示例：按月份范围分区
CREATE TABLE orders (
    id BIGINT,
    order_date DATE,
    amount DECIMAL(10,2),
    user_id INT
)
PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202401 VALUES LESS THAN (202402),
    PARTITION p202402 VALUES LESS THAN (202403),
    PARTITION p202403 VALUES LESS THAN (202404),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

嗯，这里要注意：时间分区键一定要是查询的过滤条件。如果查询里没有时间范围，分区裁剪就失效了。

2.2 业务标识分区键

比如按用户 ID、地区 ID、商户 ID 分区。适合数据有明显业务分片的场景。

-- PostgreSQL 示例：按用户 ID 哈希分区
CREATE TABLE user_logs (
    log_id BIGSERIAL,
    user_id INT,
    action TEXT,
    created_at TIMESTAMP
) PARTITION BY HASH (user_id);

-- 创建 8 个分区
CREATE TABLE user_logs_p0 PARTITION OF user_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 0);
CREATE TABLE user_logs_p1 PARTITION OF user_logs
    FOR VALUES WITH (MODULUS 8, REMAINDER 1);
-- ... 以此类推

我曾经做过一个社交平台，按用户 ID 哈希分 16 个区。查询某个用户的所有行为，直接定位到一个分区，速度飞快。

2.3 组合分区键

当单一字段无法均匀分布数据时，可以考虑组合键。比如先按时间范围分区，再按用户哈希子分区。

-- MySQL 示例：范围 + 哈希组合分区
CREATE TABLE orders (
    id BIGINT,
    order_date DATE,
    user_id INT,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH (user_id)
SUBPARTITIONS 4 (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

选键口诀：查询条件里必须有分区键；分区键的基数要足够大；数据要均匀分布到各个分区。

三、避免跨分区查询

跨分区查询是分区表性能的杀手。为什么？因为每个分区都要单独扫描，最后还要合并结果。说白了，就是本来可以走索引快速定位，现在变成了全表扫描的变种。

我画了一张图，帮你理解分区裁剪和跨分区查询的区别：

你看，左边只扫 1 个分区，右边要扫 4 个。数据量越大，差距越明显。

3.1 跨分区查询的常见场景

分区键不在 WHERE 条件里：这是最常见的。比如按时间分区，但查询条件只有 user_id。
分区键使用了函数：比如 WHERE DATE(created_at) = '2024-01-01'，MySQL 无法做分区裁剪。
范围查询跨多个分区：比如查三个月的数据，如果按月分区，就要扫三个区。

3.2 如何避免跨分区查询

我总结了四个方法，你可以对照着用：

查询时带上分区键：这是最直接的。比如按时间分区，查询一定要带时间范围。
分区键和索引配合：在分区键上建索引，同时在常用查询字段上建二级索引。
使用分区键的原始值：不要对分区键做函数转换。用 WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'，而不是 WHERE DATE(created_at) = '2024-01-15'。
业务层做分区路由：在应用层根据分区键的值，直接路由到对应的分区查询。

避坑指南：我曾经接手过一个系统，按 user_id 哈希分区，但查询全是按时间范围。结果每个查询都要扫所有分区，比全表扫描还慢。后来加了时间字段的全局索引，才解决问题。记住：分区键必须是你最常用的查询条件。

总结一下

分区表设计，三个原则要记牢：

粒度选择：单分区 100 万 - 500 万行，分区数不超过 1024
键选择：时间、业务标识或组合键，确保查询条件包含分区键
跨分区规避：查询必须带分区键，别用函数包裹分区键

做到这三点，你的分区表基本不会出大问题。下节课咱们聊聊分区表的兼容性调整，那才是真正考验功力的地方。

📚 课程目录

分区表设计原则：粒度、键选择与跨分区规避

一、分区粒度选择：别太细，也别太粗

二、分区键选择策略：选对键，事半功倍

2.1 时间类型分区键

2.2 业务标识分区键

2.3 组合分区键

三、避免跨分区查询

3.1 跨分区查询的常见场景

3.2 如何避免跨分区查询

总结一下