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
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類