MySQL8.0新特性學習筆記（三）：直方圖

一，直方圖簡介

二，直方圖的生成

三，直方圖的删除

四，直方圖的存儲

五，舉例說明直方圖的作用

六，直方圖和索引

七，獲得直方圖的資訊

一，直方圖簡介

直方圖，histogram，直譯就是柱狀圖，國内多翻譯為直方圖。

直方圖不是圖，是對表的某個字段資料分布的統計，這種資料統計是基于某字段的實際存儲資料分布得出的。

這種分布統計可以幫助查詢優化器掌握字段真實的情況，進而得出更高效而切合實際的執行計劃。

在有直方圖之前，優化器對某字段的資料分布預估有可能是不準确的，甚至可能和實際情況差距比較大，錯誤的預估會導緻不高效的執行計劃。

從Explain sql的角度看，建立直方圖後會影響Explain結果的filter字段，使之成為較為真實的比例，進而影響執行計劃的選擇。

直方圖分為單值直方圖（singleton histograms）和等高直方圖（equi-height histograms）。

不知道為什麼國内多把singleton histograms翻譯成等寬直方圖。

二，直方圖的生成

生成直方圖的sql語句是這樣的：

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

可以看到，可以同時對多個字段生成直方圖。

BUCKETS是統計結果的分組個數，如果不寫則預設100，可選值從1到1024。數字越大統計結果越精确，官方建議從小值慢慢增加到可以滿足預期的大小。

直方圖生成的時候，MySQL會把所有相關資料讀入記憶體中，然後進行分組統計。

8.0新增的參數histogram_generation_max_mem_size定義了直方圖能用多大的記憶體。

如果字段值超過限定記憶體，則會取樣，樣本随機，也就是說兩次生成的直方圖内容可能不一樣。

官方例子：

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)

sampling-rate是查詢直方圖取樣比例用的，這次的查詢結果代表此字段的直方圖是大概取樣4.8%得到的。

一個字段隻能生成一個直方圖，重複生成會把前者覆寫。

生成直方圖的sql中如果有不存在的字段，MySQL依然會為sql中存在的字段生成直方圖。

生成直方圖時，如果字段的distinct值小于sql指定的bucket，MySQL會選擇生成單值直方圖（singleton histograms），否則生成等高直方圖（equi-height histograms）。

三，直方圖的删除

sql如下：

ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];

随時可以删，不影響資料庫其他操作。

四，直方圖的存儲

生成的直方圖儲存在information_schema.COLUMN_STATISTICS表中，内容是JSON格式。

下面說一下JSON的格式，單值直方圖和等高直方圖的JSON格式略有不同。

以下是一個單值直方圖的JSON例子：

{
  "buckets": [
    [
      1,
      0.3333333333333333
    ],
    [
      2,
      0.6666666666666666
    ],
    [
      3,
      1
    ]
  ],
  "null-values": 0,
  "last-updated": "2017-03-24 13:32:40.000000",
  "sampling-rate": 1,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 128,
  "data-type": "int",
  "collation-id": 8
}

以下是一個等高直方圖的例子：

{
  "buckets": [

    [
      38671,
      99756,
      0.249795,
      17002
    ],
    [
      99757,
      100248,
      0.500035,
      492
    ],
    [
      100249,
      100743,
      0.749945,
      495
    ],
    [
      100744,
      172775,
      1.0,
      16630
    ]
  ],
  "data-type": "int",
  "null-values": 0.0,
  "collation-id": 8,
  "last-updated": "2018-09-22 09:59:30.857797",
  "sampling-rate": 1.0,
  "histogram-type": "equi-height",
  "number-of-buckets-specified": 4
}

二者在buckets參數中存儲的内容不一樣，其他部分的格式是一樣的。

在單值直方圖的buckets中，有2個值：

第1個值：bucket代表的值。類型由字段類型決定。
第2個值：自己的bucket和前面的bucket中的值的占比之和，是一個逐漸增加的值。取值在0.0到1.0之間。顯然最後一個bucket中比例一定是1。

在等高直方圖的buckets中，有4個值：

