MySQL 分區表技術深度解析：原理、類型、管理與性能實測

MySQL 5.1 引入分區表，將大表按規則拆分為物理獨立的分區，實現數據劃歸、歷史清理、跨磁盤 I/O 分攤。本文系統講解分區原理、四大類型、管理操作、實測對比及生產避坑指南。

分區核心概念

• 分區函數： 接收表達式（整數列或返回整數的函數），返回分區編號（從 0 開始遞增）。
• 本地索引： 每個分區獨立維護索引，無全局索引。故表若有 ≥2 個唯一索引，或分區鍵不包含主鍵列，無法分區（報錯 #1503）。
• 存儲引擎： 同一分區表所有分區必須用同一引擎；不同分區表可用不同引擎。
• 分區剪枝： 優化器根據 WHERE 條件推斷僅需掃描的分區，跳過無關分區，大幅減少 I/O。

四大分區類型

子分區： 分區內再劃分，每個分區子分區數量必須一致；若顯式定義一個分區的子分區，必須定義所有分區的子分區。

分區管理 DDL

RANGE / LIST

-- 刪除分區（數據同步刪除，極快）
ALTER TABLE t DROP PARTITION p20110809;

-- 新增分區（只能加在高端，範圍需連續）
ALTER TABLE t ADD PARTITION (PARTITION p20110819 VALUES LESS THAN (734734));

-- 合並/拆分重組（僅相鄰分區）
ALTER TABLE t REORGANIZE PARTITION p0,p1 INTO (
  PARTITION p0 VALUES LESS THAN (100),
  PARTITION p1 VALUES LESS THAN (200)
);

HASH / KEY

-- 增加分區數
ALTER TABLE t ADD PARTITION PARTITIONS 4;

-- 合並分區（COALESCE，非 DROP）
ALTER TABLE t COALESCE PARTITION 2;

查看分區信息

SELECT * FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = SCHEMA() AND TABLE_NAME = 't';

實測對比：1708 萬行 InnoDB 表（RANGE 按天分區 vs 不分區）

EXPLAIN PARTITIONS 關鍵指標

• partitions 列顯示實際掃描的分區列表；NULL 表示全表掃描。
• rows 估算掃描行數，分區剪枝生效時大幅下降。

生產避坑指南

• MySQL 5.1 分區尚不成熟： 5.1.22 仍缺分區級 OPTIMIZE/REPAIR/CHECK，空間回收需整表操作。
• 唯一索引限制： 主鍵/唯一鍵必須包含分區列，否則報錯。
• RANGE 新增分區只能追加高端，不可插入中間。
• REORGANIZE 僅相鄰分區，不可跨越，不可改變分區類型/表達式。
• HASH/KEY 分區實際業務價值有限，建議優先 RANGE/LIST。

最佳實踐場景

1. 日誌/監控/統計類大表，按天/月 RANGE 分區，定期 DROP PARTITION 清理歷史。
2. 查詢高頻帶分區鍵條件（時間範圍），充分利用分區剪枝。
3. 寫入為主、修改少、需跨磁盤分佈存儲降低單盤 I/O 壓力。