分区表基础：分区键的选择、分区类型与创建语法

分区表这东西，说白了就是把一张大表拆成多个小表来管理。但拆得好不好，关键看两件事：分区键选得对不对，分区类型用得合不合适。我见过不少项目，分区建了，查询却没快多少，原因就是这两点没想清楚。

分区键的选择——最容易被忽视的决策

分区键决定了数据怎么分到各个分区里。选错了，分区表就是个摆设。

核心原则：分区键必须是查询中最常用的过滤条件。如果 WHERE 子句里经常出现某个字段，那它就很适合做分区键。

我个人习惯按这几个维度来评估：

  • 区分度——字段的值分布要均匀，别让某个分区数据量特别大，其他分区却空着
  • 稳定性——分区键的值尽量不要频繁更新，否则数据跨分区移动代价很高
  • 业务相关性——最好跟数据生命周期挂钩，比如按时间分区方便归档清理

我的经验：有一次做电商订单表，选了用户ID做分区键。结果发现热门用户的分区数据量是冷门用户的几百倍，分区裁剪根本没效果。后来改成按订单日期分区，查询和清理都顺畅多了。

分区类型——三种主流方案怎么选

主流数据库（MySQL、PostgreSQL、Oracle）都支持三种分区类型。我按使用频率排个序：

分区类型 适用场景 典型分区键 我遇到的坑
范围分区 时间序列数据、日志、历史记录 日期、时间戳、自增ID 边界值没处理好，数据跑到默认分区
列表分区 枚举值、地域、状态码 省份、状态、类别 新值出现时忘记加分区，插入报错
哈希分区 数据分布均匀、没有明显范围特征 用户ID、订单号 分区数设成质数，数据分布更均匀

范围分区（RANGE）

这是最常用的分区方式。按连续区间划分数据，比如按月、按季度。

-- MySQL 示例：按月份范围分区
CREATE TABLE orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    amount DECIMAL(10,2)
)
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
);

注意：我曾经犯过一个错——没设 MAXVALUE 分区。结果某天来了个未来日期的数据，直接报错。从那以后，我习惯在范围分区末尾加一个"兜底"分区。

列表分区（LIST）

适合按离散值分组。比如按省份、按业务类型。

-- PostgreSQL 示例：按地区列表分区
CREATE TABLE sales (
    sale_id INT,
    region TEXT,
    amount NUMERIC
)
PARTITION BY LIST (region);

CREATE TABLE sales_north PARTITION OF sales
    FOR VALUES IN ('北京', '天津', '河北');

CREATE TABLE sales_south PARTITION OF sales
    FOR VALUES IN ('广东', '广西', '海南');

CREATE TABLE sales_other PARTITION OF sales
    DEFAULT;

小技巧：列表分区最好也留一个 DEFAULT 分区。业务扩展时，新值不会直接报错，给你留出时间调整分区定义。

哈希分区（HASH）

当数据没有明显的范围或列表特征时，哈希分区能让数据均匀散列到各个分区。

-- MySQL 示例：按用户ID哈希分区
CREATE TABLE user_logs (
    log_id INT,
    user_id INT,
    action VARCHAR(50),
    log_time DATETIME
)
PARTITION BY HASH (user_id)
PARTITIONS 8;  -- 建议设成2的幂次方

嗯，这里有个细节——分区数设成 2 的幂次方（2、4、8、16），数据分布会更均匀。我试过设成 7 个分区，结果某些分区数据量明显偏大，后来改成 8 个就好了。

分区表创建语法——不同数据库的差异

虽然核心概念一样，但各数据库的语法细节有差异。我整理了一张对比表：

数据库 范围分区语法特点 列表分区 哈希分区
MySQL VALUES LESS THAN VALUES IN PARTITION BY HASH
PostgreSQL FOR VALUES FROM ... TO FOR VALUES IN 不直接支持，用继承模拟
Oracle VALUES LESS THAN VALUES (list) PARTITION BY HASH

关键提醒：分区键必须包含在主键或唯一索引中。这是很多新手踩的坑——建表时主键没包含分区键，结果报错。MySQL 尤其严格，分区键必须是主键的一部分。

分区表的核心逻辑——一张图看懂

下面这张图展示了分区表的核心工作流程。你想想看，查询进来时，数据库怎么知道该扫哪个分区？

分区表核心工作流程 SQL 查询 分区裁剪：WHERE 条件匹配分区键？ 只扫描匹配的分区 全表扫描（性能差） 返回查询结果 分区裁剪的核心：查询条件能匹配到分区键时，只扫描相关分区；否则退化为全表扫描 分区键选择不当 → 分区裁剪失效 → 分区表形同虚设

说白了，分区裁剪就是数据库的"智能扫描"机制。查询条件里带了分区键，数据库就知道只扫相关分区。没带分区键？那就老老实实扫全表，分区表反而多了元数据开销。

避坑指南：我曾经接手过一个系统，分区表建了 24 个分区（按小时），但查询条件里从来没用过时间字段。结果每次查询都扫 24 个分区，比普通表还慢。分区不是建了就完事，得让查询用上分区键。

创建分区表的最佳实践

最后，我总结几条创建分区表时的实操建议：

  1. 先评估数据量——单表超过 500 万行或 10GB 再考虑分区，小表分区反而增加复杂度
  2. 分区数不要太多——MySQL 建议不超过 1024 个分区，实际项目中我一般控制在 50 个以内
  3. 分区键要稳定——别选经常更新的字段，否则每次更新都触发数据迁移
  4. 预留扩展空间——范围分区留 MAXVALUE，列表分区留 DEFAULT，哈希分区选 2 的幂次方
  5. 测试分区裁剪——建完表后用 EXPLAIN 验证查询是否真的只扫了目标分区

我的习惯：每次建完分区表，我都会跑一遍 EXPLAIN PARTITIONS（MySQL）或 EXPLAIN (ANALYZE, BUFFERS)（PostgreSQL），确认分区裁剪生效。这一步花不了几分钟，但能避免上线后才发现问题。

← 上一章 📖 返回目录 下一章 →