第1個值：bucket包括的值的範圍的下限。
第2個值：bucket包括的值的範圍的上限。
第3個值：自己bucket取值範圍和前面bucket取值範圍中的值的占比之和，是一個逐漸增加的值。取值在0.0到1.0之間。同單值直方圖的第2個值，最後一個bucket的比例是1。
第4個值：bucket包括的值的distinct數。

在buckets之外的參數：

null-values：null值比例。取值在0.0到1.0之間。0代表沒有null值。
last-updated：直方圖更新時間。格式：YYYY-MM-DD hh:mm:ss.uuuuuu。
sampling-rate：取樣比例。取值在0.0到1.0之間。1.0代表bucket值完全覆寫字段值，沒有取樣。
histogram-type：直方圖類型。singleton代表單值直方圖，每個bucket都代表一個值，字段distinct值小于sql指定bucket數時使用該類型。equi-height代表等高直方圖，每個bucket都代表一個值的範圍，字段distinct值大于sql指定bucket數時使用該類型。
number-of-buckets-specified：生成直方圖的sql語句中指定的bucket數量。
data-type：直方圖中的資料類型。從磁盤中讀取并存入記憶體中時會用到。可能的值有：int，uint (unsigned integer)，double，decimal，datetime，string (包括character and binary strings)。
collation-id：直方圖資料中的字元集id。和INFORMATION_SCHEMA.COLLATIONS表中id對應。直方圖中的資料是String類型時會有用。

直方圖中的值可以用MySQL8.0加入的JSON文法獲得。

五，舉例說明直方圖的作用

官方舉了這麼一個例子：

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)

這個sql是用來比較上午9點和下午15點的銷售情況，可見執行時間是1.48秒。

然後給wp_char_count建一個直方圖再查一遍：

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秒，效率提高非常多，原因是這樣的：

建直方圖的字段就是sql中取值5000到5200的字段，在沒有直方圖提供統計資訊的情況下，MySQL預估該字段值在5000到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操作，進而提高了查詢效率。

六，直方圖和索引

使用索引也可以把查詢效率提高不少，效果不比直方圖差，二者的差別有：

1，維護索引是有成本的。新增，删除，修改字段時同時需要維護索引，影響效率。對比之下直方圖一經建立就不再修改，不會影響操作效率（顯然是把雙刃劍，會存在資料不準确的情況）。

2，使用索引時，優化器使用index dives的方式預估大概範圍，這個操作也是有成本的。特别是in語句且清單有大量值時，直方圖的效果更強一點。

七，獲得直方圖的資訊

1，擷取更新時間

官方提供了這樣的例子，可以擷取直方圖的生成時間或更新時間：

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 |
+----------------------------+

2，查詢直方圖bucket

官方提供了這樣的例子：

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 |
+------------+--------------+-----------------------+---------------------+

其中，'$."number-of-buckets-specified"'是生成直方圖的sql中指定的bucket數量，

JSON_LENGTH(HISTOGRAM, '$.buckets')是生成的直方圖中實際的bucket數量。

實際bucket數不會超過sql中指定的bucket上限。

使用直方圖時要注意的點

通過前面直方圖和索引的比較可以看到，直方圖自生成之後就不再随資料變化而改變，除非手動重做直方圖。

這樣帶來的資料不準确的問題是需要開發者自己考慮的。比如什麼時候需要重新整理直方圖，以頻率重新整理等。

因為生成直方圖消耗的cpu和記憶體資源都挺大，使用的時候要注意。

完

MySQL8.0全部學習筆記：

MySQL8.0新特性學習筆記（一）：binlog複制政策優化

MySQL8.0新特性學習筆記（二）：視窗函數

MySQL8.0新特性學習筆記（三）：直方圖

MySQL8.0新特性學習筆記（四）：Hash Join

MySQL8.0新特性學習筆記（五）：JSON格式簡介和JSON函數詳解

MySQL8.0新特性學習筆記（六）：新特性介紹

MySQL8.0新特性學習筆記（三）：直方圖

一，直方圖簡介

二，直方圖的生成

三，直方圖的删除

四，直方圖的存儲

五，舉例說明直方圖的作用

六，直方圖和索引

七，獲得直方圖的資訊

繼續閱讀

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

opencv——圖像的灰階處理（線性變換/拉伸/直方圖/均衡化）

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案