mysql列偏移_MySQL：如何对待分布偏移的数据

---

作者微信:gp_22389860

---

btr_estimate_n_rows_in_range_low 函数用于非唯一访问的行数评估也就是在执行计划生成阶段会去访问实际的数据，尽可能的得到正确的执行计划，这个过程叫做dive。其和参数eq_range_index_dive_limit有关，如果参数设置1那么将禁用dive功能，使用统计数据进行执行计划的生成。但某些时候MySQL 5.7根据统计数据并不能得到正确的执行计划，因此才有了dive，但是dive会产生额外的代价，这个过程是需要实际访问数据块的。

常用的比如非唯一索引的数据访问，还有> 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 |

+----+-------------+----------+------------+------+---------------+------+---------+-------+------+----------+-------+

```

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-2707569/，如需转载，请注明出处，否则将追究法律责任。