747      技術社區[雲棲]

善用MySQL AHI加速神器，讓你的InnoDB查詢飛起來！

作者：張青林，騰訊雲布道師、MySQL架構師，隸屬騰訊TEG-基礎架構部-CDB內核開發團隊，專注於MySQL內核研發&相關架構工作，有著服務多個10W級QPS客戶的數據庫優化及穩定性維護經驗。

騰訊雲數據庫團隊：繼承騰訊數據庫團隊十多年海量存儲的內部數據庫運營和運維經驗，推出一係列高性能關係型、分布式、文檔型和緩存類數據庫產品，並提供高可用性、自動化運維和易維護的雲數據庫綜合解決方案。

MySQL 定位用戶記錄的過程可以描述為：

打開索引 -> 根據索引鍵值逐層查找 B+ 樹 branch 結點 -> 定位到葉子結點，將 cursor 定位到滿足條件的 rec 上；

如果樹高為 N, 則需要讀取索引樹上的 N 個結點並進行比較，如果 buffer_pool 較小，則大量的操作都會在 pread 上，用戶響應時間變長；

另外，MySQL中 Server 層與 Engine 之間的是以 row 為單位進行交互的，engine 將記錄返回給 server 層，server 層對 engine 的行數據進行相應的計算，然後緩存或發送至客戶端，為了減少交互過程所需要的時間，MySQL 做了兩個優化：

• 如果同一個查詢語句連續取出了 MYSQL_FETCH_CACHE_THRESHOLD(4) 條記錄，則會調用函數 row_sel_enqueue_cache_row_for_mysql 將 MYSQL_FETCH_CACHE_SIZE(8) 記錄緩存至 prebuilt->fetch_cache 中，在隨後的 prebuilt->n_fetch_cached 次交互中，都會從prebuilt->fetch_cache 中直接取數據返回到 server 層，那麼問題來了，即使是用戶隻需要 4 條數據，Engine 層也會將 MYSQL_FETCH_CACHE_SIZE 條數據放入 fetch_cache 中，造成了不必要的緩存使用。另外， 5.7 可以根據用戶的設置來調整緩存用戶記錄的條數；

• Engine 取出數據後，會將 cursor 的位置保存起來，當取下一條數據時，會嚐試恢複 cursor 的位置，成功則並繼續取下一條數據，否則會重新定位 cursor 的位置，從而通過保存 cursor 位置的方法可以減少 server 層 & engine 層交互的時間；

Server 層 & engine 層交互的過程如下，由於 server & engine 的 row format 不一樣，那麼 engine row format -> server row format 在讀場景下的開銷也是比較大的。

由以上的分析可以看到 MySQL 一次定位 cursor 的過程即是從根結點到葉子結點的路徑，時間複雜度為：height(index) + [CPU cost time]，上述的兩個優化過程無法省略定位 cursor 的中間結點，因此需要引入一種可以從 search info 定位到葉子結點的方法，從而省略根結點到葉子結點的路徑上所消耗的時間，而這種方法即是自適應索引(Adaptive hash index, AHI)。查詢語句使用 AHI 的時候有以下優點：

• 可以直接通過從查詢條件直接定位到葉子結點，減少一次定位所需要的時間；

• 在 buffer pool 不足的情況下，可以隻針對熱點數據頁建立緩存，從而避免數據頁頻繁的 LRU；

但是 AHI 並不總能提升性能，在多表Join & 模煳查詢 & 查詢條件經常變化的情況下，此時係統監控 AHI 使用的資源大於上述的好處時，不僅不能發揮 AHI 的優點，還會為係統帶來額外的 CPU 消耗，此時需要將 AHI 關閉來避免不必要的係統資源浪費，關於 AHI 的適應場景可以參考：mysql_adaptive_hash_index_implementation。

