正文
Mysql数据按天分区,定期删除
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
需求:
1.日志表需要按天分区
2.只保留一个月数据
方案:
1.创建两个事件,一个事件生成未来需要的分区,另一个事件定期检查过期数据(移除分区)
2.创建事件每小时执行一次,删除事件每天执行一次
3.事件开始时需要先创建一个当前所需分区
全量方法:
1.先构造存储过程 create_partition_today :将表转化为分区表,并将历史数据归集到该分区,未来数据则按天放置:
#alter table to partition table
DELIMITER $$
USE `dc_log`$$
DROP PROCEDURE IF EXISTS `create_partition_today`$$
CREATE PROCEDURE `create_partition_today`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
DECLARE BEGINTIME TIMESTAMP;
DECLARE ENDTIME TIMESTAMP;
DECLARE DAYS_ENDTIME INT;
DECLARE PARTITIONNAME VARCHAR(16);
SET BEGINTIME = NOW();
SET ENDTIME = BEGINTIME + INTERVAL 1 DAY;
SET PARTITIONNAME = DATE_FORMAT(BEGINTIME, 'p%Y%m%d');
SET DAYS_ENDTIME = TO_DAYS(ENDTIME);
SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' PARTITION BY RANGE (to_days(create_time))
(PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAYS_ENDTIME, '))');
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END$$
DELIMITER ;
2.按天构造分区的存储过程create_partition_by_day:
#procedure of build partition of today and next day
DELIMITER $$
USE `dc_log`$$
DROP PROCEDURE IF EXISTS `create_partition_by_day`$$
CREATE PROCEDURE `create_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
DECLARE ROWS_CNT INT UNSIGNED;
DECLARE BEGINTIME TIMESTAMP;
DECLARE ENDTIME TIMESTAMP;
DECLARE DAYS_ENDTIME INT;
DECLARE PARTITIONNAME VARCHAR(16);
SET BEGINTIME = NOW() + INTERVAL 1 DAY;
SET PARTITIONNAME = DATE_FORMAT(BEGINTIME, 'p%Y%m%d');
SET ENDTIME = BEGINTIME + INTERVAL 1 DAY;
SET DAYS_ENDTIME = TO_DAYS(ENDTIME);
SELECT COUNT(*)
INTO ROWS_CNT
FROM information_schema.partitions
WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
IF ROWS_CNT = 0
THEN
SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' ADD PARTITION (PARTITION ', PARTITIONNAME, ' VALUES LESS THAN (', DAYS_ENDTIME, '))');
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
ELSE
SELECT CONCAT("partition `", PARTITIONNAME, "` for table `", IN_SCHEMANAME, ".", IN_TABLENAME,
"` already exists") AS result;
END IF;
END$$
DELIMITER ;
3.按天清除数据的存储过程clear_partition_by_day
DELIMITER $$
USE `dc_log`$$
DROP PROCEDURE IF EXISTS `clear_partition_by_day`$$
CREATE PROCEDURE `clear_partition_by_day`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
DECLARE NOWDAYS INT;
DECLARE Done INT;
DECLARE part VARCHAR(64);
DECLARE descr INT;
DECLARE rs CURSOR FOR SELECT
partition_name part,
partition_description descr
FROM information_schema.partitions
WHERE
table_schema = IN_SCHEMANAME
AND table_name = IN_TABLENAME;
/* 异常处理 */
DECLARE CONTINUE HANDLER FOR SQLSTATE '' SET Done = 1;
OPEN rs;
SET NOWDAYS = TO_DAYS(NOW());
FETCH rs into part,descr;
while Done is null DO
IF NOWDAYS - descr > 30
THEN
select descr AS cc;
SET @SQL = CONCAT('ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
' DROP PARTITION ', part);
select descr AS aa;
SELECT @SQL AS result;
select descr AS bb;
PREPARE STMT FROM @SQL;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
END IF;
FETCH rs into part,descr;
end WHILE;
CLOSE rs;
END$$
DELIMITER ;
注意:以上过程有可能报错,这里记录两个错误:
1.分区字段必须包含主键:
这里采用的是事件字段作为分区字段,当然不可能是主键,所以再mysql中会报错,查阅资料大多说是因为分区表中不能保证数据唯一,需要将分区健纳入主键才可,不清楚原理.如果不愿意这样做也可以考虑,删除已有主键,无主键的表可以用任意字段作为分区字段
ALTER TABLE dc_system.service_push DROP PRIMARY KEY;
2.时间字段不允许作为分区字段:
这是因为 DATETIME 会受到时间区的影响,mysql里面分区的时间字段需要用CURRENT_TIMESTAMP
ALTER TABLE business_log
MODIFY create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;
4.继续往下,打开事件变量,这里直接修改
SET GLOBAL event_scheduler = 'ON'; #打开
重启后会被重置,如果需要永久修改,在my.cnf（mysql配置文件）中[mysqld]部分中添加下面内容，重启MYSQL
event_scheduler=ON
5.调用存储过程创建当天分区:
#created today's partition
CALL create_partition_today('dc_log', 'business_log');
6.创建事件:
DELIMITER $$
USE `dc_log`$$
CREATE EVENT IF NOT EXISTS `e_part_manage`
ON SCHEDULE EVERY 1 HOUR #every minute
STARTS '2018-05-01 18:27:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
CALL dc_log.create_partition_by_day('dc_log', 'business_log');
END$$
DELIMITER ; #event of clear data which out of date of 30 days
DELIMITER $$
USE `dc_log`$$
CREATE EVENT IF NOT EXISTS `clear_data`
ON SCHEDULE EVERY 1 DAY #every minute
STARTS '2018-05-01 15:58:51'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'clearing data'
DO BEGIN
CALL clear_partition_by_day('dc_log', 'business_log');
END$$
DELIMITER ;
7.如果有其他表也需要如此处理,则先执行创建当天分区,再修改事件:
#service_push #same steps of table business_log
ALTER TABLE dc_system.service_push DROP PRIMARY KEY;
CALL create_partition_today('dc_system', 'service_push'); DELIMITER ;
DELIMITER $$
ALTER EVENT e_part_manage
DO BEGIN
CALL dc_log.create_partition_by_day('dc_log', 'business_log');
CALL dc_log.create_partition_by_day('dc_system', 'service_push');
END$$
DELIMITER ; DELIMITER $$
ALTER EVENT clear_data
DO BEGIN
CALL dc_log.clear_partition_by_day('dc_log', 'business_log') ;
CALL dc_log.clear_partition_by_day('dc_system', 'service_push');
END$$
DELIMITER ;
8.查看表的分区情况:
use dc_log;
SELECT
partition_name part,
partition_expression expr,
partition_description descr,
table_rows
FROM
INFORMATION_SCHEMA.partitions
WHERE
TABLE_SCHEMA = SCHEMA()
AND TABLE_NAME='service_push' ;
9.手动添加分区:
ALTER TABLE dc_log.all_log ADD PARTITION (PARTITION 'PARTITIONNAME' VALUES LESS THAN (TO_DAYS(now())));
10.查看事件和事件开关
show events;
SHOW VARIABLES LIKE 'event_scheduler';
done
