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;

posted @ 2022-01-21 11:22  十四  阅读(178)  评论(0)  编辑  收藏  举报