优化器参数-eq_range_index_dive_limit-索引下探

5.7中当出现数据大量切斜的时候执行计划依然能够得到正确的执行计划。比如性别列索引，其中30行，29行为男性，1行为女性，下面是执行计划示例：

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

mysql> set eq_range_index_dive_limit=100; Query OK, 0 rows affected (0.00 sec) mysql> desc select * from testdvi3 where sex='M'; +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ |  1 | SIMPLE      | testdvi3 | NULL       | ALL  | sex           | NULL | NULL    | NULL |   30 |    96.67 | Using where | +----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (2.74 sec) mysql> desc select * from testdvi3 where sex='W'; +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ |  1 | SIMPLE      | testdvi3 | NULL       | ref  | sex           | sex  | 9       | const |    1 |   100.00 | NULL  | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (2.00 sec) mysql> set eq_range_index_dive_limit=1; Query OK, 0 rows affected (0.00 sec) mysql> desc select * from testdvi3 where sex='W'; +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ |  1 | SIMPLE      | testdvi3 | NULL       | ref  | sex           | sex  | 9       | const |   15 |   100.00 | NULL  | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> desc select * from testdvi3 where sex='M'; +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+ |  1 | SIMPLE      | testdvi3 | NULL       | ref  | sex           | sex  | 9       | const |   15 |   100.00 | NULL  | +----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+

　　使用限制

• 唯一条件的等值查询也不会使用索引下探（= in or ）。
• 一般是非唯一索引或者范围查询（< > <= >=）才会用到索引下探，实际上他们都是‘RANGE’。