句的性能陷阱與優(yōu)化實(shí)戰(zhàn))
摘要一條含近 900 個(gè)值的 NOT IN 查詢跑 3-4 秒。先破除誤解MySQL 對(duì)常量 IN 列表會(huì)排序 二分查找慢的不是「比較 900 次」而是每次重新解析那十幾 KB 的 SQL 文本。對(duì)比 LEFT JOIN、臨時(shí)表 NOT EXISTS、CTE 預(yù)計(jì)算三種方案最終選臨時(shí)表CTE 需 8.0本例是 5.7。落地踩了臨時(shí)表權(quán)限、以及連接不固定導(dǎo)致的建完查不到兩個(gè)坑。優(yōu)化到 109ms其中 85% 還花在插入 ID 上。引言你有沒(méi)有經(jīng)歷過(guò)那種等待SQL執(zhí)行仿佛過(guò)了一個(gè)世紀(jì)的感覺(jué)今天我要和大家分享一個(gè)真實(shí)的案例——如何解決MySQL中NOT IN語(yǔ)句的性能陷阱將一條執(zhí)行了3-4秒的慢SQL優(yōu)化到100多毫秒性能提升了整整30倍這不僅是一次技術(shù)優(yōu)化更是一場(chǎng)與時(shí)間和數(shù)據(jù)的較量。故事開(kāi)始于一個(gè)普通的下午系統(tǒng)監(jiān)控突然發(fā)出警報(bào)一條包含大量NOT IN子句的查詢語(yǔ)句執(zhí)行時(shí)間超過(guò)了3秒作為開(kāi)發(fā)者的我們立刻進(jìn)入戰(zhàn)斗狀態(tài)準(zhǔn)備迎接這場(chǎng)性能挑戰(zhàn)。NOT IN的真面目一個(gè)常見(jiàn)的性能陷阱讓我們先看看這條罪魁禍?zhǔn)椎腟QL語(yǔ)句SELECTCOUNT(*)FROMuser_interaction_logWHEREuser_id1234567890ANDtarget_user_idNOTIN(0987654321,1122334455,5544332211,6677889900,0099887766,2233445566,-- ... 省略大量ID值 ...3344556677,4455667788,5566778899)看到這個(gè)包含近 900 個(gè)值的NOT IN子句是不是已經(jīng)感到一絲涼意先破除一個(gè)流傳很廣的誤解網(wǎng)上很多文章會(huì)告訴你「NOT IN慢是因?yàn)樗妹恳恍腥ズ?900 個(gè)值逐一比較復(fù)雜度 O(n×m)?！贡疚牡谝话嬉彩沁@么寫(xiě)的。這個(gè)說(shuō)法是錯(cuò)的MySQL 官方文檔寫(xiě)得很清楚If no type conversion is needed for the values in theIN()list, they are all non-JSON constants of the same type …The values in the list are sorted and the search for expr is done using a binary search, which makes theIN()operation very quick.—— MySQL 官方文檔Comparison Functions and Operators也就是說(shuō)常量列表的IN/NOT INMySQL 會(huì)先排序再二分查找——900 個(gè)值只需約log?(900) ≈ 10次比較不是 900 次。單看比較開(kāi)銷(xiāo)它快得很。那到底慢在哪慢在 SQL 文本本身。900 個(gè) ID每個(gè)約 12 位數(shù)字加引號(hào)逗號(hào)整條 SQL 光字面量就十幾 KB。這帶來(lái)兩筆每次執(zhí)行都躲不掉的開(kāi)銷(xiāo)解析parse把十幾 KB 的文本切成 900 個(gè)字面量節(jié)點(diǎn)優(yōu)化optimize對(duì)這 900 個(gè)常量做類(lèi)型檢查、排序構(gòu)建那個(gè)用于二分查找的數(shù)組。注意這兩步發(fā)生在「還沒(méi)開(kāi)始讀任何一行數(shù)據(jù)」之前且 MySQL 5.x 沒(méi)有執(zhí)行計(jì)劃緩存——每來(lái)一次請(qǐng)求這十幾 KB 就要重新啃一遍。??還有一個(gè)更隱蔽的坑那個(gè)二分查找優(yōu)化是有前提的——「不需要類(lèi)型轉(zhuǎn)換、且都是同類(lèi)型常量」。如果target_user_id列的字符集 / 排序規(guī)則跟常量對(duì)不上觸發(fā)了隱式轉(zhuǎn)換優(yōu)化直接失效、退回逐個(gè)比較那才真是 O(n×m)。同款字符集坑見(jiàn) 《一條 SQL 掃描 11 億行CPU 直接拉滿字符集不一致引發(fā)的線上血案》。執(zhí)行計(jì)劃顯示雖然走了索引user_id有索引、避免了全表掃描但架不住每次都要重新解析這條巨型 SQL。這也正是后面「臨時(shí)表方案」為什么有效的原因——它把「每次傳 900 個(gè)字面量」換成了「?jìng)饕粡堄兄麈I索引的表」。把這兩種歸因、以及后面實(shí)測(cè)出來(lái)的耗時(shí)構(gòu)成放在一起看方向的差別就很清楚了解決方案大比拼面對(duì)這個(gè)問(wèn)題我們嘗試了多種解決方案每種都有其獨(dú)特的優(yōu)缺點(diǎn)方案一LEFT JOIN IS NULLSELECTCOUNT(*)FROMuser_interaction_log tLEFTJOIN(SELECT0987654321AStarget_user_idUNIONALLSELECT1122334455UNIONALL-- ... 其他值)rONt.target_user_idr.target_user_idWHEREt.user_id1234567890ANDr.target_user_idISNULL;優(yōu)點(diǎn)不受NOT IN遇 NULL 返回空集的影響見(jiàn)后面「語(yǔ)義差異」那節(jié)排除列表本身就來(lái)自另一張表時(shí)可以直接 JOIN 那張表徹底不用往 SQL 里塞字面量小規(guī)模排除列表寫(xiě)法直觀、性能夠用缺點(diǎn)排除列表照樣是字面量SQL 文本一點(diǎn)沒(méi)變短——900 個(gè)UNION ALL比原來(lái)的NOT IN列表還長(zhǎng)解析開(kāi)銷(xiāo)分毫不少。這正是它在本例里落選的原因可能會(huì)產(chǎn)生不必要的中間結(jié)果集適用場(chǎng)景排除列表本身就是一張表直接 JOIN根本不用塞字面量若仍是手寫(xiě)字面量只適合幾十個(gè)值以內(nèi)方案二臨時(shí)表 NOT EXISTS最終選擇-- 創(chuàng)建臨時(shí)表存儲(chǔ)排除列表CREATETEMPORARYTABLEtemp_excluded_users(target_user_idVARCHAR(20)PRIMARYKEY);-- 插入排除的900個(gè)IDINSERTINTOtemp_excluded_users(target_user_id)VALUES(0987654321),(1122334455),-- ...-- 查詢優(yōu)化版本SELECTCOUNT(*)FROMuser_interaction_log tWHEREt.user_id1234567890ANDNOTEXISTS(SELECT1FROMtemp_excluded_users eWHEREe.target_user_idt.target_user_id);優(yōu)點(diǎn)SQL 文本從十幾 KB 降到幾百字節(jié)——每次執(zhí)行不用再重新解析 900 個(gè)字面量這才是提速的根因臨時(shí)表的主鍵索引把查找變成索引探查不受NOT IN遇 NULL 返回空集的影響 —— ?? 但這正意味著行為變了主鍵列壓根存不下 NULL那條排除就靜默失效了見(jiàn)文末「改寫(xiě)前必須知道的語(yǔ)義差異NULL」?? 別把它記成「NOT EXISTS 比 NOT IN 快」——起作用的是換成了一張帶索引的表不是換了個(gè)關(guān)鍵字。理由見(jiàn)文末經(jīng)驗(yàn)總結(jié)第 2 條。缺點(diǎn)需要CREATE TEMPORARY TABLES權(quán)限生產(chǎn)環(huán)境常常沒(méi)有見(jiàn)后面「實(shí)施過(guò)程中的挑戰(zhàn)」建表、插數(shù)、查詢必須走同一條連接臨時(shí)表是連接私有的分步調(diào)用會(huì)建完查不到同上這兩個(gè)坑我們都踩了光插入 900 個(gè) ID 就要 84-104ms占優(yōu)化后總耗時(shí)的 85%見(jiàn)后面的耗時(shí)分解適用場(chǎng)景排除列表較大幾百到上千個(gè)值且每次都不一樣——相對(duì)穩(wěn)定的話直接做成持久表更劃算方案三預(yù)計(jì)算總數(shù)并減去WITHtotal_countAS(SELECTCOUNT(*)AScntFROMuser_interaction_logWHEREuser_id1234567890),excluded_countAS(SELECTCOUNT(*)AScntFROMuser_interaction_log tINNERJOIN(SELECT0987654321AStarget_user_idUNIONALLSELECT1122334455UNIONALL-- ...)excludedONt.target_user_idexcluded.target_user_idWHEREt.user_id1234567890)SELECT(total_count.cnt-COALESCE(excluded_count.cnt,0))ASresultFROMtotal_countLEFTJOINexcluded_countON11;優(yōu)點(diǎn)把排除換成兩次 COUNT 相減兩條都能走user_id索引對(duì)于小數(shù)據(jù)集性能較好缺點(diǎn)如果排除列表非常大INNER JOIN操作可能會(huì)導(dǎo)致性能下降需要確保排除列表無(wú)重復(fù)值適用場(chǎng)景總記錄數(shù)較小幾千條以內(nèi)且環(huán)境是 MySQL 8.0——5.7 上這個(gè)寫(xiě)法直接跑不起來(lái)??版本要求這個(gè)寫(xiě)法用了WITH ... AS公共表表達(dá)式CTEMySQL 8.0 才支持。本文案例的環(huán)境從報(bào)錯(cuò)堆棧com.mysql.jdbc.exceptions.jdbc4Connector/J 5.x看是 5.7所以方案三在當(dāng)時(shí)根本跑不起來(lái)——這也是它沒(méi)被選中的現(xiàn)實(shí)原因之一。5.7 上要實(shí)現(xiàn)同樣思路得拆成兩條 SQL 在應(yīng)用層相減。方法優(yōu)點(diǎn)缺點(diǎn)使用場(chǎng)景LEFT JOIN避開(kāi) NULL 語(yǔ)義坑排除列表本身是張表時(shí)可直接 JOIN字面量照舊SQL 文本不變短解析開(kāi)銷(xiāo)一分不少可能產(chǎn)生不必要的中間結(jié)果集排除列表本就是一張表直接 JOIN仍是字面量則只適合幾十個(gè)值臨時(shí)表NOT EXISTSSQL 文本降到幾百字節(jié)解析開(kāi)銷(xiāo)沒(méi)了這才是根因主鍵索引加速查找不受 NULL 影響要CREATE TEMPORARY TABLES權(quán)限生產(chǎn)常常沒(méi)有三步必須同一條連接插入本身就占 85% 耗時(shí)排除列表較大幾百到上千且每次都不一樣預(yù)計(jì)算并減去換成兩次 COUNT 相減都能走索引CTE 寫(xiě)法需 MySQL 8.0排除列表過(guò)大時(shí) INNER JOIN 性能下降需確保無(wú)重復(fù)值總記錄數(shù)較小幾千條以內(nèi)且在 8.0 上實(shí)施過(guò)程中的挑戰(zhàn)與解決方案理想很豐滿現(xiàn)實(shí)卻很骨感。在實(shí)際部署過(guò)程中我們遇到了不少挑戰(zhàn)權(quán)限問(wèn)題臨時(shí)表的準(zhǔn)入證當(dāng)我們滿懷信心地將臨時(shí)表方案部署到線上時(shí)卻遭遇了意想不到的障礙org.springframework.jdbc.BadSqlGrammarException:###Errorupdatingdatabase.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Accessdeniedforuser app_user%todatabaseproduction_db原來(lái)生產(chǎn)環(huán)境的數(shù)據(jù)庫(kù)用戶沒(méi)有創(chuàng)建臨時(shí)表的權(quán)限這是一個(gè)常見(jiàn)的安全措施但也是我們忽略的細(xì)節(jié)。解決方案很簡(jiǎn)單GRANTSELECT,INSERT,UPDATE,DELETE,CREATETEMPORARYTABLESONproduction_db.*TOapp_user%;下次別等它報(bào)錯(cuò)上線前拿應(yīng)用的那個(gè)賬號(hào)連一下生產(chǎn)庫(kù)敲一句SHOW GRANTS FOR CURRENT_USER;看返回里有沒(méi)有CREATE TEMPORARY TABLES。十秒鐘的事——我們就是沒(méi)核這一下一路部署到線上才撞見(jiàn)它。偶發(fā)表不存在以為是并發(fā)其實(shí)是連接沒(méi)固定解決了權(quán)限問(wèn)題后又遇到一個(gè)偶發(fā)故障跑著跑著就報(bào)表已存在或表不存在。我們最初的設(shè)計(jì)是分步執(zhí)行interactionDao.dropTempTable();// 步驟1刪除臨時(shí)表interactionDao.createTempTable();// 步驟2創(chuàng)建臨時(shí)表interactionDao.insertExcludedIds(excludeUserIds);// 步驟3插入數(shù)據(jù)intcountedinteractionDao.countExcludedIds(userId,lastDate);// 步驟4查詢?? 這里我第一版歸因錯(cuò)了寫(xiě)的是「多個(gè)線程用了同一個(gè)臨時(shí)表名所以撞了」。翻一下官方文檔就知道這個(gè)解釋根本不成立ATEMPORARYtable is visible only within the current session, and is dropped automatically when the session is closed.This means that two different sessions can use the same temporary table name without conflicting with each otheror with an existing non-TEMPORARYtable of the same name.—— MySQL 官方文檔CREATE TEMPORARY TABLE Statement臨時(shí)表是 session連接私有的兩個(gè)連接用同一個(gè)表名根本不會(huì)沖突。真正的原因是連接不固定——上面四步是四次獨(dú)立的 DAO 調(diào)用不在同一個(gè)事務(wù)里時(shí)連接每次調(diào)用完就還回連接池了步驟 2 在連接 A 上建了表步驟 4 被分到連接 B →表不存在連接 A 過(guò)一會(huì)兒被下個(gè)請(qǐng)求復(fù)用上次殘留的臨時(shí)表還掛著 →表已存在。線索其實(shí)就擺在原代碼第一行步驟 1 那個(gè)先 drop 一次的防御動(dòng)作只有在這條連接上可能留著上次的表時(shí)才有意義——我寫(xiě)下了它卻沒(méi)順著它往下想一層。最終解決方案把三步放進(jìn)同一個(gè)事務(wù)并在 finally 里清理try{interactionDao.createTempTable();// 事務(wù)內(nèi)創(chuàng)建——和下面兩步共用同一條連接interactionDao.insertExcludedIds(excludeUserIds);// 插入數(shù)據(jù)intcountedinteractionDao.countExcludedIds(userId,lastDate);// 查詢}finally{interactionDao.dropTempTable();// 用完立刻清理不留給下一個(gè)復(fù)用者}起作用的關(guān)鍵不是事務(wù)這個(gè)詞是事務(wù)把整段操作釘在了同一條連接上——建表、插數(shù)、查詢看到的是同一個(gè) session建完查不到自然就沒(méi)了。finally里的 drop 負(fù)責(zé)不把殘留丟給下一個(gè)復(fù)用這條連接的請(qǐng)求。順帶省掉一個(gè)常見(jiàn)的無(wú)用功正因?yàn)榕R時(shí)表是連接私有的給表名加線程 ID / UUID 后綴在這里是多余的——它解決的是一個(gè)不存在的問(wèn)題。要保證的是同一條連接不是表名唯一。優(yōu)化成果從3秒到100毫秒的華麗轉(zhuǎn)身經(jīng)過(guò)一系列優(yōu)化和問(wèn)題解決我們?nèi)〉昧肆钊藵M意的成果性能提升從原來(lái)的3-4秒優(yōu)化到100多毫秒性能倍數(shù)提升了約30倍穩(wěn)定性偶發(fā)的建完查不到消失了——臨時(shí)表的三步操作被釘在同一條連接上從日志中可以看到優(yōu)化后的效果cost:109ms cost:105ms cost:108ms性能分解顯示各步驟耗時(shí)步驟耗時(shí)占比創(chuàng)建臨時(shí)表2-4ms~3%插入排除 ID84-104ms~85%執(zhí)行查詢5-18ms~12%刪除臨時(shí)表微秒級(jí)~0%這張表其實(shí)說(shuō)了一件很反直覺(jué)的事真正的查詢只要 5-18ms?；叵胍幌略瓉?lái)那條NOT IN要 3-4 秒——同樣是「在 900 個(gè) ID 里排除」換成臨時(shí)表后查詢部分只花了十幾毫秒。如果慢真的來(lái)自「比較 900 次」換個(gè)寫(xiě)法不可能快出兩個(gè)數(shù)量級(jí)因?yàn)楸容^次數(shù)并沒(méi)有變少。這從側(cè)面印證了前面的結(jié)論貴的是每次重新解析那十幾 KB 的 SQL 文本不是比較本身。順便暴露了下一個(gè)優(yōu)化點(diǎn)現(xiàn)在 85% 的時(shí)間花在「把 900 個(gè) ID 插進(jìn)臨時(shí)表」上。如果這個(gè)排除列表相對(duì)穩(wěn)定完全可以做成持久表 增量更新把這 84-104ms 也省掉——那樣整體就能進(jìn) 20ms 以內(nèi)。優(yōu)化到這一步就停了是因?yàn)橐呀?jīng)夠用不是因?yàn)榈筋^了。經(jīng)驗(yàn)總結(jié)與最佳實(shí)踐這次優(yōu)化給我們帶來(lái)了寶貴的經(jīng)驗(yàn)真正的陷阱是「SQL 文本體積」不是「比較次數(shù)」常量IN列表走的是排序 二分查找比較開(kāi)銷(xiāo)極小。貴在每次執(zhí)行都要重新解析、優(yōu)化那十幾 KB 的字面量。判斷法如果你的 SQL 文本超過(guò)幾 KB先懷疑解析開(kāi)銷(xiāo)再懷疑執(zhí)行計(jì)劃。別把「NOT EXISTS 比 NOT IN 快」當(dāng)成通用結(jié)論真正起作用的是把常量列表?yè)Q成了一張帶索引的表。如果你只是把NOT IN (900 個(gè)字面量)原樣改寫(xiě)成NOT EXISTS (SELECT ... FROM (900 個(gè) UNION ALL))SQL 文本一樣大一點(diǎn)都不會(huì)變快。臨時(shí)表真正提供的是一張帶索引的表主鍵索引把查找變成索引探查SQL 文本從十幾 KB 降到幾百字節(jié)。代價(jià)是多兩次往返——本例里插 900 個(gè) ID就吃掉了 85% 的耗時(shí)。臨時(shí)表必須和用它的查詢?cè)谕粋€(gè)事務(wù)里但原因不是回滾是釘住同一條連接臨時(shí)表是連接私有的分步調(diào)用可能拿到不同連接建完就查不到。權(quán)限這類(lèi)環(huán)境差異上線前自己核一遍別等生產(chǎn)報(bào)錯(cuò)本例的臨時(shí)表權(quán)限坑測(cè)試環(huán)境沒(méi)有、一上生產(chǎn)就撞上。用應(yīng)用賬號(hào)連生產(chǎn)庫(kù)敲一句SHOW GRANTS FOR CURRENT_USER;就能提前看見(jiàn)比部署完再回頭補(bǔ)便宜得多。?? 改寫(xiě)前必須知道的語(yǔ)義差異NULL這是NOT IN最有名的坑而且恰恰會(huì)在本文推薦的這次改寫(xiě)中暴露出來(lái)——兩種寫(xiě)法遇到 NULL 時(shí)行為完全不同-- 排除列表里只要有一個(gè) NULL整條查詢返回 0 行SELECT*FROMtWHEREidNOTIN(1,2,NULL);-- 永遠(yuǎn)查不出任何數(shù)據(jù)-- NOT EXISTS 不受影響該返回什么返回什么SELECT*FROMtWHERENOTEXISTS(SELECT1FROMexcluded eWHEREe.idt.id);原因是三值邏輯id NOT IN (1,2,NULL)等價(jià)于id1 AND id2 AND idNULL而idNULL恒為UNKNOWN——整個(gè)AND鏈永遠(yuǎn)不為T(mén)RUE一行都出不來(lái)。所以從NOT IN換到NOT EXISTS不只是性能改寫(xiě)是行為也變了。如果原來(lái)的排除列表可能混進(jìn)NULL比如來(lái)自另一張表的查詢結(jié)果改寫(xiě)后結(jié)果集會(huì)突然變多——那不是 bug 修復(fù)是你之前一直在返回空結(jié)果。上線前務(wù)必確認(rèn)排除列表里有沒(méi)有NULL。結(jié)論3-4 秒到 109 毫秒靠的不是什么高級(jí)技巧就一件事把每次都要重新解析的十幾 KB 字面量換成一張帶主鍵索引的表。但這次真正值錢(qián)的是兩個(gè)原來(lái)想錯(cuò)了慢的不是比較 900 次——常量列表走排序 二分查找官方文檔寫(xiě)得明明白白約 10 次比較就夠了偶發(fā)的表不存在也不是并發(fā)撞名——臨時(shí)表是連接私有的撞不了是分步調(diào)用拿到了不同連接。這兩條我都曾憑直覺(jué)寫(xiě)錯(cuò)過(guò)而且錯(cuò)的那版讀起來(lái)一樣順。所以碰上大家都這么說(shuō)的性能結(jié)論先翻一頁(yè)官方文檔再動(dòng)手——比改完一輪才發(fā)現(xiàn)方向錯(cuò)了便宜得多。最后更新2026-10-07。兩處歸因修正照舊版會(huì)做錯(cuò)方向① 原文稱 MySQL 拿每行與 900 個(gè)值逐一比較O(n×m)——實(shí)際是先排序再二分查找約 10 次比較即可真正的開(kāi)銷(xiāo)是每次重新解析十幾 KB 的 SQL 文本所以「減少 IN 的值個(gè)數(shù)」方向就錯(cuò)了。② 原文把偶發(fā)的「表已存在 / 表不存在」歸因于「多線程用了同名臨時(shí)表」——臨時(shí)表是連接私有的撞不了真因是分步調(diào)用拿到了不同連接給表名加 UUID 后綴純屬白費(fèi)。另補(bǔ)NOT IN遇 NULL 返回空集的語(yǔ)義陷阱。延伸閱讀一條 SQL 掃描 11 億行CPU 直接拉滿字符集不一致引發(fā)的線上血案 —— 隱式類(lèi)型轉(zhuǎn)換讓索引與IN的二分查找優(yōu)化雙雙失效是本文 §那到底慢在哪 提到的那個(gè)隱蔽前提每天 4000 次掃描上百萬(wàn)行XXL-JOB 的隱藏性能陷阱 —— 另一種「有索引卻走全表掃」回表代價(jià)高優(yōu)化器主動(dòng)放棄索引缺索引引發(fā)的「蝴蝶效應(yīng)」一次死鎖事故的深度復(fù)盤(pán) —— 全表掃描不止慢還能鎖住不匹配的記錄引發(fā)死鎖9 條數(shù)據(jù)查 11 秒xxl-job 列表慢的索引救援實(shí)戰(zhàn) —— 聯(lián)合覆蓋索引在千萬(wàn)級(jí)表上的在線救援 深入剖析MySQL中NOT IN語(yǔ)句的性能陷阱與優(yōu)化實(shí)戰(zhàn)
摘要一條含近 900 個(gè)值的 NOT IN 查詢跑 3-4 秒。先破除誤解MySQL 對(duì)常量 IN 列表會(huì)排序 二分查找慢的不是「比較 900 次」而是每次重新解析那十幾 KB 的 SQL 文本。對(duì)比 LEFT JOIN、臨時(shí)表 NOT EXISTS、CTE 預(yù)計(jì)算三種方案最終選臨時(shí)表CTE 需 8.0本例是 5.7。落地踩了臨時(shí)表權(quán)限、以及連接不固定導(dǎo)致的建完查不到兩個(gè)坑。優(yōu)化到 109ms其中 85% 還花在插入 ID 上。引言你有沒(méi)有經(jīng)歷過(guò)那種等待SQL執(zhí)行仿佛過(guò)了一個(gè)世紀(jì)的感覺(jué)今天我要和大家分享一個(gè)真實(shí)的案例——如何解決MySQL中NOT IN語(yǔ)句的性能陷阱將一條執(zhí)行了3-4秒的慢SQL優(yōu)化到100多毫秒性能提升了整整30倍這不僅是一次技術(shù)優(yōu)化更是一場(chǎng)與時(shí)間和數(shù)據(jù)的較量。故事開(kāi)始于一個(gè)普通的下午系統(tǒng)監(jiān)控突然發(fā)出警報(bào)一條包含大量NOT IN子句的查詢語(yǔ)句執(zhí)行時(shí)間超過(guò)了3秒作為開(kāi)發(fā)者的我們立刻進(jìn)入戰(zhàn)斗狀態(tài)準(zhǔn)備迎接這場(chǎng)性能挑戰(zhàn)。NOT IN的真面目一個(gè)常見(jiàn)的性能陷阱讓我們先看看這條罪魁禍?zhǔn)椎腟QL語(yǔ)句SELECTCOUNT(*)FROMuser_interaction_logWHEREuser_id1234567890ANDtarget_user_idNOTIN(0987654321,1122334455,5544332211,6677889900,0099887766,2233445566,-- ... 省略大量ID值 ...3344556677,4455667788,5566778899)看到這個(gè)包含近 900 個(gè)值的NOT IN子句是不是已經(jīng)感到一絲涼意先破除一個(gè)流傳很廣的誤解網(wǎng)上很多文章會(huì)告訴你「NOT IN慢是因?yàn)樗妹恳恍腥ズ?900 個(gè)值逐一比較復(fù)雜度 O(n×m)?！贡疚牡谝话嬉彩沁@么寫(xiě)的。這個(gè)說(shuō)法是錯(cuò)的MySQL 官方文檔寫(xiě)得很清楚If no type conversion is needed for the values in theIN()list, they are all non-JSON constants of the same type …The values in the list are sorted and the search for expr is done using a binary search, which makes theIN()operation very quick.—— MySQL 官方文檔Comparison Functions and Operators也就是說(shuō)常量列表的IN/NOT INMySQL 會(huì)先排序再二分查找——900 個(gè)值只需約log?(900) ≈ 10次比較不是 900 次。單看比較開(kāi)銷(xiāo)它快得很。那到底慢在哪慢在 SQL 文本本身。900 個(gè) ID每個(gè)約 12 位數(shù)字加引號(hào)逗號(hào)整條 SQL 光字面量就十幾 KB。這帶來(lái)兩筆每次執(zhí)行都躲不掉的開(kāi)銷(xiāo)解析parse把十幾 KB 的文本切成 900 個(gè)字面量節(jié)點(diǎn)優(yōu)化optimize對(duì)這 900 個(gè)常量做類(lèi)型檢查、排序構(gòu)建那個(gè)用于二分查找的數(shù)組。注意這兩步發(fā)生在「還沒(méi)開(kāi)始讀任何一行數(shù)據(jù)」之前且 MySQL 5.x 沒(méi)有執(zhí)行計(jì)劃緩存——每來(lái)一次請(qǐng)求這十幾 KB 就要重新啃一遍。??還有一個(gè)更隱蔽的坑那個(gè)二分查找優(yōu)化是有前提的——「不需要類(lèi)型轉(zhuǎn)換、且都是同類(lèi)型常量」。如果target_user_id列的字符集 / 排序規(guī)則跟常量對(duì)不上觸發(fā)了隱式轉(zhuǎn)換優(yōu)化直接失效、退回逐個(gè)比較那才真是 O(n×m)。同款字符集坑見(jiàn) 《一條 SQL 掃描 11 億行CPU 直接拉滿字符集不一致引發(fā)的線上血案》。執(zhí)行計(jì)劃顯示雖然走了索引user_id有索引、避免了全表掃描但架不住每次都要重新解析這條巨型 SQL。這也正是后面「臨時(shí)表方案」為什么有效的原因——它把「每次傳 900 個(gè)字面量」換成了「?jìng)饕粡堄兄麈I索引的表」。把這兩種歸因、以及后面實(shí)測(cè)出來(lái)的耗時(shí)構(gòu)成放在一起看方向的差別就很清楚了解決方案大比拼面對(duì)這個(gè)問(wèn)題我們嘗試了多種解決方案每種都有其獨(dú)特的優(yōu)缺點(diǎn)方案一LEFT JOIN IS NULLSELECTCOUNT(*)FROMuser_interaction_log tLEFTJOIN(SELECT0987654321AStarget_user_idUNIONALLSELECT1122334455UNIONALL-- ... 其他值)rONt.target_user_idr.target_user_idWHEREt.user_id1234567890ANDr.target_user_idISNULL;優(yōu)點(diǎn)不受NOT IN遇 NULL 返回空集的影響見(jiàn)后面「語(yǔ)義差異」那節(jié)排除列表本身就來(lái)自另一張表時(shí)可以直接 JOIN 那張表徹底不用往 SQL 里塞字面量小規(guī)模排除列表寫(xiě)法直觀、性能夠用缺點(diǎn)排除列表照樣是字面量SQL 文本一點(diǎn)沒(méi)變短——900 個(gè)UNION ALL比原來(lái)的NOT IN列表還長(zhǎng)解析開(kāi)銷(xiāo)分毫不少。這正是它在本例里落選的原因可能會(huì)產(chǎn)生不必要的中間結(jié)果集適用場(chǎng)景排除列表本身就是一張表直接 JOIN根本不用塞字面量若仍是手寫(xiě)字面量只適合幾十個(gè)值以內(nèi)方案二臨時(shí)表 NOT EXISTS最終選擇-- 創(chuàng)建臨時(shí)表存儲(chǔ)排除列表CREATETEMPORARYTABLEtemp_excluded_users(target_user_idVARCHAR(20)PRIMARYKEY);-- 插入排除的900個(gè)IDINSERTINTOtemp_excluded_users(target_user_id)VALUES(0987654321),(1122334455),-- ...-- 查詢優(yōu)化版本SELECTCOUNT(*)FROMuser_interaction_log tWHEREt.user_id1234567890ANDNOTEXISTS(SELECT1FROMtemp_excluded_users eWHEREe.target_user_idt.target_user_id);優(yōu)點(diǎn)SQL 文本從十幾 KB 降到幾百字節(jié)——每次執(zhí)行不用再重新解析 900 個(gè)字面量這才是提速的根因臨時(shí)表的主鍵索引把查找變成索引探查不受NOT IN遇 NULL 返回空集的影響 —— ?? 但這正意味著行為變了主鍵列壓根存不下 NULL那條排除就靜默失效了見(jiàn)文末「改寫(xiě)前必須知道的語(yǔ)義差異NULL」?? 別把它記成「NOT EXISTS 比 NOT IN 快」——起作用的是換成了一張帶索引的表不是換了個(gè)關(guān)鍵字。理由見(jiàn)文末經(jīng)驗(yàn)總結(jié)第 2 條。缺點(diǎn)需要CREATE TEMPORARY TABLES權(quán)限生產(chǎn)環(huán)境常常沒(méi)有見(jiàn)后面「實(shí)施過(guò)程中的挑戰(zhàn)」建表、插數(shù)、查詢必須走同一條連接臨時(shí)表是連接私有的分步調(diào)用會(huì)建完查不到同上這兩個(gè)坑我們都踩了光插入 900 個(gè) ID 就要 84-104ms占優(yōu)化后總耗時(shí)的 85%見(jiàn)后面的耗時(shí)分解適用場(chǎng)景排除列表較大幾百到上千個(gè)值且每次都不一樣——相對(duì)穩(wěn)定的話直接做成持久表更劃算方案三預(yù)計(jì)算總數(shù)并減去WITHtotal_countAS(SELECTCOUNT(*)AScntFROMuser_interaction_logWHEREuser_id1234567890),excluded_countAS(SELECTCOUNT(*)AScntFROMuser_interaction_log tINNERJOIN(SELECT0987654321AStarget_user_idUNIONALLSELECT1122334455UNIONALL-- ...)excludedONt.target_user_idexcluded.target_user_idWHEREt.user_id1234567890)SELECT(total_count.cnt-COALESCE(excluded_count.cnt,0))ASresultFROMtotal_countLEFTJOINexcluded_countON11;優(yōu)點(diǎn)把排除換成兩次 COUNT 相減兩條都能走user_id索引對(duì)于小數(shù)據(jù)集性能較好缺點(diǎn)如果排除列表非常大INNER JOIN操作可能會(huì)導(dǎo)致性能下降需要確保排除列表無(wú)重復(fù)值適用場(chǎng)景總記錄數(shù)較小幾千條以內(nèi)且環(huán)境是 MySQL 8.0——5.7 上這個(gè)寫(xiě)法直接跑不起來(lái)??版本要求這個(gè)寫(xiě)法用了WITH ... AS公共表表達(dá)式CTEMySQL 8.0 才支持。本文案例的環(huán)境從報(bào)錯(cuò)堆棧com.mysql.jdbc.exceptions.jdbc4Connector/J 5.x看是 5.7所以方案三在當(dāng)時(shí)根本跑不起來(lái)——這也是它沒(méi)被選中的現(xiàn)實(shí)原因之一。5.7 上要實(shí)現(xiàn)同樣思路得拆成兩條 SQL 在應(yīng)用層相減。方法優(yōu)點(diǎn)缺點(diǎn)使用場(chǎng)景LEFT JOIN避開(kāi) NULL 語(yǔ)義坑排除列表本身是張表時(shí)可直接 JOIN字面量照舊SQL 文本不變短解析開(kāi)銷(xiāo)一分不少可能產(chǎn)生不必要的中間結(jié)果集排除列表本就是一張表直接 JOIN仍是字面量則只適合幾十個(gè)值臨時(shí)表NOT EXISTSSQL 文本降到幾百字節(jié)解析開(kāi)銷(xiāo)沒(méi)了這才是根因主鍵索引加速查找不受 NULL 影響要CREATE TEMPORARY TABLES權(quán)限生產(chǎn)常常沒(méi)有三步必須同一條連接插入本身就占 85% 耗時(shí)排除列表較大幾百到上千且每次都不一樣預(yù)計(jì)算并減去換成兩次 COUNT 相減都能走索引CTE 寫(xiě)法需 MySQL 8.0排除列表過(guò)大時(shí) INNER JOIN 性能下降需確保無(wú)重復(fù)值總記錄數(shù)較小幾千條以內(nèi)且在 8.0 上實(shí)施過(guò)程中的挑戰(zhàn)與解決方案理想很豐滿現(xiàn)實(shí)卻很骨感。在實(shí)際部署過(guò)程中我們遇到了不少挑戰(zhàn)權(quán)限問(wèn)題臨時(shí)表的準(zhǔn)入證當(dāng)我們滿懷信心地將臨時(shí)表方案部署到線上時(shí)卻遭遇了意想不到的障礙org.springframework.jdbc.BadSqlGrammarException:###Errorupdatingdatabase.Cause:com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:Accessdeniedforuser app_user%todatabaseproduction_db原來(lái)生產(chǎn)環(huán)境的數(shù)據(jù)庫(kù)用戶沒(méi)有創(chuàng)建臨時(shí)表的權(quán)限這是一個(gè)常見(jiàn)的安全措施但也是我們忽略的細(xì)節(jié)。解決方案很簡(jiǎn)單GRANTSELECT,INSERT,UPDATE,DELETE,CREATETEMPORARYTABLESONproduction_db.*TOapp_user%;下次別等它報(bào)錯(cuò)上線前拿應(yīng)用的那個(gè)賬號(hào)連一下生產(chǎn)庫(kù)敲一句SHOW GRANTS FOR CURRENT_USER;看返回里有沒(méi)有CREATE TEMPORARY TABLES。十秒鐘的事——我們就是沒(méi)核這一下一路部署到線上才撞見(jiàn)它。偶發(fā)表不存在以為是并發(fā)其實(shí)是連接沒(méi)固定解決了權(quán)限問(wèn)題后又遇到一個(gè)偶發(fā)故障跑著跑著就報(bào)表已存在或表不存在。我們最初的設(shè)計(jì)是分步執(zhí)行interactionDao.dropTempTable();// 步驟1刪除臨時(shí)表interactionDao.createTempTable();// 步驟2創(chuàng)建臨時(shí)表interactionDao.insertExcludedIds(excludeUserIds);// 步驟3插入數(shù)據(jù)intcountedinteractionDao.countExcludedIds(userId,lastDate);// 步驟4查詢?? 這里我第一版歸因錯(cuò)了寫(xiě)的是「多個(gè)線程用了同一個(gè)臨時(shí)表名所以撞了」。翻一下官方文檔就知道這個(gè)解釋根本不成立ATEMPORARYtable is visible only within the current session, and is dropped automatically when the session is closed.This means that two different sessions can use the same temporary table name without conflicting with each otheror with an existing non-TEMPORARYtable of the same name.—— MySQL 官方文檔CREATE TEMPORARY TABLE Statement臨時(shí)表是 session連接私有的兩個(gè)連接用同一個(gè)表名根本不會(huì)沖突。真正的原因是連接不固定——上面四步是四次獨(dú)立的 DAO 調(diào)用不在同一個(gè)事務(wù)里時(shí)連接每次調(diào)用完就還回連接池了步驟 2 在連接 A 上建了表步驟 4 被分到連接 B →表不存在連接 A 過(guò)一會(huì)兒被下個(gè)請(qǐng)求復(fù)用上次殘留的臨時(shí)表還掛著 →表已存在。線索其實(shí)就擺在原代碼第一行步驟 1 那個(gè)先 drop 一次的防御動(dòng)作只有在這條連接上可能留著上次的表時(shí)才有意義——我寫(xiě)下了它卻沒(méi)順著它往下想一層。最終解決方案把三步放進(jìn)同一個(gè)事務(wù)并在 finally 里清理try{interactionDao.createTempTable();// 事務(wù)內(nèi)創(chuàng)建——和下面兩步共用同一條連接interactionDao.insertExcludedIds(excludeUserIds);// 插入數(shù)據(jù)intcountedinteractionDao.countExcludedIds(userId,lastDate);// 查詢}finally{interactionDao.dropTempTable();// 用完立刻清理不留給下一個(gè)復(fù)用者}起作用的關(guān)鍵不是事務(wù)這個(gè)詞是事務(wù)把整段操作釘在了同一條連接上——建表、插數(shù)、查詢看到的是同一個(gè) session建完查不到自然就沒(méi)了。finally里的 drop 負(fù)責(zé)不把殘留丟給下一個(gè)復(fù)用這條連接的請(qǐng)求。順帶省掉一個(gè)常見(jiàn)的無(wú)用功正因?yàn)榕R時(shí)表是連接私有的給表名加線程 ID / UUID 后綴在這里是多余的——它解決的是一個(gè)不存在的問(wèn)題。要保證的是同一條連接不是表名唯一。優(yōu)化成果從3秒到100毫秒的華麗轉(zhuǎn)身經(jīng)過(guò)一系列優(yōu)化和問(wèn)題解決我們?nèi)〉昧肆钊藵M意的成果性能提升從原來(lái)的3-4秒優(yōu)化到100多毫秒性能倍數(shù)提升了約30倍穩(wěn)定性偶發(fā)的建完查不到消失了——臨時(shí)表的三步操作被釘在同一條連接上從日志中可以看到優(yōu)化后的效果cost:109ms cost:105ms cost:108ms性能分解顯示各步驟耗時(shí)步驟耗時(shí)占比創(chuàng)建臨時(shí)表2-4ms~3%插入排除 ID84-104ms~85%執(zhí)行查詢5-18ms~12%刪除臨時(shí)表微秒級(jí)~0%這張表其實(shí)說(shuō)了一件很反直覺(jué)的事真正的查詢只要 5-18ms?；叵胍幌略瓉?lái)那條NOT IN要 3-4 秒——同樣是「在 900 個(gè) ID 里排除」換成臨時(shí)表后查詢部分只花了十幾毫秒。如果慢真的來(lái)自「比較 900 次」換個(gè)寫(xiě)法不可能快出兩個(gè)數(shù)量級(jí)因?yàn)楸容^次數(shù)并沒(méi)有變少。這從側(cè)面印證了前面的結(jié)論貴的是每次重新解析那十幾 KB 的 SQL 文本不是比較本身。順便暴露了下一個(gè)優(yōu)化點(diǎn)現(xiàn)在 85% 的時(shí)間花在「把 900 個(gè) ID 插進(jìn)臨時(shí)表」上。如果這個(gè)排除列表相對(duì)穩(wěn)定完全可以做成持久表 增量更新把這 84-104ms 也省掉——那樣整體就能進(jìn) 20ms 以內(nèi)。優(yōu)化到這一步就停了是因?yàn)橐呀?jīng)夠用不是因?yàn)榈筋^了。經(jīng)驗(yàn)總結(jié)與最佳實(shí)踐這次優(yōu)化給我們帶來(lái)了寶貴的經(jīng)驗(yàn)真正的陷阱是「SQL 文本體積」不是「比較次數(shù)」常量IN列表走的是排序 二分查找比較開(kāi)銷(xiāo)極小。貴在每次執(zhí)行都要重新解析、優(yōu)化那十幾 KB 的字面量。判斷法如果你的 SQL 文本超過(guò)幾 KB先懷疑解析開(kāi)銷(xiāo)再懷疑執(zhí)行計(jì)劃。別把「NOT EXISTS 比 NOT IN 快」當(dāng)成通用結(jié)論真正起作用的是把常量列表?yè)Q成了一張帶索引的表。如果你只是把NOT IN (900 個(gè)字面量)原樣改寫(xiě)成NOT EXISTS (SELECT ... FROM (900 個(gè) UNION ALL))SQL 文本一樣大一點(diǎn)都不會(huì)變快。臨時(shí)表真正提供的是一張帶索引的表主鍵索引把查找變成索引探查SQL 文本從十幾 KB 降到幾百字節(jié)。代價(jià)是多兩次往返——本例里插 900 個(gè) ID就吃掉了 85% 的耗時(shí)。臨時(shí)表必須和用它的查詢?cè)谕粋€(gè)事務(wù)里但原因不是回滾是釘住同一條連接臨時(shí)表是連接私有的分步調(diào)用可能拿到不同連接建完就查不到。權(quán)限這類(lèi)環(huán)境差異上線前自己核一遍別等生產(chǎn)報(bào)錯(cuò)本例的臨時(shí)表權(quán)限坑測(cè)試環(huán)境沒(méi)有、一上生產(chǎn)就撞上。用應(yīng)用賬號(hào)連生產(chǎn)庫(kù)敲一句SHOW GRANTS FOR CURRENT_USER;就能提前看見(jiàn)比部署完再回頭補(bǔ)便宜得多。?? 改寫(xiě)前必須知道的語(yǔ)義差異NULL這是NOT IN最有名的坑而且恰恰會(huì)在本文推薦的這次改寫(xiě)中暴露出來(lái)——兩種寫(xiě)法遇到 NULL 時(shí)行為完全不同-- 排除列表里只要有一個(gè) NULL整條查詢返回 0 行SELECT*FROMtWHEREidNOTIN(1,2,NULL);-- 永遠(yuǎn)查不出任何數(shù)據(jù)-- NOT EXISTS 不受影響該返回什么返回什么SELECT*FROMtWHERENOTEXISTS(SELECT1FROMexcluded eWHEREe.idt.id);原因是三值邏輯id NOT IN (1,2,NULL)等價(jià)于id1 AND id2 AND idNULL而idNULL恒為UNKNOWN——整個(gè)AND鏈永遠(yuǎn)不為T(mén)RUE一行都出不來(lái)。所以從NOT IN換到NOT EXISTS不只是性能改寫(xiě)是行為也變了。如果原來(lái)的排除列表可能混進(jìn)NULL比如來(lái)自另一張表的查詢結(jié)果改寫(xiě)后結(jié)果集會(huì)突然變多——那不是 bug 修復(fù)是你之前一直在返回空結(jié)果。上線前務(wù)必確認(rèn)排除列表里有沒(méi)有NULL。結(jié)論3-4 秒到 109 毫秒靠的不是什么高級(jí)技巧就一件事把每次都要重新解析的十幾 KB 字面量換成一張帶主鍵索引的表。但這次真正值錢(qián)的是兩個(gè)原來(lái)想錯(cuò)了慢的不是比較 900 次——常量列表走排序 二分查找官方文檔寫(xiě)得明明白白約 10 次比較就夠了偶發(fā)的表不存在也不是并發(fā)撞名——臨時(shí)表是連接私有的撞不了是分步調(diào)用拿到了不同連接。這兩條我都曾憑直覺(jué)寫(xiě)錯(cuò)過(guò)而且錯(cuò)的那版讀起來(lái)一樣順。所以碰上大家都這么說(shuō)的性能結(jié)論先翻一頁(yè)官方文檔再動(dòng)手——比改完一輪才發(fā)現(xiàn)方向錯(cuò)了便宜得多。最后更新2026-10-07。兩處歸因修正照舊版會(huì)做錯(cuò)方向① 原文稱 MySQL 拿每行與 900 個(gè)值逐一比較O(n×m)——實(shí)際是先排序再二分查找約 10 次比較即可真正的開(kāi)銷(xiāo)是每次重新解析十幾 KB 的 SQL 文本所以「減少 IN 的值個(gè)數(shù)」方向就錯(cuò)了。② 原文把偶發(fā)的「表已存在 / 表不存在」歸因于「多線程用了同名臨時(shí)表」——臨時(shí)表是連接私有的撞不了真因是分步調(diào)用拿到了不同連接給表名加 UUID 后綴純屬白費(fèi)。另補(bǔ)NOT IN遇 NULL 返回空集的語(yǔ)義陷阱。延伸閱讀一條 SQL 掃描 11 億行CPU 直接拉滿字符集不一致引發(fā)的線上血案 —— 隱式類(lèi)型轉(zhuǎn)換讓索引與IN的二分查找優(yōu)化雙雙失效是本文 §那到底慢在哪 提到的那個(gè)隱蔽前提每天 4000 次掃描上百萬(wàn)行XXL-JOB 的隱藏性能陷阱 —— 另一種「有索引卻走全表掃」回表代價(jià)高優(yōu)化器主動(dòng)放棄索引缺索引引發(fā)的「蝴蝶效應(yīng)」一次死鎖事故的深度復(fù)盤(pán) —— 全表掃描不止慢還能鎖住不匹配的記錄引發(fā)死鎖9 條數(shù)據(jù)查 11 秒xxl-job 列表慢的索引救援實(shí)戰(zhàn) —— 聯(lián)合覆蓋索引在千萬(wàn)級(jí)表上的在線救援