MySQL 分區表完整指南：RANGE/LIST/HASH/KEY/COLUMNS 五大類型、管理操作與最佳實踐

單表超過 1000 萬行性能急劇下降，分區表將邏輯大表物理拆分為多個分區，支援分區裁剪、並行聚合、歷史數據秒級歸檔。本文系統講解五大分區類型語法、分區鍵選擇原則、新增/刪除/合併分區管理操作、主鍵與 NULL 限制、適用場景與避坑指南。

一、分區表核心優勢

• 突破單文件系統大小限制，支援更大數據量。
• DROP PARTITION 秒級歸檔歷史數據，無 DELETE 開銷。
• 分區裁剪：查詢僅掃描相關分區，大幅減少 I/O。
• 聚合函數（SUM/COUNT）可並行執行於各分區。
• I/O 吞吐分散到多磁盤/分區，緩解熱點。

二、五大分區類型語法詳解

1. RANGE 分區：連續區間（最常用，按時間/ID）

CREATE TABLE t_range (
  id INT, money INT UNSIGNED NOT NULL, dt DATETIME
) PARTITION BY RANGE (YEAR(dt)) (
  PARTITION p2007 VALUES LESS THAN (2008),
  PARTITION p2008 VALUES LESS THAN (2009),
  PARTITION p2009 VALUES LESS THAN (2010),
  PARTITION p2010 VALUES LESS THAN MAXVALUE
);

2. LIST 分區：離散值集合

CREATE TABLE t_list (
  a INT, b INT
) PARTITION BY LIST (b) (
  PARTITION p0 VALUES IN (1,3,5,7,9),
  PARTITION p1 VALUES IN (2,4,6,8,0)
);

3. HASH 分區：均勻分布（指定分區數）

CREATE TABLE t_hash (
  a INT, b DATETIME
) PARTITION BY HASH (YEAR(b)) PARTITIONS 4;

4. KEY 分區：MySQL 內部哈希函數（支援非整數列）

CREATE TABLE t_key (
  a INT, b DATETIME
) PARTITION BY KEY (b) PARTITIONS 4;

5. COLUMNS 分區（5.5+）：直接支援日期/字符串列，無需函數轉換

-- RANGE COLUMNS
CREATE TABLE t_rc (dt DATE, name VARCHAR(20))
PARTITION BY RANGE COLUMNS (dt) (
  PARTITION p0 VALUES LESS THAN ('2020-01-01'),
  PARTITION p1 VALUES LESS THAN ('2021-01-01')
);

-- LIST COLUMNS
CREATE TABLE t_lc (city VARCHAR(20))
PARTITION BY LIST COLUMNS (city) (
  PARTITION p_bj VALUES IN ('北京'),
  PARTITION p_sh VALUES IN ('上海')
);

支援類型：整數、DATE/DATETIME、CHAR/VARCHAR/BINARY/VARBINARY；不支援 FLOAT/DECIMAL/BLOB/TEXT。

三、分區管理操作

新增分區

ALTER TABLE sale_data ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

刪除分區（連同數據一併刪除）

ALTER TABLE sale_data DROP PARTITION p201010;

合併/重組分區

ALTER TABLE sale_data
REORGANIZE PARTITION p201001,p201002,p201003,p201004,p201005,p201006,p201007,p201008,p201009
INTO (
  PARTITION p2010Q1 VALUES LESS THAN (201004),
  PARTITION p2010Q2 VALUES LESS THAN (201007),
  PARTITION p2010Q3 VALUES LESS THAN (201010)
);

四、關鍵限制與避坑指南

五、選型建議

• 時間序列數據（日志、訂單、監控）：RANGE COLUMNS (dt) 按月/日分區，配合 DROP PARTITION 歸檔。
• 業務枚舉值固定（地區、狀態、租戶）：LIST COLUMNS。
• 均勻分散、無明顯範圍特徵：KEY 或 HASH。
• 避免過度分區：分區數過多元數據字典開銷大，查詢優化器分區裁剪耗時增長。