MySQL Variables eq_range_index_dive_limit 数据库 参数变量解释及正确配置使用

本站中文解释

eq_range_index_dive_limit参数是MySQL中的一个数据库服务器参数，用于设置在执行SQL时在索引中使用范围搜索时，索引中每前缀块中允许搜索多少行之后才会放弃搜索，改为使用全表搜索来提高性能和节省系统资源。 默认值是200，可以通过下面的命令来修改：

修改参数：

SET GLOBAL eq_range_index_dive_limit = ;

查看参数：

SHOW VARIABLES LIKE ‘eq_range_index_dive_limit’;

官方英文解释

eq_range_index_dive_limit

This variable indicates the number of equality ranges in an
equality comparison condition when the optimizer should switch
from using index dives to index statistics in estimating the
number of qualifying rows. It applies to evaluation of
expressions that have either of these equivalent forms, where
the optimizer uses a nonunique index to look up
col_name values:

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

In both cases, the expression contains
N equality ranges. The optimizer
can make row estimates using index dives or index statistics.
If eq_range_index_dive_limit
is greater than 0, the optimizer uses existing index
statistics instead of index dives if there are
eq_range_index_dive_limit or
more equality ranges. Thus, to permit use of index dives for
up to N equality ranges, set
eq_range_index_dive_limit to
N + 1. To disable use of index
statistics and always use index dives regardless of
N, set
eq_range_index_dive_limit to
0.

For more information, see
Equality Range Optimization of Many-Valued Comparisons.

To update table index statistics for best estimates, use
ANALYZE TABLE.