MySQL 分區表優化實戰：從建表到維護的完整指南

大表優化三大策略對比

前置檢查：確認分區支援

SHOW PLUGINS;
-- partition | ACTIVE | STORAGE ENGINE 表示可用

建立分區表（必須建表時指定，無法 ALTER 普通表轉分區）

CREATE TABLE T_part (
    f_id INT, f_name VARCHAR(20), PRIMARY KEY (f_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE (f_id) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20)
);

注意：`f_id >= 20` 的資料將無法寫入（報錯），實務上最後一個分區常用 `MAXVALUE` 兜底。

分區管理 DDL

-- 新增分區
ALTER TABLE T_part ADD PARTITION (PARTITION p2 VALUES LESS THAN (MAXVALUE));
-- 刪除分區（資料同步刪除，謹慎操作）
ALTER TABLE T_part DROP PARTITION p2;

五大分區類型速查

1. RANGE：連續區間，適合時間、自增 ID。
2. LIST：離散值列表，適合狀態、地區代碼（僅整數）。
3. HASH：使用者自定義運算式均勻分布。
4. LINEAR HASH：線性 2 的冪次運算，分區修剪更快。
5. KEY：MySQL 內部哈希函數，支援非整數欄位。

子分區：多維度分佈

CREATE TABLE T_part (...) 
PARTITION BY RANGE (f_id)
SUBPARTITION BY HASH (f_id) SUBPARTITIONS 2 (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20)
);

可將不同子分區映射至不同磁碟目錄（`DATA DIRECTORY` / `INDEX DIRECTORY`），實現物理 I/O 隔離。

核心限制與避坑

• 分區鍵必須包含主鍵/唯一鍵的所有欄位。
• 無全域索引，跨分區查詢無法利用全局索引。
• NULL 值視為 0，建議分區欄位 `NOT NULL`。
• RANGE 分區只能 `ADD PARTITION` 追加高端，不能插入中間。
• 不支援外鍵約束。

效能優化關鍵：分區修剪

查詢 WHERE 條件必須包含分區鍵，優化器才能跳過無關分區。否則退化為全分區掃描（類似合併表），大表上極慢。

總結

分區表是「低代碼侵入」的大表優化利器，核心價值在於 **I/O 隔離、快速歸檔清理、分區修剪加速查詢**。適用於「寫入較少、按時間/範圍查詢、需定期清理歷史資料」的場景（如日誌、流水、統計表）。生產上線前務必用 `EXPLAIN PARTITIONS` 驗證執行計畫。