MySQL 分區表深度實戰：分區類型選型、剪枝原理、跨磁碟分佈與避坑指南

分區核心價值

• 分區剪枝：WHERE 條件命中分區鍵 → 僅掃描相關分區，IO 大幅降低。
• 資料生命週期管理：DROP PARTITION 秒級歸檔刪除歷史資料，避免 DELETE 產生碎片與鎖爭用。
• 跨磁碟分佈：不同分區掛載不同物理裝置 (HDD/SSD/NVMe)，熱資料上 SSD、冷資料下 HDD，成本效能平衡。
• 並行查詢：5.6+ 支援分區並行掃描 (optimizer_switch=partitioned=on)。

分區類型選型決策樹

分區鍵類型
    │
    ├─ 數值/日期 且 需範圍查詢/歸檔 → RANGE (最常用)
    │       例：PARTITION BY RANGE (YEAR(created_at))
    │
    ├─ 數值/日期 且 需均勻分佈 → HASH / KEY
    │       例：PARTITION BY HASH (user_id) PARTITIONS 16
    │
    ├─ 離散枚舉值 (省份、業務類型) → LIST
    │       例：PARTITION BY LIST (region_id) 
    │
    └─ 組合場景 → SUBPARTITION (RANGE + HASH)
            例：RANGE (YEAR) SUBPARTITION HASH (user_id) 4

實戰範例：電商訂單表 RANGE 分區 + 子分區

CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  order_no VARCHAR(32) NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id, created_at),  -- 分區鍵必須包含在 PK/UK 中
  KEY idx_user_created (user_id, created_at),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at))
SUBPARTITION BY HASH (user_id)
SUBPARTITIONS 8 (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

分區剪枝驗證

EXPLAIN PARTITIONS SELECT * FROM orders WHERE created_at >= '2023-01-01' AND created_at < '2023-02-01';
-- 輸出 partitions: p2023 (僅掃描 2023 年分區)

EXPLAIN PARTITIONS SELECT * FROM orders WHERE user_id = 12345;
-- 輸出 partitions: p2020,p2021,p2022,p2023,p2024,pmax (全分區，因無分區鍵條件)

跨磁碟分佈實作 (DATA DIRECTORY / INDEX DIRECTORY)

僅支援 MyISAM；InnoDB 使用 innodb_file_per_table + 符號連結或表空間遷移。

-- MyISAM 範例
CREATE TABLE logs (
  id INT, dt DATE, msg TEXT
) ENGINE=MyISAM
PARTITION BY RANGE (YEAR(dt)) (
  PARTITION p2020 VALUES LESS THAN (2021) 
    DATA DIRECTORY '/mnt/ssd/mysql' INDEX DIRECTORY '/mnt/ssd/mysql',
  PARTITION p2021 VALUES LESS THAN (2022)
    DATA DIRECTORY '/mnt/hdd/mysql' INDEX DIRECTORY '/mnt/hdd/mysql'
);

InnoDB 替代方案：

# 1. 停止寫入該分區 (或整表)
# 2. 移動 .ibd 到目標磁碟
mv /var/lib/mysql/db/orders#P#p2020.ibd /mnt/ssd/mysql/
# 3. 建立符號連結
ln -s /mnt/ssd/mysql/orders#P#p2020.ibd /var/lib/mysql/db/orders#P#p2020.ibd
# 4. FLUSH TABLES 或重啟

生命週期管理：秒級歸檔與新增

-- 刪除 2020 年分區 (瞬間完成，無鎖、無碎片)
ALTER TABLE orders DROP PARTITION p2020;

-- 新增 2025 年分區 (預先建立，避免跨年寫入報錯)
ALTER TABLE orders ADD PARTITION (
  PARTITION p2025 VALUES LESS THAN (2026)
  SUBPARTITIONS 8
);

-- 合併分區 (重組)
ALTER TABLE orders REORGANIZE PARTITION p2020,p2021 INTO (
  PARTITION p2020_2021 VALUES LESS THAN (2022) SUBPARTITIONS 8
);

關鍵限制與避坑清單

監控與維護指標

-- 檢視分區資訊
SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA='db' AND TABLE_NAME='orders';

-- 定期 ANALYZE 更新統計 (每分區獨立)
ANALYZE TABLE orders;  -- 5.6+ 支援分區級

-- 檢查分區碎片
SELECT PARTITION_NAME, DATA_FREE FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='orders';

分區不是銀彈。適用於：單表 > 5000 萬行、明確的時間/業務維度、需快速歸檔、查詢模式固定命中分區鍵。否則增加優化器複雜度、DDL 鎖時間、統計資訊維護成本，得不償失。