mysql - 优化mysql if elseif和else逻辑

Question

MySQL 存储过程的这一部分可以工作，但速度非常慢。有什么办法可以优化吗？

IF (p_regid >0 AND p_submittedqueId>0 AND p_saveresponse=True)  then  
        if not exists(select * from  responseok where regid=p_regid AND QID =p_submittedqueId and TestId=p_TestId) AND (p_COption!='' or p_responsetext!='') then 

            insert into  responseok (regid,TestId,QID,Response,ResponseText,timestamp)  
            select p_regid,p_testId,p_submittedqueId,p_COption,p_responsetext,now(3);

         elseif (p_responsetext ='' AND p_COption='') then 
            insert into responsehistory (regid,TestId,QID,timestamp)  
            select p_regid,p_testId,p_submittedqueId,now(3);
            delete from  responseok where regid =p_regid and QID =p_submittedQueId AND TestId=p_TestId;
         else 
            insert into responsehistory (regid,TestId,QID,timestamp)  
            select p_regid,p_testId,p_submittedqueId,now(3);
            delete from  responseok where regid =p_regid and QID =p_submittedQueId AND TestId=p_TestId;
            insert into  responseok (regid,TestId,QID,Response,ResponseText,timestamp)  
            select p_regid,p_testId,p_submittedqueId,p_COption,p_responsetext,now(3);
         end if;
end if;

如果我将代码更改为以下内容，它会返回相同的结果吗？

IF (p_regid >0 AND p_submittedqueId>0 AND p_saveresponse=True AND p_COption!='' or p_responsetext!='')  then  
        if not exists(select * from  responseok where regid=p_regid AND QID =p_submittedqueId and TestId=p_TestId) then

            insert into  responseok (regid,TestId,QID,Response,ResponseText,timestamp)  
            select p_regid,p_testId,p_submittedqueId,p_COption,p_responsetext,now(3);

         else 
            insert into responsehistory (regid,TestId,QID,timestamp)  
            select p_regid,p_testId,p_submittedqueId,now(3);
            delete from  responseok where regid =p_regid and QID =p_submittedQueId AND TestId=p_TestId;
        end if;
         else 
            insert into responsehistory (regid,TestId,QID,timestamp)  
            select p_regid,p_testId,p_submittedqueId,now(3);
            delete from  responseok where regid =p_regid and QID =p_submittedQueId AND TestId=p_TestId;
            insert into  responseok (regid,TestId,QID,Response,ResponseText,timestamp)  
            select p_regid,p_testId,p_submittedqueId,p_COption,p_responsetext,now(3);
         end if;

它会提高性能吗？

如果我将逻辑“如果不存在”更改为“select 1 into some_var”之类的东西，会更快吗？

Answer 1

“如果不存在”行可能是罪魁祸首，它必须检查整个表以确保每条记录与您的条件不匹配。确保该 where 子句中的每一列(regid、QID 和 TestId)都有索引。这应该允许数据库引擎更快地过滤掉不匹配的记录。或者您可以考虑尝试重构，以便仅在单个索引字段上进行搜索。