MySQL 表分區完整指南：RANGE、LIST、HASH、KEY 與複合分區實戰

大數據量下的 MySQL 表分區策略：類型選擇、建立語法與維護操作全解析

當單表資料量超過千萬列，查詢效能與維護成本將成為瓶頸。MySQL 表分區將邏輯大表物理拆分為多個獨立分區，實現資料分層管理、查詢裁剪與並行操作。本文系統整理分區類型、建立語法、修改維護與監控指令。

分區核心前提

• 分區鍵必須包含在主鍵/唯一鍵中（MySQL 限制）。
• 分區運算式需返回整數值（整數型別、時間函式 YEAR()/TO_DAYS()、HASH/KEY 函式）。
• 單表最多 8192 個分區（含子分區）。

五大基礎分區類型語法範本

1. RANGE 分區 — 連續區間（最常用於時間序列）

CREATE TABLE sales (
    id INT NOT NULL AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

2. LIST 分區 — 離散值列舉（適合地區、部門、狀態碼）

CREATE TABLE employees (
    id INT NOT NULL,
    dept_id INT NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id, dept_id)
) PARTITION BY LIST (dept_id) (
    PARTITION p_hr   VALUES IN (10),
    PARTITION p_rd   VALUES IN (20, 30),
    PARTITION p_sale VALUES IN (40),
    PARTITION p_other VALUES IN (50, 60, 99)
);

3. HASH 分區 — 均勻分散（無明確業務邊界時）

CREATE TABLE logs (
    id BIGINT NOT NULL AUTO_INCREMENT,
    created_at DATETIME NOT NULL,
    msg TEXT,
    PRIMARY KEY (id, created_at)
) PARTITION BY HASH (YEAR(created_at)) PARTITIONS 8;

4. KEY 分區 — MySQL 內部哈希（支援非整數欄位）

CREATE TABLE users (
    uid VARCHAR(36) NOT NULL,
    email VARCHAR(100),
    PRIMARY KEY (uid)
) PARTITION BY KEY (uid) PARTITIONS 4;

5. 複合分區 — 二維劃分

-- RANGE + HASH：按年份分區，每年再按月雜湊
CREATE TABLE orders (
    id BIGINT NOT NULL,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH (MONTH(order_date)) SUBPARTITIONS 12 (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

既有表轉分區與動態維護

-- 非分區表轉為 RANGE 分區
ALTER TABLE trade_records
PARTITION BY RANGE (UNIX_TIMESTAMP(trade_time)) (
    PARTITION p202301 VALUES LESS THAN (UNIX_TIMESTAMP('2023-02-01')),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

-- 新增分區
ALTER TABLE trade_records ADD PARTITION (
    PARTITION p202302 VALUES LESS THAN (UNIX_TIMESTAMP('2023-03-01'))
);

-- 刪除分區（資料同步刪除）
ALTER TABLE trade_records DROP PARTITION p202201, p202202;

-- 合併分區
ALTER TABLE trade_records REORGANIZE PARTITION p202201, p202202 INTO (
    PARTITION p2022q1 VALUES LESS THAN (UNIX_TIMESTAMP('2022-04-01'))
);

-- 拆分分區
ALTER TABLE trade_records REORGANIZE PARTITION pmax INTO (
    PARTITION p202303 VALUES LESS THAN (UNIX_TIMESTAMP('2023-04-01')),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

分區健檢與優化指令

重新定義分區策略（不遺失資料）