4、列表分区：列表分区原理、创建列表分区表、列表分区实战案例

列表分区，说白了就是按离散值来分数据。

范围分区是按连续区间切数据，比如按时间、按ID范围。但现实世界里，很多字段的值是枚举型的——地区编码、业务类型、状态码、产品类别。这些值之间没有大小关系，你没法说「华北 > 华东」。这时候，列表分区就派上用场了。

我个人习惯把列表分区叫做「白名单分区」。你明确告诉数据库：哪些值放一起，哪些值放另一堆。数据库就老老实实按这个清单去存。

4.1 列表分区原理

列表分区的核心逻辑很简单：

• 指定一个分区列，该列的值是离散的
• 为每个分区定义一个值列表（VALUES IN）
• 数据插入时，根据分区列的值匹配所属分区
• 如果值不在任何分区列表中，插入会失败（除非有默认分区）

嗯，这里要注意：列表分区不像范围分区那样有「边界重叠」的概念。每个值只能属于一个分区，不能模棱两可。你想想看，如果「北京」同时出现在两个分区的值列表里，数据库就不知道该往哪放了——所以Oracle、MySQL这些数据库都会强制要求值列表互斥。

我在项目中遇到过最典型的场景是多租户数据隔离。每个租户有独立的租户ID，但租户数量不多（比如几十个），而且查询时总是带上租户ID条件。用列表分区按租户ID分，查询效率提升非常明显。

4.2 创建列表分区表

先看一个基础例子。假设我们有一个订单表，按订单状态做列表分区：

-- Oracle 语法
CREATE TABLE orders (
    order_id      NUMBER,
    customer_id   NUMBER,
    order_status  VARCHAR2(20),
    order_amount  NUMBER(10,2),
    create_date   DATE
)
PARTITION BY LIST (order_status) (
    PARTITION p_status_pending   VALUES ('待支付', '待审核'),
    PARTITION p_status_processing VALUES ('已支付', '已发货', '运输中'),
    PARTITION p_status_completed  VALUES ('已完成', '已签收'),
    PARTITION p_status_failed     VALUES ('已取消', '已退款', '支付失败')
);

这段代码的意思是：

• 「待支付」和「待审核」的订单，存到 p_status_pending 分区
• 「已支付」「已发货」「运输中」的订单，存到 p_status_processing 分区
• 「已完成」「已签收」的订单，存到 p_status_completed 分区
• 「已取消」「已退款」「支付失败」的订单，存到 p_status_failed 分区

MySQL的语法稍微有点不同，但思路一样：

-- MySQL 语法
CREATE TABLE orders (
    order_id      INT,
    customer_id   INT,
    order_status  VARCHAR(20),
    order_amount  DECIMAL(10,2),
    create_date   DATE
)
PARTITION BY LIST (order_status) (
    PARTITION p_status_pending   VALUES IN ('待支付', '待审核'),
    PARTITION p_status_processing VALUES IN ('已支付', '已发货', '运输中'),
    PARTITION p_status_completed  VALUES IN ('已完成', '已签收'),
    PARTITION p_status_failed     VALUES IN ('已取消', '已退款', '支付失败')
);

4.3 列表分区实战案例

好了，理论说完了。咱们直接上一个我在数据采集系统里做过的真实案例。

4.3.1 业务背景

我们当时要采集全国多个渠道的设备运行日志。日志按数据来源区域分为：华北、华东、华南、西南、西北、东北。每个区域的数据量差不多，但查询时几乎总是按区域过滤——业务人员只看自己负责的区域。

如果不分区，一张大表几亿条记录，每次查询都要全表扫描。分区后，查询只扫描对应分区，性能提升是数量级的。

4.3.2 建表语句

