前兩天幫一個(gè)朋友模擬面試他簡(jiǎn)歷上寫著“熟悉 MySQL 調(diào)優(yōu)”。我問(wèn)了一句EXPLAIN 出來(lái)的 typeref 是什么意思他答得很快非唯一索引等值查詢。然后我接著問(wèn)那 eq_ref 呢和 ref 差在哪復(fù)合唯一索引的最左前綴查詢type 會(huì)是 const 還是 ref他沉默了幾秒我就知道這個(gè)知識(shí)點(diǎn)要補(bǔ)課了。ref 這個(gè)詞很有意思它在 type 列里排在中間位置——比 range、index、ALL 好比 const、eq_ref 略差。很多面試者能背出這個(gè)順序但說(shuō)不清為什么。這篇文章把 ref 從頭到尾拆干凈從執(zhí)行計(jì)劃的含義到 BTree 的掃描原理再到真實(shí)優(yōu)化案例和面試話術(shù)適合正在準(zhǔn)備 MySQL 面試的人也適合平時(shí)用 EXPLAIN 優(yōu)化慢查詢但只停留在表面的開(kāi)發(fā)者。1. ref 到底是個(gè)啥從執(zhí)行計(jì)劃的第一列說(shuō)起1.1 type 列就是 MySQL 訪問(wèn)表的方式MySQL 執(zhí)行一條 SELECT 之前優(yōu)化器會(huì)生成一份執(zhí)行計(jì)劃EXPLAIN 就是把這計(jì)劃攤開(kāi)給你看的工具。很多初學(xué)的人盯著 select_type、table 這些列看半天但我一直認(rèn)為 type 這一列才是執(zhí)行計(jì)劃的靈魂因?yàn)樗苯痈嬖V你MySQL 到底是用什么姿勢(shì)從表里取數(shù)據(jù)的。type 的全稱叫訪問(wèn)類型access type本質(zhì)上描述的是存儲(chǔ)引擎層訪問(wèn)數(shù)據(jù)的策略。你見(jiàn)過(guò)的大多數(shù)值可以按效率從高到低粗略排序system const eq_ref ref ref_or_null range index ALL。注意這個(gè)排序不是官方文檔里的絕對(duì)順序?qū)嵅僦胁煌瑘?chǎng)景會(huì)有細(xì)微差異但作為面試回答的大框架是沒(méi)問(wèn)題的。繼續(xù)說(shuō) ref 的位置它卡在中間偏上的區(qū)域代表的是“用上了二級(jí)索引但是索引列不唯一等值匹配可能命中多行”的訪問(wèn)方式。換句話說(shuō)ref 意味著 MySQL 確實(shí)沒(méi)有悶頭做全表掃描而是順著索引去定位了一批行只是這批行可能不止一條。這個(gè)“可能不止一條”特別關(guān)鍵。很多人把 ref 簡(jiǎn)單理解成“走索引了”這不夠。走索引有很多種走法point 查詢、范圍查詢、索引全掃都是走索引但各自的開(kāi)銷和返回行數(shù)完全不是一個(gè)量級(jí)。ref 具體屬于哪一種我在后面的章節(jié)里展開(kāi)。1.2 ref 的官方定義與直觀例子看 MySQL 官方手冊(cè)對(duì) ref 的注釋原文大意是如果 join 操作只用到了索引的最左前綴或者用的是非唯一索引那么對(duì)于前一張表的每一行組合都會(huì)從這張表讀取所有匹配索引值的行這種訪問(wèn)方式就叫 ref。拆開(kāi)來(lái)看能把 type 判為 ref 的條件有三個(gè)用的是二級(jí)索引也就是非聚簇索引包括普通索引和唯一索引。查詢條件是等值比較最常見(jiàn)的就是WHERE 索引列 某個(gè)值。如果用聯(lián)合索引條件必須命中最左前綴如果索引本身有唯一性約束也必須只用到了左前綴而不是完整聯(lián)合唯一鍵。舉兩個(gè)具體例子。假設(shè)有一張用戶表user(id, name, age)其中name上有普通索引idx_name。執(zhí)行EXPLAIN SELECT * FROM user WHERE name 張三大概率你會(huì)看到 typerefref 列對(duì)應(yīng) const意思是用一個(gè)常量去索引列上做等值匹配。再假設(shè)有一個(gè)聯(lián)合唯一索引uk(a, b)執(zhí)行WHERE a 1這時(shí)候 type 依然會(huì)是 ref而不是 const。為什么因?yàn)閍1在索引里可能對(duì)應(yīng)多條記錄比如(1, 100)、(1, 200)唯一性保證不了行數(shù)是 1。這個(gè)細(xì)節(jié)如果面試能主動(dòng)講出來(lái)比背十句八股都有用。2. ref 和它的鄰居們const、eq_ref、ref_or_null、range 的區(qū)別2.1 一張表對(duì)比四種訪問(wèn)方式面試?yán)镒畛３霈F(xiàn)的連環(huán)追問(wèn)就是讓你把 ref 和前排的幾位“鄰居”做區(qū)分。我把它們放在同一張表里對(duì)比這樣信息密度最高看起來(lái)一目了然。訪問(wèn)類型典型場(chǎng)景索引要求可能返回行數(shù)常見(jiàn) SQL 形態(tài)const主鍵或完整唯一索引等值匹配唯一索引全部列最多 1 行WHERE id 1eq_ref聯(lián)表查詢時(shí)被驅(qū)動(dòng)表走主鍵/唯一索引唯一索引完整匹配每次最多 1 行JOIN ... ON t2.id t1.uidref普通二級(jí)索引等值匹配或唯一索引左前綴二級(jí)索引/最左前綴可能多行WHERE name 張三ref_or_nullref 基礎(chǔ)上額外查 NULL二級(jí)索引可能多行WHERE name 張三 OR name IS NULLrange索引列范圍比較二級(jí)索引范圍區(qū)間行WHERE age BETWEEN 20 AND 30這張表可以直接背但背完還得理解背后的邏輯。const 之所以叫“常量”是因?yàn)閮?yōu)化器在做執(zhí)行計(jì)劃之前就能確定最多只返回一行這行數(shù)據(jù)甚至可以被當(dāng)成常量直接嵌入計(jì)劃里代價(jià)低到可以忽略。eq_ref 是 join 語(yǔ)境里的 const它要求被驅(qū)動(dòng)表的連接字段是唯一索引這樣驅(qū)動(dòng)表每給一行被驅(qū)動(dòng)表最多回一行不會(huì)產(chǎn)生行數(shù)放大。而 ref 不保證行數(shù)唯一所以優(yōu)化器對(duì)它的代價(jià)估算要比 const 和 eq_ref 高一截。這也解釋了為什么 type 排序里 ref 排在它們后面它需要沿著索引掃描到一個(gè)“連續(xù)區(qū)間”區(qū)間里有幾條命中的索引條目就得處理幾條。2.2 面試官最愛(ài)挖的兩個(gè)坑唯一索引左前綴和 eq_ref 的歸屬第一個(gè)坑就是我前面提到的復(fù)合唯一索引。很多候選人背了“const 是唯一索引等值查詢”一到實(shí)際場(chǎng)景就翻車UNIQUE INDEX uk(a, b)然后 SQL 寫WHERE a 1他們脫口而出 type 是 const。錯(cuò)就錯(cuò)在沒(méi)用“完整唯一索引”這五個(gè)字。官方對(duì) const 的定義明確寫的是“最多返回一行”而a1在組合唯一索引里可能有 (1, x)、(1, y) 多行雖然索引本身唯一但查詢條件沒(méi)有鎖死所有組成列行數(shù)唯一性就沒(méi)了。第二個(gè)坑是 eq_ref 和 ref 的歸屬。有一個(gè)我常聽(tīng)到的錯(cuò)誤答案是“eq_ref 是等值引用ref 也是等值引用兩者區(qū)別不大”。實(shí)際上 eq_ref 幾乎只在多表 join 中被驅(qū)動(dòng)表的位置出現(xiàn)它強(qiáng)制要求連接列是主鍵或完整唯一索引并且連接條件走的是索引列的全部列。舉個(gè)例子SELECT * FROM orders o JOIN users u ON o.user_id u.id如果orders是驅(qū)動(dòng)表users通過(guò)主鍵id被查找那么對(duì) users 的訪問(wèn)類型就是 eq_ref。你幾乎不會(huì)在單表單條 SQL 里看到 eq_ref這一點(diǎn)能幫你在面試時(shí)快速判斷訪問(wèn)類型的實(shí)際含義。ref 和 eq_ref 的底層差異也決定了優(yōu)化器行為eq_ref 每次從驅(qū)動(dòng)表拿到一行去被驅(qū)動(dòng)表做一次點(diǎn)查最多返回一行代價(jià)近似 O(N)ref 從驅(qū)動(dòng)表每拿到一行去被驅(qū)動(dòng)表的二級(jí)索引上可能取回多條如果被驅(qū)動(dòng)表索引選擇性不好代價(jià)可能退化成接近 O(N*M)。這也是為什么有時(shí)候 MySQL 會(huì)寧愿改走全表掃描而不選 ref后面實(shí)戰(zhàn)部分我會(huì)專門演示。3. 為什么二級(jí)索引等值匹配就是 refBTree 掃描區(qū)間分析3.1 從聚簇索引和二級(jí)索引說(shuō)起要理解 ref 為什么是現(xiàn)在這個(gè)樣子得先回到 InnoDB 的索引結(jié)構(gòu)。InnoDB 表默認(rèn)按主鍵聚簇主鍵索引的葉子節(jié)點(diǎn)直接存整行數(shù)據(jù)這叫聚簇索引。你手動(dòng)在其它列上建的索引叫二級(jí)索引二級(jí)索引的葉子節(jié)點(diǎn)不存整行只存“索引列的值 主鍵值”。當(dāng)你通過(guò)二級(jí)索引找數(shù)據(jù)流程是先在二級(jí)索引的 BTree 里定位到目標(biāo)索引條目拿到主鍵然后再回聚簇索引查完整行這一步就是常說(shuō)的回表。這個(gè)設(shè)計(jì)能帶來(lái)很多好處但也有代價(jià)二級(jí)索引本身是一棵獨(dú)立的 BTree索引列有序排列而回表是額外的一次隨機(jī) IO。ref 這個(gè)訪問(wèn)類型就是在這種結(jié)構(gòu)下產(chǎn)生的標(biāo)準(zhǔn)動(dòng)作——通過(guò)二級(jí)索引先定位再看是否需要回表。這里有個(gè)很容易被忽略的點(diǎn)二級(jí)索引的葉子節(jié)點(diǎn)之間有鏈表連接并且按索引列值順序排列。所有索引查找的本質(zhì)都可以抽象成“在有序數(shù)組里劃一個(gè)或多個(gè)區(qū)間然后順序讀取區(qū)間內(nèi)的葉子節(jié)點(diǎn)”。ref 對(duì)應(yīng)的區(qū)間正好是“索引列值等于某個(gè)常量的所有葉子節(jié)點(diǎn)”也就是一個(gè)準(zhǔn)確定值區(qū)間。區(qū)間內(nèi)有多少條目不取決于 SQL 怎么寫而取決于這個(gè)索引列在表里有多少重復(fù)值。3.2 等值匹配如何在 BTree 上形成連續(xù)掃描區(qū)間很多文章講 BTree講得最多的是“矮胖樹(shù)、減少磁盤 IO”卻很少講清楚“掃描區(qū)間”這個(gè)概念。我第一次徹底理解 ref是因?yàn)榭戳艘粋€(gè)調(diào)優(yōu)案例一張表 1000 萬(wàn)行name索引區(qū)分度很低等值查一個(gè)名字能命中 20 萬(wàn)行。執(zhí)行計(jì)劃里 typerefrows 顯示 20 萬(wàn)。當(dāng)時(shí)我就意識(shí)到ref 并不是某些人想象中的“點(diǎn)查”它的效率完全取決于區(qū)間長(zhǎng)度。過(guò)程是這樣的優(yōu)化器把WHERE name張三轉(zhuǎn)換成對(duì)二級(jí)索引的一個(gè)區(qū)間掃描起始位置是索引樹(shù)中第一個(gè)等于‘張三’的葉子節(jié)點(diǎn)終止位置是最后一個(gè)等于‘張三’的葉子節(jié)點(diǎn)。BTree 的等值查找從根節(jié)點(diǎn)開(kāi)始逐層下探每一層通過(guò)二分比較最終定位到起始葉子然后順著葉子節(jié)點(diǎn)的鏈表向后遍歷直到遇到的索引值不再是‘張三’為止。命中的每一條葉子記錄里存的是主鍵值再用這個(gè)主鍵值回表查整行。這個(gè)機(jī)制的言外之意是只要葉子節(jié)點(diǎn)上命中的條數(shù)變多掃描時(shí)間就線性增長(zhǎng)回表次數(shù)也線性增長(zhǎng)。所以同樣顯示 typerefrows1的 SQL 和rows200000的 SQL實(shí)際性能天差地別。面試時(shí)如果能主動(dòng)說(shuō)出“ref 的執(zhí)行時(shí)間主要取決于匹配行數(shù)和回表成本”面試官就會(huì)覺(jué)得你是真用 EXPLAIN 排過(guò)障的人而不是只會(huì)背概念。3.3 ref 和覆蓋索引、ICP 的組合含義typeref 只告訴你 MySQL 用二級(jí)索引做了定位但沒(méi)告訴你回沒(méi)回表?；貨](méi)回表要看 Extra 這一列。這是我觀察到的另一個(gè)高頻誤區(qū)很多人以為 typeref 就代表“性能很好不用回表”。實(shí)際上 ref 和回表是兩個(gè)維度的事情。如果 SQL 只 SELECT 索引列本身比如SELECT name FROM user WHERE name張三二級(jí)索引葉子節(jié)點(diǎn)上的數(shù)據(jù)已經(jīng)足夠返回結(jié)果不需要回表Extra 會(huì)顯示Using index。這是最理想的 ref。如果 SELECT 了索引以外的列比如SELECT *那必須回表取整行Extra 不顯示Using index。如果 WHERE 里除了等值匹配條件還有額外的索引列過(guò)濾條件比如聯(lián)合索引 (name, age) 上執(zhí)行WHERE name張三 AND age20MySQL 5.6 以后可以把 age 的條件判斷下推到存儲(chǔ)引擎層先索引掃出來(lái)再過(guò)濾減少回表次數(shù)Extra 會(huì)顯示Using index condition也就是 ICP索引條件下推。這三個(gè)組合能回答一個(gè)很常見(jiàn)的面試連環(huán)問(wèn)“同樣是 ref為什么有的 SQL 快有的 SQL 慢”答案就在 Extra 列里。Using index完全不回表Using index condition部分過(guò)濾后再回表什么都沒(méi)有就要老老實(shí)實(shí)逐條回表。這幾個(gè)狀態(tài)想清楚了你對(duì) ref 的理解深度已經(jīng)超過(guò) 80% 的候選人。4. 實(shí)戰(zhàn)演示把一條 typeALL 的慢 SQL 優(yōu)化到 typeref4.1 建表、造數(shù)、一條慢查詢講了這么多理論現(xiàn)在落地跑一遍。我用一個(gè)典型場(chǎng)景用戶表收集 user_id、name、age、email 四個(gè)字段模擬一百多萬(wàn)行數(shù)據(jù)。沒(méi)有任何索引的原始狀態(tài)執(zhí)行計(jì)劃通常會(huì)讓人絕望。CREATE TABLE user ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, age INT NOT NULL, email VARCHAR(100) NOT NULL ) ENGINEInnoDB; -- 批量插入若干條數(shù)據(jù)這里用存儲(chǔ)過(guò)程造一百萬(wàn)行 -- 簡(jiǎn)單示意實(shí)際生產(chǎn)用數(shù)據(jù)生成工具或腳本執(zhí)行下面這條查詢EXPLAIN SELECT * FROM user WHERE name 趙四;在沒(méi)有索引時(shí)type 列會(huì)是 ALLkey 列是 NULLrows 接近全表行數(shù)Extra 顯示Using where。ALL 表示這條 SQL 要把聚簇索引的葉子節(jié)點(diǎn)從頭到尾掃一遍這是最壞的情況。Using where也說(shuō)明過(guò)濾是在存儲(chǔ)引擎把所有數(shù)據(jù)吐出來(lái)之后、在 server 層做的意味著大量無(wú)用數(shù)據(jù)被白白讀了一遍。這個(gè)場(chǎng)景在真實(shí)生產(chǎn)環(huán)境里太常見(jiàn)了用戶表越來(lái)越大查詢條件寫得沒(méi)問(wèn)題就是沒(méi)建索引于是一條按 name 查用戶信息的簡(jiǎn)單語(yǔ)句硬生生把數(shù)據(jù)庫(kù) CPU 打到 100%。很多初級(jí) DBA 第一反應(yīng)是“加內(nèi)存、加緩存”實(shí)際上你 EXPLAIN 一下加一個(gè)索引就能解決。4.2 加索引前后 EXPLAIN 對(duì)比給 name 字段建一個(gè)普通二級(jí)索引注意不是唯一索引因?yàn)槲覀冊(cè)试S同名用戶存在ALTER TABLE user ADD INDEX idx_name(name);再次執(zhí)行 EXPLAINEXPLAIN SELECT * FROM user WHERE name 趙四;這時(shí)候 type 變成 refkey 顯示 idx_namekey_len 是 varchar(50) 在 utf8mb4 字符集下對(duì)應(yīng)的字節(jié)長(zhǎng)度ref 列顯示 constrows 從一百多萬(wàn)掉到個(gè)位數(shù)。整條 SQL 的掃描范圍從全表變成“索引等值區(qū)間”性能提升是數(shù)量級(jí)的。再看一個(gè)覆蓋索引的例子EXPLAIN SELECT name, age FROM user WHERE name 趙四;如果我把索引改成聯(lián)合索引 (name, age)那么這條查詢的 type 依然是 ref但 Extra 會(huì)多一個(gè)Using index表示所有需要的列都在索引里不需要回表。這個(gè)優(yōu)化在實(shí)際項(xiàng)目里特別實(shí)用因?yàn)樗〉舻牟皇且粌纱?IO而是每一行命中的隨機(jī)回表 IO。4.3 為什么有時(shí)候加了索引 type 還是 ALL選擇性陷阱這里有個(gè)特別值得拿出來(lái)講的實(shí)戰(zhàn)經(jīng)驗(yàn)加了索引type 也不一定變 ref。如果一個(gè)索引列的重復(fù)值太多MySQL 優(yōu)化器會(huì)自己算一筆賬通過(guò)二級(jí)索引定位到海量主鍵再逐條回表成本和直接全表掃描差不多甚至更高于是它寧可走 ALL 也不走 ref。拿我剛才那張表舉例如果里面只有 10 個(gè)不同的 name每個(gè) name 對(duì)應(yīng)十萬(wàn)行那么WHERE name趙四雖然能用 idx_name 定位但要回表十萬(wàn)次InnoDB 大概會(huì)認(rèn)為全表掃描更快。這時(shí)候你在 EXPLAIN 里看到的 type 可能還是 ref也可能變成 ALL具體取決于優(yōu)化器版本和統(tǒng)計(jì)信息但 rows 會(huì)告訴你真實(shí)情況。這種場(chǎng)景怎么救核心思路不是強(qiáng)扭優(yōu)化器而是把索引做成覆蓋索引。如果你把 SQL 改成SELECT name, age FROM user WHERE name趙四并且索引是 (name, age)所有數(shù)據(jù)都在索引里不需要回表那么即使某個(gè) name 有十萬(wàn)行也是順序掃索引葉子節(jié)點(diǎn)成本可控。這就是為什么我一直建議想要 ref 效果穩(wěn)定優(yōu)先考慮把高頻查詢里的字段塞進(jìn)聯(lián)合索引做成覆蓋索引而不是指望一個(gè)單列索引包打天下。5. 面試追問(wèn)轟炸區(qū)ref 相關(guān)的五個(gè)深坑5.1 最左前綴原則下 ref 的邊界聯(lián)合索引 (a, b, c)查詢條件WHERE b1 AND c2能不能用上 ref答案是大概率不能。二級(jí)索引先按 a 排序a 相同再按 bb 相同再按 c。跳過(guò)了 a 直接等值匹配 b索引本身的有序性發(fā)揮不出來(lái)優(yōu)化器只能走 index 全索引掃描或者 ALL。這是最左前綴原則的基本盤。再往深問(wèn)一層WHERE a1 AND c2會(huì)是什么 typea 能命中索引左前綴typerefc 無(wú)法直接參與索引定位但它屬于索引列可以在索引內(nèi)部做過(guò)濾MySQL 會(huì)視情況啟用 ICP在掃描 (a1) 區(qū)間的過(guò)程中過(guò)濾 c2。所以這條 SQL 的 type 可能依然是 ref但 Extra 里會(huì)出現(xiàn)Using index condition字樣。能把這個(gè)細(xì)節(jié)講清楚才是真的理解最左前綴和 ref 的關(guān)系。5.2 索引列上動(dòng)手腳ref 秒變 ALL這是實(shí)戰(zhàn)里最常見(jiàn)的“意外情況”。索引列被函數(shù)包裹或者發(fā)生隱式類型轉(zhuǎn)換都會(huì)導(dǎo)致索引失效。比如 name 上有索引但寫的是WHERE UPPER(name)ZHANGSANMySQL 無(wú)法直接使用 name 的 BTree 有序結(jié)構(gòu)去定位因?yàn)樗饕锎娴氖窃贾刀皇呛瘮?shù)結(jié)果type 直接退化。再比如 phone 字段是 varchar但查詢寫WHERE phone 13800138000這里的 13800138000 會(huì)被當(dāng)成數(shù)字類型。MySQL 為了比較會(huì)把 phone 字段轉(zhuǎn)型成數(shù)字一旦對(duì)列本身做隱式轉(zhuǎn)換索引定位就用不了了。刷面試題的時(shí)候很多候選人能答出這條規(guī)律但問(wèn)“為什么”就卡住。其實(shí)原因很簡(jiǎn)單BTree 的有序性依賴原始列值的排列任何對(duì)列的加工都會(huì)破壞這個(gè)排列索引自然就廢了。5.3 ref_or_null、ORDER BY、NULL 對(duì) ref 的影響有一種特殊的 ref 變體叫 ref_or_null出現(xiàn)條件是WHERE key abc OR key IS NULL。普通等值匹配只需要掃索引里等于‘a(chǎn)bc’的區(qū)間但加上 IS NULL 之后MySQL 還得另外把值為 NULL 的索引條目也掃一遍。Extra 里通常能看到Using wheretype 顯示 ref_or_null。面試時(shí)能補(bǔ)一句“ref_or_null 比 ref 多一次 NULL 掃描”說(shuō)明你讀過(guò)官方文檔。ORDER BY 對(duì) ref 也有影響。比如SELECT * FROM user WHERE name張三 ORDER BY age如果 name 上有索引但 age 沒(méi)有MySQL 拿到所有匹配行后要額外做一次 filesort但如果聯(lián)合索引是 (name, age)排序就可以直接利用索引順序避免額外排序。常見(jiàn)面試追問(wèn)是“type 顯示 ref 時(shí)ORDER BY 能一定避免 filesort 嗎”答案是不能必須看排序字段是否包含在同一個(gè)索引中。NULL 本身比較特殊。如果索引列允許 NULL那么等值條件WHERE name張三不會(huì)匹配到 NULL 行。NULL 行只能靠 IS NULL 查出來(lái)。這也解釋了為什么很多時(shí)候 DBA 建議把索引列設(shè)置為 NOT NULL一方面是避免語(yǔ)義混淆另一方面是減少索引掃描的額外分支。6. 答題話術(shù)把 ref 答出層次感6.1 三句話及格版如果面試官只給了你三十秒你可以這樣答ref 是 MySQL 執(zhí)行計(jì)劃里 type 列的一種訪問(wèn)類型代表查詢用到了非唯一二級(jí)索引或者唯一索引的最左前綴做等值匹配匹配結(jié)果可能返回多行。它比 range、index、ALL 好因?yàn)橹辽僮咚饕ㄎ坏?const、eq_ref 差因?yàn)椴槐ＷC只返回一行可能需要回表。這個(gè)回答能拿到及格分因?yàn)楦拍顪?zhǔn)確還點(diǎn)出了它在訪問(wèn)類型譜系里的位置。但如果面試官想深挖這個(gè)答案撐不了太久因?yàn)闆](méi)解釋為什么也沒(méi)展示你對(duì)執(zhí)行計(jì)劃細(xì)節(jié)的熟悉程度。6.2 帶原理的加分版如果能多給一分鐘我會(huì)推薦這樣答訪問(wèn)類型里的 ref 本質(zhì)上是二級(jí)索引等值匹配。InnoDB 的二級(jí)索引是一棵獨(dú)立的 BTree葉子節(jié)點(diǎn)按索引列有序排列并且存了主鍵值等值條件會(huì)被優(yōu)化器轉(zhuǎn)換成一個(gè)掃描區(qū)間MySQL 從索引根節(jié)點(diǎn)定位到區(qū)間的起始葉子再沿葉子鏈表順序掃描到值變化為止。這個(gè)區(qū)間里有多少條記錄取決于索引列的選擇性。如果再往深處看ref 和回表是兩個(gè)維度的問(wèn)題SELECT 語(yǔ)句只取索引列時(shí)Extra 會(huì)出現(xiàn) Using index不用回表如果取索引列以外的字段就要回表聚簇索引。聯(lián)合索引情況下還可以配合 ICP把部分過(guò)濾條件下推到存儲(chǔ)引擎層。最左前綴原則也在這里生效跳過(guò)聯(lián)合索引最左側(cè)列通常就沒(méi)法形成 ref。這段話含金量很高因?yàn)樗言L問(wèn)類型的含義、數(shù)據(jù)結(jié)構(gòu)的支撐、實(shí)際執(zhí)行的IO路徑、相關(guān)優(yōu)化特性全部串起來(lái)了。面試官想繼續(xù)問(wèn)也只能往更深的方向問(wèn)但你已經(jīng)證明了自己不是背題人。6.3 被追問(wèn)時(shí)的應(yīng)對(duì)邏輯面試中問(wèn)完 ref大概率會(huì)繼續(xù)追 const、eq_ref、range。我的建議是不要背定義而是記一條主線type 的排序本質(zhì)上是“定位一條數(shù)據(jù)的成本從低到高”。const 是唯一匹配一行eq_ref 是 join 里唯一匹配一行ref 是二級(jí)索引匹配多行range 是一個(gè)區(qū)間index 是掃整個(gè)索引ALL 是掃全表。所有追問(wèn)都是圍繞這條主線展開(kāi)的。如果被問(wèn)到“為什么 typeref 了還是很慢”不要慌從三個(gè)角度排查回表次數(shù)是不是太多也就是索引選擇性好不好Extra 列是不是沒(méi)有 Using index說(shuō)明每條命中的記錄都額外回表了ORDER BY 或 GROUP BY 有沒(méi)有造成 filesort。這三個(gè)點(diǎn)說(shuō)完基本就把一個(gè)實(shí)際調(diào)優(yōu)問(wèn)題答全了。我在實(shí)際面試?yán)镆?jiàn)過(guò)不少人能準(zhǔn)確說(shuō)出 ref 的定義但一談到真實(shí) SQL 調(diào)優(yōu)就露餡。原因在于只背了概念沒(méi)有把執(zhí)行計(jì)劃、索引結(jié)構(gòu)、回表成本這條鏈路打通。希望這篇解析能幫你把 ref 這個(gè)點(diǎn)真正釘在腦子里下次無(wú)論是面試還是排查慢查詢都能自信地跟人聊出深度來(lái)。