iT邦幫忙

2

MySQL 系列文 - Partition Table(2) - 在既有的 TABLE 上建立 partition

前言

如同前一篇提到,假設今天接到一個DB有一張表裏面資料量蠻大的,每天大概有20萬筆,且這張表要保留至少半年的資料,平常搜尋大多是以一天為條件,所以我們希望搜尋的時候可以只用到某個 partition 區塊就好。所以就打算在既有的 TABLE 上建立 partition。

流程

先建立測試資料

  • 從2020-01-01 ~ 2020-04-01 每天塞3筆資料進去
  • PK: uid 且有自增長
-- 建立表格
drop table if exists tb1;
CREATE TABLE tb1 (
    uid int NOT NULL AUTO_INCREMENT,
    uname varchar(50),
    create_time timestamp not null default current_timestamp,
    PRIMARY KEY(uid),
    KEY (uname)
);

-- 塞入2020-01-01 到 2020-04-01  每天3筆資料
CREATE TEMPORARY TABLE IF NOT EXISTS `name_list` (
    `name` varchar(10) NOT NULL
) ENGINE = InnoDB;
INSERT INTO `name_list` VALUES ('test1'),('test2'),('test3');

INSERT INTO tb1(uname,create_time)
WITH RECURSIVE
cte  
AS (
      SELECT "2020-01-01 05:00:00" as `expiration`
      UNION ALL
      SELECT
          date_add(`expiration`, INTERVAL 1 DAY) as `expiration`
      FROM cte
      WHERE `expiration` < "2020-04-01 05:00:00"
    ),
cte1
AS
    (
    SELECT * FROM cte
    CROSS JOIN `name_list`
    )
SELECT     
    `name`
    ,`expiration`
FROM cte1

★ 假設我覺得這張表很大,想要在這張表切 partition ,且以日來切,步驟如下

  1. 因為切 partition ,切的條件必須放在 PK中,這裡我們想利用 create_time 這個欄位來切,所以PK就要修改。所以第一步要先 drop PK
ALTER TABLE tb1 DROP PRIMARY KEY;
  1. 上面這個步驟執行就發現錯誤
    1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
    所以我們需要先把自增長拿掉
ALTER TABLE tb1 modify column uid INT NOT NULL; 
  1. 接著就可以 drop PK
  2. 重新建立 PK,並把要切 partition 的欄位放進去
ALTER TABLE tb1 ADD PRIMARY KEY(`uid`,`create_time`);
  1. 把原本的自增長欄位加回去
ALTER TABLE tb1 MODIFY uid INT  NOT NULL AUTO_INCREMENT;  
  1. 這裡因文章範圍有限,我只切從2020-03-23 ~ 2020-04-02
ALTER TABLE tb1 partition by range(unix_timestamp(create_time))
(
 partition ym_200322 values less than (unix_timestamp('2020-03-23 00:00:00')), 
 partition ym_200323 values less than (unix_timestamp('2020-03-24 00:00:00')), 
 partition ym_200324 values less than (unix_timestamp('2020-03-25 00:00:00')), 
 partition ym_200325 values less than (unix_timestamp('2020-03-26 00:00:00')), 
 partition ym_200326 values less than (unix_timestamp('2020-03-27 00:00:00')), 
 partition ym_200327 values less than (unix_timestamp('2020-03-28 00:00:00')), 
 partition ym_200328 values less than (unix_timestamp('2020-03-29 00:00:00')), 
 partition ym_200329 values less than (unix_timestamp('2020-03-30 00:00:00')), 
 partition ym_200330 values less than (unix_timestamp('2020-03-31 00:00:00')), 
 partition ym_200331 values less than (unix_timestamp('2020-04-01 00:00:00')),
 partition ym_200401 values less than (unix_timestamp('2020-04-02 00:00:00'))
);  
  1. 檢查是否有進到 partition:利用下面的語法可以觀察每個 partition 裡面的數量如下圖1
select table_schema, table_name, partition_name,partition_ordinal_position,partition_method, partition_description, table_rows
from information_schema.partitions
where table_schema = 'test' and table_name = 'tb1'

從下圖可以看到

  • 因為我只從2020-03-23 開始有切 partition ,所以小於此日期的都會在這裡。
  • 後面就是每天有3筆資料。
    https://ithelp.ithome.com.tw/upload/images/20200421/20124671Eg5Rb3blWb.png
    圖1

小結

以上為如何在原本既有的 TABLE 上建立 partition。幾個重點整理一下

  1. 由於要拿來切 partition 的欄位必須在PK中,所以PK勢必得重建
  2. 如果要 drop PK 就要先把自增長欄位先拿掉
  3. 可以透過系統表來觀察資料是否有寫入到對應的 partition,如圖1,藉此檢查是否有符合自己的期望

下一篇再分享關於切的 partition 數量跟效能有什麼關係,可以無限制的切下去嗎?

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


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

1 則留言

0
ckp6250
iT邦好手 1 級 ‧ 2020-04-22 04:39:44

言簡意賅,受教了,感恩。

Stock iT邦新手 5 級 ‧ 2020-04-22 22:01:42 檢舉

:)

我要留言

立即登入留言