MySQL 表分區技術深度解析：類型、語法、索引限制、分區修剪與實戰總結

MySQL 表分區技術深度解析：類型、語法、索引限制、分區修剪與實戰總結

MySQL 5.1+ 以插件形式引入分區功能，5.6 後更穩定。本文系統整理分區支援檢查、五大類型語法、COLUMNS 分區、子分區、索引限制、分區修剪原理、維護優勢與避坑指南。

分區支援檢查

-- 5.6 前
SHOW VARIABLES LIKE '%partition%';  -- have_partition_engine=YES

-- 5.6 後
SHOW PLUGINS;  -- partition | ACTIVE | STORAGE ENGINE

分區優勢

1. 突破單檔案/檔案系統大小限制
2. 歷史資料秒級清理（DROP PARTITION）
3. 查詢分區修剪極大減少掃描範圍
4. 聚合函數可並行執行
5. I/O 吞吐提升，可跨磁碟分佈

五大分區類型完整語法

COLUMNS 分區（5.5+）

直接使用 DATE、DATETIME、CHAR、VARCHAR 等非整數型別分區，無需轉換函數：

PARTITION BY RANGE COLUMNS (credate) (
  PARTITION p20151 VALUES LESS THAN ('2015-04-01'),
  PARTITION p20152 VALUES LESS THAN ('2015-07-01'),
  PARTITION partlog VALUES LESS THAN MAXVALUE
);

子分區

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/INDEX DIRECTORY 實現多磁碟分離。

核心限制與避坑（必讀）

1. 分區鍵必須包含在主鍵/唯一索引中：ERROR 1503: A PRIMARY KEY MUST INCLUDE ALL COLUMNS IN THE TABLE'S PARTITIONING FUNCTION
2. NULL 值視為 0：RANGE/LIST 會將 NULL 歸入最小分區，建議 NOT NULL
3. 無全域索引：查詢無分區鍵 = 全分區掃描，效能災難
4. RANGE 新增分區只能加高端：ADD PARTITION 僅支援 MAXVALUE 之後
5. REORGANIZE PARTITION 僅能合併相鄰 RANGE 分區，不可跨類型轉換
6. HASH/KEY 實際業務價值有限：修剪效果弱，多用於均勻分佈存儲

分區修剪原理

優化器根據 WHERE 條件中的分區鍵，推斷僅需掃描的分區集合。務必在 WHERE 子句包含分區鍵，即使看似多餘，否則退化為全表掃描。

維護實戰建議

• 適用場景：按時間序列寫入、定期清理歷史資料、頻繁按時間範圍查詢
• 不適用：頻繁 UPDATE 分區鍵、需要全域唯一索引、查詢模式無固定範圍
• 分區數建議 10~100，過多增加優化器開銷
• 監控 EXPLAIN PARTITIONS 驗證修剪生效

官方文檔：MySQL Partitioning