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