引言

最近在重構(gòu)一個(gè)老項(xiàng)目時(shí)，被一段SQL查詢卡得死死的，排查下來才發(fā)現(xiàn)是 IN 和 NOT IN 惹的禍。雖然這兩個(gè)關(guān)鍵字看著挺順手，寫起來也方便，但我今天就來說說為啥真正的開發(fā)老司機(jī)都悄悄拋棄了它們。

性能差到驚人：一個(gè)血的教訓(xùn)

前段時(shí)間我在處理用戶數(shù)據(jù)同步時(shí)遇到個(gè)情況：users表和user_profiles表都是90萬條數(shù)據(jù)左右，大概300MB，完全算不上大表。結(jié)果我隨手寫了句：

SELECT * FROM users WHERE mobile NOT IN (SELECT mobile FROM user_profiles)

這條查詢直接把我整懵了...跑了幾分鐘還沒出結(jié)果！檢查后發(fā)現(xiàn)mobile字段在兩個(gè)表都建了索引，類型也完全一致。執(zhí)行explain后才知道，原來NOT IN這種操作方式?jīng)]走索引，難怪這么慢。改成了EXISTS后，整個(gè)世界都清靜了：

SELECT * FROM users 
WHERE NOT EXISTS (SELECT mobile FROM user_profiles WHERE users.mobile = user_profiles.mobile)

僅僅十幾秒就跑完了，簡直天壤地別！

最坑的是：它不會(huì)明確報(bào)錯(cuò)，讓你吃暗虧

除了慢，IN/NOT IN還有個(gè)更惡心的問題：容易寫錯(cuò)但不會(huì)報(bào)錯(cuò)，查出來的結(jié)果還看著像那么回事。我用兩個(gè)簡單的表來演示：

CREATE TABLE employees (emp_id INT);
CREATE TABLE departments (dept_id INT);

INSERT INTO employees (emp_id) VALUES (1),(2),(3);
INSERT INTO departments (dept_id) VALUES (1),(2);

如果我想找出在departments表中存在的員工ID，正常應(yīng)該這么寫：

SELECT emp_id FROM employees
WHERE emp_id IN (SELECT dept_id FROM departments)

結(jié)果很正常：返回1和2。但假如我手抖寫成了：

SELECT emp_id FROM employees 
WHERE emp_id IN (SELECT emp_id FROM departments)

注意看，我把子查詢里的dept_id寫成了emp_id。魔幻的是，這句SQL居然不報(bào)錯(cuò)！直接返回了1、2、3所有數(shù)據(jù)！更扯的是，如果單獨(dú)執(zhí)行SELECT emp_id FROM departments肯定會(huì)報(bào)錯(cuò)"列名'emp_id'無效"，但放在IN子查詢里就不報(bào)錯(cuò)了，這也太陰險(xiǎn)了吧。

老司機(jī)都用啥替代方案？

既然IN和NOT IN這么不靠譜，咱們就用更穩(wěn)的方案：

方案一：EXISTS/NOT EXISTS

-- 代替IN
SELECT * FROM employees
WHERE EXISTS (SELECT 1 FROM departments WHERE departments.dept_id = employees.emp_id);

-- 代替NOT IN
SELECT * FROM employees
WHERE NOT EXISTS (SELECT 1 FROM departments WHERE departments.dept_id = employees.emp_id);

方案二：JOIN大法

-- 代替IN
SELECT employees.emp_id FROM employees
INNER JOIN departments ON departments.dept_id = employees.emp_id;

-- 代替NOT IN
SELECT employees.emp_id FROM employees
LEFT JOIN departments ON departments.dept_id = employees.emp_id
WHERE departments.dept_id IS NULL;

這兩種方法不僅性能好，而且對NULL的處理也更加合理，不會(huì)搞出莫名其妙的結(jié)果。

說真的，少用IN和NOT IN吧

看完這些例子，我是再也不敢隨便用IN和NOT IN了。雖然它們看起來簡單直觀，寫起來也省事，但隱藏的坑實(shí)在太多。特別是在處理大量數(shù)據(jù)的時(shí)候，性能差異簡直就是天壤之別。如果你們團(tuán)隊(duì)的代碼庫里還有這種寫法，趕緊改掉吧！別等到生產(chǎn)環(huán)境出了問題才來查原因。