MySQL最佳化器 limit影響的case

標籤：style   blog   http   color   使用   os   strong   io   

測試的用例中，因為limit的大小不同，而產生了完全不同的執行計畫：

1. 測試case：　　

create table t1 (  f1 int(11) not null,  f2 int(11) not null,  f3 int(11) not null,  f4 tinyint(1) not null,  primary key (f1),  unique key (f2, f3),  key (f4)) engine=innodb;insert into t1 values(1,1,991,1), (2,1,992,1), (3,1,993,1), (4,1,994,1), (5,1,995,1),(6,1,996,1), (7,1,997,1), (8,1,998,1), (10,1,999,1), (11,1,9910,1),(16,1,9911,1), (17,1,9912,1), (18,1,9913,1), (19,1,9914,1), (20,1,9915,1),(21,1,9916,1), (22,1,9917,1), (23,1,9918,1), (24,1,9919,1), (25,1,9920,1),(26,1,9921,1), (27,1,9922,1);

2. 兩個不同limit的sql產生的執行計畫：

　　

　　

3. 分析過程：

　　

step 1.  擷取可用的key，並計算rows

　　　　update_ref_and_keys

　　　　　　get_quick_record_count

　　結果： f2， f4可用， 其分配的quick_rows=[22, 22]

　　

　　從上面的cardinality來看，f2，f4的過濾性都是2， 這樣掃描f2，需要11行，然後根據primary回聚簇表掃描，有需要11行，所有，使用f2， f4索引的掃描需要22行。

 　　

step2：窮舉下執行計畫，找到cost最低的

　　　　best_access_path

　　　　　　best_extension_by_limited_search

　　　結果： 全表掃描的代價比較低，records=17， cost=2， 所以最後join->best_position[0]記錄的就是全表掃描的執行計畫。

 　　

step3：limit的影響

　　　　　　在make_join_select的過程，對於limit進行處理，理由是：如果有limit，並且比當前best_position的記錄數小，我們嘗試是否有可用的index，減少掃描代價

　　　　　　所以，在limit=10的時候，進行test_quick_select尋找，並使用f2的索引。而limit=20的查詢，不滿足條件，所以繼續使用全表掃描。

　　　　

相關注釋和代碼如下：　　　　　　

/*        We plan to scan all rows.        Check again if we should use an index.        We could have used an column from a previous table in        the index if we are using limit and this is the first table      */      if ((cond &&              !tab->keys.is_subset(tab->const_keys) && i > 0) ||          (!tab->const_keys.is_clear_all() && i == join->const_tables &&           join->unit->select_limit_cnt <           join->best_positions[i].records_read &&           !(join->select_options & OPTION_FOUND_ROWS)))

　　

step4： order by的影響

　　　　　　函數：test_if_skip_sort_order

　　　　　　limit=10：ref_key=f2:   判斷有一個primary key的index可以覆蓋order by查詢， 但走pk的代價高於ref_key=f2。

　　　　　　 limit=20：ref_key=0:     判斷有一個primary key的index可以覆蓋order by查詢，而且當前使用的是全表掃描，代價小於全表，所以選擇pk。

 

所以，兩個limit值不同的查詢，導致了不同的執行計畫。