记一次添表引发的数据修复

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

createIndexpoints_detail_useronSYS_POINTS_DETAIL(user_id);

Oracle修复脚本关键SQL

积分类型可能是积分、金币等并不确定，故最外层有这样一层循环。之后手动实现分页查询用户，并汇总该用户的各项积分存入SYS_POINTS_SUM_DETAIL表。

--声明变量

declare v_nowdate:=sysdate; v_root_site_idsys_site.id%type; v_system_user_idsys_user.id%type; v_user_countSYS_POINTS_DETAIL.user_id%type; v_idSYS_POINTS_SUM_DETAIL.id%type; pageNumberNUMBER(11); pageSizeNUMBER(11) :=100; pageOffsetNUMBER(11) :=; obtainScoreSumSYS_POINTS_DETAIL.obtain_score%type :=; obtainScoreActualSumSYS_POINTS_DETAIL.obtain_score%type :=; usedScoreSumSYS_POINTS_DETAIL.obtain_score%type :=; endDateScoreSumSYS_POINTS_DETAIL.obtain_score%type :=;--开始BEGINSELECTCOUNT(DISTINCT user_id) INTO v_user_countFROMSYS_POINTS_DETAIL;selectmin(id) into v_system_user_idfromsys_user;SELECTMAX(id) INTO v_idFROMSYS_POINTS_SUM_DETAIL;--循环积分类型FOR a_typeIn(SELECTidFROMSYS_POINTS_TYPE) LOOP

--获取用户总页数pageNumber :=CEIL(v_user_count/pageSize)-1; FOR aIN.. pageNumber LOOP

--分页查询用户信息pageOffset :=pageSize*a; FOR userObjIN(SELECT*FROM(SELECTuser_id,site_id,ROWNUM RN_FROM(SELECT DISTINCTuser_id,site_idFROMSYS_POINTS_DETAIL )u)u2WHERERN_>pageOffsetANDRN_

COMMIT;END;

/

MySQL 积分数据修复。

MySQL的SYS_POINTS_DETAIL表中含有167040条积分数据，涉及7748个用户，下面修复SQL脚本执行用了17s左右。

最开始想看MySQL是否有Oracle这种PL/SQL语法，如此就能省事些，搜索时发现现实很骨感，不但没有PL/SQL，连FOR-IN-LOOP循环都没有。最后也只找到了存储过程+游标的方式。没看到有对游标进行重新赋值的，所以暂时也没做分页查询，而是将用户一下全查了出来，这里可能存在可优化的地方。

这里的修复SQL涉及基础知识有：变量声明与多种赋值方式，创建/使用/删除存储过程，循环嵌套游标等。

MySQL添加索引的方式：

--添加索引

ALTERTABLESYS_POINTS_DETAIL ADD INDEX points_detail_user (`user_id`);

MySQL修复脚本关键SQL

--数据修复

DELIMITER $$DROP PROCEDURE IF EXISTS sumPoints $$CREATE PROCEDURE sumPoints()BEGINDECLARE v_root_site_idbigintdefault; DECLARE v_system_user_idbigintdefault; DECLARE v_user_countintdefault; DECLARE v_idbigintdefault; DECLARE v_now datetime; DECLARE obtainScoreSumintdefault; DECLARE obtainScoreActualSumintdefault100; DECLARE usedScoreSumintdefault; DECLARE endDateScoreSumintdefault; DECLARE points_type_idbigintdefault; DECLARE p_user_idbigintdefault; DECLARE p_site_idbigintdefault; DECLARE stopFlagINTDEFAULT; DECLARE user_countINTDEFAULT; DECLARE points_type_cur CURSOR FORSELECTidFROMSYS_POINTS_TYPE; DECLARE user_cur CURSOR FORSELECT DISTINCTuser_id,site_idFROMSYS_POINTS_DETAIL; DECLARE CONTINUE HANDLER FOR NOT FOUNDSETstopFlag=1;SELECTmin(id) INTO v_root_site_idFROMsys_site;SELECTmin(id) INTO v_system_user_idFROMsys_user;SELECTCOUNT(DISTINCT user_id) INTO v_user_countFROMSYS_POINTS_DETAIL;SELECTMAX(id) INTO v_idFROMSYS_POINTS_SUM_DETAIL;SETv_now=now();--打开游标points_type_curOPEN points_type_cur; points_type_loop: LOOP

--获取游标的数据。FETCH points_type_cur INTO points_type_id;

SETuser_count=; IF stopFlag=1THENLEAVE points_type_loop;END IF;

--打开游标user_curOPEN user_cur;user_loop: LOOPFETCH user_cur INTO p_user_id,p_site_id;IF stopFlag=1THENLEAVE user_loop;END IF;SELECTSUM(obtain_score) INTO obtainScoreSumFROMSYS_POINTS_DETAILWHEREuser_id=p_user_idANDsite_id=p_site_id;SELECTSUM(obtain_score_actual) INTO obtainScoreActualSumFROMSYS_POINTS_DETAILWHEREuser_id=p_user_idANDsite_id=site_idANDstatusin('EFFECT','USED') ;SELECTSUM(obtain_score_actual) INTO endDateScoreSumFROMSYS_POINTS_DETAILWHEREuser_id=p_user_idANDsite_id=site_idANDstatus='ENDDATE'; IF (v_id ISNULL) THEN

SETv_id=;END IF;SETv_id=v_id+1;IF (obtainScoreActualSum ISNULL) THEN

SETobtainScoreSum=;END IF; IF (endDateScoreSum ISNULL) THEN

SETendDateScoreSum=;END IF;SETusedScoreSum=obtainScoreSum-obtainScoreActualSum-endDateScoreSum;

INSERT INTOSYS_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,,v_system_user_id,v_system_user_id,obtainScoreSum,obtainScoreActualSum,usedScoreSum,endDateScoreSum,points_type_id,p_user_id,p_site_id);SETuser_count=user_count+1;

IF user_count=v_user_count THEN

SETstopFlag=1;END IF;END LOOP user_loop;CLOSE user_cur;

SETstopFlag=; END LOOP points_type_loop; CLOSE points_type_cur;END $$DELIMITER ;

--执行存储过程

call sumPoints();

--删除存储过程

drop procedure if exists sumPoints

复制过来的SQL可能排版不太友好，可以点击原文查看原始排版。