您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 子分區深度解析：RANGE+HASH 復合分區、磁盤分佈策略、語法陷阱與實戰案例

編輯：MySQL 綜合教學

核心概念

子分區 是分區表中每個分區的再次劃分。實現「分區策略 + 子分區策略」的復合分區，如 `RANGE + HASH`、`LIST + KEY` 等。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
);
-- 總分區數 = 3 (RANGE) × 2 (HASH) = 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
    )
);

關鍵語法限制（必讀避坑）

規則	說明	違反後果
每個分區子分區數量必須相同	由 SUBPARTITIONS n 或顯式定義保證	CREATE TABLE 失敗
若一個分區顯式定義子分區，則所有分區必須顯式定義	不能混用隱式/顯式	報錯：Every partition must have same number of subpartitions
子分區名稱在分區內唯一，跨分區可重名	s0 可在 p0、p1 中同時存在	同分區內重名報錯

實戰：跨磁盤分佈數據與索引（IO 隔離）

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

效果： 6 個子分區分佈在 6 塊物理磁盤，實現數據和索引的 IO 完全隔離，極大提升並發讀寫吞吐。

未來擴展：ALTER TABLE 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
);

無需停機遷移，Online DDL (MySQL 8.0+) 可在線完成。