mysql 分区全自动维护（维护天和月）

news/2025/1/14 21:08:28/

mysql 分区全自动维护（维护天和月）

一、新建数据库和存储过程

1.1 新建分区记录表

-- auto-generated definition
create table t_partition_maintenance_logs
(id             int unsigned auto_incrementprimary key,schema_name    varchar(20)  default '' null comment '数据库名',table_name     varchar(50)  default '' null comment '表名',partition_name varchar(20)  default '' null comment '分区名',partition_time int unsigned default 0  null comment '分区时间',sql_text       varchar(512) default '' null comment 'sql脚本',status         tinyint      default 0  null comment 'sql脚本执行状态，0失败1成功',create_at      int unsigned default 0  null comment 'sql脚本执行时间'
)comment '分区表统一维护日志表' charset = utf8mb4;

1.2 函数主要分割 xxxxx,zzzzz 取 xxxxx 和 zzzzz

createdefiner = root@localhost function Func_split_val(f_string text, f_delimiter varchar(5), f_order int) returns varchar(255)deterministic
BEGINdeclare result varchar(255) default '';set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));return result;
END;

1.3 自动维护存储过程

  • 如果是天，维护15个分区，如果是月的维护三个分区

  • 天的分区名字：p20230102

  • 月的分区名字：m202307

