分區是在物理層面將一張大表的數據文件(.MYD/.ibd)與索引文件(.MYI)切分為多個較小的片段。邏輯上仍是一張表,查詢時優化器根據分區鍵自動裁剪掃描範圍(Partition Pruning),大幅降低 I/O 與掃描行數。同時支援將不同分區放置於不同磁碟/掛載點,突破單磁碟容量與 I/O 瓶頸。
前置檢查:SHOW VARIABLES LIKE '%part%'; 確認 have_partitioning=YES(5.1+ 默認開啟)。
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) ... 會自動遷移數據。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。
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 使用線性遞歸算法,增減分區時數據移動較少。
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)。
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)
);
p3sp0)。| 操作 | RANGE/LIST | HASH/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 減少分區 |
WHERE 條件命中分區鍵時,僅掃描相關分區,查詢速度提升數量級。DELETE 慢操作與碎片整理。MySQL 分區是處理 TB 級單表的利器。RANGE 適合時間序列/ID 遞增場景,LIST 適合枚舉業務維度,HASH/KEY 適合均勻分佈需求。善用 DROP/ADD PARTITION 實現數據冷熱分離與滾動窗口,配合分區裁剪讓查詢「只讀需要的數據」。