深入解析：從原始碼窺探MySQL優化器
作者 | 湯愛中，雲和恩墨SQM開發者，Oracle/MySQL/DB2的SQL解析引擎、SQL稽核與智慧優化引擎的重要貢獻者，產品廣泛應用於金融、電信等行業客戶中。
優化器是邏輯SQL到物理儲存的直譯器，是一個複雜而“愚蠢”的數學模型，它的入參通常是SQL、統計資訊以及優化器引數等，而輸出通常一個可執行的查詢計劃，因此優化器的優劣取決於數學模型的穩定性和健壯性，理解這個數學模型就能理解資料庫的SQL處理流程。
01 優化器的執行流程
（注:此圖出自李海翔）
上圖展示了優化器的大致執行過程，可以簡單描述為：
1 根據語法樹及統計統計，構建初始表訪問陣列（init_plan_arrays）
2 根據表訪問陣列，計算每個表的最佳訪問路徑(find_best_ref)，同時儲存當前最優執行計劃（COST最小）
3 如果找到更優的執行計劃則更新最優執行計劃，否則優化結束。
從上述流程可以看出，執行計劃的生成是一個“動態規劃/貪心演算法”的過程，動態規劃公式可以表示為：Min(Cost(Tn+1)) = Min(Cost(T1))+Min(Cost(T2))+...Min(Cost(Tn-1))+Min(Cost(Tn)),其中Cost(Tn)表示訪問表T1 T2一直到Tn的代價。如果優化器沒有任何先驗知識，則需要進行 A(n,n) 次迴圈，是一個全排列過程，很顯然優化器是有先驗知識的，如表大小，外連線，子查詢等都會使得表的訪問是部分有序的，可以理解為一個“被裁減”的動態規劃，動態規則的核心函式為：Join::Best_extention_limited_search，在原始碼中有如下程式碼結構：
bool Optimize_table_order::best_extension_by_limited_search( table_map remaining_tables, uint idx, uint current_search_depth) { for (JOIN_TAB **pos= join->best_ref + idx; *pos; pos++) { ...... best_access_path(s, remaining_tables, idx, false, idx ? (position-1)->prefix_rowcount : 1.0, position); ...... if (best_extension_by_limited_search(remaining_tables_after, idx + 1, current_search_depth - 1)) ...... backout_nj_state(remaining_tables, s); ...... } }以上程式碼是在一個for迴圈中遞迴搜尋，這是一個典型的全排列的演算法。
02優化器引數
MySQL的優化器對於Oracle來說還顯得比較幼稚。Oracle有著各種豐富的統計資訊，比如系統統計資訊，表統計資訊，索引統計資訊等，而MySQL則需要更多的常量，其中MySQL5.7提供了表mysql.server_cost和表mysql.engine_cost，可以供使用者配置，使得使用者能夠調整優化器模型，下面就幾個常見而又非常重要的引數進行介紹：
1 #define ROW_EVALUATE_COST 0.2f
計算符合條件的行的代價，行數越多，代價越大
2 #define IO_BLOCK_READ_COST 1.0f
從磁碟讀取一個Page的代價
3 #define MEMORY_BLOCK_READ_COST 1.0f
從記憶體讀取一個Page的代價，對於Innodb來說，表示從一個Buffer Pool讀取一個Page的代價，因此讀取記憶體頁和磁碟頁的預設代價是一樣的
4 #define COND_FILTER_EQUALITY 0.1f
等值過濾條件預設值為0.1，例如name = ‘lily’, 表大小為100，則返回10行資料
5 #define COND_FILTER_INEQUALITY 0.3333f
非等值過濾條件的預設值是0.3333,例如col1>col2
6 #define COND_FILTER_BETWEEN 0.1111f
Between過濾的預設值是0.1111f，例如:col1 between a and b
......
這樣的常量很多，涉及到過濾條件、JOIN快取、臨時表等等各種代價，理解這些常量後，看到執行計劃的Cost後，你會有種豁然開朗的感覺！
03 優化器選項
在MySQL中，執行select @@optimizer_trace, 得到如下引數：
index_merge=on,index_merge_union=off,index_merge_sort_union=off, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on, use_index_extensions=on, condition_fanout_filter=on
04 Optimize Trace是如何生成的？
在流程圖中的函式中，存在大量如下程式碼：
Opt_trace_object trace_ls(trace, "searching_loose_scan_index");
因此，在優化器執行過程中，優化器的執行路徑也被儲存在Opt_trace_object中，進而儲存在information_schema.optimizer_trace中，方便使用者查詢和跟蹤。
05 優化器的典型使用場景
5.1 全表掃描
select * from sakila.actor;
表actor統計資訊如下：
Db_name | Table_name | Last_update | n_rows | Cluster_index_size | Other_index |
sakila | actor | 2018-11-20 16:20:12 | 200 | 1 | 0 |
主鍵actor_id統計資訊如下：
Index_name | Last_update | Stat_name | Stat_value | Sample_size | Stat_description |
PRIMARY | 2018-11-14 14:25:49 | n_diff_pfx01 | 200 | 1 | actor_id |
PRIMARY | 2018-11-14 14:25:49 | n_leaf_pages | 1 | NULL | Number of leaf pages in the index |
PRIMARY | 2018-11-14 14:25:49 | size | 1 | NULL | Number of pages in the index |
執行計劃：
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "41.00" }, "table": { "table_name": "actor", "access_type": "ALL", "rows_examined_per_scan": 200, "rows_produced_per_join": 200, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "40.00", "prefix_cost": "41.00", "data_read_per_join": "56K" }, "used_columns": [ "actor_id", "first_name", "last_name", "last_update", "id" ] } } } IO_COST = CLUSTER_INDEX_SIZE * PAGE_READ_TIME = 1 * 1 =1; EVAL_COST = TABLE_ROWS*EVALUATE_COST = 200 * 0.2 =40; PREFIX_COST = IO_COST + EVAL_COST;注意以上過程忽略了記憶體頁和磁碟頁的訪問代價差異。
5.2 表連線時使用全表掃描
SELECT * FROM sakila.actor a, sakila.film_actor b WHERE a.actor_id = b.actor_id
Db_name | Table_name | Last_update | n_rows | Cluster_index_size | Other_index_size |
Sakila | Film_actor | 2018-11-20 16:55:31 | 5462 | 12 | 5 |
表film_actor中索引(actor_id,film_id)統計資訊如下：
Index_name | Last_update | Stat_name | Stat_value | Sample_size | Stat_description |
PRIMARY | 2018-11-14 14:25:49 | n_diff_pfx01 | 200 | 1 | actor_id |
PRIMARY | 2018-11-14 14:25:49 | n_diff_pfx02 | 5462 | 1 | actor_id,film_id |
PRIMARY | 2018-11-14 14:25:49 | n_leaf_pages | 11 | NULL | Number of leaf pages in the index |
PRIMARY | 2018-11-14 14:25:49 | size | 12 | NULL | Number of pages in the index |
{ "query_block": { "select_id": 1, "cost_info": { "query_cost": "1338.07" }, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "possible_keys": [ "PRIMARY" ], "rows_examined_per_scan": 200, "rows_produced_per_join": 200, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "40.00", "prefix_cost": "41.00", "data_read_per_join": "54K" }, "used_columns": [ "actor_id", "first_name", "last_name", "last_update" ] } }, { "table": { "table_name": "b", "access_type": "ref", "possible_keys": [ "PRIMARY" ], "key": "PRIMARY", "used_key_parts": [ "actor_id" ], "key_length": "2", "ref": [ "sakila.a.actor_id" ], "rows_examined_per_scan": 27, "rows_produced_per_join": 5461, "filtered": "100.00", "cost_info": { "read_cost": "204.67", "eval_cost": "1092.40", "prefix_cost": "1338.07", "data_read_per_join": "85K" }, "used_columns": [ "actor_id", "film_id", "last_update" ] } } ] } }第一張表actor的全表掃代價為41，可以參考示例1。
第二個表就是NET LOOP 代價：
read_cost(204.67) =prefix_rowcount * (1 + keys_per_value/table_rows*cluster_index_size =
200 * (1+27/13863*12)*1
注意：27 相當於對於每個actor_id，film_actor的索引估計，對於每個actor_id,平均有27條記錄=5462/200
Table_rows是如何計算的呢？
Film_actor表的實際記錄數是5462，一共12個page，11個葉子頁，總大小為11*16K(預設頁大小)=180224Byte, 最小記錄長度為26(通過計算欄位長度可得),13863 = 180224/26*2, 2是安全因子，做最差的代價估計。
表連線返回行數=200*5462/200，因此行估算代價為5462*0.2=1902.4
5.3 IN查詢
表film_actor中索引idx_id(film_id)統計資訊如下：
Index_name | Last_update | Stat_name | Stat_value | Sample_size | Stat_description |
idx_id | 2018-11-14 14:25:49 | n_diff_pfx01 | 997 | 4 | actor_id |
idx_id | 2018-11-14 14:25:49 | n_diff_pfx02 | 5462 | 4 | film_id,actor_id |
idx_id | 2018-11-14 14:25:49 | n_leaf_pages | 4 | NULL | Number of leaf pages in the index |
idx_id | 2018-11-14 14:25:49 | size | 5 | NULL | Number of pages in the index |
EXPLAIN SELECT * FROM ACTOR WHERE actor_id IN (SELECT film_id FROM film_actor) { "query_block": { "select_id": 1, "cost_info": { "query_cost": "460.79" }, "nested_loop": [ { "table": { "table_name": "ACTOR", "access_type": "ALL", "possible_keys": [ "PRIMARY" ], "rows_examined_per_scan": 200, "rows_produced_per_join": 200, "filtered": "100.00", "cost_info": { "read_cost": "1.00", "eval_cost": "40.00", "prefix_cost": "41.00", "data_read_per_join": "56K" }, "used_columns": [ "actor_id", "first_name", "last_name", "last_update", "id" ] } }, { "table": { "table_name": "film_actor", "access_type": "ref", "possible_keys": [ "idx_id" ], "key": "idx_id", "used_key_parts": [ "film_id" ], "key_length": "2", "ref": [ "sakila.ACTOR.actor_id" ], "rows_examined_per_scan": 5, "rows_produced_per_join": 200, "filtered": "100.00", "using_index": true, "first_match": "ACTOR", "cost_info": { "read_cost": "200.66", "eval_cost": "40.00", "prefix_cost": "460.79", "data_read_per_join": "3K" }, "used_columns": [ "film_id" ], "attached_condition": "(`sakila`.`actor`.`actor_id` = `sakila`.`film_actor`.`film_id`)" } } ] } } id select_type table partitions type possible_keys key key_len ref rows filtered Extra ------ ----------- ---------- ---------- ------ ------------- ------ ------- --------------------- ------ -------- --------------------------------------------- 1 SIMPLE ACTOR (NULL) ALL PRIMARY (NULL) (NULL) (NULL) 200 100.00 (NULL) 1 SIMPLE film_actor (NULL) ref idx_id idx_id 2 sakila.ACTOR.actor_id 5 100.00 Using where; Using index; FirstMatch(ACTOR)
從執行計劃中可以看出，MySQL採用FirstMatch方式。在MySQL中，半連結優化方式為：Materialization Strategy，LooseScan，FirstMatch，DuplicateWeedout，預設情況下四種優化方式都是存在的，選取方式基於最小COST。現在我們以FirstMatch為例，講解優化器的執行流程。
SQL如下：
select * from Country where Country.code IN (select City.Country from City where City.Population > 1*1000*1000) and Country.continent='Europe'
從上圖可以看出，FirstMatch是通過判斷記錄是否已經在結果集中存在來減少查詢和匹配流程。
表actor的訪問代價可以參考示例1.
表film_actor表的訪問代價200.66是如何計算的呢？
訪問表film_actor中索引欄位film_id,MySQL會走覆蓋索引掃，即IDEX_ONLY_SCAN,一次索引訪問的代價是如何計算的呢？
參考函式double handler::index_only_read_time(uint keynr, double records)
索引塊大小為16K,並且MySQL假設塊都是半滿的，則一個塊能夠存放的索引記錄數為：
16K/2/(索引長度+主鍵長度(注：二級索引儲存的是主鍵的引用))=16K/2/(2+4)+1=1366,
其中主鍵為(actor_id,film_id),兩個欄位都是smallint,佔用4個位元組，而索引idx_id(film_id)是2個位元組，因此每次訪問索引的代價為：（5.47+1366-1）/1366 = 1.0032, 訪問film_actor表一共需要200次，總訪問代價為：200*1.0032=200.66
總代價460.79 = 表actor的訪問代價+表film_actor訪問代價+行估算代價=
41+200.66+200*1*5.47*1*02,其中兩個1分別表示過濾因子，由於兩個表均沒有過濾條件因此過濾因子都是1。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31556440/viewspace-2285469/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 窺探React – 原始碼分析React原始碼
- MyBatis原始碼窺探(一):MyBatis整體架構解析MyBatis原始碼架構
- 窺探Underscore原始碼系列-開篇原始碼
- 窺探React-原始碼分析(二)React原始碼
- Vue render深入窺探之謎Vue
- [原始碼解析] PyTorch分散式優化器(2)----資料並行優化器原始碼PyTorch分散式優化並行
- [原始碼解析] PyTorch分散式優化器(1)----基石篇原始碼PyTorch分散式優化
- springboot原始碼解析-管中窺豹系列之web伺服器（七）Spring Boot原始碼Web伺服器
- 深入Mybatis原始碼——配置解析MyBatis原始碼
- Async,Await 深入原始碼解析AI原始碼
- 從特斯拉召回事件，窺探OTA汽車進化真面目事件
- 窺探NSObjectObject
- Java集合Stack原始碼深入解析Java原始碼
- Java 集合Hashtable原始碼深入解析Java原始碼
- 深入淺出ReentrantReadWriteLock原始碼解析原始碼
- 深入淺出Semaphore原始碼解析原始碼
- 深入淺出ReentrantLock原始碼解析ReentrantLock原始碼
- 深入淺出AQS原始碼解析AQS原始碼
- 深入瞭解SpringMVC原始碼解析SpringMVC原始碼
- SQL優化器探討(zt)SQL優化
- MySQL優化之索引解析MySql優化索引
- springboot原始碼解析-管中窺豹系列之aware（六）Spring Boot原始碼
- springboot原始碼解析-管中窺豹系列之Runner（三）Spring Boot原始碼
- springboot原始碼解析-管中窺豹系列之BeanFactoryPostProcessor（十一）Spring Boot原始碼Bean
- springboot原始碼解析-管中窺豹系列之EnableXXX（十）Spring Boot原始碼
- springboot原始碼解析-管中窺豹系列之排序（五）Spring Boot原始碼排序
- springboot原始碼解析-管中窺豹系列之Initializer（四）Spring Boot原始碼
- TP5.1 原始碼窺探之反射和 Yaconf 配置擴充套件原始碼反射套件
- MySQL核心原始碼解讀-SQL解析之解析器淺析MySql原始碼
- Java Timer原始碼解析（定時器原始碼解析）Java原始碼定時器
- 深入RxJava2 原始碼解析(一)RxJava原始碼
- 深入RxJava2 原始碼解析(二)RxJava原始碼
- 深入原始碼解析 tapable 實現原理原始碼
- Java 集合系列：Vector原始碼深入解析Java原始碼
- MySQL半一致性讀原理解析-從原始碼角度解析MySql原始碼
- TP5.1 原始碼窺探之類的自動載入機制原始碼
- 深入解析C# List<T>的原始碼C#原始碼
- 深入解析Underscore.js原始碼架構JS原始碼架構