iT邦幫忙

2021 iThome 鐵人賽

DAY 25
0
Software Development

MYSQL-相關實務操作學習紀錄系列 第 25

Day.25 提升大數據資料管理 - 資料表分區 ( MYSQL Partition)_2

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

範例表結構:

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');  
    

    https://ithelp.ithome.com.tw/upload/images/20211001/2013088095PlkPwaJf.png


  • 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個分區)
      https://ithelp.ithome.com.tw/upload/images/20211001/20130880IR9imjo2RU.png

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

    mysql>     SELECT * FROM act PARTITION (p2sp0);
    

    https://ithelp.ithome.com.tw/upload/images/20211001/20130880jmTwkdPr6b.png


    管理分區的指令:

(現有表結構)

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
    );
    
    

上一篇
Day.24 提升大數據資料管理 - 資料表分區 ( MYSQL Partition)
下一篇
Day.26 實務應用 - 實作表自動分區管理( event / procedure / partition )_1
系列文
MYSQL-相關實務操作學習紀錄30

尚未有邦友留言

立即登入留言