今天延續昨天的內容，繼續介紹表分區類型～明天呢我們就將資料表分區的流程實際用到實務上使用，達到每天自行清除舊資料與新建後續分區的作業。

範例表結構:

CREATE TABLE `act` (
  `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '活動ID',
  `status` bigint(20) DEFAULT NULL COMMENT '活動狀態 0(下架) 1(上架) 2(準備中)',
  `start_time` int(11) unsigned NOT NULL,
  `end_time` int(11) unsigned NOT NULL,
  `platform_id` bigint(20) DEFAULT NULL,
  `updated_at` int(11) unsigned NOT NULL,
  `created_at` int(11) unsigned NOT NULL,
  PRIMARY KEY (`actives_id`)
);

• hash

  • Hash partition : 讓資料在指定分區個數下均勻分佈。
  • partition key: 值要是整數型別。

  (1.) 將資料使用hash分區分成5個partition。

  ALTER TABLE act
  PARTITION BY HASH (actives_id) 
  PARTITIONS 5;
  

  (2.) 一次插入10筆資料，看數據分佈狀態。

  INSERT INTO `act` ( `status`, `start_time`, `end_time`, `platform_id`, `updated_at`, `created_at`) VALUES ( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '5', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200');  
  

  

  ---

• key

  • Key partition : 類似於hash分區的方式，但資料存放的運算方式不同。

  (1.) 將資料使用key分區分成5個partition。

  ALTER TABLE act
  PARTITION BY KEY (actives_id) 
  PARTITIONS 5;
  

  ps. 有PK或unique key的狀況下分區欄位可不指定～

  ALTER TABLE act
  PARTITION BY KEY () 
  PARTITIONS 5;
  

• sub

  • Sub partition : 分區下在細分一次。簡單來說就是分區在分區(切割2次)

  (1.) 使用actives_id進行sub分區

  ALTER TABLE act
  PARTITION BY RANGE(actives_id)
  SUBPARTITION BY KEY (actives_id)
  SUBPARTITIONS 3 (
  PARTITION p0 VALUES LESS THAN (5),
  PARTITION p1 VALUES LESS THAN (10),
  PARTITION p2 VALUES LESS THAN (15),
  PARTITION pmax VALUES LESS THAN MAXVALUE
  );
  

  (2.) 插入10筆數據

  INSERT INTO `act` ( `status`, `start_time`, `end_time`, `platform_id`, `updated_at`, `created_at`) VALUES ( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '0', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '1', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200'),( '5', '1611504000', '1632931200', '10868213102191', '1632499200', '1632499200');  
  

  • 數據分佈狀態可以看到，在我們設定的每個分區下又個別拆分成3個。 (4*3 = 12個分區)
    

  • 查詢其中某一個分區的方式，以上圖標示的那筆p2紀錄來說～

  mysql>     SELECT * FROM act PARTITION (p2sp0);
  

  

  ---

  管理分區的指令:

(現有表結構)

CREATE TABLE `act` (
 `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `status` bigint(20) DEFAULT NULL COMMENT ' 0() 1() 2()',
 `start_time` int(11) unsigned NOT NULL,
 `end_time` int(11) unsigned NOT NULL,
 `platform_id` bigint(20) DEFAULT NULL,
 `updated_at` int(11) unsigned NOT NULL,
 `created_at` int(11) unsigned NOT NULL,
 PRIMARY KEY (`actives_id`)
)
PARTITION BY RANGE (actives_id)(
PARTITION p0 VALUES LESS THAN (5),
PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (15),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

• 在現有分區中新增分區

1. (沒有) 設定MAXVALUE分區的狀況下直接新增

   ps.注意只能根據最後一個分區去做往後新增動作！

   ALTER TABLE act ADD PARTITION (PARTITION p3 VALUES LESS THAN(20));
   

2. (有) 設定MAXVALUE分區的狀況下重組拆分分區

   ALTER TABLE act REORGANIZE PARTITION pmax INTO (
    PARTITION p3 VALUES LESS THAN(20),
    PARTITION pmax VALUES LESS THAN MAXVALUE
   );
   

   補充: 如果要合併分區

   
   ALTER TABLE act REORGANIZE PARTITION p3,pmax INTO (
     PARTITION pmax VALUES LESS THAN MAXVALUE
   );