非分割槽錶轉換成分割槽表以及注意事項

有時候剛開始設計的時候沒考慮到資料歸檔。資料歸檔不是簡單的匯出delete。因為一般我們的資料庫都是delete不釋放空間。而我們一般資料庫遇到空間不足，就不得不面對這個問題。當然也可以做碎片整理的空間回收。我不太喜歡這樣，尤其大的表的時候。MySQL一旦超過100G的，線上環境我都不敢去做。Oracle的線上回收我做過20T的表，碎片800G，回收了一次做了55個小時（不停機）

我個人覺得最好的方式是採用分割槽，至於是遷移分割槽還是歸檔分割槽都好說。一般都是截斷分割槽這樣操作，很快。但是如果一個表之前沒有做分割槽怎麼辦？

我們舉例

create table b (id int ,a varchar2(10),time date);普通堆表

模擬5條資料

insert into b values (1,'a',sysdate-120);

insert into b values (2,'b',sysdate-90);

insert into b values (3,'c',sysdate-60);

insert into b values (4,'d',sysdate-30);

insert into b values (5,'e',sysdate);

 

一般來說我們都要使用到時間。所以時間建立索引。

create index b1 on b (time);

 

一般表都有主鍵，在Oracle中應該使用業務單據號來作為主鍵，在MySQL中用ID自增無業務含義。在PG中按照Oracle的來就行。

 

Alter table b add constraint pkb primary key(id);

為B表增加主鍵，這裡注意一定要是自己命名的而不是資料庫自己命令的。因為正常主從都沒有問題，但是有OGG的場景這樣的話就是一個坑。後面我來講為什麼。

 

ALTER TABLE b MODIFY

PARTITION BY RANGE (time)

(

PARTITION b1 VALUES LESS THAN (TO_DATE ('2022-03-01', 'YYYY-MM-DD')),

PARTITION b2 VALUES LESS THAN (TO_DATE ('2022-04-01', 'YYYY-MM-DD')),

PARTITION b3 VALUES LESS THAN (TO_DATE ('2022-05-01', 'YYYY-MM-DD')),

PARTITION b4 VALUES LESS THAN (TO_DATE ('2022-06-01', 'YYYY-MM-DD')),

PARTITION b5 VALUES LESS THAN (TO_DATE ('2022-07-01', 'YYYY-MM-DD'))

) online

UPDATE INDEXES

(

pkb GLOBAL,

b1 LOCAL

);

這個就是線上把非分割槽錶轉換成分別表的命令。注意紅色部分我這裡指定了主鍵名，如果是自動的那麼就是自動的名字。上面講的坑就在這裡，因為在OGG場景下，目標端也就是下游資料庫的自動的名字和源端上游不一樣。執行這個命令時候就會引發OGG的中斷。

 

那麼這個是資料庫的問題還是OGG的問題？我覺得都不是，是熟悉產品的問題。執行完畢以後看到表已經變成分割槽的了。

這個時候再截斷過期資料只要drop partition就可以了，當然注意一個UPDATE GLOBAL INDEXES;否則會出現資料無法寫入的問題。