GreatSQL社群原創内容未經授權不得随意使用，轉載請聯系小編并注明來源。

一、導讀

MySQL 8.0.19 開始支援對InnoDB引擎表資料進行采樣以生成直方圖統計資訊。

直方圖（Histogram）是關系型資料庫中提供的一種基礎的統計資訊，最典型的用途是估計查詢謂詞的選擇率，以便選擇優化的查詢執行計劃。

常見的直方圖種類有：等寬直方圖、等高直方圖。

二、步驟

2.1 SQL語句

# 建立直方圖
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]

# 删除直方圖
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ...

2.2 直方圖案例

建立直方圖，更新就是等于建立，會進行重新采樣

mysql> analyze table t1 update histogram on tcol01 with 2 buckets;
+---------+-----------+----------+---------------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                          |
+---------+-----------+----------+---------------------------------------------------+
| test.t1 | histogram | status   | Histogram statistics created for column 'tcol01'. |
+---------+-----------+----------+---------------------------------------------------+
1 row in set (6.38 sec)

删除直方圖

mysql> analyze table t1 drop histogram on tcol01;
+---------+-----------+----------+---------------------------------------------------+
| Table   | Op        | Msg_type | Msg_text                                          |
+---------+-----------+----------+---------------------------------------------------+
| test.t1 | histogram | status   | Histogram statistics removed for column 'tcol01'. |
+---------+-----------+----------+---------------------------------------------------+
1 row in set (0.02 sec)

2.3 檢視直方圖統計資訊

檢視直方圖的視圖資訊

mysql> show create table information_schema.column_statistics\G
*************************** 1. row ***************************
                View: COLUMN_STATISTICS
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`COLUMN_STATISTICS` AS select `mysql`.`column_statistics`.`schema_name` AS `SCHEMA_NAME`,`mysql`.`column_statistics`.`table_name` AS `TABLE_NAME`,`mysql`.`column_statistics`.`column_name` AS `COLUMN_NAME`,`mysql`.`column_statistics`.`histogram` AS `HISTOGRAM` from `mysql`.`column_statistics` where (0 <> can_access_table(`mysql`.`column_statistics`.`schema_name`,`mysql`.`column_statistics`.`table_name`))
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.01 sec)

可以通過

information_schema.column_statistics

檢視，會列出所有直方圖資訊

mysql> select * from information_schema.column_statistics\G;
*************************** 1. row ***************************
SCHEMA_NAME: test
 TABLE_NAME: t_user
