简述 mysql 的 eq_range_index_dive_limit 参数作用

最新推荐文章于 2021-11-03 13:46:00 发布

游离于移动互联网边缘

最新推荐文章于 2021-11-03 13:46:00 发布

阅读量6.3k

点赞数

文章标签： mysql

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 索引。是的，没错，删除这个垃圾重复索引，因为实际上这个索引的用处不大，够坑爹吧~~

游离于移动互联网边缘

关注

0
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
简述 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），但实际的id
复制链接

扫一扫