MySQL 表分區詳解：RANGE、LIST、HASH、KEY 四大類型與實戰案例

MySQL 5.1+ 引入表分區，將邏輯上的一張大表按規則拆分為多個物理分區，提升可管理性、可擴展性與查詢效能。

一、為什麼要分區

• 突破單文件系統大小限制，存儲更多數據。
• 歷史數據快速歸檔/刪除：DROP PARTITION 秒級完成，避免大事務 DELETE。
• 分區裁剪：WHERE 條件命中分區鍵時，僅掃描相關分區。
• 聚合並行：SUM/COUNT 可在各分區並行執行再彙總。
• 跨磁盤分佈 I/O，提升吞吐。

二、四大分區類型

三、實戰 DDL 範例

1. RANGE 分區（按店鋪 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
)
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
);

MAXVALUE 兜底，避免插入超出範圍報錯。後續可 ALTER TABLE ... ADD PARTITION 擴展。

2. LIST 分區（按地區）

CREATE TABLE employees (...)
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 分區（按 store_id 取模 4）

CREATE TABLE employees (...)
PARTITION BY HASH(store_id)
PARTITIONS 4;

線性哈希 LINEAR HASH 採用 2 的冪算法，增刪分區更快，但分佈可能不如普通 HASH 均勻。

4. KEY 分區（類似 HASH，由 Server 哈希）

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

四、分區管理常用命令

-- 查看分區
EXPLAIN PARTITIONS SELECT * FROM employees WHERE store_id = 8;

-- 新增分區（RANGE）
ALTER TABLE employees ADD PARTITION (PARTITION p4 VALUES LESS THAN (21));

-- 刪除分區（數據隨之刪除，不可恢復）
ALTER TABLE employees DROP PARTITION p0;

-- 重組分區（合併/拆分）
ALTER TABLE employees REORGANIZE PARTITION p1,p2 INTO (PARTITION p12 VALUES LESS THAN (16));

-- 將分區升級為表（MySQL 8.0+）
ALTER TABLE employees EXCHANGE PARTITION p3 WITH TABLE employees_archive;

五、限制與避坑

• 分區鍵必須是主鍵/唯一鍵的一部分（若表有 PK/UK）。
• 外鍵不支援分區表。
• 全文索引、空間索引不支援分區表。
• 分區數上限 8192（含子分區）。
• SELECT ... PARTITION (p0,p1) 可顯式指定掃描分區。

分區不是銀彈，適合「大表、明顯訪問模式、歷史數據歸檔」場景。小表或訪問模式雜亂反而增加優化器負擔。