DAY 28
0
Software Development

## Day.28 實務應用 - 實作表自動分區管理( event / procedure / partition )_END

• 情境模擬

• 刪除x天前歷史分區

概念: 取表分區最小值與今天日期相比，看需要保留幾天。

• 實作流程

``````DELIMITER ||
DROP PROCEDURE IF EXISTS del_part ||
CREATE PROCEDURE del_part(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64),IN_DELETE INT)
BEGIN

DECLARE n_sysdate date;
DECLARE min_partition_day date;
DECLARE del_partition_name VARCHAR(255) ;

--取最小分區值
select from_unixtime(partition_description) into min_partition_day from information_schema.partitions where table_schema = IN_SCHEMANAME  and table_name = IN_TABLENAME AND partition_description < 'MAXVALUE' order by partition_description asc limit 1;

set n_sysdate = sysdate();

--判斷要刪除天數的參數執行刪除分區作業
WHILE min_partition_day <= (n_sysdate - INTERVAL IN_DELETE DAY) DO

SET del_partition_name = date_format(min_partition_day - INTERVAL 1 DAY,'p%Y%m%d');

SET @sql = concat('alter table ', IN_SCHEMANAME, '.', IN_TABLENAME, ' drop partition ', del_partition_name);
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET min_partition_day = min_partition_day + INTERVAL 1 DAY;

END WHILE;

SET @STEP = "STEP: drop partition [ PASS ]";
SELECT @STEP;

END ||
DELIMITER ;
``````
• 執行刪除舊資料分區步驟
``````CALL del_part('t7','act',7);
``````

## 完成後就可以看到分區＆分區中的資料一併被刪除了

• 完整自動分區實作procedure
``````DELIMITER ||
DROP PROCEDURE IF EXISTS auto_part ||
CREATE PROCEDURE auto_part(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64),IN_CREATE INT,IN_DELETE INT)
BEGIN

DECLARE isexist_partition varchar(255) default '';
DECLARE old_date VARCHAR(255);
DECLARE new_p_description VARCHAR(255);
DECLARE new_p_name VARCHAR(255);

DECLARE n_sysdate date;
DECLARE max_partition_day date;
DECLARE p_name VARCHAR(255) ;
DECLARE p_description VARCHAR(255) ;

DECLARE min_partition_day date;
DECLARE del_partition_name VARCHAR(255) ;

select partition_name into isexist_partition from information_schema.partitions where table_schema = IN_SCHEMANAME  and table_name = IN_TABLENAME limit 1;

IF isexist_partition <=> NULL THEN
SET @p_check = "The table has no partition。 STEP: Add table partition";
SELECT @p_check;

select date(from_unixtime(`created_at`)) into old_date  from `act` order by `created_at` asc limit 1 ;
SET old_date = REPLACE(old_date, '-', '');

SET new_p_name = DATE_FORMAT(old_date, 'p%Y%m%d');
SET new_p_description = unix_timestamp(old_date + INTERVAL 1 DAY);
SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`', ' PARTITION BY RANGE (`created_at`) (PARTITION ', new_p_name, ' VALUES LESS THAN (', new_p_description, '),PARTITION pmax VALUES LESS THAN MAXVALUE );');
SELECT @SQL;
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

END IF;

SET @STEP = "STEP: check partition [ PASS ]";
SELECT @STEP;

set n_sysdate = sysdate();

select from_unixtime(partition_description) into max_partition_day from information_schema.partitions where table_schema = IN_SCHEMANAME  and table_name = IN_TABLENAME AND partition_description < 'MAXVALUE' order by partition_description desc limit 1;

WHILE max_partition_day <= (n_sysdate + INTERVAL IN_CREATE DAY) DO

SET p_name = date_format(max_partition_day ,'p%Y%m%d');
SET p_description = unix_timestamp(max_partition_day + INTERVAL 1 DAY);

SET @sql = concat('ALTER TABLE ', IN_SCHEMANAME, '.', IN_TABLENAME, ' REORGANIZE PARTITION `pmax` INTO  (partition ', p_name, ' values less than (', p_description, '),PARTITION `pmax` VALUES LESS THAN MAXVALUE )');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET max_partition_day = max_partition_day + INTERVAL 1 DAY;

END WHILE;

SET @STEP = "STEP: add historical date to today [ PASS ]";
SELECT @STEP;

select from_unixtime(partition_description) into min_partition_day from information_schema.partitions where table_schema = IN_SCHEMANAME  and table_name = IN_TABLENAME AND partition_description < 'MAXVALUE' order by partition_description asc limit 1;

WHILE min_partition_day <= (n_sysdate - INTERVAL IN_DELETE DAY) DO

SET del_partition_name = date_format(min_partition_day - INTERVAL 1 DAY,'p%Y%m%d');

SET @sql = concat('alter table ', IN_SCHEMANAME, '.', IN_TABLENAME, ' drop partition ', del_partition_name);
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET min_partition_day = min_partition_day + INTERVAL 1 DAY;

END WHILE;

SET @STEP = "STEP: drop partition [ PASS ]";
SELECT @STEP;

END ||
DELIMITER ;

``````
• 執行完整自動表分區異動
``````CALL auto_part('t7','act',5,7);
``````

1. 使用test.act資料表created_at 整數型態欄位當partition key進行分區作業。

2. 預先建立後5天分區。

3. 資料維護上只保留到7天前的資料。

1. 先看事件排成有無開啟
``````mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.01 sec)
``````
1. 設定參數為 ON
``````--重啟後失效
set global event_scheduler = on;

--修改設定檔 my.cnf
event_scheduler = 1
``````
• 建立呼叫procrdure的排程事件
``````DELIMITER ||
CREATE EVENT IF NOT EXISTS `auto_act_event` --建立事件＆確認event是否存在，存在則不建立
ON SCHEDULE EVERY 1 day  --指定為循環事件(每天執行)
STARTS '2021-10-02 20:00:00'  --START / END 用來指定事件有效開始結束時間。ps.這邊因為要持續跑所以只設定事件開始執行時間。
ON COMPLETION PRESERVE --避免事件在到期後自動刪除
ENABLE
COMMENT 'act_event_partition'
DO BEGIN    --執行內容區塊
call auto_part('t7','act',5,7);
END||
DELIMITER ;
``````
• 相關使用Event內容
``````
--查詢資料庫存在哪些event
SHOW EVENTS FROM db_name;

--查詢event建立結構
SHOW CREATE EVENT event_name;

--刪除現有的事件
DROP EVENT IF EXISTS event_name;

``````

MYSQL-相關實務操作學習紀錄30