MySQL當前已經發布到MySQL8.0版本，在新的版本中，可以看到MySQL之前被人詬病的最佳化器部分做了很多的改動，由於筆者之前的工作環境是5.6，最近切換到最新的8.0版本，本文涵蓋了一些本人感興趣的和最佳化器相關的部分，主要包括MySQL5.7的cost model以及MySQL8.0的直方圖功能。

本文基於當前最新的MySQL8.0.12版本，主要是講下cost model 和 histogram的用法和相關程式碼

Cost Model

Configurable cost constants

為什麼需要配置cost model常量 ? 我們知道MySQL已經發展了好幾十年的歷史，但是在最佳化器中依然使用了hardcode的權重值來衡量io, cpu等資源情況，而這些權重值實際上是基於多年前甚至十來年前的經驗設定的。想想看，這麼多年硬體的發展多麼迅速。幾十上百個核心的伺服器不在少數甚至在某些大型公司大規模使用，ssd早就成為主流，NVME也在崛起。高速RDMA網路正在走入尋常百姓家。這一切甚至影響到資料庫系統的實現和變革。顯而易見，那些hardcode的權值已經過時了，我們需要提供給使用者可定義的方式，甚至更進一步的，能夠智慧的根據硬體環境自動設定。

MySQL5.7引入兩個新的系統表, 透過這兩個系統表暴露給使用者來進行更新，如下：

root@(none) 04:05:24>select * from mysql.server_cost;
+------------------------------+------------+---------------------+---------+---------------+| cost_name                    | cost_value | last_update         | comment | default_value |+------------------------------+------------+---------------------+---------+---------------+| disk_temptable_create_cost   |       NULL | 2018-04-23 13:55:20 | NULL    |            20 || disk_temptable_row_cost      |       NULL | 2018-04-23 13:55:20 | NULL    |           0.5 || key_compare_cost             |       NULL | 2018-04-23 13:55:20 | NULL    |          0.05 || memory_temptable_create_cost |       NULL | 2018-04-23 13:55:20 | NULL    |             1 || memory_temptable_row_cost    |       NULL | 2018-04-23 13:55:20 | NULL    |           0.1 || row_evaluate_cost            |       NULL | 2018-04-23 13:55:20 | NULL    |           0.1 |+------------------------------+------------+---------------------+---------+---------------+6 rows in set (0.00 sec)
其中default_value是generated column，其表示式已經固定死了預設值：`default_value` float GENERATED ALWAYS AS (
(case `cost_name` when _utf8mb3'disk_temptable_create_cost' then 20.0 when _utf8mb3'disk_temptable_row_cost' then 0.5 when _utf8mb3'key_compare_cost' then 0.05 when _utf8mb3'memory_temptable_create_cost' then 1.0 when _utf8mb3'memory_temptable_row_cost' then 0.1 when _utf8mb3'row_evaluate_cost' then 0.1 else NULL end)) VIRTUAL
root@(none) 04:05:35>select * from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------------+---------+---------------+| engine_name | device_type | cost_name              | cost_value | last_update         | comment | default_value |+-------------+-------------+------------------------+------------+---------------------+---------+---------------+| default     |           0 | io_block_read_cost     |       NULL | 2018-04-23 13:55:20 | NULL    |             1 || default     |           0 | memory_block_read_cost |       NULL | 2018-04-23 13:55:20 | NULL    |          0.25 |+-------------+-------------+------------------------+------------+---------------------+---------+---------------+

你可以透過update語句來進行更新, 例如：

root@(none) 04:05:52>update mysql.server_cost set cost_value = 40 where cost_name = 'disk_temptable_create_cost';
Query OK, 1 row affected (0.05 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@(none) 04:07:13>select * from mysql.server_cost where cost_name = 'disk_temptable_create_cost';
+----------------------------+------------+---------------------+---------+---------------+
| cost_name                  | cost_value | last_update         | comment | default_value |
+----------------------------+------------+---------------------+---------+---------------+
| disk_temptable_create_cost |         40 | 2018-06-23 16:07:05 | NULL    |            20 |
+----------------------------+------------+---------------------+---------+---------------+1 row in set (0.00 sec)//更新後執行一次flush optimizer_costs操作來更新記憶體//但老的session還是會用老的cost資料root@(none) 10:10:12>flush optimizer_costs;
Query OK, 0 rows affected (0.00 sec)

可以看到用法也非常簡單，上面包含了兩張表：server_cost及engine_cost，分別對server層和引擎層進行配置

未來

目前來看，除非根據工作負載，經過充分的測試才能得出合理的配置值，但如何配置，什麼是合理的值，個人認為應該是可以自動調整配置的。關鍵是找出配置和硬體條件的對應關係。這也是我們未來可以努力的一個方向。

reference:

1. Cost Model官方文件

Histogram

直方圖也是MySQL一個萬眾期待的功能了，這個功能實際上在其他資料庫產品中是很常見的，可以很好的指導最佳化器選擇執行路徑。利用直方圖儲存了指定列的資料分佈。MariaDB從很早的10.0.2版本支援這個，而MySQL在最新的8.0版本中也開始支援

使用

MySQL裡使用直方圖是透過 ANALYZE TABLE 語法來執行：

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] ...

