MySQL 分區技術深度詳解：類型、優勢、限制與管理操作

MySQL Partition 分區技術全景解析

單表超百萬行性能下降不可避免，分表雖有效但改動架構成本高。MySQL 5.1 引入 Partition，在單實例內部實現數據水平切分，兼具性能與透明性。

一、分區核心優勢

1. 突破單文件大小限制：跨磁盤/文件系統存儲更多數據
2. 秒級歷史數據清理：DROP PARTITION 瞬時完成，避免 DELETE 鎖表產生碎片
3. 分區剪枝優化查詢：WHERE 條件命中分區鍵時，僅掃描相關分區
4. 聚合函數並行執行：SUM/COUNT 可跨分區並行聚合
5. I/O 吞吐提升：不同分區可放置不同物理磁盤

二、分區類型詳解

1. RANGE 分區：連續區間

CREATE TABLE t_range (
    id INT, money INT UNSIGNED NOT NULL, dt DATETIME
) PARTITION BY RANGE (YEAR(dt)) (
    PARTITION p2007 VALUES LESS THAN (2008),
    PARTITION p2008 VALUES LESS THAN (2009),
    PARTITION p2009 VALUES LESS THAN (2010),
    PARTITION p2010 VALUES LESS THAN MAXVALUE
);

適用：時間序列、數值範圍、歸檔場景。

2. LIST 分區：離散值集合

CREATE TABLE t_list (
    a INT, b INT
) PARTITION BY LIST (b) (
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (2,4,6,8,0)
);

適用：枚舉型業務鍵（地域代碼、業務類型）。

3. HASH 分區：均勻分布

CREATE TABLE t_hash (
    a INT, dt DATETIME
) PARTITION BY HASH (YEAR(dt)) PARTITIONS 4;

目標：數據均勻分佈，避免熱點。分區數建議 2 的冪次。

4. KEY 分區：內部哈希函數

CREATE TABLE t_key (
    a INT, dt DATETIME
) PARTITION BY KEY (dt) PARTITIONS 4;

差異：由 MySQL 內部哈希函數計算，NDB Cluster 用 MD5，其他引擎用類似 PASSWORD() 算法。支持非整數列。

5. COLUMNS 分區 (5.5+)：原生非整數支持

• RANGE COLUMNS：直接用 DATE、VARCHAR、CHAR 等分區，無需函數轉換
• LIST COLUMNS：同理支持字符串/日期離散值
• 支持多列分區：PARTITION BY RANGE COLUMNS (dt, region)

支持類型：所有整數、DATE/DATETIME、CHAR/VARCHAR/BINARY/VARBINARY。不支持 FLOAT/DECIMAL/BLOB/TEXT。

三、分區管理操作

新增分區

ALTER TABLE sale_data ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));

刪除分區（數據同步刪除）

ALTER TABLE sale_data DROP PARTITION p201010;

分區合併/拆分

ALTER TABLE sale_data REORGANIZE PARTITION p201001,p201002,p201003,
    p201004,p201005,p201006,p201007,p201008,p201009 INTO (
    PARTITION p2010Q1 VALUES LESS THAN (201004),
    PARTITION p2010Q2 VALUES LESS THAN (201007),
    PARTITION p2010Q3 VALUES LESS THAN (201010)
);

四、關鍵限制與檢查

-- 檢查是否支持分區
SHOW VARIABLES LIKE '%partition%';
-- have_partition_engine = YES

-- 查看表分區信息
SHOW CREATE TABLE t_range\G
EXPLAIN PARTITIONS SELECT * FROM t_range WHERE dt > '2008-01-01';

• 分區鍵必須在主鍵/唯一鍵中
• 分區字段非 NULL
• 外鍵不支持分區表
• 最大 8192 個分區（含子分區）
• 所有分區必須用相同存儲引擎