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

项目中积分部分之前每次查询用户总积分都是动态汇总,并未存到数据库中,数据量一大就会发现查询速度超慢,为缓解该问题,现在需要将汇总的数据持久化,新增了一个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可能排版不太友好,可以点击原文查看原始排版。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181204G09FYY00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券