COLUMN_NAME: age
  HISTOGRAM: {"buckets": [[1, 0.00002000013333422223], [10, 0.23445489636597577], [11, 0.46630977539850266], [12, 0.5326868845792305], [13, 0.5991973279821865], [14, 0.665747771651811], [15, 0.7325715504770032], [16, 0.7999486663244422], [17, 0.8668091120607471], [18, 0.9329928866192441], [19, 0.9999766665111101], [127, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated": "2022-04-21 06:53:35.194420", "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 100}
......

2.3 直方圖分類

對于等寬直方圖，每個桶包含兩個值，大緻資訊如下

SCHEMA_NAME: test  # 庫名
 TABLE_NAME: t1    # 表名
COLUMN_NAME: tcol01 # 列名
  HISTOGRAM: {
    "buckets":[
        [
            0, # 1.桶的值，表中實際資料的取值。類型是取決于字段資料類型，比如下面是`int`類型。
            0.06585605673110825 # 2.取值頻率，桶的值出現的大緻頻率，double類型。
        ],
        ......
        ],
    "data-type":"int", # 資料類型
    "null-values":0,   # 是否有NULL值
    "collation-id":8,
    "last-updated":"2022-04-21 06:59:55.850333", # 桶最後更新時間，不會自動更新
    "sampling-rate":0.4059331843720921,  # 采樣率，如果是1，表示采集所有資料
    "histogram-type":"singleton",  # 桶類型，等寬
    "number-of-buckets-specified":100  # 桶數量
}

對于等高直方圖，每個桶中包含四個值，大緻資訊如下

SCHEMA_NAME: test
 TABLE_NAME: t1
COLUMN_NAME: tcol10
  HISTOGRAM: {
    "buckets":[
    [
            "2021-04-18 12:12:00.000000",  # 1.最小值
            "2021-04-22 05:05:56.000000",  # 2.最大值
            0.010002279268725782, # 3.桶的值出現的大緻頻率，double類型
            3523 # 4.桶值出現的次數
    ],
    ......
    ],
    "data-type":"datetime",
    "null-values":0,
    "collation-id":8,
    "last-updated":"2022-04-21 07:00:43.232745",
    "sampling-rate":0.18943548604030958,
    "histogram-type":"equi-height",  # 桶類型，等高
    "number-of-buckets-specified":100
}

2.4 使用限制

直方圖是對表進行操作，可以看下不同的表類型對直方圖的支援情況：

1、直方圖支援 InnoDB , NDB , MyISAM 表類型，支援 分區表 類型，不支援 views 類型。
2、加密表不支援建立，主要還是為了避免相關資料洩漏。
3、臨時表不支援建立，連接配接會話關閉後，臨時表就被清理了，也就沒有建立必要。
4、單列唯一索引、主鍵索引不支援建立直方圖，建立的時候會提示 Error ，并輸出錯誤提示 The column 'id' is covered by a single-part unique index.
5、幾何類型（空間資料）和JSON類型也暫時不支援直方圖。

直方圖采集的基本機關是表中的列資料，也就是當列資料或類型發生變更或删除的時候直方圖可能也會出現相應變化，經過測試有如下情況：

1、當表被删除的時候，直方圖會同步被删除， truncte操作不會有影響，同理insert、delete、update也不會有影響 。
2、當修改字段類型、字段長度，直方圖會被删除。
3、當修改表名後，直方圖 TABLE_NAME 字段會同步更新，和新表建立關聯。
4、修改字段字元集後，直方圖會被删除。例如 ALTER TABLE t1006 MODIFY utf8 VARCHAR(64) CHARACTER SET latin1;

其他注意點：

1、ANALYZE TABLE 分析過程需要從表定義緩存中删除表，是以該過程會産生一個 flush鎖 。如果有長時間運作的語句或事務仍在使用表，則後續語句和事務必須等待這些操作完成後才釋放 flush鎖 。
2、直方圖把統計資料存儲在資料字典的的統計表内，是以當 innodb_read_only 參數開啟的時候，可能由于無法更新資料字典t統計資訊導緻執行失敗,

三、直方圖使用案例

先檢視下

t_user

上

age

各個年齡段的人數；目前

t_user

上

age

字段沒有建立索引。

mysql> select age,count(id) from t_user group by age;
+------+-----------+
| age  | count(id) |
+------+-----------+
|   11 |     69556 |
|    1 |         6 |
|  127 |         7 |
|   10 |     70330 |
|   19 |     20095 |
|   13 |     19953 |
|   18 |     19855 |
|   12 |     19913 |
|   14 |     19965 |
|   17 |     20058 |
|   15 |     20047 |
|   16 |     20213 |
+------+-----------+
12 rows in set (0.18 sec)

解析查詢年齡段

> 10 age < 12

。

通過分析，可以看出執行過程

type=ALL

走了全表掃描，

filtered=11.11

過濾比例還是比較低的，同時表上沒有建立索引，是以

key=NuLL

。

mysql> explain select * from t_user where age>10 and age<12;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299131 |    11.11 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

建立一個直方圖；根據步驟1查出來，年齡段大概分類12，是以我們建立12個桶

mysql> analyze table t_user update histogram on age with 12 buckets;
+-------------+-----------+----------+------------------------------------------------+
| Table       | Op        | Msg_type | Msg_text                                       |
+-------------+-----------+----------+------------------------------------------------+
| test.t_user | histogram | status   | Histogram statistics created for column 'age'. |
+-------------+-----------+----------+------------------------------------------------+
1 row in set (0.06 sec)


# 檢視建立的直方圖資訊
mysql> select * from information_schema.column_statistics\G;
SCHEMA_NAME: test
 TABLE_NAME: t_user
COLUMN_NAME: age
  HISTOGRAM: {{
	"buckets": [
		[1, 0.0002608242044861763],
		[10, 0.27339593114241006],
		[11, 0.5397496087636933],
		[12, 0.5968179447052686],
		[13, 0.6553990610328638],
		[14, 0.7131977047470005],
		[15, 0.7706311945748565],
		[16, 0.8261345852895148],
		[17, 0.8855503390714657],
		[18, 0.9423056859676577],
		[19, 0.9996870109546165],
		[127, 1.0]
	],
	"data-type": "int",
	"null-values": 0.0,
	"collation-id": 8,
	"last-updated": "2022-04-24 03:00:47.361704",
	"sampling-rate": 0.059696731054764834,
	"histogram-type": "singleton",
	"number-of-buckets-specified": 12
}
3 rows in set (0.00 sec)

再進行查詢分析

通過分析，可以看出執行過程

type=ALL

在建立直方圖後也是走了全表掃描，

filtered=39.22

過濾有顯著提升。

mysql> explain select * from t_user where age>10 and age<12;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 299131 |    39.22 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

總體而言，從兩次執行計劃的差别可以看出建立直方圖後，filtered 列

39.22

比沒有建立直方圖

11.11

值有更好的過濾效果。

PS：關于

filtered

列，這個字段表示存儲引擎傳回的資料在Server層過濾後，剩下多少滿足查詢的記錄數量的比例。

進一步通過開啟

TRACE

檢視執行計劃

由于優化器會預設的認為各個年齡段的資料分布是均勻的，是以當沒有直方圖掃描的範圍會比較大，相應的在Server層過濾的資料也就較少。

同理，在有直方圖的情況下，優化器可以通過直方圖來分析年齡段的資料分布，進而調整掃描範圍，過濾更多資料。

mysql> SET OPTIMIZER_TRACE = "enabled=on";
mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE = 1000000;
mysql> explain select * from t_user where age>10 and age<12;
mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;

# 可以看到執行計劃裡面用到了`histogram_selectivity`
"considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`t_user`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 299131,
                      "filtering_effect": [
                        {
                          "condition": "(`t_user`.`age` > 10)",
                          "histogram_selectivity": 0.726604
                        },
                        {
                          "condition": "(`t_user`.`age` < 12)",
                          "histogram_selectivity": 0.53975
                        }
                      ],
                      "final_filtering_effect": 0.392184,
                      "access_type": "scan",
                      "resulting_rows": 117314,
                      "cost": 30193.9,
                      "chosen": true
                    }
                  ]

四、直方圖資源消耗

生成直方圖需要對資料進行采樣分析，這個過程需要消耗一定的記憶體資源和IO資源。

我們可以通過監控來檢視下該過程需要消耗多少資源，當然我們也可以通過

sys.x$memory_by_thread_by_current_bytes

視圖來對比采樣前後記憶體的內插補點變化來大緻預估直方圖建立過程需要消耗多少記憶體，詳細步驟可移步老葉茶館閱讀

https://mp.weixin.qq.com/s/7FI87f6t3UvbE9GGhw8iVA

另外可以通過調整參數

set session histogram_generation_max_mem_size = 1000000;

來限制記憶體的使用，同時這樣也會相應

降低采樣率

。

五、總結

本文對直方圖的内容進行簡單的介紹，篇幅所限，更多細節内容請移步官網進行檢視，另外關于列中已經有索引的情況下，優化器會如何選擇執行計劃，篇幅所限，以後再進行測試。

https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html#analyze-table-histogram-statistics-analysis
https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html
https://mp.weixin.qq.com/s/7FI87f6t3UvbE9GGhw8iVA

Enjoy GreatSQL :)

