MySQL 8.0中的 explain analyze（譯）

来源:https://www.cnblogs.com/wy123/archive/2020/05/29/12984774.html
-Advertisement-
Play Games

原文地址：https://mysqlserverteam.com/mysql-explain-analyze/ MySQL 8.0.18剛剛發佈(譯者註：原文發表時間為October 17, 2019)，它包含了一個全新的特性來分析和理解查詢是如何執行的:explain analyze。 expla ...


原文地址：https://mysqlserverteam.com/mysql-explain-analyze/

 

MySQL 8.0.18剛剛發佈(譯者註：原文發表時間為October 17, 2019)，它包含了一個全新的特性來分析和理解查詢是如何執行的:explain analyze。

explain analyze是什麼

EXPLAIN ANALYZE是一個查詢分析工具，它會告訴你MySQL在查詢上花了多少時間以及原因。它將計劃查詢、度量查詢並執行查詢，同時計算行數並測量在執行計劃中不同階段花費的時間。
當執行完成時，EXPLAIN ANALYZE將列印計劃和度量結果，而不是查詢結果。(譯者註：直白地說就是，explain analyze會真是地執行當前的查詢，返回的執行計劃以及代價信息，但是不會返回查詢自身的結果)

這個新特性是在常規的EXPLAIN查詢計劃檢查工具之上構建的，可以看作是先前在MySQL 8.0中添加的explain forat = tree的擴展。
除了普通的explain將列印的查詢計劃和估計成本之外，explain analyze還將輸出執行計劃中單個迭代器的實際成本。

如何使用explain analyze

作為一個示例，我們將使用來自Sakila Sample資料庫的數據和一個查詢，該查詢列出了每個員工在2005年8月完成的工作總量。這個問題很簡單:：

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;

+------------+-----------+----------+
| first_name | last_name | total    |
+------------+-----------+----------+
| Mike       | Hillyer   | 11853.65 |
| Jon        | Stephens  | 12218.48 |
+------------+-----------+----------+
2 rows in set (0,02 sec)

只有兩個人，Mike和Jon，我們在2005年8月得到了他們每個人的總數，EXPLAIN FORMAT=TREE 將會顯示執行計劃和成本信息

 1 EXPLAIN FORMAT=TREE
 2 SELECT first_name, last_name, SUM(amount) AS total
 3 FROM staff INNER JOIN payment
 4   ON staff.staff_id = payment.staff_id
 5      AND
 6      payment_date LIKE '2005-08%'
 7 GROUP BY first_name, last_name;
 8 
 9 -> Table scan on <temporary>10     -> Aggregate using temporary table
11         -> Nested loop inner join  (cost=1757.30 rows=1787)
12             -> Table scan on staff  (cost=3.20 rows=2)
13             -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894)
14                 -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043)

但是它沒有告訴我們這些估計是否正確，或者查詢計劃中的哪些操作實際花費了時間。 EXPLAIN ANALYZE可以做到這一點:

 1 EXPLAIN ANALYZE
 2 SELECT first_name, last_name, SUM(amount) AS total
 3 FROM staff INNER JOIN payment
 4   ON staff.staff_id = payment.staff_id
 5      AND
 6      payment_date LIKE '2005-08%'
 7 GROUP BY first_name, last_name;
 8 
 9 -> Table scan on <temporary>  (actual time=0.001..0.001 rows=2 loops=1)
10     -> Aggregate using temporary table  (actual time=58.104..58.104 rows=2 loops=1)
11         -> Nested loop inner join  (cost=1757.30 rows=1787) (actual time=0.816..46.135 rows=5687 loops=1)
12             -> Table scan on staff  (cost=3.20 rows=2) (actual time=0.047..0.051 rows=2 loops=1)
13             -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894) (actual time=0.464..22.767 rows=2844 loops=2)
14                 -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043) (actual time=0.450..19.988 rows=8024 loops=2)

這裡有一些新的衡量方法:

  • 獲取第一行的實際時間(毫秒)
  • 獲取所有行的實際時間(以毫秒為單位)
  • 讀取的實際行數
  • 實際迴圈次數

讓我們看一個具體的例子，篩選迭代器的成本估計和實際度量，篩選迭代器選擇了2005年8月的銷售(上面的EXPLAIN ANALYZE輸出中的第13行)。

Filter: (payment.payment_date like '2005-08%')
(cost=117.43 rows=894)
(actual time=0.464..22.767 rows=2844 loops=2)

