MySQL 分區表完整指南：類型、創建、管理、優缺點與分表對比

MySQL 分區表完整指南：類型、創建、管理、優缺點與分表對比

系統講解 MySQL 5.1+ 分區功能：RANGE/LIST/HASH/KEY/子分區/Columns 分區創建語法、管理操作、性能優勢、與分表區別聯繫。

什麼是分區

將一張大表的數據/索引文件（.MYD/.MYI）按規則物理拆分為多個小塊（分區），查詢時僅掃描相關分區（**分區裁剪**），突破單文件大小限制，支援跨磁盤分佈。

分區類型與創建範例

1. RANGE 分區（最常用）

CREATE TABLE user (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50), sex TINYINT
) ENGINE=MyISAM
PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (3),
  PARTITION p1 VALUES LESS THAN (6),
  PARTITION p2 VALUES LESS THAN (9),
  PARTITION p3 VALUES LESS THAN (12),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

優勢： 歷史數據歸檔、按時間/ID 範圍查詢極快。

2. LIST 分區

CREATE TABLE list_part (
  id INT NOT NULL, province_id TINYINT, name VARCHAR(50), sex TINYINT
) ENGINE=InnoDB
PARTITION BY LIST (province_id) (
  PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
  PARTITION p1 VALUES IN (9,10,11,12,16,21),
  PARTITION p2 VALUES IN (13,14,15,19),
  PARTITION p3 VALUES IN (17,18,20,22,23,24)
);

限制： 分區鍵必須包含在主鍵中（若有主鍵），否則報錯 ERROR 1503。

3. HASH 分區

CREATE TABLE hash_part (
  id INT AUTO_INCREMENT PRIMARY KEY, comment VARCHAR(1000), ip VARCHAR(25)
) ENGINE=InnoDB
PARTITION BY HASH(id) PARTITIONS 3;

用途： 均勻分佈熱點數據，無明確範圍邏輯時。

4. KEY 分區

CREATE TABLE key_part (
  news_id INT, content VARCHAR(1000), u_id VARCHAR(25), create_time DATE
) ENGINE=InnoDB
PARTITION BY LINEAR HASH(YEAR(create_time)) PARTITIONS 3;

由 MySQL 內部哈希函數計算，支援非整數列。

5. 子分區（復合分區）

CREATE TABLE sub_part (
  news_id INT, content VARCHAR(1000), u_id INT, create_time DATE
) ENGINE=InnoDB
PARTITION BY RANGE(YEAR(create_time))
SUBPARTITION BY HASH(TO_DAYS(create_time)) (
  PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, s1, s2),
  PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3, s4, good),
  PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION tank0, tank1, tank3)
);

規則： 所有分區子分區數相同；同分區內子分區名唯一，跨分區可重名（5.1.50 有 bug 禁止重名）。

6. COLUMNS 分區（5.5+）

支援整數、日期、字符串直接分區，無需轉換函數。

CREATE TABLE daily_log (
  id INT, sid CHAR(36), sname CHAR(20), date DATETIME,
  PRIMARY KEY (id, date)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(date) (
  PARTITION p1 VALUES LESS THAN ('2000-01-02'),
  PARTITION p2 VALUES LESS THAN ('2000-01-03'),
  ...
  PARTITION p367 VALUES LESS THAN (MAXVALUE)
);

分區管理操作

分區優缺點總結

分表 vs 分區：核心區別與聯繫

聯繫： 可結合使用——大表先分表（按業務維度），單表再分區（按時間維度），雙重優化。

適用場景建議

• 單表 > 1 億行、或 > 10GB、或明顯熱冷數據分離 → 考慮分區。
• 查詢模式固定、高頻按時間/ID 範圍查詢 → RANGE 分區收益最大。
• 需跨磁盤、快速歸檔歷史數據 → 分區優於分表。
• 業務路由清晰、需極致寫擴展 → 分表（Sharding）優先。