eq_range_index_dive_limit的用途

2024-09-12 20:30:17

字體：大中小

來源：轉載

供稿：網友

　　MySQL5.6引入了一個新的系統變量eq_range_index_dive_limit。
　　查閱MySQL5.6官方文檔得知，MySQL在執行等值范圍查詢例如select ... from xxx where xxx in(...)時，優化器在計算執行計劃成本時會根據條件個數采用不同的方式以減小選擇執行計劃的開銷。
　　當條件數N小于eq_range_index_dive_limit時，優化器認為此時條件個數尚可，可以采用成本較高但更為精確的index dive方式來計算執行成本；當N大于或等于eq_range_index_dive_limit時，優化器會認為此時使用index dive的方式計算成本帶來的開銷過大，此時MySQL優化器會根據index statistics直接估算成本。
　　大部分情況下，where條件中使用的索引列的選擇性都還是不錯的，使用index statistic直接估算返回行數并不會有太大偏差，并且能夠避免index dive帶來的開銷，在IN條件較多的情況下，能快速找到正確的執行計劃，提升系統性能。然而，不均勻分布的索引也不罕見，這種情況下，eq_range_index_dive_limit可能會顯著影響查詢執行計劃，這里借用網上的一個案例：

　　估計的行數變化很大的原因是一個新的系統變量eq_range_index_dive_limit。如在線手冊所述，“如果eq_range_index_dive_limit大于0，如果有eq_range_index_dive_limit或更多相等范圍”，優化器將使用現有索引統計信息而不是索引潛水。默認eq_range_index_dive_limit為10.因此，當設置10個或更多IN條件時，MySQL會跳過索引dive，并從統計信息中估計行數。在這個例子中，MySQL估計360行（1.67M（表t的估計總行數）/ 46K（基數id1）* 10（IN條件）== 360）。

　　通過增加eq_range_index_dive_limit足夠大，MySQL不會錯誤地估計行。

　　mysql> set session eq_range_index_dive_limit = 1000;
　　query OK，0 row affected（0.00秒）

　　mysql>explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9,10）/G
　　***************** 1.行********** *
　　id：1
　　select_type：SIMPLE
　　table：t
　　type：range
　　possible_keys：PRIMARY
　　key：PRIMARY
　　key_len：8
　　ref：NULL
　　rows：937684
　　extra：using where;using index
　　1 row（0.00 sec）
　　由于SQL強制走了主鍵索引，在這個例子中MySQL并沒有選錯執行計劃，但eq_range_index_dive_limit對于MySQL選擇執行計劃的影響顯而易見。
　　在eq_range_index_dive_limit設置過小且索引分布極不均勻的情況下，MySQL可能會由于成本計算誤差太大，導致選擇錯誤的執行計劃這一災難性后果！
　　如果是業務特征決定了需要執行多次類似于上述案例中的SQL，DBA應考慮關閉該特性：
　　set global eq_range_index_dive_limit = 0;
　　總結：
　　eq_range_index_dive_limit有助于減少查詢執行計劃的index dive成本，但5.6版本缺省值為10，有點偏小，DBA應根據業務特點選擇合理的值或者關閉該特性。
　　注：該參數在MySQL 5.7中缺省值為200。

（編輯：武林網）

上一篇：UniDAC使用教學數據類型映射

下一篇：MariaDB stop slave用途