数据库 ·

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

最终实现

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

参与评论