CREATE TABLE device_logs (
    log_id          NUMBER,
    device_id       VARCHAR2(32),
    region_code     VARCHAR2(10),   -- 区域编码：华北、华东、华南、西南、西北、东北
    log_level       VARCHAR2(10),
    log_content     CLOB,
    collect_time    DATE
)
PARTITION BY LIST (region_code) (
    PARTITION p_region_north     VALUES ('华北'),
    PARTITION p_region_east      VALUES ('华东'),
    PARTITION p_region_south     VALUES ('华南'),
    PARTITION p_region_west      VALUES ('西南'),
    PARTITION p_region_northwest VALUES ('西北'),
    PARTITION p_region_northeast VALUES ('东北')
);

4.3.3 分区后的查询效果

看一个实际查询：

-- 查询华北区域的所有错误日志
SELECT * FROM device_logs
WHERE region_code = '华北'
  AND log_level = 'ERROR'
  AND collect_time >= SYSDATE - 7;

没分区时，这个查询要扫全表。分区后，数据库直接定位到 p_region_north 分区，只扫这个分区的数据。如果每个分区1000万条，总表6000万条，查询量直接降到原来的1/6。

4.3.4 分区维护操作

实际运维中，分区不是建完就不管了。我整理了几个常用操作：

操作	SQL示例	说明
添加分区	ALTER TABLE device_logs ADD PARTITION p_region_central VALUES ('华中');	新增一个区域时使用
删除分区	ALTER TABLE device_logs DROP PARTITION p_region_northeast;	删除整个分区的数据（慎用）
截断分区	ALTER TABLE device_logs TRUNCATE PARTITION p_region_northwest;	清空分区数据，保留分区结构
合并分区	ALTER TABLE device_logs MERGE PARTITIONS p_region_north, p_region_northeast INTO PARTITION p_region_north;	将两个分区合并为一个

4.3.5 分区裁剪验证

建完分区后，我建议你验证一下分区裁剪是否生效。说白了就是确认查询真的只扫了目标分区，而不是全表扫描。

-- Oracle 查看执行计划
EXPLAIN PLAN FOR
SELECT * FROM device_logs WHERE region_code = '华北';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 输出中应该能看到类似：
-- PARTITION LIST SINGLE
-- PARTITION: 1  (表示只扫描了第1个分区)

如果执行计划显示 PARTITION LIST ALL 或者 PARTITION LIST INLIST 扫了多个分区，说明你的查询条件可能没写好，或者分区键被函数包裹了。

4.4 列表分区 vs 范围分区

我经常被问到：「什么时候用列表分区，什么时候用范围分区？」。这里做个对比：

对比维度	列表分区	范围分区
分区键类型	离散值（枚举、编码、状态）	连续值（时间、数字ID）
分区定义方式	VALUES IN (值列表)	VALUES LESS THAN (边界值)
数据分布	按业务分组，可能不均匀	按区间切分，可控制均匀度
新增分区	直接ADD，不影响已有分区	需要SPLIT或重新定义边界
典型场景	区域、状态、类型、租户	时间序列、自增ID

说白了，值是可枚举的固定集合，就用列表分区；值是连续的区间，就用范围分区。两者也可以组合使用，那就是后面章节要讲的复合分区了。

4.5 避坑指南

最后，分享几个我实际踩过的坑：

1. 分区键不要用函数包裹。比如 WHERE UPPER(region_code) = '华北'，数据库无法做分区裁剪，会全表扫描。保持分区键裸写。
2. 值列表不要包含空值。Oracle中，如果插入NULL值且没有默认分区，会报错。建议单独建一个 PARTITION p_null VALUES (NULL) 来处理空值。
3. 分区数量不要太多。我曾经接手过一个系统，列表分区建了50多个，每个分区数据量只有几十万。管理起来非常痛苦，查询性能反而因为分区元数据膨胀而下降。10个以内是黄金标准。
4. 注意分区键的选择性。如果某个值的数据量特别大（比如「华东」占了总数据量的60%），这个分区会成为热点。这时候要考虑是否要再细分，或者改用复合分区。

列表分区，说白了就是「按清单分堆」。设计得好，查询效率翻倍；设计得糙，维护成本飙升。嗯，希望今天的实战案例能帮你少走弯路。