---

一、MySQL EXPLAIN ANALYZE 執行計劃指南

MySQL 的 EXPLAIN ANALYZE 是一個強大的查詢分析工具，它提供了比傳統 EXPLAIN 更詳細的查詢執行信息。

主要功能

實際執行性能分析

• 不僅顯示預估的執行計劃，還會實際執行查詢并收集真實的性能數據
• 提供每個操作的實際執行時間、處理行數等精確信息

詳細的執行統計

• 顯示每個步驟的實際成本(actual cost)
• 提供實際處理的行數與估計行數的對比
• 展示每個操作的實際執行時間

性能瓶頸識別

• 快速定位查詢中最耗時的操作
• 識別全表掃描、不必要的排序等性能問題
• 找出索引使用不當的情況

與普通 EXPLAIN 的區別

EXPLAIN 只是分析執行計劃而不實際執行查詢，而 EXPLAIN ANALYZE 會真正執行查詢，因此：

• 提供更準確的性能數據
• 能發現優化器估算錯誤的情況
• 但執行時間會更長，特別是對于復雜查詢

使用場景

查詢優化

• 對比不同索引策略的實際效果
• 驗證查詢重寫是否真正提升性能
• 分析復雜 JOIN 查詢的執行效率

問題診斷

• 排查慢查詢的根本原因
• 識別數據分布不均勻導致的性能問題
• 發現統計信息過時的情況

總結

EXPLAIN ANALYZE 是數據庫性能調優和查詢優化的重要工具，能幫助你從估算轉向基于實際數據的優化決策。通過提供真實的執行統計信息，它讓數據庫性能分析更加準確和可靠。

---

二、EXPLAIN ANALYZE 執行計劃

樣例

-> Sort: <temporary>.Population DESC (actual time=8.306..8.431 rows=125 loops=1)-> Stream results (actual time=0.145..8.033 rows=125 loops=1)-> Nested loop inner join (cost=241.12 rows=205) (actual time=0.141..7.787 rows=125 loops=1)-> Filter: (world.country.Continent = 'Asia') (cost=25.40 rows=34) (actual time=0.064..0.820 rows=51 loops=1)-> Table scan on country (cost=25.40 rows=239) (actual time=0.059..0.359 rows=239 loops=1)-> Filter: (world.city.Population > 1000000) (cost=4.53 rows=6) (actual time=0.030..0.131 rows=2 loops=51)-> Index lookup on city using CountryCode (CountryCode=world.country.`Code`) (cost=4.53 rows=18) (actual time=0.023..0.096 rows=35 loops=51)

分析

我們來詳細解讀這個 MySQL 的 EXPLAIN ANALYZE 執行計劃。

這個執行計劃展示了數據庫為了執行一個查詢而采取的具體步驟、成本估算以及（因為有 actual time）實際的執行時間和處理行數。

首先，根據執行計劃我們可以推斷出原始的 SQL 查詢大致是這樣的：

EXPLAIN ANALYZE
SELECT-- ... some columns from city and country
FROMcity
INNER JOINcountry ON city.CountryCode = country.Code
WHEREcountry.Continent = 'Asia'AND city.Population > 1000000
ORDER BYcity.Population DESC;

---

執行順序解讀

數據庫執行計劃的讀取順序是 從內到外，從上到下。也就是說，縮進最深的步驟最先執行。執行流程如下：

1. 掃描 country 表 (Line 5)
2. 過濾出 Continent = 'Asia' 的國家 (Line 4)
3. 對于每一個亞洲國家，去 city 表中查找對應的城市 (Line 7)
4. 過濾出人口大于一百萬的城市 (Line 6)
5. 將上面兩步（3和4）組合成一個嵌套循環連接 (Line 3)
6. 將連接后的結果進行流式處理 (Line 2)
7. 對最終結果按 Population 降序排序 (Line 1)

---

逐行詳細解釋

第 7 行 (最內層)

-> Index lookup on city using CountryCode (CountryCode=world.country.`Code`) (cost=4.53 rows=18) (actual time=0.023..0.096 rows=35 loops=51)

• 操作: Index lookup on city using CountryCode
  • 含義: 這是嵌套循環的內層操作。數據庫正在使用 city 表上的 CountryCode 索引來查找與外層（country 表）匹配的行。連接條件是 city.CountryCode = country.Code。
• 成本估算: (cost=4.53 rows=18)
  • 含義: 優化器估計每次執行這個查找操作的成本是 4.53，并且估計平均每次能找到 18 行。
• 實際執行: (actual time=0.023..0.096 rows=35 loops=51)
  • actual time=0.023..0.096: 第一次執行此操作耗時 0.023 毫秒，所有執行中最長的一次耗時 0.096 毫秒。
  • rows=35: 實際上平均每次查找返回了 35 行。這說明優化器的估計（18行）偏低了。
  • loops=51: 這個操作被執行了 51 次。這非常關鍵，它告訴我們上一步（過濾國家）產生了 51 行結果。

第 6 行

-> Filter: (world.city.Population > 1000000) (cost=4.53 rows=6) (actual time=0.030..0.131 rows=2 loops=51)

