3. MySQL分区表实现：RANGE、LIST、HASH、KEY分区语法与示例

分区表这东西，说白了就是把一张大表拆成多个物理小表来管理。MySQL从5.1开始支持分区，我最早在生产环境用分区表是2014年——那时候还踩了不少坑。今天咱们把四种主流分区方式挨个过一遍，语法、示例、适用场景，一次讲透。

核心认知：分区对应用层是透明的。你写SQL时感觉不到分区存在，但MySQL内部会根据分区键把数据路由到对应分区。查询时如果条件能匹配分区键，就会触发分区裁剪——只扫描相关分区，性能提升非常明显。

3.1 RANGE分区：按范围切分

RANGE分区是最常用的分区方式。它根据列值的连续范围来分配行。我个人习惯用时间字段做RANGE分区，比如按月份、按季度。

语法示例：

CREATE TABLE orders (
    order_id INT NOT NULL,
    customer_id INT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p_2021 VALUES LESS THAN (2022),
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

这里有个关键点：VALUES LESS THAN定义的是上界，不包含边界值。比如2022-01-01的数据会进p_2022分区，而不是p_2021。

我的经验：一定要留一个MAXVALUE分区兜底。我曾经在一个项目里没加这个，结果业务数据提前进入了下一个年份，插入直接报错——凌晨两点被电话叫醒，那滋味不好受。

适用场景：

  • 按时间归档的历史数据（日志、订单、交易记录）
  • 需要定期删除旧分区的场景（直接DROP PARTITION比DELETE快几个数量级）
  • 范围查询频繁的业务（比如查某个月的数据）

3.2 LIST分区：按离散值匹配

LIST分区适合分区键取值是有限集合的场景。比如按地区、按状态、按类型。它不像RANGE那样连续，而是精确匹配。

语法示例：

CREATE TABLE customer_orders (
    order_id INT NOT NULL,
    customer_id INT,
    region ENUM('North','South','East','West') NOT NULL,
    amount DECIMAL(10,2)
)
PARTITION BY LIST (region) (
    PARTITION p_north VALUES IN ('North'),
    PARTITION p_south VALUES IN ('South'),
    PARTITION p_east VALUES IN ('East'),
    PARTITION p_west VALUES IN ('West')
);

注意：LIST分区没有MAXVALUE兜底。如果插入的数据不在任何分区的值列表中，MySQL会直接报错。我曾经在迁移数据时遇到过——源库有个'Central'值，目标库没定义这个分区，结果迁移脚本跑了一半卡住了。

适用场景：

  • 按地理区域划分数据
  • 按业务状态分类（待支付、已支付、已退款）
  • 按数据来源分片（不同渠道的数据放不同分区）

3.3 HASH分区：均匀打散

HASH分区适合分区键取值分布不均匀，但你又想均匀分散数据的场景。MySQL对分区键取模，然后分配到对应分区。

语法示例：

CREATE TABLE user_sessions (
    session_id INT NOT NULL,
    user_id INT NOT NULL,
    login_time DATETIME
)
PARTITION BY HASH (user_id)
PARTITIONS 4;

这里PARTITIONS 4指定了4个分区。MySQL内部计算逻辑是：MOD(user_id, 4)，结果0~3分别对应分区0~3。

关键点：分区数最好是2的幂次方（2、4、8、16...）。为什么？因为后续如果要增加分区，2的幂次方能让数据重新分布时影响最小。这不是MySQL强制要求，是我在实践中总结出来的经验。

适用场景：

  • 分区键取值均匀（比如自增ID、用户ID）
  • 不需要按范围查询，只需要分散IO压力
  • 数据量持续增长，需要水平扩展

3.4 KEY分区：MySQL自带的哈希

KEY分区和HASH分区很像，但有两点区别：第一，KEY分区使用MySQL内置的哈希函数（不是简单的取模）；第二，KEY分区可以不用指定分区键——如果不指定，默认用主键。

语法示例：

CREATE TABLE user_profiles (
    user_id INT NOT NULL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100)
)
PARTITION BY KEY ()
PARTITIONS 4;

