遇到慢查詢怎麼辦？一文解讀MySQL 8.0查詢分析工具

华为云开发者联盟發表於2024-10-31

本文分享自華為雲社群《【華為雲MySQL技術專欄】MySQL 8.0 EXPLAIN ANALYZE 工具介紹》，作者：GaussDB 資料庫。

1. EXPLAIN ANALYZE可以解決什麼問題

MySQL 8.0.18 版本開始支援查詢分析工具EXPLAIN ANALYZE，該工具不僅會實際執行SQL語句，還會展示SQL語句詳細的執行資訊，包含執行運算元（Iterator）粒度的掃描行數、執行耗時、迭代次數等資訊。

EXPLAIN ANALYZE工具是MySQL EXPLAIN FORMAT=TREE 功能的擴充套件，除了展示執行計劃和代價估算之外，還提供了細粒度執行運算元的耗時等資訊。這使得DBA和開發人員能夠基於代價估算和運算元實際執行耗時資訊，判斷執行計劃是否合理，並識別出後續的最佳化點。

2. EXPLAIN ANALYZE如何使用

以TPC-H基準測試中的Q14 查詢為例，該SQL為兩個表的連線及GROUP BY聚合操作，用於統計發貨日誌在1996年1月的促銷商品收入佔比。

select
	100.00 * sum(case
		when p_type like 'PROMO%'
			then l_extendedprice * (1 - l_discount)
		else 0
	end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue
from
	lineitem,
	part
where
	l_partkey = p_partkey
	and l_shipdate >= '1996-01-01'
	and l_shipdate < date_add( '1996-01-01', interval '1' month);

透過EXPLAIN FORMAT=TREE語句，可以看出執行計劃和代價估算資訊：

-> Aggregate: sum((lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT))), sum((case when (part.P_TYPE like 'PROMO%') then (lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT)) else 0 end))
  -> Nested loop inner join (cost=83997.65 rows=66041)
      -> Filter: ((lineitem.L_SHIPDATE >= DATE'1996-01-01') and (lineitem.L_SHIPDATE < <cache>(('1996-01-01' + interval '1' month)))) (cost=60883.30 rows=66041)
          -> Table scan on lineitem (cost=60883.30 rows=594488)
      -> Single-row index lookup on part using PRIMARY (P_PARTKEY=lineitem.L_PARTKEY) (cost=0.25 rows=1)

透過EXPLAIN ANALYZE語句，可以看出每個運算元詳細的執行資訊，如下:

-> Aggregate: sum((lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT))), sum((case when (part.P_TYPE like 'PROMO%') then (lineitem.L_EXTENDEDPRICE * (1 - lineitem.L_DISCOUNT)) else 0 end)) (actual time=203.753..203.753 rows=1 loops=1)
  -> Nested loop inner join (cost=83997.65 rows=66041) (actual time=0.056..200.386 rows=7884 loops=1)
      -> Filter: ((lineitem.L_SHIPDATE >= DATE'1996-01-01') and (lineitem.L_SHIPDATE < <cache>(('1996-01-01' + interval '1' month)))) (cost=60883.30 rows=66041) (actual time=0.042..183.957 rows=7884 loops=1)
          -> Table scan on lineitem (cost=60883.30 rows=594488) (actual time=0.039..140.993 rows=600572 loops=1)
      -> Single-row index lookup on part using PRIMARY (P_PARTKEY=lineitem.L_PARTKEY) (cost=0.25 rows=1) (actual time=0.002..0.002 rows=1 loops=7884)

相比EXPLAIN FORMAT=TREE，EXPLAIN ANALYZE會實際執行SQL語句，並統計每個運算元的詳細耗時資訊，每個運算元額外提供如下資訊：

(actual time=m_start..m_end rows=m_rows loops=m_loops)

m_start: 該運算元返回第一行資料的實際時間(毫秒)
m_end: 該運算元返回所有資料的實際時間(毫秒)
m_rows: 該運算元實際的返回行數
m_loops: 該運算元實際的迭代次數

例如，Filter運算元過濾lineitem表的L_SHIPDATE欄位在 ['1996-01-01', '1996-02-01') 區間的資料。

Filter: ((lineitem.L_SHIPDATE >= DATE'1996-01-01') and (lineitem.L_SHIPDATE < <cache>(('1996-01-01' + interval '1' month))))
(cost=60883.30 rows=66041)
(actual time=0.042..183.957 rows=7884 loops=1)

最佳化器基於統計資訊估算出的代價為 60883.30，預測返回行數為 66041；然而，實際執行後發現，真實的返回行數為7884。其中，Filter運算元過濾掉了592688行 (600572 - 7884)。迭代次數為1（對應於Nested Loop Join中外表的掃描次數），返回給上層運算元（Nested loop inner join）第一行資料的時間為 0.042 毫秒，返回給上層運算元所有資料的時間為 183.957 毫秒。

例如，點查運算元Single-row index lookup on part using PRIMARY，作為Nested loop inner join的內表，透過條件part.p_partkey = lineitem.l_partkey迴圈獲取滿足條件的行。

