MySQL 5.1+ 引入原生分區，單表突破文件系統/磁碟限制，實現資料生命週期管理、查詢剪枝、並行聚合。本文系統講解 RANGE/LIST/HASH/KEY/COLUMNS 五大分區類型、DDL 運維操作、效能優化關鍵與生產避坑指南。

分區核心優勢

• 突破單檔大小限制：分區文件分散存儲，單表可達 PB 級
• 秒級歷史資料清理：DROP PARTITION 瞬間完成，無 DELETE 開銷
• 分區剪枝：查詢僅掃描相關分區，IO 大幅降低
• 並行聚合：SUM()/COUNT() 可跨分區並行（需 partition_pruning + parallel_query 支援）
• I/O 隔離：熱分區放 SSD，冷分區放 HDD

分區前置約束（必讀）

1. 分區鍵必須包含在主鍵/唯一鍵中：PRIMARY KEY (id, partition_key) 或 UNIQUE KEY (partition_key, ...)
2. 分區鍵不可為 NULL：NOT NULL 強制，否則無法確定分區歸屬
3. 分區表不支援外鍵
4. 所有分區使用同一引擎
5. 最大 8192 個分區（5.7+），建議 < 1000

五大分區類型詳解

1. RANGE 分區：連續範圍（最常用）

CREATE TABLE sales (
  id INT NOT NULL,
  amount DECIMAL(10,2),
  sale_date DATE NOT NULL
) 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 範圍、自然有序業務。注意：VALUES LESS THAN 為開區間，MAXVALUE 兜底。

2. LIST 分區：離散值列舉

CREATE TABLE users (
  id INT NOT NULL,
  region_code INT NOT NULL
) PARTITION BY LIST (region_code) (
  PARTITION p_north VALUES IN (1,3,5,7,9),
  PARTITION p_south VALUES IN (2,4,6,8,0)
);

適用：枚舉型業務鍵（地區、狀態、租戶 ID）。缺點：新值需手動 REORGANIZE PARTITION 或 ADD PARTITION。

3. HASH 分區：均勻分布

CREATE TABLE logs (
  id INT NOT NULL,
  created_at DATETIME NOT NULL
) PARTITION BY HASH (YEAR(created_at))
PARTITIONS 4;

適用：無明顯範圍/枚舉特徵，需均勻分散寫入熱點。缺點：無法範圍剪枝，DROP PARTITION 不支援（需 COALESCE PARTITION 合併）。

4. KEY 分區：MySQL 內建哈希

CREATE TABLE events (
  id INT NOT NULL,
  event_time DATETIME NOT NULL
) PARTITION BY KEY (event_time)
PARTITIONS 4;

與 HASH 差異：哈希函數由 MySQL 內建（MD5/內部演算法），支援非整數列（字符串、日期），NDB Cluster 專用。

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

-- RANGE COLUMNS：直接用 DATE 類型，無需 YEAR() 函數
CREATE TABLE orders (
  id INT NOT NULL,
  order_date DATE NOT NULL
) PARTITION BY RANGE COLUMNS (order_date) (
  PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
  PARTITION p202302 VALUES LESS THAN ('2023-03-01')
);

-- LIST COLUMNS：直接用字符串
CREATE TABLE products (
  id INT NOT NULL,
  category VARCHAR(20) NOT NULL
) PARTITION BY LIST COLUMNS (category) (
  PARTITION p_electronics VALUES IN ('phone','laptop','tablet'),
  PARTITION p_clothing VALUES IN ('shirt','pants','shoes')
);

支援類型：所有整數、DATE/DATETIME、CHAR/VARCHAR/BINARY/VARBINARY。不支援：FLOAT/DECIMAL、BLOB/TEXT、其他時間類型。

驗證分區支援

mysql> SHOW VARIABLES LIKE '%partition%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_partition_engine | YES   |
+-----------------------+-------+

分區運維 DDL 操作

新增分區

ALTER TABLE sale_data ADD PARTITION (PARTITION p202310 VALUES LESS THAN (202311));

刪除分區（資料同步刪除，不可恢復）

ALTER TABLE sale_data DROP PARTITION p202301;

分區合併/拆分：REORGANIZE PARTITION

-- 將 p202301-p202309 合併為季度分區
ALTER TABLE sale_data
REORGANIZE PARTITION p202301,p202302,p202303,
  p202304,p202305,p202306,
  p202307,p202308,p202309 INTO (
  PARTITION p2023Q1 VALUES LESS THAN (202304),
  PARTITION p2023Q2 VALUES LESS THAN (202307),
  PARTITION p2023Q3 VALUES LESS THAN (202310)
);

✅ 資料不丟失，MySQL 內部重組數據文件。

檢查分區資訊

SHOW CREATE TABLE sale_data\G
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME='sale_data';\G

效能優化關鍵

1. 查詢必須帶分區鍵條件：否則全分區掃描，效能災難
2. 索引包含分區鍵：INDEX idx_partition_col (partition_key, other_cols)
3. 避免跨分區 JOIN/排序：強制串行、臨時表、失去並行優勢
4. 分區數適度：太多 → 元資料開銷大、打開表慢；太少 → 剪枝粒度粗
5. 統計資訊收集：ANALYZE TABLE 需逐分區執行，或 ANALYZE TABLE tbl PARTITION(p1,p2)

生產避坑指南

坑點	後果	規避
分區鍵為 NULL	插入報錯 / 全落入默認分區	NOT NULL + 業務預設值
主鍵未含分區鍵	建表失敗	PK 包含分區鍵
查詢無分區條件	全表掃描	應用層強制帶分區鍵
大量小分區	打開表緩存壓力、FD 耗盡	合併冷分區、調大 table_open_cache
備份工具不支援	mysqldump 慢、還原錯誤	用 xtrabackup/物理備份、或單分區導出

總結

MySQL 分區是大表治理利器，核心價值在「秒級歸檔清理」與「查詢剪枝」。選型優先 RANGE COLUMNS（時間）/ LIST COLUMNS（枚舉），嚴守「分區鍵入主鍵、非空、帶條件查詢」三鐵律。運維自動化腳本化（定時 ADD/DROP/REORGANIZE），納入變更管理體系。