為什么盡量避免使用 IN 和 NOT IN？

前言

在SQL查詢中，IN 和 NOT IN 是常用的關(guān)鍵字，用于篩選符合條件的數(shù)據(jù)。

然而，盡管它們使用方便，但在某些情況下，使用它們可能會(huì)導(dǎo)致效率低下或查詢結(jié)果不準(zhǔn)確。

本文將從效率和潛在問(wèn)題兩個(gè)角度，深入探討為什么應(yīng)盡量避免使用 IN 和 NOT IN，并提供替代方案。

一、效率問(wèn)題

1. NOT IN 的性能瓶頸

在SQL查詢中，NOT IN 往往會(huì)導(dǎo)致性能問(wèn)題，尤其是在處理大數(shù)據(jù)集時(shí)。

以下是一個(gè)典型的例子：

假設(shè)有兩個(gè)表 t1 和 t2，每個(gè)表都有150萬(wàn)條數(shù)據(jù)，且 phone 字段上都有索引。

我們想查詢 t1 中 phone 不在 t2 中的記錄：

SELECT * FROM t1
WHERE phone NOT IN (SELECT phone FROM t2);

這條查詢可能會(huì)運(yùn)行得非常慢，甚至需要十幾分鐘才能完成。

原因是 NOT IN 子查詢無(wú)法有效利用索引，尤其是在子查詢返回的結(jié)果集較大時(shí)，數(shù)據(jù)庫(kù)引擎需要逐條比較，導(dǎo)致性能急劇下降。

2. 使用 NOT EXISTS 優(yōu)化

相比之下，NOT EXISTS 是一種更高效的替代方案。

同樣的查詢可以改寫為：

SELECT * FROM t1
WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.phone = t2.phone);

這種寫法通常只需要20秒左右即可完成查詢，性能提升顯著。原因是 NOT EXISTS 可以在子查詢中利用索引，避免了全表掃描。

二、潛在問(wèn)題

1. IN 子查詢中的列名錯(cuò)誤

IN 子查詢的一個(gè)潛在問(wèn)題是，如果列名寫錯(cuò)，查詢可能不會(huì)報(bào)錯(cuò)，但會(huì)返回錯(cuò)誤的結(jié)果。

例如：

SELECT id1 FROM test1
WHERE id1 IN (SELECT id1 FROM test2);

在這個(gè)例子中，test2 表中并沒(méi)有 id1 列，但查詢不會(huì)報(bào)錯(cuò)，而是返回 test1 中的所有記錄。

這是因?yàn)?IN 子查詢中的列名錯(cuò)誤被忽略，導(dǎo)致查詢邏輯失效。

2. NOT IN 與 NULL 值的問(wèn)題

NOT IN 在處理包含 NULL 值的數(shù)據(jù)時(shí)，可能會(huì)導(dǎo)致查詢結(jié)果不準(zhǔn)確。

例如：

SELECT id1 FROM test1
WHERE id1 NOT IN (SELECT id2 FROM test2);

如果 test2 中的 id2 列包含 NULL 值，查詢將不會(huì)返回任何結(jié)果。

原因是 NULL 值與任何值的比較都會(huì)返回 UNKNOWN，導(dǎo)致 NOT IN 條件無(wú)法滿足。

三、替代方案

為了避免上述問(wèn)題，可以采用以下替代方案：

1. 使用 EXISTS 或 NOT EXISTS

EXISTS 和 NOT EXISTS 是更安全和高效的替代方案。

例如：

-- 查詢存在于 test2 中的 test1 記錄
SELECT * FROM test1
WHERE EXISTS (SELECT 1 FROM test2 WHERE test2.id2 = test1.id1);

-- 查詢不存在于 test2 中的 test1 記錄
SELECT * FROM test1
WHERE NOT EXISTS (SELECT 1 FROM test2 WHERE test2.id2 = test1.id1);

2. 使用 JOIN

JOIN 也可以用來(lái)替代 IN 和 NOT IN。

例如：

-- 查詢存在于 test2 中的 test1 記錄
SELECT test1.id1
FROM test1
INNER JOIN test2 ON test1.id1 = test2.id2;

-- 查詢不存在于 test2 中的 test1 記錄
SELECT test1.id1
FROM test1
LEFT JOIN test2 ON test1.id1 = test2.id2
WHERE test2.id2 IS NULL;

四、何時(shí)可以使用 IN 和 NOT IN？

盡管 IN 和 NOT IN 存在一些問(wèn)題，但在某些情況下仍然可以使用。

例如，當(dāng)查詢的條件是一個(gè)確定且有限的集合時(shí)，IN 是合適的：

SELECT * FROM test1
WHERE id1 IN (1, 2, 3);

這種寫法簡(jiǎn)潔明了，且不會(huì)導(dǎo)致性能問(wèn)題。

五、總結(jié)

• 效率問(wèn)題：NOT IN 在處理大數(shù)據(jù)集時(shí)性能較差，建議使用 NOT EXISTS 或 JOIN 替代。
• 潛在問(wèn)題：IN 和 NOT IN 容易因列名錯(cuò)誤或 NULL 值導(dǎo)致查詢結(jié)果不準(zhǔn)確。
• 替代方案：優(yōu)先使用 EXISTS、NOT EXISTS 或 JOIN，以提高查詢效率和準(zhǔn)確性。
• 適用場(chǎng)景：在確定且有限的集合中，IN 和 NOT IN 仍然可以使用。

通過(guò)合理選擇查詢方式，可以有效提升SQL查詢的性能和可靠性。

– 歡迎點(diǎn)贊、關(guān)注、轉(zhuǎn)發(fā)、收藏【我碼玄黃】，各大平臺(tái)同名。