MySQL5.6引入了一个新的系统变量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)
设置10个或更多的IN条件是很常见的,不均匀分布的索引也很常见。 eq_range_index_dive_limit有助于减少查询执行计划的index dive成本,但我们认为10太小了。MySQL 5.7目前默认设置为200