前言

在InnoDB體系架構(gòu)圖的內(nèi)存結(jié)構(gòu)中，還有一塊區(qū)域名為：Adaptive Hash Index，翻譯成中文：自適應(yīng)哈希索引，縮寫：AHI，它是一個(gè)純內(nèi)存結(jié)構(gòu)，我們今天就來(lái)了解它。

• 首先放一個(gè)MySQL官檔中提供的InnoDB體系架構(gòu)圖：

如圖所示：

在InnoDB體系架構(gòu)圖的內(nèi)存結(jié)構(gòu)中，還有一塊區(qū)域（已經(jīng)用紅框標(biāo)出）名為：Adaptive Hash Index，翻譯成中文：自適應(yīng)哈希索引，縮寫：AHI。

它是一個(gè)純內(nèi)存結(jié)構(gòu)，今天我們就來(lái)深入的了解它。

一、MySQL InnoDB是否支持哈希索引？

在網(wǎng)絡(luò)上，流傳著兩種關(guān)于MySQL InnoDB哈希索引的傳言。

有一部分人說(shuō)，InnoDB不支持哈希索引；有一部分人說(shuō)，InnoDB支持哈希索引。

那么到底誰(shuí)說(shuō)的對(duì)呢，其實(shí)兩種說(shuō)法都對(duì)。

1.1 InnoDB不支持Hash Index

首先我們創(chuàng)建一張student表，代碼如下：

mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb;
Database changed
mysql> create table student (  
student_id int not null auto_increment comment '學(xué)號(hào)',  
student_name varchar(20) not null comment '姓名',  
address varchar(100) default '江蘇省蘇州市常熟市' comment '家庭住址',  
primary key (student_id)  
) comment='學(xué)生表';
Query OK, 0 rows affected (0.03 sec)

然后，我們?yōu)閍ddress字段添加一個(gè)Hash Index

mysql> alter table student add index idx_address(address) using hash;
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

我們查看MySQL的warning

show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                 |
+-------+------+---------------------------------------------------------------------------------------------------------+
| Note  | 3502 | This storage engine does not support the HASH index algorithm, storage engine default was used instead. |
+-------+------+---------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

系統(tǒng)提示我們Innodb存儲(chǔ)引擎不支持HASH索引算法，使用存儲(chǔ)引擎默認(rèn)值代替，接著查詢創(chuàng)建完哈希索引后的表結(jié)構(gòu)

mysql> show index from student \G;
*************************** 1. row ***************************
        Table: student
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: student_id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: student
   Non_unique: 1
     Key_name: idx_address
 Seq_in_index: 1
  Column_name: address
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

ERROR: 
No query specified

從上面的顯示結(jié)果看，你會(huì)驚人的發(fā)現(xiàn)，剛創(chuàng)建的Hash Index，終歸還是B+樹Index

1.2 InnoDB支持Hash Index

InnoDB會(huì)進(jìn)行自調(diào)優(yōu)，如果判定建立Adaptive Hash Index，能夠提升查詢效率，InnoDB自己會(huì)在內(nèi)存中建立相關(guān)哈希索引（所以這就是Adaptive——“自適應(yīng)”的由來(lái)），不需要人工手動(dòng)干預(yù)，InnoDB會(huì)根據(jù)所需自己創(chuàng)建自適應(yīng)哈希索引。所以，從這個(gè)角度來(lái)說(shuō)，InnoDB是支持哈希索引的

二、Adaptive Hash Index的概念

自適應(yīng)哈希索引是Innodb引擎的一個(gè)特殊功能，當(dāng)它注意到某些索引值使用的非常頻繁，發(fā)現(xiàn)建立哈希索引（又稱散列索引）可以帶來(lái)速度的提升，Innodb就會(huì)在自己的內(nèi)存緩沖區(qū)（Buffer Pool）里，開辟一塊區(qū)域，建立自適應(yīng)哈希索引（Adaptive Hash Index，AHI），以便加速查詢。

官檔地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-adaptive-hash.html

三、涉及Adaptive Hash Index的參數(shù)

show variables like '%adaptive%'

3.1 innodb_adaptive_hash_index

該參數(shù)影響自適應(yīng)哈希索引是否啟用。默認(rèn)情況下啟用此變量。當(dāng)我們禁用自適應(yīng)哈希索引會(huì)立即清空哈希表。

當(dāng)哈希表被清空時(shí)，正常操作可以繼續(xù)，并且執(zhí)行使用哈希表的查詢直接訪問索引 B 樹。

當(dāng)重新啟用自適應(yīng)散列索引時(shí)，在正常操作期間會(huì)再次填充散列表。

set persist innodb_adaptive_hash_index = on;

3.2 innodb_adaptive_flushing

該參數(shù)影響每秒刷新臟頁(yè)的操作，默認(rèn)情況下是啟用此變量，刷新臟頁(yè)會(huì)通過判斷產(chǎn)生重做日志的速度來(lái)判斷最合適的刷新臟頁(yè)的數(shù)量，如果關(guān)閉該參數(shù)會(huì)導(dǎo)致你的MySQL的服務(wù)器的tps有明顯的波動(dòng)。

