eq_range_index_dive_limit的作用

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可能會顯著影響查詢執行計劃，這里借用網上的一個案例：

有一個表“t”。主鍵由從“id1”開始的多個列組成。表t中有1.67M行，id1的基數是46K（這些數字可以通過SHOW TABLE STATUS / SHOW INDEX收集）。因此，每個id1平均有36行（1.67M / 46K = 36），但實際的id1分布是不均勻的。有接近1M行，其中id1在1和10之間。

mysql> explain select count（*）from t force index（PRIMARY）where id1 in（1,2,3,4,5,6,7,8,9）\G 
***************** 1.行********** * 
id：1 
select_type：SIMPLE 
table：t 
type：range 
possible_keys：PRIMARY 
key：PRIMARY 
key_len：8 
ref：NULL 
rows：912388 
extra：using where;using index 
1 row（0.00 sec）

MySQL估計912K行匹配，其中id1 IN（1..9）。這接近實際數字。 MySQL5.6引入了持久化優化器統計，使統計信息更準確。

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：360 
extra：using where;using index 
1 row（0.00 sec）

當添加一個IN條件（id1 IN（1..10））時，突然估計的行數下降到360！這比實際匹配的行數小得多。估計的行數越來越少（或更大）經常使MySQL選擇不正確的查詢執行計劃，所以這是真的很嚴重。

估計的行數變化很大的原因是一個新的系統變量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。