最终实现
项目中积分部分之前每次查询用户总积分都是动态汇总，并未存到数据库中，数据量一大就会发现查询速度超慢，为缓解该问题，现在需要将汇总的数据持久化，新增了一个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);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;/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` );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
评论