原文地址：https://hackmysql.com/post/book-2/

原文作者：Hack MySQL

發(fā)布時(shí)間：2022/2/27

從 MySQL 8.0.18 開始，EXPLAIN ANALYZE 是了解查詢執(zhí)行不可或缺的工具，因?yàn)樗ㄟ^測(cè)量查詢執(zhí)行計(jì)劃的每個(gè)步驟來分解響應(yīng)時(shí)間的查詢執(zhí)行階段。 這些信息很有啟發(fā)性，但輸出并不直觀：它需要練習(xí)并了解 MySQL 如何執(zhí)行超出傳統(tǒng) EXPLAIN 輸出顯示的表連接順序的查詢。 這篇博文仔細(xì)研究了 EXPLAIN ANALYZE 輸出的三個(gè)不同示例。

這篇博文是十一篇文章中的第三篇：一篇作為我的書 [Efficient MySQL Performance](https://oreil.ly/efficient-mysql-performance 的序言，每章十篇。完整列表是 tags/efficient-mysql-performance。

背景和術(shù)語

Norvald Ryeng 的“MySQL 8.0 EXPLAIN ANALYZE”的幻燈片列舉并說明了 MySQL 查詢執(zhí)行器在 8.0 中的變化：

在 MySQL 8.0 之前，在查詢執(zhí)行計(jì)劃中檢測(cè)步驟是不可行的，因?yàn)榇a是分散的和異構(gòu)的。從 MySQL 8.0 開始，在查詢執(zhí)行計(jì)劃中檢測(cè)步驟是微不足道的，因?yàn)槊總€(gè)步驟本質(zhì)上都是一樣的：一個(gè)迭代器。由于迭代器是一個(gè)接口，因此可以透明地將每個(gè)真正的迭代器包裝在一個(gè)定時(shí)迭代器（sql/timing_iterator.h）中：

template <class RealIterator>
bool TimingIterator<RealIterator>::Init() {
  ++m_num_init_calls;
  steady_clock::time_point start = now();     /* start time    */
  bool err = m_iterator.Init();               /* real iterator */
  steady_clock::time_point end = now();       /* end time      */
  m_time_spent_in_first_row += end - start;
  m_first_row = true;
  return err;
}

TimingIterator這是包裝和測(cè)量真實(shí)迭代器的掛鐘時(shí)間的類的一部分（我添加的評(píng)論）。在執(zhí)行查詢時(shí)，MySQL 直接執(zhí)行真正的迭代器。對(duì)于EXPLAIN ANALYZE，MySQL 將每個(gè)真正的迭代器包裝在 a 中TimingIterator（并丟棄結(jié)果集）。

迭代器接口有兩個(gè)方法：Init()和Read()。“循環(huán)”是迭代器的一次調(diào)用：調(diào)用Init(), 然后Read()直到?jīng)]有更多行。 EXPLAIN ANALYZE打印每個(gè)迭代器的測(cè)量值，例如：

(actual time=0.106..9.991 rows=2844 loops=2)

0.106

Init time: average time (in milliseconds) for Init() and first row (first Read())

9.991

Read time: average time (in milliseconds) for Init() and all rows (all Read())

rows=2844

Total number of rows read (all loops)

loops=2

Number of calls to Init() (number of times iterator invoked)

第一個(gè)時(shí)間值，我稱之為“初始化時(shí)間”，被認(rèn)為是啟動(dòng)成本，它通常非常低，但它取決于迭代器——下一節(jié)，示例 1：文件排序，就是一個(gè)例子。

第二個(gè)時(shí)間值，我稱之為“讀取時(shí)間”，乘以loops計(jì)算時(shí)最有用迭代器時(shí)間：迭代器讀取行所花費(fèi)的總時(shí)間（以毫秒為單位）。在本例中，9.991 ms × 2 = 19.982 ms 迭代器時(shí)間。

When loops=1, read time 和 iterator time 是一樣的。

