MySQL 復合分區深度解析：RANGE-HASH 子分區實戰與避坑指南（增強版）

MySQL 復合分區深度解析：RANGE-HASH 子分區實戰與避坑指南（增強版）

相比 Oracle 豐富的分區功能，MySQL 5.6 僅支援 RANGE/LIST 作為一級分區，子分區僅限 HASH/KEY 類型。掌握其語法限制與最佳實踐，對大表管理至關重要。

基本語法與結構

典型的 RANGE-HASH 復合分區範例：

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 個物理分區。

顯式定義子分區與物理路徑分離

可使用 SUBPARTITION 子句顯式命名並指定物理存放路徑，實現 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 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
    )
);

此特性可將熱/冷資料分散至不同磁碟，顯著提升 IO 吞吐。

核心限制與常見錯誤

1. 子分區數量必須一致：若使用顯式 SUBPARTITION，每個分區定義的子分區個數必須相同，否則報錯
2. 全部或不定義：不可部分分區定義子分區、部分不定義
3. DATA/INDEX DIRECTORY 實測異常：本地測試遇到 ERROR 1030 (HY000): Got error -1 from storage engine，疑似 MySQL Bug，生產環境需充分驗證

適用場景建議

• 時間序列資料：按年份 RANGE 分區，日期 HASH 子分區
• 需按物理磁碟隔離存儲的大表
• 查詢常帶時間範圍條件，可實現分區裁剪

參考官方文檔：Subpartitions、Tablespace Placing