MySQL 分區表完整指南：類型、創建、管理、子分區與分表對比

MySQL 分區表完整指南：類型、創建、管理、子分區與分表對比

概述

MySQL 5.1+ 支持分區：按規則將一張表拆分為多個物理區域存儲。對應用透明，類似「化整為零」。支持 MyISAM/InnoDB/Memory 等引擎，不支持 MERGE/CSV。同表所有分區引擎必須一致。

分區作用

• 突破單文件大小限制，存儲更多數據
• 分區裁剪：WHERE 條件含分區鍵時，僅掃描相關分區
• 聚合函數可在分區並行執行，最後匯總
• 過期數據通過 DROP PARTITION 秒級清理
• 跨磁盤分佈提升 I/O 並發能力

四大分區類型

限制： 5.1 要求分區鍵為 INT；5.5+ 支持 RANGE COLUMNS/LIST COLUMNS 非整型。
核心規則： 無主鍵/唯一鍵，或分區鍵必須包含主鍵/唯一鍵的一列。

詳細語法示例

1. RANGE 分區

CREATE TABLE partition_t2(
    id INT UNSIGNED NOT NULL,
    ...
) ENGINE=MYISAM
PARTITION BY RANGE(id)(
    PARTITION t21 VALUES LESS THAN (10),
    PARTITION t22 VALUES LESS THAN (20),
    PARTITION t23 VALUES LESS THAN MAXVALUE
);

解釋：id<10 → t21；10≤id<20 → t22；id≥20 → t23。

2. RANGE COLUMNS (5.5+)

PARTITION BY RANGE COLUMNS(birth_date)(
    PARTITION t31 VALUES LESS THAN ('1996-01-01'),
    PARTITION t32 VALUES LESS THAN ('2006-01-01'),
    PARTITION t33 VALUES LESS THAN ('2038-01-01')
);

3. LIST 分區

PARTITION BY LIST(id)(
    PARTITION t41 VALUES IN (1,2),
    PARTITION t42 VALUES IN (3,6),
    PARTITION t43 VALUES IN (5,4),
    PARTITION t44 VALUES IN (7,8)
);

4. HASH 分區

-- 常規 HASH
PARTITION BY HASH(MONTH(birth_date)) PARTITIONS 5;

-- 線性 HASH (維護效率高，分佈稍不均)
PARTITION BY LINEAR HASH(id) PARTITIONS 5;

5. KEY 分區

PARTITION BY LINEAR KEY(email) PARTITIONS 5;

COLUMNS 分區與子分區

多列 RANGE COLUMNS

PARTITION BY RANGE COLUMNS(a,b)(
    PARTITION p0 VALUES LESS THAN (0,10),
    PARTITION p1 VALUES LESS THAN (10,10),
    PARTITION p2 VALUES LESS THAN (10,20),
    ...
);

判斷邏輯：(a<10) OR (a=10 AND b<10)。

子分區（復合分區）

CREATE TABLE partition_t8(
    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
);

分區管理命令

RANGE/LIST

-- 刪除分區
ALTER TABLE partition_t8 DROP PARTITION p2;

-- 添加分區 (只能在最大端添加)
ALTER TABLE partition_t8 ADD PARTITION (
    PARTITION p4 VALUES LESS THAN (2030)
);

-- 重定義相鄰分區 (覆蓋相同區間)
ALTER TABLE partition_t8 REORGANIZE PARTITION p3 INTO (
    PARTITION p2 VALUES LESS THAN (2005),
    PARTITION p3 VALUES LESS THAN (2015)
);

HASH/KEY

-- 減少分區數
ALTER TABLE partition_t8 COALESCE PARTITION 2;

-- 增加分區數
ALTER TABLE partition_t8 ADD PARTITION PARTITIONS 8;

分區 vs 分表

分區是單表內部物理拆分，對應用透明；分表是邏輯拆分為多張表，需應用層路由。分區適合單表過大、有明確範圍特徵的場景；分表適合極高並發、跨庫分佈的場景。