mysql index dive_MySQL非唯一索引值分布不均匀与优化器参数eq_range_index_dive_limit

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

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值