深入聊聊MySQL直方图的应用

深入聊聊MySQL直方图的应用

本文是在假定读者了解了直方图是什么，直方图如何进行添加维护的前提下，围绕直方图与索引的对比、何时应该添加直方图，及直方图如何帮助优化器选择更优的执行计划这几个方面来介绍直方图。

直方图的作用

直方图用于描述数据表中列上的数据分布，这些关于数据分布的内容，可以帮助优化器更准确地估计给定的where子句或联接条件将过滤掉表中多少数据。这里我们要清楚的一点，直方图并不能像索引一样可以帮助减少要访问的行数，它只是帮助优化器选择更合适的执行计划。MySQL8开始支持直方图，但实际上直方图在MySQL中，不像在其他数据库中那样有用，因为MySQL能够通过index dive，直接访问索引对应的B+树，来计算某个扫描区间内对应的索引记录条数，所以直方图不能与同一列上的索引一起使用，而且优化器优先使用索引。

直方图与索引相比，优点是什么

MySQL的索引既可以有效估算索引范围内的行数，又可以帮助减少要访问的记录，为什么还要引入直方图呢？与索引相比，直方图的一个好处是，在确定过滤条件返回行数时直方图比索引成本要低，直方图的统计信息可以轻松用于优化器，而索引在确定查询计划时，要执行下潜操作来估算行数，并且每次执行查询时都要重复执行这样的操作。讲到这里可能大家会有一个疑问，不是有索引统计信息吗，为何每次都要执行下潜操作来估算呢？其实MySQL是这样设计的，有一个参数eq_range_index_dive_limit(默认值200), 对于索引列而言，当存在与此参数设置相等或更大的区间范围过滤条件时，优化器将从下潜转换为只使用索引统计信息来估算匹配行的数量。因为MySQL认为使用index dive估算比统计信息更准确，但是当过滤的区间范围条件多，比如使用in来过滤，条目值达到1000，过滤区间就会有1000个，这样index dive的成本太高，MySQL就倾向于使用索引统计信息了。似乎跑题了呢，回归正传。与索引相比，直方图的第二个好处是，索引维护有代价，执行DML操作时需要维护索引，所以索引多了就会影响DML操作的效率，直方图统计信息只需在非业务高峰定期收集即可，对DML操作无影响。与索引相比，直方图的第三个好处是，索引会增加表空间文件的大小，而直方图统计信息占用的空间可忽略不计。

既无索引又无直方图，优化器如何估算返回行数

如果过滤条件上既没有索引也没有直方图，优化器如何估算过滤比例呢，优化器会根据MySQL代码中内置的默认规则来估计过滤比例，相当于根据自己的想法瞎猜。默认的过滤比例以一个列表形式来展示如下：

过滤类型

过滤比例

等值过滤（=）

10%

不等于（<>或!=）

90%

不等式(< 或>)

33.33%

Between

11.11%

IN

Min(条目*10, 50)

举个例子；执行语句：explain select * from t1 where temporary= 'N';对t1表的字段 temporary 进行等值过滤。

从上图可以看出，优化器按规则估算过滤比例，filtered为10%，也就是估算返回行数为rows * filtered/100=7183行，而实际返回行数为72214，filtered=72214/72435=99.69。这个差异可谓很大了。

收集一下该列上直方图的统计信息后，再去查看执行计划中的filtered，此时filtered就相当精确了。

数据分布不均匀时，MySQL以不变应万变的处理规则，估算肯定是相当不准确的，因此在选择执行计划时就有可能做出错误的决策。索引的维护有代价，不能在每个涉及条件的列上都加上索引，那么在不适合创建索引的列上创建直方图，可以作为索引的补充，帮助优化器更好的选择执行计划。

何时应该添加直方图

因为MySQL在sql优化阶段会对索引进行下潜操作来估算返回行数，导致直方图在MySQL中使用空间是有限的，那么究竟要在哪些列上创建直方图，才能有效发挥直方图的作用呢？创建直方图的最佳候选是符合下列条件的列：

数据分布不均匀，或者具有太多值，以至于优化器粗略估算无法很好的估计数据的选择行。

选择性差的列（否则索引可能是更好的选择）

用于在where子句或联接条件过滤表的数据。如果不对列进行过滤，则优化器无法使用直方图。

随着时间推移，数据分布逐渐稳定的列。直方图统计信息不会自动更新。如果在数据分布频繁变化的列上添加直方图，则直方图统计信息可能不准确。

直方图应用举例

其实直方图对于单表访问用处不大，主要体现在表联接时，表的联接方式有多种选择时，直方图才可以帮助确定何种选择最好。举个例子来说明。a1,a2两个表做关联查询。两个表结构信息如下图所示：

关联查询语句：select * from a1,a2 where a1.id=a2.id and a1.temporary='N' and a2.status='NOVALID';两表在关联条件的字段上都有索引，又都有额外的过滤条件，优化器在选择走嵌套联接时，有两种可能，一种a1驱动a2，一种是a2驱动a1，哪种方式更好，取决于两表使用过滤条件过滤后哪个表返回的行数少，因为我们知道嵌套联接时，小表驱动大表效率高。而a1表的temporary字段，a2表的status字段数据分布不均匀，选择性差，不适合建立索引。这个时候直方图就有用武之地了。已知a1表的temporary='N'条件过滤性差，a2表的status='NOVALID'的过滤性好，用a2驱动a1效率会更高。因为没有直方图时，优化器不知道谁的过滤性好，按等值过滤的默认规则filtered=10进行过滤，在选择执行计划时就有可能做出错误决策。我们先看没有收集直方图时的执行计划。如下图所示：

从图中可以看出优化器选择了a1驱动a2, a1表过滤后估算的行数为7049，而实际为72214,a2表作为被驱动表被扫描72214次。执行总耗时280ms。 下面对a2表的status列收集直方图，然后再执行关联查询，如下图所示：

从上图可以看出，有了直方图后，优化器选择了a2驱动a1, a2表过滤后估算的行数为8，实际也为8，a1作为被驱动表只扫描了8次。执行总耗时87ms，效率提升了3倍。

体会到直方图的作用了吗，直方图告诉了优化器数据分布，让优化器估算更准确，进而让优化器做出了英明的决策。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。