您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 子分區完整教學：複合分區架構、磁盤分佈與管理實戰

編輯：MySQL 資料庫相關知識

子分區 是分區表中每個分區的二次劃分，實現「複合分區」架構。本文系統講解子分區語法規則、顯式定義約束、命名規範、磁盤級數據/索引分佈實戰案例，助你構建超大表的高性能存儲架構。

一、核心概念與語法基礎

定義： PARTITION BY ... SUBPARTITION BY ...，第一層為 RANGE/LIST，第二層為 HASH/KEY。
總分區數 = 第一層分區數 × 子分區數。例如 3 個 RANGE 分區 × 2 個 HASH 子分區 = 6 個物理分區。
MySQL 5.1+ 支援： RANGE/LIST 作為第一層，HASH/KEY 作為第二層。

二、基礎語法範例

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 個 HASH 子分區，共 6 個物理分區。但受 RANGE 條件限制，p0 僅存儲 1990 年前數據，p1 存 1990-1999 年，p2 存 2000 年後。

三、顯式定義子分區（精細控制）

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
    )
);

四、關鍵語法約束（必讀避坑）

子分區數量一致性： 每個父分區必須擁有相同數量的子分區。

全或無原則： 若在任一父分區上使用 SUBPARTITION 顯式定義，則 必須為所有父分區定義所有子分區。混合使用顯式與隱式定義會導致語法錯誤。

-- 錯誤示例：p1 未定義子分區
PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1),
PARTITION p1 VALUES LESS THAN (2000),  -- 缺少 SUBPARTITION 子句
PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION s2, SUBPARTITION s3);

命名規範：
- 每個 SUBPARTITION 子句至少需指定名稱。
- 同一父分區內子分區名稱唯一；不同父分區間可重名（如 p0.s0、p1.s0 合法）。

五、磁盤級分佈實戰：跨多磁盤分佈數據與索引

假設有 6 塊磁盤 /disk0 ~ /disk5，需將超大訂單表按年份分區，並在分區內進一步將數據與索引分離到不同磁盤：

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
            DATA DIRECTORY = '/disk0/data'
            INDEX DIRECTORY = '/disk0/idx',
        SUBPARTITION s1
            DATA DIRECTORY = '/disk1/data'
            INDEX DIRECTORY = '/disk1/idx'
    ),
    PARTITION p1 VALUES LESS THAN (2000)
    (
        SUBPARTITION s0
            DATA DIRECTORY = '/disk2/data'
            INDEX DIRECTORY = '/disk2/idx',
        SUBPARTITION s1
            DATA DIRECTORY = '/disk3/data'
            INDEX DIRECTORY = '/disk3/idx'
    ),
    PARTITION p2 VALUES LESS THAN MAXVALUE
    (
        SUBPARTITION s0
            DATA DIRECTORY = '/disk4/data'
            INDEX DIRECTORY = '/disk4/idx',
        SUBPARTITION s1
            DATA DIRECTORY = '/disk5/data'
            INDEX DIRECTORY = '/disk5/idx'
    )
);

存儲拓撲說明：

p0（1990 前，數據量最大）→ 佔用 4 塊盤：s0 數據/索引分別在 disk0，s1 在 disk1。
p1（1990-1999）→ 佔用 disk2、disk3。
p2（2000 後）→ 佔用 disk4、disk5。

六、彈性擴容策略：REORGANIZE PARTITION

若未來 p2 空間不足，可線上重組：

ALTER TABLE ts REORGANIZE PARTITION p2 INTO (
    PARTITION p2 VALUES LESS THAN (2010)
    (
        SUBPARTITION s0 DATA DIRECTORY='/disk6/data' INDEX DIRECTORY='/disk6/idx',
        SUBPARTITION s1 DATA DIRECTORY='/disk7/data' INDEX DIRECTORY='/disk7/idx'
    ),
    PARTITION p3 VALUES LESS THAN MAXVALUE
    (
        SUBPARTITION s0, SUBPARTITION s1
    )
);

實現無停機、無數據遷移的平滑擴容。

七、另一種混合佈局範例

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 s0a DATA DIRECTORY='/disk0' INDEX DIRECTORY='/disk1',
        SUBPARTITION s0b DATA DIRECTORY='/disk2' INDEX DIRECTORY='/disk3'
    ),
    PARTITION p1 VALUES LESS THAN (2000)
    (
        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  -- 使用默認路徑
    )
);

展示了不同分區可採用不同磁盤佈局策略的靈活性。