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不用建索引吧?
2 年多之前 回复
lovebosom
lovebosom SELECT add_time FROM t_person WHERE pid=pidno AND page = 1 ;
2 年多之前 回复
CSDNXIAOD
CSDNXIAOD   2016.03.30 11:23

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

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
存储过程与sql优化小结
由于工作中接触存储过程比较多点,在这里大概记录下解决
C# WPF 扫雷
有个不好的地方,就是开始的时候会卡住,求大神告知怎么优化。
SQLServer 复杂存储过程并发优化(案例)
一个存储过程,1500行代码,内部有一个查询,关联使用了200多张表(其中有重复的表),并发线程执行,耗时15秒左右,结果返回一般几行记录。这个存储过程是系统中最耗时、最消耗性能的。今天突然想着得优化一下了! 取出存储过程内的查询,声明相关参数执行脚本。查看执行计划,其中有这么一段,如图: 有操作符 Filter ,将87万行数据变成了1行!这点是可以优化的点,
oracle 游标,存储过程,SQL优化的总结
游标,存储过程,SQL优化。 游标,存储过程,SQL优化 游标,存储过程,SQL优化 游标,存储过程,SQL优化
ORACLE-SQL存储过程优化
在SQL语句优化过程中,我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法: 1. 表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 例如: SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT'; 2. 表明对语句块选择基于开销的优化方法,并获得最佳响应
TSQL 存储过程 游标 数据库 sql优化 存储过程分页
TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页 TSQL 存储过程 游标 数据库 sql优化 存储过程分页
SQL优化(四) PostgreSQL存储过程
本文介绍了存储过程的概念,优势,并结合实例讲解了存储过程在PostgreSQL中的实现,注意事项
基于Oracle PLSQL的存储过程性能优化方法案例
【前言】:最近忙着一个项目,Oracle有个JOB基于定时任务处理,经过我多次冥思苦索优化后,该存储过程最终依然需要花费1小时6分。这个JOB处理是为了生成年度、季度、月度三张统计分析报表以供用户查询使用,但客户知情后,指出必须实现时时查询汇总。由于鄙人技术能力有限,领导安排协调oracle高手来上海协助解决该问题,经过两天的学习,从中也跟这位师父学到不少东西,因此,今天特此总结,以供分享学习。
存储过程-------------代码优化、逻辑优化
在前两天做注册时,需要考虑学生表、卡信息表、充值信息表等多个表,类似与这种情况需要操作多个表的操作(充值、退卡、上机、下机、结账等等),如果还是用原来的办法多个函数重复的调用,太浪费精力了,代码麻烦,调用关系搞不好还乱了,第一次做机房的时候,听说过这个词感觉特别难,存储过程很是神奇,现在来看,也就那么回事(走过了,发现啥事不能事先被自己所吓倒),先来理解存储过程的相关知识 下面是自己重构机房收费
关于Csharp通过url下载的问题
为什么documentComplete 就是不起作用呢?为什么反复的循环呢?求大神解释下