舉個簡單的例子：

我們以普通的sysbench表為例：
root@sb1 05:16:33>show create table sbtest1\G
*************************** 1. row ***************************       Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `k` int(11) NOT NULL DEFAULT '0',  `c` char(120) NOT NULL DEFAULT '',  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.01 sec)# 建立直方圖並儲存到資料詞典中root@sb1 05:16:38>ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON k with 10 BUCKETS;
+-------------+-----------+----------+----------------------------------------------+| Table       | Op        | Msg_type | Msg_text                                     |
+-------------+-----------+----------+----------------------------------------------+
| sb1.sbtest1 | histogram | status   | Histogram statistics created for column 'k'. |+-------------+-----------+----------+----------------------------------------------+1 row in set (0.55 sec)
root@sb1 05:17:03>ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON k,pad with 10 BUCKETS;
+-------------+-----------+----------+------------------------------------------------+| Table       | Op        | Msg_type | Msg_text                                       |
+-------------+-----------+----------+------------------------------------------------+
| sb1.sbtest1 | histogram | status   | Histogram statistics created for column 'k'.   || sb1.sbtest1 | histogram | status   | Histogram statistics created for column 'pad'. |
+-------------+-----------+----------+------------------------------------------------+
2 rows in set (7.98 sec)
刪除pad列上的histogram:
root@sb1 05:17:51>ANALYZE TABLE sbtest1 DROP HISTOGRAM ON pad;
+-------------+-----------+----------+------------------------------------------------+
| Table       | Op        | Msg_type | Msg_text                                       |+-------------+-----------+----------+------------------------------------------------+| sb1.sbtest1 | histogram | status   | Histogram statistics removed for column 'pad'. |
+-------------+-----------+----------+------------------------------------------------+
1 row in set (0.06 sec)
root@sb1 05:58:12>ANALYZE TABLE sbtest1 DROP HISTOGRAM ON k;
+-------------+-----------+----------+----------------------------------------------+
| Table       | Op        | Msg_type | Msg_text                                     |+-------------+-----------+----------+----------------------------------------------+| sb1.sbtest1 | histogram | status   | Histogram statistics removed for column 'k'. |
+-------------+-----------+----------+----------------------------------------------+
1 row in set (0.08 sec)
# 如果不指定bucket的話，預設Bucket的數量是100
root@sb1 05:58:27>ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON k;
+-------------+-----------+----------+----------------------------------------------+
| Table       | Op        | Msg_type | Msg_text                                     |+-------------+-----------+----------+----------------------------------------------+| sb1.sbtest1 | histogram | status   | Histogram statistics created for column 'k'. |
+-------------+-----------+----------+----------------------------------------------+
1 row in set (0.56 sec)

直方圖統計資訊儲存於InnoDB資料詞典中，可以透過information_schema表來獲取

