MySQL 8.0 新特性之统计直方图

|  译者简介

译者    韩杰·沃趣科技MySQL数据库工程师

熟悉mysql体系架构、主从复制，熟悉问题定位与解决

出品   沃趣科技

原文链接：

https://mysqlserverteam.com/histogram-statistics-in-mysql/

|  概览

MySQL8.0实现了统计直方图。利用直方图，用户可以对一张表的一列做数据分布的统计，特别是针对没有索引的字段。这可以帮助查询优化器找到更优的执行计划。统计直方图的主要使用场景是用来计算字段选择性，即过滤效率。

可以通过以下方式来创建或者删除直方图：

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

buckets默认是100。统计直方图的信息存储在数据字典表"column_statistcs"中,可以通过视图information_schema.COLUMN_STATISTICS访问。直方图以灵活的JSON的格式存储。ANALYZE TABLE会基于表大小自动判断是否要进行取样操作。ANALYZE TABLE也会基于表中列的数据分布情况以及bucket的数量来决定是否要建立等宽直方图（singleton）还是等高直方图（equi-height）。

| 什么是直方图

数据库中，查询优化器负责将SQL转换成最有效的执行计划。有时候，查询优化器会走不到最优的执行计划，导致花费了更多不必要的时间。造成这种情况的主要原因是，查询优化器有时无法准确的知道以下几个问题的答案：

• 每个表有多少行？

• 每一列有多少不同的值？

• 每一列的数据分布情况？

举例说明：一张简单的表，两个字段，一个字段是person_id，另一个字段是time_of_day，表示睡觉时间

CREATE TABLE bedtime (
person_id INT,
time_of_day TIME);

对于time_of_day列，大部分人上床时间会在晚上11:00左右。所以下面第一个查询会比第二个查询返回更多的行数：

1) SELECT * FROM bedtime WHERE time_of_day BETWEEN "22:00:00" AND "23:59:00"
2) SELECT * FROM bedtime WHERE time_of_day BETWEEN "12:00:00" AND "14:00:00"

如果没有统计数据，优化器会假设time_of_day的值是均匀分配的，即一个人的上床时间在下午3点和晚上11点的概率差不多。如何才能使查询优化器知道数据的分布情况？一个解决方法就是在列上建立统计直方图。

直方图能近似获得一列的数据分布情况，从而让数据库知道它含有哪些数据。直方图有多种形式，MySQL支持了两种：等宽直方图（singleton）、等高直方图（equi-height）。直方图的共同点是，它们都将数据分到了一系列的buckets中去。MySQL会自动将数据划到不同的buckets中，也会自动决定创建哪种类型的直方图。

|  如何创建和删除统计直方图

为了管理统计直方图，ANALYZE TABLE命令新增了两个子句：

ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

第一个表示一次可以为一个或多个列创建统计直方图：

mysql> ANALYZE TABLE payment UPDATE HISTOGRAM ON amount WITH 32 BUCKETS;
+----------------+-----------+----------+---------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                          |
+----------------+-----------+----------+---------------------------------------------------+
| sakila.payment | histogram | status   | Histogram statistics created for column 'amount'. |
+----------------+-----------+----------+---------------------------------------------------+
1 row in set (0.27 sec)
mysql> ANALYZE TABLE payment UPDATE HISTOGRAM ON amount, payment_date WITH 32 BUCKETS;
+----------------+-----------+----------+---------------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                                |
+----------------+-----------+----------+---------------------------------------------------------+
| sakila.payment | histogram | status   | Histogram statistics created for column 'amount'.       |
| sakila.payment | histogram | status   | Histogram statistics created for column 'payment_date'. |
+----------------+-----------+----------+---------------------------------------------------------+

buckets的值必须指定，可以设置为1到1024，默认值是100。 

对于不同的数据集合，buckets的值取决于以下几个因素：

• 这列有多少不同的值

• 数据的分布情况

• 需要多高的准确性

但是，某些buckets的值能提升的关于数据分布情况的准确性相当低。所以，建议的做法是，开始的时候将buckets的值设的低一点，比如32，然后如果没有满足期望，再往上增大。

