记一次添表引发的数据修复
最终实现
项目中积分部分之前每次查询用户总积分都是动态汇总，并未存到数据库中，数据量一大就会发现查询速度超慢，为缓解该问题，现在需要将汇总的数据持久化，新增了一个SYS_POINTS_SUM_DETAIL表，此时就涉及到数据修复问题。本文就是对修复脚本的记录，里面覆盖了众多基础语法，亦可当做学习实例。
Oracle的使用PL/SQL语句，MySQL的使用了存储过程+游标。目标是通过将SYS_POINTS_DETAIL中的存储的用户积分记录按用户汇总，将每个人的汇总的总积分、剩余积分、已用积分、过期积分存储到SYS_POINTS_SUM_DETAIL表中。
脚本中的注释均可删除。
Oracle 积分数据修复
Oracle的SYS_POINTS_DETAIL表中含有144546条积分数据，涉及14691个用户，下面修复SQL脚本执行用了6s左右。
这里的PL/SQL涉及基础知识有：变量多种定义与赋值，FOR-IN-LOOP循环，IF条件语句、手动分页查询等。
优化查询，首先应是考虑添加索引，索引无法完全满足后，在考虑拆表等方案。
最开始MySQL中SYS_POINTS_DETAIL表的user_id未添加索引，在里面执行修复脚本时一直跑了上千秒之多，甚至导致无法停止存储过程，只能强行终止，然而当对user_id添加索引后，几乎瞬间变执行完了，可见适当的索引的重要性。
Oracle添加索引的方式：
create Index points_detail_user on SYS_POINTS_DETAIL(user_id);
1
Oracle修复脚本关键SQL
积分类型可能是积分、金币等并不确定，故最外层有这样一层循环。之后手动实现分页查询用户，并汇总该用户的各项积分存入SYS_POINTS_SUM_DETAIL表。
-- 声明变量
declare
v_now date := sysdate;
v_root_site_id sys_site.id%type;
v_system_user_id sys_user.id%type;
v_user_count SYS_POINTS_DETAIL.user_id%type;
v_id SYS_POINTS_SUM_DETAIL.id%type;
pageNumber NUMBER(11);
pageSize NUMBER(11) := 100;
pageOffset NUMBER(11) := 0;
obtainScoreSum SYS_POINTS_DETAIL.obtain_score%type :=0;
obtainScoreActualSum SYS_POINTS_DETAIL.obtain_score%type :=0;
usedScoreSum SYS_POINTS_DETAIL.obtain_score%type :=0;
endDateScoreSum SYS_POINTS_DETAIL.obtain_score%type :=0;
-- 开始
BEGIN
SELECT COUNT(DISTINCT user_id) INTO v_user_count FROM SYS_POINTS_DETAIL;
select min(id) into v_system_user_id from sys_user;
SELECT MAX(id) INTO v_id FROM SYS_POINTS_SUM_DETAIL;
-- 循环积分类型
FOR a_type In (SELECT id FROM SYS_POINTS_TYPE) LOOP
-- 获取用户总页数
pageNumber := CEIL(v_user_count / pageSize)-1;
FOR a IN 0 .. pageNumber LOOP
-- 分页查询用户信息
pageOffset := pageSize*a;
FOR userObj IN ( SELECT * FROM ( SELECT user_id,site_id,ROWNUM RN_ FROM (SELECT DISTINCT user_id,site_id FROM SYS_POINTS_DETAIL )u)u2 WHERE RN_ > pageOffset AND RN_ <= pageOffset+pageSize ) LOOP
-- 汇总用户积分并存入汇总表
SELECT SUM(obtain_score) INTO obtainScoreSum FROM SYS_POINTS_DETAIL WHERE user_id = userObj.user_id AND site_id = userObj.site_id;
SELECT SUM(obtain_score_actual) INTO obtainScoreActualSum FROM SYS_POINTS_DETAIL WHERE user_id = userObj.user_id AND site_id = userObj.site_id AND status in ('EFFECT','USED') ;
SELECT SUM(obtain_score_actual) INTO endDateScoreSum FROM SYS_POINTS_DETAIL WHERE user_id = userObj.user_id AND site_id = userObj.site_id AND status = 'ENDDATE' ;
IF (v_id IS NULL) THEN
v_id := 0;
END IF;
v_id := v_id + 1;
IF (obtainScoreActualSum IS NULL) THEN
obtainScoreSum := 0;
END IF;
IF (endDateScoreSum IS NULL) THEN
endDateScoreSum := 0;
END IF;
usedScoreSum := obtainScoreSum - obtainScoreActualSum - endDateScoreSum;
INSERT INTO SYS_POINTS_SUM_DETAIL(ID,CREATED_DATE,LAST_MODIFIED_DATE,IS_DELETED,CREATED_BY,LAST_MODIFIED_BY,OBTAIN_SCORE_SUM,OBTAIN_SCORE_ACTUAL_SUM,USED_SCORE_SUM,END_DATE_SCORE_SUM,POINTS_TYPE_ID,USER_ID,SITE_ID)VALUES(v_id,v_now,v_now,0,v_system_user_id,v_system_user_id,obtainScoreSum,obtainScoreActualSum,usedScoreSum,endDateScoreSum,a_type.id,userObj.user_id,userObj.site_id);
END LOOP;
END LOOP;
END LOOP;
COMMIT;
END;
/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
MySQL 积分数据修复。
MySQL的SYS_POINTS_DETAIL表中含有167040条积分数据，涉及7748个用户，下面修复SQL脚本执行用了17s左右。
最开始想看MySQL是否有Oracle这种PL/SQL语法，如此就能省事些，搜索时发现现实很骨感，不但没有PL/SQL，连FOR-IN-LOOP循环都没有。最后也只找到了存储过程+游标的方式。没看到有对游标进行重新赋值的，所以暂时也没做分页查询，而是将用户一下全查了出来，这里可能存在可优化的地方。
这里的修复SQL涉及基础知识有：变量声明与多种赋值方式，创建/使用/删除存储过程，循环嵌套游标等。
MySQL添加索引的方式：
-- 添加索引
ALTER TABLE SYS_POINTS_DETAIL ADD INDEX points_detail_user ( `user_id` );
1
2
2
MySQL修复脚本关键SQL
-- 数据修复
DELIMITER $$
DROP PROCEDURE IF EXISTS sumPoints $$
CREATE PROCEDURE sumPoints()
BEGIN
DECLARE v_root_site_id bigint default 0;
DECLARE v_system_user_id bigint default 0;
DECLARE v_user_count int default 0;
DECLARE v_id bigint default 0;
DECLARE v_now datetime;
DECLARE obtainScoreSum int default 0;
DECLARE obtainScoreActualSum int default 100;
DECLARE usedScoreSum int default 0;
DECLARE endDateScoreSum int default 0;
DECLARE points_type_id bigint default 0;
DECLARE p_user_id bigint default 0;
DECLARE p_site_id bigint default 0;
DECLARE stopFlag INT DEFAULT 0;
DECLARE user_count INT DEFAULT 0;
DECLARE points_type_cur CURSOR FOR SELECT id FROM SYS_POINTS_TYPE;
DECLARE user_cur CURSOR FOR SELECT DISTINCT user_id,site_id FROM SYS_POINTS_DETAIL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;
SELECT min(id) INTO v_root_site_id FROM sys_site;
SELECT min(id) INTO v_system_user_id FROM sys_user;
SELECT COUNT(DISTINCT user_id) INTO v_user_count FROM SYS_POINTS_DETAIL;
SELECT MAX(id) INTO v_id FROM SYS_POINTS_SUM_DETAIL;
SET v_now = now();
-- 打开游标points_type_cur
OPEN points_type_cur;
points_type_loop: LOOP
-- 获取游标的数据。
FETCH points_type_cur INTO points_type_id;
SET user_count = 0;
IF stopFlag = 1 THEN
LEAVE points_type_loop;
END IF;
-- 打开游标user_cur
OPEN user_cur;
user_loop: LOOP
FETCH user_cur INTO p_user_id,p_site_id;
IF stopFlag = 1 THEN
LEAVE user_loop;
END IF;
SELECT SUM(obtain_score) INTO obtainScoreSum FROM SYS_POINTS_DETAIL WHERE user_id = p_user_id AND site_id = p_site_id;
SELECT SUM(obtain_score_actual) INTO obtainScoreActualSum FROM SYS_POINTS_DETAIL WHERE user_id = p_user_id AND site_id = site_id AND status in ('EFFECT','USED') ;
SELECT SUM(obtain_score_actual) INTO endDateScoreSum FROM SYS_POINTS_DETAIL WHERE user_id = p_user_id AND site_id = site_id AND status = 'ENDDATE' ;
IF (v_id IS NULL) THEN
SET v_id = 0;
END IF;
SET v_id = v_id + 1;
IF (obtainScoreActualSum IS NULL) THEN
SET obtainScoreSum = 0;
END IF;
IF (endDateScoreSum IS NULL) THEN
SET endDateScoreSum = 0;
END IF;
SET usedScoreSum = obtainScoreSum - obtainScoreActualSum - endDateScoreSum;
INSERT INTO SYS_POINTS_SUM_DETAIL(ID,CREATED_DATE,LAST_MODIFIED_DATE,IS_DELETED,CREATED_BY,LAST_MODIFIED_BY,OBTAIN_SCORE_SUM,OBTAIN_SCORE_ACTUAL_SUM,USED_SCORE_SUM,END_DATE_SCORE_SUM,POINTS_TYPE_ID,USER_ID,SITE_ID)VALUES(v_id,v_now,v_now,0,v_system_user_id,v_system_user_id,obtainScoreSum,obtainScoreActualSum,usedScoreSum,endDateScoreSum,points_type_id,p_user_id,p_site_id);
SET user_count = user_count +1;
IF user_count = v_user_count THEN
SET stopFlag = 1;
END IF;
END LOOP user_loop;
CLOSE user_cur;
SET stopFlag=0;
END LOOP points_type_loop;
CLOSE points_type_cur;
END $$
DELIMITER ;
-- 执行存储过程
call sumPoints();
-- 删除存储过程
drop procedure if exists sumPoints
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
预览
除特别注明外，本站所有文章均为 竹风清语 原创，转载请注明出处来自： jiyicitianbiaoyinfadeshujuxiufu
Loading comments...
预览
暂无数据