时间戳

首页 > 解决方案 > 优化mysql if elseif和else逻辑

mysql - 优化mysql if elseif和else逻辑

问题描述

这部分 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”之类的逻辑会更快吗？

标签： mysqlstored-procedures

解决方案

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

推荐阅读