MySQL 分區技術詳解：RANGE 與 LIST 分區實戰指南

MySQL 分區技術詳解：RANGE 與 LIST 分區實戰指南

MySQL 分區功能自 5.1 版本起正式引入，透過將大型表拆分為更小、更易管理的片段，能顯著提升查詢效能與維護彈性。本文深入解析兩大核心分區類型——RANGE 與 LIST，並附上完整建表範例與常見坑點分析。

一、RANGE 分區：按數值區間劃分資料

RANGE 分區依據欄位值落入的指定區間，自動將資料導向對應分區。典型應用場景包括按時間、ID 範圍歸檔歷史資料。

CREATE TABLE emp (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name CHAR(25) NOT NULL DEFAULT ''
) PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (100),   -- 0-99
    PARTITION p1 VALUES LESS THAN (200),   -- 100-199
    PARTITION p2 VALUES LESS THAN (300)    -- 200-299
);

關鍵注意事項：

• 預設包含 0 值，實際儲存範圍為 0-99 或負數；
• 分區欄位若包含於主鍵中，則必須是主鍵的一部分；
• MySQL 5.5+ 支援日期型別直接分區，極適合時間序列資料歸檔。

二、LIST 分區：按離散值清單劃分資料

LIST 分區適用於欄位值為固定枚舉集合的場景，如部門編號、狀態碼、分類標籤等。

CREATE TABLE list_emp (
    tid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    tname CHAR(20)
) PARTITION BY LIST (tid) (
    PARTITION p0 VALUES IN (1,3,5,7,9),
    PARTITION p1 VALUES IN (2,4,6,8,10)
);

核心差異： RANGE 定義連續區間，LIST 定義離散值集合。LIST 分區若插入未在清單中的值（如 tid=11），會直接報錯 ERROR 1526 (HY000): Table has no partition for value 11，資料寫入失敗。

三、分區與主鍵衝突的實戰對策

InnoDB 若無顯式主鍵，會自動生成 6 字節隱藏聚集索引（RowID）。若分區欄位非主鍵，可能導致主鍵與分區鍵不一致，影響效能。建議：

1. 依業務邏輯評估是否移除主鍵，改用分區欄位作主鍵；
2. 若保留主鍵，確保分區欄位包含於主鍵中；
3. 適當擴大分區範圍、維持順序寫入，可緩解隱藏索引帶來的效能損耗。

四、現有表如何新增分區？

MySQL 5.1+ 支援 ALTER TABLE ... PARTITION BY ... 直接為現有表添加分區，操作過程會重組資料，大表建議低峰期執行。