MySQL 表分區技術深度解析：類型、語法與實戰限制

為什麼需要分區？

單表資料量達到百萬級以上時，查詢效能明顯下降。MySQL 5.1 引入分區功能，將邏輯上的一張大表，按規則拆分為多個物理檔案（可分佈不同磁碟），帶來以下優勢：

• 突破單檔案大小限制，存儲更多資料
• 秒級刪除過期分區（DROP PARTITION），無需逐行刪除
• 查詢自動修剪無關分區，大幅減少掃描行數
• 聚合函數（SUM/COUNT）可跨分區並行執行
• I/O 吞吐提升，利用多磁碟並行讀寫

檢查分區支援

-- MySQL 5.6 以前
SHOW VARIABLES LIKE '%partition%';
-- have_partition_engine = YES 表示支援

-- MySQL 5.6 及以上
SHOW PLUGINS;
-- partition | ACTIVE | STORAGE ENGINE 表示已啟用

五大核心分區類型與語法範例

1. RANGE 分區（範圍分區）

按連續區間劃分，常用於時間欄位。

CREATE TABLE range_p (
    perid INT, pername CHAR(12), monsalary DECIMAL(10,2), credate DATETIME
) PARTITION BY RANGE (YEAR(credate)) (
    PARTITION p2011 VALUES LESS THAN (2011),
    PARTITION p2012 VALUES LESS THAN (2012),
    PARTITION p2013 VALUES LESS THAN (2013),
    PARTITION p2014 VALUES LESS THAN (2014),
    PARTITION p2015 VALUES LESS THAN MAXVALUE
);

2. LIST 分區（列舉分區）

按離散值集合劃分，如性別、地區代碼。注意：分區值必須為整數。

CREATE TABLE list_p (
    perid INT, pername CHAR(12), sex INT, monsalary DECIMAL(10,2), credate DATETIME
) PARTITION BY LIST (sex) (
    PARTITION psex1 VALUES IN (1),
    PARTITION psex2 VALUES IN (2)
);

3. HASH 分區（哈希分區）

按運算式結果均勻分布，適合無明顯範圍特徵的欄位。

-- 對整數欄位哈希
CREATE TABLE hash_p (...) PARTITION BY HASH (perid) PARTITIONS 8;

-- 對時間函數哈希
CREATE TABLE hash_p (...) PARTITION BY HASH (YEAR(credate)) PARTITIONS 8;

4. KEY 分區（鍵值分區）

類似 HASH，但由 MySQL 內部哈希函數計算，支援非整數欄位（需含整數列）。

CREATE TABLE key_p (...) PARTITION BY KEY (perid) PARTITIONS 8;

5. COLUMNS 分區（5.5+ 增強）

直接支援日期、字串等非整數類型，可多欄位組合分區。

CREATE TABLE range_p (...) PARTITION BY RANGE COLUMNS (credate) (
    PARTITION p20151 VALUES LESS THAN ('2015-04-01'),
    PARTITION p20152 VALUES LESS THAN ('2015-07-01'),
    PARTITION p20153 VALUES LESS THAN ('2015-10-01'),
    PARTITION p20154 VALUES LESS THAN ('2016-01-01'),
    PARTITION partlog VALUES LESS THAN MAXVALUE
);

關鍵限制與避坑指南

1. 分區鍵必須包含主鍵：若表有主鍵/唯一索引，分區欄位必須是其子集，否則報錯 ERROR 1503。
2. 無全域索引：索引僅在分區內部，跨分區查詢無法利用全域索引。
3. RANGE 新增分區只能追加高端：不能插入中間或更小範圍的分區。
4. 分區類型不可互轉：REORGANIZE PARTITION 僅能調整同類型分區邊界。
5. NULL 值視為 0：建議分區欄位設 NOT NULL。

子分區（Subpartitioning）

在一級分區基礎上再次切分，如 RANGE + HASH 組合，實現更精細的資料分佈與磁碟映射。

總結

分區是大表優化的利器，但並非萬能。適用場景：按時間/範圍頻繁查詢、需定期清理舊資料、單表超大且修改操作較少。HASH/KEY 分區實際效用有限，生產環境多採用 RANGE/LIST + COLUMNS 組合。使用前務必測試驗證查詢計畫（EXPLAIN PARTITIONS）。