mysql hash index作用_你的Mysql库真需要Adaptive Hash Index吗

最新推荐文章于 2024-06-11 15:01:59 发布

罗漫

最新推荐文章于 2024-06-11 15:01:59 发布

阅读量130

点赞数

文章标签： mysql hash index作用

本文链接：https://blog.csdn.net/weixin_42697475/article/details/113714961

版权

说起AHI(Adaptive Hash Index)，有的同学估计很陌生，都没听说，没关系，下面我会详细解释说明的，AHI是什么，mysql库为什么要设计AHI，解决什么问题，只有了解这些原理之后，才能判断，你的业务库是否需要AHI。

在说AHI之前，先给大家提一下B+tree索引层数的问题，我们都是知道，随着MySQL数据库单表数据量越来越多(在这里留一个问题，多少数据量，才会导致B+tree层数增加呢？)，B+tree的层数会逐渐增高。

当数据层数增加，索引检索时，从根节点到叶子节点的时间成本就会变大，这是因为需要搜索更多的枝节点。

这个时候，Mysql数据库的设计者，就考虑用一种技术去解决检索成本的问题，使用一种缓存结构，使用检索条件，可以直接查询需要的叶子节点数据，而不用逐层的搜索，跳过枝节点。这个缓存结构就是AHI，也就是自适应哈希索引(Adaptive Hash Index)。

在mysql数据库中AHI默认是开启的，并且占用buffer pool大小的1/16

[root@localhost] 14:11:04 [(none)]>show variables like '%innodb_adaptive_hash_index%';+----------------------------------+-------+| Variable_name | Value |+----------------------------------+-------+| innodb_adaptive_hash_index | ON || innodb_adaptive_hash_index_parts | 8 |+----------------------------------+-------+2 rows in set (0.00 sec)

AHI是可以在线关闭的，执行以下命令就可以关闭了

[root@localhost] 14:16:47 [(none)]>set global innodb_adaptive_hash_index=off;Query OK, 0 rows affected (0.00 sec)[root@localhost] 14:16:57 [(none)]>show variables like '%innodb_adaptive_hash_index%';+----------------------------------+-------+| Variable_name | Value |+----------------------------------+-------+| innodb_adaptive_hash_index | OFF || innodb_adaptive_hash_index_parts | 8 |+----------------------------------+-------+2 rows in set (0.00 sec)

而且不是B+tree索引树上什么数据都能进入到AHI的，是需要满足一定条件才能被加载到AHI缓冲区

写到这里，大家可能有点迷糊了，既然AHI是为了提高索引查询效率，减少搜索时间的，为什么要关闭呢？

在这里大家回想一下，AHI解决的是什么问题，是因为B+tree索引层数高，导致索引搜索时间变成，那么这里就得想想，B+tree索引层数到底有多高，多少业务记录数才会到知道层数加1。

在MySQL中一个页大小为16Kb，这个是可以从参数里查看的

如果业务表一条记录平均长度为1Kb，那么1个页，可以存放16条业务记录，假设主键ID为bigint类型，其长度为8个字节，在B+tree里，页面指针大小为6字节，这样枝节点和根节点，一条记录是14字节，那么一个页中能存放多少这样的记录呢，16384/14=1170，一颗高度为2的B+tree可以存放1170 * 16=18720条记录，如果高度为3，则可以存放1170 * 1170 * 16=21902400条记录，大约2000多万条记录，可以满足绝大多数单表记录条数了，如果是交易订单表超过，建议分表，保证单表记录数不超过500W，单表大小不超过10G。

上述例子讲的是主键索引，普通的二级索引的情况呢，假设主键ID为bigint类型，其长度为8个字节，二级索引为vachar类型，长度为10，那么索引叶子节点可以存放16384/48=341，在这里需要注意如果是utf8，则占用30字节，utf8mb4则占用40字节，假设二级索引10个长度全部写满，在utf8mb4情况下，页面指针大小6字节+二级索引列长度40，一个16k的非叶子节点页能存放16384/46=356条记录。

一颗高度为2的B+tree能存放的索引记录为：341 * 356 = 121396

一颗高度为3的B+tree能存放的索引记录为：341 * 356 * 356 = 43216976

可以看到大约能存放4000多万二级索引数据。

综上可以看出，生产上的B+tree的高度基本不会超过3，而能被加载到AHI缓冲区的数据也不会太多，除非是经常被索引查询的业务数据，而且现在数据库服务器开始大量使用SSD盘，随机读的速度也很快，可以考虑关闭AHI，将内存释放，还给buffer pool，同时，在做truncate table，drop table时，不用再清理AHI中清理要删除表的数据块，提高truncate table，drop table速度。