MySQL的 where 1=1 會(huì)影響性能嗎？

作者：猿java 2024-05-31 13:04:09

在日常業(yè)務(wù)開發(fā)中，where 1=1和<where> 標(biāo)簽到底會(huì)不會(huì)影響性能，本文將從官方文檔來進(jìn)行說明。

在日常業(yè)務(wù)開發(fā)中，經(jīng)常會(huì)使用where 1=1來簡(jiǎn)化動(dòng)態(tài) SQL語句的拼接，有人說where 1=1會(huì)影響性能，也有人說不會(huì)，到底會(huì)不會(huì)影響性能？本文將從 MySQL的官方資料來進(jìn)行分析。

一、動(dòng)態(tài)拼接SQL

在 Mybatis中，動(dòng)態(tài)拼接 SQL最常用的兩種方式：使用 where 1=1 和使用<where>標(biāo)簽。

1. 使用where 1=1

使用過 iBATIS的小伙伴應(yīng)該都知道：在 iBATIS中沒有<where>標(biāo)簽，動(dòng)態(tài) SQL的處理相對(duì)較為原始和復(fù)雜，因此使用where 1=1這種寫法的用戶很大一部分是還在使用 iBATIS 或者是從 iBATIS過度到 Mybatis。

如下示例，通過where 1=1來動(dòng)態(tài)拼接有效的 if語句：

<select id="" parameterType = "">
 SELECT * FROM user 
 WHERE 1=1
    <if test="name != null and name != ''">
        AND name = #{name}
    </if>
    <if test="age != null ">
        AND age = #{age }
    </if>
</select>

2. 使用<where>標(biāo)簽

Mybatis提供了<where>標(biāo)簽，<where>標(biāo)簽只有在至少一個(gè) if條件有值的情況下才去生成 where子句，若 AND或 OR前沒有有效語句，where元素會(huì)將它們?nèi)コ簿褪钦f，如果 Mybatis通過<where>標(biāo)簽動(dòng)態(tài)生成的語句為where AND name = '111'，最終會(huì)被優(yōu)化為where name = '111'。

<where>標(biāo)簽使用示例如下：

<select id="" parameterType = "">
 SELECT * FROM user 
    <where>
        <if test="name != null and name != ''">
           AND name = #{name}
        </if>
        <if test="age != null">
           AND age = #{age}
        </if>
    </where>
</select>

<where>標(biāo)簽是在 MyBatis中引入的，所以，很多一開始就使用 MyBatis的用戶對(duì)這個(gè)標(biāo)簽使用的比較多。

二、性能影響

where 1=1到底會(huì)不會(huì)影響性能？我們可以先看一個(gè)具體的例子：

說明：示例基于 MySQL 8.0.30

可以使用如下指令查看 MySQL版本：

SELECT VERSION();

場(chǎng)景：基于一張擁有 100多萬條數(shù)據(jù)的user表，根據(jù)name進(jìn)行查詢。

查看表結(jié)構(gòu)和表的總數(shù)據(jù)，如下圖：

下面，通過執(zhí)行兩條 SQL查詢語句（一條帶有 1=1）：

select * from user where name = 'name-96d1b3ce-1a24-4d47-b686-6f9c6940f5f6';
select * from user where 1=1 and name = 'name-f692472e-40de-4053-9498-54b9800e9fb1';

對(duì)比兩條 SQL執(zhí)行的結(jié)果，可以發(fā)現(xiàn)它們消耗的時(shí)間幾乎相同，因此，看起來where 1=1對(duì)整體的性能似乎并不影響。

為了排除一次查詢不具有代表性，我們分別對(duì)兩條 SQL語句查詢 100遍，然后計(jì)算平均值：

SET PROFILING = 1;
DO SLEEP(0.001); -- 確保每次查詢之間有足夠時(shí)間間隔

SET @count = 0;
WHILE @count < 100 DO
select * from user where name = 'name-96d1b3ce-1a24-4d47-b686-6f9c6940f5f6';
-- or
select * from user where 1=1 and name = 'name-f692472e-40de-4053-9498-54b9800e9fb1';
SET @count = @count + 1;
END WHILE;    

SHOW PROFILES;

兩條 SQL分別執(zhí)行 100次后，最終也發(fā)現(xiàn)它們的平均值幾乎相同，因此，上述示例似乎證明了 where 1=1 對(duì)整體的性能并沒有不影響。

為什么沒有影響？是不是 MySQL對(duì) 1=1進(jìn)行了優(yōu)化？

為了證明猜想，我們借助show warnings命令來查看信息，在 MySQL中，show warnings命令用于顯示最近執(zhí)行的 SQL語句產(chǎn)生的警告、錯(cuò)誤或通知信息。它可以幫助我們了解語句執(zhí)行過程中的問題。如下示例：

explain select * from user where 1=1 and name = 'name-f692472e-40de-4053-9498-54b9800e9fb1';
show warnings;

將上述示例的 warnings信息摘出來如下：