Single-row index lookup on part using PRIMARY (P_PARTKEY=lineitem.L_PARTKEY)
(cost=0.25 rows=1)
(actual time=0.002..0.002 rows=1 loops=7884)

最佳化器估算出的代價為0.25，預測返回行數為 1；然而，實際執行後發現，真實的返回行數為1，但迭代次數為7884，與外表FILTER運算元執行後的結果資料量相等，每次迭代只返回上層運算元1行。因此，返回給上層運算元（Nested loop inner join）第一行資料的時間和所有資料的時間相等，都是0.002毫秒，可以推算出內表點查的累計耗時為 15.768 毫秒(7884 * 0.002毫秒)。

基於以上分析，我們可以看出該SQL語句執行耗時約200 毫秒，lineitem表的全表掃描耗時約140 毫秒，Filter運算元耗時約40 毫秒，part表迴圈點查約16 毫秒。

3. EXPLAIN ANALYZE原始碼實現

MySQL 8.0 使用火山執行引擎，火山模型是資料庫系統中廣泛使用的迭代模型。SQL語句經過查詢解析生成抽象語法樹（AST），然後經過查詢最佳化，最終生成執行樹，執行樹的每個節點對應一個執行運算元（Iterator）。每個運算元提供了Init,Read,End介面，每個運算元從子節點獲取資料，執行該運算元的相關工作，並返回結果給父節點。

以MySQL 8.0.22版本為例，它提供了37個執行運算元來處理資料讀取、多表連線、聚合操作、資料物化等多個操作場景，每個執行運算元都繼承自一個基類RowIterator。

例如， TableScanIterator（處理全表掃描）和 NestedLoopIterator（處理2表連線）的類圖如圖1所示：

圖1 TableScanIterator 和 NestedLoopIterator 類圖

EXPLAIN ANALYZE 工具的作用是展示SQL語句的執行計劃以及詳細記錄各個運算元的執行耗時。在MySQL 8.0中，這一功能的實現是透過新增一個介面模板類TimingIterator，將37個執行運算元封裝起來，以便統計各個執行運算元的詳細執行耗時資訊。這樣做的好處是實現簡單，無需對所有運算元單獨適配，而且不影響非EXPLAIN ANALYZE語句的執行效率。

例如，全表掃描運算元TableScanIterator 對應TimingIterator<TableScanIterator>，表連線運算元 NestedLoopIterator 對應 TimingIterator<NestedLoopIterator>，其類圖如圖2所示：

圖2 TimingIterator<TableScanIterator> 和 TimingIterator<NestedLoopIterator> 類圖

3.1 執行樹生成

資料庫最佳化器在確定了最優的訪問路徑(AccessPath)之後，會透過函式 CreateIteratorFromAccessPath 生成執行樹，該函式會依據運算元型別，呼叫NewIterator函式生成對應的運算元。

如果是普通DQL(SELECT)語句，則生成對應的運算元；如果是 EXPLAIN ANALYZE語句，則生成一個 TimingIterator<RealIterator>Wapper物件，其真實執行運算元被儲存在 TimingIterator::m_iterator 中。

例如，EXPLAIN ANALYZE語句，TableScanIterator 會生成TimingIterator<TableScanIterator> 運算元，NestedLoopIterator 會生成 TimingIterator<NestedLoopIterator> 運算元，執行流程如圖3所示。

圖3 執行樹生成流程

3.2 統計運算元執行耗時

TimingIterator 模板類的主體實現如下表所示，執行的統計資訊記錄在幾個私有成員變數中。

template <class RealIterator>
class TimingIterator final : public RowIterator {
 public:
  bool Init() override;
  int Read() override;
  std::string TimingString() const override; // 列印函式，輸出運算元執行時間資訊

 private:
  uint64_t m_num_rows = 0; // 該運算元累計處理的記錄數
  uint64_t m_num_init_calls = 0; // 呼叫 Init 函式的次數
  // 返回第一行的執行時間
  steady_clock::time_point::duration m_time_spent_in_first_row{0}; 
  // 返回所有行的執行時間
  steady_clock::time_point::duration m_time_spent_in_other_rows{0};
  bool m_first_row; // 是否為第一行資料
  RealIterator m_iterator; // 真實的執行運算元
};

在SQL語句實際執行過程中，透過 Init 和 Read 函式的排程來記錄詳細執行資訊，具體實現如下:

template <class RealIterator>
bool TimingIterator<RealIterator>::Init() {
  ++m_num_init_calls;  // Init 函式的呼叫次數遞增
  steady_clock::time_point start = now();
  bool err = m_iterator.Init(); // 呼叫真實執行運算元的Init函式
  steady_clock::time_point end = now();
  m_time_spent_in_first_row += end - start; // 累計獲取第一行資料的時間
  m_first_row = true;
  return err;
}

