简述 mysql 的 eq_range_index_dive_limit 参数作用

你的名字 2022-07-14 05:19 86阅读 0赞

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

--------------------

最近碰到一个慢SQL问题，解决过程有点小曲折，和大家分享下。 SQL本身不复杂，表结构、索引也比较简单，不过个别字段存在于多个索引中。

CREATE TABLE `pre_forum_post` (  
`pid` int(10) unsigned NOT NULL,  
`fid` mediumint(8) unsigned NOT NULL DEFAULT ‘0’,  
`tid` mediumint(8) unsigned NOT NULL DEFAULT ‘0’,  
`first` tinyint(1) NOT NULL DEFAULT ‘0’,  
`author` varchar(40) NOT NULL DEFAULT ”,  
`authorid` int(10) unsigned NOT NULL DEFAULT ‘0’,  
`subject` varchar(80) NOT NULL DEFAULT ”,  
`dateline` int(10) unsigned NOT NULL DEFAULT ‘0’,  
`message` mediumtext NOT NULL,  
`useip` varchar(15) NOT NULL DEFAULT ”,  
`invisible` tinyint(1) NOT NULL DEFAULT ‘0’,  
`anonymous` tinyint(1) NOT NULL DEFAULT ‘0’,  
`usesig` tinyint(1) NOT NULL DEFAULT ‘0’,  
`htmlon` tinyint(1) NOT NULL DEFAULT ‘0’,  
`bbcodeoff` tinyint(1) NOT NULL DEFAULT ‘0’,  
`smileyoff` tinyint(1) NOT NULL DEFAULT ‘0’,  
`parseurloff` tinyint(1) NOT NULL DEFAULT ‘0’,  
`attachment` tinyint(1) NOT NULL DEFAULT ‘0’,  
`rate` smallint(6) NOT NULL DEFAULT ‘0’,  
`ratetimes` tinyint(3) unsigned NOT NULL DEFAULT ‘0’,  
`status` int(10) NOT NULL DEFAULT ‘0’,  
`tags` varchar(255) NOT NULL DEFAULT ‘0’,  
`comment` tinyint(1) NOT NULL DEFAULT ‘0’,  
`replycredit` int(10) NOT NULL DEFAULT ‘0’,  
`position` int(8) unsigned NOT NULL AUTO\_INCREMENT,  
PRIMARY KEY (`tid`,`position`),  
UNIQUE KEY `pid` (`pid`),  
KEY `fid` (`fid`),  
KEY `displayorder` (`tid`,`invisible`,`dateline`),  
KEY `first` (`tid`,`first`),  
KEY `new_auth` (`authorid`,`invisible`,`tid`),  
KEY `idx_dt` (`dateline`)  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

“root@localhost Fri Aug 1 11:59:56 2014 11:59:56 \[test\]>show table status like ‘pre\_forum\_post’\\G  
***\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\**** 1. row ***\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\****  
Name: pre\_forum\_post  
Engine: MyISAM  
Version: 10  
Row\_format: Dynamic  
Rows: 23483977  
Avg\_row\_length: 203  
Data\_length: 4782024708  
Max\_data\_length: 281474976710655  
Index\_length: 2466093056  
Data\_free: 0  
Auto\_increment: 1  
Create\_time: 2014-08-01 11:00:56  
Update\_time: 2014-08-01 11:08:49  
Check\_time: 2014-08-01 11:12:23  
Collation: utf8\_general\_ci  
Checksum: NULL  
Create\_options:  
Comment:

mysql> show index from pre\_forum\_post;  
\+—————-+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+  
| Table | Non\_unique | Key\_name | Seq\_in\_index | Column\_name | Collation | Cardinality | Sub\_part | Packed | Null | Index\_type | Comment | Index\_comment |  
\+—————-+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+  
| pre\_forum\_post | 0 | PRIMARY | 1 | tid | A | 838713 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 0 | PRIMARY | 2 | position | A | 23483977 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 0 | pid | 1 | pid | A | 23483977 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | fid | 1 | fid | A | 1470 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | displayorder | 1 | tid | A | 838713 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | displayorder | 2 | invisible | A | 869776 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | displayorder | 3 | dateline | A | 23483977 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | first | 1 | tid | A | 838713 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | first | 2 | first | A | 1174198 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | new\_auth | 1 | authorid | A | 1806459 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | new\_auth | 2 | invisible | A | 1956998 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | new\_auth | 3 | tid | A | 11741988 | NULL | NULL | | BTREE | | |  
| pre\_forum\_post | 1 | idx\_dt | 1 | dateline | A | 23483977 | NULL | NULL | | BTREE | | |  
\+—————-+————+————–+————–+————-+———–+————-+———-+——–+——+————+———+—————+  
我们来看下这个SQL的执行计划：

mysql> select \* from pre\_forum\_post where tid=7932612 and `invisible` in(‘0’,’-2’) order by dateline limit 15;  
15 rows in set (26.78 sec)  
看下MySQL的会话状态值：Handler\_read\_next

| Handler\_read\_next | 17274153 |  
从1700多万数据中选取15条记录，结果可想而知，非常慢。 我们强制指定比较靠谱的索引再看下：

mysql> explain select \* from pre\_forum\_post force index(displayorder) where tid=7932612 and `invisible` in(‘0’,’-2’) order by dateline limit 15\\G  
***\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\**** 1. row ***\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\****  
id: 1  
select\_type: SIMPLE  
table: pre\_forum\_post  
type: range  
possible\_keys: displayorder  
key: displayorder  
key\_len: 4  
ref: NULL  
rows: 46131  
Extra: Using index condition; Using filesort  
看下实际执行的耗时：

mysql> select \* from pre\_forum\_post force index(displayorder) where tid=7932612 and `invisible` in(‘0’,’-2’) order by dateline limit 15;  
15 rows in set (0.08 sec)  
尼玛，怎么可以这么快，查询优化器未免太坑爹了吧。 再看下MySQL的会话状态值：Handler\_read\_next

| Handler\_read\_next | 31188 |  
和不强制索引的情况相比，差了553倍！ 所幸，5.6以上除了EXPLAIN外，还支持OPTIMIZER\_TRACE，我们来观察下两种执行计划的区别，发现不强制指定索引时的执行计划有诈，会在最后判断到 ORDER BY 子句时，修改执行计划：

{\
            "reconsidering_access_paths_for_index_ordering": {\
              "clause": "ORDER BY",\
              "index_order_summary": {\
                "table": "`pre_forum_post`",\
                "index_provides_order": true,\
                "order_direction": "asc",\
                "index": "idx_dt",\
                "plan_changed": true,\
                "access_type": "index_scan"\
              } /* index_order_summary */\
            } /* reconsidering_access_paths_for_index_ordering */\

而在前面analyzing\_range\_alternatives和considered\_execution\_plans阶段，都认为其他几个索引也是可选择的，直到这里才给强X了，你Y的… 看起来像是MySQL 5.6查询优化器的bug了，GOOGLE了一下，还真发有人已经反馈过类似的问题： MySQL bug 70245: incorrect costing for range scan causes optimizer to choose incorrect index

看完才发现，其实不是神马BUG，而是原来从5.6开始，增加了一个选项叫eq\_range\_index\_dive\_limit 的高级货，这货大概的用途是： 在较多等值查询（例如多值的IN查询）情景中，预估可能会扫描的记录数，从而选择相对更合适的索引，避免所谓的index dive问题。

当面临下面两种选择时：

1、索引代价较高，但结果较为精确；  
2、索引代价较低，但结果可能不够精确；  
简单说，选项 eq\_range\_index\_dive\_limit 的值设定了 IN列表中的条件个数上线，超过设定值时，会将执行计划分支从 1 变成 2。

该值默认为10，但社区众多人反馈较低了，因此在5.7版本后，将默认值调整为200了。

不过，今天我们这里的案例却是想反的，因为优化器选择了看似代价低但精确的索引，实际却选择了更低效的索引。 因此，我们需要将其阈值调低，尝试设置 eq\_range\_index\_dive\_limit = 2 后（上面的例子中，IN条件里有2个值），再看下新的查询计划：

mysql> set eq\_range\_index\_dive\_limit = 2;

mysql> explain select \* from pre\_forum\_post where tid=7932612 and `invisible` in(‘0’,’-2’) order by dateline limit 15\\G  
***\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\**** 1. row ***\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\****  
id: 1  
select\_type: SIMPLE  
table: pre\_forum\_post  
type: range  
possible\_keys: PRIMARY,displayorder,first  
key: displayorder  
key\_len: 4  
ref: NULL  
rows: 54  
Extra: Using index condition; Using filesort  
卧槽，预估扫描记录数又降了557倍，相比最开始降了接近32万倍！ 在这个案例中，虽然通过修改选项 eq\_range\_index\_dive\_limit 的阈值可以达到优化效果，但事实上更靠谱的做法是：直接删除 idx\_dt 索引。 是的，没错，删除这个垃圾重复索引，因为实际上这个索引的用处不大，够坑爹吧~~