createdefiner = root@`%` procedure Proc_Partition_maintenance()
BEGIN-- 按天分区表最少空余15个分区DECLARE v_day_least_par_num INT DEFAULT 15;-- 按月分区表最少空余3个分区DECLARE v_month_least_par_num INT DEFAULT 3;-- 分区表个数DECLARE v_partition_table_num INT DEFAULT 0;DECLARE v_i INT DEFAULT 1;DECLARE v_schema_name VARCHAR(20) DEFAULT '';DECLARE v_table_name VARCHAR(50) DEFAULT '';DECLARE v_column VARCHAR(20) DEFAULT '';DECLARE v_time VARCHAR(30) DEFAULT '';DECLARE v_time_first INT DEFAULT 0;DECLARE v_time_second INT DEFAULT 0;DECLARE v_type VARCHAR(10) DEFAULT '';DECLARE v_diff_days INT DEFAULT 0;DECLARE v_diff_partitions INT DEFAULT 0;DECLARE v_partition_name VARCHAR(50) DEFAULT '';DECLARE v_partition_time INT DEFAULT 0;DECLARE v_sql_status TINYINT DEFAULT 0;-- 查询所有库中RANGE分区的表，存入临时表DROP TEMPORARY TABLE IF EXISTS tmp_partition_table;CREATE TEMPORARY TABLE tmp_partition_table(id INT NOT NULL AUTO_INCREMENT, schema_name VARCHAR(20), table_name VARCHAR(50), PRIMARY KEY (id));# 插入查询分区表的SQLINSERT INTO tmp_partition_table(schema_name, table_name)SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM information_schema.PARTITIONSWHERE PARTITION_NAME IS NOT NULL AND PARTITION_ORDINAL_POSITION > 0 AND PARTITION_METHOD = 'RANGE';SELECT * FROM tmp_partition_table;SELECT COUNT(1) INTO v_partition_table_num FROM tmp_partition_table;WHILE(v_i <= v_partition_table_num) DOSELECT schema_name, table_name INTO v_schema_name, v_table_name FROM tmp_partition_table WHERE id = v_i;SELECT M.PARTITION_EXPRESSION, GROUP_CONCAT(M.PARTITION_DESCRIPTION) INTO v_column, v_time FROM (SELECT PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM information_schema.`PARTITIONS`WHERE TABLE_SCHEMA = v_schema_name AND TABLE_NAME = v_table_name AND PARTITION_METHOD = 'RANGE'AND PARTITION_NAME IS NOT NULL AND PARTITION_ORDINAL_POSITION > 0 ORDER BY PARTITION_ORDINAL_POSITION DESC LIMIT 2) M GROUP BY M.PARTITION_EXPRESSION;SELECT crawler.Func_split_val(v_time,',',1) INTO v_time_first;SELECT crawler.Func_split_val(v_time,',',2) INTO v_time_second;IF(v_time_first > 0 AND v_time_second > 0) THENSET v_diff_days = DATEDIFF(FROM_UNIXTIME(v_time_first,'%Y-%m-%d'),FROM_UNIXTIME(v_time_second,'%Y-%m-%d'));IF(v_diff_days = 1) THENSELECT COUNT(1) INTO v_diff_partitions FROM information_schema.`PARTITIONS`WHERE TABLE_SCHEMA = v_schema_name AND TABLE_NAME = v_table_name AND PARTITION_METHOD = 'RANGE'AND PARTITION_DESCRIPTION >= UNIX_TIMESTAMP(CURRENT_DATE())AND PARTITION_NAME IS NOT NULL AND PARTITION_ORDINAL_POSITION > 0;IF(v_diff_partitions < v_day_least_par_num) THENSET v_partition_time = UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(v_time_first,'%Y-%m-%d'),INTERVAL 1 DAY));SET v_partition_name = CONCAT('p',DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(v_partition_time),INTERVAL -1 DAY),'%Y%m%d'));IF EXISTS(SELECT 1 FROM information_schema.`PARTITIONS` WHERE TABLE_SCHEMA = v_schema_name AND TABLE_NAME = v_table_name AND PARTITION_NAME = v_partition_name) THENSET v_partition_name = CONCAT('d',DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(v_partition_time),INTERVAL -1 MONTH),'%Y%m'));END IF;SET @v_sql=CONCAT('ALTER TABLE ',v_schema_name,'.',v_table_name,' ADD PARTITION (PARTITION ',v_partition_name,' VALUES LESS THAN (',v_partition_time,') ENGINE = InnoDB);');-- SELECT CAST(@v_sql AS CHAR(10000) CHARACTER SET utf8);PREPARE stmt FROM @v_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;  -- 释放掉IF EXISTS(SELECT 1 FROM information_schema.`PARTITIONS` WHERE TABLE_SCHEMA = v_schema_name AND TABLE_NAME = v_table_name AND PARTITION_NAME = v_partition_name) THENSET v_sql_status = 1;END IF;INSERT INTO t_partition_maintenance_logs(schema_name, table_name, partition_name, partition_time, sql_text, `status`, create_at)VALUES(v_schema_name, v_table_name, v_partition_name, v_partition_time, @v_sql, v_sql_status, UNIX_TIMESTAMP());END IF;-- 按天分区ELSEIF(v_diff_days >= 28 AND v_diff_days <= 31) THEN-- 按月分区SELECT COUNT(1) INTO v_diff_partitions FROM information_schema.`PARTITIONS`WHERE TABLE_SCHEMA = v_schema_name AND TABLE_NAME = v_table_name AND PARTITION_METHOD = 'RANGE'AND PARTITION_DESCRIPTION >= UNIX_TIMESTAMP(DATE_ADD(LAST_DAY(NOW()),INTERVAL 1 DAY))AND PARTITION_NAME IS NOT NULL AND PARTITION_ORDINAL_POSITION > 0;IF(v_diff_partitions < v_month_least_par_num) THENSET v_partition_time = UNIX_TIMESTAMP(DATE_ADD(FROM_UNIXTIME(v_time_first,'%Y-%m-01'),INTERVAL 1 MONTH));SET v_partition_name = CONCAT('p',DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(v_partition_time),INTERVAL -1 MONTH),'%Y%m'));IF EXISTS(SELECT 1 FROM information_schema.`PARTITIONS` WHERE TABLE_SCHEMA = v_schema_name AND TABLE_NAME = v_table_name AND PARTITION_NAME = v_partition_name) THENSET v_partition_name = CONCAT('m',DATE_FORMAT(DATE_ADD(FROM_UNIXTIME(v_partition_time),INTERVAL -1 MONTH),'%Y%m'));END IF;SET @v_sql=CONCAT('ALTER TABLE ',v_schema_name,'.',v_table_name,' ADD PARTITION (PARTITION ',v_partition_name,' VALUES LESS THAN (',v_partition_time,') ENGINE = InnoDB);');-- SELECT CAST(@v_sql AS CHAR(10000) CHARACTER SET utf8);PREPARE stmt FROM @v_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;  -- 释放掉IF EXISTS(SELECT 1 FROM information_schema.`PARTITIONS` WHERE TABLE_SCHEMA = v_schema_name AND TABLE_NAME = v_table_name AND PARTITION_NAME = v_partition_name) THENSET v_sql_status = 1;END IF;INSERT INTO t_partition_maintenance_logs(schema_name, table_name, partition_name, partition_time, sql_text, `status`, create_at)VALUES(v_schema_name, v_table_name, v_partition_name, v_partition_time, @v_sql, v_sql_status, UNIX_TIMESTAMP());END IF;END IF;END IF;SET v_i = v_i + 1;END WHILE;DROP TEMPORARY TABLE IF EXISTS tmp_partition_table;
END;

