MySQL Partition

 

Partition

官方文件

用來把大表資料分區，分不同檔案存放。

Partitioning enables you to distribute portions of individual tables 
across a file system according to rules which you can set largely as needed. 
In effect, different portions of a table are stored as separate tables 
in different locations. — MySQL 5.7

Partitioning 方式

MySQL partitioning 支援 4 種分區方式，分別是 range、list、hash、key，
要採用哪種分區方式要取決於需求以及資料本身特性，
如果選到不適合的分區方式，效能甚至會不進反退。
不同的分區方式，也有不同的維護方法，在決定方式前一定都要考量到

• Range：範圍分區。例如用時間當做 partition key，每一個月為一個分區。

CREATE TABLE table (
    id INT NOT NULL,
    data varchar(255) NOT NULL
    timeMS timestamp
);
//用id 當 range
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (11),
    PARTITION p1 VALUES LESS THAN (21),
    PARTITION p2 VALUES LESS THAN (31),
    PARTITION p3 VALUES LESS THAN MAXVALUE);

//用 timeMS 當 range
PARTITION BY RANGE (timeMs) (
    PARTITION p0 VALUES LESS THAN (1430409600000),
    PARTITION p201505 VALUES LESS THAN (1433088000000),
    ...中略
    PARTITION p202005 VALUES LESS THAN (1590940800000),
    PARTITION p202006 VALUES LESS THAN (1593532800000),
    PARTITION p202007 VALUES LESS THAN (1596211200000),
    PARTITION pMax VALUES LESS THAN MAXVALUE); 
      
    //p0 為台灣時區 2015/5/1 以前的資料
    //p201505 為台灣時區 2015/05 的資料 (2015/5/1 ~ 2015/5/31)，而後每個月依此類推，
    //p202007 為台灣時區 2020/07 的資料 (2020/7/1 ~ 2020/7/31)
    //pMax 為台灣時區 2020/8/1 之後的資料，
    //pMax 不應該有資料，因為會預先建立未來月份的分區，讓資料寫入對應月份分區。
    //pMax 有兩個目的，其一個是捕捉不預期時間的資料，例如未來時間，另一個則是為了用來建立新分區
  

若是InnoDB，且 innodb_file_per_table 有開啟，
產生的分區檔案：table#p#p0.ibd、table#p#p1.ibd、table#p#p2.ibd、table#p#p3.ibd

• List：用預設的集合分區，指定集合內的元素分別屬於哪個分區。例如以國家當做 partition key，自定義集合如下圖，國家代碼 1 就會屬於 partition 1。

CREATE TABLE tb (
    id INT NOT NULL,
    data varchar(255) NOT NULL
);
PARTITION BY LIST (id) (
    PARTITION pA VALUES IN (1,3,5),
    PARTITION pB VALUES IN (12,15));

id 等於 1、3、5，分在 pA 區
id 等於 12、15，分在 pB 區
如果寫入的資料不在這些範圍會出錯。

• Hash：指定分區數量 N，以單一 int 欄位或
  以回傳 int 的表達式結果 mod N決定資料屬於哪個分區。

CREATE TABLE tb (
    id INT NOT NULL,
    mydate DATE NOT NULL DEFAULT '1970-01-01'
);
PARTITION BY HASH(mydate)
PARTITIONS 3;
  

依據 mydate 進行 HASH 分區在 3 個 partition。
也可以使用函式，例如 PARTITION BY HASH(YEAR(mydate))，
每次有異動，YEAR()都會執行一次，所以複雜的運算會影響效能。

• Key：指定分區數量 N，支援多個欄位為 partition key，用內建 hash function 決定資料屬於哪個分區。
  跟 HASH 分區類似，差在 HASH 分區使用使用者自訂的運算方式，
  KEY 分區使用 MySQL server 內部的運算方式。

  
CREATE TABLE tb (
    mydate DATE NOT NULL DEFAULT '1970-01-01',
    data varchar(255) NOT NULL
);
PARTITION BY KEY(mydate);
PARTITIONS 3;
  

• 子分區

  //Range 分區底下，再建立 Range 分區
CREATE TABLE tb (
    id INT NOT NULL,
    data varchar(255) NOT NULL
);
PARTITION BY RANGE(id)
SUBPARTITION BY RANGE(id)(
    PARTITION p0 VALUES LESS THAN (10)(
        PARTITION s0 VALUES LESS THAN (6),
        PARTITION s1 VALUES LESS THAN MAXVALUE
    ),
    PARTITION p1 VALUES LESS THAN MAXVALUE(
        PARTITION s2 VALUES LESS THAN (30),
        PARTITION s3 VALUES LESS THAN MAXVALUE
    )
);
  

Partitioning key 必須是Primary key

如果partition key 不是 pk, 那麼可以將兩個做成組合鍵

新增 Partitioning

 
//語法1
ALTER TABLE tb ADD PARTITION (PARTITION p0 VALUES LESS THAN(11));

//語法2
ALTER TABLE 'tb2' REORGANIZE PARTITION pMax INTO (
    PARTITION p202008 VALUES LESS THAN (1598889600000),
    PARTITION pMax VALUES LESS THAN MAXVALUE
);

合併相鄰分區

 ALTER TABLE aa REORGANIZE PARTITION s0,s1 INTO (
    PARTITION p0 VALUES LESS THAN (11));

刪除分區(刪除後，儲存在該分區的資料也會不見)

ALTER TABLE aa DROP PARTITION p0;