上面这个例子中，我们对于amount列建立了两次直方图。第一个语句，建立了一个新的直方图；第二个语句，amount列的直方图被重写了。

如果需要删除已经创建的直方图，用DROP HISTOGRAM就可以实现：

mysql> ANALYZE TABLE payment DROP HISTOGRAM ON payment_date;
+----------------+-----------+----------+---------------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                                |
+----------------+-----------+----------+---------------------------------------------------------+
| sakila.payment | histogram | status   | Histogram statistics removed for column 'payment_date'. |
+----------------+-----------+----------+---------------------------------------------------------+

UPDATE HISTOGRAM可以一次性为多个列创建直方图。如果命令中间写错，ANALYZE TABLE仍然会起作用。比如，你指定了三列，但第二列不存在。MySQL仍然会为第一列和第三列创建直方图。

mysql> ANALYZE TABLE customer UPDATE HISTOGRAM ON c_birth_day, c_foobar, c_birth_month WITH 32 BUCKETS;
+----------------+-----------+----------+----------------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                                 |
+----------------+-----------+----------+----------------------------------------------------------+
| tpcds.customer | histogram | status   | Histogram statistics created for column 'c_birth_day'.   |
| tpcds.customer | histogram | status   | Histogram statistics created for column 'c_birth_month'. |
| tpcds.customer | histogram | Error    | The column 'c_foobar' does not exist.                    |
+----------------+-----------+----------+----------------------------------------------------------+
3 rows in set (0.15 sec)

| 数据库内部发生了什么

当你读过MySQL手册，你可能已经注意到新的系统变量histogram_generation_max_mem_size。当用户建立统计直方图，这个值是用来控制大约多少内存能允许被使用。那么，为什么要控制这个呢？

当你在建立直方图的时候，MySQL server会将所有数据读到内存中，然后在内存中进行操作，包括排序。如果对一个很大的表建立直方图，可能会有风险将几百M的数据都读到内存中，但这是不明智的。为了规避这个风险，MySQL会根据给定的histogram_generation_max_mem_size的值计算该将多少行数据读到内存中。如果根据当前histogram_generation_max_mem_size的限制，MySQL认为只能读一部分数据，那么MySQL会进行取样。通过“sampling-rate”属性，可以观察到取样比率。

mysql> SET histogram_generation_max_mem_size = 1000000;
Query OK, 0 rows affected (0.00 sec)
mysql> ANALYZE TABLE customer UPDATE HISTOGRAM ON c_birth_country WITH 16 BUCKETS;
+----------------+-----------+----------+------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+------------------------------------------------------------+
| tpcds.customer | histogram | status | Histogram statistics created for column 'c_birth_country'. |
+----------------+-----------+----------+------------------------------------------------------------+
1 row in set (0.22 sec)
mysql> SELECT histogram->>'$."sampling-rate"'
-> FROM information_schema.column_statistics
-> WHERE table_name = "customer"
-> AND column_name = "c_birth_country";
+---------------------------------+
| histogram->>'$."sampling-rate"' |
+---------------------------------+
| 0.048743243211626014 |
+---------------------------------+
1 row in set (0.00 sec)

优化器创建了一个直方图，大约读了c_birth_country列4.8%的数据。取样是不确定的，因此意义不大。同样的数据，同样的两条语句‘‘ANALYZE TABLE tbl UPDATE HISTOGRAM …’’，如果用了取样，得到的直方图可能就不一样。

|  查询案例

统计直方图可以带来些什么？我们可以看个例子，这个例子中用了直方图，在执行时间上会有很大的不同。

环境：

• TPC-DS Benchmark with scale factor of 1

• Intel Core i7-4770

• Debian Stretch

• MySQL 8.0 RC1

• innodb_buffer_pool_size = 2G

• optimizer_switch = "condition_fanout_filter=on"

|  Query 90

查询如下：上午售卖的数量与晚上售卖的数量的比率。