init time、read time和iterator time 是我定義的術(shù)語，不是 MySQL 官方術(shù)語，因?yàn)?MySQL 沒有為這些值指定簡(jiǎn)潔的名稱。

一般來說，迭代器時(shí)間是從葉子到根的累積時(shí)間。

-> A (200ms loops=1)
   -> B (185ms loops=1)
      -> C (90ms loops=2)

給定上面的偽迭代器樹，葉子迭代器 C 需要 180 毫秒（90 × 2 循環(huán)）。由于迭代器 B 調(diào)用迭代器 C，迭代器的時(shí)間 B（不包括迭代器 C）為 5 毫秒（185 毫秒 - 180 毫秒）。同樣，迭代器A調(diào)用迭代器 B，因此前者的時(shí)間為 15 毫秒（200 毫秒 - 185 毫秒）。這很重要，因?yàn)椴樵兓ㄙM(fèi)了 200 毫秒，而不是報(bào)告時(shí)間的總和（565 毫秒）。這通常是正確的，但并非總是如此——MySQL 總是有這樣有趣的小驚喜。

有關(guān)更多技術(shù)細(xì)節(jié)，請(qǐng)閱讀：

• Volcano—— Goetz Graefe的可擴(kuò)展和并行查詢?cè)u(píng)估系統(tǒng)
• MySQL WL#11785：Volcano 迭代器設(shè)計(jì)
• MySQL WL#12074：Volcano 迭代器執(zhí)行器基礎(chǔ)

輸出格式和注釋

EXPLAIN ANALYZE輸出并非易讀（瑣碎的查詢除外）。這是真正的輸出格式：

mysql> EXPLAIN ANALYZE SELECT c FROM sbtest1 WHERE k < 450000 ORDER BY id\G
*************************** 1. row ***************************
EXPLAIN: -> Sort: sbtest1.id  (cost=84245.94 rows=133184) (actual time=1347.876..1357.375 rows=68440 loops=1)
    -> Index range scan on sbtest1 using k_1, with index condition: (sbtest1.k < 450000)  (cost=84245.94 rows=133184) (actual time=41.021..1298.076 rows=68440 loops=1)

1 row in set (1.37 sec)

當(dāng)有許多迭代器時(shí)，輸出是密集且繁瑣的。這不是對(duì) MySQL 的批評(píng)，它只是EXPLAIN輸出在 MySQL CLI/shell 中的工作方式。（MySQL Workbench有一個(gè)“視覺解釋”功能，但我不知道它是否適用EXPLAIN ANALYZE。）

以下是我在以下三個(gè)示例中重新格式化和注釋輸出的方式：

1 -> Sort: sbtest1.id
     (actual time=2043.416 .. 2051.792 rows=68440 loops=1)
0     -> Index range scan on sbtest1 using k_1, with index condition: (sbtest1.k < 450000)
         (actual time=109.302 .. 1996.350 rows=68440 loops=1)

左邊距中的數(shù)字，從 0 開始，按讀取順序?qū)Φ鬟M(jìn)行編號(hào)：如何讀取輸出以遵循行訪問的流程。這很有幫助，因?yàn)檩敵鐾ǔＪ紫葟纳系较伦x取深度，但并非總是如此。我用這些數(shù)字來指代迭代器，比如“索引范圍掃描 (0) 在……期間執(zhí)行”。

此外，為了使輸出更易于閱讀和遵循：

• (cost=…)已移除
• (actual time=…)放在自己的線上
• 41.021…1298.076初始化時(shí)間和讀取時(shí)間有額外的空白：41.021 … 1298.076
• 語法突出顯示使數(shù)字從文本中脫穎而出

現(xiàn)在我們可以檢查和討論一些例子，而不會(huì)太費(fèi)神。

示例 1：文件排序

1 -> Sort: sbtest1.id
     (actual time=2043.416 .. 2051.792 rows=68440 loops=1)