文章推薦：

面向金融級應用的GreatSQL正式開源

https://mp.weixin.qq.com/s/cI_wPKQJuXItVWpOx_yNTg

Changes in GreatSQL 8.0.25 (2021-8-18)

https://mp.weixin.qq.com/s/qcn0lmsMoLtaGO9hbpnhVg

MGR及GreatSQL資源彙總

https://mp.weixin.qq.com/s/qXMct_pOVN5FGoLsXSD0MA

GreatSQL MGR FAQ

https://mp.weixin.qq.com/s/J6wkUpGXw3YkyEUJXiZ9xA

在Linux下源碼編譯安裝GreatSQL/MySQL

https://mp.weixin.qq.com/s/WZZOWKqSaGSy-mpD2GdNcA

關于 GreatSQL

GreatSQL是由萬裡資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支援InnoDB并行查詢特性，是适用于金融級應用的MySQL分支版本。

Gitee:

https://gitee.com/GreatSQL/GreatSQL

GitHub:

https://github.com/GreatSQL/GreatSQL

Bilibili：

https://space.bilibili.com/1363850082/video

4.直方圖介紹和使用|MySQL索引學習文章推薦：關于 GreatSQL

一、導讀

二、步驟

2.1 SQL語句

2.2 直方圖案例

2.3 檢視直方圖統計資訊

2.3 直方圖分類

2.4 使用限制

三、直方圖使用案例

四、直方圖資源消耗

五、總結

文章推薦：

關于 GreatSQL

繼續閱讀

SQL優化SQL語句優化的目的

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

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

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

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

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

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

關于SQL語言

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

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

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

neo4j之cypher使用文檔

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

sqlServer根據經緯查距離