MySQL 分區表管理與維護實戰：RANGE+HASH 組合分區動態調整

MySQL 分區表管理與維護實戰：RANGE+HASH 組合分區動態調整

分區類型概覽

MySQL 支援四種分區：RANGE、LIST（按區間/列表值）、HASH、KEY（按算法分布）。本文實戰採用 RANGE 主分區 + HASH 子分區 組合模式。

實戰場景：聊天記錄表

用戶數千，已按用戶水平分表，但單表仍過大。需按時間分區，保留近兩年數據，定期刪除舊分區、創建新分區，實現高效維護。

建表語句（含分區定義）

CREATE TABLE `msgss` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `sender` INT(10) UNSIGNED NOT NULL COMMENT '發送者ID',
  `reciver` INT(10) UNSIGNED NOT NULL COMMENT '接收者ID',
  `msg_type` TINYINT(3) UNSIGNED NOT NULL COMMENT '消息類型',
  `msg` VARCHAR(225) NOT NULL COMMENT '消息內容',
  `atime` INT(10) UNSIGNED NOT NULL COMMENT '發送時間(Unix時間戳)',
  `sub_id` TINYINT(3) UNSIGNED NOT NULL COMMENT '部門ID',
  PRIMARY KEY (`id`, `atime`, `sub_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (atime)
SUBPARTITION BY HASH (sub_id) (
  PARTITION t0 VALUES LESS THAN (1451577600) (  -- 2016-01-01 前
    SUBPARTITION s0, s1, s2, s3, s4, s5
  ),
  PARTITION t1 VALUES LESS THAN (1483200000) (  -- 2017-01-01 前
    SUBPARTITION s6, s7, s8, s9, s10, s11
  ),
  PARTITION t2 VALUES LESS THAN MAXVALUE (      -- 2017-01-01 及以後
    SUBPARTITION s12, s13, s14, s15, s16, s17
  )
);

三個 RANGE 主分區（按時間），每主分區下 6 個 HASH 子分區（按部門 ID）。

測試數據插入

INSERT INTO `msgss` (`sender`,`reciver`,`msg_type`,`msg`,`atime`,`sub_id`) VALUES
(1,2,0,'Hello HASH',UNIX_TIMESTAMP(NOW()),1),
(1,2,0,'Hello HASH 2',UNIX_TIMESTAMP(NOW()),2),
-- 更多測試數據...
(1,2,0,'Hello HASH 5',1514736098,5);  -- 2017 年數據

分區效果驗證

-- 查看查詢掃描的分區
EXPLAIN PARTITIONS SELECT * FROM msgss;

-- 驗證數據分布
EXPLAIN PARTITIONS SELECT * FROM msgss WHERE atime < 1451577600;        -- 僅掃描 t0
EXPLAIN PARTITIONS SELECT * FROM msgss WHERE atime BETWEEN 1451577600 AND 1483200000;  -- 僅掃描 t1
EXPLAIN PARTITIONS SELECT * FROM msgss WHERE atime > 1483200000;       -- 僅掃描 t2

動態維護：刪除 2015 年數據並重組分區

需求：進入 2017 年，刪除 2015 年（t0）數據，保留 2016 年（t1），2017 年繼續寫入 t2。

ALTER TABLE `msgss` DROP PARTITION t0;

數據隨分區一同刪除，瞬間完成，無需逐行 DELETE。

ALTER TABLE `msgss` 
PARTITION BY RANGE (atime)
SUBPARTITION BY HASH (sub_id) (
  PARTITION t0 VALUES LESS THAN (1483200000) (  -- 原 t1 變為新 t0 (2016 年)
    SUBPARTITION s0, s1, s2, s3, s4, s5
  ),
  PARTITION t1 VALUES LESS THAN (1514736000) (  -- 原 t2 前半段變為新 t1 (2017 年上半年)
    SUBPARTITION s6, s7, s8, s9, s10, s11
  ),
  PARTITION t2 VALUES LESS THAN MAXVALUE (      -- 2017 年下半年及以後
    SUBPARTITION s12, s13, s14, s15, s16, s17
  )
);

核心優勢

• 秒級歸檔/清理：DROP PARTITION 為元數據操作，不掃描數據行
• 查詢裁剪：WHERE 條件含分區鍵時，僅掃描相關分區
• 存儲分離：可將不同分區掛載到不同磁碟/存儲介質
• 維護彈性：REORGANIZE PARTITION、ADD PARTITION、COALESCE PARTITION 靈活調整

注意事項

1. 分區鍵必須是主鍵/唯一鍵的一部分（故主鍵含 atime, sub_id）
2. 外鍵不支援分區表
3. 最大 8192 個分區（含子分區）
4. 所有分區必須使用相同存儲引擎