AHI 會監控查詢語句中的條件並進行分析（稍後會進行詳細的介紹），當滿足 AHI 緩存建立的條件後，會選擇索引的若幹前綴索引列對熱點數據頁組建 hash page 以記錄 hash value -> page block 之間的對應關係，本小節主要對 AHI 的內存結構 & 內存來源進行相應的介紹，其內存結構如圖：

上圖是 AHI 的一個內存結構示意圖，AHI 主要使用以下兩種內存：

• 係統初始化分配的 hash_table 的內存，其中每一個 hash_table 的數組大小為：(buf_pool_get_curr_size() / sizeof(void*) / 64)，根據機器位數的不同，數組大小不同， 32位機器為 buffer_pool大小的 1/256, 64 位機器為 buffer_pool 大小的 1/512， 此部分內存為係統內存(mem_area_alloc->malloc)，主要用於構建 hash_table 結構；

• 當 AHI 對數據頁麵構造 AHI 緩存時，此時使用 buffer_pool 的 free 鏈接中的內存，即 buffer_pool 的內存，所以在頁數據發生變化的時候，需要對 AHI 緩存進行相應的維護；

AHI 在查詢過程中的作用範圍

MySQL 中 Server & InnoDB 的交互中是以行為單位進行交互的，InnoDB 逐行取數據的過程可以分為以下 6 個步驟：

1. 如果發現其它線程需要對btr_search_latch上鎖，則釋放 btr_search_latch，然後執行 1； (5.6 & 5.7 在實現上不同)

2. 嚐試從 row_prebuilt_t->fetch_cache 中取數據庫記錄，有則直接返回，如果沒有數據或者不可以使用 fetch cache, 則執行2；

3. 在滿足條件的情況下，使用 AHI 定位 cursor 位置並返回數據, 否則執行 3；

4. 根據 direction 的值確認是否可以從 row_prebuilt_t中恢複 cursor 的位置，如果 direction = 0 或不可以從 row_prebuilt_t中恢複 cursor 的位置， 則調用 btr_pcur_open_at_index_side 打開索引，調用 btr_cur_search_to_nth_level，如果可以使用 AHI，則快速定位葉子結點，否則遍曆 height(index) 個結點定位 cursor, 然後進入 4；如果可以從 row_prebuilt_t 恢複則執行 5；

5. 根據查找的值在葉子結點中逐個匹配，查找滿足條件的記錄，返回數據，取下一條記錄時執行 3,5；

6. 移動 cursor 到下一條記錄並返回數據；

AHI 則在第 [2, 3] 兩個步驟中影響著定位葉子結點的過程，根據查詢條件定位葉子節點的過程中發揮著 hash 的作用，AHI 的實現主要包括 AHI 初始化過程、構建條件、使用過程、維護過程、係統監控等部分，我們從源碼的實現的角度上分析上述過程。

AHI 初始化過程

AHI 作為 buffer_pool 的一部分，是建立查詢條件與 REC 在內存中位置的一個 hash_table, 在係統啟動的時候會隨著 buffer_pool 的初始化而自動的建立相應的內存結構，其初始化過程為：

• 利用係統內存 (malloc) 創建全局變量 btr_search_sys 及其鎖結構；

• 利用係統內存 (malloc) 建立 hash_table 內存結構，並初始化其成員變量，其中 hash_table 數組的大小取決於當前 buffer_pool 的 size 與 係統的機器位數，計算公式為：buf_pool_get_curr_size() / sizeof(void*) / 64，hash_table_t 的結構如下所示：

說明：

• 所有 buffer_pool instances 共享一個 AHI， 而不是每一個 buffer_pool instance 一個 AHI；

• 5.7.8 之前 AHI 隻有一個全局的鎖結構 btr_search_latch，當壓力比較大的時候會出現性能瓶頸，5.7.8 對 AHI 進行了拆鎖處理，詳情可以參考函數: btr_get_search_table() & btr_search_sys_create()；