二、表一开始需要新建二个分区

2.1 修改表建立分区

例如下面我是新建2个月分区

ALTER TABLE t_crawler_articleDROP PRIMARY KEY,DROP INDEX article_md5,ADD PRIMARY KEY (id, create_at),ADD UNIQUE INDEX article_md5 (article_md5, create_at)PARTITION BY RANGE (create_at) (PARTITION m202306 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01 00:00:00')),PARTITION m202307 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01 00:00:00')));

如果有数据情况还可以备份,以免丢失数据

create table t_crawler_article_bak select * from `t_crawler_article`;

2.2 执行维护分区存储，多执行也没关系

call Proc_Partition_maintenance();
call Proc_Partition_maintenance();
call Proc_Partition_maintenance();
可以多执行几次，最多维护3个月分区

2.3 查看分区，最好是空余三个月分区

SELECTTABLE_NAME,PARTITION_NAME,PARTITION_ORDINAL_POSITION,TABLE_ROWS,PARTITION_DESCRIPTION
FROMINFORMATION_SCHEMA.PARTITIONS
WHERETABLE_SCHEMA = 'crawler' ANDTABLE_NAME = 't_crawler_article';

三、定时器去定时维护分区

3.1 创建事件

每天凌晨 4:05:06执行，自动维护分区

create  event Job_Partition_maintenance on scheduleevery '1' DAYstarts '2023-01-01 04:05:06'enabledoCALL Proc_Partition_maintenance();

3.2 查看事件是否开启

SHOW VARIABLES LIKE 'event_scheduler';

可以看到下面的结果

mysql> SHOW VARIABLES LIKE 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

如果为off 可以设置下面命令开启，再查看

SET GLOBAL event_scheduler = ON;  这种是临时开启

再my.cnf 设置 永久开启：

[mysqld]
event_scheduler=ON

重启mysql systemctl restart mysqld.service

3.3 还可以查看具体事件是否开启

SELECT * FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'crawler';  参数为库的名字

可以看到输出结果

mysql> SELECT * FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'crawler'\G;
*************************** 1. row ***************************EVENT_CATALOG: defEVENT_SCHEMA: crawlerEVENT_NAME: Job_Partition_maintenanceDEFINER: root@localhostTIME_ZONE: SYSTEMEVENT_BODY: SQLEVENT_DEFINITION: CALL Proc_Partition_maintenance()EVENT_TYPE: RECURRINGEXECUTE_AT: NULLINTERVAL_VALUE: 1INTERVAL_FIELD: DAYSQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONSTARTS: 2023-01-01 04:05:06ENDS: NULLSTATUS: ENABLED  # 这个代表开启了ON_COMPLETION: NOT PRESERVECREATED: 2023-07-13 07:39:35LAST_ALTERED: 2023-07-13 07:39:35LAST_EXECUTED: NULLEVENT_COMMENT: ORIGINATOR: 1
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_general_ciDATABASE_COLLATION: utf8mb4_bin
1 row in set (0.00 sec)

