mysql的explain命令是數(shù)據(jù)庫性能調(diào)優(yōu)的關(guān)鍵工具，1. 它通過顯示查詢執(zhí)行計劃幫助識別性能瓶頸；2. 核心字段包括type、rows和extra，分別反映數(shù)據(jù)訪問方式、預估掃描行數(shù)和額外操作；3. 全表掃描（type=all）、高rows值、using filesort和using temporary等是常見性能問題信號；4. 優(yōu)化策略包括添加或調(diào)整索引、重寫查詢、優(yōu)化join、限制結(jié)果集、調(diào)整表結(jié)構(gòu)和配置參數(shù)；5. 使用explain進行持續(xù)迭代分析是優(yōu)化過程的重要環(huán)節(jié)。

MySQL的EXPLAIN命令，在我看來，簡直是數(shù)據(jù)庫性能調(diào)優(yōu)的“X光機”。它不會直接告訴你哪里病了，但能清晰地展現(xiàn)出查詢語句在執(zhí)行時的“骨骼結(jié)構(gòu)”和“血液循環(huán)”狀況。通過解讀它的輸出，我們就能洞察到查詢的真實執(zhí)行路徑，發(fā)現(xiàn)潛在的性能瓶頸，比如索引是否被有效利用、數(shù)據(jù)掃描量是否過大、以及MySQL是否在默默地做一些耗時的額外操作。掌握EXPLAIN，是每個數(shù)據(jù)庫開發(fā)者和運維人員必備的實戰(zhàn)技能。

解決方案

要開始使用EXPLAIN，你只需要在任何SELECT語句前加上它。例如：

EXPLAIN SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2023-01-01'
ORDER BY o.total_amount DESC;

執(zhí)行后，你會得到一個表格，里面包含了MySQL對這條查詢的執(zhí)行計劃。這個表格的每一行代表了查詢中的一個操作（比如訪問一張表），而每一列則提供了關(guān)于這個操作的詳細信息。

你需要重點關(guān)注的幾個核心輸出字段包括：id、select_type、table、type、possible_keys、key、EXPLAIN0、EXPLAIN1、EXPLAIN2、EXPLAIN3和EXPLAIN4。在我個人的經(jīng)驗里，type、EXPLAIN2和EXPLAIN4這三個字段，往往是揭示性能問題的關(guān)鍵線索。type告訴你MySQL是如何訪問數(shù)據(jù)的（是全表掃描、索引掃描還是精確查找），EXPLAIN2預估了MySQL需要讀取多少行數(shù)據(jù)才能找到結(jié)果，而EXPLAIN4則會顯示一些不那么明顯的、但可能非常耗時的操作，比如文件排序（EXPLAIN1）或創(chuàng)建臨時表（EXPLAIN2）。

我的做法通常是：

1. 找到那些執(zhí)行緩慢、資源消耗高的查詢語句。
2. 對這些查詢執(zhí)行EXPLAIN。
3. 逐行、逐列地分析輸出結(jié)果，特別是type、EXPLAIN2和EXPLAIN4。
4. 根據(jù)分析結(jié)果，判斷問題出在哪里：是索引缺失、索引失效、數(shù)據(jù)量過大、查詢邏輯復雜，還是MySQL內(nèi)部操作效率低下。
5. 針對性地制定優(yōu)化方案，比如添加或調(diào)整索引、重寫查詢、調(diào)整表結(jié)構(gòu)，甚至考慮數(shù)據(jù)庫配置參數(shù)。
6. 優(yōu)化后，再次運行EXPLAIN，確認優(yōu)化效果，看看執(zhí)行計劃是否變得更高效了。這個迭代過程非常重要，有時候一個優(yōu)化會帶來新的問題，或者效果不如預期，需要反復嘗試。

如何通過EXPLAIN識別常見的性能瓶頸？

在我與MySQL打交道的這些年里，EXPLAIN總是能幫我揪出那些藏在深處的性能“搗蛋鬼”。識別它們，其實有幾個非常直觀的信號。

最顯而易見的，就是type列顯示為SELECT0。這幾乎是在大聲告訴你：“我正在進行全表掃描！”尤其是在數(shù)據(jù)量大的表上，SELECT0意味著MySQL不得不逐行檢查表中的所有記錄，效率可想而知有多低。即使是SELECT2類型，雖然它利用了索引，但卻是全索引掃描，如果索引很大，性能也未必理想。理想情況我們希望看到的是EXPLAIN1、SELECT4、SELECT5或SELECT6，這些都代表了更高效的索引使用方式。

再來看EXPLAIN2列，這個數(shù)字是MySQL估計為了找到所需數(shù)據(jù)而需要讀取的行數(shù)。一個查詢?nèi)绻?code>EXPLAIN2值異常大，即使type不是SELECT0，也可能意味著查詢的篩選性很差，或者索引沒有被充分利用。我見過一些查詢，雖然走了索引，但EXPLAIN2值依然幾萬幾十萬，那多半是索引不夠精準，或者查詢條件沒有完全覆蓋索引列。

