萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 數據庫分區功能詳解:類型、實戰與管理最佳實踐

什麼是數據庫分區?

分區是在物理層面將一張大表的數據文件(.MYD/.ibd)與索引文件(.MYI)切分為多個較小的片段。邏輯上仍是一張表,查詢時優化器根據分區鍵自動裁剪掃描範圍(Partition Pruning),大幅降低 I/O 與掃描行數。同時支援將不同分區放置於不同磁碟/掛載點,突破單磁碟容量與 I/O 瓶頸。

橫向分區 vs 縱向分區

  • 橫向分區(MySQL 原生支援): 按行範圍/哈希/列表將數據水平切分,表結構不變。如按年份、ID 範圍分區。
  • 縱向分區: 將大字段(如 TEXT/BLOB)拆到獨立表,屬於架構設計層面,非 MySQL 內建分區功能。

MySQL 分區類型與實戰

前置檢查:SHOW VARIABLES LIKE '%part%'; 確認 have_partitioning=YES(5.1+ 默認開啟)。

1. RANGE 分區 —— 按連續區間劃分

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50),
  `sex` TINYINT DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (id) (
  PARTITION p0 VALUES LESS THAN (3),
  PARTITION p1 VALUES LESS THAN (6),
  PARTITION p2 VALUES LESS THAN (9),
  PARTITION p3 VALUES LESS THAN (12),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);
  • 物理文件:user#P#p0.MYD、user#P#p1.MYD… 最小分配單元 4KB。
  • ALTER TABLE user DROP PARTITION p4; 瞬間刪除歷史數據(配合歸檔策略極佳)。
  • 現有表轉分區:ALTER TABLE aa PARTITION BY RANGE(id) ... 會自動遷移數據。

2. LIST 分區 —— 按離散值列表劃分

CREATE TABLE `list_part` (
  `id` INT NOT NULL,
  `province_id` TINYINT NOT NULL DEFAULT 0,
  `name` VARCHAR(50),
  `sex` TINYINT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LIST (province_id) (
  PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
  PARTITION p1 VALUES IN (9,10,11,12,16,21),
  PARTITION p2 VALUES IN (13,14,15,19),
  PARTITION p3 VALUES IN (17,18,20,22,23,24)
);

限制: 分區鍵必須包含在主鍵中(如 PRIMARY KEY (id, province_id)),否則報錯 ERROR 1503。

3. HASH 分區 —— 均勻分佈

CREATE TABLE `hash_part` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `comment` VARCHAR(1000),
  `ip` VARCHAR(25),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY HASH(id) PARTITIONS 3;

適合無明顯範圍特徵、追求負載均衡的場景。LINEAR HASH 使用線性遞歸算法,增減分區時數據移動較少。

4. KEY 分區 —— 服務端哈希

CREATE TABLE `key_part` (
  `news_id` INT NOT NULL,
  `content` VARCHAR(1000),
  `u_id` VARCHAR(25),
  `create_time` DATE NOT NULL DEFAULT '0000-00-00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY LINEAR HASH(YEAR(create_time)) PARTITIONS 3;

哈希函數由 MySQL 內部提供,支援非整數列(如 DATE、DATETIME)。

5. 子分區 —— 復合分區

CREATE TABLE `sub_part` (
  `news_id` INT NOT NULL,
  `content` VARCHAR(1000),
  `u_id` INT NOT NULL DEFAULT 0,
  `create_time` DATE NOT NULL DEFAULT '0000-00-00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE(YEAR(create_time))
SUBPARTITION BY HASH(TO_DAYS(create_time)) (
  PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, s1, s2),
  PARTITION p1 VALUES LESS THAN (2000) (SUBPARTITION s3, s4, good),
  PARTITION p2 VALUES LESS THAN MAXVALUE (SUBPARTITION tank0, tank1, tank3)
);
  • 規則:所有分區必須有相同數量的子分區;同分區內子分區名唯一,跨分區可重名(5.1.50+ 已支援)。
  • 新增分區時子分區名自動生成(如 p3sp0)。

分區管理操作速查

操作RANGE/LISTHASH/KEY
刪除分區ALTER TABLE t DROP PARTITION p0;同左
新增分區ALTER TABLE t ADD PARTITION (PARTITION p4 VALUES LESS THAN MAXVALUE);ALTER TABLE t ADD PARTITION PARTITIONS 4;(重新分佈)
重組分區ALTER TABLE t REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN MAXVALUE);不支援,改用 COALESCE PARTITION N 減少分區

分區四大核心優勢

  1. 突破單磁碟限制: 分區分佈於多磁碟,單表可超過文件系統大小限制。
  2. 分區裁剪: WHERE 條件命中分區鍵時,僅掃描相關分區,查詢速度提升數量級。
  3. 並行查詢: 大掃描操作可並行讀取多個分區(需優化器支援)。
  4. 生命周期管理: 歷史分區秒級歸檔/刪除,無需 DELETE 慢操作與碎片整理。

避坑指南

  • 分區鍵必須為整數類型或返回整數的表達式(5.5+ 支援 DATE/DATETIME 直接分區)。
  • 主鍵/唯一鍵必須包含分區鍵所有列。
  • 外鍵不支援分區表。
  • 過多的分區(>1000)會增加優化器分析開銷,建議控制在百級以內。

總結

MySQL 分區是處理 TB 級單表的利器。RANGE 適合時間序列/ID 遞增場景,LIST 適合枚舉業務維度,HASH/KEY 適合均勻分佈需求。善用 DROP/ADD PARTITION 實現數據冷熱分離與滾動窗口,配合分區裁剪讓查詢「只讀需要的數據」。

copyright © 萬盛學電腦網 all rights reserved