MySQL 表分區完整指南：RANGE、LIST、HASH、KEY 四大分區類型深度解析

什麼是表分區？

將一張邏輯大表，根據分區鍵物理拆分為多個獨立的分區（底層為多個 .ibd 文件）。SQL 層面透明，優化器自動進行分區裁剪，僅掃描相關分區。MySQL 5.1+ 支援，8.0 大幅增強。

為什麼要分區？

• 突破單表/單文件大小限制，支撐更大數據量。
• 歷史數據快速歸檔/刪除：DROP PARTITION 秒級完成，避免 DELETE 產生碎片、鎖表、產生大量 Binlog。
• 查詢分區裁剪：WHERE 條件命中分區鍵，僅掃描目標分區，IO 大幅降低。
• 聚合並行化：SUM/COUNT 可在各分區並行執行，彙總結果。
• 跨磁盤分佈 IO：不同分區可掛載不同物理卷，提升吞吐。

四大分區類型詳解

1. RANGE 分區：連續區間

基於列值屬於給定連續區間分配行。區間不可重疊，用 VALUES LESS THAN 定義。

CREATE TABLE employees (
  id INT NOT NULL,
  fname VARCHAR(30),
  hired DATE NOT NULL DEFAULT '1970-01-01',
  store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
  PARTITION p0 VALUES LESS THAN (6),
  PARTITION p1 VALUES LESS THAN (11),
  PARTITION p2 VALUES LESS THAN (16),
  PARTITION p3 VALUES LESS THAN MAXVALUE  -- 兜底分區，防插入報錯
);

• 適用：時間序列（按月/年）、自然有序業務鍵（店號、用戶ID範圍）。
• 優勢：歷史分區 DROP PARTITION p0 秒級歸檔；範圍查詢自動裁剪。
• 注意：分區鍵列建議 NOT NULL，表達式不可返回 NULL。

2. LIST 分區：離散值集合

基於列值匹配離散值列表。用 VALUES IN (...) 定義。

CREATE TABLE employees (
  id INT NOT NULL,
  store_id INT NOT NULL
)
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)
);

• 適用：枚舉型業務鍵（地區、部門、狀態碼）。
• 限制：無 MAXVALUE 兜底，插入值不在列表直接報錯。
• 優勢：按業務邏輯分組管理，整組歸檔極簡單。

3. HASH 分區：哈希均勻分佈

基於用戶表達式返回值對分區數取模。表達式需返回非負整數。

CREATE TABLE employees (
  id INT NOT NULL,
  store_id INT NOT NULL
)
PARTITION BY HASH (store_id)
PARTITIONS 4;

• 線性哈希：PARTITION BY LINEAR HASH(expr)，用 2 的冪次映射，增刪分區極快（適合超大表），但數據分佈可能不如標準哈希均勻。
• 適用：無明顯範圍/枚舉特徵，需均勻分佈熱點數據。
• 缺點：無法進行分區裁剪（除非 WHERE 條件為精確哈希值），全表掃描場景才有優勢。

4. KEY 分區：MySQL 內建哈希

類似 HASH，但哈希函數由 MySQL 內部提供，支援一列或多列，列類型不限於整數（字符串也可）。

CREATE TABLE tk (
  col1 INT NOT NULL,
  col2 CHAR(5),
  col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;

• 適用：分區鍵含非整數列、或不想自定義哈希表達式。
• NDB Cluster：預設分區數等於數據節點數。

子分區：多維度組合

RANGE + HASH/KEY、LIST + HASH/KEY 兩級分區。例如：一級按年 RANGE，二級按用戶 ID HASH，同時享受時間歸檔與熱點均勻。

分區管理常用操作

避坑指南與最佳實踐

1. 分區鍵必須出現在主鍵/唯一鍵中：PRIMARY KEY (id, store_id) 或 UNIQUE KEY (store_id, ...)。
2. 分區數建議 ≤ 1024（8.0 上限 8192），過多元數據操作變慢。
3. 所有分區使用同一存儲引擎，不可混用。
4. 外鍵不支援分區表（5.7+ 移除限制但不推薦）。
5. 檢查分區裁剪生效：EXPLAIN PARTITIONS SELECT ... 觀察 partitions 列是否為目標分區而非 ALL。
6. 大表分區遷移：建新分區表 → INSERT SELECT 分批遷移 → 重命名切換，或用 pt-online-schema-change。