iT邦幫忙

0

MySQL 系列文 - Partition Table(4) - partition by 日期 方式

前言

在系列文 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'

https://ithelp.ithome.com.tw/upload/images/20200517/20124671ui5jqM5ZxN.png
圖1

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

  • 下面的時間範圍應該只要 scan p1 這個 partition 就好。
    但是看下圖2,卻 p1,p2 兩個 partition 都去找了。
explain
select * from pt1
where ctime<'2020-02-01' and ctime > '2020-01-01' 

https://ithelp.ithome.com.tw/upload/images/20200517/20124671byS95Oj0MY.png
圖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'

https://ithelp.ithome.com.tw/upload/images/20200517/20124671y6PqILwsjH.png
圖3

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

  • 下面的時間範圍應該只要 scan p1 這個 partition 就好。
    看下圖4, 的確只找了 p1 這個 partition 。
explain
select * from pt2
where ctime<'2020-02-01' and ctime > '2020-01-01'  

https://ithelp.ithome.com.tw/upload/images/20200517/20124671ExmX7VjJAW.png
圖4

小結

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

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


圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

1
jeremy_h_l
iT邦新手 5 級 ‧ 2020-11-23 01:52:49

如果是 datatime 類型 partition by range(TO_DAYS(ctime)) 可以換個等效 where 條件查:

where ctime < '2020-02-01' and ctime > '2020-01-01' 

換成

where ctime <= '2020-01-31' and ctime > '2020-01-01'

就會只用到 p1 分區。

我要留言

立即登入留言