0     -> Index range scan on sbtest1 using k_1, with index condition: (sbtest1.k < 450000)
         (actual time= 109.302 .. 1996.350 rows=68440 loops=1)

在索引和索引的 Efficient MySQL Performance 的第 2 章中，我使用一頁（字面意思是一頁：打印中的第 70 頁）EXPLAIN SELECT c FROM sbtest1 WHERE k < 450000 ORDER BY id 來證明數(shù)據(jù)訪問而不是文件排序是該查詢中最慢的部分。

我在書中解釋了輸出，所以我不會(huì)在這里重復(fù)自己。相反，EXPLAIN ANALYZE在閱讀更復(fù)雜的例子之前，讓我們重用這個(gè)簡(jiǎn)單的例子來更深入地理解。

的輸出與EXPLAIN ANALYZE我們對(duì)查詢執(zhí)行的看法有些相反。對(duì)于這個(gè)查詢和其他類似的查詢，我們有理由認(rèn)為 MySQL 獲取然后對(duì)行進(jìn)行排序（無法對(duì)尚未獲取的行進(jìn)行排序），但輸出顯示不同：index range scan (0) nested (indented) one排序 (1) 中的級(jí)別，看起來像獲取行 — 索引范圍掃描 (0) — 發(fā)生在排序 (1) 之后。但事實(shí)并非如此。左邊距中帶注釋的數(shù)字是行訪問的順序。輸出反映了這些迭代器是如何實(shí)現(xiàn)和調(diào)用的，如 sql/iterators/sorting_iterator.h 中所述：

