MySQL·8.0版本更新·效能優化篇

本文主要總結下MySQL在8.0版本和效能相關的一些改動，隨著新的小版本的釋出，本文將不斷進行更新，直到正式GA。

|已更新版本|

| MySQL 8.0.0

MySQL 8.0.0

WL#9387: InnoDB: Group purging of rows by table ID

這個問題最早是facebook的工程師Domas報的一個bug，InnoDB使用多執行緒來進行Undo Purge操作，但分配undo的策略不太合理，直接輪詢分配。這意味著如果從一張表上刪除大量資料，這N個purge執行緒可能產生大量的索引鎖衝突(例如索引頁合併及重組織)

在WL#9387中，在parse undo log時，通過table_id進行分組儲存，在分發時確保同一個table id的記錄被分配給同一個執行緒。(參考函式 trx_purge_attach_undo_recs)

當然這也意味著合理的不會產生衝突的單表操作, 無法利用到多執行緒purge了，也算是一個弊端。

WL#8423: InnoDB: Remove the buffer pool mutex

這個算是眾望所歸的改進了，由Percona貢獻的補丁(bug#75534)，主要是對InnoDB的buffer pool mutex這個大鎖進行了拆分，降低鎖衝突：

Name	Desc
buf_pool_t::LRU_list_mutex	用於保護LRU連結串列，例如從LRU連結串列上刷髒或驅逐Page
buf_pool_t::free_list_mutex	保護free list及withdraw list(online resize)
buf_pool_t::zip_free_mutex	保護zip_free陣列，該陣列用於維護對壓縮表產生的非標準page size的記憶體維護, ref buf/buf0buddy.cc
buf_pool_t::zip_hash_mutex	保護zip_hash, 其中儲存壓縮頁block
buf_pool_t::flush_state_mutex	保護init_flush, n_flush, no_flush等陣列

分配空閒block(buf_LRU_get_free_block):

• 從free list獲取: buf_pool_t::free_list_mutex
• 從unzip_lru/lru上驅逐一個空閒page，需要buf_pool_t::LRU_list_mutex

批量掃描LRU（buf_do_LRU_batch）: buf_pool_t::LRU_list_mutex

批量掃描FLUSH_LIST（buf_do_flush_list_batch）: buf_pool_t::flush_list_mutex

髒頁加入到flush_list(buf_flush_insert_into_flush_list): buf_pool_t::flush_list_mutex

髒頁寫回磁碟後，從flush list上移除(buf_flush_write_complete): buf_pool_t::flush_state_mutex/flush_list_mutex

從LRU上驅逐Page(buf_LRU_free_page):buf_pool_t::LRU_list_mutex， 及buf_pool_t::free_list_mutex(buf_LRU_block_free_non_file_page)

buf_flush_LRU_list_batch 使用mutex_enter_nowait 來獲取block鎖，如果獲取失敗，說明正被其他session佔用，忽略該block.

有些變數的修改從通過buf_pool_t::mutex保護，修改成通過memory barrior來保護(os_rmb or os_wmb), 例如下面幾個函式中均有體現：

btr_search_enable()
buf_resize_thread()
buf_get_withdraw_depth()
buf_LRU_get_free_block()

通過對鎖的拆分，降低了全域性大鎖的競爭，提升了buffer pool的擴充套件性，這個特性其實在Percona Server中很多年前就有了， 但直到MySQL8.0版本才合併進來。

WL#7170: InnoDB buffer estimates for tables and indexes

主要是用於為優化器提供更準確的資訊，即資料是存在與磁碟還是記憶體中， 這樣優化器可以更準確的做出代價計算。

增加一個全域性物件（buf_stat_per_index_t）來管理所有的索引頁計數

為了避免引入新的全域性鎖開銷，實現並使用一個lock-free的hash結構("include/ut0lock_free_hash.h)來儲存索引資訊，key值為索引id。（目前索引id具有唯一性，但不排除未來可能發生改變）。

增加計數:

1. Page剛從磁碟讀入記憶體 (buf_page_io_complete --> buf_page_monitor)
2. 建立一個新的page (btr_page_create)

遞減計數: Page從LRU上釋放時進行遞減(buf_LRU_block_remove_hashed)

增加新的information_schema.innodb_cached_indexs 列印每個索引在記憶體中的page個數，其結構如下：

mysql> show create table INNODB_CACHED_INDEXESG
*************************** 1. row ***************************
       Table: INNODB_CACHED_INDEXES
Create Table: CREATE TEMPORARY TABLE `INNODB_CACHED_INDEXES` (
  `SPACE_ID` int(11) unsigned NOT NULL DEFAULT `0`,
  `INDEX_ID` bigint(21) unsigned NOT NULL DEFAULT `0`,
  `N_CACHED_PAGES` bigint(21) unsigned NOT NULL DEFAULT `0`
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

### 和表名/索引名關聯

SELECT
    tables.name AS table_name,
    indexes.name AS index_name,
    cached.n_cached_pages AS n_cached_pages
FROM
    information_schema.innodb_cached_indexes AS cached,
    information_schema.innodb_sys_indexes AS indexes,
    information_schema.innodb_sys_tables AS tables
WHERE
    cached.index_id = indexes.index_id
        AND
    indexes.table_id = tables.table_id;

相關worklog: WL#7168: API for estimates for how much of table and index data that is in memory buffer

WL#9383: INNODB: ADD AN OPTION TO TURN OFF/ON DEADLOCK CHECKER

增加選項，可以動態關閉死鎖檢測，這對諸如熱點更新這樣的場景效果顯著，之前已專門寫了篇部落格，感興趣的自取

Bug#77094

這個優化來自alisql的貢獻，主要是優化了InnoDB Redo的擴充套件性問題，通過雙buffer機制，允許在寫日誌到磁碟的同時，也允許進行mtr commit，具體參閱我寫的這篇月報

WL#7093: Optimizer provides InnoDB with a bigger buffer

為了減少對Btree的鎖佔用，InnoDB在讀取資料時實際上是有一個小的快取buffer。對於連續記錄掃描，InnoDB在滿足比較嚴格的條件時採用row cache的方式連續讀取8條記錄（並將記錄格式轉換成MySQL Format），儲存線上程私有的row_prebuilt_t::fetch_cache中；這樣一次尋路就可以獲取多條記錄，在server層處理完一條記錄後，可以直接從cache中取資料而無需再次尋路，直到cache中資料取完，再進行下一輪。

在WL#7093中引入了新的介面，由於優化器可以估算可能讀取的行數，因此可以提供給儲存引擎一個更合適大小的row buffer來儲存需要的資料。大批量的連續資料掃描的效能將受益於更大的record buffer。

Record buffer由優化器來自動決定是否開啟，增加新的類Record_buffer進行管理, Record buffer的大小最大不超過128KB， 目前是hard code的，不可以配置。

判斷及分配record buffer函式: set_record_buffer， 並通過新的API介面(handler::ha_set_record_buffer)傳到引擎層

buffer本身是引擎無關的，在sever層分配，通過handler成員m_record_buffer傳遞到引擎層。

增加新的介面，判斷是否支援Record buffer， 目前僅InnoDB支援，需要滿足如下條件 (ref set_record_buffer):

1. access type 不是 ref, ref_or_null, index_merge, range, index 或者ALL
2. 不是臨時表
3. 不是loose index scan
4. 進入InnoDB引擎層判斷((row_prebuilt_t::can_prefetch_records))

       return select_lock_type == LOCK_NONE // 只讀查詢
                && !m_no_prefetch   // 允許prefetch
                && !templ_contains_blob // 沒有BLOB, TEXT, JSON, GEOMETRY這些大列
                && !templ_contains_fixed_point // 不是空間資料型別DATA_POINT
                && !clust_index_was_generated   // 需要使用者定義的primary key 或者唯一索引（被隱式的用作Pk）
                && !used_in_HANDLER // 不是通過HANDLER訪問的
                && !innodb_api // 不是通過類似innodb memcached訪問的
                && template_type != ROW_MYSQL_DUMMY_TEMPLATE //不是check table
                && !in_fts_query; // 不是全文索引查詢

在InnoDB中，當record buffer被配置時，就使用server層提供的record buffer，而不是row_prebuilt_t::fetch_cache

官方部落格對此改進的介紹：http://mysqlserverteam.com/mysql-8-0-faster-batch-record-retrieval/

WL#9250: Split LOCK_thd_list and LOCK_thd_remove mutexes

該Worklog的目的是改進短連線場景下的效能，對thd list的操作可能導致比較高的鎖競爭。

解決方案也比較傳統，就是進行分割槽，將連結串列thd_list劃分成多個陣列，目前為8個分割槽，相應的鎖LOCK_thd_remove和LOCK_thd_list鎖也進行了分割槽。