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用途