前言

MySQL 5.1 引入表分區，提供 RANGE、LIST、HASH、KEY 四大類型。理解各類型底層邏輯、語法細節、適用邊界，是設計高性能大表架構的前提。本文深度解析四類分區機制，附帶完整建表示例、分區裁剪驗證、性能對比與選型指南。

一、RANGE 分區：連續區間映射

核心機制

根據分區表達式值落入的連續區間分配分區。區間定義使用 VALUES LESS THAN，**必須按遞增順序定義**，最後需 MAXVALUE 兜底。

語法示例：按店鋪 ID 範圍分區

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 NOT NULL,
  `store_id` INT NOT NULL
) ENGINE=InnoDB
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
);
-- store_id 1-5 → p0, 6-10 → p1, 11-15 → p2, 16+ → p3

進階：基於日期表達式分區

-- 按離職年份分區
PARTITION BY RANGE (YEAR(`separated`)) (
  PARTITION p0 VALUES LESS THAN (1991),
  PARTITION p1 VALUES LESS THAN (1996),
  PARTITION p2 VALUES LESS THAN (2001),
  PARTITION p3 VALUES LESS THAN MAXVALUE
)

RANGE 分區優勢場景

1. 歷史數據快速歸檔：ALTER TABLE t DROP PARTITION p0 秒級刪除舊數據
2. 時間維度查詢裁剪：WHERE YEAR(separated)=2000 僅掃描 p2
3. 熱數據/冷數據分離存儲：不同分區掛載不同存儲介質 (HDD/SSD) — 需配合 DATA DIRECTORY/INDEX DIRECTORY

二、LIST 分區：離散值集合映射

核心機制

根據分區表達式值是否屬於離散值集合分配分區。使用 VALUES IN 定義，**無 MAXVALUE 兜底**，所有可能值必須顯式列出。

語法示例：按地區分區

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

關鍵限制

• 插入值不在任何分區列表中 → 報錯 ERROR 1525，不會落入默認分區
• 不支援表達式，僅支援整數列或返回整數的函數
• 適合枚舉值固定、業務邊界清晰的場景 (如省份、業務線、租戶分組)

三、HASH 分區：哈希均勻分發

核心機制

通過用戶定義表達式計算哈希值，對分區數取模決定分區。目標是**數據均勻分散**，消除熱點。語法：PARTITION BY HASH(expr) PARTITIONS N。

語法示例

CREATE TABLE `employees` (
  `id` INT NOT NULL,
  `store_id` INT NOT NULL
) ENGINE=InnoDB
PARTITION BY HASH(`store_id`)
PARTITIONS 4;
-- store_id=7 → 7%4=3 → p3

LINEAR HASH (線性哈希)

使用 2 的冪次方算法，而非取模。優點：增加/刪除/合並/拆分分區極快 (只需移動少量數據)，適合 TB 級超大表。缺點：分區數據分布不如標準 HASH 均勻。

PARTITION BY LINEAR HASH(YEAR(`hired`)) PARTITIONS 6;

HASH 分區適用場景

• 無明確業務分區邊界，需將寫入壓力均勻分散到多個物理文件
• 主鍵為 UUID/雪花 ID，範圍查詢少，點查為主
• 配合 PARTITIONS N 控制並行度

四、KEY 分區：MySQL 內置哈希函數

核心機制

類似 HASH，但**不允許自定義表達式**，僅接受**列名列表** (一或多列)，由 MySQL 內部哈希函數 (基於 PASSWORD() 演算法) 計算。支援非整數列 (字符串、二進制)，內部自動轉換為整數哈希。

語法示例

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

-- 多列 KEY 分區
PARTITION BY KEY(`col1`, `col2`) PARTITIONS 4;

LINEAR KEY

同 LINEAR HASH，使用 2 的冪次方算法：

PARTITION BY LINEAR KEY(`col1`) PARTITIONS 3;

五、四大類型綜合對比

維度	RANGE	LIST	HASH	KEY
分區邏輯	業務範圍 (時間、ID段)	業務枚舉 (地區、類型)	哈希均勻分發	內置哈希均勻分發
表達式	函數/列 (需返回整數)	列 (整數)	任意返回整數表達式	僅列名 (支援字符串/日期)
定義方式	VALUES LESS THAN 遞增	VALUES IN 列表	PARTITIONS N	PARTITIONS N
兜底分區	MAXVALUE 必須	無 (值必須全覆蓋)	自動均勻	自動均勻
裁剪效率	高 (範圍/等值)	高 (等值/IN)	低 (僅等值命中單分區)	低 (僅等值命中單分區)
維護靈活性	REORGANIZE 較重	ADD/DROP 輕量	LINEAR HASH 秒級增減	LINEAR KEY 秒級增減
典型場景	日志、訂單、監控、賬單	多租戶、地區、狀態機	寫入熱點分散、會話表	無明確邊界、混合類型列

六、子分區 (Composite Partitioning)

一級分區 + 二級子分區，如 RANGE + HASH、LIST + KEY：

CREATE TABLE `sales` (
  `id` INT, `dt` DATE, `region` INT
) PARTITION BY RANGE (YEAR(`dt`))
SUBPARTITION BY HASH(`region`) SUBPARTITIONS 4 (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 總分區數 = 3 * 4 = 12

適用：時間範圍裁剪 + 區域內並行掃描。

七、性能測試參考 (1000 萬行，SSD，InnoDB)

操作	RANGE (按月)	HASH (16分區)	KEY (16分區)	無分區
按月範圍查詢	12 ms (裁剪1分區)	1,200 ms (全掃)	1,180 ms (全掃)	1,350 ms
點查 (主鍵)	0.8 ms	0.9 ms	0.9 ms	0.7 ms
批量插入 10萬	3.2 s	2.8 s	2.9 s	3.5 s
刪除歷史月份	0.02 s (DROP PARTITION)	N/A	N/A	45 s (DELETE)

八、選型指南

是否有明確業務邊界 (時間/地區/狀態)？
├─ 是 → 邊界為連續範圍？ → RANGE (優先 RANGE COLUMNS)
│       邊界為離散枚舉？ → LIST
└─ 否 → 需消除寫入熱點？
        ├─ 是 → 列類型多樣/含字符串？ → KEY / LINEAR KEY
        └─ 否 → 僅整數列？ → HASH / LINEAR HASH

九、總結

四大分區類型各有千秋：**RANGE 解決時間維度生命週期管理，LIST 解決業務枚舉隔離，HASH/KEY 解決寫入熱點均勻化**。生產環境建議優先使用 RANGE COLUMNS 或 LIST COLUMNS (原生類型支援、裁剪高效)，結合子分區實現多維度查詢加速。分區不是越多越好，建議單表分區數 < 100，總分區數 < 1000，避免元數據開銷過大。