MySQL 基於時間字段分區方案總結：RANGE、RANGE COLUMNS、HASH、TIMESTAMP 專用 UNIX_TIMESTAMP 實測對比

MySQL 基於時間字段分區方案總結與實測對比

一、分區類型速覽

二、測試數據集

• 表 test(id, hiredate DATETIME)，100 萬行
• hiredate 僅覆蓋 10 天：2015-12-01 ~ 2015-12-10，每天約 10 萬行

三、方案 1：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 SELECT * FROM range_datetime
WHERE hiredate >= '20151207124503' AND hiredate <= '20151210111230';
-- partitions: p7,p8,p9,p10  ✅ 裁剪生效

四、方案 2：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')
);

優勢： 無需函數轉換、直接支援 DATETIME/DATE/字符串列、多列分區、元組比較。

分區裁剪驗證： 同樣得到 p7,p8,p9,p10 ✅

五、方案 3：HASH + TO_DAYS()

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

分區裁剪驗證：

EXPLAIN PARTITIONS SELECT * FROM hash_datetime
WHERE hiredate >= '20151207124503' AND hiredate <= '20151210111230';
-- partitions: p0,p1,p2,p3,p4,p5,p6,p7,p8,p9  ❌ 全分區掃描，無裁剪

HASH 分區對範圍查詢**無法裁剪**，僅適合等值點查詢或負載均衡寫入場景。

六、TIMESTAMP 專用方案：RANGE + UNIX_TIMESTAMP()

MySQL 限制：TIMESTAMP 分區**只能**使用 UNIX_TIMESTAMP()，其他函數報錯 ERROR 1486。

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 ✅

七、總結與選型建議

個人強烈傾向 RANGE COLUMNS：語義清晰、維護簡單、效能最優。LIST 分區雖可實現同樣按天分區，但需枚舉每天值，極不靈活。