萬盛學電腦網

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

MySQL 表分區核心注意事項:分區鍵約束、類型條件、允許函數、分區裁剪與維護操作

前言

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 內部哈希函數無明確分區邏輯、需均勻分發

三、允許的分區函數 (MySQL 5.7 官方白名單)

只有以下函數可用於分區表達式,**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

四、分區維護操作實戰

1. 新增分區 (RANGE/LIST)

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

2. 刪除分區 (數據同步刪除)

ALTER TABLE `orders` DROP PARTITION p202301;
-- 等同於 DELETE FROM orders WHERE dt < '2023-02-01' 但秒級完成

3. 合並分區

ALTER TABLE `orders` REORGANIZE PARTITION p202401,p202402,pmax INTO (
  PARTITION p_merged VALUES LESS THAN MAXVALUE
);
-- 注意:合並必須包含最後一個分區,否則報錯「total range changed」

4. 拆分分區

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

5. 移除分區屬性 (保留數據,變回普通表)

ALTER TABLE `orders` REMOVE PARTITIONING;
-- 5.6+ 支援,5.5 可能有 Bug,先測試

6. 交換分區 (與獨立表交換數據,極速歸檔/導入)

-- 將分區 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) = 2024WHERE 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 兩條查詢

六、RANGE COLUMNS / LIST COLUMNS (5.5+ 推薦)

直接使用列而非表達式,支援 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、全表掃描聚合場景。

copyright © 萬盛學電腦網 all rights reserved