防止业务表过大做历史表迁移

Javazzc123

已于 2022-11-03 14:55:07 修改

阅读量687

点赞数

CC 4.0 BY-SA版权

文章标签： sql 数据库 servlet

于 2022-10-14 10:55:28 首次发布

本文链接：https://blog.youkuaiyun.com/Javazzc123/article/details/127316171

本文介绍了一个使用存储过程实现的日志表迁移方案，该方案能够将符合条件的数据从当前表迁移至历史表，并记录迁移日志。同时，文章还提供了一种简单的调试方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

日志表迁移，从当前表迁移到历史表。
通过存储过程，实现的逻辑：查询符合迁移条件的记录，放入游标中，通过循环遍历游标，把记录迁移到历史表，同时删除当前表对应的记录。最终记录迁移日志。可针对迁移日志进行监控，已达到对迁移失败记录重新迁移等。

CREATE PROCEDURE `transLogMoveProc`()
BEGIN

-- 需要定义接收游标数据的变量
  declare a_id bigint(20);
  declare a_user_id varchar(32) DEFAULT NULL ;
  declare a_user_name varchar(80) DEFAULT NULL ;
  declare a_remark1 varchar(32) DEFAULT NULL ;
  declare a_version varchar(20) DEFAULT NULL ;
  declare a_host_ip varchar(80) DEFAULT NULL;
  declare a_code varchar(32) DEFAULT NULL ;
  declare a_message varchar(100) DEFAULT NULL ;
  declare a_create_time timestamp   DEFAULT NULL;
  declare a_create_by varchar(32) DEFAULT NULL ;
  declare a_modify_time timestamp   DEFAULT NULL ;
  declare a_modify_by varchar(32) DEFAULT NULL ;

	-- 迁移记录数
	DECLARE i_count int default 0;
	-- 提交记录数
	DECLARE c_count int default 0;
	-- 当次迁移总数
	DECLARE all_count int default 0;
	-- 遍历数据结束标志
	DECLARE done INT DEFAULT FALSE;
	-- 当次迁移成功标志
	DECLARE execute_status INT 	DEFAULT FALSE;
	DECLARE beginTime timestamp   DEFAULT NULL ;
	DECLARE endTime timestamp   DEFAULT NULL ;
	DECLARE diffTime int default 0;
	DECLARE bcount int default 0;
	
	DECLARE rs_cursor CURSOR FOR (
	SELECT 
	  id as a_id,
		user_id as a_user_id,
		user_name as a_user_name,
		remark1 as a_remark1,
		version as a_version,
		code as a_code, 
		message as a_message,
		create_time as a_create_time ,
		create_by as a_create_by,
		modify_time as a_modify_time ,
		modify_by as a_modify_by from t_fop_trans_log_test where 
		TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1);

	-- 将结束标志绑定到游标
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;
		set beginTime = NOW();	
			-- 当次迁移总数
		select count(1) into all_count from t_fop_trans_log_test where 
		TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1;
		select all_count;
		
		-- 打开游标
		OPEN rs_cursor;
		  set AUTOCOMMIT=0;
		  -- 开始循环
		  transLog: LOOP
			  -- 提取游标里的数据
			  FETCH rs_cursor INTO a_id, a_user_id, a_user_name, a_remark1, a_version, a_code, a_message, a_create_time, a_create_by, a_modify_time, a_modify_by;
      
			  -- 声明结束
			  IF done THEN
  				LEAVE transLog;
			  END IF;
			
			-- 循环事件
		  -- 把符合迁移条件的记录迁移到历史表		  
			insert into t_trans_log_his ( `id`,`user_id`, `user_name`, `remark1`, `version`,  `code`, `message`, `create_time`, `create_by`, `modify_time`, `modify_by`) values (a_id, a_user_id, a_user_name, a_remark1, a_version,  a_code, a_message, a_create_time, a_create_by, a_modify_time, a_modify_by);
		  -- 从当前表删除已经迁移的记录
			 DELETE FROM t_trans_log_test where id = a_ID ;
			
			set i_count=i_count + 1;
    	set c_count=c_count + 1;
			-- 每10000条记录提交一次，避免频繁提交io，导致执行缓慢
			IF MOD(c_count,10000)=0 THEN
				COMMIT;
				SELECT c_count;
				SET c_count=0;
			END IF;
		END LOOP apiLog;
		
		COMMIT;
		set endTime = NOW();
	  
		set diffTime = TIMESTAMPDIFF(SECOND,beginTime,endTime);
		select 	beginTime;
	  select 	endTime;
		select 	diffTime;
		select i_count;
		-- 总记录数与迁移记录数相等，标识迁移成功
		if(i_count = all_count) then
			set execute_status = TRUE;
			-- 迁移记录表中记录当次记录，迁移总数，迁移开始时间，迁移结束时间。迁移月份等
 			insert into t_fop_apimove_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
			select count(1) into bcount from t_fop_apimove_log;
		END IF;
		if (i_count != all_count) then
			 insert into t_fop_apimove_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
			 select cout(1) into bcount from t_fop_apimove_log;
		 end if;
		 COMMIT;
		 select bcount;
		SELECT execute_status;
		-- 关闭游标
		CLOSE rs_cursor;
	END

