分區核心概念

• 將大表按規則拆分為多個物理分區，每分區獨立儲存（可跨磁碟）。
• 查詢時可 分區剪枝，僅掃描相關分區，大幅降低 I/O。
• 支援類型：RANGE、LIST、HASH、KEY，以及 子分區（RANGE/LIST + HASH/KEY）。

限制條件（必讀）

1. 主鍵/唯一索引必須包含分區鍵欄位。
2. 分區鍵必須為整數型別或返回整數的運算式（如 YEAR(date)、TO_DAYS(date)）。
3. 單表最多 1024 個分區（含子分區）。
4. 外鍵不支援分區表。
5. 所有分區必須使用相同儲存引擎。

四大分區類型建表範例

1. RANGE 分區（按範圍，最常用於時間序列）

CREATE TABLE users (
  uid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  email VARCHAR(30) NOT NULL,
  created DATE NOT NULL,
  PRIMARY KEY (uid, created)
)
PARTITION BY RANGE (YEAR(created)) (
  PARTITION p2020 VALUES LESS THAN (2021) DATA DIRECTORY='/data0' INDEX DIRECTORY='/idx0',
  PARTITION p2021 VALUES LESS THAN (2022) DATA DIRECTORY='/data1' INDEX DIRECTORY='/idx1',
  PARTITION p2022 VALUES LESS THAN (2023) DATA DIRECTORY='/data2' INDEX DIRECTORY='/idx2',
  PARTITION pmax VALUES LESS THAN MAXVALUE DATA DIRECTORY='/data3' INDEX DIRECTORY='/idx3'
);

• DATA DIRECTORY/INDEX DIRECTORY 可將分區分散到不同掛載點，提升 IO 吞吐。
• 必須包含 MAXVALUE 分區捕獲超出範圍資料。

2. LIST 分區（枚舉值，如地區、狀態碼）

CREATE TABLE category (
  cid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  region_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (cid, region_id)
)
PARTITION BY LIST (region_id) (
  PARTITION p_north VALUES IN (1,2,3) DATA DIRECTORY='/data0',
  PARTITION p_south VALUES IN (4,5,6) DATA DIRECTORY='/data1',
  PARTITION p_east  VALUES IN (7,8,9) DATA DIRECTORY='/data2',
  PARTITION p_west  VALUES IN (10,11,12) DATA DIRECTORY='/data3'
);

3. HASH 分區（均勻分布，適合無明顯範圍/枚舉的鍵）

CREATE TABLE logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  msg TEXT,
  created DATETIME NOT NULL,
  PRIMARY KEY (id, created)
)
PARTITION BY HASH (MONTH(created))
PARTITIONS 12;
-- 或指定每個分區目錄
PARTITION BY HASH (MONTH(created)) (
  PARTITION p0 DATA DIRECTORY='/data0', PARTITION p1 DATA DIRECTORY='/data1', ...
);

4. KEY 分區（類似 HASH，但由 MySQL 內部哈希函數計算，支援非整數列）

CREATE TABLE users (
  uid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(30),
  email VARCHAR(30),
  PRIMARY KEY (uid)
)
PARTITION BY KEY (uid) PARTITIONS 4;

5. 子分區

CREATE TABLE sales (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  shop_id INT UNSIGNED NOT NULL,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, shop_id, sale_date)
)
PARTITION BY RANGE (YEAR(sale_date))
SUBPARTITION BY HASH (shop_id) SUBPARTITIONS 4 (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

• 先按年份 RANGE，再按店鋪 HASH 子分區，雙重剪枝。

分區管理操作

操作	SQL	備註
新增 RANGE 分區	ALTER TABLE t ADD PARTITION (PARTITION p2023 VALUES LESS THAN (2024));	必須在 MAXVALUE 之前
刪除分區（含資料）	ALTER TABLE t DROP PARTITION p2020;	資料永久刪除，極快（元資料操作）
合併/重組分區	ALTER TABLE t REORGANIZE PARTITION p2021,p2022 INTO (PARTITION p2022 VALUES LESS THAN (2023));	RANGE/LIST 適用
增加 HASH/KEY 分區數	ALTER TABLE t ADD PARTITION PARTITIONS 8;	重新分佈資料，耗時較久
減少 HASH/KEY 分區數	ALTER TABLE t COALESCE PARTITION 2;	合併分區
截斷分區	ALTER TABLE t TRUNCATE PARTITION p2020;	5.6+，清空分區資料保留結構
交換分區（與非分區表交換）	ALTER TABLE t EXCHANGE PARTITION p2020 WITH TABLE t_staging;	極快導入/導出，無資料拷貝

效能實測對比（800 萬行測試）

查詢	分區表	非分區表	提升
單年份範圍 COUNT(*)	0.55s	4.69s	90% ↓
三年份範圍 COUNT(*)	2.63s	5.42s	51% ↓
無索引欄位過濾	0.75s	11.52s	93% ↓

結論：無索引查詢受益最大，有索引時差距縮小（索引已縮小掃描範圍）。

給現有表加分區

ALTER TABLE results
PARTITION BY RANGE (MONTH(ttime)) (
  PARTITION p0 VALUES LESS THAN (1), ..., PARTITION p11 VALUES LESS THAN (12),
  PARTITION pmax VALUES LESS THAN (13)
);

若主鍵不含分區鍵，需先調整主鍵：

ALTER TABLE results DROP PRIMARY KEY;
ALTER TABLE results ADD PRIMARY KEY (id, ttime);
-- 再執行 PARTITION BY

調優建議

1. 業務查詢模式決定分區鍵：時間範圍查詢 → RANGE(YEAR/MONTH)；租戶隔離 → LIST/HASH(tenant_id)。
2. 分區數適中：百萬行以上考慮分區，分區數通常 10~100，避免過多元資料開銷。
3. 配合 DATA DIRECTORY 分散磁碟，NVMe + HDD 分層存儲熱/冷資料。
4. 定期 ANALYZE TABLE 更新分區統計資訊。
5. 監控 INFORMATION_SCHEMA.PARTITIONS 中 TABLE_ROWS、DATA_LENGTH 均衡度。