過濾器的估計成本為117.43，估計返回894行，這些估計是查詢優化器在執行查詢之前根據可用的統計信息做出的。該信息也以EXPLAIN FORMAT=TREE輸出的形式出現。
從迴圈數開，此篩選迭代器的迴圈次數為2。這是什麼意思?要理解這個數字，我們必須查看查詢計劃中過濾迭代器上面的內容。
在第11行有一個嵌套迴圈聯接，在第12行有一個對staff表的表掃描。
這意味著我們正在執行一個嵌套迴圈聯接，其中我們掃描staff 表，對於該表中的每一行，我們使用索引查找和對付款日期進行篩選來查找付款表中相應的行。
因為staff表中有兩行(Mike和Jon)，我們對過濾和第14行上的索引查找進行了兩次迴圈迭代。
對於很多人來說，EXPLAIN ANALYZE提供的一個有趣的信息是實際消耗時間，“0.464..22.767”，
這意味著讀取第一行平均需要0.464 ms，讀取所有行平均需要22.767 ms。
是平均值嗎?是的，因為迴圈，我們必須對迭代器計時兩次，報告的數字是所有迴圈迭代的平均值。
這意味著過濾的實際執行時間是這些數字的兩倍，因此，如果我們查看在嵌套迴圈迭代器(第11行)中接收所有行所需的時間，它是46.135 ms，比一次運行過濾迭代器所需的時間多一倍多。

譯者註：

這裡的時間成本計算規律就是，每一步的執行時間，是包含了其子步驟的執行時間的之和，這幾個步驟的時間包含關係是這樣的：
Nested loop inner join這一層總的時間是58.104ms，也就是整各join的時間成本，包含了
“Table scan on staff表” 和 “payment表上的Filter的時間”
filter的時間又包含了：“index lookup”+“where條件filter條件”的時間，其中最耗時的就是index lookup這一步，也即數據查詢的過程。
Index lookup 這一步的時間是19.988*2，乘以2意思是兩次迴圈迭代，因此整個loop join過程的時間大部分都耗費在這個index lookup這個查找上，
平均每次（兩次）Filter（22.767）= payment_date like '2005-08%'的篩選 + Index lookup on payment 查找（19.988）

實際讀取的行數為2844，而估計值為894行。優化器漏掉了一個因數3（譯者註：這一句話不太明白是什麼意思，漏掉了什麼）。
同樣，由於迴圈的原因，估計的和實際的數字都是所有迴圈迭代的平均值。
如果我們查看表結構，payment_date列上沒有索引或直方圖，因此提供給優化器用於計算篩選器選擇性的統計信息是有限的。
對於更好的統計信息會產生更準確的估計的示例，我們可以再次查看索引查找迭代器。我們看到索引提供了更精確的統計數據:8043行與8024行實際讀取的比較。
這很好，出現這種情況是因為索引附帶了額外的統計信息，而非索引列則沒有。

那麼你能利用這些信息做些什麼呢?分析查詢並理解為什麼它們執行得不好需要一些實踐。但一些簡單的提示，讓你開始:

  • 如果你想知道為什麼花了這麼長時間，看看時間，執行的時候時間都花費在哪一步?
  • 如果您想知道為什麼優化器選擇了該計劃，請查看行計數器。估算的行數與實際的行數之間有很大的差異（即幾個數量級或更多），這表明您應該仔細看一下。
    優化器根據估算值選擇計劃，但是查看實際執行情況可能會告訴您，另一個計劃會更好。

如果您想知道優化器為什麼選擇該計劃，請查看行計數器。巨大的差異。在估計的行數和實際行數之間的幾個數量級或更多)是一個標誌，表明您應該更仔細地查看它。
優化器根據估計值選擇計劃，但是查看實際執行情況可能會告訴您另一個計劃會更好。

就是這樣！MySQL查詢分析工具箱中的另一個工具: 

  • 要檢查查詢計劃：EXPLAIN FORMAT=TREE
  • 要跟蹤查詢執行：EXPLAIN ANALYZE
  • 要理解計劃選擇：Optimizer trace

我希望您喜歡這個新特性的快速瀏覽，解釋分析將幫助您分析和理解慢速查詢。

 

 

 

譯者補充：

關於MySQL執行計劃的幾種展示方式，explain/explain format=tree/explain format=json/optimizer_trace
其實本質上都是一樣的，只是詳細程度不一樣，對於explain analyze同時可以顯式預估的+實際執行的信息，以下是將譯文中使用的示例資料庫導入到本地後，展示出來的一些信息，與上文中的信息稍有差異。
1，explain
最簡潔或者粗略的執行計劃顯式方式，可以顯式：表的訪問方式、表之間的驅動順序，以及Extra列中的其他信息，包括是否產生排序，使用臨時表空間等等。
2，expalin format = tree
與explain analyze類似，同時包含了以預估的每一步的代價信息，僅僅是預估信息，並不包含實際執行信息

1 -> Table scan on <temporary>
2     -> Aggregate using temporary table
3         -> Nested loop inner join  (cost=1757.30 rows=1787)
4             -> Table scan on staff  (cost=3.20 rows=2)
5             -> Filter: (payment.payment_date like '2005-08%')  (cost=117.43 rows=894)
6                 -> Index lookup on payment using idx_fk_staff_id (staff_id=staff.staff_id)  (cost=117.43 rows=8043)

