浅谈innodb的索引页结构,插入缓冲,自适应哈希索引

admin MySQL 2022-02-10 01:49:09 mysql 自适应哈希索引"

Physical Structure of an InnoDB Index

所有的innodb索引都是btree索引，索引记录保存在叶子上，默认的索引页大小是16K。当有新的记录插入时，innodb出于对将来的insert和update操作的考虑，会尝试留下1/16的空闲页大小。

如果索引记录是完全按照索引记录的大小顺序插入的，那么索引也将填满整个页大小的15/16，如果插入顺序完全随机，那么索引页基本上填充为1/2至15/16自建。如果填充因子低于1/2,innodb会尝试重建b-tree。

Mysql5.6以后，可以通过innodb_page_size参数设置当前实例下每个索引页的大小，一旦设定，无法再更改回来。推荐的配置一般是16K，8K或者4K。另外假如一个Mysql实例设置了不同于默认值的innodb_page_size A，那么将无法使用其他不同于A值的实例上的文件（比如做一个物理备份和恢复）

Insert Buffering

数据库应用通常按照主键顺序插入的，在这种情况下，因为聚集索引的顺序和这个主键值的顺序完全一致，insert操作将会减少很多的随机IO。

另一方面，二级索引通常不是唯一的，那么在二级索引中插入数据时是一个相对随机的顺序。同样的，delete和update操作在影响数据页时，涉及到索引的变更，在二级索引上也并不是紧挨着的。这就导致了大量的随机IO。

当插入一条记录，或者从非唯一的二级索引删除一条记录，innodb首先会去检查该二级索引页是否在缓冲池中。如果在缓冲池，innodb将会直接在内存中修改这个索引页。如果该索引也不在缓冲池，那么innodb将会将这个修改记录到插入缓冲，也就是insertbuffer。Insert buffer通常都比较小，所以能够保证全部在缓冲池中，并且更新非常频繁。这个修改的进程就是change buffering（通常情况下，它只会只作用于insert操作，所以也被称为insertbuffering，而该数据结构就是insert buffer）

Disk I/O for Flushing the Insert Buffer

那么插入缓冲如何减少随机IO的呢？每个一段时间，insert buffer会去合并在insertbuffer中的二级非唯一索引。通常情况下，它会合并N个修改到同一个btree索引的索引页中，从而节约了很多IO操作。经测试，insertbuffer可以提高15倍的插入速度。

在事务提交后，insert buffer可能还在合并写入。所以，假如当DB异常重启，reovery阶段，当有非常多的二级索引需要更新或插入时，insert buffer将可能花费很长时间，甚至几个小时。在这个阶段，磁盘IO将会增加，那么就会导致disk-bound类型的查询有显著的性能下滑。

Adaptive Hash Indexes

自适应哈希索引（AHI）使得innodb在缓冲池拥有足够的内存和某些工作负载下，看起来更像一个内存数据库，并且不会牺牲任何事务的特点和稳定性。这个特色由参数innodb_adaptive_hash_index控制，动态参数，默认为on表示打开自适应哈希索引，关闭AHI后内置哈希表将会被立马清空，而正常的操作依旧可以继续，只是直接通过访问B-TREE索引。重新使能AHI后哈希表又会被重建。

通过观察搜素模式，mysql会利用index key的前缀建立哈希索引，这个前缀可以是任意长度，并且它可能仅仅是B-tree上的某些值，而不是整个b-tree。哈希索引通过检测，会在经常被访问的index pages上建立哈希索引。

如果一个表几乎大部分都在缓冲池中，那么建立一个哈希索引能够加快等值查询，通过将btree的索引值转换成一个排序指针。Innodb有这个机制，可以监控索引的搜索情况，如果它注意到有些查询通过建立哈希索引可以优化查询，那么它会自动建立，所以说它是“自适应的”。

在某些工作负载下，通过哈希索引查找带来的性能提升价值远大于这个额外的监控索引搜索情况和保持这个哈希表结构所带来的开销。但某些时候，在负载较高的情况下，自适应哈希索引中添加的read/write 锁也会带来竞争，比如高并发的join操作。Like操作和%的通配符同样不适用于AHI。如果工作负载不适合AHI，建议将它关闭，以免带来不必要的性能开销。因为mysql内部很难预示在一个特定的场合下AHI到底是否合适，推荐做一个实际工作负载的压测（有无AHI两种情况）。在5.6及以后版本中将会考虑让越来越多的工作负载最好disable掉自适应哈希索引，尽管目前而言它默认还是开启的。

哈希索引的创建往往是基于现有的b-tree，innodb可以通过观察b-tree的搜索情况建立任意长度的b-tree索引前缀的方式建立哈希索引。一个哈希索引可以只是部分的，仅包括b-tree index中最经常被访问到的页。

你可以通过观察show engine innodb status结果中的SEMAPHORES部分来决定是否使用自适应哈希索引。如果你看到很多线程都在btr0sea.c文件上创建rw-latch上waiting，那么建议关闭掉自适应哈希索引。本人曾经碰到过的一个case截图如下，典型的高并发模式下AHI引起的竞争，需要关闭AHI