浅谈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的索引页结构,插入缓冲,自适应哈希索引就是小编分享给大家的全部内容了，希望能给大家一个参考，也希望大家多多支持我们。

赞 (0)

相关推荐

  • Mysql中的Btree与Hash索引比较

    mysql最常用的索引结构是btree(O(log(n))),但是总有一些情况下我们为了更好的性能希望能使用别的类型的索引.hash就是其中一种选择,例如我们在通过用户名检索用户id的时候,他们总是一对一的关系,用到的操作符只是=而已,假如使用hash作为索引数据结构的话,时间复杂度可以降到O(1).不幸的是,目前的mysql版本(5.6)中,hash只支持MEMORY和NDB两种引擎,而我们最常用的INNODB和MYISAM都不支持hash类型的索引. 不管怎样,还是要了解一下这两种索引的区别

  • InnoDB的关键特性-插入缓存,两次写,自适应hash索引详解

    InnoDB的关键特性-插入缓存,两次写,自适应hash索引详解

    InnoDB存储引擎的关键特性包括插入缓冲.两次写(double write).自适应哈希索引(adaptive hash index).这些特性为InnoDB存储引擎带来了更好的性能和更高的可靠性. 插入缓冲 插入缓冲是InnoDB存储引擎关键特性中最令人激动的.不过,这个名字可能会让人认为插入缓冲是缓冲池中的一个部分.其实不然,InnoDB缓冲池中有Insert Buffer信息固然不错,但是Insert Buffer和数据页一样,也是物理页的一个组成部分. 主键是行唯一的标识符,在应用程序

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

    浅谈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.

  • 一文搞懂MySQL索引页结构

    一文搞懂MySQL索引页结构

    目录 1.前言 2.索引页结构 2.1FileHeader 2.2PageHeader 2.3UserRecords 2.4Infimum&Supremum 2.5PageDirectory 2.6FileTrailer 3.总结 1. 前言 「页」是InnoDB管理存储空间的基本单位,也是内存和磁盘交互的基本单位.也就是说,哪怕你需要1字节的数据,InnoDB也会读取整个页的数据,下次读取的数据如果恰巧也在这个页里,就能命中缓存了.写也是一样的,写数据前要先把页加载到内存,然后在内存中修改,该

  • 浅谈Go语言中的结构体struct & 接口Interface & 反射

    结构体struct struct 用来自定义复杂数据结构,可以包含多个字段(属性),可以嵌套: go中的struct类型理解为类,可以定义方法,和函数定义有些许区别: struct类型是值类型. struct定义 type User struct { Name string Age int32 mess string } var user User var user1 *User = &User{} var user2 *User = new(User) struct使用 下面示例中user1和

  • 浅谈JavaScript中的分支结构

    浅谈JavaScript中的分支结构

    说到JavaScript中的分支结构,我们就不得不提到流程控制这个词,我们所有的程序都是由数据和算法组成的. 程序=数据+算法 通常我们所说的算法都可以通过"顺序","分支","循环"三种结构来组合完成. 在ECMA中规定了一些语句(也称为流程控制语句,分支结构语句),从本质上来说,这些语句定义了ECMAScript中的主要语法,语句通常使用一个或者多个关键字来完成给定任务. 1.1 if 语句 if 语句 - 只有当指定条件为 true 时,使

  • 浅谈InnoDB隔离模式的使用对MySQL性能造成的影响

    浅谈InnoDB隔离模式的使用对MySQL性能造成的影响

    在这篇文章里我将讨论一个相关的主题 – InnoDB 事务隔离模式,还有它们与MVCC(多版本并发控制)的关系,以及它们是如何影响MySQL性能的. MySQL手册提供了一个关于MySQL支持的事务隔离模式的恰当描述 – 在这里我并不会再重复,而是聚焦到对性能的影响上. SERIALIZABLE – 这是最强的隔离模式,本质上打败了在锁管理(设置锁是很昂贵的)的条件下,多版本控制对所有选择进行锁定造成大量的开销,还有你得到的并发.这个模式仅在MySQL应用中非常特殊的情况下使用. REPEATA

  • SQL Server2014 哈希索引原理详解

    SQL Server2014 哈希索引原理详解

    当一个key-value键值对传递给一个哈希函数的时候,经过哈希函数的计算之后,根据结果会把key-value键值对放在合适的hash buckets(哈希存储桶)里 举个栗子 我们假设对10取模( % 10 )就是哈希函数.如果key-value键值对的key是1525 ,传递到哈希函数,那么1525 会存放在第五个bucket里 因为5 as 1525 % 10 = 5. 同样,537 会存放在第七个bucket ,2982 会存放在第二个bucket ,依次类推 同样,在hash inde

  • Mysql InnoDB引擎中的数据页结构详解

    Mysql InnoDB引擎中的数据页结构详解

    目录 Mysql InnoDB引擎数据页结构 一.页的简介 二.数据页的结构 三.记录在页中的存储结构 四.记录头信息 1. deleted_flag 2. min_rec_flag 3. n_owned 4. heap_no 5. record_type 6. next_record Mysql InnoDB引擎数据页结构 InnoDB 是 mysql 的默认引擎,也是我们最常用的,所以基于 InnoDB,学习页结构.而学习页结构,是为了更好的学习索引. 一.页的简介 页是 InnoDB 管理

  • 浅谈MySQL和Lucene索引的对比分析

    浅谈MySQL和Lucene索引的对比分析

    MySQL和Lucene都可以对数据构建索引并通过索引查询数据,一个是关系型数据库,一个是构建搜索引擎(Solr.ElasticSearch)的核心类库.两者的索引(index)有什么区别呢?以前写过一篇<Solr与MySQL查询性能对比>,只是简单的对比了下查询性能,对于内部原理却没有解释,本文简单分析下两者的索引区别. MySQL索引实现 在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式. M

  • 浅谈MySQL的B树索引与索引优化小结

    浅谈MySQL的B树索引与索引优化小结

    MySQL的MyISAM.InnoDB引擎默认均使用B+树索引(查询时都显示为"BTREE"),本文讨论两个问题: 为什么MySQL等主流数据库选择B+树的索引结构? 如何基于索引结构,理解常见的MySQL索引优化思路? 为什么索引无法全部装入内存 索引结构的选择基于这样一个性质:大数据量时,索引无法全部装入内存. 为什么索引无法全部装入内存?假设使用树结构组织索引,简单估算一下: 假设单个索引节点12B,1000w个数据行,unique索引,则叶子节点共占约100MB,整棵树最多20

  • 浅谈MySQL之浅入深出页原理

    浅谈MySQL之浅入深出页原理

    一.页的概览 我们往 MySQL 插入的数据最终都是存在页中的.在 InnoDB 中的设计中,页与页之间是通过一个双向链表连接起来. 而存储在页中的一行一行的数据则是通过单链表连接起来的. 上图中的 User Records 的区域就是用来存储行数据的.那 InnoDB 为什么要这么设计?假设我们没有页这个概念,那么当我们查询时,成千上万的数据要如何做到快速的查询出结果?众所周知,MySQL 的性能是不错的,而如果没有页,我们剩下的只能是逐条逐条的遍历数据了. 那页是如何做到快速查询的呢?在当前

随机推荐