MySQL 表分區完整指南：類型、創建、管理與避坑

MySQL 5.5+ 表分區實戰大全

分區將大表按規則拆分為多個物理片段，提升查詢剪枝、歸檔刪除、並行聚合效能。本文覆蓋 RANGE/LIST/HASH/KEY 四大類型、子分區、管理操作、核心限制。

啟用檢查

SHOW VARIABLES LIKE '%partition%';
-- have_partitioning = YES 表示支援

四大分區類型

1. RANGE 分區：連續區間

CREATE TABLE p_range (
    id INT NOT NULL AUTO_INCREMENT,
    name CHAR(20),
    PRIMARY KEY (id)
) ENGINE=MyISAM
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (8),
    PARTITION p1 VALUES LESS THAN MAXVALUE
);

• 適用：時間序列、定期歸檔刪除舊分區
• MAXVALUE 必備，否則超出範圍寫入報錯

2. LIST 分區：離散值集合

CREATE TABLE p_list (
    id INT NOT NULL AUTO_INCREMENT,
    typeid MEDIUMINT,
    PRIMARY KEY (id, typeid)
) ENGINE=MyISAM
PARTITION BY LIST (typeid) (
    PARTITION p0 VALUES IN (1,2,3,4),
    PARTITION p1 VALUES IN (5,6,7,8)
);

• 無 MAXVALUE 等價寫法，**所有可能值必須列全**
• 適用：業務類型固定、枚舉值分區

3. HASH 分區：均勻分佈

CREATE TABLE p_hash (
    id INT NOT NULL AUTO_INCREMENT,
    storeid MEDIUMINT,
    PRIMARY KEY (id, storeid)
) ENGINE=InnoDB
PARTITION BY HASH (storeid) PARTITIONS 4;

• MySQL 自動按 MOD(expr, partitions) 分佈
• expr 可為欄位或表達式（如 YEAR(created)），但複雜表達式影響寫入效能
• 支援 LINEAR HASH（線性哈希），增刪分區更快，適合 TB 級大表

4. KEY 分區：服務端哈希

CREATE TABLE p_key (
    id INT NOT NULL AUTO_INCREMENT,
    keyname CHAR(20),
    PRIMARY KEY (id)
) ENGINE=MyISAM
PARTITION BY KEY (id) PARTITIONS 4;

• 類似 HASH，但哈希函數由 MySQL 內部提供（Cluster 用 MD5，其他引擎用 PASSWORD 算法）
• 僅接受欄位列表，不接受表達式

子分區

PARTITION BY RANGE (YEAR(createtime))
SUBPARTITION BY HASH (MONTH(createtime)) (
    PARTITION p0 VALUES LESS THAN (2012) (
        SUBPARTITION s1, SUBPARTITION s2
    ),
    PARTITION p1 VALUES LESS THAN (2013) (
        SUBPARTITION s3, SUBPARTITION s4
    ),
    PARTITION p2 VALUES LESS THAN MAXVALUE (
        SUBPARTITION s5, SUBPARTITION s6
    )
);

• 每個一級分區再按月哈希為 2 子分區，共 6 物理片段
• 規則：同表所有一級分區子分區數必相同；同一分區內子分區名唯一

核心限制（必讀）

1. 主鍵/唯一鍵**必須包含分區鍵**
2. LIST 無 MAXVALUE，值必須全覆蓋
3. 同表所有分區**必須同一存儲引擎**
4. 分區名不區分大小寫
5. 5.1 版本開始支援，生產建議 5.5+

分區管理操作

維護指令

-- 重建（整理碎片）
ALTER TABLE t REBUILD PARTITION (p0, p1);
-- 優化（回收空間）
ALTER TABLE t OPTIMIZE PARTITION (p0, p1);
-- 分析統計
ALTER TABLE t ANALYZE PARTITION (p3);
-- 修復損壞
ALTER TABLE t REPAIR PARTITION (p0, p1);
-- 檢查
ALTER TABLE t CHECK PARTITION (p1);

查看分區資訊

EXPLAIN PARTITIONS SELECT * FROM p_hash;
SELECT * FROM information_schema.PARTITIONS WHERE TABLE_NAME = 'p_list';
SHOW CREATE TABLE p_list;