MySQL 分區表完整指南：RANGE/LIST/HASH/KEY、增刪改分區、分區裁剪與局限

為什麼用分區表？

• 查詢裁剪：只掃描相關分區，大幅降低 I/O。
• 維護友好：DROP PARTITION 秒級歸檔舊數據，避免大事務 DELETE。
• 物理分佈：不同分區可掛載不同磁碟/存儲介質。

四大分區類型

RANGE 分區實戰：銷售表按月分區

CREATE TABLE sale_data (
  sale_date DATETIME NOT NULL,
  sale_item VARCHAR(2) NOT NULL,
  sale_money DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(sale_date)*100 + MONTH(sale_date)) (
  PARTITION p202401 VALUES LESS THAN (202402),
  PARTITION p202402 VALUES LESS THAN (202403),
  PARTITION p202403 VALUES LESS THAN (202404),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

分區管理 DDL

-- 新增分區
ALTER TABLE sale_data ADD PARTITION (PARTITION p202404 VALUES LESS THAN (202405));

-- 刪除分區（連同資料一起刪，極快）
ALTER TABLE sale_data DROP PARTITION p202401;

-- 合併分區
ALTER TABLE sale_data REORGANIZE PARTITION p202401,p202402,p202403 INTO (
  PARTITION p2024Q1 VALUES LESS THAN (202404)
);

-- 拆分分區
ALTER TABLE sale_data REORGANIZE PARTITION p2024Q1 INTO (
  PARTITION p202401 VALUES LESS THAN (202402),
  PARTITION p202402 VALUES LESS THAN (202403),
  PARTITION p202403 VALUES LESS THAN (202404)
);

-- 截斷分區資料（保留定義）
ALTER TABLE sale_data TRUNCATE PARTITION p202401;

子分區：不同物理磁碟

CREATE TABLE ts (
  id INT, purchased DATE
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(purchased))
SUBPARTITION BY HASH (id)
(
  PARTITION p0 VALUES LESS THAN (1990) (
    SUBPARTITION s0 DATA DIRECTORY='/disk1' INDEX DIRECTORY='/idx1',
    SUBPARTITION s1 DATA DIRECTORY='/disk2' INDEX DIRECTORY='/idx2'
  ),
  PARTITION p1 VALUES LESS THAN (MAXVALUE) (
    SUBPARTITION s2 DATA DIRECTORY='/disk3',
    SUBPARTITION s3 DATA DIRECTORY='/disk4'
  )
);

分區裁剪驗證

EXPLAIN PARTITIONS SELECT * FROM sale_data WHERE sale_date < '2024-03-01';
-- partitions 欄位顯示 p202401,p202402 → 裁剪生效

局限與避坑

1. 所有分區必須使用相同儲存引擎。
2. 唯一索引/主鍵必須包含分區鍵列。
3. 不支援外鍵。
4. 分區運算式函數受限（YEAR()、TO_DAYS()、UNIX_TIMESTAMP() 等）。
5. LOAD INDEX INTO CACHE 不支援。
6. 鎖升級：某些操作仍會鎖全表分區。
7. 不總是提升性能——需 EXPLAIN PARTITIONS 實測。

選型建議

• 時間序列、定期歸檔 → RANGE + DROP PARTITION。
• 固定枚舉（省份、業務線） → LIST。
• 大表無明顯維度、需均勻分散 → HASH/KEY（分區數建議 2 的冪次）。

分區表是大數據量單表優化的利器，善用「分區裁剪」與「秒級歸檔」兩大核心價值。