MySQL 分區技術權威指南：類型詳解、管理操作與優化實戰

MySQL Partition 分區技術全維度深度解析

單表百萬行以上性能不可避免下降。分表改動架構成本高，MySQL 5.1+ Partition 在單實例內部實現水平切分，兼顧性能與透明性。

一、分區五大核心優勢

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

二、關鍵限制（必讀）

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

三、五大分區類型詳解

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

適用：時間序列、數值範圍、歸檔。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)
);

適用：枚舉業務鍵（地域、類型）。插入非列表值報錯（InnoDB 回滾，MyISAM 部分寫入）。

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 用 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。

四、分區管理操作

檢查支持

SHOW VARIABLES LIKE '%partition%';
-- have_partition_engine = YES

新增分區

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

五、優化與監控

• EXPLAIN PARTITIONS 驗證分區剪枝：partitions 列顯示實際掃描分區
• 大表 REORGANIZE 低峰期執行，監控 Innodb_buffer_pool_pages_dirty
• 定期 ANALYZE TABLE 更新分區統計信息
• 分區表備份：mysqldump 自動處理，物理備份需注意 .ibd 文件對應