MySQL 5.1 分區技術實戰：RANGE/LIST/HASH/KEY 四大類型與性能對比

分區核心價值

邏輯水平分表，僅訪問目標分區，降低掃描量。適用海量數據、歷史歸檔、並行聚合、跨盤 I/O 分散。

四大分區類型

案例：薪資表 400 萬行 RANGE 分區

CREATE TABLE salaries (
  emp_no INT NOT NULL,
  salary INT NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  PRIMARY KEY (emp_no, from_date)
) PARTITION BY RANGE (YEAR(from_date)) (
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (1995),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (2005),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

性能對比實測

1. 單表範圍查詢

SELECT COUNT(*) FROM salaries WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';

• 無分區：全表掃描 400 萬行
• 有分區：僅掃描 p2（1995-2000），行數銳減，耗時大幅下降

2. 錯誤寫法導致分區失效

SELECT COUNT(*) FROM salaries WHERE YEAR(from_date)=1999;  -- 無法命中分區

EXPLAIN PARTITIONS 顯示掃描所有分區。改寫為範圍條件即可命中。

3. 連接查詢

同條件下分區表連接查詢約快 3 倍，數據量越大優勢越明顯。

4. 歷史數據秒級清理

ALTER TABLE salaries DROP PARTITION p0;  -- 0.05s vs DELETE 2.82s

刪除分區即丟棄文件，極速。後續插入 1998 年數據自動路由回 p0（或新建分區）。

設計關鍵考量

1. 分區列選擇：高頻 WHERE/JOIN 列
2. 分區函數：非整數列需轉換函數（YEAR()、TO_DAYS()）
3. 服務器 CPU/記憶體：分區元數據佔用內存
4. 索引大小 > RAM 時考慮分區替代索引
5. 避開主鍵做分區鍵（主鍵唯一性檢查跨分區昂貴）
6. 高 CPU 場景可搭配 Archive 引擎
7. 大量歷史數據：Archive + PARTITION

總結

• MySQL 分區 = 邏輯水平分表
• 分區裁剪 只訪問目標分區
• 支援 RANGE/LIST/HASH/KEY/復合分區
• 兼容所有存儲引擎
• 除 KEY 外，分區鍵必須為整數或可轉整數