Day.27 实务应用 - 实作表自动分区管理( event / procedure / partition )_2

延续昨天的流程内容，做到能输入参数(资料库&资料表)判断有无分区的作用～
确保有切表的状况下就能方便进行下一步作业，把资料中最早的日期至今天日期的分区都先切分区出来，後续再来做保留几天资料的动作。

• 情境模拟

有一个尚未切分区的资料表act，且本身已经有资料在内了，想用该表栏位created_at(建立时间)当做partition key切表，除了切分区还要能达到自动新增分区与删除旧资料分区的动作，方便资料上的维护。

CREATE DATABASE t7;

CREATE TABLE `act` (
  `actives_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `status` bigint(20) NOT NULL,
  `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 COMMENT '建立时间',
  PRIMARY KEY (`actives_id`,`created_at`)
);

• 验证数据

先将数据写入，检验最後跑流程後的数据分布是否都在对的地方(分区)。

INSERT INTO act (status,start_time,end_time,platform_id,updated_at,created_at) values(1,1000000,1000000,666,1000000,1632499200),(2,1000000,1000000,666,1000000,1632758400),(2,1000000,1000000,666,1000000,1632758400),(2,1000000,1000000,666,1000000,1632931200),(2,1000000,1000000,666,1000000,1632931200),(5,1000000,1000000,666,1000000,1633104000);

-- 有6笔数据&建立日期
mysql> select actives_id,created_at,from_unixtime(created_at) from act;
+------------+------------+---------------------------+
| actives_id | created_at | from_unixtime(created_at) |
+------------+------------+---------------------------+
|          1 | 1632499200 | 2021-09-24 16:00:00       |
|          2 | 1632758400 | 2021-09-27 16:00:00       |
|          3 | 1632758400 | 2021-09-27 16:00:00       |
|          4 | 1632931200 | 2021-09-29 16:00:00       |
|          5 | 1632931200 | 2021-09-29 16:00:00       |
|          6 | 1633104000 | 2021-10-01 16:00:00       |
+------------+------------+---------------------------+
6 rows in set (0.00 sec)

• 先跑昨天的判断切分区流程

ps. 复制前一天文章procdure 到新资料库: t7

CALL check_part('t7','act');

跑完切出来2个分区内的资料，符合我们预期的结果。

继续进行後续流程 ...

• 在已存在分区资料表中做分区。

  概念: 取pmax分区前一个分区内容当作最大值，与今天日期相比看差几天就补对应几天分区。

  Today: 2021/10/02

• 实作流程

DELIMITER ||  
DROP PROCEDURE IF EXISTS add_part ||  
CREATE PROCEDURE add_part(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN   

DECLARE n_sysdate date; --当天日期
DECLARE max_partition_day date; --现有分区最大值

DECLARE p_name VARCHAR(255) ;
DECLARE p_description VARCHAR(255) ;

--取分区最大值做判断
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;

set n_sysdate = sysdate();

--判断与当天日期差几天(要新增的分区数)
WHILE max_partition_day <= (n_sysdate) 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; 

END || 
DELIMITER ;

• 执行切分区步骤

CALL add_part('t7','act');

• 验证数据是否都在对应分区内

• 预先分区

如果要采预先分区的话就只要拿上面的内容做小部分修改即可达到要求。

WHILE max_partition_day <= (n_sysdate) DO

改成

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

以上面加5天来举例如下～

今天实作新建分区的整个流程～
最後重要的旧资料清除＆每天触发执行动作。在明天做最後的ending~