而EXPLAIN4列，簡直就是個“寶藏”，里面藏著很多你肉眼看不到的性能陷阱。最常見的兩個“紅旗”就是EXPLAIN1和EXPLAIN2。

藍心千詢

藍心千詢是vivo推出的一個多功能AI智能助手

34

查看詳情

• EXPLAIN1：這表示MySQL無法利用索引來完成排序操作（id6或id7），而是在內(nèi)存中或磁盤上進行了一次額外的文件排序。磁盤排序尤其慢，是嚴重的性能瓶頸。
• EXPLAIN2：這通常意味著MySQL需要創(chuàng)建一個內(nèi)部臨時表來處理查詢，比如id7或select_type0操作無法直接通過索引完成。臨時表可能在內(nèi)存中，也可能在磁盤上，同樣，磁盤上的臨時表會嚴重拖慢查詢速度。

如果key列為select_type2，那就很明確了，查詢根本沒有使用到任何索引。這可能是因為沒有合適的索引，或者現(xiàn)有索引因為某些原因（比如在索引列上使用了函數(shù)，或者數(shù)據(jù)類型不匹配導致隱式轉(zhuǎn)換）失效了。有時候possible_keys有值，但key卻是select_type2，這說明MySQL認為有索引可用，但最終決定不使用，這可能是因為優(yōu)化器認為全表掃描更快（比如表很?。?，或者索引的選擇性太差。

最后，EXPLAIN3這個百分比也很有意思。它表示MySQL掃描了多少行，最終有多少行滿足了條件并返回。如果EXPLAIN3值很低，比如只有10%，而EXPLAIN2很高，那就意味著MySQL掃描了大量數(shù)據(jù)，但大部分都被過濾掉了，這通常暗示著索引可以做得更精確，或者查詢條件可以更優(yōu)化。

EXPLAIN的輸出字段都代表什么，哪些最值得關(guān)注？

EXPLAIN的輸出結(jié)果是一張表格，每一列都有其特定的含義，理解這些含義是解讀查詢計劃的基礎。

• id: 這個是查詢中每個SELECT子句的標識符。如果一個查詢包含子查詢或table2操作，你會看到多個id。通常，id值越大，執(zhí)行的優(yōu)先級越高；如果id值相同，則從上到下順序執(zhí)行。這對于理解復雜查詢的執(zhí)行順序很有幫助。
• select_type: 查詢的類型。常見的有：
  • table7: 簡單的SELECT查詢，不包含table2或子查詢。
  • type0: 最外層的SELECT查詢。
  • type2: SELECT或type4子句中的子查詢。
  • type5: type6子句中的子查詢，MySQL會將其結(jié)果物化為臨時表。
  • table2: table2中的第二個或后續(xù)的SELECT語句。
  • possible_keys0: table2操作的結(jié)果。
• table: 當前操作的表名。
• type: 這個字段在我看來是EXPLAIN輸出中最重要的指標之一，它揭示了MySQL訪問表的方式。從最好到最壞的順序大致是：
  • possible_keys5/SELECT5: 表只有一行或只有匹配的行，例如對主鍵或唯一索引的等值查詢。速度極快。
  • SELECT4: 在possible_keys8操作中，被驅(qū)動表通過主鍵或唯一索引等值匹配。每條來自前一個表的記錄，都只在當前表中找到唯一一條匹配記錄。
  • EXPLAIN1: 非唯一索引掃描。例如，通過非唯一索引查找多個匹配行。
  • SELECT6: 范圍掃描。例如key1或key2。
  • SELECT2: 全索引掃描。遍歷整個索引來查找匹配的行。雖然比SELECT0好，但如果索引很大，依然很慢。
  • SELECT0: 全表掃描。效率最低，應盡量避免。
• possible_keys: MySQL在執(zhí)行查詢時可能選擇使用的索引。這是一個提示，不代表實際會使用。
• key: 這個字段同樣非常關(guān)鍵，它顯示了MySQL實際決定使用的索引。如果這里是select_type2，說明沒有使用任何索引，即使possible_keys有值。
• EXPLAIN0: 使用的索引的字節(jié)長度。理論上，在滿足查詢需求的前提下，EXPLAIN0越短越好，因為它表示MySQL實際掃描了索引的多少部分。
• EXPLAIN1: 顯示與key列一起使用的列或常量。例如，SELECT5表示使用了一個常量值，EXPLAIN05表示使用了某個表的某個列。
• EXPLAIN2: 另一個非常重要的指標，MySQL估計為了找到所需行而需要讀取的行數(shù)。這個值越小越好，它直接反映了查詢的效率。
• EXPLAIN3: MySQL估計返回結(jié)果的百分比。例如，如果EXPLAIN2是1000，EXPLAIN3是10.00，表示MySQL掃描了1000行，但只有100行（10%）滿足條件。這個值越高越好，因為它表示過濾效率高。
• EXPLAIN4: 這個字段往往是優(yōu)化突破口，它提供了額外的信息，揭示了MySQL在執(zhí)行查詢時的一些內(nèi)部操作。
  • EXPLAIN1: 排序無法通過索引完成，需要額外的排序操作。
  • EXPLAIN2: 查詢需要創(chuàng)建臨時表來存儲中間結(jié)果，通常是由于id7或select_type0操作無法利用索引。
  • EXPLAIN15: 覆蓋索引。查詢所需的所有列都包含在索引中，MySQL可以直接從索引中獲取數(shù)據(jù)，無需回表查詢，效率極高。
  • EXPLAIN16: 表明使用了type4子句進行過濾。
  • EXPLAIN18: 當無法有效利用索引進行連接時，MySQL可能會使用連接緩存。
  • EXPLAIN19: 索引條件下推（ICP），MySQL 5.6+ 的優(yōu)化，在存儲引擎層進行部分過濾，減少回表次數(shù)。

