mysql-定时任务

在学习MySQL时，遇到大批量的数据产生时，数据库中会存储大量的历史数据，为了mysql性能的优化，对数据库进行优化非常有必要，对数据库语句进行优化是一部分增加性能的一个方法，定时任务对数据库历史数据（不重要的和交易没有影响的数据）进行迁移也是很好的增加数据库性能的方法，以下是我写的很简单的定时任务迁移。

定时任务特别注意：定时任务的事件计划一定要打开

一、查看定时任务的时间列表在这个表里面， information_schema.events；记住了

-- 查看定时任务的事件列表
SELECT * FROM information_schema.events; 
-- 先查看mysql的事件是否打开
show variables like '%sche%';
-- 设置将mysql的事件计划打开或关闭，只有在打开的状态下才行
SET GLOBAL event_scheduler = 0; -- 0/OFF都可   
SET GLOBAL event_scheduler = 1;  -- 1/ON都可，这里表示打开

1、创建新表来存一个系统时间

-- 这里就是用来创建一个表用来存系统的时间  Mysql引擎选择InnerDB
-- 新建一个存时间的表
DROP TABLE IF EXISTS systemDate;
CREATE TABLE systemDate(
	systemDate VARCHAR(20)
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
COMMIT;

2、创建一个新表来存数据迁移定时任务事件发生时间的表

--同样的 Mysql引擎选择InnerDB 格式仍然是utf-8
-- 新建一个存定时任务运行时间的表
DROP TABLE IF EXISTS systemDateEvent;
CREATE TABLE systemDateEvent(
	systemDate VARCHAR(20)
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
COMMIT;

3、创建一个函数来获取当前MySQL数据的系统时间进行返回

-- 创建函数获得当前的系统时间
DROP FUNCTION  IF EXISTS  getSystemDate;
commit;
CREATE FUNCTION getSystemDate() RETURNS varchar(20) CHARSET utf8
BEGIN 
	DECLARE 
		nowTime VARCHAR(20);
	SELECT
		SYSDATE() INTO nowTime
	FROM
		dual;
	RETURN nowTime;
END;
commit;

函数运行以下就出来结果了，其实就是使用dual这个表，select sysdate() from dual;自己很懒

4、创建一个存储过程调用getSystemDate()函数对systemDate以及systemDateEvent进行插值，写的很简单就是插一个值

-- 创建不断更新系统时间存储过程
DROP PROCEDURE IF EXISTS getSystemDateTime;
delimiter //
CREATE PROCEDURE getSystemDateTime()
BEGIN
		INSERT INTO systemDateEvent VALUES(getSystemDate());
		INSERT INTO systemDate VALUES(getSystemDate());
		COMMIT;
END; //
COMMIT;

5、创建一个定时任务，每一个分钟进行调度存储过程，就是这么简单，我就写这么简单

-- 定时任务
-- 定时调度 每秒定时执行一次
DROP EVENT IF EXISTS getSystemDateEvent;
CREATE EVENT getSystemDateEvent
ON SCHEDULE EVERY 1 MINUTE
DO CALL getSystemDateTime();
commit;

二、注意下面才是重点，上面是造数据，要等十几分钟才有一点数据，闲的着急可以扣手机，现在才是数据迁移嘿嘿嘿

1、创建一个存定时任务运行时间的备份表，这里是存的是systemDateEvent这个表的数据，将定时任务运行时间表根据当前系统时间保留前5分钟的数据，大于5分钟的进行迁移到systemDateEvent_bak

-- 新建一个存定时任务运行时间表迁移的数据表
DROP TABLE IF EXISTS systemDateEvent_bak;
CREATE TABLE systemDateEvent_bak(
	systemDate VARCHAR(20)
) ENGINE = INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
COMMIT;

2、之后就是创建一个存储过程给定时任务进行调度

-- 新建一个存储过程   模拟数据迁移将定时任务运行时间表根据当前系统时间保留前5分钟的数据，大于5分钟的进行迁移
drop PROCEDURE IF EXISTS systemDateEventTransfer;
delimiter //
CREATE PROCEDURE systemDateEventTransfer()
BEGIN 
	declare systemDates VARCHAR(20);
	-- 创建结束标志变量  
  declare done int default false;  
  -- 创建游标  
  declare cur cursor for SELECT
		systemDate as systemDates
	FROM systemDateEvent WHERE  (UNIX_TIMESTAMP(getSystemDate()) - UNIX_TIMESTAMP(systemDate)) > 300;
	-- 指定游标循环结束时的返回值  
	declare continue HANDLER for not found set done = true;  
	-- 打开游标  
	open cur;  
		-- 开始循环游标里的数据  
		read_loop:loop  
		-- 根据游标当前指向的一条数据  
		fetch cur into systemDates;
		-- 判断游标的循环是否结束  
			if done then  
				leave read_loop;    -- 跳出游标循环  
			end if; 
			-- 将需要迁移的数据进行迁移
			INSERT INTO systemDateEvent_bak (systemDate) VALUES (systemDates);
			commit;
			-- 删除迁移的数据
			delete from systemDateEvent where systemDate=systemDates;
			commit;
		-- 结束游标循环  
		end loop;  
	-- 关闭游标  
	close cur;  
END;//
COMMIT;

3、最后啦，创建一个定时任务来调度这个存储过程systemDateEventTransfer，好好结束了搞定

-- 数据迁移的定时事件  每五分钟进行迁移一次
DROP EVENT IF EXISTS systemDateEventTransferEvent;
CREATE EVENT systemDateEventTransferEvent
ON SCHEDULE EVERY 5 MINUTE
DO CALL systemDateEventTransfer();
commit;

4、接下来就是每五分钟运行定时任务将定时任务表里面前五分钟之前的全部迁移到备份表中，开心搞定

三、下面自己又手贱觉得定时任务运行时间表数据就算迁移到备份表，但是备份表中还是增加数据啊，并没有减轻多大MySQL性能，还是删掉跑路比较好，先删库后跑路。

1、所以我又双叒叕创建了一个定时任务，把定时任务运行时间备份表中的数据弄成只保留十五分钟的，前面15分钟的全部删掉，就是删掉没毛病，所以又多了一个存储过程一个定时任务

2、存储过程是这个

-- 就是通过查询systemdateevent_bak  这个备份表里面根据系统时间推到前15分钟，至于15分钟的删！！！！不犹豫
-- 创建不断更新系统时间存储过程
DROP PROCEDURE IF EXISTS deleteSystemDateEventTime;
delimiter //
CREATE PROCEDURE deleteSystemDateEventTime()
BEGIN
		delete  FROM systemdateevent_bak  WHERE  (UNIX_TIMESTAMP(getSystemDate()) - UNIX_TIMESTAMP(systemDate)) > 900;
		COMMIT;
END; //
COMMIT;

3、定时任务是这个

-- 数据迁移的定时事件  每5分钟进行迁移一次
DROP EVENT IF EXISTS deleteSystemDateEventTimeEvent;
CREATE EVENT deleteSystemDateEventTimeEvent
ON SCHEDULE EVERY 5 MINUTE
DO CALL deleteSystemDateEventTime();
commit;

四、好的MySQL定时任务就学到这里！