我們在日常維護資料庫的時候，如果遇到慢語句查詢的時候，我們一般會怎麼做？執行EXPLAIN去檢視它的執行計劃？是的。我們經常會這麼做，然後看到執行計劃展示給我們的一些資訊，告訴我們MySQL是如何執行語句的。但是，執行計劃往往隻給我們帶來了最基礎的分析資訊，比如是否有使用索引，還有一些其他供我們分析的資訊，比如使用了臨時表、排序等等。
下面是此次案例的SQL文本：
SELECT
table_space_info.db_id AS table_space_info_db_id,
NULL AS param_2,
round(( sum( table_space_info.use_capacity ) / sum( table_space_info.expandable_total_capacity )) * 100, 2 ) AS expandable_usage_rate
FROM
table_space_info
WHERE
table_space_info.create_at = ( SELECT max( tbs.create_at ) AS max_2 FROM table_space_info AS tbs WHERE tbs.db_id = table_space_info.db_id )
AND ifnull( table_space_info.pdb_name, '' ) != 'N/A'
GROUP BY
table_space_info.db_id; 複制
下面是mariadb（版本5.5.64-MariaDB）執行計劃：用xxx代替上述SQL
MariaDB [dg-ins]> explain xxx;
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
| 1 | PRIMARY | table_space_info | index | NULL | db_id | 8 | NULL | 6855 | Using where |
| 2 | DEPENDENT SUBQUERY | tbs | ref | db_id | db_id | 8 | test.table_space_info.db_id | 161 | |
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
2 rows in set (0.03 sec) 複制
下面是MySQL5.7（版本5.7.32-log）執行計劃：用xxx代替上述SQL
mysql> explain SELECT xxx;
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
| 1 | PRIMARY | table_space_info | index | db_id | db_id | 8 | NULL | 6904 | Using where |
| 2 | DEPENDENT SUBQUERY | tbs | ref | db_id | db_id | 8 | test.table_space_info.db_id | 300 | NULL |
+----+--------------------+------------------+-------+---------------+-------+---------+-----------------------------+------+-------------+
2 rows in set, 2 warnings (0.00 sec)
Note (Code 1276): Field or reference 'test.table_space_info.db_id' of SELECT #2 was resolved in SELECT #1
Note (Code 1003): /* select#1 */ select `test`.`table_space_info`.`db_id` AS `table_space_info_db_id`,NULL AS `param_2`,round(((sum(`test`.`table_space_info`.`use_capacity`) / sum(`test`.`table_space_info`.`expandable_total_capacity`)) * 100),2) AS `expandable_usage_rate` from `test`.`table_space_info` where ((`test`.`table_space_info`.`create_at` = (/* select#2 */ select max(`test`.`tbs`.`create_at`) AS `max_2` from `test`.`table_space_info` `tbs` where (`test`.`tbs`.`db_id` = `test`.`table_space_info`.`db_id`))) and (ifnull(`test`.`table_space_info`.`pdb_name`,'') <> 'N/A')) group by `test`.`table_space_info`.`db_id`
mysql>
mysql> show warnings;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1276 | Field or reference 'test.table_space_info.db_id' of SELECT #2 was resolved in SELECT #1 |
| Note | 1003 | /* select#1 */ select `test`.`table_space_info`.`db_id` AS `table_space_info_db_id`,NULL AS `param_2`,round(((sum(`test`.`table_space_info`.`use_capacity`) / sum(`test`.`table_space_info`.`expandable_total_capacity`)) * 100),2) AS `expandable_usage_rate` from `test`.`table_space_info` where ((`test`.`table_space_info`.`create_at` = (/* select#2 */ select max(`test`.`tbs`.`create_at`) AS `max_2` from `test`.`table_space_info` `tbs` where (`test`.`tbs`.`db_id` = `test`.`table_space_info`.`db_id`))) and (ifnull(`test`.`table_space_info`.`pdb_name`,'') <> 'N/A')) group by `test`.`table_space_info`.`db_id` |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec) 複制
上述兩個不同環境下，執行計劃一摸一樣，但是MariaDB執行0.02秒，MySQL5.7要執行5秒。
這個時候，下一步動作可能就要去看optimizer_trace尋找原因
OPTIMIZER_TRACE是什麼呢？
它有點類似于Oracle的10053，會給你做簡單版的transform，考慮給你做一定程度的優化，同時它也有跟蹤功能，跟蹤執行的語句的解析優化執行的過程，并将跟蹤到的資訊記錄到INFORMATION_SCHEMA的OPTIMIZER_TRACE表中，可以通過optimizer_trace系統變量啟停跟蹤功能，MySQL從5.6開始提供了相關的功能，但是MySQL預設是關閉它的，我們在需要使用的時候才會手動去開啟。
optimizer_trace可以是會話或者是全局開啟，但是每個會話都隻能跟蹤它自己執行的語句，表中預設隻記錄最後一個查詢的跟蹤結果（表中記錄的跟蹤結果數可以通過optimizer_trace的參數設定）
可跟蹤語句對象包括：SELECT/INSERT/REPLACE/UPDATE/DELETE、EXPLAIN、SET、DO、CALL、DECLARE CASE IF RETURN
mysql> show variables like '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name | Value |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace | enabled=on,one_line=off |
| optimizer_trace_features | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit | 1 |
| optimizer_trace_max_mem_size | 16384 |
| optimizer_trace_offset | -1 |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)
optimizer_trace
* enabled：啟用/禁用optimizer_trace功能
* one_line：決定了跟蹤資訊的存儲方式，為on表示使用單行存儲，否則以JSON樹的标準展示形式存儲。單行存儲中跟蹤結果中沒有空格，造成可讀性極差，但對于JSON解析器來說是可以解析的，将該參數打開唯一的優勢就是節省空間，一般不建議開啟
optimizer_trace_features：該變量中存儲了跟蹤資訊中可控的列印項，可以通過調整該變量，控制在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的trace列需要列印的JSON項和不需要列印的JSON項。預設打開該參數下的所有項
optimizer_trace_max_mem_size ：optimizer_trace記憶體的大小，如果跟蹤資訊超過這個大小，資訊将會被截斷
optimizer_trace_limit & optimizer_trace_offset
* 這兩個參數神似于SELECT語句中的“LIMIT offset, row_count”，optimizer_trace_limit 限制的是跟蹤資訊存儲的個數，optimizer_trace_offset 則是限制偏移量。和 LIMIT 一樣，optimizer_trace_offset 從0開始計算（最老的一個查詢記錄的偏移量為0）
* optimizer_trace_offset 的正負值，不需要太過于去糾結，如下表所示，其實offset 0 = offset -5 ，它們是一個等價的關系，僅僅是表述方式不同。這樣的表述方式和python中的切片的表述是一緻的，了解python的童鞋們都知道，切片的時候經常用到-1取清單中最後一個數值或者是反向取值。 複制
抓取optimizer_trace步驟：
# 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"; 複制
整理如下
set optimizer_trace_max_mem_size = 50000;
SET optimizer_trace="enabled=on";
SELECT
table_space_info.db_id AS table_space_info_db_id,
NULL AS param_2,
round(( sum( table_space_info.use_capacity ) / sum( table_space_info.expandable_total_capacity )) * 100, 2 ) AS expandable_usage_rate
FROM
table_space_info
WHERE
table_space_info.create_at = ( SELECT max( tbs.create_at ) AS max_2 FROM table_space_info AS tbs WHERE tbs.db_id = table_space_info.db_id )
AND ifnull( table_space_info.pdb_name, '' ) != 'N/A'
GROUP BY
table_space_info.db_id;
SELECT trace FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G
SET optimizer_trace="enabled=off"; 複制
抓取結果整體結構
整個OPTIMIZER_TRACE的重點就是TRACE的JSON樹。TRACE中的JSON樹大部分都又臭又長，個人更建議使用帶有收縮代碼格式的編輯器去圍觀這棵樹，能更清晰地理順這棵樹，如下圖所示，我們先來看看TRACE的大架構。
在TRACE的JSON中有三個步驟構成： join_preparation（準備階段）、join_optimization（優化階段）、join_execution（執行階段）。
"join_preparation": {},（準備階段）
"join_optimization": {},（優化階段）
"join_execution": {},（執行階段） 複制
先看下 準備階段json代碼
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select max(`tbs`.`create_at`) AS `max_2` from `table_space_info` `tbs` where (`tbs`.`db_id` = `table_space_info`.`db_id`)"
}
]
}
},
{
"expanded_query": "/* select#1 */ select `table_space_info`.`db_id` AS `table_space_info_db_id`,NULL AS `param_2`,round(((sum(`table_space_info`.`use_capacity`) / sum(`table_space_info`.`expandable_total_capacity`)) * 100),2) AS `expandable_usage_rate` from `table_space_info` where ((`table_space_info`.`create_at` = (/* select#2 */ select max(`tbs`.`create_at`) AS `max_2` from `table_space_info` `tbs` where (`tbs`.`db_id` = `table_space_info`.`db_id`))) and (ifnull(`table_space_info`.`pdb_name`,'') <> 'N/A')) group by `table_space_info`.`db_id`"
}
]
}
} 複制
優化階段這裡忽略掉...
join_execution階段：
此SQL的optimizer_trace的執行部分，存在幾百次的相同的重複單元，如下：
{
"subselect_execution": {
"select#": 2,
"steps": [
{
"join_execution": {
"select#": 2,
"steps": [
]
}
}
]
}
} 複制
說明嵌套查詢幾百次，主要耗時在這裡。
将SQL改寫成如下形式：
SELECT
table_space_info.db_id AS table_space_info_db_id,
NULL AS param_2,
round(( sum( table_space_info.use_capacity ) / sum( table_space_info.expandable_total_capacity )) * 100, 2 ) AS expandable_usage_rate
FROM
table_space_info
WHERE
( table_space_info.db_id, table_space_info.create_at ) IN ( SELECT db_id, max( tbs.create_at ) AS max_2 FROM table_space_info AS tbs GROUP BY tbs.db_id )
AND ifnull( table_space_info.pdb_name, '' ) != 'N/A'
GROUP BY
table_space_info.db_id; 複制
sql立即傳回結果，執行計劃我這裡沒記錄，這個案例也從側面說明了網上的一些建議，盡量用in代替exists傳遞值關聯，那麼其本質還是由于優化器的transform行為，MySQL中SQL的寫法是會影響執行計劃判斷的，這與Oracle類似。
關于這個案例，你學廢了嗎...
更多文章歡迎關注本人公衆号，搜dbachongzi或掃二維碼
作者：姚崇
Oracle OCM、MySQL OCP、Oceanbase OBCA、PingCAP PCTA認證，擅長基于Oracle、MySQL Performance Turning及多種關系型 NoSQL資料庫。