mysql> SELECT CAST(amc AS DECIMAL(15, 4)) / CAST(pmc AS DECIMAL(15, 4)) am_pm_ratio
-> FROM (SELECT COUNT(*) amc
->              FROM web_sales,
->                          household_demographics,
->                          time_dim,
->                          web_page
->             WHERE ws_sold_time_sk = time_dim.t_time_sk
->                          AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
->                          AND ws_web_page_sk = web_page.wp_web_page_sk
->                          AND time_dim.t_hour BETWEEN 9 AND 9 + 1
->                          AND household_demographics.hd_dep_count = 2
->                          AND web_page.wp_char_count BETWEEN 5000 AND 5200) at,
->              (SELECT COUNT(*) pmc
->               FROM web_sales,
->                          household_demographics,
->                          time_dim,
->                          web_page
->              WHERE ws_sold_time_sk = time_dim.t_time_sk
->                            AND ws_ship_hdemo_sk = household_demographics.hd_demo_sk
->                            AND ws_web_page_sk = web_page.wp_web_page_sk
->                            AND time_dim.t_hour BETWEEN 15 AND 15 + 1
->                            AND household_demographics.hd_dep_count = 2
->                            AND web_page.wp_char_count BETWEEN 5000 AND 5200) pt
-> ORDER BY am_pm_ratio
-> LIMIT 100;
+-------------+
| am_pm_ratio |
+-------------+
| 1.27619048 |
+-------------+
1 row in set (1.48 sec)

可以看到，查询花费了1.5秒左右。看起来不算多，但是通过在一列上建立直方图，可以让执行速度快三倍。

mysql> ANALYZE TABLE web_page UPDATE HISTOGRAM ON wp_char_count WITH 8 BUCKETS;
+----------------+-----------+----------+----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+----------------------------------------------------------+
| tpcds.web_page | histogram | status | Histogram statistics created for column 'wp_char_count'. |
+----------------+-----------+----------+----------------------------------------------------------+
1 row in set (0.06 sec)
mysql> SELECT ...
+-------------+
| am_pm_ratio |
+-------------+
| 1.27619048 |
+-------------+
1 row in set (0.50 sec)

通过这个直方图，查询花费了0.5秒左右。原因呢？主要的原因是，查询语句中的谓词“web_page.wp_char_count BETWEEN 5000 AND 5200”。没有直方图的时候，优化器会假设web_page表中符合谓词“web_page.wp_char_count BETWEEN 5000 AND 5200”的数据占到总数据11.11%左右。但，这是错误的。用下面的查询语句，可以看到实际上满足条件的数据只有1.6%。

mysql> SELECT
-> (SELECT COUNT(*) FROM web_page WHERE web_page.wp_char_count BETWEEN 5000 AND 5200)
-> /
-> (SELECT COUNT(*) FROM web_page) AS ratio;
+--------+
| ratio |
+--------+
| 0.0167 |
+--------+
1 row in set (0.00 sec)

通过直方图，优化器会知道这个信息，并且更早进行表join，因此执行时间快了三倍。

|   Query 61

查询如下：在给定的年份和月份，有和没有广告宣传的情况下货物的售卖比率。

