MySQL 表分區完整指南：RANGE/LIST/HASH/KEY 四大類型與子分區實戰

為什麼需要分區？

• 單表突破檔案系統/硬體大小限制（單表可達 PB 級）。
• 歷史資料快速歸檔/刪除（DROP PARTITION 秒級，避免 DELETE 鎖表）。
• 查詢分區裁剪：WHERE 條件命中分區鍵時，僅掃描相關分區。
• 聚合查詢並行化：各分區獨立計算 SUM/COUNT，最終合併。
• 跨磁碟分佈 I/O（掛載不同磁碟/RAID 的分區目錄）。

檢查分區支援

SHOW VARIABLES LIKE '%partition%';
-- have_partitioning = YES 表示支援

四大分區類型詳細解析

1. RANGE 分區 — 連續區間（最常用）

CREATE TABLE sales (
    id INT NOT NULL AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)  -- 分區鍵必須包含在 PK/UK 中
)
PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax VALUES LESS THAN MAXVALUE  -- 兜底，必須有
);

• 適用：時間序列、自增 ID 範圍。
• DROP PARTITION p2020 秒級刪除 2020 年資料。
• 查詢 WHERE sale_date >= '2021-01-01' 自動裁剪至 p2021+。

2. LIST 分區 — 離散值集合

CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    region_id TINYINT NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, region_id)
)
PARTITION BY LIST (region_id) (
    PARTITION p_north VALUES IN (1,2,3),
    PARTITION p_south VALUES IN (4,5,6),
    PARTITION p_east  VALUES IN (7,8),
    PARTITION p_west  VALUES IN (9,10)
    -- LIST 無 MAXVALUE，所有值必須在 IN 列表中
);

• 適用：固定枚舉值（地區、類型、狀態）。
• 插入不在列表的值會報錯。

3. HASH 分區 — 均勻分佈

CREATE TABLE logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    msg TEXT,
    PRIMARY KEY (id, user_id)
)
PARTITION BY HASH (user_id)
PARTITIONS 16;  -- 2 的冪次最佳

• MySQL 計算 MOD(user_id, 16) 決定分區。
• 適用：無明顯範圍/列表特徵，需均勻分散寫入熱點。
• LINEAR HASH：PARTITION BY LINEAR HASH(user_id)，採用 2 的冪算法，增刪分區更快，適合超大表（TB+）。

4. KEY 分區 — 內建哈希函數

CREATE TABLE sessions (
    id BIGINT NOT NULL AUTO_INCREMENT,
    session_data BLOB,
    PRIMARY KEY (id)
)
PARTITION BY KEY (id)
PARTITIONS 8;

• 類似 HASH，但哈希函數由 MySQL 內建（MD5/PASSWORD 衍生），支援非整數欄位。
• 同樣支援 LINEAR KEY。

子分區 — 二維分區

CREATE TABLE events (
    id BIGINT NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL,
    event_type TINYINT NOT NULL,
    data JSON,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH (MONTH(created_at))
SUBPARTITIONS 2
(
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

• 外層 RANGE 按年，內層 HASH 按月再分 2 區，總計 3×2=6 子分區。
• 物理檔案：events#P#p2022#SP#sp0.ibd、events#P#p2022#SP#sp1.ibd 等。

分區管理操作

-- 新增分區（RANGE/LIST）
ALTER TABLE sales ADD PARTITION (PARTITION p2023 VALUES LESS THAN (2024));

-- 刪除分區（資料同步刪除，不可恢復）
ALTER TABLE sales DROP PARTITION p2020;

-- 合併分區（HASH/KEY）
ALTER TABLE logs COALESCE PARTITION 4;  -- 減少 4 個分區

-- 拆分分區（RANGE/LIST）
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 將分區轉為非分區表
ALTER TABLE sales REMOVE PARTITIONING;

效能調優與避坑

實戰決策樹

資料有明顯時間/ID 範圍特徵？
├─ 是 → RANGE 分區（按月/季/年），配合 DROP PARTITION 歸檔
└─ 否 → 有固定枚舉值（地區/類型）？
    ├─ 是 → LIST 分區
    └─ 否 → 需均勻分散寫入熱點？
        ├─ 是 → HASH/KEY 分區（PARTITIONS 2^n）
        └─ 否 → 不分區，靠索引優化