MySQL LIST 分區詳解：離散值映射與高效數據歸檔實戰

LIST 分區 針對離散、非連續的值集合進行分區，是處理「地區-門店」、「狀態碼」、「枚舉類型」等業務場景的利器。本文對比 RANGE 分區差異，詳細講解語法規則、多值列表定義、插入約束機制、刪除歸檔優勢及與子分區結合的進階用法。

一、 核心差異：LIST vs RANGE

二、 語法規則與定義細節

• 分區函式： PARTITION BY LIST(expr)，expr 為列或返回整數的表達式（MySQL 5.1 僅支援整數列表）。
• 分區定義： 每個分區用 PARTITION name VALUES IN (v1, v2, ...) 列出所屬值，值之間用逗號分隔。
• 無序性： 分區聲明順序不影響路由邏輯，便於業務邏輯分組。
• 完整覆蓋： 所有可能的值必須在某個分區的 VALUES IN 中出現，否則 INSERT 會報錯 ERROR 1525 (HY000): Table has no partition for value ...。無類似 MAXVALUE 的兜底分區。

三、 實戰案例：音像店地區分區

20 家店鋪分佈 4 大地區，需將同地區店鋪員工歸檔同一分區：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast  VALUES IN (1,2,10,11,19,20),
    PARTITION pWest  VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

路由示例： store_id=12 → pWest；store_id=8 → pCentral。

四、 高效業務操作：分區級歸檔與清理

場景：西區門店整體出售，需批量刪除相關員工記錄

分區級刪除（秒級元數據操作）：

ALTER TABLE employees DROP PARTITION pWest;

對比傳統 DELETE（行級掃描、鎖開銷大）：

DELETE FROM employees WHERE store_id IN (4,12,13,14,18);

優勢： DROP PARTITION 僅修改元數據，不掃描行，對億級大表同樣瞬間完成，業務無感知。

五、 插入約束與錯誤處理

嘗試插入 store_id=21（未在任何分區列表中）：

INSERT INTO employees VALUES (224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21);
-- ERROR 1525 (HY000): Table has no partition for value 21

應對策略：

1. 應用層驗證輸入值是否在分區覆蓋範圍內。
2. 預留「其他」分區：PARTITION pOther VALUES IN (99, 999, ...) 收納異常值，定期人工清洗。
3. 使用 REORGANIZE PARTITION 動態擴展值列表（MySQL 5.7+ 支援）。

六、 進階：複合子分區（Composite Partitioning）

LIST 分區可作為第一層，結合 HASH/KEY 作為第二層子分區，實現「地區分區 + 哈希均勻分佈」的雙層架構：

CREATE TABLE employees (
    ...
)
PARTITION BY LIST(store_id)
SUBPARTITION BY HASH(id)
SUBPARTITIONS 4
(
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast  VALUES IN (1,2,10,11,19,20),
    PARTITION pWest  VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

每個地區分區下再均勻哈希 4 個子分區，兼顧業務邏輯隔離與數據均勻分佈。

七、 總結

LIST 分區以離散值映射為核心，完美契合地區、狀態、業務類別等枚舉型分區需求。其分區級 DDL 操作（增刪分區）為大表歸檔、清理提供極致性能。設計時務必確保值列表完整覆蓋，並結合子分區技術應對單分區過大問題，構建靈活高效的分區架構。