MySQ 8.0 推出直方圖，性能大大提升！
點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)
作者：LuHengXing
來(lái)源：http://www.dbapub.cn/2020/09/01/MySQL8.0直方圖/
查詢優(yōu)化器負(fù)責(zé)將SQL查詢轉(zhuǎn)換為盡可能高效的執(zhí)行計(jì)劃，但隨著數(shù)據(jù)環(huán)境不斷變化，查詢優(yōu)化器可能無(wú)法找到最佳的執(zhí)行計(jì)劃，導(dǎo)致SQL效率低下。造成這種情況的原因是優(yōu)化器對(duì)查詢的數(shù)據(jù)了解的不夠充足，例如：每個(gè)表有多少行數(shù)據(jù)，每列中有多少不同的值，每列的數(shù)據(jù)分布情況。
MySQL的直方圖分為：等寬直方圖和等高直方圖，MySQL會(huì)自動(dòng)分配使用哪種類型的直方圖，無(wú)法干預(yù)
創(chuàng)建和刪除直方圖
創(chuàng)建語(yǔ)法
ANALYZE TABLE tbl_name UPDATE HISTOGRAM ON col_name [, col_name] WITH N BUCKETS;
創(chuàng)建直方圖時(shí)能夠同時(shí)為多個(gè)列創(chuàng)建直方圖，但必須指定bucket數(shù)量，范圍在1-1024之間，默認(rèn)100。對(duì)于bucket數(shù)量應(yīng)該綜合考慮其有多少不同值、數(shù)據(jù)的傾斜度、精度等，建議從較低的值開(kāi)始，不符合再依次增加。
刪除語(yǔ)法
ANALYZE TABLE tbl_name DROP HISTOGRAM ON col_name [, col_name];
直方圖信息
MySQL通過(guò)字典表column_statistics來(lái)保存直方圖的定義，每行記錄對(duì)應(yīng)一個(gè)字段的直方圖，已JSON格式保存。另外，MySQL 系列面試題和答案全部整理好了，大家可以在Java面試庫(kù)小程序在線刷題。
root@employees 13:49: select json_pretty(histogram) from information_schema.column_statistics where table_name='employees' and column_name='first_name';;
{
"buckets": [
[
"base64:type254:QWFtZXI=",
"base64:type254:QWRlbA==",
0.010176045588684237,
13
],
"data-type": "string",
"null-values": 0.0,
"collation-id": 255,
"last-updated": "2020-09-09 05:47:32.548874",
"sampling-rate": 0.163495700259278,
"histogram-type": "equi-height",
"number-of-buckets-specified": 100
}
MySQL為employees的first_name字段分配了等高直方圖，默認(rèn)為100個(gè)bucket。
當(dāng)生成直方圖時(shí)，MySQL會(huì)將所有數(shù)據(jù)都加載到內(nèi)存中，并在內(nèi)存中執(zhí)行所有工作。如果在大表上生成直方圖，可能會(huì)將幾百M(fèi)的數(shù)據(jù)讀取到內(nèi)存中的風(fēng)險(xiǎn)，因此我們可以通過(guò)參數(shù)hitogram_generation_max_mem_size來(lái)控制生成直方圖最大允許的內(nèi)存量，當(dāng)指定內(nèi)存滿足不了所有數(shù)據(jù)集時(shí)就會(huì)采用采樣的方式。
root@employees 14:12: select histogram->>'$."sampling-rate"' from information_schema.column_statistics where table_name='employees' and column_name='first_name';;
+---------------------------------+
| histogram->>'$."sampling-rate"' |
+---------------------------------+
| 0.163495700259278 |
+---------------------------------+
從MySQL8.0.19開(kāi)始，存儲(chǔ)引擎自身提供了存儲(chǔ)在表中數(shù)據(jù)的采樣實(shí)現(xiàn)，存儲(chǔ)引擎不支持時(shí)，MySQL使用默認(rèn)采樣需要全表掃描，這樣對(duì)于大表來(lái)說(shuō)成本太高，采樣實(shí)現(xiàn)避免了全表掃描提高采樣性能。
關(guān)注公眾號(hào)Java技術(shù)棧，在后臺(tái)回復(fù)：面試，可以獲取我整理的 Java/ MySQL 系列面試題和答案，非常齊全。
通過(guò)INNODB_METRICS計(jì)數(shù)器可以監(jiān)視數(shù)據(jù)頁(yè)的采樣情況，這需要提前開(kāi)啟計(jì)數(shù)器
root@employees 14:26: SELECT NAME, COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'sampled%'\G
*************************** 1. row ***************************
NAME: sampled_pages_read
COUNT: 430
*************************** 2. row ***************************
NAME: sampled_pages_skipped
COUNT: 456
2 rows in set (0.04 sec)
采樣率的計(jì)算公式為：sampled_page_read/(sampled_pages_read + sampled_pages_skipped)
優(yōu)化案例
復(fù)制一張表出來(lái)，源表不添加直方圖，新表添加直方圖
root@employees 14:32: create table employees_like like employees;
Query OK, 0 rows affected (0.03 sec)
root@employees 14:33: insert into employees_like select * from employees;
Query OK, 300024 rows affected (3.59 sec)
Records: 300024 Duplicates: 0 Warnings: 0
root@employees 14:33: ANALYZE TABLE employees_like update HISTOGRAM on birth_date,first_name;
+--------------------------+-----------+----------+-------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------------+-----------+----------+-------------------------------------------------------+
| employees.employees_like | histogram | status | Histogram statistics created for column 'birth_date'. |
| employees.employees_like | histogram | status | Histogram statistics created for column 'first_name'. |
+--------------------------+-----------+----------+-------------------------------------------------------+
root@employees 14:43: explain format=json select count(*) from employees where (birth_date between '1953-05-01' and '1954-05-01') and first_name like 'A%';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "30214.45"
},
"table": {
"table_name": "employees",
"access_type": "ALL",
"rows_examined_per_scan": 299822,
"rows_produced_per_join": 3700,
"filtered": "1.23",
"cost_info": {
"read_cost": "29844.37",
"eval_cost": "370.08",
"prefix_cost": "30214.45",
"data_read_per_join": "520K"
},
"used_columns": [
"birth_date",
"first_name"
],
"attached_condition": "((`employees`.`employees`.`birth_date` between '1953-05-01' and '1954-05-01') and (`employees`.`employees`.`first_name` like 'A%'))"
}
}
}
root@employees 14:45: explain format=json select count(*) from employees where (birth_date between '1953-05-01' and '1954-05-01') and first_name like 'A%';
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "18744.56"
},
"table": {
"table_name": "employees",
"access_type": "range",
"possible_keys": [
"idx_birth",
"idx_first"
],
"key": "idx_first",
"used_key_parts": [
"first_name"
],
"key_length": "58",
"rows_examined_per_scan": 41654,
"rows_produced_per_join": 6221,
"filtered": "14.94",
"index_condition": "(`employees`.`employees`.`first_name` like 'A%')",
"cost_info": {
"read_cost": "18122.38",
"eval_cost": "622.18",
"prefix_cost": "18744.56",
"data_read_per_join": "874K"
},
"used_columns": [
"birth_date",
"first_name"
],
"attached_condition": "(`employees`.`employees`.`birth_date` between '1953-05-01' and '1954-05-01')"
}
}
}
可以看出Cost值從30214.45降到了18744.56，掃描行數(shù)從299822降到了41654，性能有所提升。另外，關(guān)注公眾號(hào)Java技術(shù)棧，在后臺(tái)回復(fù)：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。
參考資料：
https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html#analyze-table-histogram-statistics-analysis
https://mysqlserverteam.com/histogram-statistics-in-mysql/
關(guān)注Java技術(shù)棧看更多干貨
