业务运行一段时间后,会出现有些表数据量很大,可能对系统性能产生不良的影响,常见的如订单表、登录log表等,这些数据很有时效性,比如我们一般很少去查上个月的订单,最多也就是报表统计会涉及到。
解决方案:创建一张与A表字段一样的B表,每天定时把A表昨天的数据插入到B表,再把A表昨天之前的数据删除,也可以根据需求改成一个月一次。如果不想只保存到一张表里,也可以在存储过程中,以年月份为后缀每次新建表。
1、首先创建一个存储过程
DELIMITER $
/*数据库名称*/
USE `dbcp`$$
/*存储过程名称*/
DROP PROCEDURE IF EXISTS `apaas_log_procedure`$$
CREATE DEFINER=`root`@`%` PROCEDURE `apaas_log_procedure`()
BEGIN
SET @current_time = NOW();
/*备份昨天之前的数据*/
INSERT INTO `apaas_log_copy`
SELECT * FROM `apaas_log`
WHERE DATEDIFF(`create_Date`,@current_time) < 0;
/*删除昨天之前的数据*/
DELETE FROM `apaas_log`
WHERE DATEDIFF(`create_Date`,@current_time) < 0;
/*备份昨天之前的数据*/
INSERT INTO `apaas_log_unqualified_copy`
SELECT * FROM `apaas_log_unqualified`
WHERE DATEDIFF(`create_Date`,@current_time) < 0;
/*删除昨天之前的数据*/
DELETE FROM `apaas_log_unqualified`
WHERE DATEDIFF(`create_Date`,@current_time) < 0;
END$$
DELIMITER ;
2、创建定时事件
DELIMITER $$
/*每天4点执行*/
ALTER DEFINER=`root`@`%` EVENT `apaas_log_event`
ON SCHEDULE EVERY 1 DAY STARTS '2019-02-19 04:00:00'
ON COMPLETION PRESERVE ENABLE DO CALL apaas_log_procedure()$$
DELIMITER ;
3.启动定时任务
/*启动定时任务*/
SET GLOBAL event_scheduler = 1
/*关闭定时任务*/
SET GLOBAL event_scheduler = 0
/*查看定时任务状态*/
SHOW VARIABLES LIKE '%event_sche%'
---------------------------------------------------------------------------
1.在使用之前必须确保 event_scheduler已开启
2.创建素具迁移的存储过程
DELIMITER $$
USE `yst_uic_service`$$
DROP PROCEDURE IF EXISTS `Sync_uchome_seen_history`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Sync_uchome_seen_history`(IN tabSize INT)
BEGIN
DECLARE i INT DEFAULT 0;
## 获取需同步数据的时间节点(上个月)
SET @upmonth= DATE_ADD(NOW(), INTERVAL -1 MONTH);
WHILE i < tabSize DO
SET @sqlstr=CONCAT('INSERT INTO `uchome_history_',i,'`(`icon`,`uid`,`epgId`,`detailsId`,`score`,`titleData`,`datePoint`,`dateLine`,`objtype`,`templateId`,`times`,`watchTime`)
SELECT `icon`,`uid`,`epgId`,`detailsId`,`score`,`titleData`,`datePoint`,`dateLine`,`objtype`,`templateId`,`times`,`watchTime` FROM `uchome_seen_',i,'` WHERE `dateLine`<=?');
SET @delsqlstr=CONCAT('delete from `uchome_seen_',i,'` WHERE `dateLine`<=? ');
#执行数据迁移
PREPARE _stmt FROM @sqlstr;
EXECUTE _stmt USING @upmonth;
DEALLOCATE PREPARE _stmt;
#执行迁移后的数据删除
PREPARE _stdel FROM @delsqlstr;
EXECUTE _stdel USING @upmonth;
DEALLOCATE PREPARE _stdel;
SET i := i+1;
END WHILE;
END$$
DELIMITER ;
3.创建定时任务
DELIMITER $$
ALTER DEFINER=`root`@`localhost` EVENT `Sync_uchome_seen_history`
ON SCHEDULE EVERY 1 DAY
STARTS '2015-06-29 01:30:00'
ON COMPLETION NOT PRESERVE ENABLE
DO CALL`Sync_uchome_seen_history`(10)$$
DELIMITER ;
4.关闭和开启任务时间
临时关闭: ALTER EVENT Sync_uchome_seen_history ON COMPLETION PRESERVE DISABLE;
关闭任务 : ALTER EVENT Sync_uchome_seen_history DISABLE;
临时开启: ALTER EVENT Sync_uchome_seen_histor