本文假设读者了解什么是直方图，如何添加和维护直方图，并着重介绍直方图和索引的比较，什么时候添加直方图直方图，以及图如何帮助优化器选择更好的执行计划引入直方图。直方图的作用直方图用来描述数据在数据表中各列上的分布情况。这些关于数据分布的内容可以帮助优化器更准确地估计表中有多少数据将被给定的where子句或连接条件过滤掉。这里需要明确一点，直方图并不能像索引一样帮助减少访问的行数，它只是帮助优化器选择更合适的执行计划。MySQL8开始支持直方图，但其实直方图在MySQL中并没有在其他数据库中有用，因为MySQL可以通过indexdive直接访问索引对应的B+树，计算出某个扫描区间内对应索引记录的条数行数，因此直方图不能与同一列上的索引一起使用，优化器更喜欢使用索引。与索引相比，直方图有何优势？MySQL的索引不仅可以有效地估计索引范围内的行数，还有助于减少访问的记录数。为什么要引入直方图？与索引相比，直方图的一个优点是在确定过滤条件返回的行数时，直方图的成本低于索引。直方图的统计信息很容易被优化器利用，索引需要在确定查询计划时执行。深入估计行数并在每次执行查询时重复此操作。说到这里大家可能会有疑问，不是有指标统计吗，为什么每次都要进行一次下潜操作来估算呢？其实MySQL就是这样设计的。有一个参数eq_range_index_dive_limit（默认值200）。对于索引列，当存在等于或大于该参数设置的区间范围过滤条件时，优化器将从潜水切换为仅使用索引统计来估计匹配行数。因为MySQL认为使用indexdive来估算比统计信息更准确，但是当过滤区间条件比较多的时候，比如用in来过滤，entry值达到1000，就会有1000个过滤区间，所以成本指数跳水过高。MySQL我倾向于使用索引统计。好像跑题了，回归正文。与索引相比，直方图的第二个优点是索引的维护成本高，在进行DML操作时需要维护索引，所以索引过多会影响DML操作的效率.只是收集，对DML操作没有影响。与索引相比，直方图的第三个好处是索引增加了表空间文件的大小，而直方图统计信息占用的空间可以忽略不计。没有索引也没有直方图，优化器如何估计返回的行数？如果过滤条件既没有索引也没有直方图，优化器如何估计过滤率？优化器会根据MySQL代码中内置的默认规则来估算过滤率，相当于按照自己的想法盲目猜测。默认过滤比例以列表形式显示如下：过滤类型过滤比例等效过滤（=）10%不等于（<>or!=）90%不等式（）33.33%Between11.11%INMin(输入*10,50)例如；执行语句：explainselect*fromt1wheretemporary='N';对t1表的temporary字段进行等价过滤。由上图可以看出，优化器根据规则预估过滤比例，filtered为10%，即预估返回行数为rows*filtered/100=7183行，而实际返回行数返回的行是72214，过滤=72214/72435=99.69。这种差异可以用巨大来形容。收集完列上柱状图的统计信息后，查看执行计划中的filtered。这时候filtered就比较准确了。当数据分布不均匀时，MySQL采用相同的处理规则来响应变化，估计肯定是相当不准确的。因此，在选择执行计划时有可能做出错误的决定。索引的维护是有代价的，不可能对每一个涉及条件的列都加索引。然后，在不适合创建索引的列上创建直方图，可以作为索引的补充，帮助优化器选择更好的执行计划。什么时候应该添加直方图？因为MySQL在SQL优化阶段会对索引进行dive操作来估计返回的行数，所以直方图在MySQL中使用的空间是有限的，那么应该在哪些列上创建直方图才有效呢？扮演直方图的角色呢？创建直方图的最佳候选者是数据分布不均匀的列，或者具有太多值以致于优化器的粗略估计不能很好地估计数据的选定行的列。选择性较低的列（否则索引可能是更好的选择）用于在where子句或连接条件中过滤表的数据。优化器不能在不对列进行过滤的情况下使用直方图。数据分布随时间稳定的列。直方图统计数据不会自动更新。如果在数据分布变化频繁的列上添加直方图，直方图统计可能会不准确。直方图的应用实例其实直方图对于单表访问用处不是很大，主要是表join的时候。当表格的连接方式有多种选择时，直方图可以帮助确定哪种选择是最好的。举个例子来说明。两个表a1和a2是关联查询。两张表的结构信息如下图所示：关联查询语句：select*froma1,a2wherea1.id=a2.idanda1.temporary='N'anda2.status='NOVALID';两张表在关联条件的所有字段上都有索引，还有额外的过滤条件。当优化器选择使用嵌套连接时，有两种可能性。一个是a1带动a2，一个是a2带动a1。哪种方法比较好，要看两张表使用过滤条件过滤后，哪张表返回的行数最少，因为我们知道，当使用嵌套连接时，小表带动大表的效率是高的。但是a1表的临时字段和a2表的状态字段数据分布不均匀，选择性差，不适合做索引。这是直方图派上用场的地方。已知a1表的temporary='N'条件过滤性差，a2表的status='NOVALID'条件过滤性好，用a2驱动a1效率会更高。因为当没有直方图的时候，优化器不知道谁的过滤性能最好。如果按照等价过滤的默认规则filtered=10进行过滤，在选择执行计划时有可能做出错误的决定。我们先来看一下不采集直方图时的执行计划。如下图所示：从图中可以看出，优化器选择a1驱动a2，a1表过滤后估计行数为7049，实际为72214，扫描a2表为72214次作为从动表。总执行时间为280毫秒。接下来对a2表的status列采集直方图，然后执行关联查询，如下图：从上图可以看出，有了直方图后，优化器选择a2来驱动a1，而a2表过滤后的估计值行数为8，实际为8。a1作为驱动表，只扫描了8次。总执行时间87ms，效率提升3倍。你意识到直方图的作用了吗？直方图告诉优化器数据的分布情况，使优化器的估计更加准确，进而让优化器做出明智的决策。
