引言

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

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

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

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

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

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

僅僅十幾秒就跑完了，簡(jiǎn)直天壤地別！

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

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

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)該這么寫(xiě)：

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

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

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

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

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

說(shuō)真的，少用IN和NOT IN吧

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