馕觯簭母拍?、分類到?yōu)化改寫實(shí)踐)
MySQL子查詢是個很神奇的東西你剛接觸SQL時覺得它難懂寫了幾年后又發(fā)現(xiàn)它是排查慢SQL時最常被拎出來“扣字眼”的地方。前陣子幫團(tuán)隊(duì)處理一個報表慢查詢SQL里嵌套了三層子查詢單次執(zhí)行要二三十秒拆開分析后才發(fā)現(xiàn)不是子查詢本身的問題而是很多人把它當(dāng)成了萬能鑰匙隨手往任何位置塞結(jié)果既傷性能又難維護(hù)。我干脆把MySQL子查詢從頭到尾整理了一遍從概念、分類、執(zhí)行邏輯、優(yōu)化器行為到改寫技巧都揉進(jìn)這篇文章里適合剛學(xué)SQL的人當(dāng)系統(tǒng)性筆記也適合寫了兩三年SQL但沒認(rèn)真梳理過子查詢的開發(fā)者查漏補(bǔ)缺。本文用的全是可以在自己電腦上跑的MySQL 5.7/8.0例子不依賴任何在線環(huán)境的特定寫法。為了保證盡量全我把它拆成了六個大塊先說清楚子查詢的底層邏輯然后按“出現(xiàn)位置”和“返回結(jié)果”兩個維度重新分類再專門講相關(guān)子查詢和EXISTS最后是把子查詢弄慢的坑和改寫思路。直接開始。1. 先把子查詢這件事說清楚嵌套的SELECT到底返回了什么很多人背過定義子查詢就是“嵌套在其他查詢里的SELECT”。但這句話太稀松平常了它沒有回答那個最核心的問題一條SELECT塞進(jìn)另一條SELECT之后到底算什么1.1 子查詢的本質(zhì)一個可以“插”在任意位置的臨時結(jié)果集一個子查詢本質(zhì)上就是一條完整、獨(dú)立、用括號包起來的SELECT語句。它的執(zhí)行結(jié)果可能是單個值、一列數(shù)據(jù)、一行數(shù)據(jù)也可能是一整張表。外層查詢拿到這個結(jié)果以后再根據(jù)自己的邏輯繼續(xù)篩選、計算、關(guān)聯(lián)。你可以把它理解成一個函數(shù)調(diào)用先執(zhí)行內(nèi)層拿到一個返回值再把這個返回值塞進(jìn)外層表達(dá)式里。比如最常見的“查工資高于平均工資的員工”腦子里的第一反應(yīng)通常是先算出平均工資再把員工表和這個平均值比較。寫成SQL就是SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);這里的(SELECT AVG(salary) FROM employee)就是子查詢它返回了一個數(shù)值外層把它當(dāng)成普通條件來比較。正因?yàn)樽硬樵兊谋举|(zhì)是“臨時結(jié)果”所以子查詢幾乎可以出現(xiàn)在SQL語句的任意角落SELECT子句里可以放、FROM子句里可以放、WHERE和HAVING里可以放、ORDER BY后面也能放。這也引出了很多人的困惑點(diǎn)——到底什么位置該放什么類型的子查詢答案其實(shí)很簡單看你需要這個子查詢返回什么要一個值就放表達(dá)式位置要一張表就放FROM里要一列值就放到IN、ANY、ALL這類集合運(yùn)算的右邊。1.2 建一套測試數(shù)據(jù)后面所有例子都跑在它上面空口說概念很難有體感我先把后面反復(fù)用到的一整套測試表建出來。這個場景很簡單部門表、員工表、訂單表屬于業(yè)務(wù)系統(tǒng)里最常見的那類數(shù)據(jù)模型。CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR(50) NOT NULL, location VARCHAR(20) ); CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, department_id INT, salary DECIMAL(10, 2) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, emp_id INT, order_amount DECIMAL(10, 2), order_date DATE );插入少量但存在差異的數(shù)據(jù)方便看出每種查詢的含義INSERT INTO department VALUES (1, 技術(shù)部, A區(qū)), (2, 市場部, B區(qū)), (3, 財務(wù)部, A區(qū)); INSERT INTO employee VALUES (1, 員工A, 1, 10000.00), (2, 員工B, 1, 8000.00), (3, 員工C, 2, 9000.00), (4, 員工D, 2, 7000.00), (5, 員工E, 3, 6000.00); INSERT INTO orders VALUES (1, 1, 500.00, 2024-01-01), (2, 1, 300.00, 2024-01-05), (3, 2, 200.00, 2024-02-01), (4, 3, 400.00, 2024-02-10);先停在幾張表上不要急著復(fù)制代碼。后面每講一種寫法你都回到這個數(shù)據(jù)模型里跑一遍比死記語法有效得多。2. 按出現(xiàn)位置拆解SELECT、FROM、WHERE、HAVING里都能藏子查詢子查詢的“位置”決定了它的角色。我見過太多人只會寫WHERE后跟IN的子查詢?nèi)缓笠慌龅綇?fù)雜報表就傻眼。實(shí)際上四個主要位置各有各的用法和注意點(diǎn)。2.1 FROM子句的派生表把子查詢當(dāng)臨時表用FROM子句里的子查詢在MySQL里叫派生表Derived Table你可以直接把它的查詢結(jié)果想象成一張臨時表然后再去JOIN、再篩選。最典型的場景是先做一層聚合統(tǒng)計再和其他表關(guān)聯(lián)。比如我想看“各部門平均工資超過8000元的部門名稱和平均工資”。難點(diǎn)在于部門名稱在department表平均工資必須從employee表里先GROUP BY算出來。這時候就適合先把平均工資算成派生表再去關(guān)聯(lián)部門表SELECT d.department_name, t.avg_salary FROM ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) t JOIN department d ON d.department_id t.department_id WHERE t.avg_salary 8000;有兩個坑必須記住。第一MySQL要求派生表必須寫別名哪怕你后面完全沒用到也要給個名字否則直接報語法錯誤。第二從MySQL 5.7開始默認(rèn)會把符合條件的派生表合并進(jìn)外層查詢?nèi)绻阍谂缮砝飳懥薕RDER BY但外層沒有LIMIT這個ORDER BY很可能被優(yōu)化器直接忽略排序順序不保證。正確的做法是要排序就在最外層排不要指望內(nèi)層排序能穩(wěn)定傳出來。MySQL 8.0.14以后還可以用LATERAL派生表它允許內(nèi)層子查詢引用外層查詢的列用來解決“每行動態(tài)算一個結(jié)果”的問題。但在日常業(yè)務(wù)里用得不算多先把普通派生表玩明白再說。2.2 WHERE子句的篩選比較、IN、ANY/ALL都在這里WHERE子句里的子查詢最常見但它不是只有IN一種寫法。它可以出現(xiàn)在普通比較運(yùn)算符的右邊也可以配合IN、NOT IN、ANY、SOME、ALL這些集合運(yùn)算符。最簡單的比較式篩選就是前面那個查工資高于平均工資的例子SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);如果子查詢返回的不是一個值而是一列值那就交給IN處理。比如查“部門位置在A區(qū)的所有員工”SELECT emp_id, emp_name, department_id FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A區(qū) );ANY和ALL相對冷門但偶爾遇到會很有用。ANY表示“只要比子查詢返回的任意一個值大就算滿足”等價于大于最小值A(chǔ)LL表示“要比所有值都大才算滿足”等價于大于最大值。例如“找出工資大于A區(qū)任意部門員工工資的人”可以寫成SELECT emp_id, emp_name, salary FROM employee WHERE salary ANY ( SELECT salary FROM employee WHERE department_id IN (1, 3) );這個SQL讀起來有點(diǎn)繞但它確實(shí)是合法的而且解決的是“一組里任意一個”或“一組里全部”的比較需求。用之前先想清楚你的業(yè)務(wù)是“大于任何一個”還是“大于全部”寫反了結(jié)果差異很大。2.3 SELECT和HAVING里的標(biāo)量子查詢邊查邊算SELECT子句里也能放子查詢它返回一個值作為結(jié)果集中的一列。比如我想查每個員工的姓名以及他至今下了多少筆訂單SELECT e.emp_name, (SELECT COUNT(*) FROM orders o WHERE o.emp_id e.emp_id) AS order_cnt FROM employee e;這個寫法很直觀但有個限制SELECT子查詢必須返回標(biāo)量也就是一行一列。如果返回了多行MySQL會直接報錯Subquery returns more than 1 row。所以使用前心里要有數(shù)被查的那一側(cè)要能保證唯一。HAVING子句里同樣可以放子查詢。比如“找出平均工資高于全公司平均工資的部門”SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) ( SELECT AVG(salary) FROM employee );注意HAVING里如果用到子查詢通常和GROUP BY配合它作用的對象是分組以后的結(jié)果不是原始行。很多人誤以為HAVING只是高級WHERE其實(shí)它是在分組聚合之后再做條件過濾執(zhí)行順序和WHERE完全不同。3. 按返回結(jié)果拆解標(biāo)量、單列、單行、整表四種子查詢各有脾氣位置是子查詢“放在哪”返回類型是子查詢“返回什么”。這兩個維度經(jīng)常被混在一起連很多教程都沒講清楚。返回類型決定了子查詢能用在哪些語法里也決定了報錯姿勢是什么。3.1 標(biāo)量子查詢只能返回一個值否則直接報錯標(biāo)量子查詢返回一行一列也就是一個單獨(dú)的值。它是最“聽話”的一種子查詢可以用在幾乎任何普通表達(dá)式能出現(xiàn)的地方SELECT后、WHERE比較符右邊、SET賦值等。SELECT emp_name, salary, salary - (SELECT AVG(salary) FROM employee) AS diff FROM employee;這種寫法在報表里特別常見它能在同一行同時顯示員工工資和與平均工資的差值。使用時必須注意如果子查詢返回多行整個SQL直接報錯如果返回零行結(jié)果就是NULL不會報錯但參與計算時要提防NULL傳播。判斷一個子查詢是不是標(biāo)量最粗暴的方法是看它有沒有“唯一性保證”。比如用主鍵或唯一鍵去篩選通常就是標(biāo)量用GROUP BY后再聚合也一定是標(biāo)量。反過來?xiàng)l件里不帶任何限制的SELECT department_id FROM employee想都不用想一定是多行結(jié)果。3.2 行子查詢把多個列打包成一個“元組”去比較行子查詢返回一行多列。它的用法比較特殊左括號里可以跟多個列右邊用一行子查詢結(jié)果去逐一對應(yīng)。比如我要找“和員工A部門相同、工資也完全相同的那個人”可以寫SELECT emp_id, emp_name, department_id, salary FROM employee WHERE (department_id, salary) ( SELECT department_id, salary FROM employee WHERE emp_id 1 );這里(department_id, salary)是一個行構(gòu)造器等號右邊的子查詢通過主鍵emp_id1保證了只返回一行。兩個括號里列的數(shù)量必須完全一致位置要一一對應(yīng)否則MySQL會報列數(shù)不匹配的錯誤。這種寫法在日常開發(fā)里不算高頻但它對理解SQL的“行”這個維度挺有幫助。有時候你想表達(dá)“整行和某行一致”用行子查詢比拆開寫兩個條件更貼近業(yè)務(wù)語義可讀性反而更好。3.3 列子查詢與IN/ANY/ALL搭配集合關(guān)系判斷列子查詢返回一列多行本質(zhì)上是一組值的集合。這組值通常喂給IN、ANY、ALL等運(yùn)算符。IN的語義是“外層值是否屬于這個集合”SELECT emp_name FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A區(qū) );這里有個經(jīng)典性能陷阱如果你寫的子查詢本身要去重比如上面的結(jié)果可能返回多個重復(fù)department_idMySQL優(yōu)化器通常會幫你處理。但你也可以在子查詢里顯式加DISTINCT讓語義更明確。ANY和ALL與列子查詢配合時語義要搞清 ANY (...)等價于 IN ANY (...)等價于大于集合里的最小值 ALL (...)等價于大于集合里的最大值比如“找出工資大于市場部所有人工資的員工”SELECT emp_name, salary FROM employee WHERE salary ALL ( SELECT salary FROM employee WHERE department_id 2 );用ALL有個隱藏坑如果右邊的子查詢結(jié)果集為空ALL條件在大多數(shù)數(shù)據(jù)庫里會被判定為TRUE。這聽起來很反直覺但在邏輯上是自洽的——“大于空集合中的所有值”這個命題不存在反例于是為真。所以寫ALL之前一定要確認(rèn)子查詢不會查空。3.4 表子查詢本質(zhì)就是一張臨時表表子查詢返回多行多列通常只能出現(xiàn)在FROM子句里也就是前面講的派生表。它的所有行為都可以理解為“憑空生成一張臨時表”。SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) 8000;其實(shí)把上面這整段包一層括號再加個別名它就是一個表子查詢。表子查詢最大的價值在于“嵌套”你可以先聚合出一張臨時表再基于這張臨時表繼續(xù)查。繼續(xù)在外面套一層也是合法的但可讀性會迅速惡化這也是后面第六章里我要勸你用CTE的原因。四種子查詢的特點(diǎn)我常用一張表記返回類型返回形態(tài)典型出現(xiàn)位置典型報錯標(biāo)量子查詢一行一列SELECT、WHERE比較符右側(cè)Subquery returns more than 1 row行子查詢一行多列WHERE行構(gòu)造器右側(cè)Operand should contain N column(s)列子查詢多行一列IN、ANY、ALL右側(cè)一般不報錯結(jié)果可能為空集表子查詢多行多列FROM子句忘記別名導(dǎo)致語法錯誤4. 相關(guān)子查詢的執(zhí)行邏輯外層帶一行內(nèi)層跟著跑一次前面講的所有子查詢執(zhí)行時基本都是“先算內(nèi)層把它當(dāng)成一個固定的結(jié)果再算外層”。但有一類子查詢不是這個路子它讓很多新手栽過跟頭那就是相關(guān)子查詢Correlated Subquery。4.1 非相關(guān)子查詢先算內(nèi)層一次再算外層先看個對比。前面寫過的“工資高于全公司平均工資”內(nèi)層是SELECT AVG(salary) FROM employee這個查詢結(jié)果和任何一行員工都沒有關(guān)系完全獨(dú)立。這種就叫非相關(guān)子查詢它的執(zhí)行邏輯非常直白先執(zhí)行一次內(nèi)層拿到一個常數(shù)平均值再去外層一行行比較。性能開銷基本可以忽略。SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);這種“先算一次再復(fù)用”的模型最簡單也是優(yōu)化的理想形態(tài)。MySQL優(yōu)化器遇到這類子查詢通常會緩存結(jié)果不會傻傻地每行重新算一遍。4.2 相關(guān)子查詢逐行“靈魂拷問”用對場景威力巨大相關(guān)子查詢的內(nèi)層會引用外層查詢的列導(dǎo)致它沒法獨(dú)立執(zhí)行。外層查一行內(nèi)層就要帶著這一行的參數(shù)跑一次。聽起來很慢但它能解決一類非相關(guān)子查詢做不到的問題每一行需要單獨(dú)計算的對比。典型例子是“找出工資高于本部門平均工資的員工”。這個需求里每個員工的對比基準(zhǔn)不一樣技術(shù)部的員工要跟技術(shù)部平均工資比市場部的員工要跟市場部平均工資比。你沒法用固定平均值解決只能讓內(nèi)層跟著外層的department_id動態(tài)變化SELECT e1.emp_id, e1.emp_name, e1.salary, e1.department_id FROM employee e1 WHERE e1.salary ( SELECT AVG(e2.salary) FROM employee e2 WHERE e2.department_id e1.department_id );執(zhí)行時MySQL取外層第一行員工A帶著它的department_id1去算技術(shù)部平均工資比較再取員工B還是department_id1又算一次技術(shù)部平均工資到了員工Cdepartment_id變成2再算市場部平均工資。這就是為什么相關(guān)子查詢被叫作逐行“靈魂拷問”——外層有多少行內(nèi)層就可能跑多少次。在數(shù)據(jù)量小的時候這種寫法很爽邏輯直接。但表一大相關(guān)子查詢就是性能殺手。優(yōu)化思路通常有兩個一個是用JOIN加聚合先把每部門的平均工資算成臨時表再關(guān)聯(lián)另一個是用窗口函數(shù)AVG() OVER(PARTITION BY department_id)MySQL 8.0已經(jīng)支持寫起來更優(yōu)雅。例如SELECT emp_id, emp_name, salary, department_id FROM ( SELECT emp_id, emp_name, salary, department_id, AVG(salary) OVER(PARTITION BY department_id) AS dept_avg_salary FROM employee ) t WHERE salary dept_avg_salary;這種寫法只需要掃一遍表效率比相關(guān)子查詢高不少。能用窗口函數(shù)替代的場景我?guī)缀醪惶珪孟嚓P(guān)子查詢。4.3 EXISTS / NOT EXISTS存在性判斷的正確打開方式EXISTS后面接的子查詢不關(guān)心返回什么列它只問一個問題子查詢會不會返回任何一行。如果返回了哪怕一行EXISTS為真否則為假。所以很多老手會寫SELECT 1而不是SELECT *意義在于提醒自己和讀者列名無關(guān)緊要存在性才重要。比如找“下過訂單的員工”SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );EXISTS天然就是相關(guān)子查詢因?yàn)閮?nèi)層通常都要通過外層的員工ID去訂單表里找匹配記錄。它的優(yōu)勢在于“提前終止”只要找到第一條滿足條件的訂單這一行員工就算命中不用繼續(xù)把訂單掃完。配合訂單表上的emp_id索引效果非常好。這里必須說一個高頻坑NOT EXISTS和NOT IN并不等價。NOT IN在遇到子查詢結(jié)果里有NULL時會直接變成“未知”最終結(jié)果往往是空集不是你要的數(shù)據(jù)。想表達(dá)“沒有訂單記錄”這種語義優(yōu)先用NOT EXISTSSELECT e.emp_id, e.emp_name FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );很多線上事故就是有人圖省事把NOT EXISTS改成了NOT IN然后整個報表查出來一片空白。第六章里我會再把這個NULL坑單獨(dú)拎出來強(qiáng)調(diào)。5. 性能排障現(xiàn)場子查詢?yōu)槭裁磿约癕ySQL優(yōu)化器如何處理與其玄學(xué)地背“子查詢慢、JOIN快”不如先搞清楚慢在哪個環(huán)節(jié)。很多情況下子查詢被優(yōu)化器處理得足夠好你不比盲目改寫。下面這個章節(jié)我用實(shí)際排障的思路來拆。5.1 三個讓子查詢變慢的常見原因第一個原因相關(guān)子查詢的循環(huán)次數(shù)爆炸。外層有一萬行內(nèi)層就執(zhí)行一萬次即使每次內(nèi)層只要幾毫秒累計起來也是幾十秒。這種慢最容易被忽略因?yàn)橹豢磫螚lSQL文本你根本看不出執(zhí)行次數(shù)。第二個原因派生表或子查詢結(jié)果被反復(fù)物化成臨時表且沒有索引。所謂物化就是優(yōu)化器把子查詢的結(jié)果真正落成一張帶數(shù)據(jù)的臨時表放在內(nèi)存或磁盤上。如果這張臨時表很大或者外層查詢要反復(fù)訪問它但沒有合適的索引性能就直線下降。第三個原因統(tǒng)計信息不準(zhǔn)確導(dǎo)致優(yōu)化器選擇了錯誤策略。MySQL的優(yōu)化器依賴表的行數(shù)和索引基數(shù)做成本估算。如果長期沒更新統(tǒng)計信息它可能把一個本來能走索引的子查詢判斷成“物化更劃算”或“掃描更劃算”最終執(zhí)行計劃很糟糕。遇到詭異變慢的子查詢先跑一下ANALYZE TABLE更新統(tǒng)計信息再測試我至少遇到兩次是這個問題。5.2 透過EXPLAIN看優(yōu)化器SUBQUERY、DERIVED、MATERIALIZED定位地址永遠(yuǎn)是執(zhí)行計劃。MySQL里用EXPLAIN看子查詢時重點(diǎn)關(guān)注select_type列最常見的幾個值是select_type含義PRIMARY最外層查詢SUBQUERY非相關(guān)子查詢通常執(zhí)行一次DEPENDENT SUBQUERY相關(guān)子查詢外層每行執(zhí)行一次DERIVEDFROM子句里的派生表會被物化或合并MATERIALIZED子查詢被物化成臨時表UNCACHEABLE SUBQUERY每次執(zhí)行結(jié)果都可能變化無法緩存看計劃時先找DEPENDENT SUBQUERY只要出現(xiàn)它你就得懷疑循環(huán)次數(shù)。再看有沒有DERIVED或MATERIALIZED如果有注意臨時表的大小和是否使用到索引。MySQL 8.0的EXPLAIN FORMATTREE會把子查詢的執(zhí)行關(guān)系顯示得更直觀。比如對這條SQLEXPLAIN FORMATTREE SELECT emp_id, emp_name FROM employee WHERE emp_id IN (SELECT emp_id FROM orders);8.0的執(zhí)行計劃里可能會看到類似“Nested loop semijoin”的節(jié)點(diǎn)這說明優(yōu)化器把IN子查詢轉(zhuǎn)換成了半連接semi-join而不是傻傻地物化整個orders表。這種“優(yōu)化器替你做決定”的情況非常多所以別把“子查詢一定慢”當(dāng)成金科玉律。關(guān)于半連接可以簡單理解它只拿外層表的每一行去子查詢里找“能不能匹配上”匹配上就停不關(guān)心子查詢里匹配了幾條。這比把整個子查詢結(jié)果先算出來再IN判斷要高效很多尤其是在子查詢結(jié)果集很大的場景。5.3 子查詢改寫JOIN的實(shí)戰(zhàn)標(biāo)準(zhǔn)和反例那什么時候值得手動把子查詢改寫成JOIN我的判斷標(biāo)準(zhǔn)很簡單先看執(zhí)行計劃確認(rèn)優(yōu)化器沒有做半連接物化等優(yōu)化并且這個子查詢可以無損地改成JOIN。最典型的改寫場景是IN列子查詢。例如SELECT e.emp_id, e.emp_name FROM employee e WHERE e.emp_id IN ( SELECT o.emp_id FROM orders o WHERE o.order_amount 100 );可以改寫為SELECT DISTINCT e.emp_id, e.emp_name FROM employee e JOIN orders o ON o.emp_id e.emp_id WHERE o.order_amount 100;關(guān)鍵差別在于JOIN會把兩表匹配的所有組合都保留而IN只需要“有匹配”所以要多加一個DISTINCT。如果你不加同一員工下過三筆符合條件的訂單結(jié)果里就會出現(xiàn)三行這是IN與JOIN最常見的語義差異。但也要講清楚反例。如果子查詢里用了聚合函數(shù)、窗口函數(shù)或者你需要子查詢結(jié)果保持NULL語義改寫就要非常謹(jǐn)慎。比如“查工資高于本部門平均工資的員工”用相關(guān)子查詢寫很直觀改寫JOIN就得先把部門平均工資聚合出來再關(guān)聯(lián)寫法上未必更簡單SELECT e.emp_id, e.emp_name, e.salary FROM employee e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) d ON d.department_id e.department_id WHERE e.salary d.avg_salary;這種改寫把相關(guān)子查詢變成了“一表一聚合一關(guān)聯(lián)”執(zhí)行次數(shù)從N次變成掃兩次表加一次JOIN才是真正的性能優(yōu)化。改寫前最好用EXPLAIN確認(rèn)優(yōu)化器原本的策略確實(shí)不理想別為了“看起來專業(yè)”去改一段不慢的SQL。6. 我平時寫子查詢的幾條習(xí)慣有的坑踩多了才總結(jié)出來最后這部分不是語法講解是我自己寫SQL時默認(rèn)遵守的習(xí)慣。不保證每條都適合所有團(tuán)隊(duì)但照著做大概率能少挨幾次夜班。6.1 邏輯清晰優(yōu)先性能問題用分析說話同一個需求子查詢和JOIN往往都能實(shí)現(xiàn)。我的第一選擇永遠(yuǎn)是“誰更能表達(dá)業(yè)務(wù)語義就用誰”。子查詢在這方面的優(yōu)勢是它能把“先算什么再算什么”這個過程寫得很直白。比如“找出比平均工資高的人”子查詢一眼就能看出來先算平均值。如果你直接用自連接JOIN寫可能要想半天為什么有兩張employee表。不要聽風(fēng)就是雨一聽子查詢就皺眉。先用EXPLAIN看執(zhí)行計劃再決定要不要改寫。很多時候MySQL已經(jīng)幫你處理得很好手動改寫反而畫蛇添足。6.2 涉及NULL時IN/NOT IN要格外小心這是我在實(shí)際項(xiàng)目里栽得最痛的一次。當(dāng)時查“沒有訂單記錄的員工”同事寫的是NOT IN結(jié)果查出來永遠(yuǎn)是空集。原因就是子查詢結(jié)果里有NULL?；A(chǔ)規(guī)則是這樣的如果子查詢返回的列表里哪怕有一個NULLNOT IN的結(jié)果往往就是“不成立”而不是“找到?jīng)]匹配的行”。比如SELECT emp_name FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM orders);如果orders表里存在一行emp_id為NULL的記錄條件emp_id NOT IN (1, 2, NULL)的邏輯結(jié)果不是TRUE或FALSE而是NULL/UNKNOWN。WHERE只保留TRUE行于是什么都不返回。所以凡是寫NOT IN我都會先確認(rèn)子查詢列有沒有NOT NULL約束。如果沒有直接改用NOT EXISTS更安全性能也更好。6.3 嵌套太深就用CTE別疊三層以上子查詢可以無限嵌套但不代表我們應(yīng)該這么干。三層以內(nèi)勉強(qiáng)能靠縮進(jìn)讀懂超過三層基本只有寫它的人能維護(hù)一個月后再看就是天書。MySQL 8.0里我強(qiáng)烈建議用CTECommon Table Expression替代深嵌套子查詢。比如前面那個“找本部門工資高于平均工資的員工”用CTE寫是這樣WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) SELECT e.emp_name, e.salary, d.avg_salary FROM employee e JOIN dept_avg d ON e.department_id d.department_id WHERE e.salary d.avg_salary;CTE的另一個好處是同一段中間結(jié)果可以被后續(xù)多個查詢復(fù)用而普通子查詢只能在一個地方使用。遇到一段邏輯要被引用兩次的場景CTE能顯著減少SQL膨脹。MySQL 8.0以下的版本沒法用CTE那就盡量把嵌套層數(shù)控制在兩層內(nèi)或者把復(fù)雜邏輯拆成視圖。6.4 上線之前用EXPLAIN ANALYZE看一眼真實(shí)執(zhí)行時間寫SQL不能靠猜尤其是上線到生產(chǎn)環(huán)境的報表SQL。MySQL 8.0.18開始支持EXPLAIN ANALYZE它能真正執(zhí)行SQL并返回實(shí)際執(zhí)行時間、行數(shù)和循環(huán)次數(shù)比單獨(dú)看EXPLAIN的估算更可靠。EXPLAIN ANALYZE SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );結(jié)果里會顯示類似“actual time0.1...0.2 rows1 loops5”的信息。這里loops尤其重要如果loops接近外層行數(shù)說明這就是相關(guān)子查詢在逐行循環(huán)那就是下一步優(yōu)化的主要對象。拿到真實(shí)數(shù)據(jù)后你改不改、改成什么都有了依據(jù)而不是憑感覺說“子查詢太慢”。我在實(shí)際使用中最常做的一件事就是先用小數(shù)據(jù)量驗(yàn)證邏輯對不對再拿生產(chǎn)數(shù)據(jù)量做EXPLAIN ANALYZE最后把慢的候選SQL集中優(yōu)化一遍。MySQL子查詢本身沒有善惡之分它只是SQL的一部分真正決定快慢的是你對數(shù)據(jù)結(jié)構(gòu)、索引和優(yōu)化器行為的理解。把這幾個維度串起來你就能在正確的地方用子查詢在合適的時候改寫它也就不需要再記什么“子查詢一定慢”的教條了。 MySQL子查詢?nèi)馕觯簭母拍?、分類到?yōu)化改寫實(shí)踐
MySQL子查詢是個很神奇的東西你剛接觸SQL時覺得它難懂寫了幾年后又發(fā)現(xiàn)它是排查慢SQL時最常被拎出來“扣字眼”的地方。前陣子幫團(tuán)隊(duì)處理一個報表慢查詢SQL里嵌套了三層子查詢單次執(zhí)行要二三十秒拆開分析后才發(fā)現(xiàn)不是子查詢本身的問題而是很多人把它當(dāng)成了萬能鑰匙隨手往任何位置塞結(jié)果既傷性能又難維護(hù)。我干脆把MySQL子查詢從頭到尾整理了一遍從概念、分類、執(zhí)行邏輯、優(yōu)化器行為到改寫技巧都揉進(jìn)這篇文章里適合剛學(xué)SQL的人當(dāng)系統(tǒng)性筆記也適合寫了兩三年SQL但沒認(rèn)真梳理過子查詢的開發(fā)者查漏補(bǔ)缺。本文用的全是可以在自己電腦上跑的MySQL 5.7/8.0例子不依賴任何在線環(huán)境的特定寫法。為了保證盡量全我把它拆成了六個大塊先說清楚子查詢的底層邏輯然后按“出現(xiàn)位置”和“返回結(jié)果”兩個維度重新分類再專門講相關(guān)子查詢和EXISTS最后是把子查詢弄慢的坑和改寫思路。直接開始。1. 先把子查詢這件事說清楚嵌套的SELECT到底返回了什么很多人背過定義子查詢就是“嵌套在其他查詢里的SELECT”。但這句話太稀松平常了它沒有回答那個最核心的問題一條SELECT塞進(jìn)另一條SELECT之后到底算什么1.1 子查詢的本質(zhì)一個可以“插”在任意位置的臨時結(jié)果集一個子查詢本質(zhì)上就是一條完整、獨(dú)立、用括號包起來的SELECT語句。它的執(zhí)行結(jié)果可能是單個值、一列數(shù)據(jù)、一行數(shù)據(jù)也可能是一整張表。外層查詢拿到這個結(jié)果以后再根據(jù)自己的邏輯繼續(xù)篩選、計算、關(guān)聯(lián)。你可以把它理解成一個函數(shù)調(diào)用先執(zhí)行內(nèi)層拿到一個返回值再把這個返回值塞進(jìn)外層表達(dá)式里。比如最常見的“查工資高于平均工資的員工”腦子里的第一反應(yīng)通常是先算出平均工資再把員工表和這個平均值比較。寫成SQL就是SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);這里的(SELECT AVG(salary) FROM employee)就是子查詢它返回了一個數(shù)值外層把它當(dāng)成普通條件來比較。正因?yàn)樽硬樵兊谋举|(zhì)是“臨時結(jié)果”所以子查詢幾乎可以出現(xiàn)在SQL語句的任意角落SELECT子句里可以放、FROM子句里可以放、WHERE和HAVING里可以放、ORDER BY后面也能放。這也引出了很多人的困惑點(diǎn)——到底什么位置該放什么類型的子查詢答案其實(shí)很簡單看你需要這個子查詢返回什么要一個值就放表達(dá)式位置要一張表就放FROM里要一列值就放到IN、ANY、ALL這類集合運(yùn)算的右邊。1.2 建一套測試數(shù)據(jù)后面所有例子都跑在它上面空口說概念很難有體感我先把后面反復(fù)用到的一整套測試表建出來。這個場景很簡單部門表、員工表、訂單表屬于業(yè)務(wù)系統(tǒng)里最常見的那類數(shù)據(jù)模型。CREATE TABLE department ( department_id INT PRIMARY KEY, department_name VARCHAR(50) NOT NULL, location VARCHAR(20) ); CREATE TABLE employee ( emp_id INT PRIMARY KEY, emp_name VARCHAR(50) NOT NULL, department_id INT, salary DECIMAL(10, 2) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, emp_id INT, order_amount DECIMAL(10, 2), order_date DATE );插入少量但存在差異的數(shù)據(jù)方便看出每種查詢的含義INSERT INTO department VALUES (1, 技術(shù)部, A區(qū)), (2, 市場部, B區(qū)), (3, 財務(wù)部, A區(qū)); INSERT INTO employee VALUES (1, 員工A, 1, 10000.00), (2, 員工B, 1, 8000.00), (3, 員工C, 2, 9000.00), (4, 員工D, 2, 7000.00), (5, 員工E, 3, 6000.00); INSERT INTO orders VALUES (1, 1, 500.00, 2024-01-01), (2, 1, 300.00, 2024-01-05), (3, 2, 200.00, 2024-02-01), (4, 3, 400.00, 2024-02-10);先停在幾張表上不要急著復(fù)制代碼。后面每講一種寫法你都回到這個數(shù)據(jù)模型里跑一遍比死記語法有效得多。2. 按出現(xiàn)位置拆解SELECT、FROM、WHERE、HAVING里都能藏子查詢子查詢的“位置”決定了它的角色。我見過太多人只會寫WHERE后跟IN的子查詢?nèi)缓笠慌龅綇?fù)雜報表就傻眼。實(shí)際上四個主要位置各有各的用法和注意點(diǎn)。2.1 FROM子句的派生表把子查詢當(dāng)臨時表用FROM子句里的子查詢在MySQL里叫派生表Derived Table你可以直接把它的查詢結(jié)果想象成一張臨時表然后再去JOIN、再篩選。最典型的場景是先做一層聚合統(tǒng)計再和其他表關(guān)聯(lián)。比如我想看“各部門平均工資超過8000元的部門名稱和平均工資”。難點(diǎn)在于部門名稱在department表平均工資必須從employee表里先GROUP BY算出來。這時候就適合先把平均工資算成派生表再去關(guān)聯(lián)部門表SELECT d.department_name, t.avg_salary FROM ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) t JOIN department d ON d.department_id t.department_id WHERE t.avg_salary 8000;有兩個坑必須記住。第一MySQL要求派生表必須寫別名哪怕你后面完全沒用到也要給個名字否則直接報語法錯誤。第二從MySQL 5.7開始默認(rèn)會把符合條件的派生表合并進(jìn)外層查詢?nèi)绻阍谂缮砝飳懥薕RDER BY但外層沒有LIMIT這個ORDER BY很可能被優(yōu)化器直接忽略排序順序不保證。正確的做法是要排序就在最外層排不要指望內(nèi)層排序能穩(wěn)定傳出來。MySQL 8.0.14以后還可以用LATERAL派生表它允許內(nèi)層子查詢引用外層查詢的列用來解決“每行動態(tài)算一個結(jié)果”的問題。但在日常業(yè)務(wù)里用得不算多先把普通派生表玩明白再說。2.2 WHERE子句的篩選比較、IN、ANY/ALL都在這里WHERE子句里的子查詢最常見但它不是只有IN一種寫法。它可以出現(xiàn)在普通比較運(yùn)算符的右邊也可以配合IN、NOT IN、ANY、SOME、ALL這些集合運(yùn)算符。最簡單的比較式篩選就是前面那個查工資高于平均工資的例子SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);如果子查詢返回的不是一個值而是一列值那就交給IN處理。比如查“部門位置在A區(qū)的所有員工”SELECT emp_id, emp_name, department_id FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A區(qū) );ANY和ALL相對冷門但偶爾遇到會很有用。ANY表示“只要比子查詢返回的任意一個值大就算滿足”等價于大于最小值A(chǔ)LL表示“要比所有值都大才算滿足”等價于大于最大值。例如“找出工資大于A區(qū)任意部門員工工資的人”可以寫成SELECT emp_id, emp_name, salary FROM employee WHERE salary ANY ( SELECT salary FROM employee WHERE department_id IN (1, 3) );這個SQL讀起來有點(diǎn)繞但它確實(shí)是合法的而且解決的是“一組里任意一個”或“一組里全部”的比較需求。用之前先想清楚你的業(yè)務(wù)是“大于任何一個”還是“大于全部”寫反了結(jié)果差異很大。2.3 SELECT和HAVING里的標(biāo)量子查詢邊查邊算SELECT子句里也能放子查詢它返回一個值作為結(jié)果集中的一列。比如我想查每個員工的姓名以及他至今下了多少筆訂單SELECT e.emp_name, (SELECT COUNT(*) FROM orders o WHERE o.emp_id e.emp_id) AS order_cnt FROM employee e;這個寫法很直觀但有個限制SELECT子查詢必須返回標(biāo)量也就是一行一列。如果返回了多行MySQL會直接報錯Subquery returns more than 1 row。所以使用前心里要有數(shù)被查的那一側(cè)要能保證唯一。HAVING子句里同樣可以放子查詢。比如“找出平均工資高于全公司平均工資的部門”SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) ( SELECT AVG(salary) FROM employee );注意HAVING里如果用到子查詢通常和GROUP BY配合它作用的對象是分組以后的結(jié)果不是原始行。很多人誤以為HAVING只是高級WHERE其實(shí)它是在分組聚合之后再做條件過濾執(zhí)行順序和WHERE完全不同。3. 按返回結(jié)果拆解標(biāo)量、單列、單行、整表四種子查詢各有脾氣位置是子查詢“放在哪”返回類型是子查詢“返回什么”。這兩個維度經(jīng)常被混在一起連很多教程都沒講清楚。返回類型決定了子查詢能用在哪些語法里也決定了報錯姿勢是什么。3.1 標(biāo)量子查詢只能返回一個值否則直接報錯標(biāo)量子查詢返回一行一列也就是一個單獨(dú)的值。它是最“聽話”的一種子查詢可以用在幾乎任何普通表達(dá)式能出現(xiàn)的地方SELECT后、WHERE比較符右邊、SET賦值等。SELECT emp_name, salary, salary - (SELECT AVG(salary) FROM employee) AS diff FROM employee;這種寫法在報表里特別常見它能在同一行同時顯示員工工資和與平均工資的差值。使用時必須注意如果子查詢返回多行整個SQL直接報錯如果返回零行結(jié)果就是NULL不會報錯但參與計算時要提防NULL傳播。判斷一個子查詢是不是標(biāo)量最粗暴的方法是看它有沒有“唯一性保證”。比如用主鍵或唯一鍵去篩選通常就是標(biāo)量用GROUP BY后再聚合也一定是標(biāo)量。反過來?xiàng)l件里不帶任何限制的SELECT department_id FROM employee想都不用想一定是多行結(jié)果。3.2 行子查詢把多個列打包成一個“元組”去比較行子查詢返回一行多列。它的用法比較特殊左括號里可以跟多個列右邊用一行子查詢結(jié)果去逐一對應(yīng)。比如我要找“和員工A部門相同、工資也完全相同的那個人”可以寫SELECT emp_id, emp_name, department_id, salary FROM employee WHERE (department_id, salary) ( SELECT department_id, salary FROM employee WHERE emp_id 1 );這里(department_id, salary)是一個行構(gòu)造器等號右邊的子查詢通過主鍵emp_id1保證了只返回一行。兩個括號里列的數(shù)量必須完全一致位置要一一對應(yīng)否則MySQL會報列數(shù)不匹配的錯誤。這種寫法在日常開發(fā)里不算高頻但它對理解SQL的“行”這個維度挺有幫助。有時候你想表達(dá)“整行和某行一致”用行子查詢比拆開寫兩個條件更貼近業(yè)務(wù)語義可讀性反而更好。3.3 列子查詢與IN/ANY/ALL搭配集合關(guān)系判斷列子查詢返回一列多行本質(zhì)上是一組值的集合。這組值通常喂給IN、ANY、ALL等運(yùn)算符。IN的語義是“外層值是否屬于這個集合”SELECT emp_name FROM employee WHERE department_id IN ( SELECT department_id FROM department WHERE location A區(qū) );這里有個經(jīng)典性能陷阱如果你寫的子查詢本身要去重比如上面的結(jié)果可能返回多個重復(fù)department_idMySQL優(yōu)化器通常會幫你處理。但你也可以在子查詢里顯式加DISTINCT讓語義更明確。ANY和ALL與列子查詢配合時語義要搞清 ANY (...)等價于 IN ANY (...)等價于大于集合里的最小值 ALL (...)等價于大于集合里的最大值比如“找出工資大于市場部所有人工資的員工”SELECT emp_name, salary FROM employee WHERE salary ALL ( SELECT salary FROM employee WHERE department_id 2 );用ALL有個隱藏坑如果右邊的子查詢結(jié)果集為空ALL條件在大多數(shù)數(shù)據(jù)庫里會被判定為TRUE。這聽起來很反直覺但在邏輯上是自洽的——“大于空集合中的所有值”這個命題不存在反例于是為真。所以寫ALL之前一定要確認(rèn)子查詢不會查空。3.4 表子查詢本質(zhì)就是一張臨時表表子查詢返回多行多列通常只能出現(xiàn)在FROM子句里也就是前面講的派生表。它的所有行為都可以理解為“憑空生成一張臨時表”。SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id HAVING AVG(salary) 8000;其實(shí)把上面這整段包一層括號再加個別名它就是一個表子查詢。表子查詢最大的價值在于“嵌套”你可以先聚合出一張臨時表再基于這張臨時表繼續(xù)查。繼續(xù)在外面套一層也是合法的但可讀性會迅速惡化這也是后面第六章里我要勸你用CTE的原因。四種子查詢的特點(diǎn)我常用一張表記返回類型返回形態(tài)典型出現(xiàn)位置典型報錯標(biāo)量子查詢一行一列SELECT、WHERE比較符右側(cè)Subquery returns more than 1 row行子查詢一行多列WHERE行構(gòu)造器右側(cè)Operand should contain N column(s)列子查詢多行一列IN、ANY、ALL右側(cè)一般不報錯結(jié)果可能為空集表子查詢多行多列FROM子句忘記別名導(dǎo)致語法錯誤4. 相關(guān)子查詢的執(zhí)行邏輯外層帶一行內(nèi)層跟著跑一次前面講的所有子查詢執(zhí)行時基本都是“先算內(nèi)層把它當(dāng)成一個固定的結(jié)果再算外層”。但有一類子查詢不是這個路子它讓很多新手栽過跟頭那就是相關(guān)子查詢Correlated Subquery。4.1 非相關(guān)子查詢先算內(nèi)層一次再算外層先看個對比。前面寫過的“工資高于全公司平均工資”內(nèi)層是SELECT AVG(salary) FROM employee這個查詢結(jié)果和任何一行員工都沒有關(guān)系完全獨(dú)立。這種就叫非相關(guān)子查詢它的執(zhí)行邏輯非常直白先執(zhí)行一次內(nèi)層拿到一個常數(shù)平均值再去外層一行行比較。性能開銷基本可以忽略。SELECT emp_id, emp_name, salary FROM employee WHERE salary (SELECT AVG(salary) FROM employee);這種“先算一次再復(fù)用”的模型最簡單也是優(yōu)化的理想形態(tài)。MySQL優(yōu)化器遇到這類子查詢通常會緩存結(jié)果不會傻傻地每行重新算一遍。4.2 相關(guān)子查詢逐行“靈魂拷問”用對場景威力巨大相關(guān)子查詢的內(nèi)層會引用外層查詢的列導(dǎo)致它沒法獨(dú)立執(zhí)行。外層查一行內(nèi)層就要帶著這一行的參數(shù)跑一次。聽起來很慢但它能解決一類非相關(guān)子查詢做不到的問題每一行需要單獨(dú)計算的對比。典型例子是“找出工資高于本部門平均工資的員工”。這個需求里每個員工的對比基準(zhǔn)不一樣技術(shù)部的員工要跟技術(shù)部平均工資比市場部的員工要跟市場部平均工資比。你沒法用固定平均值解決只能讓內(nèi)層跟著外層的department_id動態(tài)變化SELECT e1.emp_id, e1.emp_name, e1.salary, e1.department_id FROM employee e1 WHERE e1.salary ( SELECT AVG(e2.salary) FROM employee e2 WHERE e2.department_id e1.department_id );執(zhí)行時MySQL取外層第一行員工A帶著它的department_id1去算技術(shù)部平均工資比較再取員工B還是department_id1又算一次技術(shù)部平均工資到了員工Cdepartment_id變成2再算市場部平均工資。這就是為什么相關(guān)子查詢被叫作逐行“靈魂拷問”——外層有多少行內(nèi)層就可能跑多少次。在數(shù)據(jù)量小的時候這種寫法很爽邏輯直接。但表一大相關(guān)子查詢就是性能殺手。優(yōu)化思路通常有兩個一個是用JOIN加聚合先把每部門的平均工資算成臨時表再關(guān)聯(lián)另一個是用窗口函數(shù)AVG() OVER(PARTITION BY department_id)MySQL 8.0已經(jīng)支持寫起來更優(yōu)雅。例如SELECT emp_id, emp_name, salary, department_id FROM ( SELECT emp_id, emp_name, salary, department_id, AVG(salary) OVER(PARTITION BY department_id) AS dept_avg_salary FROM employee ) t WHERE salary dept_avg_salary;這種寫法只需要掃一遍表效率比相關(guān)子查詢高不少。能用窗口函數(shù)替代的場景我?guī)缀醪惶珪孟嚓P(guān)子查詢。4.3 EXISTS / NOT EXISTS存在性判斷的正確打開方式EXISTS后面接的子查詢不關(guān)心返回什么列它只問一個問題子查詢會不會返回任何一行。如果返回了哪怕一行EXISTS為真否則為假。所以很多老手會寫SELECT 1而不是SELECT *意義在于提醒自己和讀者列名無關(guān)緊要存在性才重要。比如找“下過訂單的員工”SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );EXISTS天然就是相關(guān)子查詢因?yàn)閮?nèi)層通常都要通過外層的員工ID去訂單表里找匹配記錄。它的優(yōu)勢在于“提前終止”只要找到第一條滿足條件的訂單這一行員工就算命中不用繼續(xù)把訂單掃完。配合訂單表上的emp_id索引效果非常好。這里必須說一個高頻坑NOT EXISTS和NOT IN并不等價。NOT IN在遇到子查詢結(jié)果里有NULL時會直接變成“未知”最終結(jié)果往往是空集不是你要的數(shù)據(jù)。想表達(dá)“沒有訂單記錄”這種語義優(yōu)先用NOT EXISTSSELECT e.emp_id, e.emp_name FROM employee e WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );很多線上事故就是有人圖省事把NOT EXISTS改成了NOT IN然后整個報表查出來一片空白。第六章里我會再把這個NULL坑單獨(dú)拎出來強(qiáng)調(diào)。5. 性能排障現(xiàn)場子查詢?yōu)槭裁磿约癕ySQL優(yōu)化器如何處理與其玄學(xué)地背“子查詢慢、JOIN快”不如先搞清楚慢在哪個環(huán)節(jié)。很多情況下子查詢被優(yōu)化器處理得足夠好你不比盲目改寫。下面這個章節(jié)我用實(shí)際排障的思路來拆。5.1 三個讓子查詢變慢的常見原因第一個原因相關(guān)子查詢的循環(huán)次數(shù)爆炸。外層有一萬行內(nèi)層就執(zhí)行一萬次即使每次內(nèi)層只要幾毫秒累計起來也是幾十秒。這種慢最容易被忽略因?yàn)橹豢磫螚lSQL文本你根本看不出執(zhí)行次數(shù)。第二個原因派生表或子查詢結(jié)果被反復(fù)物化成臨時表且沒有索引。所謂物化就是優(yōu)化器把子查詢的結(jié)果真正落成一張帶數(shù)據(jù)的臨時表放在內(nèi)存或磁盤上。如果這張臨時表很大或者外層查詢要反復(fù)訪問它但沒有合適的索引性能就直線下降。第三個原因統(tǒng)計信息不準(zhǔn)確導(dǎo)致優(yōu)化器選擇了錯誤策略。MySQL的優(yōu)化器依賴表的行數(shù)和索引基數(shù)做成本估算。如果長期沒更新統(tǒng)計信息它可能把一個本來能走索引的子查詢判斷成“物化更劃算”或“掃描更劃算”最終執(zhí)行計劃很糟糕。遇到詭異變慢的子查詢先跑一下ANALYZE TABLE更新統(tǒng)計信息再測試我至少遇到兩次是這個問題。5.2 透過EXPLAIN看優(yōu)化器SUBQUERY、DERIVED、MATERIALIZED定位地址永遠(yuǎn)是執(zhí)行計劃。MySQL里用EXPLAIN看子查詢時重點(diǎn)關(guān)注select_type列最常見的幾個值是select_type含義PRIMARY最外層查詢SUBQUERY非相關(guān)子查詢通常執(zhí)行一次DEPENDENT SUBQUERY相關(guān)子查詢外層每行執(zhí)行一次DERIVEDFROM子句里的派生表會被物化或合并MATERIALIZED子查詢被物化成臨時表UNCACHEABLE SUBQUERY每次執(zhí)行結(jié)果都可能變化無法緩存看計劃時先找DEPENDENT SUBQUERY只要出現(xiàn)它你就得懷疑循環(huán)次數(shù)。再看有沒有DERIVED或MATERIALIZED如果有注意臨時表的大小和是否使用到索引。MySQL 8.0的EXPLAIN FORMATTREE會把子查詢的執(zhí)行關(guān)系顯示得更直觀。比如對這條SQLEXPLAIN FORMATTREE SELECT emp_id, emp_name FROM employee WHERE emp_id IN (SELECT emp_id FROM orders);8.0的執(zhí)行計劃里可能會看到類似“Nested loop semijoin”的節(jié)點(diǎn)這說明優(yōu)化器把IN子查詢轉(zhuǎn)換成了半連接semi-join而不是傻傻地物化整個orders表。這種“優(yōu)化器替你做決定”的情況非常多所以別把“子查詢一定慢”當(dāng)成金科玉律。關(guān)于半連接可以簡單理解它只拿外層表的每一行去子查詢里找“能不能匹配上”匹配上就停不關(guān)心子查詢里匹配了幾條。這比把整個子查詢結(jié)果先算出來再IN判斷要高效很多尤其是在子查詢結(jié)果集很大的場景。5.3 子查詢改寫JOIN的實(shí)戰(zhàn)標(biāo)準(zhǔn)和反例那什么時候值得手動把子查詢改寫成JOIN我的判斷標(biāo)準(zhǔn)很簡單先看執(zhí)行計劃確認(rèn)優(yōu)化器沒有做半連接物化等優(yōu)化并且這個子查詢可以無損地改成JOIN。最典型的改寫場景是IN列子查詢。例如SELECT e.emp_id, e.emp_name FROM employee e WHERE e.emp_id IN ( SELECT o.emp_id FROM orders o WHERE o.order_amount 100 );可以改寫為SELECT DISTINCT e.emp_id, e.emp_name FROM employee e JOIN orders o ON o.emp_id e.emp_id WHERE o.order_amount 100;關(guān)鍵差別在于JOIN會把兩表匹配的所有組合都保留而IN只需要“有匹配”所以要多加一個DISTINCT。如果你不加同一員工下過三筆符合條件的訂單結(jié)果里就會出現(xiàn)三行這是IN與JOIN最常見的語義差異。但也要講清楚反例。如果子查詢里用了聚合函數(shù)、窗口函數(shù)或者你需要子查詢結(jié)果保持NULL語義改寫就要非常謹(jǐn)慎。比如“查工資高于本部門平均工資的員工”用相關(guān)子查詢寫很直觀改寫JOIN就得先把部門平均工資聚合出來再關(guān)聯(lián)寫法上未必更簡單SELECT e.emp_id, e.emp_name, e.salary FROM employee e JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) d ON d.department_id e.department_id WHERE e.salary d.avg_salary;這種改寫把相關(guān)子查詢變成了“一表一聚合一關(guān)聯(lián)”執(zhí)行次數(shù)從N次變成掃兩次表加一次JOIN才是真正的性能優(yōu)化。改寫前最好用EXPLAIN確認(rèn)優(yōu)化器原本的策略確實(shí)不理想別為了“看起來專業(yè)”去改一段不慢的SQL。6. 我平時寫子查詢的幾條習(xí)慣有的坑踩多了才總結(jié)出來最后這部分不是語法講解是我自己寫SQL時默認(rèn)遵守的習(xí)慣。不保證每條都適合所有團(tuán)隊(duì)但照著做大概率能少挨幾次夜班。6.1 邏輯清晰優(yōu)先性能問題用分析說話同一個需求子查詢和JOIN往往都能實(shí)現(xiàn)。我的第一選擇永遠(yuǎn)是“誰更能表達(dá)業(yè)務(wù)語義就用誰”。子查詢在這方面的優(yōu)勢是它能把“先算什么再算什么”這個過程寫得很直白。比如“找出比平均工資高的人”子查詢一眼就能看出來先算平均值。如果你直接用自連接JOIN寫可能要想半天為什么有兩張employee表。不要聽風(fēng)就是雨一聽子查詢就皺眉。先用EXPLAIN看執(zhí)行計劃再決定要不要改寫。很多時候MySQL已經(jīng)幫你處理得很好手動改寫反而畫蛇添足。6.2 涉及NULL時IN/NOT IN要格外小心這是我在實(shí)際項(xiàng)目里栽得最痛的一次。當(dāng)時查“沒有訂單記錄的員工”同事寫的是NOT IN結(jié)果查出來永遠(yuǎn)是空集。原因就是子查詢結(jié)果里有NULL?；A(chǔ)規(guī)則是這樣的如果子查詢返回的列表里哪怕有一個NULLNOT IN的結(jié)果往往就是“不成立”而不是“找到?jīng)]匹配的行”。比如SELECT emp_name FROM employee WHERE emp_id NOT IN (SELECT emp_id FROM orders);如果orders表里存在一行emp_id為NULL的記錄條件emp_id NOT IN (1, 2, NULL)的邏輯結(jié)果不是TRUE或FALSE而是NULL/UNKNOWN。WHERE只保留TRUE行于是什么都不返回。所以凡是寫NOT IN我都會先確認(rèn)子查詢列有沒有NOT NULL約束。如果沒有直接改用NOT EXISTS更安全性能也更好。6.3 嵌套太深就用CTE別疊三層以上子查詢可以無限嵌套但不代表我們應(yīng)該這么干。三層以內(nèi)勉強(qiáng)能靠縮進(jìn)讀懂超過三層基本只有寫它的人能維護(hù)一個月后再看就是天書。MySQL 8.0里我強(qiáng)烈建議用CTECommon Table Expression替代深嵌套子查詢。比如前面那個“找本部門工資高于平均工資的員工”用CTE寫是這樣WITH dept_avg AS ( SELECT department_id, AVG(salary) AS avg_salary FROM employee GROUP BY department_id ) SELECT e.emp_name, e.salary, d.avg_salary FROM employee e JOIN dept_avg d ON e.department_id d.department_id WHERE e.salary d.avg_salary;CTE的另一個好處是同一段中間結(jié)果可以被后續(xù)多個查詢復(fù)用而普通子查詢只能在一個地方使用。遇到一段邏輯要被引用兩次的場景CTE能顯著減少SQL膨脹。MySQL 8.0以下的版本沒法用CTE那就盡量把嵌套層數(shù)控制在兩層內(nèi)或者把復(fù)雜邏輯拆成視圖。6.4 上線之前用EXPLAIN ANALYZE看一眼真實(shí)執(zhí)行時間寫SQL不能靠猜尤其是上線到生產(chǎn)環(huán)境的報表SQL。MySQL 8.0.18開始支持EXPLAIN ANALYZE它能真正執(zhí)行SQL并返回實(shí)際執(zhí)行時間、行數(shù)和循環(huán)次數(shù)比單獨(dú)看EXPLAIN的估算更可靠。EXPLAIN ANALYZE SELECT e.emp_id, e.emp_name FROM employee e WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.emp_id e.emp_id );結(jié)果里會顯示類似“actual time0.1...0.2 rows1 loops5”的信息。這里loops尤其重要如果loops接近外層行數(shù)說明這就是相關(guān)子查詢在逐行循環(huán)那就是下一步優(yōu)化的主要對象。拿到真實(shí)數(shù)據(jù)后你改不改、改成什么都有了依據(jù)而不是憑感覺說“子查詢太慢”。我在實(shí)際使用中最常做的一件事就是先用小數(shù)據(jù)量驗(yàn)證邏輯對不對再拿生產(chǎn)數(shù)據(jù)量做EXPLAIN ANALYZE最后把慢的候選SQL集中優(yōu)化一遍。MySQL子查詢本身沒有善惡之分它只是SQL的一部分真正決定快慢的是你對數(shù)據(jù)結(jié)構(gòu)、索引和優(yōu)化器行為的理解。把這幾個維度串起來你就能在正確的地方用子查詢在合適的時候改寫它也就不需要再記什么“子查詢一定慢”的教條了。