0. 什么是直方图1. 直方图怎么工作2. 同时有索引和直方图会怎样3. 如何提高直方图的统计精确度
0. 什么是直方图
直方图（Histogram）是数据库提供的一种（索引之外的）基础统计信息，用于描述列上的数据分布情况。它最典型的场景是估算查询谓词的选择率，以便选择合适的执行计划。也就是说，某个列可以不创建索引但创建直方图，也可以帮助提升查询效率。
MySQL 8.0开始支持直方图，这是个很大的进步。直方图可以针对某个列记录其数据分布统计信息，例如有个列的值是从1到1万，那么可以利用直方图分成100个桶（bucket），每个桶中统计这1万个值是怎么分布的，以及每个桶中的最大值、最小值、占比等信息。虽然可以利用索引优化SQL效率，但索引维护的代价更高，索引要保持更新，而直方图可以按需手动更新。索引统计信息也有不可靠的时候，例如存在数据倾斜，或者统计延迟等问题。另外，在有需要的时候，可以在每个有需要的列上创建直方图，但却不太可能同时创建多个单列索引，那样代价太高了。例如下面这个执行计划：
[root@yejr.run]> explain select * from t1 where seq = 1234; +------+---------------+------+---------+------+--------+----------+-------------+ | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+---------------+------+---------+------+--------+----------+-------------+ | ALL | NULL | NULL | NULL | NULL | 299876 | 10.00 | Using where | +------+---------------+------+---------+------+--------+----------+-------------+ [root@yejr.run]> select * from t1 where seq = 1234; Empty set (0.097 sec)
在还没创建直方图之前，seq列上同时也没有索引，这时是全表扫描，注意到 filtered 列的值是10%。
创建完直方图之后，再看这个执行计划：
[root@yejr.run]> explain select * from t1 where seq = 1234; +------+---------------+------+---------+------+--------+----------+-------------+ | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+---------------+------+---------+------+--------+----------+-------------+ | ALL | NULL | NULL | NULL | NULL | 299876 | 0.00 | Using where | +------+---------------+------+---------+------+--------+----------+-------------+ [root@yejr.run]> select * from t1 where seq = 1234; Empty set (0.079 sec)
注意到 filtered 列值变成了 0%，并且实际耗时是原来的81%，虽然绝对值也不算小，但相对于原来的全表扫描也还是要节省了将近20%耗时。
所以说，直方图还是很有意义的，当然了，直方图还是无法代替索引，只在一些特定的场景里比较有用。
1. 直方图怎么工作
MySQL支持两种直方图模式：等宽、等高。等宽直方图是每个桶保存一个值以及这个值累积频率，等高直方图每个桶需要保存不同值的个数，上下限以及累积频率等。MySQL会自动选用哪种类型的直方图，无需也不能指定。一般来说，数据数据分布范围比较大的话就采用等高，反之，如果数据分布比较小就采用等宽。直方图的统计信息物理表 column_statistics 存储在mysql表空间中，无法直接读写，但可以访问 information_schema.COLUMN_STATISTICS 视图来查看统计结果。
[root@yejr.run]> show create view column_statistics\G *************************** 1. row *************************** View: COLUMN_STATISTICS Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `COLUMN_STATISTICS` AS select `mysql`.`column_statistics`.`schema_name` AS ... character_set_client: utf8 collation_connection: utf8_general_ci
每条记录对应一个直方图统计结果，用JSON格式保存。
此外，还有个参数 histogram_generation_max_mem_size 用来控制创建直方图时可用的内存，该参数很重要，后面会讲到。
截止MySQL 8.0.19版本，直方图支持多种数据类型和场景，甚至包括虚拟列。但不支持以下几种情况：
- 加密表、临时表。
- JSON数据类型、空间（spatial）数据类型。
- 已创建唯一索引的单列。
来个一个创建失败的例子：
[root@yejr.run]> analyze table t2 update histogram on nu; +---------+-----------+----------+-----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-----------+----------+-----------------------------------------------------------+ | yejr.t2 | histogram | Error | The column 'nu' is covered by a single-part unique index. | +---------+-----------+----------+-----------------------------------------------------------+
MySQL干脆利落地拒绝了这种反智行为。
当然了，如果是一个列创建了非唯一辅助索引，就可以创建直方图，不会冲突。
来创建个正式直方图：
+---------+-----------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------+-----------+----------+------------------------------------------------+ | yejr.t1 | histogram | status | Histogram statistics created for column 'seq'. | +---------+-----------+----------+------------------------------------------------+
再看下 COLUMN_STATISTICS 中存储的统计信息：
[root@yejr.run]> select SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_PRETTY(HISTOGRAM) from COLUMN_STATISTICS\G *************************** 1. row *************************** SCHEMA_NAME: yejr --库 TABLE_NAME: t1 --表 COLUMN_NAME: seq --列 JSON_PRETTY(HISTOGRAM): { "buckets": [ [ --第一个桶(bucket)中的统计信息 1, --最小值 9710, --最大值 0.009996666666666666, --累积占比，0.99% 2571 --第一个桶中累积几个值 ], ... [ --第100个桶中的统计信息 989875, 999994, 0.9996666666666667, --因为该列包含部分NULL值，所以这里不是1.0（100%） 2580 ] ], "data-type": "int", --该列数据类型，是INT "null-values": 0.0003333333333333333, --是否包含NULL值，或者NULL值的占比 "collation-id": 8, "last-updated": "2020-04-21 07:21:53.084054", --直方图最后更新时间 "sampling-rate": 1.0, --采样比例 100% "histogram-type": "equi-height", --等高直方图 "number-of-buckets-specified": 100 --共有100个桶 }
上面这个等高直方图，共100个桶，每个桶的数据量从2571 ~ 2620不等，总数据量259550，占比99.9667%。此外，还有大约0.033%为NULL的记录。
再来个等宽的直方图
"buckets": [ [ 5, --存储每个值 0.07692307692307693 ---该值频率 ], [ 25, 0.15384615384615385 ], ...... ], "data-type": “int", "null-values": 0.0, "collation-id": 8, "last-updated": "2019-07-02 06:44:15.134130”, "sampling-rate": 1.0, --数据采样比例 "histogram-type": “singleton", --等宽 "number-of-buckets-specified": 100 }
等宽直方图里，每个桶里记录是各个值的分布信息。
2. 同时有索引和直方图会怎样
某天，群里有同学在问，假如有个列同时创建了直方图和索引，优化器会怎么选择呢？
在回答之前，可以先开通脑筋想几秒钟…
事实上，真是这样的话，优化器会选择索引而非直方图。因为索引的统计信息相对"更及时"，也可能"更精确"，因为直方图是需要手动更新的，没办法保证"及时性"。当然了，我没去看源码，仅是我猜的，并通过试验确认的。
假设上面创建直方图的列 seq，同时也创建了索引，在开启 optimizer_trace 之后，可以看到两个执行计划之间的区别（我只选取了部分内容）