调试存储过程
写了一大串存储过程脚本后，如何调式存储过程？网上找了比较多的方法是有专门针对mysql的存储过程的客户端dbforgemysql，但是运行调试需要比较高的用户权限，一般正常使用用户是没有权限去调试的。
比较常规的方法是，存储过程中，在你想断点debug的地方，增加变量，然后select @变量可打印出调试信息。

-- 当次迁移总数
select count(1) into all_count from t_fop_trans_log_test where
TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1;
-- 这里就是调试信息，运行存储过程时，如果前面都没有报错，则可在控制台打印出all_count的值
select all_count;

-- 总记录数与迁移记录数相等，标识迁移成功
		if(i_count = all_count) then
			set execute_status = TRUE;
			-- 迁移记录表中记录当次记录，迁移总数，迁移开始时间，迁移结束时间。迁移月份等
 			insert into t_move_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
			select count(1) into bcount from t_fop_apimove_log;
		END IF;
		if (i_count != all_count) then
			 insert into t_move_log(`move_count`,`use_time`, `move_time`, `move_status`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status);
		 end if;

根据文档写了自己的数据迁移

改变了取数条件，现在的取数条件是数据库里【创建时间】字段小于当前运行时间【 now（）】的所有数据将被迁移

BEGIN
-- 需要定义接收游标数据的变量
declare a_id bigint(20);
declare a_app_id varchar(100) DEFAULT NULL ;
declare a_project_no  varchar(100) DEFAULT NULL ;
declare a_customer_no  varchar(100) DEFAULT NULL ;
declare a_flow_no  varchar(100) DEFAULT NULL ;
declare a_method  varchar(100) DEFAULT NULL ;
declare a_version  varchar(20) DEFAULT NULL ;
declare a_params varchar(1000) DEFAULT NULL ;
declare a_key varchar(255) DEFAULT NULL ;
declare a_sign varchar(1000) DEFAULT NULL ;
declare a_timestamp bigint(50) DEFAULT NULL ;
declare a_success varchar(10) DEFAULT NULL ;
declare a_resp_code varchar(10) DEFAULT NULL ;
declare a_resp_msg varchar(255) DEFAULT NULL ;
declare a_data varchar(1000) DEFAULT NULL ;
declare a_r_sign varchar(1000) DEFAULT NULL ;
declare a_r_key varchar(255) DEFAULT NULL ;
declare a_r_timestamp  bigint(50) DEFAULT NULL ;
declare a_create_user  varchar(50) DEFAULT NULL ;
declare a_update_user  varchar(50) DEFAULT NULL ;
declare a_create_time  datetime DEFAULT NULL ;
declare a_update_time  datetime DEFAULT NULL ;

	-- 迁移记录数
	DECLARE i_count int default 0;
	-- 提交记录数
	DECLARE c_count int default 0;
	-- 当次迁移总数
	DECLARE all_count int default 0;
	-- 遍历数据结束标志
	DECLARE done INT DEFAULT FALSE;
	-- 当次迁移成功标志
	DECLARE execute_status INT 	DEFAULT FALSE;
	DECLARE beginTime timestamp   DEFAULT NULL ;
	DECLARE endTime timestamp   DEFAULT NULL ;
	DECLARE diffTime int default 0;
	DECLARE bcount int default 0;

	-- 当前时间
	DECLARE nowTime datetime default NOW();

	DECLARE rs_cursor CURSOR FOR (
											SELECT 
											id			         as  a_id,
											app_id           as  a_app_id,
											project_no       as  a_project_no,
											customer_no      as  a_customer_no,
											flow_no          as  a_flow_no,
											method           as  a_method,
											version          as  a_version ,
											params           as  a_params,
											`key`            as  a_key,
											`sign`           as  a_sign,
											`timestamp`      as  a_timestamp,
											success          as  a_success,
											resp_code        as  a_resp_code,
											resp_msg         as  a_resp_msg,
											`data`           as  a_data,
											r_sign           as  a_r_sign,
											r_key            as  a_r_key,
											r_timestamp      as  a_r_timestamp,
											create_user      as  a_create_user,
											update_user      as  a_update_user,
											create_time      as  a_create_time,
											update_time      as  a_update_time
											FROM td_qmqb_trade_data_info 
											WHERE  create_time < nowTime
		-- TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1
);

	-- 将结束标志绑定到游标
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE;

		set beginTime = NOW();	
	-- 当次迁移总数
		select count(1) into all_count from td_qmqb_trade_data_info where 
    create_time < nowTime ;
	-- TIMESTAMPDIFF(MONTH,DATE_FORMAT(tran_date,'%Y-%m-%d %H:%i:%S'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S')) >= 1;
		-- select all_count;

		-- 打开游标
		OPEN rs_cursor;
		  set AUTOCOMMIT=0;
		  -- 开始循环
		  transLog: LOOP
			  -- 提取游标里的数据
			  FETCH rs_cursor INTO a_id, a_app_id, a_project_no, a_customer_no, a_flow_no,a_method, a_version , a_params,a_key,a_sign, a_timestamp,
														 a_success,a_resp_code,a_resp_msg,a_data,a_r_sign,a_r_key,a_r_timestamp,a_create_user,a_update_user,a_create_time,a_update_time;
      
			  -- 声明结束
			  IF done THEN
  				LEAVE transLog;
			  END IF;		
			-- 循环事件
		  -- 把符合迁移条件的记录迁移到历史表		  
			INSERT INTO `td_qmqb_trade_data_info_his` (`id`, `app_id`, `project_no`, `customer_no`, `flow_no`, `method`, `version`, `params`, `key`, `sign`, `timestamp`, `success`, `resp_code`, `resp_msg`, `data`, `r_sign`, `r_key`, `r_timestamp`, `create_user`, `update_user`, `create_time`, `update_time`) 
																		   VALUES  (a_id ,a_app_id, a_project_no, a_customer_no, a_flow_no, a_method,  a_version,a_params, a_key, a_sign, a_timestamp, a_success, a_resp_code, a_resp_msg, a_data, a_r_sign,  a_r_key,a_r_timestamp, a_create_user,  a_update_user,a_create_time,  a_update_time);
		  
			-- 从当前表删除已经迁移的记录     
			 DELETE FROM td_qmqb_trade_data_info where id = a_ID ;
			
			set i_count=i_count + 1;
    	set c_count=c_count + 1;
			-- 每10000条记录提交一次，避免频繁提交io，导致执行缓慢
			IF MOD(c_count,10000)=0 THEN
				COMMIT;
				-- SELECT c_count;
				SET c_count=0;
			END IF;
		END LOOP ; -- apiLog;	
		COMMIT;

		set endTime = NOW(); 
		set diffTime = TIMESTAMPDIFF(SECOND,beginTime,endTime);
		-- select 	beginTime;
	  -- select 	endTime;
		-- select 	diffTime;
		-- select  i_count;
		-- 总记录数与迁移记录数相等，标识迁移成功
		if(i_count = all_count) then
			set execute_status = TRUE;
			-- 迁移记录表中记录当次记录，迁移总数，迁移开始时间，迁移结束时间。迁移月份等
 			insert into td_qmqb_data_move_log(`move_count`,`use_time`, `move_time`, `move_status`,`create_time`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status ,NOW());
			-- select count(1) into bcount from td_qmqb_data_move_log;
		END IF;
		if (i_count != all_count) then
			 insert into td_qmqb_data_move_log(`move_count`,`use_time`, `move_time`, `move_status`,`create_time`) VALUES (i_count, diffTime,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'), execute_status,NOW());
			 -- select count(1) into bcount from td_qmqb_data_move_log;
		 end if;
		 COMMIT;
		 -- select bcount;
		 SELECT execute_status;
		-- 关闭游标
		CLOSE rs_cursor;	
END

调用：

设置数据库的定时任务调用：


-- 查看定时策略是否开启
show variables like '%event_sche%';
-- 开启定时策略（ON是开启状态）
set global event_scheduler=1;
-- 创建procedure(存储过程)
-- use 123;
-- delimiter //
-- create procedure updateTime()
-- begin
-- update demo_table set demo_time = DATE_ADD(NOW(),  INTERVAL  FLOOR(1 + (RAND() * 10800))   SECOND );
-- end//
-- delimiter ;

-- 创建定时任务
create event qmqbDataInfoHisMove_scheduler
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
on completion preserve disable
-- do call updateTime();
do CALL qmqbDataInfoHisMoveProc;
-- 查看定时任务事件
SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;
-- 开启和关闭创建好的事件 //开启定时任务
alter event qmqbDataInfoHisMove_scheduler on completion preserve enable;
-- //关闭定时任务
alter event qmqbDataInfoHisMove_scheduler on completion preserve disable;
-- 删除
drop event qmqbDataInfoHisMove_scheduler;


-- 单位有：SECOND,MINUTE,HOUR,DAY,WEEK(周),QUARTER(季度),MONTH,YEAR
-- 每秒执行1次
ON SCHEDULE EVERY 1 SECOND
-- 每两分钟执行1次
ON SCHEDULE EVERY 2 MINUTE
-- 每3天执行1次
ON SCHEDULE EVERY 3 DAY
-- 5天后执行
ON SCHEDULE AT CURRENT_TIMESTAMP()+INTERVAL 5 DAY
-- 10分钟后执行
ON SCHEDULE AT CURRENT_TIMESTAMP()+INTERVAL 10 MINUTE
-- 在2016年10月1日，晚上9点50执行
ON SCHEDULE AT '2021-12-01 1:50:00'
-- 5天后开始每天都执行执行到下个月底
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP()+INTERVAL 5 DAY ENDS CURRENT_TIMESTAMP()+INTERVAL 1 MONTH
-- 从现在起每天执行，执行5天
ON SCHEDULE EVERY 1 DAY ENDS CURRENT_TIMESTAMP()+INTERVAL 5 DAY
-- 每天凌晨一点执行
ON SCHEDULE EVERY 1 DAY STARTS DATE_ADD(DATE_ADD(CURDATE(), INTERVAL 1 DAY), INTERVAL 1 HOUR)
-- 每个月的五号一点执行一次
ON SCHEDULE EVERY 5 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1 MONTH),INTERVAL 1 HOUR)
-- 每年一月一号凌晨三点执行一次
ON SCHEDULE  EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 3 HOUR)


show plugins
创建表  
-- 表 分区
partition by hash(id)
partitions 3;



drop table if exists `range_table`;
create table `range_table`(
	`id` int,
	`name` varchar(10)
) 
partition by range(id)(
	partition p1 values less than (10),
	partition p2 values less than (20),
	partition p3 values less than maxvalue
);

select * from range_table partition (p1);