http://www.ppmy.cn/news/859501.html

相关文章

幼儿蒙教启思

幼儿蒙教启思

参考 古代蒙养教育及其对当代幼儿教育的启思_论文选读_湖南国际教育科学研究院 (hnskedu.cn)蒙以养正&#xff5c;幼儿教育为何称“蒙学”&#xff1f;中国古代“蒙学”经典有哪些&#xff1f;_腾讯新闻 (qq.com)大语文时代&#xff0c;这些才是《声律启蒙》的有效打开方式&am…
阅读更多...
儿童数学启蒙

儿童数学启蒙

美国启蒙教育目标 https://www.linstitute.net/archives/50879 shopping启蒙 http://www.xiaohuasheng.cn/blog/4b00f21a1d722529 启蒙三大误区 https://www.jianshu.com/p/370757ca2011 数学敏感度 https://www.jianshu.com/p/7a7ab4fd8ea8 别人家的孩子 https://www.jia…
阅读更多...
机器人启蒙教育关联

机器人启蒙教育关联

​随着现代经济的快速发展&#xff0c;孩子的教育问题已经成为时下讨论的热点。对于孩子的教育也有了更多的新兴思维。格物斯坦表示:特别是机器人启蒙教育&#xff0c;家长在这方面为了孩子耗费了很多时间、精力与财力&#xff0c;孩子各方面要得到提升接受新鲜事物能力要快就必…
阅读更多...
教宝宝学英语

教宝宝学英语

教宝宝学英语 来源&#xff1a; 日期&#xff1a;2004-11-12 作者&#xff1a;langedu 阅读&#xff1a;3409 次 　　英语的重要性已为人们深刻领会&#xff0c;现在中国的年轻父母无不重视对孩子英语教育的培养。　　　心理学关于幼儿学习的研究成果及笔者的实践表明&#…
阅读更多...
kubernetes 系列教程之部署 BusyBox 容器

kubernetes 系列教程之部署 BusyBox 容器

文章目录 在 Kubernetes 上部署 BusyBox 容器步骤一&#xff1a;创建 BusyBox Pod步骤二&#xff1a;进入 BusyBox 容器结论 Kubernetes版本 v1.19.14 在 Kubernetes 上部署 BusyBox 容器 BusyBox 是一个轻量级的 Unix 工具集合&#xff0c;它将许多常用的 Unix 工具打包在一个…
阅读更多...
分析少儿编程思维的启蒙问题

分析少儿编程思维的启蒙问题

如果你现在闭上眼睛&#xff0c;想象十年后的世界&#xff0c;你会发现那时的世界将发生巨大的变化&#xff0c;信息和计算机将进入人们生活的每一个角落。随着人工智能时代的到来&#xff0c;人与计算机的合作更加紧密。给机器下发指令的编程将成为每个人的需要有的技能与思维…
阅读更多...
励步启蒙 android平板,励步启蒙官方版app

励步启蒙 android平板,励步启蒙官方版app

励步启蒙官方版app是一款儿童在线启蒙教育学习软件&#xff0c;软件为孩子提供了各种各样的课程&#xff0c;帮助家长培养孩子的多方面的兴趣和学习能力&#xff0c;利用了趣味性的教育方式帮助家长教育孩子&#xff0c;感兴趣的朋友就快来下载吧! 相关合集推荐&#xff1a;学习…
阅读更多...
0-1岁宝宝应该如何早教

0-1岁宝宝应该如何早教

0-1岁宝宝应该如何早教_妈妈网小百科现在早教已经非常普及了&#xff0c;大多数父母对于孩子的早期教育问题都非常的重视&#xff0c;生怕孩子输在起跑线上。如果早期教育做得好&#xff0c;就可以为宝宝的人生打下良好的基础&#xff0c;还有可能会培养出超常宝宝。那么&#…
阅读更多...
最新文章

Copyright @ 2022~2023