mysql> SELECT promotions,                                                                                                                                                                                                ->        total,
->        CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100
-> FROM   (SELECT SUM(ss_ext_sales_price) promotions
->         FROM   store_sales,
->                store,
->                promotion,
->                date_dim,
->                customer,
->                customer_address,
->                item
->         WHERE  ss_sold_date_sk = d_date_sk
->                AND ss_store_sk = s_store_sk
->                AND ss_promo_sk = p_promo_sk
->                AND ss_customer_sk = c_customer_sk
->                AND ca_address_sk = c_current_addr_sk
->                AND ss_item_sk = i_item_sk
->                AND ca_gmt_offset = -5
->                AND i_category = 'Home'
->                AND ( p_channel_dmail = 'Y'
->                       OR p_channel_email = 'Y'
->                       OR p_channel_tv = 'Y' )
->                AND s_gmt_offset = -5
->                AND d_year = 2000
->                AND d_moy = 12) promotional_sales,
->        (SELECT SUM(ss_ext_sales_price) total
->         FROM   store_sales,
->                store,
->                date_dim,
->                customer,
->                customer_address,
->                item
->         WHERE  ss_sold_date_sk = d_date_sk
->                AND ss_store_sk = s_store_sk
->                AND ss_customer_sk = c_customer_sk
->                AND ca_address_sk = c_current_addr_sk
->                AND ss_item_sk = i_item_sk
->                AND ca_gmt_offset = -5
->                AND i_category = 'Home'
->                AND s_gmt_offset = -5
->                AND d_year = 2000
->                AND d_moy = 12) all_sales
-> ORDER  BY promotions,
->           total
-> LIMIT  100;
+------------+------------+--------------------------------------------------------------------------+
| promotions | total      | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 |
+------------+------------+--------------------------------------------------------------------------+
| 3213210.07 | 5966836.78 |                                                              53.85114741 |
+------------+------------+--------------------------------------------------------------------------+
1 row in set (2.78 sec)

可以看到，查询花费了2.8秒左右。但是，查询优化器不知道s_gmt_offset列只有一个不同的值。没有统计数据的情况下，优化器会用所谓的“hard-coded guesstimates”,会假设10%的数据符合条件“ca_gmt_offset = -5“。如果在这个列上增加一个直方图，优化器会知道所有的数据都符合条件，因此会走一个更好的执行计划。

mysql> ANALYZE TABLE store UPDATE HISTOGRAM ON s_gmt_offset WITH 8 BUCKETS;
+-------------+-----------+----------+---------------------------------------------------------+
| Table       | Op        | Msg_type | Msg_text                                                |
+-------------+-----------+----------+---------------------------------------------------------+
| tpcds.store | histogram | status   | Histogram statistics created for column 's_gmt_offset'. |
+-------------+-----------+----------+---------------------------------------------------------+
1 row in set (0.06 sec)
mysql> SELECT ...
+------------+------------+--------------------------------------------------------------------------+
| promotions | total      | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 |
+------------+------------+--------------------------------------------------------------------------+
| 3213210.07 | 5966836.78 |                                                              53.85114741 |
+------------+------------+--------------------------------------------------------------------------+
1 row in set (1.37 sec)

有了直方图，查询花了不到1.4秒，差不多提升了2倍。原因是：

• 第一个执行计划，优化器选择了第一个派生表在store表上做了全表扫描，然后对表item, store_sales, date_dim, customer，customer_address分别做了主键查找。

• 但是，当MySQL意识到store表会比它猜测的返回更多的数据时，优化器会在item表上做全表扫描，然后对store_sales, store, date_dim, customer，customer_address 分别做主键查找。

|  为什么不用索引？

索引往往也能做上述工作，比如：

