什麼是子分區？

**一級分區**再切分為**二級分區**，形成二維分區矩陣。語法：
PARTITION BY RANGE/LIST (...) SUBPARTITION BY HASH/KEY (...) SUBPARTITIONS N

基礎範例：RANGE + HASH 兩級 3×2 = 6 分區

CREATE TABLE ts (
  id INT, purchased DATE
)
PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (TO_DAYS(purchased))
SUBPARTITIONS 2
(
  PARTITION p0 VALUES LESS THAN (1990),
  PARTITION p1 VALUES LESS THAN (2000),
  PARTITION p2 VALUES LESS THAN MAXVALUE
);

p0、p1、p2 各自再按天數哈希分 2 子分區，邏輯上 6 片，物理上可分佈 6 塊磁碟。

顯式命名子分區（完全控制）

CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (TO_DAYS(purchased))
(
  PARTITION p0 VALUES LESS THAN (1990)
    (SUBPARTITION s0, SUBPARTITION s1),
  PARTITION p1 VALUES LESS THAN (2000)
    (SUBPARTITION s2, SUBPARTITION s3),
  PARTITION p2 VALUES LESS THAN MAXVALUE
    (SUBPARTITION s4, SUBPARTITION s5)
);

語法鐵律（違反即報錯）

1. **每個一級分區子分區數量必須相同**。
2. **若任一一級分區用 SUBPARTITION 顯式命名，則所有一級分區必須全部顯式命名所有子分區**（不能混用隱式/顯式）。
3. 子分區名在**同一一級分區內唯一**即可，跨一級分區可重名（如 p0.s0、p1.s0 合法）。

多磁碟分佈實戰：6 塊盤、熱/溫/冷數據分層

CREATE TABLE ts (id INT, purchased DATE)
PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (TO_DAYS(purchased))
(
  PARTITION p0 VALUES LESS THAN (1990)  -- 冷數據量大，拆 4 盤
    (SUBPARTITION s0a DATA DIRECTORY='/disk0/data' INDEX DIRECTORY='/disk0/idx',
     SUBPARTITION s0b DATA DIRECTORY='/disk1/data' INDEX DIRECTORY='/disk1/idx',
     SUBPARTITION s0c DATA DIRECTORY='/disk2/data' INDEX DIRECTORY='/disk2/idx',
     SUBPARTITION s0d DATA DIRECTORY='/disk3/data' INDEX DIRECTORY='/disk3/idx'),
  PARTITION p1 VALUES LESS THAN (2000)  -- 溫數據，拆 2 盤
    (SUBPARTITION s1a DATA DIRECTORY='/disk4/data' INDEX DIRECTORY='/disk4/idx',
     SUBPARTITION s1b DATA DIRECTORY='/disk5/data' INDEX DIRECTORY='/disk5/idx'),
  PARTITION p2 VALUES LESS THAN MAXVALUE  -- 熱數據，暫留預設目錄
    (SUBPARTITION s2a, SUBPARTITION s2b)
);

未來擴容：REORGANIZE PARTITION

熱分區 p2 滿載時：
ALTER TABLE ts REORGANIZE PARTITION p2 INTO (PARTITION p2 VALUES LESS THAN (2010) ..., PARTITION p3 VALUES LESS THAN MAXVALUE ...);
將熱數據切出新分區並掛載新磁碟，線上操作（含鎖，建議低峰執行）。

適用判斷

• ✅ 單表 > 100GB、多磁碟、明顯熱溫冷分層。
• ✅ 需同時按時間範圍裁剪、按哈希並行掃描。
• ❌ 中小表、單磁碟、無明確存儲分層需求 → 單層分區或不分區更簡潔。