浅谈innodb的索引页结构,插入缓冲,自适应哈希索引
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
以上这篇浅谈innodb的索引页结构,插入缓冲,自适应哈希索引就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持脚本之家。
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持，可以创建 JSON 类型的字段，并有一套函数支持对JSON的查询、修改等操作，下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢？commen sence指的是那些大家都应该知道的事情，但往往大家又会会略这些东西，或者对这些东西一知半解，今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取)，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表：当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。根据个人经验，mysql执行一个sql的过程如下：1...2014-05-31
- 我们自己鼓捣mysql时，总免不了会遇到这个问题：插入中文字符出现乱码，虽然这是运维先给配好的环境，但是在自己机子上玩的时候咧，总得知道个一二吧，不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql，这里记录一下安装的过程，方便以后查阅Mysql5.5.37安装需要cmake，5.6版本开始都需要cmake来编译，5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候，客户机在Bridged Adapter模式下，使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候，客户机在Bridged Adapter模式下，使用可选项里唯一一个WIFI选项，Microsoft Virtual Wifi Miniport Adapter也无法上网，故弃之。...2013-09-19
- 首先要声明一点，大部分情况下，修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库： 1，进入MySQL目录下的bin文件夹：cd MySQL中到bin文件夹的目录 如我输入的命令行：cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式： mysql -h主机地址 -u用户名 －p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口，然后进入目录mysql/bin，再键入命令mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件，只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容，具有很好的参考价值，希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程，快速掌握MySQL基础知识，真正了解MySQL，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2016-12-02
- 一、准备编译环境，安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
- 传统的关系数据库一般由数据库（database）、表（table）、记录（record）三个层次概念组成，MongoDB是由数据库（database）、集合（collection）、文档对象（document）三个层次组成。MongoDB对于关系型数据库里的表，但是集合中没有列、行和关...2013-09-11
- 这篇文章主要为大家详细介绍了MySQL远程连接不上的解决方法，具有一定的参考价值，感兴趣的小伙伴们可以参考一下...2017-01-26
- 这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30