1. 從一次慢查詢引發的“靈魂拷問”說起那天下午監控系統突然報警一個核心接口的響應時間從平時的幾十毫秒飆升到了十幾秒。團隊立刻進入“戰備”狀態我作為當時的值班工程師第一反應就是去查數據庫。登錄到數據庫管理工具找到那條拖垮接口的SQL它看起來并不復雜就是一個多表關聯查詢附帶幾個篩選條件。直覺告訴我問題出在索引上但具體是哪個環節是全表掃描了還是用錯了索引是關聯順序有問題還是臨時表拖了后腿光靠猜是沒用的這時候EXPLAIN就成了我手中最鋒利的“手術刀”。對于任何與數據庫打交道的開發者、DBA甚至數據分析師來說EXPLAIN都是一個必須掌握的核心技能。它不是什么高深莫測的黑魔法而是數據庫引擎提供的一份“執行計劃說明書”。當你把一條SQL語句交給數據庫時數據庫的優化器會像一位老練的廚師思考如何用最快的速度做出這道菜——是先切配菜過濾數據還是先熱鍋選擇驅動表是用猛火快炒走索引還是需要文火慢燉全表掃描。EXPLAIN就是把這位廚師的“做菜思路”完整地展示給你看。很多人對EXPLAIN的理解停留在“看有沒有走索引”的層面這遠遠不夠。索引只是執行計劃中的一個環節。一份完整的EXPLAIN輸出能告訴你查詢將訪問哪些表、以何種順序訪問、使用何種連接方法、預估需要檢查多少行數據、是否使用了臨時表、是否進行了文件排序等關鍵信息。讀懂它你就能精準定位性能瓶頸是索引缺失、索引失效、統計信息不準還是SQL寫法本身就有優化空間。可以說EXPLAIN是數據庫性能調優的“第一性原理”繞過它去談優化無異于盲人摸象。本文將以MySQL的EXPLAIN為核心其原理和大部分字段與其他如PostgreSQL的EXPLAIN相通帶你徹底拆解這份“執行計劃說明書”。我不會僅僅羅列字段含義而是結合大量真實的調優場景告訴你每個字段背后的“為什么”以及看到異常值時該如何思考和行動。無論你是剛接觸數據庫的新手還是希望深化理解的資深開發者這篇文章都將是你手邊一份詳實的實戰指南。2. 執行計劃的核心字段逐行精解拿到一份EXPLAIN的輸出通常是一個表格每一行代表查詢中的一個操作例如訪問一個表。每一列則描述了該操作的詳細信息。我們常說“讀執行計劃”其實就是解讀這些列的組合含義。下面我們深入到每一個核心字段看看它們到底在說什么。2.1id: 查詢的執行順序與嵌套關系id是執行計劃的“序列號”但它表示的并不是絕對的執行順序而是查詢的“輪次”或“層級”。id相同表示這些操作屬于同一個SELECT執行順序從上到下。通常出現在多表JOIN中數據庫會按照優化器決定的順序依次執行連接。id不同如果是子查詢id序號會遞增。id值越大優先級越高越先執行。這很直觀內層的子查詢需要先計算出結果才能供外層查詢使用。id為NULL這通常出現在UNION結果合并的衍生表unionM,N行。它表示這是一個用于合并結果的臨時操作。實戰經驗看id是理解復雜查詢執行流的第一步。如果看到一個很大的查詢id很多且不同就要警惕嵌套過深的子查詢可能帶來的性能問題考慮能否改寫為JOIN。2.2select_type: 查詢類型的“身份標簽”這一列告訴你當前行對應的是簡單查詢還是復雜查詢中的哪一部分。常見的類型有SIMPLE最簡單的查詢不包含子查詢或UNION。這是你最希望看到的類型。PRIMARY查詢中最外層的SELECT或者在子查詢中位于最外層的SELECT。SUBQUERY在SELECT或WHERE列表中包含了子查詢且該子查詢不依賴于外部查詢。DEPENDENT SUBQUERY同樣是個子查詢但它的結果依賴于外部查詢的字段。這是一個危險信號因為對于外部查詢的每一行這個子查詢都可能要重新執行一次極易導致性能災難。DERIVED來自FROM子句的子查詢派生表。MySQL會將這些子查詢的結果物化成一個臨時表然后對外部查詢進行處理。如果派生表數據量很大創建臨時表的過程會很耗資源。UNIONUNION中的第二個或后續的SELECT。UNION RESULT從UNION臨時表檢索結果的SELECT。避坑指南當你看到DEPENDENT SUBQUERY或DERIVED且涉及大數據集時性能往往不佳。優化的方向通常是嘗試用JOIN重寫查詢或者確保派生表子查詢本身是高效、結果集小的。2.3table: 當前操作的對象這一列顯示當前行正在訪問哪個表。它可能是實際的表名也可能是諸如derivedNid為N的查詢產生的派生表、unionM,NUNION了id為M和N的查詢結果這樣的別名。2.4partitions: 匹配的分區信息如果你的表使用了分區這一列會顯示查詢命中了哪些分區。對于非分區表此列為NULL。這是進行分區裁剪優化的重要觀察點。2.5type: 訪問類型——性能的“生死線”這是EXPLAIN中最關鍵的列之一它顯示了數據庫決定如何查找表中的行。從最優到最差常見的類型排列大致如下systemconsteq_refrefrangeindexALLsystem/const性能最優。system是const的特例表里只有一行數據。const表示通過主鍵或唯一索引進行等值查詢最多返回一行。因為結果確定所以速度極快。EXPLAIN SELECT * FROM users WHERE id 1; -- type 很可能是 const因為 id 是主鍵。eq_ref在多表連接時對于前一個表的每一行在當前表中只找到唯一的一行與之匹配。通常出現在使用主鍵或非空唯一索引進行關聯查詢時。這是性能最好的連接類型之一。EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id u.id; -- 如果 u.id 是主鍵對于 orders 表的每一行在 users 表中通過主鍵查找一行type 就是 eq_ref。ref比eq_ref稍差表示使用非唯一索引進行等值查找可能會返回多行。如果匹配的行數很少性能依然很好。EXPLAIN SELECT * FROM users WHERE email userexample.com; -- 如果 email 字段上有普通索引type 就是 ref。range使用索引檢索給定范圍的行常見于BETWEEN、、、IN()、LIKE ‘prefix%’注意前綴匹配等操作。EXPLAIN SELECT * FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-01-31; -- 如果 create_time 有索引type 就是 range。index全索引掃描。它遍歷整個索引樹來獲取數據雖然避免了全表掃描但通常也需要讀取大量的索引條目。當查詢的列全部包含在某個索引中覆蓋索引且需要讀取大部分索引條目時可能會走index。EXPLAIN SELECT id FROM users; -- id 是主鍵也是一種索引這條查詢只取 id 列可能會走 index 掃描主鍵索引。ALL全表掃描。性能最差意味著數據庫需要逐行檢查表中的所有數據來找到匹配的行。當表數據量很大時這將是災難性的。核心心法優化type列是索引優化的首要目標。我們的核心戰斗就是盡可能避免ALL減少index爭取達到range、ref在關聯查詢中追求eq_ref。2.6possible_keys與key: 可能用與實際用的索引possible_keys查詢可能使用到的索引。這是一個理論值由優化器根據WHERE、JOIN、ORDER BY等子句中涉及的列計算得出。這一列為NULL并不意味著沒索引可用有時可能因為數據分布等原因優化器認為全表掃描更快。key查詢實際決定使用的索引。如果為NULL則表示沒有使用索引。關鍵洞察如果possible_keys有值而key為NULL這通常是一個強烈的警告信號。它意味著優化器認為使用索引的成本回表等開銷高于全表掃描。你的索引可能因為函數操作、類型轉換等原因而“失效”了。 你需要仔細檢查SQL語句和索引定義。2.7key_len: 索引使用長度的“顯微鏡”key_len表示查詢中使用的索引字段的最大可能長度字節數。通過這個值你可以判斷索引是否被“充分”使用。計算規則對于定長字段如INT4字節BIGINT8字節DATE3字節直接使用其固定長度。對于變長字段如VARCHAR(N)需要額外考慮長度前綴通常1或2字節和字符集如utf8mb4是4字節/字符。是否為NULL也會占用1字節標識位。實戰意義如果key_len小于索引定義的總長度說明只使用了索引的前綴部分復合索引的最左匹配原則。對比key_len與索引定義長度是驗證復合索引是否高效起作用的絕佳手段。舉例有一個復合索引idx_name_age (name, age)name是VARCHAR(20) utf8mb4age是INT。查詢WHERE name ‘Alice’key_len大約是20*4 1(變長前綴) 1(NULL標識如果可為空)。只用了索引的第一部分。查詢WHERE name ‘Alice’ AND age 25key_len會加上age的4字節說明索引的兩部分都被用到了。2.8ref: 哪些列或常量被用于索引查找這一列顯示與key列指定的索引進行比較的列或常量。它告訴你索引查找是基于什么值進行的。常見形式有const常量、func某個函數的結果、db.table.column其他表的列。在多表關聯中觀察ref列可以幫助你理解連接條件是如何被使用的。2.9rows: 優化器的“預估成本”這是一個估算值表示MySQL認為它必須檢查多少行才能找到所需的行。這個數字基于表的統計信息。它是性能評估的一個核心指標。重要性即使type是ref或range如果rows值非常大比如幾萬、幾十萬也意味著查詢需要處理大量數據可能仍然很慢。這時可能需要更優的索引來減少掃描行數。注意rows是每張表的估算值。對于多表連接總成本是所有表rows值的某種乘積取決于連接類型這個值會急劇放大。所以優化時要重點關注rows最大的那個表驅動表。2.10filtered: 條件過濾的“百分比”這個字段表示存儲引擎返回的數據在經過WHERE條件過濾后剩余行數的百分比。它是一個0到100之間的估算值。rows * filtered / 100可以粗略估算出將與下一張表進行連接的行數。新版MySQL的洞察在MySQL 5.7及以上版本EXPLAIN的輸出默認包含filtered列。它對于理解多表連接的成本特別有用。如果驅動表的filtered值很低比如10%意味著WHERE條件過濾掉了大部分數據這對性能是好事。如果很高比如100%且rows很大則意味著大量數據將流入下一個連接步驟需要警惕。2.11Extra: 額外信息——“魔鬼在細節中”這一列包含MySQL解決查詢的額外信息很多重要的性能線索都藏在這里。下面是一些需要高度關注的“壞消息”Using filesort警告這意味著MySQL無法利用索引完成排序需要額外的排序步驟。它可能會在磁盤上創建臨時文件進行排序當數據量大時非常消耗CPU和內存。看到這個就應該考慮為ORDER BY或GROUP BY的列建立合適的索引。Using temporary嚴重警告這意味著查詢需要創建臨時表來保存中間結果常見于GROUP BY、DISTINCT、UNION等操作。在磁盤上創建臨時表當內存不夠時會帶來巨大的性能開銷。Using index好消息這表示查詢使用了“覆蓋索引”即所需的數據列全部包含在索引中因此無需回表查詢數據行。這是極高的性能優化。Using where表示存儲引擎返回的行需要在服務器層再進行一次WHERE條件過濾。如果type是ALL或index且Using where通常意味著性能不佳。Using join buffer (Block Nested Loop)表示連接查詢使用了連接緩沖區。當被驅動表沒有可用索引時可能會出現這個。這通常意味著連接效率不高需要考慮為被驅動表的連接字段添加索引。3. 實戰演練從執行計劃到優化決策理解了每個字段的含義我們來看如何將它們組合起來解決實際問題。我們模擬一個經典的電商場景orders訂單表 和users用戶表。初始表結構簡化與數據量假設users表100萬用戶主鍵id在email和create_time上有獨立索引。orders表1000萬訂單主鍵id有user_id外鍵索引status狀態字段amount金額字段create_time下單時間字段。場景一查詢某個用戶的所有訂單一個典型的低效查詢EXPLAIN SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC;假設這條SQL執行很慢。我們來看可能出現的執行計劃及分析idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_ididx_user_id5const50100.00Using filesort解讀與優化type: ref使用了user_id索引這是好的開始。rows: 50預估找到約50條該用戶的訂單數據量不大。Extra: Using filesort問題所在雖然通過索引快速找到了用戶的訂單但排序字段create_time沒有包含在idx_user_id索引中。因此MySQL需要將這50條記錄撈出來回表在內存或磁盤上進行一次額外的排序。優化方案建立復合索引(user_id, create_time)。這樣索引本身就能按照user_id等值篩選并且在user_id相同的情況下數據已經按照create_time排序了。優化后的執行計劃Extra列很可能變成Using index condition如果查詢列不全在索引中或NULL如果覆蓋索引Using filesort消失。場景二查詢過去一個月內狀態為“已完成”的訂單并按金額排序EXPLAIN SELECT * FROM orders WHERE status completed AND create_time 2024-04-01 ORDER BY amount DESC LIMIT 100;假設status和create_time上都有獨立索引但查詢依然很慢。idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersALLidx_status, idx_create_timeNULLNULLNULL998000011.11Using where; Using filesort解讀與優化type: ALLkey: NULL災難優化器放棄了所有索引選擇了全表掃描近1000萬行。possible_keys顯示有兩個索引可用但都沒用。為什么因為獨立索引idx_status和idx_create_time各自只能優化一個條件。優化器評估后發現先用status索引篩選出大量“已完成”訂單再過濾時間或者先用時間索引篩選出最近一個月的訂單再過濾狀態其成本大量的回表操作過濾都可能高于直接全表掃描。Using where; Using filesort雪上加霜需要自己過濾還要在巨大的結果集上排序。優化方案建立復合索引(status, create_time, amount)。注意順序第一列status用于等值匹配快速縮小范圍。第二列create_time用于范圍查詢在status相同的條件下create_time是有序的。第三列amount雖然ORDER BY amount無法直接利用索引排序因為create_time是范圍查詢打斷了索引的連續性但將其放入索引可以形成覆蓋索引避免回表同時如果配合LIMIT在內存中排序少量數據也會快很多。 更優的寫法可能是建立(status, create_time)索引并確保status的過濾性足夠好。如果status’completed’的數據仍然很多可能需要考慮分區或更復雜的優化策略。場景三關聯查詢用戶及其訂單信息EXPLAIN SELECT u.name, o.order_no, o.amount FROM users u JOIN orders o ON u.id o.user_id WHERE u.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 100;idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEurangePRIMARY,idx_create_timeidx_create_time4NULL20000100.00Using index condition; Using temporary; Using filesort1SIMPLEorefidx_user_ididx_user_id5db.u.id10100.00NULL解讀與優化驅動表是u(users)因為u的type是range使用了時間索引rows預估2萬行。被驅動表o(orders)type是ref使用user_id索引關聯每次關聯預估10行效率尚可。核心問題在Extra驅動表u出現了Using temporary; Using filesort。這是因為我們需要對最終結果按o.create_time排序但驅動表是u排序字段在o表。MySQL需要將連接后的結果集放入臨時表再進行排序非常低效。優化方案這種“排序字段在非驅動表”的問題通常有兩種思路改變驅動表如果先排序再連接成本更低可以嘗試用子查詢。例如SELECT ... FROM (SELECT user_id FROM users WHERE create_time ... ORDER BY id LIMIT 1000) u JOIN orders o ...先限制驅動表數量。使用覆蓋索引優化確保被驅動表的連接和排序能高效完成。這里可以為orders表建立(user_id, create_time)復合索引并讓查詢只選擇索引包含的列覆蓋索引減少回表。重寫查詢有時根據業務邏輯可以調整查詢方式。例如如果業務上更關心“最新訂單對應的用戶”可以反過來以orders為驅動表SELECT ... FROM orders o JOIN users u ... WHERE o.create_time ... ORDER BY o.create_time DESC LIMIT 100并為orders.create_time建立索引。4. 進階EXPLAIN ANALYZE與執行計劃的局限性傳統的EXPLAIN輸出的是優化器預估的執行計劃。而 MySQL 8.0.18 引入的EXPLAIN ANALYZE是一個革命性的工具它會實際執行查詢并返回每個步驟的實際執行時間、實際返回行數等詳細信息。EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC;輸出會是一個樹狀結構包含每個迭代器的實際成本如(cost... rows... actual time... loops...)。actual time是核心它告訴你每個操作實際花了多少時間單位通常是毫秒。EXPLAIN的局限性它是預估的rows和filtered基于統計信息可能不準確。統計信息過舊會導致優化器做出錯誤判斷這時需要ANALYZE TABLE來更新。不考慮緩存EXPLAIN不顯示查詢是否從緩沖池Buffer Pool中讀取數據而緩存對實際性能影響巨大。不執行觸發器/存儲過程它只分析SELECT語句本身的執行路徑。對于復雜查詢計劃可能不唯一數據庫的優化器可能因為數據變化、參數變化而選擇不同的計劃這就是“執行計劃抖動”。因此最佳實踐是使用EXPLAIN進行初步分析和索引設計。在測試環境使用EXPLAIN ANALYZE對真實數據或模擬的真實數據量進行驗證獲取真實的性能數據。結合慢查詢日志Slow Query Log和性能模式Performance Schema來監控生產環境中查詢的實際表現。5. 工具與可視化讓分析更高效純文本的EXPLAIN輸出對于復雜查詢不夠直觀。很多優秀的數據庫客戶端工具提供了可視化功能。例如DBeaver在運行EXPLAIN后通常會以圖形化的方式展示執行計劃樹讓你一目了然地看到各個操作的先后順序和成本占比。HeidiSQL、MySQL Workbench也都有類似功能。一些云數據庫控制臺如阿里云RDS、騰訊云CDB更是內置了強大的SQL診斷和優化建議功能其底層核心依然是EXPLAIN。關于網絡熱詞“dbeaver explain 顯示的是個統計,沒看到執行計劃”的解答這通常是因為DBeaver默認可能執行的是EXPLAIN FORMATTRADITIONAL表格形式或者在某些版本/配置下對于很簡單的查詢它可能只顯示概要信息。你需要確保在SQL編輯器中正確選中要分析的SQL語句。點擊“執行計劃”按鈕通常是一個帶箭頭的圖表圖標而不是直接執行。或者直接在查詢前手動輸入EXPLAIN或EXPLAIN ANALYZE然后執行在結果面板查看。DBeaver通常會在“執行計劃”標簽頁以圖形和表格兩種形式展示。掌握EXPLAIN就像獲得了數據庫的“X光透視”能力。它不能直接解決性能問題但能精準地告訴你問題出在哪里。所有的優化手段——添加索引、重寫SQL、調整結構——都需要建立在準確診斷的基礎上。下次遇到慢查詢別急著盲目添加索引先靜下心來用EXPLAIN好好看看它的“執行計劃”你會找到那條最高效的優化路徑。 MySQL EXPLAIN執行計劃深度解析：從原理到實戰優化
1. 從一次慢查詢引發的“靈魂拷問”說起那天下午監控系統突然報警一個核心接口的響應時間從平時的幾十毫秒飆升到了十幾秒。團隊立刻進入“戰備”狀態我作為當時的值班工程師第一反應就是去查數據庫。登錄到數據庫管理工具找到那條拖垮接口的SQL它看起來并不復雜就是一個多表關聯查詢附帶幾個篩選條件。直覺告訴我問題出在索引上但具體是哪個環節是全表掃描了還是用錯了索引是關聯順序有問題還是臨時表拖了后腿光靠猜是沒用的這時候EXPLAIN就成了我手中最鋒利的“手術刀”。對于任何與數據庫打交道的開發者、DBA甚至數據分析師來說EXPLAIN都是一個必須掌握的核心技能。它不是什么高深莫測的黑魔法而是數據庫引擎提供的一份“執行計劃說明書”。當你把一條SQL語句交給數據庫時數據庫的優化器會像一位老練的廚師思考如何用最快的速度做出這道菜——是先切配菜過濾數據還是先熱鍋選擇驅動表是用猛火快炒走索引還是需要文火慢燉全表掃描。EXPLAIN就是把這位廚師的“做菜思路”完整地展示給你看。很多人對EXPLAIN的理解停留在“看有沒有走索引”的層面這遠遠不夠。索引只是執行計劃中的一個環節。一份完整的EXPLAIN輸出能告訴你查詢將訪問哪些表、以何種順序訪問、使用何種連接方法、預估需要檢查多少行數據、是否使用了臨時表、是否進行了文件排序等關鍵信息。讀懂它你就能精準定位性能瓶頸是索引缺失、索引失效、統計信息不準還是SQL寫法本身就有優化空間。可以說EXPLAIN是數據庫性能調優的“第一性原理”繞過它去談優化無異于盲人摸象。本文將以MySQL的EXPLAIN為核心其原理和大部分字段與其他如PostgreSQL的EXPLAIN相通帶你徹底拆解這份“執行計劃說明書”。我不會僅僅羅列字段含義而是結合大量真實的調優場景告訴你每個字段背后的“為什么”以及看到異常值時該如何思考和行動。無論你是剛接觸數據庫的新手還是希望深化理解的資深開發者這篇文章都將是你手邊一份詳實的實戰指南。2. 執行計劃的核心字段逐行精解拿到一份EXPLAIN的輸出通常是一個表格每一行代表查詢中的一個操作例如訪問一個表。每一列則描述了該操作的詳細信息。我們常說“讀執行計劃”其實就是解讀這些列的組合含義。下面我們深入到每一個核心字段看看它們到底在說什么。2.1id: 查詢的執行順序與嵌套關系id是執行計劃的“序列號”但它表示的并不是絕對的執行順序而是查詢的“輪次”或“層級”。id相同表示這些操作屬于同一個SELECT執行順序從上到下。通常出現在多表JOIN中數據庫會按照優化器決定的順序依次執行連接。id不同如果是子查詢id序號會遞增。id值越大優先級越高越先執行。這很直觀內層的子查詢需要先計算出結果才能供外層查詢使用。id為NULL這通常出現在UNION結果合并的衍生表unionM,N行。它表示這是一個用于合并結果的臨時操作。實戰經驗看id是理解復雜查詢執行流的第一步。如果看到一個很大的查詢id很多且不同就要警惕嵌套過深的子查詢可能帶來的性能問題考慮能否改寫為JOIN。2.2select_type: 查詢類型的“身份標簽”這一列告訴你當前行對應的是簡單查詢還是復雜查詢中的哪一部分。常見的類型有SIMPLE最簡單的查詢不包含子查詢或UNION。這是你最希望看到的類型。PRIMARY查詢中最外層的SELECT或者在子查詢中位于最外層的SELECT。SUBQUERY在SELECT或WHERE列表中包含了子查詢且該子查詢不依賴于外部查詢。DEPENDENT SUBQUERY同樣是個子查詢但它的結果依賴于外部查詢的字段。這是一個危險信號因為對于外部查詢的每一行這個子查詢都可能要重新執行一次極易導致性能災難。DERIVED來自FROM子句的子查詢派生表。MySQL會將這些子查詢的結果物化成一個臨時表然后對外部查詢進行處理。如果派生表數據量很大創建臨時表的過程會很耗資源。UNIONUNION中的第二個或后續的SELECT。UNION RESULT從UNION臨時表檢索結果的SELECT。避坑指南當你看到DEPENDENT SUBQUERY或DERIVED且涉及大數據集時性能往往不佳。優化的方向通常是嘗試用JOIN重寫查詢或者確保派生表子查詢本身是高效、結果集小的。2.3table: 當前操作的對象這一列顯示當前行正在訪問哪個表。它可能是實際的表名也可能是諸如derivedNid為N的查詢產生的派生表、unionM,NUNION了id為M和N的查詢結果這樣的別名。2.4partitions: 匹配的分區信息如果你的表使用了分區這一列會顯示查詢命中了哪些分區。對于非分區表此列為NULL。這是進行分區裁剪優化的重要觀察點。2.5type: 訪問類型——性能的“生死線”這是EXPLAIN中最關鍵的列之一它顯示了數據庫決定如何查找表中的行。從最優到最差常見的類型排列大致如下systemconsteq_refrefrangeindexALLsystem/const性能最優。system是const的特例表里只有一行數據。const表示通過主鍵或唯一索引進行等值查詢最多返回一行。因為結果確定所以速度極快。EXPLAIN SELECT * FROM users WHERE id 1; -- type 很可能是 const因為 id 是主鍵。eq_ref在多表連接時對于前一個表的每一行在當前表中只找到唯一的一行與之匹配。通常出現在使用主鍵或非空唯一索引進行關聯查詢時。這是性能最好的連接類型之一。EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id u.id; -- 如果 u.id 是主鍵對于 orders 表的每一行在 users 表中通過主鍵查找一行type 就是 eq_ref。ref比eq_ref稍差表示使用非唯一索引進行等值查找可能會返回多行。如果匹配的行數很少性能依然很好。EXPLAIN SELECT * FROM users WHERE email userexample.com; -- 如果 email 字段上有普通索引type 就是 ref。range使用索引檢索給定范圍的行常見于BETWEEN、、、IN()、LIKE ‘prefix%’注意前綴匹配等操作。EXPLAIN SELECT * FROM orders WHERE create_time BETWEEN 2023-01-01 AND 2023-01-31; -- 如果 create_time 有索引type 就是 range。index全索引掃描。它遍歷整個索引樹來獲取數據雖然避免了全表掃描但通常也需要讀取大量的索引條目。當查詢的列全部包含在某個索引中覆蓋索引且需要讀取大部分索引條目時可能會走index。EXPLAIN SELECT id FROM users; -- id 是主鍵也是一種索引這條查詢只取 id 列可能會走 index 掃描主鍵索引。ALL全表掃描。性能最差意味著數據庫需要逐行檢查表中的所有數據來找到匹配的行。當表數據量很大時這將是災難性的。核心心法優化type列是索引優化的首要目標。我們的核心戰斗就是盡可能避免ALL減少index爭取達到range、ref在關聯查詢中追求eq_ref。2.6possible_keys與key: 可能用與實際用的索引possible_keys查詢可能使用到的索引。這是一個理論值由優化器根據WHERE、JOIN、ORDER BY等子句中涉及的列計算得出。這一列為NULL并不意味著沒索引可用有時可能因為數據分布等原因優化器認為全表掃描更快。key查詢實際決定使用的索引。如果為NULL則表示沒有使用索引。關鍵洞察如果possible_keys有值而key為NULL這通常是一個強烈的警告信號。它意味著優化器認為使用索引的成本回表等開銷高于全表掃描。你的索引可能因為函數操作、類型轉換等原因而“失效”了。 你需要仔細檢查SQL語句和索引定義。2.7key_len: 索引使用長度的“顯微鏡”key_len表示查詢中使用的索引字段的最大可能長度字節數。通過這個值你可以判斷索引是否被“充分”使用。計算規則對于定長字段如INT4字節BIGINT8字節DATE3字節直接使用其固定長度。對于變長字段如VARCHAR(N)需要額外考慮長度前綴通常1或2字節和字符集如utf8mb4是4字節/字符。是否為NULL也會占用1字節標識位。實戰意義如果key_len小于索引定義的總長度說明只使用了索引的前綴部分復合索引的最左匹配原則。對比key_len與索引定義長度是驗證復合索引是否高效起作用的絕佳手段。舉例有一個復合索引idx_name_age (name, age)name是VARCHAR(20) utf8mb4age是INT。查詢WHERE name ‘Alice’key_len大約是20*4 1(變長前綴) 1(NULL標識如果可為空)。只用了索引的第一部分。查詢WHERE name ‘Alice’ AND age 25key_len會加上age的4字節說明索引的兩部分都被用到了。2.8ref: 哪些列或常量被用于索引查找這一列顯示與key列指定的索引進行比較的列或常量。它告訴你索引查找是基于什么值進行的。常見形式有const常量、func某個函數的結果、db.table.column其他表的列。在多表關聯中觀察ref列可以幫助你理解連接條件是如何被使用的。2.9rows: 優化器的“預估成本”這是一個估算值表示MySQL認為它必須檢查多少行才能找到所需的行。這個數字基于表的統計信息。它是性能評估的一個核心指標。重要性即使type是ref或range如果rows值非常大比如幾萬、幾十萬也意味著查詢需要處理大量數據可能仍然很慢。這時可能需要更優的索引來減少掃描行數。注意rows是每張表的估算值。對于多表連接總成本是所有表rows值的某種乘積取決于連接類型這個值會急劇放大。所以優化時要重點關注rows最大的那個表驅動表。2.10filtered: 條件過濾的“百分比”這個字段表示存儲引擎返回的數據在經過WHERE條件過濾后剩余行數的百分比。它是一個0到100之間的估算值。rows * filtered / 100可以粗略估算出將與下一張表進行連接的行數。新版MySQL的洞察在MySQL 5.7及以上版本EXPLAIN的輸出默認包含filtered列。它對于理解多表連接的成本特別有用。如果驅動表的filtered值很低比如10%意味著WHERE條件過濾掉了大部分數據這對性能是好事。如果很高比如100%且rows很大則意味著大量數據將流入下一個連接步驟需要警惕。2.11Extra: 額外信息——“魔鬼在細節中”這一列包含MySQL解決查詢的額外信息很多重要的性能線索都藏在這里。下面是一些需要高度關注的“壞消息”Using filesort警告這意味著MySQL無法利用索引完成排序需要額外的排序步驟。它可能會在磁盤上創建臨時文件進行排序當數據量大時非常消耗CPU和內存。看到這個就應該考慮為ORDER BY或GROUP BY的列建立合適的索引。Using temporary嚴重警告這意味著查詢需要創建臨時表來保存中間結果常見于GROUP BY、DISTINCT、UNION等操作。在磁盤上創建臨時表當內存不夠時會帶來巨大的性能開銷。Using index好消息這表示查詢使用了“覆蓋索引”即所需的數據列全部包含在索引中因此無需回表查詢數據行。這是極高的性能優化。Using where表示存儲引擎返回的行需要在服務器層再進行一次WHERE條件過濾。如果type是ALL或index且Using where通常意味著性能不佳。Using join buffer (Block Nested Loop)表示連接查詢使用了連接緩沖區。當被驅動表沒有可用索引時可能會出現這個。這通常意味著連接效率不高需要考慮為被驅動表的連接字段添加索引。3. 實戰演練從執行計劃到優化決策理解了每個字段的含義我們來看如何將它們組合起來解決實際問題。我們模擬一個經典的電商場景orders訂單表 和users用戶表。初始表結構簡化與數據量假設users表100萬用戶主鍵id在email和create_time上有獨立索引。orders表1000萬訂單主鍵id有user_id外鍵索引status狀態字段amount金額字段create_time下單時間字段。場景一查詢某個用戶的所有訂單一個典型的低效查詢EXPLAIN SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC;假設這條SQL執行很慢。我們來看可能出現的執行計劃及分析idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_ididx_user_id5const50100.00Using filesort解讀與優化type: ref使用了user_id索引這是好的開始。rows: 50預估找到約50條該用戶的訂單數據量不大。Extra: Using filesort問題所在雖然通過索引快速找到了用戶的訂單但排序字段create_time沒有包含在idx_user_id索引中。因此MySQL需要將這50條記錄撈出來回表在內存或磁盤上進行一次額外的排序。優化方案建立復合索引(user_id, create_time)。這樣索引本身就能按照user_id等值篩選并且在user_id相同的情況下數據已經按照create_time排序了。優化后的執行計劃Extra列很可能變成Using index condition如果查詢列不全在索引中或NULL如果覆蓋索引Using filesort消失。場景二查詢過去一個月內狀態為“已完成”的訂單并按金額排序EXPLAIN SELECT * FROM orders WHERE status completed AND create_time 2024-04-01 ORDER BY amount DESC LIMIT 100;假設status和create_time上都有獨立索引但查詢依然很慢。idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersALLidx_status, idx_create_timeNULLNULLNULL998000011.11Using where; Using filesort解讀與優化type: ALLkey: NULL災難優化器放棄了所有索引選擇了全表掃描近1000萬行。possible_keys顯示有兩個索引可用但都沒用。為什么因為獨立索引idx_status和idx_create_time各自只能優化一個條件。優化器評估后發現先用status索引篩選出大量“已完成”訂單再過濾時間或者先用時間索引篩選出最近一個月的訂單再過濾狀態其成本大量的回表操作過濾都可能高于直接全表掃描。Using where; Using filesort雪上加霜需要自己過濾還要在巨大的結果集上排序。優化方案建立復合索引(status, create_time, amount)。注意順序第一列status用于等值匹配快速縮小范圍。第二列create_time用于范圍查詢在status相同的條件下create_time是有序的。第三列amount雖然ORDER BY amount無法直接利用索引排序因為create_time是范圍查詢打斷了索引的連續性但將其放入索引可以形成覆蓋索引避免回表同時如果配合LIMIT在內存中排序少量數據也會快很多。 更優的寫法可能是建立(status, create_time)索引并確保status的過濾性足夠好。如果status’completed’的數據仍然很多可能需要考慮分區或更復雜的優化策略。場景三關聯查詢用戶及其訂單信息EXPLAIN SELECT u.name, o.order_no, o.amount FROM users u JOIN orders o ON u.id o.user_id WHERE u.create_time 2024-01-01 ORDER BY o.create_time DESC LIMIT 100;idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEurangePRIMARY,idx_create_timeidx_create_time4NULL20000100.00Using index condition; Using temporary; Using filesort1SIMPLEorefidx_user_ididx_user_id5db.u.id10100.00NULL解讀與優化驅動表是u(users)因為u的type是range使用了時間索引rows預估2萬行。被驅動表o(orders)type是ref使用user_id索引關聯每次關聯預估10行效率尚可。核心問題在Extra驅動表u出現了Using temporary; Using filesort。這是因為我們需要對最終結果按o.create_time排序但驅動表是u排序字段在o表。MySQL需要將連接后的結果集放入臨時表再進行排序非常低效。優化方案這種“排序字段在非驅動表”的問題通常有兩種思路改變驅動表如果先排序再連接成本更低可以嘗試用子查詢。例如SELECT ... FROM (SELECT user_id FROM users WHERE create_time ... ORDER BY id LIMIT 1000) u JOIN orders o ...先限制驅動表數量。使用覆蓋索引優化確保被驅動表的連接和排序能高效完成。這里可以為orders表建立(user_id, create_time)復合索引并讓查詢只選擇索引包含的列覆蓋索引減少回表。重寫查詢有時根據業務邏輯可以調整查詢方式。例如如果業務上更關心“最新訂單對應的用戶”可以反過來以orders為驅動表SELECT ... FROM orders o JOIN users u ... WHERE o.create_time ... ORDER BY o.create_time DESC LIMIT 100并為orders.create_time建立索引。4. 進階EXPLAIN ANALYZE與執行計劃的局限性傳統的EXPLAIN輸出的是優化器預估的執行計劃。而 MySQL 8.0.18 引入的EXPLAIN ANALYZE是一個革命性的工具它會實際執行查詢并返回每個步驟的實際執行時間、實際返回行數等詳細信息。EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 12345 ORDER BY create_time DESC;輸出會是一個樹狀結構包含每個迭代器的實際成本如(cost... rows... actual time... loops...)。actual time是核心它告訴你每個操作實際花了多少時間單位通常是毫秒。EXPLAIN的局限性它是預估的rows和filtered基于統計信息可能不準確。統計信息過舊會導致優化器做出錯誤判斷這時需要ANALYZE TABLE來更新。不考慮緩存EXPLAIN不顯示查詢是否從緩沖池Buffer Pool中讀取數據而緩存對實際性能影響巨大。不執行觸發器/存儲過程它只分析SELECT語句本身的執行路徑。對于復雜查詢計劃可能不唯一數據庫的優化器可能因為數據變化、參數變化而選擇不同的計劃這就是“執行計劃抖動”。因此最佳實踐是使用EXPLAIN進行初步分析和索引設計。在測試環境使用EXPLAIN ANALYZE對真實數據或模擬的真實數據量進行驗證獲取真實的性能數據。結合慢查詢日志Slow Query Log和性能模式Performance Schema來監控生產環境中查詢的實際表現。5. 工具與可視化讓分析更高效純文本的EXPLAIN輸出對于復雜查詢不夠直觀。很多優秀的數據庫客戶端工具提供了可視化功能。例如DBeaver在運行EXPLAIN后通常會以圖形化的方式展示執行計劃樹讓你一目了然地看到各個操作的先后順序和成本占比。HeidiSQL、MySQL Workbench也都有類似功能。一些云數據庫控制臺如阿里云RDS、騰訊云CDB更是內置了強大的SQL診斷和優化建議功能其底層核心依然是EXPLAIN。關于網絡熱詞“dbeaver explain 顯示的是個統計,沒看到執行計劃”的解答這通常是因為DBeaver默認可能執行的是EXPLAIN FORMATTRADITIONAL表格形式或者在某些版本/配置下對于很簡單的查詢它可能只顯示概要信息。你需要確保在SQL編輯器中正確選中要分析的SQL語句。點擊“執行計劃”按鈕通常是一個帶箭頭的圖表圖標而不是直接執行。或者直接在查詢前手動輸入EXPLAIN或EXPLAIN ANALYZE然后執行在結果面板查看。DBeaver通常會在“執行計劃”標簽頁以圖形和表格兩種形式展示。掌握EXPLAIN就像獲得了數據庫的“X光透視”能力。它不能直接解決性能問題但能精準地告訴你問題出在哪里。所有的優化手段——添加索引、重寫SQL、調整結構——都需要建立在準確診斷的基礎上。下次遇到慢查詢別急著盲目添加索引先靜下心來用EXPLAIN好好看看它的“執行計劃”你會找到那條最高效的優化路徑。