优化MySQL ifelif和else逻辑

debugcn 发表于 Dev

山塔诺

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”，会更快吗？

尼尔·N

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。