原文地址：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中會列舉出來標訪問時候每種可能性，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/35440.html
標籤：MySQL
上一篇：資料庫實驗-資料查詢語言