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

1. 数据分布不均匀，或者具有太多值，以至于优化器粗略估算无法很好的估计数据的选择行。
2. 选择性差的列（否则索引可能是更好的选择）
3. 用于在where子句或联接条件过滤表的数据。如果不对列进行过滤，则优化器无法使用直方图。
4. 随着时间推移，数据分布逐渐稳定的列。直方图统计信息不会自动更新。如果在数据分布频繁变化的列上添加直方图，则直方图统计信息可能不准确。