MySQL 分區技術完整指南：類型、創建與管理實戰

MySQL 分區技術概述

MySQL 分區是將大型表按邏輯劃分為多個較小、更易管理的物理片段（分區）的技術。其核心優勢在於：提升查詢性能（透過分區裁剪僅掃描相關分區）與簡化數據維護（如快速刪除歷史分區、獨立備份恢復）。MySQL 5.1 起正式支持，常見於數據倉儲、日誌分析、大數據量業務表。

檢查版本支持

執行以下命令確認實例是否啟用分區功能：

SHOW VARIABLES LIKE '%partition%';

若返回 have_partitioning = YES 即可使用。

五大核心分區類型詳解

1. RANGE 分區 —— 連續區間劃分

根據列值落在指定的連續範圍內分配行。典型場景：按時間（年/月）、ID 範圍存儲歷史數據。

CREATE TABLE emp_age_range (
    empno VARCHAR(20) NOT NULL,
    empname VARCHAR(20),
    deptno INT,
    age INT
)
PARTITION BY RANGE (age) (
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (20),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

進階：可對表達式分區，如 PARTITION BY RANGE (YEAR(birthdate)) 按出生年份劃分。

2. LIST 分區 —— 離散值匹配

類似 RANGE，但基於枚舉值集合。適合狀態碼、地區代碼等固定枚舉場景。

CREATE TABLE emp_deptno_list (
    empno VARCHAR(20) NOT NULL,
    empname VARCHAR(20),
    deptno INT,
    birthdate DATE NOT NULL,
    salary INT
)
PARTITION BY LIST (deptno) (
    PARTITION p1 VALUES IN (10),
    PARTITION p2 VALUES IN (20),
    PARTITION p3 VALUES IN (30)
);

3. HASH 分區 —— 自定義表達式哈希

通過用戶定義表達式（返回非負整數）將數據均勻分布到指定數量分區。適合無明顯範圍/枚舉特徵、需均衡 I/O 的場景。

CREATE TABLE emp_birthday_hash (
    empno VARCHAR(20) NOT NULL,
    empname VARCHAR(20),
    deptno INT,
    birthdate DATE NOT NULL,
    salary INT
)
PARTITION BY HASH (YEAR(birthdate))
PARTITIONS 4;

4. KEY 分區 —— 內置哈希函數

類似 HASH，但由 MySQL 內部哈希函數計算，僅支持列（需含整數類型），無需編寫表達式。

CREATE TABLE emp_birthdate_key (
    empno VARCHAR(20) NOT NULL,
    empname VARCHAR(20),
    deptno INT,
    birthdate DATE NOT NULL,
    salary INT
)
PARTITION BY KEY (birthdate)
PARTITIONS 4;

5. 復合分區 —— 多層劃分

結合兩種類型：RANGE-HASH、RANGE-KEY、LIST-HASH、LIST-KEY。第一層按範圍/列表劃分大區，第二層在分區內再做哈希/鍵分區，進一步均衡數據與並行度。

-- RANGE + HASH 示例
CREATE TABLE emp_birthdate_range_hash (
    empno VARCHAR(20) NOT NULL,
    empname VARCHAR(20),
    deptno INT,
    birthdate DATE NOT NULL,
    salary INT
)
PARTITION BY RANGE (salary)
SUBPARTITION BY HASH (YEAR(birthdate))
SUBPARTITIONS 3 (
    PARTITION p1 VALUES LESS THAN (2000),
    PARTITION p2 VALUES LESS THAN MAXVALUE
);

分區表管理與監控

常用檢查命令

• SHOW CREATE TABLE table_name —— 查看完整建表語句含分區定義
• SHOW TABLE STATUS —— 確認表是否為分區表
• SELECT * FROM information_schema.partitions WHERE table_schema = SCHEMA() AND table_name = 'table_name' —— 獲取分區名、表達式、描述、行數等元數據
• EXPLAIN PARTITIONS SELECT ... —— 分析查詢掃描了哪些分區（驗證分區裁剪生效）

實戰輸出示例

查詢 information_schema.partitions 可清晰看到每個分區的 partition_name、partition_expression、partition_description 及 table_rows，便於監控數據分佈是否均勻。

最佳實踐建議

1. 選擇合適分區鍵：優先考慮 WHERE 條件常用列、時間序列列，確保分區裁剪生效。
2. 控制分區數量：過多（>100）會增加優化器開銷，建議按業務增長預估規劃。
3. 避免 NULL 值：分區鍵列最好定義為 NOT NULL，否則需額外處理。
4. 定期維護：利用 ALTER TABLE ... DROP PARTITION 快速清理過期數據；REORGANIZE PARTITION 調整範圍。
5. 索引策略：分區表的局部索引（每個分區獨立索引）通常優於全局索引，需根據查詢模式設計。

掌握 MySQL 分區技術，能讓 TB 級大表的查詢與維護變得輕鬆可控，是高性能架構的關鍵利器之一。