• AHI 的 btr_search_latch (bug#62018) & index lock 是MySQL中兩個比較大的鎖，詳情可以參考 Index lock and adaptive search – next two biggest InnoDB problems，5.7 通過對 AHI 鎖拆分 (5.7 commit id: ab17ab91) 以及引入不同的索引鎖協議 (WL#6326) 解決了這兩個問題；

AHI 構建條件

AHI 是建立在 search info & REC 內存地址之間的映射信息，在係統接受訪問之前並沒有足夠的信息來建立 AHI 的映射信息，所以需要搜集 SQL 語句在執行過程中的 search_info & block info 信息並判斷是否可以為數據頁建立 AHI 緩存，其中：

search info 對應 btr_search_t， 用於記錄 index 中的 n_fields (前綴索引列數) & n_bytes(last column bytes) 信息，這些被用於計算 fold 值；

block info 用於記錄計算 fold 的值所需要的 fields & bytes 之外，還記錄了在此情況下使用 AHI 在此數據頁上潛在成功的次數；

我們簡單地對 AHI 統計信息的幾個方麵進行描述。

觸發 AHI 索引統計的條件

SQL 語句在定位 cursor 的過程中會執行 btr_cur_search_to_nth_level 函數，當打開 AHI 的時候，在btr_cur_search_to_nth_level 返回之前會調用 btr_search_info_update 來更新相應的統計信息，如果當前的索引的 serch_info->hash_analysis < BTR_SEARCH_HASH_ANALYSIS (17)，則對 search info & block info 不進行統計，否則則會調用 btr_search_info_update_slow 更新 search info & block info 信息，實現如下：

AHI 中索引查詢信息 (index->search_info) 的更新與自適應的過程

背景知識：

btr_cur_search_to_nth_level 中在定位 cursor 的過程中會在樹的每一層調用 page_cur_search_with_match 來確定下一個 branch 結點或葉子結點，page_cur_search_with_match 函數會將查詢過程中比較的前綴索引列數 & 最後一列匹配的字節數記錄至 {cursor->up_match, cursor->up_bytes, cursor->low_bytes, cursor->low_match}，目的是為了保存與 search tuple 在比較過程時的最小比較單元，詳細的計算過程可以參考 page_cur_search_with_match 的實現代碼。

首先判斷當前 index 是否為 insert buffer tree， 如果是 insert buffer, 則不進行 AHI 等相關的操作；

其次，如果當前索引的 info->n_hash_potential = 0，則會按照推薦算法從 {cursor->up_match, cursor->up_bytes, cursor->low_bytes, cursor->low_match} 推薦出前綴索引列數 & 最後一列的字節數用於計算 AHI 中存儲的鍵 {ha_node_t->fold} 的值。

當 info->n_hash_potential != 0 時，則會判斷當前查詢匹配模式 & index->search_info 中保存的匹配模式是否發生變化，如果沒有發生變化，則會增加此模式下潛在利用 AHI 成功的次數 (info->n_hash_potential)，否則需要重新推薦前綴索引列等相關信息，並清空 info->n_hash_potential 的值（info->n_hash_potential = 0），AHI 就是利用這種方法來實現自適應的，所以在打開 AHI 的係統中不建議經常變換查詢條件，前綴索引等信息的計算過程如下：

由以上算法可以看出，選擇{info->n_fields, info->n_bytes, info->left_side}的依據則是在不超過 unique index 列數的前提下，使其計算代價最小，而 index->info->left_side 的值則會決定存儲同一數據頁上相同前綴索引的最左記錄還是最右記錄。

數據頁 block 信息的更新

數據頁 block info 的更新主要包括數據頁上的索引匹配模式、在已有索引匹配模式下成功的次數以及是否為該數據頁建立 AHI 緩存信息的判斷，其主要過程如下：

1. 將 index->info->last_hash_succ 設置為 FALSE, 此時其它線程無法使用該索引上 AHI 功能；

2. 如果 index->search_info 的匹配格式 & 該數據頁上保存的匹配模式相同時，則增加此 block 使用 AHI 成功的次數 block->n_hash_helps， 如果已經為該數據頁建立 AHI 緩存，則設置 index->info->last_hash_succ = TRUE；

3. 如果 index->search_info 的匹配格式 & 該數據頁上保存的匹配模式不相同，則設置 block->n_hash_helps=1 且使用 index->search_info 對 block 上的索引匹配信息進行重新設置，詳細過程可參考 btr_search_update_block_hash_info；

4. 判斷是否需要為數據頁建立 AHI 緩存，在數據頁 block 上使用 AHI 成功的次數大於此數據頁上用戶記錄的 1/16 且當前前綴索引的條件下使用 AHI 成功的次數大於 100 時， 如果此數據頁使用 AHI 潛在成功的次數大於 2 倍該數據頁上的用戶記錄或者當前推薦的前綴索引信息發生了變化的時，則需要為數據頁構造 AHI 緩存信息，詳情可參考以下代碼；

AHI 構建過程（收集 & 判斷 & 建立）

AHI 的構建過程指的是根據 index->search_info 構建查詢條件 & 數據頁的 hash 關係，其主要過程為：

1.收集 hash 信息。遍曆該數據頁上的所有用戶記錄，建立由前綴索引信息 & 物理記錄之間的映射關係的數組 {folds, recs}，其中 index->info->left_side 用來判斷在前綴索引列相同情況下如何保存物理頁記錄，從代碼中可以得知：當 left_side 為 TRUE 時前綴索引列相同的記錄隻保存最左記錄，當 left_side 為 FALSE 時前綴索引列相同的記錄隻保存最右記錄，代碼實現如下：

2.如果之前該數據頁已經存在 AHI 緩存信息但前綴索引信息與當前的信息不一致，則釋放之前緩存的 AHI 信息，如果釋放超過了一個 page size，則將釋放的數據頁退還給 buffer_pool->free 鏈表；

3.調用 btr_search_check_free_space_in_heap 來確保 AHI 有足夠的內存生成映射信息 ha_node_t {fold, data, next}，該內存從 buffer_pool->free 鏈表獲得，詳情參考：buf_block_alloc(), fold 的值的計算可參考函數：rec_fold();

4.由於操作過程中釋放了 btr_search_latch，需要再次檢查 block 上的AHI信息是否發生了變化，如果發生變化則退出函數；

5.調用 ha_insert_for_fold 方法將之前收集的信息生成 ha_node_t, 並將其存放到 btr_search_sys->hash_table 的數組中，其中存放後的結構可以參考圖 AHI memory structure；

  for (i = 0; i < n_cached; i++) {

    ha_insert_for_fold(table, folds[i], block, recs[i]);

  }

AHI 使用條件及定位葉子結點過程

在 “AHI 在查詢過程中的作用範圍” 一節中我們詳細的介紹了 MySQL 中 Server 層 & engine 層中的交互方式以及 AHI 在整個過程中的位置 & 作用，下麵著要看一下在 步驟 2, 3 中 AHI 是如何工作的。

步驟 2 中，是使用 AHI 的一種 shortcut 查詢方式，隻有在滿足很苛刻的條件後才能使用 AHI 的 shortcut 查詢方式，這些苛刻條件包括：

1. 當前索引是 cluster index；

2. 當前查詢是 unique search；

3. 當前查詢不包含 blob 類型的大字段；

4. 記錄長度不能大於 page_size/8；

5. 不是使用 memcache 接口協議的查詢；

6. 事物開啟且隔離級別大於 READ UNCOMMITTED；

7. 簡單 select 查詢而非在 function & procedure；

在滿足以上條件後才能使用 AHI 的 shortcut 查詢方式定位葉子結點，5.7 中滿足條件後的操作可以簡單的描述為：

rw_lock_s_lock(btr_get_search_latch(index));

...

row_sel_try_search_shortcut_for_mysql()

...

rw_lock_s_lock(btr_get_search_latch(index));

步驟 3 中使用 AHI 快速定位葉子結點同樣需要滿足一些條件，具體可以參考代碼：btr_cur_search_to_nth_level()，在此不再累述，我們著重分析一下使用 AHI 定位葉子節點的過程。

1.對 index 所在的 hash_table 上鎖，使用查詢條件中的 tuple 信息計算出鍵值 fold;

rw_lock_s_lock(btr_search_get_latch(index));

fold = dtuple_fold(tuple, cursor->n_fields, cursor->n_bytes, index_id);

2.在 hash_table 上進行查找 key = fold 的 ha_node_t;

3.釋放鎖資源並根據返回的記錄定位葉子結點；

block = buf_block_align(rec);

rw_lock_s_unlock(btr_search_get_latch(index));

btr_cur_position(index, (rec_t*) rec, block, cursor);

4.定位到葉子結點後的過程和不使用 AHI 之後的過程類似，直接返回記錄並記錄 cursor 位置；

AHI 維護 & 監控

MySQL 5.7 中有兩個 AHI 相關的參數，分別為：

innodb_adaptive_hash_index, innodb_adaptive_hash_index_parts，其中 innodb_adaptive_hash_index 為動態調整的參數，用以控製是否打開 AHI 功能；innodb_adaptive_hash_index_parts 是隻讀參數，在實例運行期間是不能修改，用於調整 AHI 分區的個數（5.7.8 引入），減少鎖衝突，詳細介紹可以參考官方說明：innodb_adaptive_hash_index, innodb_adaptive_hash_index，本節主要介紹操作 AHI 的相關命令以及命令的內部實現過程。

1) 打開 AHI 操作 & 內部實現