/**
  An adapter that takes in another RowIterator and produces the same rows,
  just in sorted order. (The actual sort happens in Init().)

正如該評(píng)論所指出的，排序迭代器 (1) 是一個(gè)適配器，它在后臺(tái)調(diào)用另一個(gè)迭代器來獲取行——在本例中為索引范圍掃描 (0)。然后 sort (1) 對(duì)索引范圍掃描 (0) 獲取的行進(jìn)行排序。而 sort (1) 在Init(). sort (1)Read()只是按順序返回已經(jīng)獲取和排序的行，這就是為什么讀取時(shí)間非常接近初始化時(shí)間：它主要是初始化時(shí)間，只有幾毫秒才能返回已排序的 68,440 行。

結(jié)果，排序（1）的初始化時(shí)間和讀取時(shí)間都不是純粹的排序時(shí)間測(cè)量，這是（恕我直言）我們期望從排序迭代器的計(jì)時(shí)中看到的，因?yàn)樗哪康氖桥判颍緵]有編程方式。相反，sort (1) init time 包括索引范圍掃描 (0) 讀取時(shí)間和對(duì)行進(jìn)行排序所花費(fèi)的時(shí)間。

由于索引范圍掃描 (0) 讀取時(shí)間只是獲取行所花費(fèi)的時(shí)間，我們可以從排序 (1) 初始化時(shí)間中減去它來粗略計(jì)算排序行所花費(fèi)的時(shí)間：2043.416 - 1996.350 = 47 ms。

唉，前面提到的意思是我在 Efficient MySQL Performance 的 第 70 頁上的原始解釋是不準(zhǔn)確的。我寫道：“文件排序（第 1 行）在 [2043.416] 毫秒后開始，在 [2051.792] 毫秒后結(jié)束，這意味著文件排序花了 8 毫秒。” 結(jié)論仍然正確：“答案是否定的：filesort 不會(huì)使這個(gè)查詢變慢。問題是數(shù)據(jù)訪問：68,439 行不是一個(gè)小的結(jié)果集。” 但是我得出結(jié)論的方式是不正確的。

這個(gè)故事的寓意是：有時(shí)您需要了解迭代器如何在底層工作（在 MySQL 源代碼中）才能正確解釋報(bào)告的計(jì)時(shí)值。

示例 2：簡(jiǎn)單連接

3 -> Nested loop inner join
     (actual time=0.022..0.036 rows=4 loops=1)
1     -> Filter: (elem.a in ('Ag','Au','At'))
         (actual time=0.015..0.024 rows=4 loops=1)
0         -> Covering index range scan on elem using idx_a_b
             (actual time=0.013..0.022 rows=4 loops=1)
2     -> Single-row index lookup on elem_names using PRIMARY (symbol=elem.a)
         (actual time=0.002..0.002 rows=1 loops=4)

這是EXPLAIN ANALYZE您在本書第 74 頁找到并下載示例的 2-19 的輸出。查詢是：

SELECT name FROM elem JOIN elem_names ON (elem.a=elem_names.symbol) WHERE a IN ('Ag', 'Au', 'At');

這個(gè)例子強(qiáng)調(diào)了你通常如何閱讀 EXPLAIN ANALYZE輸出：深度優(yōu)先。盡管查詢執(zhí)行是基于連接 (3)，但行訪問從 table 上的覆蓋索引掃描 (0) 開始elem。然后為IN()子句過濾行 (1)。匹配行用于查找和連接表中的相應(yīng)行elem_names：主鍵查找（2）。

請(qǐng)注意，loops=1對(duì)于覆蓋索引掃描（0）和過濾器（1）：連接中的第一個(gè)表被訪問一次。但是loops=4對(duì)于主鍵查找 (2)，因?yàn)檫B接表（連接中的第二個(gè)和后續(xù)表）通常會(huì)為前面表中的每一行訪問多次。同樣，過濾器 (1) 匹配rows=4，它對(duì)應(yīng)于主鍵查找 (2) loops=4：第一個(gè)表中的四行導(dǎo)致 MySQL 訪問連接表四次。要了解更多信息，請(qǐng)閱讀 Efficient MySQL Performance 中第 2 章末尾的“表連接算法”部分。

示例 3：Sakila

5 -> Table scan on <temporary>
     (actual time=0.001 .. 0.002 rows=2 loops=1)
4     -> Aggregate using temporary table
         (actual time=26.210 .. 26.210 rows=2 loops=1)
3         -> Nested loop inner join
             (actual time=0.140 .. 20.580 rows=5687 loops=1)
0             -> Table scan on staff
                 (actual time=0.023 .. 0.028 rows=2 loops=1)
2             -> Filter: (payment.payment_date like '2005-08%')
                 (actual time=0.106 .. 9.991 rows=2844 loops=2)
1                 -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)
                     (actual time=0.098 .. 7.099 rows=8024 loops=2)

這是 Norvald Ryeng 在 MySQL EXPLAIN ANALYZE (MySQL Blog Archive) 中使用的示例。該查詢是一個(gè)簡(jiǎn)單的帶有 GROUP BY 子句的兩表連接，這就是為什么存在聚合 (4) 和臨時(shí)表掃描 (5) 的原因：

SELECT first_name, last_name, SUM(amount) AS total
FROM staff INNER JOIN payment ON staff.staff_id=payment.staff_id AND payment_date LIKE '2005-08%'
GROUP BY first_name, last_name;

從表掃描 (0) 到索引查找 (1) 到過濾 (2) 以完成連接 (3) 的行訪問流程與前面的示例類似。

聚合 (4) 是新的：它從連接 (3) 接收和分組行。注意 join (3) rows=5687but aggregate (4) rows=2：查詢只返回 2 個(gè)組（每組一行）。有趣的是，聚合 (4) 的初始化時(shí)間和讀取時(shí)間是相同的，這表明所有工作Init()都已完成——我們必須檢查 MySQL 源代碼來驗(yàn)證。

正如 Norvald 在他的博客文章中指出的那樣，這個(gè)查詢花費(fèi)的大部分時(shí)間不是表掃描 (0)，這有點(diǎn)令人驚訝，因?yàn)榧俣ū頀呙璺浅Ｂ５菍?duì)于這個(gè)查詢，有一個(gè)非常簡(jiǎn)單的解釋：rows=2在表掃描 (0) 上——表只有兩行。因此，97% 的連接 (3) 用于查找和過濾行（分別為索引查找 (1) 和過濾器 (2)）。過濾器 (2) 的時(shí)間包括索引查找 (1) 所花費(fèi)的時(shí)間，過濾器 (2) 的迭代器時(shí)間為 9.991 毫秒 × 2 = 19.982 毫秒，是連接 (3) 迭代器時(shí)間 20.580 毫秒的 97%。表掃描通常很慢，但此示例表明，如果一張表很小，則對(duì)連接表的索引查找可能會(huì)更耗時(shí)。或者換一種說法：如果您type: ALL在 EXPLAIN 計(jì)劃中看到（傳統(tǒng)EXPLAIN輸出），全表掃描有可能不是查詢響應(yīng)時(shí)間中最耗時(shí)的部分。

這個(gè)例子的奇怪部分是臨時(shí)表掃描（5）：它的時(shí)間不包括其他迭代器的時(shí)間，即使它是根迭代器。我無法從 MySQL 源代碼中看出為什么會(huì)這樣，但我認(rèn)為這是因?yàn)榕R時(shí)表掃描 (5) 發(fā)生在聚合 (4) 完全完成之后，幾乎就像臨時(shí)表掃描 (5) 是完全獨(dú)立的不包含和調(diào)用聚合 (4) 的迭代器。

無論如何，重點(diǎn)是：您必須 EXPLAIN ANAZLYE 非常仔細(xì)地閱讀輸出，從相關(guān)（嵌套）迭代器中提取相對(duì)數(shù)字，并根據(jù)對(duì) MySQL 實(shí)際如何執(zhí)行查詢的理解為不符合典型解釋的輸出留出一些余量. 無論這些挑戰(zhàn)如何，都EXPLAIN ANALYZE可以提供對(duì)查詢響應(yīng)時(shí)間的有價(jià)值的低級(jí)洞察。

其他資源

令人驚訝的是，MySQL 社區(qū)幾乎沒有寫過關(guān)于 EXPLAIN ANALYZE 方面的信息,以下是一些討論的其他資源（其中一些已經(jīng)在前面鏈接過）EXPALIN ANALYZE：

• 使用 EXPLAIN ANALYZE 獲取信息（MySQL 手冊(cè)）
• MySQL 解釋分析（MySQL 博客存檔）
• WL#4168: Implement EXPLAIN ANALYZE
• 在 MySQL 8 (Percona) 中使用解釋分析
• MySQL 8.0 EXPLAIN ANALYZE（幻燈片）
• 使用 MySQL EXPLAIN 進(jìn)行查詢優(yōu)化（視頻）
• MySQL 8 查詢性能調(diào)優(yōu)（第 20 章）

除了這些資源和這篇博文之外，我建議您使用自己的查詢場(chǎng)景進(jìn)行練習(xí)。從傳統(tǒng) EXPLAIN的 查看表連接順序開始，然后 EXPLAIN ANALYZE以 一般自上而下、深度優(yōu)先的方式閱讀輸出，同時(shí)考慮表連接順序以幫助指導(dǎo)您。如果你看到一些不太有意義的東西，很可能是輸出的奇怪而不是誤讀。

最后，請(qǐng)記住 EXPLAIN ANALYZE 實(shí)際執(zhí)行查詢（鎖定和其他所有操作），因此不要在活動(dòng)的生產(chǎn)服務(wù)器上運(yùn)行它：使用具有相同數(shù)據(jù)的只讀副本或登臺(tái)服務(wù)器，因?yàn)閿?shù)據(jù)通常是影響查詢執(zhí)行和性能的一個(gè)因素，這是進(jìn)入我將在下個(gè)月(2022 年 3月)寫的本系列的下一篇博客文章的完美過渡。