Mysql InnoDB存储引擎中的自适应Hash索引、两次写和插入缓冲

Mysql中的存储引擎：InnoDB有三个关键特性：自适应hash索引、两次写以及插入缓冲。

自适应hash索引（自优化读写速度提升）

由来

由于innodb不支持hash索引，但是在某些情况下hash索引的效率很高，于是出现了adaptive hash index功能，该功能默认开启，建议关掉，意义不大。可以通过 set global innodb_adaptive_hash_index=off/on 关闭和打开该功能。

查看自适应hash索引功能是否被打开

show variables like ’%ap%hash_index’；

在这里插入图片描述

自适应hash索引原理

InnoDB存储引擎会监控对二级索引的查找，如果发现某一个二级索引被频繁访问，二级索引成为一个热数据。那么此时建立hash索引可以带来速度的提升。
经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

而且不需要将整个表都建哈希索引，InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

数据库自优化

根据InnoDB的官方文档显示，启用自适应哈希索引后，读取和写入速度可以提高2倍；对于辅助索引的连接操作，性能可以提高5倍。自适应哈希索引是非常好的优化模式，其设计思想是数据库自优化（self-tuning），即无需DBA对数据库进行调整。

查看当前自适应哈希索引的使用状况：show engine innodb status\G

自适应hash索引的限制：

1、只能用于等值比较，例如=， <=>，in
　　2、hash自适应索引会占用innodb buffer pool 缓冲池；

2、无法用于排序

3、有冲突可能

4、MySQL自动管理，人为无法干预。

两次写（提高可靠性）

部分写失效：

有这么一个问题：当数据正在从内存向磁盘中写一个数据页时，数据库宕机，从而导致了这个页只写了部分数据，导致部分数据丢失。
重做日志是通过对页的物理修改，但是部分写失效这个问题，页本身已经损坏，重做日志也无能为力。
所以我们引入了两次写机制。

两次写机制

在这里插入图片描述
两次写需要额外添加两个部分：
1）内存中的两次写缓冲（doublewrite buffer），大小为2MB
2）磁盘上共享表空间中连续的128页，大小也为2MB

其原理是这样的：

1）当刷新缓冲池脏页时，并不直接写到数据文件中，而是会通过memcpy函数先拷贝至内存中的两次写缓冲区（doublewrite buffer）。
2）接着从两次写缓冲区分两次写入共享表空间的物理磁盘中，每次写入1MB，然后马上调用fsync函数，同步磁盘，避免缓冲写带来的问题。
3）待第2步完成后，再将两次写缓冲区的页离散的写入数据文件中。

这样就可以解决上文提到的部分写失效的问题，因为在磁盘共享表空间中已有数据页副本拷贝，如果数据库在页写入数据文件的过程中宕机，在实例恢复时，可以从共享表空间中找到该页副本，将其拷贝覆盖原有的数据页，再应用重做日志即可。

其中第2步是额外的性能开销，但由于磁盘共享表空间是连续的，因此开销不是很大。可以通过参数skip_innodb_doublewrite禁用两次写功能，默认是开启的，强烈建议开启该功能。

插入缓冲（提高写性能）

插入缓冲技术由来

由于对于聚集索引，插入操作是按照主键自增的顺序将页中的记录进行存放，不需要随机读取其他页的数据。效率很高。
但是涉及到辅助索引（二级索引Secondary index），例如：

mysql>create table t (
           id int auto_increment,
           name varchar(30),
           primary key (id),
           key (name));

对于上述表t中：除了主键聚合索引外，还产生了一个name列的辅助索引，对于该非聚集索引来说，叶子节点的插入不再有序，这时就需要离散访问非聚集索引页，插入性能变低（B+树的特性决定了非聚集索引插入的离散性）。

插入缓冲技术机制

为了解决涉及辅助索引的插入效率低下的情况，InnoDB引入了插入缓冲技术，对于非聚集类索引的插入和更新操作，不是每一次都直接插入到索引页中，而是先插入到内存中。

步骤：

如果该索引页在缓冲池中，就直接插入；如果不在缓冲池中，先将其放入插入缓冲池中，再以一定的频率和索引页合并。这样就可以将同一个索引页中的多个插入合并到一个IO操作中，提升了写的性能。

这个设计思路和HBase中的LSM树有相似之处，都是通过先在内存中修改，到达一定量后，再和磁盘中的数据合并，目的都是为了提高写性能，具体可参考《HBase LSM树》，这又再一次说明，学到最后，技术都是相通的。

插入缓冲的启用需要满足一下两个条件：

1）索引是辅助索引（secondary index）
2）索引不适合唯一的
如果辅助索引是唯一的，就不能使用该技术，原因很简单，因为如果这样做，整个索引数据被切分为2部分，无法保证唯一性。

插入缓冲带来的问题

1）可能导致数据库宕机后实例恢复时间变长。如果应用程序执行大量的插入和更新操作，且涉及非唯一的聚集索引，一旦出现宕机，这时就有大量内存中的插入缓冲区数据没有合并至索引页中，导致实例恢复时间会很长。
2）在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可以占用1/2，这在实际应用中会带来一定的问题。

资料：https://www.cnblogs.com/wade-luffy/p/6279500.html

自适应hash索引：https://www.cnblogs.com/geaozhang/p/7252389.html
插入缓冲：https://blog.csdn.net/dbanote/article/details/9077113
两次写：https://blog.csdn.net/dbanote/article/details/9078091