template <class RealIterator>
int TimingIterator<RealIterator>::Read() {
  steady_clock::time_point start = now();
  int err = m_iterator.Read(); // 呼叫真實執行運算元的Read 函式
  steady_clock::time_point end = now();
  if (m_first_row) {
    m_time_spent_in_first_row += end - start; // 更新獲取第一行資料的時間
    m_first_row = false; // 獲取第一行資料結束
  } else {
    m_time_spent_in_other_rows += end - start; // 更新獲取所有行資料的時間
  }
  if (err == 0) {
    ++m_num_rows; // 重新整理該運算元累計處理的記錄數
  }
  return err;
}

3.3 列印運算元執行耗時

SQL語句執行結束後，會呼叫函式 TimingIterator<RealIterator>::TimingString 列印運算元執行耗時資訊，呼叫堆疊資訊如下表所示：

dispatch_command
  mysql_parse
    mysql_execute_command
      Sql_cmd_dml::execute
        Sql_cmd_dml::execute_inner
          explain_query
            ExplainIterator
              PrintQueryPlan
                ExplainAccessPath
                  TimingIterator<RealIterator>::TimingString

TimingIterator<RealIterator>::TimingString 函式，會基於執行階段的統計列印以下資訊：

該運算元返回第一行資料的實際時間(毫秒)
該運算元返回所有資料的實際時間(毫秒)
該運算元實際的返回行數
該運算元實際的迭代次數

4. 總結

綜上，我們分析了MySQL 8.0 EXPLAIN ANALYZE命令的使用，並結合原始碼介紹其實現思路，幫助資料庫使用者和開發者更好的使用、理解該功能。

當遇到慢查詢時，我們也可藉助於EXPLAIN ANALYZE工具觀察執行計劃是否合理、分析SQL執行的主要耗時點，進而去最佳化SQL執行。

參考資料

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-18.html

https://dev.mysql.com/worklog/task/?id=4168

https://dev.mysql.com/doc/refman/8.0/en/using-explain.html

華為開發者空間，匯聚鴻蒙、昇騰、鯤鵬、GaussDB、尤拉等各項根技術的開發資源及工具，致力於為每位開發者提供一臺雲主機、一套開發工具及雲上儲存空間，讓開發者基於華為根生態創新。點選連結，免費領取您的專屬雲主機~

點選關注，第一時間瞭解華為雲新鮮技術~

MySQL慢查詢分析工具之mysqldumpslow
2020-04-29
MySql
mysql慢查詢日誌分析工具使用
2017-11-10
MySql
Mysql慢查詢日誌分析工具mysqlsla
2015-06-24
MySql
Mysql優化_慢查詢開啟說明及Mysql慢查詢分析工具mysqldumpslow用法講解
2018-01-20
MySql優化
MySQL 慢查詢
2021-01-27
MySql
MySQL慢查詢
2020-11-08
MySql
MySQL 慢查詢分析工具~pt-query-digest 詳解
2020-02-08
MySql
mysql分析慢查詢日誌工具mysqlsla安裝
2017-10-23
MySql
分析mysql慢查詢日誌的好工具--mysqlsla
2011-04-19
MySql
詳解MySQL--慢查詢
2020-12-05
MySql
mysqlsla 分析mysql慢查詢日誌
2015-10-19
MySql
linux下開啟mysql慢查詢，分析查詢語句
2013-12-19
LinuxMySql
mongodb慢查詢分析
2018-08-30
MongoDB
【MySql】慢日誌查詢工具之mysqlsla
2012-02-18
MySql
關於MySQL 通用查詢日誌和慢查詢日誌分析
2018-10-10
MySql
慢查詢分析調優工具～mysqldumpslow
2019-12-31
MySql
慢查詢分析調優工具～show profile
2020-01-02
MySQL 慢查詢優化
2019-06-23
MySql優化
MySQL：慢查詢日誌
2023-05-09
MySql
Mysql慢查詢操作梳理
2016-12-27
MySql
MySQL開啟慢查詢
2016-07-16
MySql
mysql慢查詢日誌
2008-11-13
MySql
【MySql】mysql 慢日誌查詢工具之mysqldumpslow
2012-02-24
MySql
mysql慢查詢，死鎖解決方案
2018-06-11
MySql
mysql慢查詢和錯誤日誌分析
2018-04-19
MySql
慢查詢
2024-08-20
MySQL 慢查詢那點事
2020-03-30
MySql
MySQL 清空慢查詢檔案
2015-12-03
MySql
[Mysql]慢查詢最佳化
2024-09-07
MySql
MySQL優化 - 開啟MySQL慢查詢日誌及分析工具mysqldumpslow
2016-03-23
MySql優化
深入mysql慢查詢設定的詳解
2021-09-09
MySql
對 MySQL 慢查詢日誌的簡單分析
2020-07-08
MySql
MySQL information_schema.columns表查詢慢原因分析
2022-02-06
MySqlORM
Redis 慢查詢
2017-12-16
Redis
mysql怎麼查詢年齡段
2021-09-11
MySql
Mysql 慢查詢優化實踐
2019-01-29
MySql優化
mysql慢查詢的奇異事件
2017-12-11
MySql事件
【MySQL】慢查詢日誌不列印
2017-02-06
MySql