使用 IF() 或 CASE 是否有性能差异 [英] Is there a performance difference in using IF() or CASE

问题描述

在包含多个子选择的大型 UPDATE 语句中，我们使用了大量嵌套的 IF() 语句.我想将这些更复杂的 IF() 语句重构为 CASE 语句，主要是为了提高可读性并减少编码错误的变化.

In a large UPDATE statement with several subselects, we have been using a lot of nested IF() statements. I would like to refactor the more complex of these IF() statements into CASE statements primarily to improve readability and lessen the change of coding errors.

像这样的声明部分:

SET cr.Price=IF(cmr.dirty AND rtg.connectRateToMasterRate=0, cr.Price,
                (cmr.price + IF(rtg.RateDeviationType='FIXED_AMOUNT', 
                                rtg.masterRateRateDeviation, cmr.Price * 
                                rtg.masterRateRateDeviation / 100)  
               )) * IF(masterSettings.masterCurrencyConvertActive='TRUE', 
                       cuMaster.AValue / cu.AValue, 1),

会变成这样:

SET
 cr.Price = CASE WHEN cmr.dirty AND rtg.connectRateToMasterRate
                 THEN
                   (cmr.Price +  
                    IF(rtg.RateDeviationType='FIXED_AMOUNT',  
                        rtg.masterRateRateDeviation,  
                        cmr.Price * rtg.masterRateRateDeviation / 100  
                      )  
                   ) * IF(masterSettings.masterCurrencyConvertActive='TRUE',  
                          cuMaster.AValue / cu.AValue,  
                          1  
                         )  
                 ELSE cr.Price  
            END

我的问题是这样的重构是否会影响查询的性能.请记住，此更新查询将更新数千条记录，因此即使是很小的增加也会产生重大影响.

My question is if such a refactoring would have impact on the performance of the query. Bear in mind that this update query will update thousands of records, so even a small increase could have significant impact.

顺便说一下，我们使用的是 MySQL 5.6.19.

We are using MySQL 5.6.19 by the way.

推荐答案

IF 作为函数 可能由于函数调用而导致很小的开销.区分差异的唯一方法是在受控环境中对执行进行计时.我仍然不希望有大的变化，而且数千条记录现在似乎没有那么大了.

The use of the IF as a function may result in a small overhead due to the function call. The only way to tell the difference is to clock the executions in a controlled environment. Still I won't expect big changes, and thousands of records doesn't seem to be that huge nowadays.

这篇关于使用 IF() 或 CASE 是否有性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！