MySQL 分區表完全指南：類型選擇、運維操作與性能優化

分區表核心價值

• 並發統計：分區裁剪後並行掃描，聚合查詢提速數倍
• 秒級歸檔：DROP PARTITION 瞬間刪除歷史數據，無鎖、無碎片
• 存儲分離：熱數據 SSD、冷數據 HDD，成本優化
• 查詢裁剪：WHERE 條件自動定位分區，避免全表掃描

MySQL 5.7+ 分區查詢增強

類型適用場景分區鍵要求典型語法
RANGE時間序列、ID 範圍整數/日期轉整數PARTITION BY RANGE (YEAR(created_at))
LIST枚舉值（地區、狀態）離散值列表PARTITION BY LIST (region_id)
RANGE COLUMNS直接用日期/字符串無需函數日期、字符串、多列PARTITION BY RANGE COLUMNS (created_at)
LIST COLUMNS直接用字符串枚舉字符串、多列PARTITION BY LIST COLUMNS (status)
HASH均勻分布、無明確範圍整數/表達式轉整數PARTITION BY HASH (id) PARTITIONS 16
KEY非整數類型、含 NULL除 TEXT/BLOB 外所有類型PARTITION BY KEY (uuid) PARTITIONS 16


關鍵限制與規範

主鍵/唯一鍵包含分區列：分區列必須是主鍵或唯一鍵的一部分
存儲引擎一致：同表所有分區必須同引擎（不可混用 MyISAM/InnoDB）
不支持外鍵：分區表不可有 FK
分區名不區分大小寫，避免使用關鍵字
NULL 值處理：RANGE 視為最小值、LIST 必須顯式列出、HASH/KEY 視為 0


日期分區函數選擇
ALTER TABLE sales ADD PARTITION (
  PARTITION p202312 VALUES LESS THAN ('2024-01-01')
);

2. 刪除分區（數據同步刪除）
ALTER TABLE sales TRUNCATE PARTITION p202201;

4. 合併/拆分分區
ALTER TABLE sales REMOVE PARTITIONING;

6. 交換分區（與非分區表交換數據，極快）
CREATE TABLE access_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  url VARCHAR(500),
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at)  -- 主鍵包含分區列
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS (created_at) (
  PARTITION p202301 VALUES LESS THAN ('2023-02-01'),
  PARTITION p202302 VALUES LESS THAN ('2023-03-01'),
  ...
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 定時任務：每月 1 號刪除 13 個月前分區
-- ALTER TABLE access_log DROP PARTITION p202201;