MySQL 分區表完整指南：類型、語法、限制與實戰判斷

MySQL 分區表完整指南

面對單表億級資料、查詢緩慢、維護困難等痛點，MySQL 分區表是「不拆分業務、不遷移架構」的低成本破局關鍵。本文系統梳理分區表核心概念、六大分區類型語法、限制條件、支援檢測與實戰選型建議。

一、什麼是分區表？

分區表：依據規則將一張邏輯大表拆分為多個物理分區。邏輯上是一張表，物理上是多個分區檔案。每個分區可獨立存放於不同磁碟/檔案系統，實現 I/O 分散與並行處理。

二、分區表 vs 分表

三、分區表五大核心優勢

1. 資料分散：分區可掛載不同磁碟/掛載點，突破單磁碟 I/O 瓶頸。
2. 突破檔案系統限制：單表大小受限於檔案系統（如 ext4 16TB），分區後總容量 = 分區數 × 單分區上限。
3. 查詢剪枝：WHERE 條件含分區鍵時，僅掃描相關分區（Partition Pruning），大幅降低掃描量。
4. 維護高效：歷史資料歸檔僅需 DROP PARTITION，避免大量 DELETE 產生碎片與鎖爭用。
5. 規避熱點鎖：分散 InnoDB 索引鎖、ext4 inode 鎖等單點爭用。

四、分區表硬性限制（必讀）

1. 分區數上限：1024 個（含子分區）。
2. 分區運算式型別：
   • 5.1：必須為整數或回傳整數的運算式。
   • 5.5+：支援 COLUMNS 分區，直接支援整數、日期時間、字串型別（不含 BLOB/TEXT）。
3. 主鍵/唯一索引包含規則：若分區鍵包含 PK/UK 欄位，則所有 PK/UK 欄位必須全包含在分區鍵中。
4. 不支援外鍵：分區表無法定義 Foreign Key。
5. 全表全索引分區：資料與索引必須同步分區，不可只分區資料或只分區索引。

五、檢測 MySQL 是否支援分區

SHOW VARIABLES LIKE '%partition%';
-- 回傳 have_partitioning = YES 即支援

六、MySQL 六大分區類型詳解

1. RANGE 分區：連續區間

PARTITION BY RANGE (expr) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p2 VALUES LESS THAN MAXVALUE
)

• expr 為欄位或運算式（如 YEAR(created_at)）。
• 區間必須連續、遞增、不重疊，順序顛倒會報錯 VALUES LESS THAN value must be strictly increasing。
• MAXVALUE 兜底分區，捕獲超出範圍資料。
• 典型場景：按時間（年/月/日）、ID 範圍分區。

2. LIST 分區：離散值列表

PARTITION BY LIST (expr) (
    PARTITION p0 VALUES IN (1, 3, 5),
    PARTITION p1 VALUES IN (2, 4, 6)
)

• 值不需連續、不需排序。
• 無 MAXVALUE 兜底，插入未列舉值會直接報錯。
• 典型場景：按地區代碼、業務類型枚舉值分區。

3. COLUMNS 分區（5.5+）：原生非整數支援

• RANGE COLUMNS：支援整數、日期時間、字串直接分區，無需 YEAR() 等函數轉換。
• LIST COLUMNS：同理支援字串/日期離散值。
• 支援多欄位分區：PARTITION BY RANGE COLUMNS (a, b, c)。

4. HASH 分區：均勻分散熱點

-- 一般 HASH（取模）
PARTITION BY HASH (store_id) PARTITIONS 4;

-- 線性 HASH（Linear Hash，2 的冪次運算，維護效能更好）
PARTITION BY LINEAR HASH (store_id) PARTITIONS 4;

5. KEY 分區：HASH 變體，支援非整數

PARTITION BY KEY (col1, col2) PARTITIONS 4;
-- 不指定欄位時：有 PK 用 PK，無 PK 用第一個 NOT NULL UNIQUE KEY

• 不允許自訂運算式，僅接受欄位列表。
• 支援除 BLOB/TEXT 外所有型別。
• 內部使用 MySQL 內建雜湊函數。

6. 子分區：複合分區

在一級分區（如 RANGE）下再建立二級分區（如 HASH/KEY）：

PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH (user_id) SUBPARTITIONS 4 (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
)

七、分區類型選型決策樹

1. 業務是否有明確時間/範圍特徵？→ RANGE / RANGE COLUMNS
2. 業務是否為離散枚舉（地區、類型）？→ LIST / LIST COLUMNS
3. 需均勻分散寫入熱點、無明確範圍？→ HASH / KEY
4. 分區鍵含非整數（字串、日期）且版本 ≥ 5.5？→ COLUMNS
5. 需二級分散（如時間 + 用戶雜湊）？→ 子分區

八、常用管理操作速查

九、實戰避坑指南

1. 分區鍵選擇：必須是高頻查詢條件欄位，否則無法剪枝，反而增加優化器開銷。
2. 避免 NULL 值：RANGE/LIST 中 NULL 會被視為最小值，易導致資料傾斜。
3. 統計資訊更新：大量資料變更後執行 ANALYZE TABLE，確保優化器準確剪枝。
4. 備份策略：mysqldump 預設備份全表，大表建議分區級備份或使用 xtrabackup 物理備份。
5. 升級風險：5.1 → 5.5+ 升級時，舊版 RANGE/LIST 整數限制解除，但現有分區定義不變，新增分區可用 COLUMNS 語法。

分區表是 MySQL 單機擴展的利器，用對場景（時間序列、熱點分散、歷史歸檔）能以極低成本解決億級單表效能難題。關鍵在於：分區鍵 = 核心查詢條件，且嚴守限制條件，配合自動化分區管理腳本（如定時新增/歸檔分區），才能長期穩定收益。