/* select#1 */ select `yuanjava`.`user`.`id` AS `id`,
      `yuanjava`.`user`.`name` AS `name`,
      `yuanjava`.`user`.`age` AS `age`,
      `yuanjava`.`user`.`sex` AS `sex`,
      `yuanjava`.`user`.`created_at` AS `created_at` 
from `yuanjava`.`user` 
where (`yuanjava`.`user`.`name` = 'name-f692472e-40de-4053-9498-54b9800e9fb1')

從 warnings信息可以看出：1=1已經(jīng)被查詢優(yōu)化器優(yōu)化掉，因此，對(duì)整體的性能影響并不大。

那么，有沒有 MySQL的官方資料可以佐證 where 1=1確實(shí)被優(yōu)化了？

答案：有！MySQL有一種 Constant-Folding Optimization（常量折疊優(yōu)化）的功能。

三、Constant-Folding Optimization

MySQL的優(yōu)化器具有一項(xiàng)稱為 Constant-Folding Optimization（常量折疊優(yōu)化）的功能，可以從查詢中消除重言式表達(dá)式。Constant-Folding Optimization 是一種編譯器的優(yōu)化技術(shù)，用于優(yōu)化編譯時(shí)計(jì)算表達(dá)式的常量部分，從而減少運(yùn)行時(shí)的計(jì)算量，換句話說：Constant-Folding Optimization 是發(fā)生在編譯期，而不是引擎執(zhí)行期間。

對(duì)于上述表達(dá)的”重言式表達(dá)式”又是什么呢？

重言式

重言式（Tautology ）又稱為永真式，它的漢語拼音為：[Chóng yán shì]，是邏輯學(xué)的名詞。命題公式中有一類重言式，如果一個(gè)公式，對(duì)于它的任一解釋下其真值都為真，就稱為重言式（永真式）。

其實(shí)，重言式在計(jì)算機(jī)領(lǐng)域也具有重要應(yīng)用，比如”重言式表達(dá)式”（Tautological expression），它指的是那些總是為真的表達(dá)式或邏輯條件。

在 SQL查詢中，重言式表達(dá)式是指無論在什么情況下，結(jié)果永遠(yuǎn)為真，它們通常會(huì)被優(yōu)化器識(shí)別并優(yōu)化掉，以提高查詢效率。例如，如果 where中包含 1=1 或 A=A 這種重言式表達(dá)式，它們就會(huì)被優(yōu)化器移除，因?yàn)閷?duì)查詢結(jié)果沒有實(shí)際影響。如下兩個(gè)示例：

SELECT * from user where 1=1 and name = 'xxx';
-- 被優(yōu)化成
SELECT * from user where name = 'xxx'；

SELECT id, name, salary * (1 + 0.05 * 2) AS real_salary FROM employees;
-- 優(yōu)化成(1 + 0.05 * 2 被優(yōu)化成 1.1)
SELECT id, name, salary * 1.1 AS real_salary FROM employees;

另外，通過下面 MySQL架構(gòu)示意圖可以看出：優(yōu)化器是屬于 MySQL的 Server層，因此，Constant-Folding Optimization功能支持受 MySQL Server的版本影響。

查閱了 MySQL的官方資料，Constant-Folding Optimization 從 MySQL5.7版本開始引入，至于 MySQL5.7以前的版本是否具備這個(gè)功能，還有待考證。

四、如何選擇？

where 1=1 和 <where> 標(biāo)簽兩種方案，該如何選擇？

如果 MySQL Server版本大于等于 5.7，兩個(gè)隨便選，或者根據(jù)團(tuán)隊(duì)的要求來選；
如果 MySQL Server版本小于 5.7，假如使用的是 MyBatis，建議使用<where> 標(biāo)簽，如果使用的還是比較老的 iBATIS，只能使用where 1=1；

信息補(bǔ)充：2009年5月，iBATIS從 2.0版本開始更名為 MyBatis，標(biāo)簽最早出現(xiàn)在MyBatis 3.2.0版本中。

五、總結(jié)

where 1=1和<where> 標(biāo)簽到底會(huì)不會(huì)影響性能，這個(gè)問題在網(wǎng)上已經(jīng)出現(xiàn)了很多次，今天還是想從官方文檔來進(jìn)行說明。本文通過 MySQL的官方資料，加上百萬數(shù)據(jù)的表進(jìn)行真實(shí)測(cè)試，得出下面的結(jié)論：

如果 MySQL Server版本大于等于 5.7，where 1=1 對(duì)于性能幾乎沒有影響，因此，兩個(gè)方式隨便選，或者根據(jù)團(tuán)隊(duì)的要求來選；
如果 MySQL Server版本小于 5.7，假如使用的是 MyBatis，建議使用<where> 標(biāo)簽，如果使用的還是比較老的 iBATIS，只能使用where 1=1；

最后，遇到問題，建議首先查找官方的一手資料，這樣才能幫助自己在一條正確的技術(shù)道路上成長(zhǎng)！

責(zé)任編輯：趙寧寧來源：猿java