每當(dāng)重做日志寫滿了，MySQL就會(huì)停下手頭的任務(wù)，先把臟頁(yè)刷到磁盤里，才能繼續(xù)干活

set persist innodb_adaptive_flushing = on;

3.3 innodb_adaptive_flushing_lwm

該參數(shù)可以設(shè)置redo log flush低水位線，當(dāng)需要flush的redo log超過這個(gè)低水位時(shí)，innodb會(huì)立即啟用adaptive flushing，默認(rèn)值10，最小值0，最大值70

set persist innodb_adaptive_flushing_lwm= 10;

3.4 innodb_adaptive_hash_index_parts

該參數(shù)是5.7后InnoDB將自適應(yīng)哈希索引進(jìn)行了分區(qū)處理，每個(gè)區(qū)對(duì)應(yīng)一個(gè)鎖，如果大量地訪問，那么可能會(huì)對(duì)性能產(chǎn)生影響(搶鎖)，InnoDB將這個(gè)值默認(rèn)設(shè)為8，最小值1，最大值512

set persist_only innodb_adaptive_hash_index_parts= 10;

小提示

相關(guān)參數(shù)解釋了這么多，其實(shí)生產(chǎn)環(huán)境我們均采取默認(rèn)值即可。

四、準(zhǔn)備工作

為了后面內(nèi)容的順利進(jìn)行，我們對(duì)student學(xué)生表做了一些改造

• 刪除了并沒有實(shí)際用處的“Hash”索引idx_address

mysql> alter table student drop index idx_address;

• 為student_name學(xué)生姓名字段添加了一個(gè)普通二級(jí)索引。

mysql> alter table student add index idx_student_name(student_name);

• 為student_name插入數(shù)據(jù)

insert into student(student_name,address) values('張一','工業(yè)園區(qū)');
insert into student(student_name,address) values('張二','姑蘇區(qū)');
insert into student(student_name,address) values('張三','吳中區(qū)');
insert into student(student_name,address) values('張四','高新區(qū)');
insert into student(student_name,address) values('張五','吳江區(qū)');
insert into student(student_name,address) values('張六','相城區(qū)');
insert into student(student_name,address) values('張七','常熟市');
insert into student(student_name,address) values('張八','昆山市');
insert into student(student_name,address) values('張九','太倉(cāng)市');
insert into student(student_name,address) values('張十','張家港市');

• 最后，我們看一下表結(jié)構(gòu)和表中的數(shù)據(jù)

mysql> show create table student \G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `student_id` int NOT NULL AUTO_INCREMENT COMMENT '學(xué)號(hào)',
  `student_name` varchar(20) NOT NULL COMMENT '姓名',
  `address` varchar(100) DEFAULT '江蘇省蘇州市常熟市' COMMENT '家庭住址',
  PRIMARY KEY (`student_id`),
  KEY `idx_student_name` (`student_name`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='學(xué)生表'
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> select * from student;
+------------+--------------+--------------+
| student_id | student_name | address      |
+------------+--------------+--------------+
|          1 | 張一         | 工業(yè)園區(qū)     |
|          2 | 張二         | 姑蘇區(qū)       |
|          3 | 張三         | 吳中區(qū)       |
|          4 | 張四         | 高新區(qū)       |
|          5 | 張五         | 吳江區(qū)       |
|          6 | 張六         | 相城區(qū)       |
|          7 | 張七         | 常熟市       |
|          8 | 張八         | 昆山市       |
|          9 | 張九         | 太倉(cāng)市       |
|         10 | 張十         | 張家港市     |
+------------+--------------+--------------+
10 rows in set (0.00 sec)

五、通過聚簇索引和普通索引訪問記錄的過

InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，聚簇索引就是按照每張表的主鍵構(gòu)造一顆B+樹，同時(shí)葉子節(jié)點(diǎn)中存放的就是整張表的行記錄數(shù)據(jù)，也將聚集索引的葉子節(jié)點(diǎn)稱為數(shù)據(jù)頁(yè)。

這個(gè)特性決定了索引組織表中數(shù)據(jù)也是索引的一部分；

5.1 聚簇索引訪問記錄過程

student學(xué)生表中的數(shù)據(jù)我們可以簡(jiǎn)易畫一個(gè)B+樹的結(jié)構(gòu)圖（真實(shí)的B+樹結(jié)構(gòu)要復(fù)雜的多，后期會(huì)開辟單獨(dú)的文章詳細(xì)講解），如下所示：

InnoDB會(huì)在主鍵student_id上建立聚集索引（Clustered Index），葉子節(jié)點(diǎn)存儲(chǔ)記錄本身，在student_name上會(huì)建立普通索引（Secondary Index），葉子節(jié)點(diǎn)存儲(chǔ)主鍵值（聚集索引就是數(shù)據(jù)的完整記錄，普通索引也是單獨(dú)的物理結(jié)構(gòu)，兩者均存放在.ibd文件中）。發(fā)起主鍵student_id查詢時(shí)，能夠通過聚集索引，直接定位到行記錄。

select * from student where student_id = 6;

此時(shí)的過程如下圖所示：

5.2 普通索引訪問記錄過程

select * from student where student_name = '張九';

通過普通索引查詢記錄時(shí)，和通過聚簇索引查詢記錄有所不同，分為兩步：

• 步驟1：查詢會(huì)先訪問普通索引，定位到主鍵值9；
• 步驟2：再通過步驟1得到的主鍵值，回表到聚集索引上經(jīng)過二次遍歷定位到具體的完整記錄。

六、通過Adaptive Hash Index訪問記錄的過程

從上面的流程圖可以看出，不管是聚集索引還是普通索引，記錄定位的尋路路徑（Search Path）都很長(zhǎng)?；氐紸daptive Hash Index的概念上：在MySQL運(yùn)行的過程中，如果InnoDB發(fā)現(xiàn)：

• 有很多尋路很長(zhǎng)（比如B+樹層數(shù)太多、回表次數(shù)多等情況）的SQL；
• 有很多SQL會(huì)命中相同的頁(yè)（Page）。

Innodb就會(huì)在自己的內(nèi)存緩沖區(qū)（Buffer Pool）里，開辟一塊區(qū)域，建立自適應(yīng)哈希索引（Adaptive Hash Index，AHI），以便加速查詢。

Adaptive Hash Index訪問記錄原理

通過上面的流程圖，我們可以得出以下結(jié)論：

• MySQL InnoDB的Adaptive Hash Index，更像“索引的索引”，以此來(lái)縮短尋路路徑（Search Path）。
• 我們都知道，Hash數(shù)據(jù)結(jié)構(gòu)都是包含鍵（Key）、值（Value）的，在Adaptive Hash Index，Key就是經(jīng)常訪問到的索引鍵值，Value就是該索引鍵值匹配的完整記錄所在頁(yè)面（Page）的位置。
• 因?yàn)槭荕ySQL InnoDB自己維護(hù)創(chuàng)建的，所以稱之為“自適應(yīng)”哈希索引，但系統(tǒng)也有誤判的時(shí)候，也不能起到加速查詢的效果。

