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。