MySQL RANGE 分區深度解析：區間劃分、MAXVALUE 兜底、日期分區、分區裁剪優化與遷移實戰

核心機制

RANGE 分區 將數據按**連續、不重疊的區間** 劃分，使用 `VALUES LESS THAN` 定義上邊界（含邊界值）：

• 區間必須按遞增順序定義（類似 `switch-case`）。
• 分區表達式 `expr` 必須返回整數 (5.1) 或支持日期/字符串 (8.0+ `RANGE COLUMNS`)。
• **必須處理最大值**：使用 `VALUES LESS THAN MAXVALUE` 作為最後分區兜底，否則超出範圍數據 INSERT 失敗。

經典案例：店鋪 ID 範圍分區

CREATE TABLE employees (
    id INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),    -- 1-5
    PARTITION p1 VALUES LESS THAN (11),   -- 6-10
    PARTITION p2 VALUES LESS THAN (16),   -- 11-15
    PARTITION p3 VALUES LESS THAN MAXVALUE -- 16+
);

日期分區：最常見實戰模式

-- 按離職年份分區
CREATE TABLE employees (
    id INT NOT NULL,
    separated DATE NOT NULL DEFAULT '9999-12-31'
)
PARTITION BY RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (1991),
    PARTITION p1 VALUES LESS THAN (1996),
    PARTITION p2 VALUES LESS THAN (2001),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

-- MySQL 8.0+ 推薦：RANGE COLUMNS 直接用日期列，無需 YEAR() 函數
PARTITION BY RANGE COLUMNS (separated) (
    PARTITION p0 VALUES LESS THAN ('1991-01-01'),
    PARTITION p1 VALUES LESS THAN ('1996-01-01'),
    ...
);

三大核心優勢場景

分區裁剪驗證

EXPLAIN PARTITIONS 
SELECT COUNT(*) FROM employees 
WHERE YEAR(separated) = 2000 GROUP BY store_id;
-- 輸出 partitions 欄顯示 p2 → 裁剪生效

遷移與擴展實戰

-- 新增 2020-2029 年分區
ALTER TABLE employees REORGANIZE PARTITION p3 INTO (
    PARTITION p3 VALUES LESS THAN (2020),
    PARTITION p4 VALUES LESS THAN (2030),
    PARTITION p5 VALUES LESS THAN MAXVALUE
);

-- MySQL 8.0+ Online DDL 無鎖完成

避坑指南

• 分區鍵列**必須 NOT NULL**，否則 `VALUES LESS THAN` 比較失敗。
• 表達式不可為非確定性函數 (如 `RAND()`, `NOW()`)。
• 主鍵/唯一鍵必須包含分區鍵列，否則創建失敗。
• 分區數建議 ＜ 100，過多傷優化器裁剪性能。