2 lovebosom lovebosom 于 2016.03.25 10:28 提问

求大神怎么优化sql 和存储过程呢?

需求是这样的:
每个人学习分3个阶段,查询出来每个人这3个阶段学习的开始时间和结束时间,放到一个表里t_stage_info。
第1阶段的结束时间做为第二阶段的开始时间,第2阶段学习结束时间作为第3阶段学习开始时间。

这个表结构:
t_stage_info(
idbigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
person_plan_idbigint(20) NOT NULL COMMENT 'person_plan_id',
start_timedatetime DEFAULT NULL COMMENT '开始学习时间',
end_timedatetime DEFAULT NULL COMMENT '结束学习时间',
sum_study_timeint(11) DEFAULT NULL COMMENT '当前学时',
target_study_timeint(11) DEFAULT NULL COMMENT '目标学时',
stageint(10) DEFAULT NULL COMMENT '阶段信息:1第一阶段 2第二阶段 3第三阶段',
PRIMARY KEY (
id`)
}
如果这个人学到第1个阶段( 如果学到第二阶段(12 如果学到第三阶段(>14学时),就把第1阶段和第2阶段的学习开始和结束时间更新下。
也就是说每个人在这个表里最多有3条数据,

因为是处理老数据,所以当学到第三阶段时,也需要把查出来的第一阶段的开始和结束时间放到t_stage_info表里。

SET firstEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29611);
这个是查询这个人,在第n个阶段的结束时间,是在另个表里查出来的,第1阶段的结束时间做为第二阶段的开始时间,第2阶段学习结束时间作为第3阶段学习开始时间

我这样写效率太低了。速度也慢。求大神怎么优化sql 贺存储过程呢?

代码如下:
BEGIN
#Routine body goes here...
DECLARE personPlaneId BIGINT;
DECLARE starttime datetime; #开始时间
DECLARE endtime datetime; #结束时间
DECLARE sumStudyTime INT; #当前学时
DECLARE targetStudyTime INT; #目标学时
DECLARE stage INT; #阶段信息:1第一阶段 2第二阶段 3第三阶段
DECLARE id1 BIGINT ; #t_stage_info 第一阶段主键
DECLARE id2 BIGINT ; #t_stage_info 第二阶段主键
DECLARE id3 BIGINT ; #t_stage_info 第三阶段主键
DECLARE firstEndTime datetime; #第一阶段结束时间
DECLARE secEndTime datetime; #第二阶段结束时间
DECLARE thirdEndTime datetime; #第三阶段结束时间
DECLARE done INT DEFAULT 0;

    #声明光标
        DECLARE cur1 CURSOR FOR
            SELECT p1.id,
            p1.start_learn_time start_time,
            p1.last_date end_time,
            p1.sum_study_time,p1.target_study_time, 
                case when p1.sum_study_time >12 AND p1.sum_study_time<14 THEN 1 
                    when p1.sum_study_time >14 AND p1.sum_study_time<24 THEN 2 
                    when p1.sum_study_time >24 THEN 3 END  AS stage 
            FROM t_person_plan p1 LEFT JOIN t_person_plan_record p2 
            ON p1.id = p2.person_plan_id WHERE p1.license_type = 7  #AND p2.chapter_id IN (chapterId) 
            AND DATE_FORMAT(p1.last_date,'%Y-%m-%d') <= DATE_FORMAT(NOW(),'%Y-%m-%d');

    #当游标到达尾部时,mysql自动设置done=1
  declare continue handler for SQLSTATE '02000' SET done = 1;    

    OPEN cur1;  
        REPEAT
             /* 移动游标并赋值 */  
    fetch cur1 into personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime,stage; 
            if NOT done  then   


                    #当学到第三阶段时
                    IF sumStudyTime>14 THEN

                        #获取第一阶段结束时间
                        SET firstEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29611);
                        #获取第二阶段结束时间
                        SET secEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29625);
                        #获取第三阶段结束时间
                        SET thirdEndTime = (SELECT add_time FROM t_person_plan_record WHERE person_plan_id=personPlaneId AND chapter_id=29734);

                        SET id1 = (SELECT id from t_stage_info where person_plan_id=personPlaneId AND stage=1);
                        SET id2 = (SELECT id from t_stage_info where person_plan_id=personPlaneId AND stage=2);
                        SET id3 = (SELECT id from t_stage_info where person_plan_id=personPlaneId AND stage=3);
                        #更新第一阶段数据
                        IF id1 IS NULL THEN
                            INSERT INTO t_stage_info(id,person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(default,personPlaneId,startTime,firstEndTime,sumStudyTime,targetStudyTime,1); 
                        ELSE
                            UPDATE t_stage_info SET end_time=firstEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
                        END IF;
                        #更新第二阶段数据
                        IF id2 IS NULL THEN
                            INSERT INTO t_stage_info(id,person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(default,personPlaneId,firstEndTime,secEndTime,sumStudyTime,targetStudyTime,2); 
                        ELSE
                            UPDATE t_stage_info SET end_time=secEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2;
                        END IF;
                        #更新第三阶段数据
                        IF id3 IS NULL THEN
                            INSERT INTO t_stage_info(id,person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(default,personPlaneId,secEndTime,thirdEndTime,sumStudyTime,targetStudyTime,3); 
                        ELSE
                            UPDATE t_stage_info SET end_time=thirdEndTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=3;
                        END IF;

                    END IF;
                SET done = 0;
    end if;
        UNTIL done END REPEAT;  
    close cur1; 

END

2个回答

cyxevil
cyxevil   2016.03.28 16:05

最直接的方法是建索引

lovebosom
lovebosom SELECT add_time FROM t_person WHERE pid=pidno AND page = 1 ; 这个建索引是(pid,page)的组合索引吗?add_time不用建索引吧?
一年多之前 回复
lovebosom
lovebosom SELECT add_time FROM t_person WHERE pid=pidno AND page = 1 ;
一年多之前 回复
CSDNXIAOD
CSDNXIAOD   2016.03.30 11:23

SQL存储过程事务和优化方法
存储过程与sql优化小结
----------------------biu~biu~biu~~~在下问答机器人小D,这是我依靠自己的聪明才智给出的答案,如果不正确,你来咬我啊!

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!