mysql-請教存儲過程執行很慢，如何優化？

請教存儲過程執行很慢，如何優化？

BEGIN
DECLARE personPlaneId BIGINT;

DECLARE starttime datetime; #開始時間
DECLARE endtime datetime; #結束時間
DECLARE sumStudyTime INT; #當前學時
DECLARE targetStudyTime INT; #目標學時
DECLARE id1_count BIGINT default 0; #t_stage_info 第一階段count
DECLARE id2_count BIGINT default 0; #t_stage_info 第二階段count
DECLARE id3_count BIGINT default 0; #t_stage_info 第三階段count
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 
            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 p1.last_date <= CURRENT_DATE();
#t_person_plan_record p2 這個表數據大概500多萬條
    #當游標到達尾部時，mysql自動設置done=1
  declare continue handler for SQLSTATE '02000' SET done = 1;    

    OPEN cur1;  
        REPEAT
             /* 移動游標並賦值 */  
    fetch cur1 into personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime; 
            if NOT done  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);
                        #獲取主鍵標識
                        SELECT count(1) into id1_count from t_stage_info where person_plan_id=personPlaneId AND stage=1;
                        SELECT count(1) into id2_count from t_stage_info where person_plan_id=personPlaneId AND stage=2;
                        SELECT count(1) into id3_count from t_stage_info where person_plan_id=personPlaneId AND stage=3;

                    IF sumStudyTime <= 12 THEN

                        IF id1_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,startTime,endTime,sumStudyTime,targetStudyTime,1); 
                        ELSE
                            UPDATE t_stage_info SET end_time=endTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=1;
                        END IF;

                    #當學到第二階段時
                    ELSEIF sumStudyTime>12 AND sumStudyTime<=14 THEN

                        #更新第一階段數據 
                        IF id1_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(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_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(personPlaneId,firstEndTime,endTime,sumStudyTime,targetStudyTime,2); 
                        ELSE
                            UPDATE t_stage_info SET end_time=endTime,sum_study_time=sumStudyTime WHERE person_plan_id=personPlaneId AND stage=2;
                        END IF;

                    #當學到第三階段時
                    ELSEIF sumStudyTime>14 THEN

                        #如果thirdEndTime為空則取最後學習的時間
                        SET thirdEndTime = CASE WHEN thirdEndTime IS NULL THEN endTime ELSE thirdEndTime END;

                        #更新第一階段數據
                        IF id1_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(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_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(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_count=0 THEN
                            INSERT INTO t_stage_info(person_plan_id,start_time,end_time,sum_study_time,target_study_time,stage) 
                                VALUES(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;

    end if;
        UNTIL done END REPEAT;  
    close cur1; 

END
現在執行了2小時才有了10幾萬數據，
我在t_person_plan_record 表裡對‘person_plan_id ’建了索引，
在表t_stage_info 對person_plan_id ’建立了索引，其他我就不知道怎麼優化了，
請問大神，還能怎麼優化比較好呢？

最佳回答：

CURSOR 有游標就不可能快的起來~！ 換換思路 看能不能通過批量update 解決你現在用游標在解決的事情