MySQL 5.1+ 支援表分區,通過將大表拆分為多個物理分區,實現數據歸檔、歷史清理、查詢分區裁剪。但分區並非銀彈,存在嚴格約束與易踩坑點。本文系統梳理分區列索引約束、四大分區類型條件、允許的分區函數、分區裁剪失效場景、分區維護操作 (ADD/DROP/REORGANIZE/REMOVE),助你避開生產事故。
鐵律:表存在 PRIMARY KEY 或 UNIQUE KEY 時,**分區列必須包含在所有唯一索引的列列表中**。
-- 錯誤示範:uk_uid 不包含 partition key `dt`
CREATE TABLE `orders` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`uid` INT NOT NULL,
`dt` DATE NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_uid` (`uid`)
) PARTITION BY RANGE (YEAR(`dt`)) (...);
-- ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function
-- 正確:分區列 dt 加入主鍵或唯一鍵
CREATE TABLE `orders` (
`id` BIGINT NOT NULL AUTO_INCREMENT,
`uid` INT NOT NULL,
`dt` DATE NOT NULL,
PRIMARY KEY (`id`, `dt`), -- 包含 dt
UNIQUE KEY `uk_uid_dt` (`uid`, `dt`) -- 包含 dt
) PARTITION BY RANGE (YEAR(`dt`)) (...);
原因:唯一性檢查需在單分區內完成,若分區列不在唯一索引中,同一鍵值可能落在不同分區,導致跨分區唯一性檢查性能災難。
| 類型 | 分區表達式 | 值類型要求 | 區間/集合定義 | 典型場景 |
|---|---|---|---|---|
| RANGE | 連續區間 | 整數/日期/時間函數返回整數 | VALUES LESS THAN (值),連續不重疊,需 MAXVALUE 兜底 | 時間序列 (日志、訂單、監控) |
| LIST | 離散值集合 | 整數 | VALUES IN (值列表),值不重疊,無 MAXVALUE 兜底 | 地區、狀態碼、租戶 ID |
| HASH | 用戶表達式 | 整數 (非負) | PARTITIONS N,MySQL 內部哈希分發 | 均勻分散熱點、無明確業務邊界 |
| KEY | 列名 (不支持表達式) | 整數列 (一或多列) | PARTITIONS N,MySQL 內部哈希函數 | 無明確分區邏輯、需均勻分發 |
只有以下函數可用於分區表達式,**FROM_UNIXTIME、UNIX_TIMESTAMP、DATE_FORMAT 均不支援**:
ABS(), CEILING(), FLOOR(),
DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(),
DATEDIFF(), EXTRACT(),
HOUR(), MICROSECOND(), MINUTE(), SECOND(),
MOD(), MONTH(), QUARTER(),
TIME_TO_SEC(), TO_DAYS(), TO_SECONDS(),
WEEKDAY(), YEAR(), YEARWEEK()
-- 按年分區
PARTITION BY RANGE (YEAR(`dt`)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION pmax VALUES LESS THAN MAXVALUE
)
-- 按月分區 (推薦 DATE 列直接 DIV 100)
PARTITION BY RANGE (`dt` DIV 100) (
PARTITION p202401 VALUES LESS THAN (202402),
PARTITION p202402 VALUES LESS THAN (202403),
PARTITION pmax VALUES LESS THAN MAXVALUE
)
-- 2024-01-15 -> 20240115 DIV 100 = 202401
-- RANGE: 必須在 MAXVALUE 分區之前拆分
ALTER TABLE `orders` REORGANIZE PARTITION pmax INTO (
PARTITION p202411 VALUES LESS THAN (202412),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- LIST: 直接 ADD (值不衝突)
ALTER TABLE `orders` ADD PARTITION (
PARTITION p_new_region VALUES IN (100, 101)
);
ALTER TABLE `orders` DROP PARTITION p202301;
-- 等同於 DELETE FROM orders WHERE dt < '2023-02-01' 但秒級完成
ALTER TABLE `orders` REORGANIZE PARTITION p202401,p202402,pmax INTO (
PARTITION p_merged VALUES LESS THAN MAXVALUE
);
-- 注意:合並必須包含最後一個分區,否則報錯「total range changed」
ALTER TABLE `orders` REORGANIZE PARTITION pmax INTO (
PARTITION p202411 VALUES LESS THAN (202412),
PARTITION p202412 VALUES LESS THAN (202501),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
ALTER TABLE `orders` REMOVE PARTITIONING;
-- 5.6+ 支援,5.5 可能有 Bug,先測試
-- 將分區 p202301 數據秒級移至獨立表 archive_202301
ALTER TABLE `orders` EXCHANGE PARTITION p202301 WITH TABLE `archive_202301`;
-- 要求:archive_202301 結構完全一致、無外鍵、非分區表
| 失效模式 | 錯誤寫法 | 正確寫法 |
|---|---|---|
| 範圍查詢非等值 | WHERE dt >= '2024-01-01' AND dt <= '2024-01-31' | WHERE dt IN ('2024-01-01',...,'2024-01-31') 或 WHERE dt BETWEEN ... (5.7+ 支援 BETWEEN 裁剪) |
| 函數包裹分區列 | WHERE YEAR(dt) = 2024 | WHERE dt >= '2024-01-01' AND dt < '2025-01-01' (配合 RANGE COLUMNS) |
| 隱式類型轉換 | WHERE dt = 20240101 (數字) | WHERE dt = '2024-01-01' |
| OR 條件跨分區 | WHERE dt = '2024-01-01' OR uid = 100 | 拆分為 UNION ALL 兩條查詢 |
直接使用列而非表達式,支援 DATE、DATETIME、VARCHAR 等類型,**原生支援分區裁剪**,無需函數轉換:
CREATE TABLE `orders` (
`id` BIGINT, `dt` DATE, `uid` INT,
PRIMARY KEY (`id`, `dt`)
) PARTITION BY RANGE COLUMNS(`dt`) (
PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
-- 查詢 WHERE dt = '2024-01-15' 直接裁剪到 p202401
MySQL 分區核心在於:分區列進主鍵/唯一鍵、選對類型 (RANGE COLUMNS 首選)、查寫避開裁剪失效模式、善用 EXCHANGE PARTITION 秒級歸檔、定期 REORGANIZE 維護分區邊界。分區解決「大表歷史數據管理」與「時間維度查詢裁剪」兩大問題,不適用於高併發點查、複雜 JOIN、全表掃描聚合場景。