MySQL 5.1+ 分區表實戰：RANGE 分區建立、修改與合併

分區功能確認

SHOW VARIABLES LIKE 'have_partitioning';

回傳 `YES` 表示支援。MySQL 5.1+ 內建，5.7+ 為標準功能，8.0 持續增強。

編譯安裝啟用分區 (如未內建)

./configure --with-plugins=partition ...
make && make install

RANGE 分區建立範例

CREATE TABLE users_part (
  uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(30) NOT NULL DEFAULT '',
  email VARCHAR(30) NOT NULL DEFAULT ''
)
PARTITION BY RANGE (uid) (
  PARTITION p0 VALUES LESS THAN (10000)
    DATA DIRECTORY = '/data/part0' INDEX DIRECTORY = '/data/part0',
  PARTITION p1 VALUES LESS THAN (20000)
    DATA DIRECTORY = '/data/part1' INDEX DIRECTORY = '/data/part1',
  PARTITION p2 VALUES LESS THAN (30000)
    DATA DIRECTORY = '/data/part2' INDEX DIRECTORY = '/data/part2',
  PARTITION p3 VALUES LESS THAN MAXVALUE
    DATA DIRECTORY = '/data/part3' INDEX DIRECTORY = '/data/part3'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

關鍵點：

• 分區鍵需為整數或衍生整數表達式
• `VALUES LESS THAN` 遞增定義，最後用 `MAXVALUE` 兜底
• `DATA DIRECTORY`/`INDEX DIRECTORY` 可將分區分散至不同磁碟 (MyISAM/InnoDB 皆支援，需 `innodb_file_per_table=ON`)
• 目錄需預先建立且 mysql 進程有寫入權限

驗證分區效果

# 檔案系統層面會出現符號連結
ls -l /data/mysql/test/
# users_part#P#p0.ibd -> /data/part0/users_part#P#p0.ibd
# ...

# SQL 層面檢視
EXPLAIN PARTITIONS SELECT * FROM users_part WHERE uid = 15000;
-- 輸出 partitions 欄顯示 p1，確認分區剪枝生效

分區維護操作

拆分分區 (REORGANIZE PARTITION)

-- 將 p0 (0-9999) 拆分為 s0 (0-3999) 與 s1 (4000-9999)
ALTER TABLE users_part REORGANIZE PARTITION p0 INTO (
  PARTITION s0 VALUES LESS THAN (4000)
    DATA DIRECTORY = '/data/part0' INDEX DIRECTORY = '/data/part0',
  PARTITION s1 VALUES LESS THAN (10000)
    DATA DIRECTORY = '/data/part0' INDEX DIRECTORY = '/data/part0'
);

合併分區

-- 將 s0, s1 合併回 p0
ALTER TABLE users_part REORGANIZE PARTITION s0, s1 INTO (
  PARTITION p0 VALUES LESS THAN (10000)
    DATA DIRECTORY = '/data/part0' INDEX DIRECTORY = '/data/part0'
);

新增分區 (範圍擴展)

ALTER TABLE users_part ADD PARTITION (
  PARTITION p4 VALUES LESS THAN (40000)
    DATA DIRECTORY = '/data/part4' INDEX DIRECTORY = '/data/part4'
);

刪除分區 (資料同步刪除)

ALTER TABLE users_part DROP PARTITION p4;

截斷分區 (保留結構清空資料)

ALTER TABLE users_part TRUNCATE PARTITION p0;

其他分區類型速覽

分區表限制與注意事項

• 分區鍵必須包含在主鍵/唯一鍵中
• 外鍵不支援分區表
• 全文索引不支援分區表 (5.6+ 部分支援)
• 最大 8192 個分區 (含子分區)
• 查詢必須包含分區鍵條件才能觸發 **Partition Pruning (分區剪枝)**
• 避免 `SELECT * FROM table` 全表掃描所有分區

效能效益

• **I/O 隔離**：熱點分區置於快速儲存，冷資料置於大容量慢速儲存
• **維護靈活**：單分區 `OPTIMIZE`、`CHECK`、`REPAIR`、備份還原
• **刪除高效**：`DROP PARTITION` 秒級完成，避免大表 `DELETE` 鎖表產生碎片
• **並行查詢**：多核 CPU 可並行掃描多個分區 (需優化器支援)