結(jié)合EXPLAIN結(jié)果，如何制定實際的優(yōu)化策略？

拿到EXPLAIN的輸出后，下一步就是根據(jù)這些“診斷報告”來制定具體的“治療方案”。這就像醫(yī)生根據(jù)X光片和化驗單來開藥一樣，需要對癥下藥。

首先，索引是永遠的重頭戲。如果type是SELECT0或SELECT2，或者key是select_type2，那么首要任務就是檢查并優(yōu)化索引。

• 為type4、id6、id7以及possible_keys8條件中的列創(chuàng)建復合索引。記住“最左匹配原則”：如果你創(chuàng)建了一個EXPLAIN30的復合索引，那么只有查詢條件從EXPLAIN31開始匹配，索引才能被充分利用。
• 考慮覆蓋索引。如果EXPLAIN4列出現(xiàn)EXPLAIN15，那說明你命中了覆蓋索引，查詢的所有字段都能從索引中直接獲取，無需回表。這是性能極高的狀態(tài)。所以，有時候為了達到覆蓋索引的目的，即使某個字段不用于type4條件，也可能將其加入復合索引的末尾。
• 避免索引失效。這方面有很多“坑”：在索引列上使用函數(shù)（如EXPLAIN35）、隱式類型轉(zhuǎn)換（如字符串列與數(shù)字比較）、EXPLAIN36這種左模糊查詢、或者EXPLAIN37條件兩邊沒有同時使用索引等。這些都會讓索引形同虛設，導致全表掃描。

其次，重寫查詢語句本身也常常能帶來顯著提升。

• *減少不必要的`SELECT `**。只查詢你真正需要的列，特別是當沒有命中覆蓋索引時，這能減少回表的數(shù)據(jù)量。
• 拆分復雜查詢。有時候一個過于復雜的查詢，即使優(yōu)化器很聰明，也難以找到最優(yōu)解。將其拆分成幾個簡單的查詢，然后在應用層進行邏輯組合，反而可能更快。
• 優(yōu)化possible_keys8操作。如果possible_keys8的type是SELECT0或SELECT2，或者EXPLAIN4出現(xiàn)EXPLAIN44，那說明連接效率低下。檢查EXPLAIN45條件是否都有索引，確保連接列的數(shù)據(jù)類型一致。
• 限制結(jié)果集。如果只需要少量數(shù)據(jù)，務必加上EXPLAIN46子句。

再者，調(diào)整表結(jié)構(gòu)也是一種思路，盡管這通常是成本最高的優(yōu)化方式。

• 選擇合適的數(shù)據(jù)類型。用EXPLAIN47就別用EXPLAIN48，能用EXPLAIN49就別用EXPLAIN47。更小的數(shù)據(jù)類型意味著更少的存儲空間，更少的I/O，更快的處理速度。
• 適當?shù)姆捶妒皆O計。在某些讀多寫少的場景下，為了減少possible_keys8操作，可以適當增加一些冗余字段。比如，訂單表里直接冗余客戶名稱，避免每次查詢訂單都要possible_keys8客戶表。
• 垂直或水平分表。當單表數(shù)據(jù)量過大時，可以考慮根據(jù)業(yè)務邏輯進行垂直分表（將不常用的大字段拆分出去）或水平分表（根據(jù)某個規(guī)則將數(shù)據(jù)分散到多張表）。

最后，別忘了MySQL服務器配置。雖然這不直接體現(xiàn)在EXPLAIN里，但它影響著EXPLAIN背后的執(zhí)行效率。例如，EXPLAIN55設置得太小，可能導致大量磁盤I/O；EXPLAIN56、EXPLAIN57等參數(shù)如果設置不當，也會加劇EXPLAIN1和EXPLAIN2的性能問題。這些是系統(tǒng)層面的優(yōu)化，需要結(jié)合具體的硬件資源和業(yè)務負載來調(diào)整。

在我看來，性能優(yōu)化是一個持續(xù)迭代的過程，沒有一勞永逸的方案。每次優(yōu)化后，都應該重新EXPLAIN，觀察變化，就像一個偵探，不斷尋找線索，直到找到那個最佳的執(zhí)行計劃。

以上就是MySQL查詢計劃EXPLAIN解讀_MySQL性能優(yōu)化實戰(zhàn)方法的詳細內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！