mysql> CREATE INDEX s_gmt_offset_idx ON store (s_gmt_offset);
Query OK, 0 rows affected (0.53 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SELECT ...
+------------+------------+--------------------------------------------------------------------------+
| promotions | total      | CAST(promotions AS DECIMAL(15, 4)) / CAST(total AS DECIMAL(15, 4)) * 100 |
+------------+------------+--------------------------------------------------------------------------+
| 3213210.07 | 5966836.78 |                                                              53.85114741 |
+------------+------------+--------------------------------------------------------------------------+
1 row in set (1.41 sec)

但是，用直方图而不是索引有以下两个原因：

• 维护一个索引有代价。每一次的insert、update、delete都会需要更新索引，会对性能有一定的影响。而直方图一次创建永不更新，除非明确去更新它。所以不会影响insert、update、delete的性能。

• 如果有索引，优化器用使用index dives技术来估算符合条件范围的记录数量。这种方式也是有代价的，特别是查询语句条件中有很长的IN列表。直方图相对而言代价小，因此可能更合适。

|  检索统计直方图

统计直方图以JSON的形式存在数据字典中。可以用内建的JSON函数built-in JSON functions从直方图获取一些信息。举例来说，如果需要知道amount列的直方图的创建或者更新时间，可以用JSON unquoting extraction operator来获取信息：

mysql> SELECT
->   HISTOGRAM->>'$."last-updated"' AS last_updated
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE
->   SCHEMA_NAME = "sakila"
->   AND TABLE_NAME = "payment"
->   AND COLUMN_NAME = "amount";
+----------------------------+
| last_updated               |
+----------------------------+
| 2017-09-15 11:54:25.000000 |
+----------------------------+

如果要查找实际有多少个buckets，以及用analyze table时指定了多少个buckets，可以如下：

mysql> SELECT
->   TABLE_NAME,
->   COLUMN_NAME,
->   HISTOGRAM->>'$."number-of-buckets-specified"' AS num_buckets_specified,
->   JSON_LENGTH(HISTOGRAM, '$.buckets') AS num_buckets_created
-> FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
-> WHERE
->   SCHEMA_NAME = "sakila";
+------------+--------------+-----------------------+---------------------+
| TABLE_NAME | COLUMN_NAME  | num_buckets_specified | num_buckets_created |
+------------+--------------+-----------------------+---------------------+
| payment    | amount       | 32                    |                  19 |
| payment    | payment_date | 32                    |                  32 |
+------------+--------------+-----------------------+---------------------+

经测试，num_buckets_created与字段的distinct值很接近，近似相等；但是num_buckets_created不会大于num_buckets_specified。如果num_buckets_created与num_buckets_specified相等，那么存在可能，在创建直方图的时候指定的buckets不够多，那么此时可以通过增加buckets的数量，来提高直方图的准确性。 
buckets可以设置为1到1024

|  优化器trace

如果你想要知道直方图做了什么，最简单的方式就是看一下执行计划：

mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day BETWEEN 1 AND 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 98633 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> ANALYZE TABLE customer UPDATE HISTOGRAM ON c_birth_day WITH 32 BUCKETS;
+----------------+-----------+----------+--------------------------------------------------------+
| Table          | Op        | Msg_type | Msg_text                                               |
+----------------+-----------+----------+--------------------------------------------------------+
| tpcds.customer | histogram | status   | Histogram statistics created for column 'c_birth_day'. |
+----------------+-----------+----------+--------------------------------------------------------+
1 row in set (0.10 sec)
mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day BETWEEN 1 AND 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 98633 |    32.12 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到filtered列，从默认的11.11%变成了更精确的32.12%。但是，如果有多个条件，有些有直方图，有些没有，就比较难判断优化器做了什么改进：

mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day <= 20 AND c_birth_year = 1967;
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 98633 |     6.38 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果想要知道更多关于直方图统计的细节，可以使用trace:

mysql> SET OPTIMIZER_TRACE = "enabled=on";
Query OK, 0 rows affected (0.00 sec)
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE = 1000000;
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT * FROM customer WHERE c_birth_day <= 20 AND c_birth_year = 1967;
mysql> SELECT JSON_EXTRACT(TRACE, "$**.filtering_effect") FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+----------------------------------------------------------------------------------------+
| JSON_EXTRACT(TRACE, "$**.filtering_effect")                                            |
+----------------------------------------------------------------------------------------+
| [[{"condition": "(`customer`.`c_birth_day` <= 20)", "histogram_selectivity": 0.6376}]] |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

这里用了JSON_EXTRACT从trace里取出相关的部分。对于每个条件，直方图被使用的话，就会看到估算过的字段的选择性。在这个例子里，通过直方图，对“c_birth_day <= 20”条件，估算出63.76%的数据满足条件。事实上，与实际的数据分布情况基本一致：

mysql> SELECT
->   (SELECT count(*) FROM customer WHERE c_birth_day <= 20)
->   /
->   (SELECT COUNT(*) FROM customer) AS ratio;
+--------+
| ratio  |
+--------+
| 0.6376 |
+--------+
1 row in set (0.03 sec)

加入QQ技术交流群

扫码加入知数堂4群-王者峡谷

（QQ群号：650149401）