前言

我們在日常維護資料庫的時候，如果遇到慢語句查詢的時候，我們一般會怎麼做？執行EXPLAIN去檢視它的執行計劃？
是的。我們經常會這麼做，然後看到執行計劃展示給我們的一些資訊，告訴我們MySQL是如何執行語句的。
BUT，執行計劃往往只給我們帶來了最基礎的分析資訊，比如是否有使用索引，還有一些其他供我們分析的資訊，比如使用了臨時表、排序等等。我們能從這些資訊裡面找一些最佳化點，這樣就足夠了嗎？

看看這張圖裡的執行計劃，我們可以提很多問題：為什麼t2表上明明使用了索引在Extra列中還是能看到temporary和filesort？如果possible_keys列中有多個索引的話，最佳化器是基於什麼選定使用的索引？這些問題，並不能非常直觀地從執行計劃中看出來更多的資訊，這個時候，我們可以開啟OPTIMIZER_TRACE，基於OPTIMIZER_TRACE捕獲的資訊，去做更細緻的追蹤分析。一起來看看吧~

OPTIMIZER_TRACE是什麼呢？

它是一個跟蹤功能，跟蹤執行的語句的解析最佳化執行的過程，並將跟蹤到的資訊記錄到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中
可以透過optimizer_trace系統變數啟停跟蹤功能，MySQL從5.6開始提供了相關的功能，但是MySQL預設是關閉它的，我們在需要使用的時候才會手動去開啟。
optimizer_trace可以是會話或者是全域性開啟，但是每個會話都只能跟蹤它自己執行的語句，表中預設只記錄最後一個查詢的跟蹤結果（表中記錄的跟蹤結果數可以透過optimizer_trace的引數設定）
可跟蹤語句物件：
- SELECT/INSERT/REPLACE/UPDATE/DELETE
- EXPLAIN
- SET
- DO
- DECLARE/CASE/IF/RETURN
- CALL

如何跟蹤分析

先來看看官網怎麼說~官網的使用說明非常的簡單粗暴了
- 1、開啟optimizer_trace引數
- 2、執行要分析的查詢
- 3、檢視INFORMATION_SCHEMA.OPTIMIZER_TRACE表中跟蹤結果
- 4、迴圈2、3步驟
- 5、當不再需要分析的時候，關閉引數

# Turn tracing on (it's off by default):  
SET optimizer_trace="enabled=on"; 
SELECT ...; # your query here 
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; 
# possibly more queries... 
# When done with tracing, disable it: 
SET optimizer_trace="enabled=off";

以上是官網給出的分析的示例流程，雖然看上去都非常簡單明瞭，但是，如果你檢視INFORMATION_SCHEMA.OPTIMIZER_TRACE，就不一定會那麼認為了，我們進一步來分析一下OPTIMIZER_TRACE

INFORMATION_SCHEMA.OPTIMIZER_TRACE

INFORMATION_SCHEMA.OPTIMIZER_TRACE表結構

CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` (
  `QUERY` longtext NOT NULL,
  `TRACE` longtext NOT NULL,
  `MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int(20) NOT NULL DEFAULT '0',
  `INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INFORMATION_SCHEMA.OPTIMIZER_TRACE表欄位含義
* QUERY：跟蹤的SQL語句
* TRACE：跟蹤資訊（JSON格式）
* MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟蹤資訊過長時，被截斷的跟蹤資訊的位元組數
* INSUFFICIENT_PRIVILEGES：執行跟蹤語句的使用者是否有檢視物件的許可權。當不具有許可權時，該列資訊為1且TRACE欄位為空。一般出現在呼叫帶有SQL SECURITY DEFINER的檢視或者是儲存過程的情況下

跟蹤結果解析

在此為了更全面的去認知，這裡借用官網提供的案例的資料進行分析。案例參考： https://dev.mysql.com/doc/internals/en/tracing-example.html
相關配置：
* sql_mode = ''
* optimizer_prune_level = 1

QUERY&EXPLAIN

# query
SELECT SUM(t2.col_varchar_nokey) , t2.pk AS field2 
FROM t2 STRAIGHT_JOIN t1 ON t2.pk = t1.col_int_key 
WHERE t2.pk BETWEEN 10 AND 100 
GROUP BY field2 
ORDER BY t1.col_int_key,t2.pk ;
# explain 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t2 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 91 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------

TRACE

整個OPTIMIZER_TRACE的重點就是TRACE的JSON樹。TRACE中的JSON樹大部分都又臭又長，個人更建議使用帶有收縮程式碼格式的編輯器去圍觀這棵樹，能更清晰地理順這棵樹，如下圖所示，我們先來看看TRACE的大框架。在TRACE的JSON中有三個步驟構成： join_preparation（準備階段）、join_optimization（最佳化階段）、join_execution（執行階段）。

如下圖所示中的左JSON樹，該樹是在基礎層級上的展開，每個階段下面都可以看到有“select# : N”，它表示當前的結構體是在跟蹤分析第幾個SELECT，因為語句僅僅只有一個SELECT語句，所以示例中的JSON樹只有“select# : 1”。如果有多個SELECT就會出現“select# : 2”的情況，比如使用了“SELECT UNION SELECT”，可以參照下圖所示中的右JSON樹。

join_preparation（準備階段）

如圖示，是join_preparation階段的JSON樹，我們按著steps[0]->steps[1]的順序，看看在這個示例中準備階段時都做了什麼事情
step[0].expanded_query：對比下原始語句，可以看到這裡的語句進行了格式化，補充了原有語句中隱式的庫、表、列名
step[1].transformations_to_nested_joins：這裡進行了轉換，將JOIN的ON條件句轉換成了WHERE條件句（JOIN_condition_to_WHERE），並輸出了新的expanded_query。對照下step[0].expanded_query的語句，新的expanded_query中將原有的ON條件句改寫成了WHERE，這就是JOIN_condition_to_WHERE操作導致的
在語句的補充和轉換等操作完成之後，準備階段結束並進入下一階段。

join_optimization（最佳化階段）

如圖示，是join_optimization階段的JSON樹，最佳化階段實際上是我們分析OPTIMIZER_TRACE的一個重點階段，它的步驟非常多也相對複雜，先粗略看看steps下的每個步驟，大致都在做些什麼
steps[0].condition_processing ：條件句處理。該步驟對WHERE條件句進行最佳化處理。
steps[1].substitute_generated_columns ：替換虛擬生成列。
steps[2].table_dependencies ：梳理表之間的依賴關係。
steps[3].ref_optimizer_key_uses ：如果最佳化器認為查詢可以使用ref的話，在這裡列出可以使用的索引
steps[4].rows_estimation ：估算錶行數和掃描的代價。如果查詢中存在range掃描的話，對range掃描進行計劃分析及代價估算。
steps[5].considered_execution_plans ：對比各可行計劃的代價，選擇相對最優的執行計劃
steps[6].attaching_conditions_to_tables ：新增附加條件，使得條件儘可能篩選單表資料。
steps[7&8].clause_processing ：對DISTINCT、GROUP BY、ORDER BY等語句進行最佳化（每一種語句會產生一個clause_processing 結構體，示例語句中既有排序又有分組，所以會有7、8兩個步驟）
steps[9].refine_plan ：最佳化後的執行計劃

1、condition_processing

條件句處理。該步驟對WHERE條件句進行最佳化處理。
處理物件：
* condition：最佳化物件型別。WHERE條件句或者是HAVING條件句（還記得麼，準備階段的ON條件句已經被轉換為WHERE條件句了，所以這裡不存在ON的條件句）
* original_condition：最佳化前的原始語句
處理步驟：
* 在圖中可以看到有三次的語句最佳化的過程，每步都寫明瞭轉換型別（transformation），明確轉換做的事情，以及轉換之後的結果語句（resulting_condition）
* transformation：轉換型別句。這三次的轉換分別是equality_propagation（等值條件句轉換），constant_propagation（常量條件句轉換），trivial_condition_removal（無效條件移除的轉換）
* resulting_condition：轉換之後的結果輸出。從圖示來說，原始的WHERE條件句檢測到“=”，進行了等值條件句轉換，但是其並不存在常量條件句與無效條件，故在equality_propagation步驟中進行轉換之後，後面的步驟中並沒有再次變化
基於該案例，在這裡提供一個三階段都有轉換的語句供參考觀察，有興趣的童鞋可以執行觀測

select * from t1 join t2 on t1.pk=t2.pk+1 where t2.pk = 5 and 1 =1 ;

2、table_dependencies

表依賴關係，會列出該語句中涉及到所有的表
* table：涉及的表名及其別名
* row_may_be_null：列是否允許為NULL，這裡並不是指表中的列屬性是否允許為NULL，而是指JOIN操作之後的列是否為NULL。比如說原始語句中如果使用了LEFT JOIN，那麼後一張表的row_may_be_null則會顯示為true
* map_bit：表的對映編號，從0開始遞增。
* depends_on_map_bits：依賴的對映表，這裡主要是在使用STRAIGHT_JOIN進行強制連線順序或者是LEFT JOIN/RIGHT JOIN有順序差別時，會在depends_on_map_bits中列出前置表的map_bit

3、ref_optimizer_key_uses

列出了所有可用的ref型別的索引。如果是使用了組合索引的多個部分，在ref_optimizer_key_uses下會列出多個結構體。單個結構體中會列出單表ref使用的索引及其對應值。

4、rows_estimation

rows_estimation估算需要掃描的記錄數，這一段以表物件做為結構體進行展開。如示例中對t2表和t1表分別進行了分析，其中t1表由於沒有可用的索引，故其在此階段的結構體非常簡單，僅僅包括了一步table_scan（全表掃描）。t2表則進入range analysis階段，經歷了看上去繁多的步驟，以下依據t2表的range_analysis，解剖這些看似複雜的步驟
range_analysis
- table_scan：全表掃描的行數(rows)以及所需要的代價(cost)。圖示可知示例中對t2表如果使用全表掃描，一共要掃描100行，其代價為23.1
- potential_range_indexes：該階段會列出表中所有的索引並分析其是否可用，並且還會列出索引中可用的列欄位
- group_index_range：評估在使用了GROUP BY或者是DISTINCT的時候是否有適合的索引可用。當語句中沒有GROUP BY或者是DISTINCT的時候，該結構體下顯示chosen='false' & cause = 'not_group_by_or_distinct'；如果語句中在多表關聯時使用了GROUP BY或DISTINCT時，在該結構體下顯示chosen='false' & cause = 'not_single_table'；其他情況下會去嘗試分析可用的索引（potential_group_range_indexes）並且計算對應的掃描行數及其所需代價
- analyzing_range_alternatives ：分析可選方案的代價。包括range_scan_alternatives（range掃描分析）、analyzing_roworder_intersect（index merge分析）兩個階段，分別針對不同的情況進行執行代價的分析，從中選擇出更優的執行計劃。
  
  * range_san_alternatives：range掃描分析針對所有可用於range掃描的索引進行了代價分析，並根據分析結果確認選擇使用的索引
  index：分析的索引名
  ranges：range掃描的條件句範圍
  index_dives_for_eq_ranges：是否使用了index dive。這個值會被引數eq_range_index_dive_limit設定值影響，有興趣的童鞋可以深入研究一下。
  rowid_ordered：該range掃描的結果集是否根據PK值進行排序
  using_mrr：是否有使用mrr
  index_only：是否是覆蓋索引
  rows：掃描行數
  cost：使用索引的代價
  chosen：是否選擇使用該索引
  * analyzing_roworder_insersect：由於示例沒有使用index merge，所以在這一段僅僅給出了不使用index merge的原因。如果是語句可以使用index_merge的情況，在該階段會分析使用index_merge過程中消耗的代價（index_scan_cost、disk_sweep_cost等），並彙總merge的代價確認是否選擇使用index_merge以及對應使用的索引。
- chosen_range_access_summary：在前一個步驟中分析了各類索引使用的方法及代價，得出了一定的中間結果之後，在summary階段彙總前一階段的中間結果確認最後的方案。
  
  * range_access_plan：range掃描最終選擇的執行計劃。在該結構體中會給出執行計劃的type，使用的索引以及掃描行數。如果range_access_plan.type是“index_roworder_intersect”（即index merge）的話，在該結構體下還會列intersect_of結構體給出index merge的具體資訊。
  * rows_for_plan：該執行計劃的掃描行數
  * cost_for_plan：該執行計劃的執行代價
  *chosen：是否選擇該執行計劃

5、considered_execution_plans

considered_execution_plans的部分負責對比各可行計劃的代價，選擇相對最優的執行計劃。在這裡透過對比STRAIGHT_JOIN和JOIN的結構體來分析一波
- STRAIGHT_JOIN：在原始語句中，使用的是STRAIGHT_JOIN，這就意味著在執行的時候，連線順序被強制性決定，而不是由最佳化器選擇，這裡強制了由t2表驅動t1表進行關聯。所以STRAIGHT_JOIN的樹中並不像JOIN的樹中存在considered_execution_plans[1]，因為關聯順序的固定，所以只有一種執行順序的方案。而在considered_execution_plans[0]中直隸的欄位資訊與其下直隸的“rest_of_plan”結構體中的欄位資訊重複度非常高，兩者的table物件分別是t2和t1，並且“rest_of_plan”下存在的“plan_prefix”（前置計劃）是t1。從字面的英文釋義和層級關係分析，considered_execution_plans[N]表示的是可選的執行順序的一種，由於STRAIGHT_JOIN決定了只能是t2表驅動t1表，所以在該執行計劃僅存在considered_execution_plans[0]，在該結構體中先解析了t2表並估算其執行代價，再基於t2表解析t1表並估算整個查詢的執行代價（在這裡只有兩張表之間的JOIN，如果是三張表之間的JOIN則可以看到在“rest_of_plan”下還會存在下級的“rest_of_plan”，在下級的“rest_of_plan”中會分析第三張表的代價資訊）
- JOIN：將原始語句的STRAIGHT_JOIN修改為普通的JOIN。這時候連線順序是由最佳化器計算比較執行代價後決定的，有可能是t1驅動t2，也有可能是t2驅動t1，所以在圖示中可以看到considered_execution_plans[0]、considered_execution_plans[1]兩種執行順序。
欄位補充釋義：
- 前面也有說明，rest_of_plan的內容與considered_execution_plans的內容非常相近，它們分析的內容其實是一致的，僅僅是表物件不一致，所以在這將這兩者合併起來進行說明。下面的說明中會用左圖和右圖的描述來表示considered_execution_plans的部分和considered_execution_plans.rest_of_plan的部分
- table：分析的表物件名稱及其別名。可以看到右圖是t2左圖是t1，符合我們分析的由t2驅動t1的順序
- plan_prefix：前置的執行計劃（格式：順序ID：表名）。由於STRAIGHT_JOIN的關係，語句強制性由t2驅動t1，可以看到右圖中plan_prefix陣列裡面並沒有內容，而左圖中展示了plan_prefix是t2，這個符合我們語句分析中得出的t2表驅動t1表的執行順序。
- best_access_path：當前最優的執行順序資訊結果集，我們看看展開細節圖可以發現主要內容實際上是considered_access_paths，這是一個選擇比較的過程。根據索引的使用與否以及具體的使用方法可能會產生considered_access_paths[N]，例如左圖的considered_access_paths[0]是分析透過ref的方式使用PRIMARY索引，considered_access_paths[1]是透過range的方式使用PRIMARY索引。
  * access_type表示使用索引的方式，可參照為explain中的type欄位，除了前面提到的左圖的使用方式，右圖中使用的是“scan”（全表掃描或是索引掃描，如果是索引掃描會列出對應的index）方式。根據access_type的不同，在具體的分析過程中列出來的一些欄位列就會有所不同
  * 使用了索引的情況類似於左圖的considered_access_paths[N]。在considered_access_paths[0]中，由於該索引在ref方式下不可用（usable = false），所以最終沒有使用該方案（chosen = false）。在considered_access_paths[1]中，給出了range方式下的掃描行數（rows_to_scan = 91）以及range掃描使用的索引（range_details.used_index = 'PRIMARY'）。使用了range掃描的方式之後得到的最終結果集行數（resulting_rows）及該操作應需要的代價（cost），最後的是在選擇比較的結果中是否選擇了該方式（chosen）
  * 使用了掃描的情況類似於右圖的considered_access_paths[0]。該結構體下列出了該表的掃描行數（rows_to_scan），由於這裡沒有列出index所以能分析出這裡的access_type中的“scan”在這裡指的是全表掃描。與左圖中不同的是，右圖還列出了關聯過程中是否有使用到job_buffer（using_join_cache）以及使用的次數（buffers_needed），從這兩個值中可以瞭解對於當前的查詢來說，join_buffer的配置及使用是否恰當。
- condition_filtering_pct：類似於explain中的filtered列，這是一個估算值。
- rows_for_plan：該執行計劃最終的掃描行數，這裡的行數其實也是估算值，是由considered_access_paths的resulting_rows相乘之後再乘以condition_filtering_pct獲得
- cost_for_plan：該執行計劃的執行代價，由considered_access_paths的cost相加而得
- chosen：是否選擇了該執行計劃，這個值在左圖中並不存在，因為左圖只是considered_access_paths的前半部分，直到右圖的結束才是一個完整的considered_access_paths[0]，執行計劃的是否選擇，是在整個分析流程都走完了之後，才確定的。

6、attaching_conditions_to_tables

這一步是基於considered_execution_plans中已選執行計劃改造原有的where條件句並針對表的增加適當的附加條件便於單表資料的篩選。這部分條件的增改主要是為了便於ICP，但是ICP是否開啟並不影響該部分的構造。
original_condition：在準備階段原始SQL語句以及considered_execution_plans中使用的索引基礎上，改寫語句，儘可能將原有語句中不能使用索引的條件句繫結到單表中對單表進行資料篩選
attached_conditions_computation：使用啟發式演算法計算已使用的索引，如果已使用的索引的訪問型別是ref的話，計算使用range方式訪問是否能使用組合索引中更多的索引列，如果可以的話，用range的方式替換ref的訪問方式。
attached_conditions_summary ：針對上述的附加之後的情況彙總
* table ：物件表及其別名
* attached：附加的條件或者是原語句中能直接下推給單表篩選的條件

7&8、clause_processing

這部分結構體主要是對DISTINCT、GROUP BY、ORDER BY等語句進行最佳化
clause：最佳化語句關鍵字（DISTINCT、GROUP BY、ORDER BY）
original_clause：最佳化物件的原始語句
items：original_clause中包含的物件
* item：物件名
* eq_ref_to_preceding_items：與前置表關聯的是否是唯一索引。在這個示例中，並沒有這樣的情況所以沒有列出。如果語句稍微改寫一下，將原始查詢語句改寫為“t1 STRAIGHT_JOIN t2”，讓t1去驅動t2表，就發現在分析items[1].item = t2.pk時出現了該欄位。這是由於t1與t2透過t2的主鍵pk列進行關聯，這就意味著，t1中的一行資料最多隻能在t2中關聯出一列，所以在後續最佳化的結果語句中order by 的t2.pk列被最佳化掉了，因為這一列已經確認唯一不需要再進行排序。
resulting_clause_is_simple：最佳化後的結果語句是否是簡單語句
resulting_clause：最佳化後的結果語句

9、refine_plan

該階段展示的是改善之後的執行計劃，如圖所示只展示了對應的表物件沒有其他的欄位的話，說明在之前已經確定的執行計劃中沒有需要再最佳化的地方，可直接應用
* table：涉及的表名及其別名
* pushed_index_condition：可使用到ICP的條件句
* table_condition_attached：在attaching_conditions_to_tables階段新增了附加條件的條件語句
* access_type：最佳化後的索引訪問型別

join_execution （執行階段）

join_execution是語句的執行階段，我們前面提到trace有三大部分構成，其中join_preparation、join_optimization可以分析explain的過程，但是在join_execution階段，如果分析的語句是explain的話，該階段的程式碼是空結構體，只有在真正執行語句之後，該階段的程式碼才會有具體的步驟過程。
在執行階段中，本示例主要經歷了圖示中的這些步驟：creating_tmp_table（建立臨時表）、filesort_information（檔案排序資訊）、filesort_priority_queue_optimization（檔案排序-優先佇列最佳化）、filesort_execution（檔案排序執行）、filesort_summary（檔案排序彙總資訊）。我們接下來一一展開這些步驟進行分析

0、creating_tmp_table

該階段根據語句建立臨時表，雖然在圖示中沒有展示，但如果臨時表的大小超過了設定的max_heap_table_size或者是tmp_table_size引數的話，會在此步驟後引發多一個步驟：converting_tmp_table_to_ondisk（將臨時表落地到磁碟中），有興趣的童鞋可以改寫一下這兩個引數值召喚一下這個步驟
- tmp_table_info：臨時表資訊
  * table：臨時表的名稱
  * row_length：臨時表的單行長度
  * key_length：臨時表索引長度
  * unique_constraint：是否有使用唯一約束
  * location：表儲存位置，比如記憶體表memory (heap)，或者是轉換到磁碟的物理表disk (InnoDB)
  * row_limit_estimate：該臨時表中能儲存的最大行數

1、filesort

join_execution 階段在建立臨時表之後如果臨時表不需要轉換為磁碟表的話，即開始對檔案排序進行處理
filesort_information：檔案排序資訊（如果有多列，filesort_information下會有多個結構體）
* 排序方式：排序列是升序還是降序
* table：排序的表物件名
* field：排序列
filesort_priority_queue_optimization：優先佇列最佳化排序，一般在使用limit子句的時候會使用優先佇列
* usable：是否有使用
* cause：沒有使用的原因
filesort_execution：執行檔案排序
filesort_summary：檔案排序彙總資訊
* rows：預計掃描行數
* examined_rows：參與排序的行數
* number_of_tmp_files：使用臨時檔案的個數，這個值為0代表全部使用sort_buffer記憶體排序，否則表示使用了磁碟檔案排序
* sort_buffer_size：使用的sort_buffer的大小
* sort_mode：排序方式

結束語

在整個optimizer_trace中我們重點其實就是在跟蹤記錄TRACE的JSON樹，我們透過這棵樹中的內容可以具體去分析最佳化器究竟做了什麼事情，進行了哪些選擇，是基於什麼原因做的選擇，選擇的結果及依據。這一系列都可以輔助驗證我們的一些觀點及最佳化，更好的幫助我們對我們的資料庫的例項進行調整。
如果真的能看到這一段，真的要佩服下各位的毅力了。在寫這篇文章之初沒有想過這篇文章會被我話癆到那麼長，當然一方面的原因是optimizer_trace的東西的確很多，文章中僅僅只針對了單個案例來分析，並沒有完全覆蓋到optimizer_trace中trace記錄的所有狀態，官網中在這一部分也並沒有找到對應的文件是描述optimizer_trace的trace結構體的具體內容的，所以難免還有缺漏的地方，需要後續不斷去補充。
希望這篇文章對看到這裡的你有所幫助。

| 作者簡介

劉雲·沃趣科技資料庫工程師

熟悉MySQL體系結構、InnoDB儲存引擎，豐富的資料庫故障診斷、資料庫最佳化經驗。

手把手教你認識OPTIMIZER_TRACE

前言

OPTIMIZER_TRACE是什麼呢？

相關變數淺析

如何跟蹤分析

INFORMATION_SCHEMA.OPTIMIZER_TRACE

跟蹤結果解析

QUERY&EXPLAIN

join_preparation（準備階段）

join_optimization（最佳化階段）

1、condition_processing

2、table_dependencies

3、ref_optimizer_key_uses

4、rows_estimation

5、considered_execution_plans

6、attaching_conditions_to_tables

7&8、clause_processing

9、refine_plan

join_execution （執行階段）

0、creating_tmp_table

1、filesort

結束語

相關文章