前言

在系列文 MySQL 系列文 - Partition Table(2) - 在既有的 TABLE 上建立 partition 中有舉了一個範例是用日來做分區。 當然相信有很多人都有試過要用日來做切割，但是可能會遇到一些狀況。本篇文章就舉幾個我遇到的問題分享給大家，或許在建置過程中有遇到一樣的問題可以參考。

日期 type 使用 datetime

測試資料

create table pt1
(
id int ,
ctime datetime,
primary key(ctime)
)
partition by range(TO_DAYS(ctime))
(
partition p0 values less than (TO_DAYS('2020-01-01')),   # 資料在 < 2020-01-01
partition p1 values less than (TO_DAYS('2020-02-01'))    # 資料在 >= 2020-01-01 and < 2020-02-01 
);

insert into pt1 values(1,'2019-10-05');
insert into pt1 values(1,'2020-01-02');
insert into pt1 values(1,'2020-01-05');

查一下分區狀況

• 看起來沒問題，p0: 有一筆資料 p1: 有兩筆資料

select table_schema, table_name, partition_name,partition_method, partition_description, table_rows
from information_schema.partitions
where table_schema = 'test2' and table_name = 'pt1'

圖1

這時候來搜尋看看是不是真的如我們所願

• 下面的時間範圍應該只要 scan p1 這個 partition 就好。
  但是看下圖2,卻 p1,p2 兩個 partition 都去找了。

explain
select * from pt1
where ctime<'2020-02-01' and ctime > '2020-01-01' 

圖2

日期 type 使用 timestamp (確保可以用正確的 partition)

測試資料

create table pt2
(
id int ,
ctime TIMESTAMP,
primary key(ctime)
) ENGINE=InnoDB
partition by range(unix_timestamp(ctime))
(
partition p0 values less than (unix_timestamp('2020-01-01 00:00:00')), # 資料在 < 2020-01-01
partition p1 values less than (unix_timestamp('2020-02-01 00:00:00')) # 資料在 >= 2020-01-01 and < 2020-02-01
);

insert into pt2 values(1,'2019-10-05');
insert into pt2 values(1,'2020-01-02');
insert into pt2 values(1,'2020-01-15');

查一下分區狀況

• 看起來沒問題，p0: 有一筆資料 p1: 有兩筆資料

select table_schema, table_name, partition_name,partition_method, partition_description, table_rows
from information_schema.partitions
where table_schema = 'test2' and table_name = 'pt2'

圖3

這時候來搜尋看看是不是真的如我們所願

• 下面的時間範圍應該只要 scan p1 這個 partition 就好。
  看下圖4, 的確只找了 p1 這個 partition 。

explain
select * from pt2
where ctime<'2020-02-01' and ctime > '2020-01-01'  

圖4

小結

以上兩種方式雖然都可以寫到正確的 partition ，但是用 datetime 的時候在 select 時會用不到指定的 partition。目前是沒有找到原因，只知道改用 timestamp 是可以成功的，知道的也麻煩留言給我啦!
當然 datetime 和 timestamp 這兩個時間類型各有各的優缺點，大家可以自己 google 了解一下，我這裡只是提供我的一個解法給大家參考，如果有更好的解法也請各位分享給我。

資料庫知識相當廣泛，文中若有不正確的地方，也煩請各位大神不吝指教，謝謝