3，explain format = json
以json的格式顯式與expalin format = tree的信息類似，說實話，可讀性並不入expalin format = tree

 1 {
 2   "query_block": {
 3     "select_id": 1,
 4     "cost_info": {
 5       "query_cost": "1757.30"
 6     },
 7     "grouping_operation": {
 8       "using_temporary_table": true,
 9       "using_filesort": false,
10       "nested_loop": [
11         {
12           "table": {
13             "table_name": "staff",
14             "access_type": "ALL",
15             "possible_keys": [
16               "PRIMARY"
17             ],
18             "rows_examined_per_scan": 2,
19             "rows_produced_per_join": 2,
20             "filtered": "100.00",
21             "cost_info": {
22               "read_cost": "3.00",
23               "eval_cost": "0.20",
24               "prefix_cost": "3.20",
25               "data_read_per_join": "1K"
26             },
27             "used_columns": [
28               "staff_id",
29               "first_name",
30               "last_name"
31             ]
32           }
33         },
34         {
35           "table": {
36             "table_name": "payment",
37             "access_type": "ref",
38             "possible_keys": [
39               "idx_fk_staff_id"
40             ],
41             "key": "idx_fk_staff_id",
42             "used_key_parts": [
43               "staff_id"
44             ],
45             "key_length": "1",
46             "ref": [
47               "sakila.staff.staff_id"
48             ],
49             "rows_examined_per_scan": 8043,
50             "rows_produced_per_join": 1787,
51             "filtered": "11.11",
52             "cost_info": {
53               "read_cost": "145.50",
54               "eval_cost": "178.72",
55               "prefix_cost": "1757.30",
56               "data_read_per_join": "41K"
57             },
58             "used_columns": [
59               "payment_id",
60               "staff_id",
61               "amount",
62               "payment_date"
63             ],
64             "attached_condition": "(`sakila`.`payment`.`payment_date` like '2005-08%')"
65           }
66         }
67       ]
68     }
69   }
70 }

4，trace
set session optimizer_trace='enabled=ON';
explain sql
其實這些信息，都是跟explain format = json或者說explain analyze中，預估部分的一致的，這些數據都跟expalin format = tree一致，只不過trace中會枚舉出來標訪問時候每種可能性。

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 伺服器開發 Ubuntu
    一、Ubuntu安裝： 為什麼用Ubuntu，作為伺服器初學者開發，如果真的要買蘋果系統電腦性價比不高，所以在window系統中安裝Linux虛擬機是不二之選。為什麼用Ubuntu不多說了，開始安裝吧。 以window 10 為例： 1）點擊控制面板->搜索"開發者設置"->設置開發者模式 2）點擊 ...
  • 如何高效回退到特定層級目錄？
    Linux 下如果我們進入到了一個比較長的路徑，比如： /home/alvin/projects/blogdemos/linux-system-programming/thread /home/alvin/projects/blogdemos/diff /home/harry/study/亞洲文化/ ...
  • tr命令
    linux命令tr: (Translate/ Text Replacer)：可以對來自標準輸入的內容進行字元替換，字元刪除，以及重覆字元壓縮。 ***只從stdin標準輸入接收數據： 格式：tr [options] set1 set2 將輸入按照位置從set1映射到set2，然後將輸出寫到stdou ...
  • Chronyd同步時間（Server/Client）配置
    chronyd時間伺服器，和之前NTP的功能一樣，提供時間的。基礎概念我就不說了，不擅長。chronyd是NTP之後誕生的。最近下載了最新的Centos Linux 8.1，發現裡面沒有ntp服務了，百度一搜發現改為了Chronyd，這篇文章就是部署一個伺服器+客戶端測試。1、伺服器端Centos ... ...
  • 安卓到底是不是Linux
    有人說安卓就是 Linux，也有人說安卓是安卓、Linux 是 Linux，兩者沒什麼關係，還有人說安卓用的是 Linux 的內核，那麼它們之間到底是什麼關係呢。要想得到這個問題的答案，我們要先弄清楚幾個概念。 Linux 與 Linux 內核 Linux 與 Linux 內核其實是不一樣的，關於這 ...
  • 詳細講解docker容器和鏡像的區別
    當我對Docker技術還是一知半解的時候，我發現理解Docker的命令非常困難。於是，我花了幾周的時間來學習Docker的工作原理，更確切地說，是關於Docker統一文件系統（the union file system）的知識，然後回過頭來再看Docker的命令，一切變得順理成章，簡單極了。 題外話 ...
  • Ubuntu下用腳本執行sql語句（sqlite）
    1、環境準備，安裝sqlite3軟體 sudo apt-get install sqlite3 2、編寫sql執行腳本（更新操作），每條sql語句以分號結束，sql.sql文件內容： ALTER TABLE "GameDeviceInfo" ADD "Delay" INTEGER NOT NULL ...
  • python/pandas數據挖掘（十四）-groupby,聚合，分組級運算
    https://blog.csdn.net/youngbit007/article/details/54288603 groupbyimport pandas as pddf = pd.DataFrame({'key1':list('aabba'), 'key2': ['one','two','on ...
一周排行
    -Advertisement-
    Play Games
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類