MySQL 基於時間欄位分區方案總結：RANGE COLUMNS 最佳實踐

MySQL 基於時間欄位分區方案總結：RANGE COLUMNS 最佳實踐

背景與目標

需對 DATETIME 類型欄位按天進行分區。MySQL 支援四大分區類型：RANGE、LIST、HASH、KEY，各有衍生子類型。本文針對「按天分區」需求，對比三種主流方案的分區裁剪效果。

測試資料準備

建立測試表 test，含 100 萬筆資料，hiredate 跨度 10 天（2015-12-01 ~ 2015-12-10），每天約 10 萬筆。

mysql> SELECT MIN(hiredate), MAX(hiredate) FROM test;
+---------------------+---------------------+
| MIN(hiredate)       | MAX(hiredate)       |
+---------------------+---------------------+
| 2015-12-01 00:00:00 | 2015-12-10 23:59:56 |
+---------------------+---------------------+

mysql> SELECT DATE(hiredate), COUNT(*) FROM test GROUP BY DATE(hiredate);
+----------------+----------+
| DATE(hiredate) | COUNT(*) |
+----------------+----------+
| 2015-12-01     |  99963   |
| 2015-12-02     | 100032   |
... (共 10 天)
| 2015-12-10     |  99902   |
+----------------+----------+

測試維度：分區裁剪

核心指標：執行 EXPLAIN PARTITIONS SELECT ... WHERE hiredate BETWEEN ... 時，partitions 欄位是否僅顯示相關分區（即實現 Partition Pruning）。

方案一：RANGE + TO_DAYS()

CREATE TABLE range_datetime (
  id INT, hiredate DATETIME
) PARTITION BY RANGE (TO_DAYS(hiredate)) (
  PARTITION p1 VALUES LESS THAN (TO_DAYS('20151202')),
  PARTITION p2 VALUES LESS THAN (TO_DAYS('20151203')),
  ...
  PARTITION p10 VALUES LESS THAN (TO_DAYS('20151211'))
);

結果： EXPLAIN 顯示 partitions: p7,p8,p9,p10 —— 成功裁剪。

方案二：RANGE COLUMNS（推薦）

CREATE TABLE range_columns (
  id INT, hiredate DATETIME
) PARTITION BY RANGE COLUMNS(hiredate) (
  PARTITION p1 VALUES LESS THAN ('20151202'),
  PARTITION p2 VALUES LESS THAN ('20151203'),
  ...
  PARTITION p10 VALUES LESS THAN ('20151211')
);

結果： 同樣顯示 p7,p8,p9,p10 —— 成功裁剪，且語法更直觀，無需函數轉換。

方案三：HASH + TO_DAYS()

CREATE TABLE hash_datetime (
  id INT, hiredate DATETIME
) PARTITION BY HASH(TO_DAYS(hiredate)) PARTITIONS 10;

結果： EXPLAIN 顯示 partitions: p0,p1,...,p9（全部分區）—— 無法裁剪，HASH 分區不支援範圍查詢裁剪。

TIMESTAMP 類型的特殊處理

TIMESTAMP 僅支援 UNIX_TIMESTAMP() 函數分區，使用 TO_DAYS() 會報錯：ERROR 1486 (HY000): Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed。

CREATE TABLE range_timestamp (
  id INT, hiredate TIMESTAMP
) PARTITION BY RANGE (UNIX_TIMESTAMP(hiredate)) (
  PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-02 00:00:00')),
  ...
  PARTITION p10 VALUES LESS THAN (UNIX_TIMESTAMP('2015-12-11 00:00:00'))
);

測試同樣實現分區裁剪（p7,p8,p9,p10）。

總結與建議

1. 首選 RANGE COLUMNS：語法簡潔、直接支援日期類型、無需函數轉換、分區裁剪完美支援。
2. MySQL 5.7 前，DATE/DATETIME 分區裁剪僅支援 YEAR()、TO_DAYS()；5.7 新增 TO_SECONDS()。
3. LIST 雖可實現按天分區，但需枚舉每一天值，維護成本高，不如 RANGE 適合。
4. TIMESTAMP 只能 使用 UNIX_TIMESTAMP() 進行分區，切勿嘗試其他函數。

參考資源

MySQL 官方文檔 - Partitioning