• 操作: Filter: (world.city.Population > 1000000)
  • 含義: 對上一步（Index lookup）返回的城市結果進行過濾，只保留人口 (Population) 大于 1,000,000 的城市。
• 成本估算: (cost=4.53 rows=6)
  • 含義: 優化器估計在找到的城市中，平均有 6 個城市的人口會超過一百萬。
• 實際執行: (actual time=0.030..0.131 rows=2 loops=51)
  • rows=2: 實際上平均每次只有 2 個城市滿足人口條件。
  • loops=51: 這個過濾操作同樣被執行了 51 次，與上一步的循環次數一致。

第 5 行

-> Table scan on country (cost=25.40 rows=239) (actual time=0.059..0.359 rows=239 loops=1)

• 操作: Table scan on country
  • 含義: 這是嵌套循環的外層驅動操作的起點。數據庫正在執行全表掃描，即讀取 country 表中的每一行。
• 成本估算: (cost=25.40 rows=239)
  • 含義: 優化器估計全表掃描的成本是 25.40，并估計 country 表總共有 239 行。
• 實際執行: (actual time=0.059..0.359 rows=239 loops=1)
  • rows=239: 實際上確實掃描了 239 行。
  • loops=1: 這個全表掃描操作只執行了 1 次。

第 4 行

-> Filter: (world.country.Continent = 'Asia') (cost=25.40 rows=34) (actual time=0.064..0.820 rows=51 loops=1)

• 操作: Filter: (world.country.Continent = 'Asia')
  • 含義: 對上一步（全表掃描）的結果進行過濾，只保留 Continent 字段為 ‘Asia’ 的國家。
• 成本估算: (cost=25.40 rows=34)
  • 含義: 優化器估計會有 34 個亞洲國家。
• 實際執行: (actual time=0.064..0.820 rows=51 loops=1)
  • rows=51: 實際上找到了 51 個亞洲國家。這個數字（51）成為了內層循環（Index lookup 和 Filter）的 loops 次數。
  • loops=1: 這個過濾操作也只執行了 1 次。

第 3 行

-> Nested loop inner join (cost=241.12 rows=205) (actual time=0.141..7.787 rows=125 loops=1)

• 操作: Nested loop inner join
  • 含義: 這是一個總結行，表示數據庫使用了嵌套循環連接算法。它將上面兩個分支（過濾后的 country 表和過濾后的 city 表）的結果連接起來。
• 成本估算: (cost=241.12 rows=205)
  • 含義: 優化器估計整個連接操作的總成本是 241.12，最終會產生 205 行結果。
• 實際執行: (actual time=0.141..7.787 rows=125 loops=1)
  • rows=125: 實際上，整個連接操作最終產生了 125 行結果（51個亞洲國家中，總共有125個城市人口超百萬）。
  • loops=1: 整個連接過程作為一個整體，執行了 1 次。

第 2 行

-> Stream results (actual time=0.145..8.033 rows=125 loops=1)

• 操作: Stream results
  • 含義: 這是一個中間步驟，將連接操作產生的 125 行結果以流的形式傳遞給下一個操作（排序）。
• 實際執行: (actual time=... rows=125 loops=1)
  • 它處理了 125 行數據，耗時反映了從接收第一行到傳遞完最后一行的時間。

第 1 行 (最外層)

-> Sort: <temporary>.Population DESC (actual time=8.306..8.431 rows=125 loops=1)

• 操作: Sort: <temporary>.Population DESC
  • 含義: 這是查詢的最后一步。數據庫對前序步驟傳來的 125 行結果，按照 Population 字段進行降序排序 (DESC)。
  • <temporary>: 這個標記意味著 MySQL 需要使用一個臨時表（可能在內存或磁盤上）來完成排序操作。這通常發生在 ORDER BY 的字段沒有可用索引時。
• 實際執行: (actual time=8.306..8.431 rows=125 loops=1)
  • actual time: 從開始接收數據到排序完成并輸出最后一行，總共耗時約 8.431 毫秒。這是整個查詢的主要耗時部分。
  • rows=125: 排序了 125 行數據。

---

總結與性能分析

1. 連接策略: 查詢使用了 Nested Loop Join。對于外層結果集不大（51行）的情況，這是一個合理的選擇。
2. 外層掃描: 對 country 表進行了全表掃描。由于該表只有 239 行，這幾乎沒有性能影響。如果 country 表非常大，那么在 Continent 字段上建立索引將是首要的優化點。
3. 內層查找: 對 city 表的查找使用了 CountryCode 索引，這是非常高效的。
4. 排序: 查詢的最后一步是排序，并且使用了臨時表。這是因為結果集是動態生成的，無法利用現有索引來避免排序。這是查詢總耗時的主要來源。
5. 優化器估算: 優化器在行數估算上存在一些偏差（如亞洲國家34 vs 51，每個國家的城市數18 vs 35），但這些偏差沒有導致選擇錯誤的執行計劃。
6. 潛在優化: 如果要進一步優化，可以考慮在 city 表上創建一個復合索引 (CountryCode, Population)。這樣數據庫可以在索引層面就完成對 Population > 1000000 的過濾，減少從磁盤讀取的數據頁，可能會略微提升性能。

文章如有問題，請彥祖幫忙指正！感激不盡！

---