root@information_schema 05:34:49>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 `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 can_access_table(`mysql`.`column_statistics`.`schema_name`,`mysql`.`column_statistics`.`table_name`)
character_set_client: utf8
collation_connection: utf8_general_ci1 row in set (0.00 sec)

從column_statistics表的定義可以看到，有一個名為mysql.column_statistics系統表，但被隱藏了，沒有對外暴露

以下舉個簡單的例子：

root@sb1 05:58:55>ANALYZE TABLE sbtest1 UPDATE HISTOGRAM ON k WITH 4 BUCKETS;
+-------------+-----------+----------+----------------------------------------------+| Table       | Op        | Msg_type | Msg_text                                     |
+-------------+-----------+----------+----------------------------------------------+
| sb1.sbtest1 | histogram | status   | Histogram statistics created for column 'k'. |+-------------+-----------+----------+----------------------------------------------+1 row in set (0.63 sec)# 查詢表上的直方圖資訊root@sb1 06:00:43>SELECT JSON_PRETTY(HISTOGRAM) FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE SCHEMA_NAME='sb1' AND TABLE_NAME = 'sbtest1'\G
*************************** 1. row ***************************
JSON_PRETTY(HISTOGRAM): {  "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}1 row in set (0.00 sec)

從輸出的json可以看到，在執行了上述語句後產生的直方圖，有4個bucket，資料型別為Int, 型別為equi-height，即等高直方圖(另外一種是等寬直方圖，即SINGLETON)。每個Bucket中，描述的資訊包括：數值的上界和下界, 頻率以及不同值的個數。透過這些資訊可以獲得比較精確的資料分佈情況，從而最佳化器來根據這些統計資訊決定更優的執行計劃。

如果列上存在大量的重複值，那麼MySQL也可能選擇等寬直方圖，例如上例，我們將列k上的值更新為一半10一半為20，那麼出來的直方圖資料如下：

root@sb1 10:41:17>SELECT JSON_PRETTY(HISTOGRAM) FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE SCHEMA_NAME='sb1' AND TABLE_NAME = 'sbtest1'\G
*************************** 1. row ***************************
JSON_PRETTY(HISTOGRAM): {  "buckets": [
    [
      10,
      0.499995
    ],
    [
      20,
      1.0
    ]
  ],  "data-type": "int",  "null-values": 0.0,  "collation-id": 8,  "last-updated": "2018-09-22 14:41:17.312601",  "sampling-rate": 1.0,  "histogram-type": "singleton",  "number-of-buckets-specified": 100
}
1 row in set (0.00 sec)

如上，對於SINGLETON型別，每個bucket只包含兩個值：列值，及對應的累計頻率（即百分之多少的資料比當前Bucket裡的值要小或相等）

注意這裡的sampling-rate, 這裡的值為1，表示讀取了表上所有的資料來進行統計，但通常對於大表而言，我們可能不希望讀太多的資料，因為可能產生過度的記憶體消耗，因此MySQL還提供了一個引數 histogram_generation_max_mem_size 來限制記憶體的使用上限。

如果表上的DML不多，那直方圖基本是穩定的，但頻繁寫入的話，那我們就可能需要去定期更新直方圖，MySQL本身不會去主動更新。

最佳化器透過histogram來計算列的過濾性，大多數的謂詞都可以使用到。具體參閱官方文件

關於直方圖影響查詢計劃，這篇及

reference

WL#5384 PERFORMANCE_SCHEMA, HISTOGRAMS
WL#8706 Persistent storage of Histogram data
WL#8707 Classes/structures for Histograms
WL#8943 Extend ANALYZE TABLE with histogram support
WL#9223 Using histogram statistics in the optimizer

其他

最佳化rec_per_key

MySQL透過rec_per_key 介面來估算記錄的個數（暗示每個索引Key對應的記錄個數），但在早前版本中這個數字是整數，對於小數會取整，不能表示準確的rec_per_key，從而影響到索引的選擇，因此在5.7版本中，將其記錄的值改成了float型別

引入資料cache狀態計算開銷

在之前的版本中，最佳化器是無法知道資料的狀態，是否是cache在記憶體中，還是需要從磁碟讀出來的，缺乏這部分資訊，導致最佳化器統一認為資料屬於磁碟的來計算開銷。這可能導致低效的執行計劃。

相關程式碼：

server層新增api，用於獲取表或索引上有百分之多少的資料是儲存在cache中的

 handler::table_in_memory_estimate
 handler::index_in_memory_estimate

而在innodb層，增加了一個全域性變數 buf_stat_per_index (對應型別為 buf_stat_per_index_t ) 來維護每個索引在記憶體中的leaf page個數, 其內部實現了一個lock-free的hash結構，Key值為 (m_space_id) << 32 | m_index_id) , 在讀入page時或者記憶體中建立新page時，如果對應的page是leaf page，就遞增計數；當從page hash中移除時，則遞減計數。

為了減少效能的影響，計數器是透過lock-free hash的結構儲存的，對應的結構為 ut_lock_free_hash_t 。
基本的實現思路是：hash是一個定長的陣列，陣列元素為(key, val), 根據Key計算一個hash值再模上array size, 找到對應的槽位, 如果槽位被佔用了，則向右查詢一個空閒的slot。
當陣列滿了的時候，會建立一個新的更大的陣列，在資料還沒Move到這個新hash之前，所有的search都需要查詢兩個陣列。當所有的記錄到遷移到新陣列，並且沒有執行緒訪問老的陣列時，就可以把老的hash刪除掉了。

在hash中儲存的counter本身，也考慮到多核和numa架構，避免同時更新引起的cpu cache失效。在大量core的場景下這個問題可能很明顯。Innodb封裝計數操作到類 ut_lock_free_cnt_t 中，使用陣列維護counter, 按照cpu no作為index更新，需要獲取counter值時則累加陣列中的值。

這個Lock free hash並不是個通用場景的hash結構：例如處理衝突的時候，可能佔用其他key的槽位，hash不夠用時，需要遷移到新的array中。實際上mysql本身實現了一個lf_hash，在擴充套件Hash時無需遷移資料，有空單獨開篇部落格講一下。

你可以從 information_schema.innodb_cached_indexes 表中讀取到每個索引cache的page個數。

當定義好介面，並且Innodb提供相應的統計資料後，最佳化器就可以利用這些資訊來計算開銷：

Cost_model_table::page_read_cost
Cost_model_table::page_read_cost_index

MySQL8.0 · 最佳化器新特性 · Cost Model, 直方圖及最佳化器開銷最佳化

Cost Model

Configurable cost constants

相關程式碼:

全域性cache Cost_constant_cache

執行緒cost model初始化

Table Cost Model

Cost_estimate

未來

reference:

Histogram

使用

相關程式碼

reference

其他

最佳化rec_per_key

引入資料cache狀態計算開銷

相關文章