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