MySql游标的使用以及5000万大表加索引
创建临时测试表
1 -- 初始化数据; 2 3 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-01-01 00:00:00',1,0); 4 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-01-03 00:00:00',1,0); 5 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-01-31 00:00:00',1,0); 6 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-01 00:00:00',1,0); 7 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-02 00:00:00',1,0); 8 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-03 00:00:00',1,0); 9 insert into `rms`.`temp_workdaykind` (`WorkDate`,`DayKind`,`IsDelete`) values('2022-02-04 00:00:00',1,0);
1 drop procedure if exists Proc_ClearHoliday; 2 DELIMITER $$ 3 CREATE PROCEDURE Proc_ClearHoliday() 4 BEGIN 5 declare tempWorkDay datetime; 6 declare done int; 7 -- 创建游标,并存储数据 8 declare cur_test CURSOR for 9 select WorkDate from temp_workdaykind where DayKind=1; 10 -- 游标中的内容执行完后将done设置为1 11 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 12 -- 打开游标 13 open cur_test; 14 -- 执行循环 15 posLoop:LOOP 16 -- 判断是否结束循环 17 IF done=1 THEN 18 LEAVE posLoop; 19 END IF; 20 -- 取游标中的值 21 FETCH cur_test into tempWorkDay; 22 -- 备份数据 23 insert into `rms`.`temp_WorkDayBak` (`WD_ID`,`WD_PrjCode`,`WD_RCID`,`WD_EmpNo`,`WD_WorkDay`,`DayKind`,`IsDelete`,`CreateTime`) 24 select WD_ID,WD_PrjCode, WD_RCID, WD_EmpNo,WD_WorkDay,1,0,now() from resconfig_workday where WD_WorkDay=tempWorkDay; 25 -- 删除节假日 26 delete from resconfig_workday where WD_WorkDay=tempWorkDay; 27 -- 更新备份表状态 28 update temp_WorkDayBak set IsDelete =1 where WD_WorkDay=tempWorkDay;DELIMITER $$ 29 CREATE DEFINER=`root`@`%` PROCEDURE `Proc_ClearHoliday`() 30 BEGIN 31 declare tempWorkDay datetime; 32 declare done int; 33 -- 创建游标,并存储数据 34 declare cur_test CURSOR for 35 select WorkDate from temp_workdaykind where DayKind=1; 36 -- 游标中的内容执行完后将done设置为1 37 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 38 -- 打开游标 39 open cur_test; 40 -- 执行循环 41 posLoop:LOOP 42 -- 判断是否结束循环 43 IF done=1 THEN 44 LEAVE posLoop; 45 END IF; 46 -- 取游标中的值 47 FETCH cur_test into tempWorkDay; 48 -- 备份数据 49 insert into `rms`.`temp_WorkDayBak` (`WD_ID`,`WD_PrjCode`,`WD_RCID`,`WD_EmpNo`,`WD_WorkDay`,`DayKind`,`IsDelete`,`CreateTime`) 50 select WD_ID,WD_PrjCode, WD_RCID, WD_EmpNo,WD_WorkDay,1,0,now() from resconfig_workday where WD_WorkDay=tempWorkDay; 51 -- 删除节假日 52 delete from resconfig_workday where WD_WorkDay=tempWorkDay; 53 -- 更新备份表状态 54 update temp_WorkDayBak set IsDelete =1 where WD_WorkDay=tempWorkDay; 55 END LOOP posLoop; 56 -- 释放游标 57 CLOSE cur_test; 58 59 END 60 DELIMITER $$; 61 62 END LOOP posLoop; 63 -- 释放游标 64 CLOSE cur_test; 65 66 END; 67 68 DELIMITER $$ 69 CALL Proc_ClearHoliday(); 70 DELIMITER $$
给大表加索引方法一:
①创建一个临时的新表,首先复制旧表的结构(包含索引)
②给新表加上新索引
③把旧表的数据复制过来
④重命名旧表,重命名新表的名字为旧表的名字
-- 在线mysql>5.5 Online DDL不锁表加索引二:
alter table resconfig_workday
add index Idx_WD_WorkDay(WD_WorkDay), ALGORITHM=INPLACE, LOCK=NONE;
本文来自博客园,作者:十四,转载请注明原文链接:https://www.cnblogs.com/yanghucheng/p/15829567.html