set global innodb_adaptive_hash_index=ON，此命令隻是對全局變量進行設置，代碼實現如下：

2) 關閉 AHI 操作 & 內部實現

set global innodb_adaptive_hash_index= OFF，此命令用於關閉 AHI 功能，具體實現可參考 btr_search_disable()， 關閉流程說明：

• 設置 btr_search_enabled = FALSE，關閉 AHI 功能；

• 將數據字典中所有緩存的表對象的 ref_count 設置為0，隻有 btr_search_info_get_ref_count(info, index) = 0 的情況下才能清除數據字典中的緩存對象，詳情見 dict_table_can_be_evicted()；

• 將所有數據頁中的統計信息置空，具體實現可參考 buf_pool_clear_hash_index();

• 釋放 AHI 所使用的 buffer_pool 的內存，btr_search_disable 具體實現如下：

3) AHI 緩存信息的維護

AHI 維護的是 search info & REC 在物理內存地址的 hash 關係，當物理記錄的位置或者所在 block 的地址發生變化時，AHI 也需要對其進行相應的維護，如新記錄的的插入，表記錄的的刪除，數據頁的分裂，drop table & alter table，LRU 換頁等都需要對 AHI 進行相應的維護，詳情可參考函數 btr_search_update_hash_ref() & btr_search_drop_page_hash_index() & buf_LRU_drop_page_hash_for_tablespace()的實現；

4) AHI 信息的監控

AHI 默認情況下隻對 adaptive_hash_searches (使用 AHI 方式查詢的次數) & adaptive_hash_searches_btree (使用 bree 查詢的次數，需要遍曆 branch 結點) 進行監控，更詳細的監控需要進行額外的設置，詳細設置方法可參考 innodb_monitor_enable &module_adaptive_hash ，打開 AHI 的監控方法、使用監控、重置監控的方法如下：

值得一提的是隻有執行 set global innodbmonitor_reset_all='adaptive_hash%' & set global innodb_monitor_disable='adaptive_hash%' 才對狀態進行重置，如果發現 adaptive_hash_searches << adaptive_hash_searches_btree 的時候，則應該關閉 AHI 以減少不必要的係統消耗。

原文發布時間為：2017-01-24

本文來自雲棲社區合作夥伴DBAplus

最後更新：2017-05-15 18:01:34

  上一篇：  互聯網企業安全高級指南3.12　關於應急響應

  下一篇：  互聯網企業安全高級指南3.11　業務持續性管理