一文讀懂MySQL 8.0直方圖(1)

0. 什麼是直方圖1. 直方圖怎麼工作2. 同時有索引和直方圖會怎樣3. 如何提高直方圖的統計精确度

0. 什麼是直方圖

直方圖（Histogram）是資料庫提供的一種（索引之外的）基礎統計資訊，用于描述列上的資料分布情況。它最典型的場景是估算查詢謂詞的選擇率，以便選擇合适的執行計劃。也就是說，某個列可以不建立索引但建立直方圖，也可以幫助提升查詢效率。

MySQL 8.0開始支援直方圖，這是個很大的進步。直方圖可以針對某個列記錄其資料分布統計資訊，例如有個列的值是從1到1萬，那麼可以利用直方圖分成100個桶（bucket），每個桶中統計這1萬個值是怎麼分布的，以及每個桶中的最大值、最小值、占比等資訊。雖然可以利用索引優化SQL效率，但索引維護的代價更高，索引要保持更新，而直方圖可以按需手動更新。索引統計資訊也有不可靠的時候，例如存在資料傾斜，或者統計延遲等問題。另外，在有需要的時候，可以在每個有需要的列上建立直方圖，但卻不太可能同時建立多個單列索引，那樣代價太高了。例如下面這個執行計劃：

[[email protected]]> 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 |
+------+---------------+------+---------+------+--------+----------+-------------+

[[email protected]]> select * from t1 where seq = 1234;
Empty set (0.097 sec)      

在還沒建立直方圖之前，seq列上同時也沒有索引，這時是全表掃描，注意到 filtered 列的值是10%。

建立完直方圖之後，再看這個執行計劃：

[[email protected]]> 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 |
+------+---------------+------+---------+------+--------+----------+-------------+

[[email protected]]> 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 視圖來檢視統計結果。

[[email protected]]> 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）資料類型。
• 已建立唯一索引的單列。

來個一個建立失敗的例子：

[[email protected]]> 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 中存儲的統計資訊：

[[email protected]]> 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 之後，可以看到兩個執行計劃之間的差別（我隻選取了部分内容）