您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL RANGE 分區深度解析：語法、實例與最佳實踐

編輯：MySQL 資料庫相關知識

分區是 MySQL 處理大表性能優化的關鍵特性之一。RANGE 分區 根據列值所屬的連續區間將數據劃分到不同分區，適用於時間序列、ID 範圍等有序數據場景。本文系統講解 RANGE 分區語法規則、多種實戰案例、邊界處理技巧及優化優勢，助你靈活應對大表管理挑戰。

一、核心概念與語法規則

分區鍵： 使用 PARTITION BY RANGE(expr)，expr 為列或表達式（返回整數）。
區間定義： 用 VALUES LESS THAN value 定義上邊界（不含），區間必須 連續且不重疊，按遞增順序聲明（類似 switch-case）。
捕獲異常值： 使用 VALUES LESS THAN MAXVALUE 作為最後一個分區，收納超出顯式範圍的數據，避免插入報錯。

二、基於 store_id 的店鋪分區實例

假設有 20 家音像店（ID 1~20），員工表 employees 包含 store_id 列。需按店鋪區間分區：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),    -- store 1-5
    PARTITION p1 VALUES LESS THAN (11),   -- store 6-10
    PARTITION p2 VALUES LESS THAN (16),   -- store 11-15
    PARTITION p3 VALUES LESS THAN (21)    -- store 16-20
);

數據路由邏輯： store_id=7 → p1；store_id=13 → p2。若插入 store_id=21 且無 MAXVALUE 分區，將報錯 ERROR 1526 (HY000): Table has no partition for value 21。

改進版：使用 MAXVALUE 兜底

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN MAXVALUE  -- 收納 16 及以上所有值
);

未來店鋪擴展至 25、30 家時，可用 ALTER TABLE ... REORGANIZE PARTITION 切分 p3 增加新分區，實現平滑擴容。

三、基於 job_code 的職級分區

假設職位編碼規則：2 位數=店內工人、3 位數=辦公室/支援、4 位數=管理層。利用數值區間特性分區：

PARTITION BY RANGE (job_code) (
    PARTITION p0 VALUES LESS THAN (100),    -- 1-99 店內工人
    PARTITION p1 VALUES LESS THAN (1000),   -- 100-999 辦公室/支援
    PARTITION p2 VALUES LESS THAN (10000)   -- 1000-9999 管理層
);

四、基於日期表達式的分區：YEAR(separated)

按員工離職年份分區，適合歷史數據歸檔與清理：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY RANGE (YEAR(separated)) (
    PARTITION p0 VALUES LESS THAN (1991),   -- 1991 前離職
    PARTITION p1 VALUES LESS THAN (1996),   -- 1991-1995
    PARTITION p2 VALUES LESS THAN (2001),   -- 1996-2000
    PARTITION p3 VALUES LESS THAN MAXVALUE  -- 2001 及以後
);

注意： 分區表達式 YEAR(separated) 必須返回非 NULL 整數，故相關列需定義為 NOT NULL。

五、 RANGE 分區的核心優勢場景

1. 高效清理歷史數據（分區裁剪）

需刪除 1991 年前離職員工記錄：

ALTER TABLE employees DROP PARTITION p0;

對比 DELETE FROM employees WHERE YEAR(separated) <= 1990;，DROP PARTITION 為元數據操作，瞬間完成，無行級鎖開銷，對大表極其高效。

2. 時間序列/遞增 ID 列的天然適配

訂單表按 created_at 月份分區、日誌表按 log_id 範圍分區，均屬典型適用場景。

3. 查詢分區裁剪優化

執行 SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id; 時，優化器僅掃描 p2 分區，跳過其餘分區，大幅降低 I/O。（註：MySQL 5.1 尚未完全啟用此優化，後續版本已成熟支援）