看到没？PARTITION BY KEY ()括号里是空的，MySQL自动用主键user_id做分区键。

也可以显式指定分区键：

CREATE TABLE user_profiles (
    user_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY KEY (username)
PARTITIONS 4;

我的建议：如果你不确定用HASH还是KEY，优先选KEY。因为MySQL内置的哈希函数比简单的取模更均匀，能减少数据倾斜的风险。我在一个日活千万的社交平台上做过对比，KEY分区的数据分布偏差比HASH小了大概30%。

适用场景：

  • 分区键是字符串类型（KEY分区对字符串友好）
  • 希望数据分布更均匀
  • 不想手动计算哈希逻辑

3.5 四种分区方式对比

我把四种分区方式的核心差异整理成了一张表，方便你快速对比：

分区类型 分区依据 数据分布 适用分区键类型 兜底机制
RANGE 连续范围 按范围集中 数值、日期 MAXVALUE
LIST 离散值列表 按值分组 枚举、有限集合 无（需手动兜底）
HASH 取模运算 均匀分布 数值 自动（取模结果）
KEY 内置哈希函数 均匀分布 任意类型 自动（哈希结果）

3.6 分区表知识体系总览

下面这张图把四种分区方式的核心逻辑串起来了。你一看就明白：

MySQL 四种分区方式核心逻辑 分区表 RANGE 分区 LIST 分区 HASH 分区 KEY 分区 按范围切分 VALUES LESS THAN 支持 MAXVALUE 按离散值匹配 VALUES IN (列表) 无兜底机制 取模均匀分布 MOD(键, 分区数) 适合数值类型 内置哈希函数 默认用主键 支持字符串 核心原则：分区键选择决定性能，分区策略决定可维护性

3.7 分区表操作实战

光会建分区还不够，日常维护才是重头戏。我整理了几个最常用的操作：

查看分区信息：

-- 查看表的分区定义
SHOW CREATE TABLE orders\G

-- 查看每个分区的行数
SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'orders';

新增分区：

-- RANGE分区新增
ALTER TABLE orders ADD PARTITION (
    PARTITION p_2024 VALUES LESS THAN (2025)
);

-- HASH/KEY分区增加分区数
ALTER TABLE user_sessions ADD PARTITION PARTITIONS 4;

注意：HASH和KEY分区增加分区数时，MySQL会重建所有分区数据。如果表很大，这个操作会锁表很久。我建议在业务低峰期操作，或者用pt-online-schema-change这类工具。

删除分区：

-- 删除整个分区（数据一并删除）
ALTER TABLE orders DROP PARTITION p_2021;

-- 只清空分区数据（保留分区结构）
ALTER TABLE orders TRUNCATE PARTITION p_2021;

合并分区（RANGE分区重组）：

-- 把p_2021和p_2022合并成一个新分区
ALTER TABLE orders REORGANIZE PARTITION p_2021, p_2022 INTO (
    PARTITION p_2021_2022 VALUES LESS THAN (2023)
);

3.8 避坑指南

最后分享几个我踩过的坑，你遇到了能少走弯路：

  • 分区键必须包含在主键或唯一键中。MySQL要求所有唯一索引（包括主键）都必须包含分区键。这是硬性规定，绕不过去。
  • 分区数不是越多越好。分区太多会导致MySQL打开大量文件句柄，反而降低性能。我一般建议单表分区数不超过1024，实际生产环境控制在64~256之间比较合理。
  • NULL值处理要小心。RANGE分区中，NULL值会被放到最小的分区。LIST分区中，除非你显式定义了VALUES IN (NULL)，否则插入NULL会报错。
  • 分区裁剪不是万能的。如果查询条件里对分区键做了函数运算（比如YEAR(order_date)），MySQL可能无法进行分区裁剪。尽量让查询条件直接匹配分区键的原始值。
← 上一章 📖 返回目录 下一章 →