七 、Adaptive Hash Index狀態(tài)監(jiān)控

show engine innodb status \G;

注意： 這是一段時(shí)間的結(jié)果。通過hash searches、non-hash searches計(jì)算AHI帶來(lái)的收益以及成本，確定是否開啟AHI

八、Adaptive Hash Index 注意事項(xiàng)

8.1 使用場(chǎng)景

• 很多單行記錄查詢，比如用戶登錄系統(tǒng)時(shí)密碼的校驗(yàn)。
• 索引范圍查詢，此時(shí)AHI可以快速定位首行記錄。
• 所有記錄內(nèi)存能放得下，這時(shí)AHI往往是有效的。
• 當(dāng)業(yè)務(wù)有大量LIKE或者JOIN，AHI的維護(hù)反而可能成為負(fù)擔(dān)，降低系統(tǒng)效率，此時(shí)可以手動(dòng)關(guān)閉AHI功能。

8.2 注意事項(xiàng)

• AHI目的：緩存索引中的熱點(diǎn)數(shù)據(jù)，提高檢索效率，時(shí)間復(fù)雜度O(1) VS O(N)的差異；
• 基于主鍵的搜索，幾乎都是hash searches；
• 基于普通索引的搜索，大部分是non-hash searches，小部分是hash searches；
• 無(wú)序，沒有樹高，對(duì)熱點(diǎn)Buffer Pool建立AHI，非持久化；
• 初始化為innodb_buffer_pool_size的1/64，會(huì)隨著InnoDB Buffer Pool動(dòng)態(tài)調(diào)整；
• 只支持等值查詢（基于主鍵的等值查詢AHI效果更好）
• AHI很可能是部分長(zhǎng)度索引，并非所有的查詢都能有效果

8.3 Adaptive Hash Index 限制

• 只能用于等值比較，例如=、<=>、IN、AND等
• 無(wú)法用于排序
• 有沖突可能
• MySQL自動(dòng)（“自適應(yīng)”）管理，人為無(wú)法干預(yù)

總結(jié)

今天理論的知識(shí)不是很多，下面簡(jiǎn)單做一下總結(jié)：

• Adaptive Hash Index是內(nèi)存結(jié)構(gòu)，非持久化
• 設(shè)置innodb_adaptive_hash_index_parts，可以降低資源競(jìng)爭(zhēng)提高并發(fā)
• 在Adaptive Hash Index，Key就是經(jīng)常訪問到的索引鍵值，Value就是該索引鍵值匹配的完整記錄所在頁(yè)面（Page）的位置
• Adaptive Hash Index只能用于等值比較，例如=、<=>、IN、AND等，無(wú)法用于排序
• Adaptive Hash Index是InnoDB自己維護(hù)創(chuàng)建的，人為無(wú)法干預(yù)。初始化為innodb_buffer_pool_size的1/64，會(huì)隨著InnoDB Buffer Pool動(dòng)態(tài)調(diào)整
• 基于主鍵的搜索，幾乎都是hash searches；基于普通索引的搜索，大部分是non-hash searches，小部分是hash searches

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論