4. 列表分区（List Partitioning）：原理、适用场景与典型SQL示例

列表分区，说白了就是「按枚举值分堆」。

范围分区是按连续区间切数据，而列表分区是按离散的、固定的值来划分。比如地区、状态码、产品类别——这些字段的值就那么几种，你提前列出来，数据库就知道哪条记录该进哪个分区。

我个人习惯把列表分区叫作「白名单分区」。你给每个分区指定一组允许的值，数据来的时候对号入座，对不上就报错或者扔进默认分区。

4.1 核心原理

列表分区的逻辑其实很简单：

• 选择一个分区键（通常是枚举类型或有限取值范围的列）
• 为每个分区显式指定一组值
• 插入数据时，数据库根据分区键的值匹配对应的分区
• 如果值不在任何分区的列表中，插入会失败（除非定义了默认分区）

我记得有一次在电商系统里做订单表分区，订单状态就那么几个值：待支付、已支付、已发货、已完成、已取消。用列表分区简直完美匹配。你想想看，范围分区处理这种场景反而别扭——状态之间没有大小关系，硬套范围分区只会让查询走错分区。

4.2 适用场景

我在项目中遇到过不少适合列表分区的场景，这里列几个典型的：

场景	分区键	分区示例	说明
地理区域	省份/城市代码	华东区、华南区、华北区	按业务区域隔离数据，便于局部维护
业务状态	订单状态、审批状态	待处理、处理中、已完成	活跃数据与归档数据物理分离
数据来源	渠道编码	APP端、Web端、API端	不同渠道的数据量差异大时可独立管理
分类标签	商品类目ID	电子产品、服装、食品	按类目做分区裁剪，加速聚合查询

说白了，只要你的分区键取值是有限的、稳定的，列表分区就是最自然的选择。但要注意——如果枚举值会频繁新增或变更，列表分区的维护成本会上升，因为每次加值都要改分区定义。

4.3 典型SQL示例

下面我用 MySQL 和 PostgreSQL 两种常用数据库来演示。先看 MySQL 的写法：

-- MySQL 列表分区示例：按订单状态分区
CREATE TABLE orders (
    order_id     BIGINT PRIMARY KEY,
    customer_id  INT NOT NULL,
    status       VARCHAR(20) NOT NULL,
    amount       DECIMAL(10,2),
    created_at   DATETIME
)
PARTITION BY LIST COLUMNS(status) (
    PARTITION p_pending VALUES IN ('pending', 'unpaid'),
    PARTITION p_processing VALUES IN ('paid', 'shipped'),
    PARTITION p_completed VALUES IN ('completed', 'cancelled'),
    PARTITION p_other VALUES IN ('refunded', 'archived')
);

这里我用了 LIST COLUMNS，它支持字符串、日期等非整数类型作为分区键。如果是整数类型的枚举值，用 PARTITION BY LIST(column) 就行。

再看 PostgreSQL 的写法，语法上更灵活一些：

-- PostgreSQL 列表分区示例：按区域编码分区
CREATE TABLE sales_data (
    sale_id     SERIAL PRIMARY KEY,
    region_code CHAR(2) NOT NULL,
    product_id  INT,
    sale_date   DATE,
    amount      NUMERIC
) PARTITION BY LIST (region_code);

CREATE TABLE sales_east PARTITION OF sales_data
    FOR VALUES IN ('SH', 'ZJ', 'JS', 'SD');

CREATE TABLE sales_south PARTITION OF sales_data
    FOR VALUES IN ('GD', 'FJ', 'HN', 'GX');

CREATE TABLE sales_north PARTITION OF sales_data
    FOR VALUES IN ('BJ', 'TJ', 'HB', 'SX');

CREATE TABLE sales_default PARTITION OF sales_data
    DEFAULT;

PostgreSQL 的 DEFAULT 分区是个好东西。我曾经在一个数据接入项目里吃过亏——上游系统突然新增了一个区域编码，因为没有默认分区，插入直接报错，导致整个数据管道中断。从那以后，我建列表分区一定会加一个 DEFAULT 分区兜底。

4.4 列表分区 vs 范围分区

很多初学者会搞混这两个概念。我画了张图帮你理清思路：

简单总结一下区别：

• 列表分区：分区键的值是「属于某个集合」的关系，适合枚举值
• 范围分区：分区键的值是「落在某个区间」的关系，适合连续数值或时间
• 选择依据：看你的查询条件——如果 WHERE 里经常写 status IN ('a','b')，列表分区更合适；如果经常写 created_at BETWEEN x AND y，范围分区更合适

4.5 性能与维护要点

列表分区用得好，查询性能提升很明显。但有几个细节你得注意：

举个例子，假设你的订单表按状态做了列表分区，查询时这样写才能利用分区剪枝：

-- 能触发分区剪枝（推荐）
SELECT * FROM orders WHERE status IN ('paid', 'shipped');

-- 不能触发分区剪枝（全分区扫描）
SELECT * FROM orders WHERE amount > 1000;

我曾经帮一个团队优化过慢查询，他们的订单表有 5000 万行，按状态做了列表分区。但开发人员写查询时没带状态条件，每次都要扫全部分区。加上状态条件后，查询从 12 秒降到了 0.3 秒——分区剪枝的效果就是这么直接。

4.6 一个完整的实战案例

最后，我给你一个完整的实战案例，把上面讲的东西串起来。假设我们要设计一个多区域电商的订单表：

-- 创建列表分区表（MySQL 语法）
CREATE TABLE orders_region (
    order_id     BIGINT AUTO_INCREMENT,
    region       VARCHAR(10) NOT NULL COMMENT '区域：east/south/north/west',
    status       VARCHAR(20) NOT NULL,
    customer_id  INT,
    amount       DECIMAL(12,2),
    created_at   DATETIME,
    PRIMARY KEY (order_id, region)
)
PARTITION BY LIST COLUMNS(region) (
    PARTITION p_east VALUES IN ('east'),
    PARTITION p_south VALUES IN ('south'),
    PARTITION p_north VALUES IN ('north'),
    PARTITION p_west VALUES IN ('west')
);

-- 插入数据
INSERT INTO orders_region VALUES 
(1, 'east', 'paid', 1001, 299.00, NOW()),
(2, 'south', 'shipped', 1002, 159.00, NOW()),
(3, 'north', 'pending', 1003, 89.00, NOW());

-- 查询时带上分区键
SELECT region, COUNT(*) as order_count, SUM(amount) as total_amount
FROM orders_region
WHERE region IN ('east', 'south')
  AND created_at >= '2024-01-01'
GROUP BY region;

这个案例里，我把区域作为分区键，每个区域一个分区。查询时只要指定区域，数据库就只扫描对应的分区，数据量大的时候效果特别明显。