精华内容
下载资源
问答
  • MySql Innodb底层存储架构
  • Mysql InnoDB底层实现

    2020-10-23 00:34:48
    Mysql InnoDB底层实现1.InnoDB存储结构2.InnoDB的索引实现3.InnoDB中的八种锁3.1 共享锁或排它锁(Shared and Exclusive Locks)3.2 意向锁(Intention Locks)3.3 索引记录锁(Record Locks)3.4 间隙锁(Gap Locks)3.5 下...

    1.InnoDB存储结构

    InnoDB的基本存储结构是页,页也是InnoDB存储引擎管理数据库的最小磁盘单位。在这里插入图片描述

    • 各个数据页之间是一个双向链表
    • 每个数据页中的记录则是一个单向链表
    • 通过主键查找可以在数据页的页目录中通过二分查找,快速找到指定记录,而以其他列作为搜索条件时则需要依次遍历单链表中的每条记录

    2.InnoDB的索引实现

    因此,我们需要将无序的数据变得相对有序,以加快查询速度,这就是索引的作用。

    InnoDB 是通过 B+Tree 结构对主键建立索引,然后在叶子节点中存储记录,该索引被称作聚集索引。采用 InnoDB 引擎的数据存储文件有两个,一个定义文件,一个是数据文件。

    若建索引的字段不是主键 ID,则对该字段建索引,然后在叶子节点中存储的是该记录的主键,然后通过主键索引找到对应的记录,该索引被称作非聚集索引。在这里插入图片描述

    3.InnoDB中的八种锁

    InnoDB一共有8种锁类型,其中,意向锁(Intention Locks)和自增锁(AUTO-INC Locks)是表级锁,剩余全部都是行级锁。此外,共享锁或排它锁(Shared and Exclusive Locks)尽管也作为8种锁类型之一,它却并不是具体的锁,它是锁的模式,用来“修饰”其他各种类型的锁。

    3.1 共享锁或排它锁(Shared and Exclusive Locks)

    它并不是一种锁的类型,而是其他各种锁的模式,每种锁都有shard或exclusive两种模式。

    当我们说到共享锁(S锁)或排它锁(X锁)时,一般是指行上的共享锁或者行上的排它锁。需要注意的是,表锁也存在共享锁和排它锁,即表上的S锁和表上的X锁,表上的锁除了这两种之外,还包括下面将会提到的意向共享锁(Shard Intention Locks)即IS锁、意向排它锁(Exclusive Intention Locks)即IX锁。表上的锁,除了这四种之外,还有其他类型的锁,这些锁都是在访问表的元信息时会用到的(create table/alter table/drop table等)

    S锁允许一个事务读取一行,多个用户可以同时读取同一个资源,但是阻止其他事务获得相同数据集的X锁,也就是不允许修改。

    X锁会允许获得排他锁的事务更新数据,但是阻止其他事务取得相同数据集的S锁和X锁,也就是说X锁会阻塞其他的写锁和读锁。

    举例:

    情景一:假设T1持有数据行r上的S锁,则当T2请求r上的锁时:
    
    1. T2请求r上的S锁,则,T2立即获得S锁。T1和T2同时都持有r上的S锁。
    
    2. T2请求r上的X锁,则,T2无法获得X锁。T2必须要等待直到T1释放r上的S锁。
    
    
    情景二:假设T1持有r上的X锁,则当T2请求r上的锁时:
    
    T2请求r上的任何类型的锁时,T2都无法获得锁,此时,T2必须要等待直到T1释放r上的X锁。
    

    3.2 意向锁(Intention Locks)

    为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB有两种内部使用的意向锁,有shard或exclusive两种模式,这两种意向锁都是表锁。

    意向锁的目的是告知其他事务,某事务已经锁定了或即将锁定某个/些数据行。事务在获取行锁之前,首先要获取到意向锁,即:

    1. 事务在获取行上的S锁之前,事务必须首先获取 表上的 IS锁或表上的更强的锁。

    2. 事务在获取行上的X锁之前,事务必须首先获取 表上的 IX锁。

    事务请求锁时,如果所请求的锁 与 已存在的锁兼容,则该事务 可以成功获得 所请求的锁;如果所请求的锁 与 已存在的锁冲突,则该事务 无法获得 所请求的锁。

    意向锁IS和IX和任何行锁都兼容,它实际上只会阻塞全表请求
    

    3.3 索引记录锁(Record Locks)

    也就是所谓的行锁,锁定的是索引记录。索引记录锁总是锁定索引记录,即使表上并未定义索引。表未定义索引时,InnoDB自动创建隐藏的聚集索引(索引名字是GEN_CLUST_INDEX),使用该索引执行record lock。

    3.4 间隙锁(Gap Locks)

    索引记录之间的间隙上的锁,锁定尚未存在的记录,即索引记录之间的间隙。有shard或exclusive两种模式,但,两种模式没有任何区别,二者等价。

    gap lock锁住的间隙可以是第一个索引记录前面的间隙,或相邻两条索引记录之间的间隙,或最后一个索引记录后面的间隙。

    InnoDB索引是B+树形式的,因此索引是从小到大按序排列的,在索引记录上查找给定记录时,InnoDB会在第一个不满足查询条件的记录上加gap lock,防止新的满足条件的记录插入。

    gap lock存在的唯一目的就是阻止其他事务向gap中插入数据行,它用于在隔离级别为RR时,阻止幻影行(phantom row)的产生;隔离级别为RC时,搜索和索引扫描时,gap lock是被禁用的,只在 外键约束检查 和 重复key检查时gap lock才有效,正是因为此,RC时会有幻影行问题。

    gap lock是RR(可重复读)和RC(读已提交)隔离级别的一个重要区别
    

    3.5 下一个键锁(Next-Key Locks)

    next-key lock 是 (索引记录上的索引记录锁) + (该索引记录前面的间隙上的锁) 二者的合体,它锁定索引记录以及该索引记录前面的间隙。有shard或exclusive两种模式。

    当InnoDB 搜索或扫描索引时,InnoDB在它遇到的索引记录上所设置的锁就是next-key lock,它会锁定索引记录本身以及该索引记录前面的gap。

    3.6 插入意向锁(Insert Intention Locks)

    一种特殊的gap lock。INSERT操作插入成功后,会在新插入的行上设置index record lock,但,在插入行之前,INSERT操作会首先在索引记录之间的间隙上设置insert intention lock,该锁的范围是(插入值, 向下的一个索引值)。有shard或exclusive两种模式,但,两种模式没有任何区别,二者等价。、

    insert intention lock发出按此方式进行插入的意图:多个事务向同一个index gap并发进行插入时,多个事务无需相互等待。
    

    3.7 自增锁(AUTO-INC Locks)

    自增锁也是表锁,在向带有AUTO_INCREMENT列 的表时插入数据行时,事务需要首先获取到该表的AUTO-INC表级锁,以便可以生成连续的自增值。插入语句开始时请求该锁,插入语句结束后释放该锁(注意:是语句结束后,而不是事务结束后)。

    3.8 空间索引(Predicate Locks for Spatial Indexes)

    这个锁使用较为稀少,可以查看mysql技术文档自行拓展

    https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-predicate-locks

    展开全文
  • 了解InnoDB底层原理

    2020-12-19 23:04:22
    存储引擎 存储引擎,我认为就是一种存储解决方案,实现了新增数据、...InnoDB是目前使用最广的MySQL存储引擎,MySQL从5.5版本开始InnoDB就已经是默认的存储引擎了。 InnoDB的内存架构主要分为三大块,缓冲池(B...

    存储引擎 

    存储引擎,我认为就是一种存储解决方案,实现了新增数据、更新数据和建立索引等等功能。

     

    有哪些已有的存储引擎可以让我们选择呢?

    InnoDB、MyISAM、Memory、CSV、Archive、Blackhole、Merge、Federated、Example

     常用的存储引擎目前就只有InnoDB和MyISAM。

    InnoDB是目前使用最广的MySQL存储引擎,MySQL从5.5版本开始InnoDB就已经是默认的存储引擎了。

    InnoDB的内存架构主要分为三大块,缓冲池(Buffer Pool)、重做缓冲池(Redo Log Buffer)和额外内存池 


     缓冲池

    InnoDB为了做数据的持久化,会将数据存储到磁盘上。但是面对大量的请求时,CPU的处理速度和磁盘的IO速度之间差距太大,为了提高整体的效率, InnoDB引入了缓冲池

    当有请求来查询数据时,如果缓存池中没有,就会去磁盘中查找,将匹配到的数据放入缓存池中。同样的,如果有请求来修改数据,MySQL并不会直接去修改磁盘,而是会修改已经在缓冲池的页中的数据,然后再将数据刷回磁盘,这就是缓冲池的作用,加速读,加速写,减少与磁盘的IO交互。

    缓冲池说白了就是把磁盘中的数据丢到内存,那既然是内存就会存在没有内存空间可以分配的情况。所以缓冲池采用了LRU算法,在缓冲池中没有空闲的页时,来进行页的淘汰。但是采用这种算法会带来一个问题叫做缓冲池污染

    当你在进行批量扫描甚至全表扫描时,可能会将缓冲池中的热点页全部替换出去。这样以来可能会导致MySQL的性能断崖式下降。所以InnoDB对LRU做了一些优化,规避了这个问题。

    MySQL采用日志先行,在真正写数据之前,会首先记录一个日志,叫Redo Log,会定期的使用CheckPoint技术将新的Redo Log刷入磁盘

    除了数据之外,里面还存储了索引页、Undo页、插入缓冲、自适应哈希索引、InnoDB锁信息和数据字典。下面选几个比较重要的来简单聊一聊。


    插入缓冲

     

    插入缓冲针对的操作是更新或者插入,我们考虑最坏的情况,那就是需要更新的数据都不在缓冲池中。那么此时会有下面两种方案。

    1. 来一条数据就直接写入磁盘
    2. 等数据达到某个阈值(例如50条)才批量的写入磁盘

    很明显,第二种方案要好一点,减少了与磁盘IO的交互。

     


    两次写

    插入缓冲提高了MySQL的性能,而两次写则在此基础上提高了数据的可靠性。我们知道,当数据还在缓冲池中的时候,当机器宕机了,发生了写失效,有Redo Log来进行恢复。但是如果是在从缓冲池中将数据刷回磁盘的时候宕机了呢?

    这种情况叫做部分写失效,此时重做日志就无法解决问题

     在刷脏页时,并不是直接刷入磁盘,而是copy到内存中的Doublewrite Buffer中,然后再拷贝至磁盘共享表空间(你可以就理解为磁盘)中,每次写入1M,等copy完成后,再将Doublewrite Buffer中的页写入磁盘文件。

    有了两次写机制,即使在刷脏页时宕机了,在实例恢复的时候也可以从共享表空间中找到Doublewrite Buffer的页副本,直接将其覆盖原来的数据页即可。


    自适应哈希索引

     

    自适应索引就跟JVM在运行过程中,会动态的把某些热点代码编译成Machine Code一样,InnoDB会监控对所有索引的查询,对热点访问的页建立哈希索引,以此来提升访问速度。

    你可能多次看到了一个关键字


    ,是InnoDB中数据管理的最小单位。当我们查询数据时,其是以页为单位,将磁盘中的数据加载到缓冲池中的。同理,更新数据也是以页为单位,将我们对数据的修改刷回磁盘。每页的默认大小为16k,每页中包含了若干行的数据,页的结构如下图所示。

     

     

    我们只需要知道这样设计的好处在哪儿。每一页的数据,可以通过FileHeader中的上一下和下一页的数据,页与页之间可以形成双向链表。因为在实际的物理存储上,数据并不是连续存储的。你可以把他理解成G1的Region在内存中的分布。

    而一页中所包含的行数据,行与行之间则形成了单向链表。我们存入的行数据最终会到User Records中,当然最初User Records并不占据任何存储空间。随着我们存入的数据越来越多,User Records会越来越大,Free Space的空间会越来越小,直到被占用完,就会申请新的数据页。

    User Records中的数据,是按照主键id来进行排序的,当我们按照主键来进行查找时,会沿着这个单向链表一直往后找,


    重做日志缓冲

    InnoDB中缓冲池中的页数据更新会先于磁盘数据更新的,InnoDB也会采用日志先行(Write Ahead Log)策略来刷新数据,什么意思呢?当事务开始时,会先记录Redo Log到Redo Log Buffer中,然后再更新缓冲池页数据。

    Redo Log Buffer中的数据会按照一定的频率写到重做日志中去。被更改过的页就会被标记成脏页,InnoDB会根据CheckPoint机制来将脏页刷到磁盘。


    日志 

    日志分为如下两个维度。

    1. MySQL层面
    2. InnoDB层面

    MySQL日志

     

    MySQL的日志可以分为错误日志、二进制文件、查询日志和满查询日志。

    • 错误日志 很好理解,就是服务运行过程中发生的严重错误日志。当我们的数据库无法启动时,就可以来这里看看具体不能启动的原因是什么
    • 二进制文件 它有另外一个名字你应该熟悉,叫Binlog,其记录了对数据库所有的更改。
    • 查询日志 记录了来自客户端的所有语句
    • 慢查询日志 这里记录了所有响应时间超过阈值的SQL语句,这个阈值我们可以自己设置,参数为long_query_time,其默认值为10s,且默认是关闭的状态,需要手动的打开

     


    InnoDB日志 

    InnoDB日志就只有两种,Redo Log和Undo Log

    • Redo Log 重做日志,用于记录事务操作的变化,且记录的是修改之后的值。不管事务是否提交都会记录下来。例如在更新数据时,会先将更新的记录写到Redo Log中,再更新缓存中页中的数据。然后按照设置的更新策略,将内存中的数据刷回磁盘。
    • Undo Log 记录的是记录的事务开始之前的一个版本,可用于事务失败之后发生的回滚。

    Redo Log记录的是具体某个数据页上的修改,只能在当前Server使用,而Binlog可以理解为可以给其他类型的存储引擎使用。这也是Binlog的一个重要作用,那就是主从复制,另外一个作用是数据恢复

    上面提到过,Binlog中记录了所有对数据库的修改,其记录日志有三种格式。分别是Statement、Row和MixedLevel。

    • Statement 记录所有会修改数据的SQL,其只会记录SQL,并不需要记录下这个SQL影响的所有行,减少了日志量,提高了性能。但是由于只是记录执行语句,不能保证在Slave节点上能够正确执行,所以还需要额外的记录一些上下文信息
    • Row 只保存被修改的记录,与Statement只记录执行SQL来比较,Row会产生大量的日志。但是Row不用记录上下文信息了,只需要关注被改成啥样就行。
    • MixedLevel 就是Statement和Row混合使用。

    具体使用哪种日志,需要根据实际情况来决定。例如一条UPDATE语句更新了很多的数据,采用Statement会更加节省空间,但是相对的,Row会更加的可靠。


    InnoDB和MyISAM的区别

     

    由于MyISAM并不常用,不打算去深究其底层的一些原理和实现。我们在这里简单的对比一下这两个存储引擎的区别就好。我们分点来一点点描述。

    • 事务 InnoDB支持事务、回滚、事务安全和奔溃恢复。而MyISAM不支持,但查询的速度要比InnoDB更快
    • 主键 InnoDB规定,如果没有设置主键,就自动的生成一个6字节的主键,而MyISAM允许没有任何索引和主键的存在,索引就是行的地址
    • 外键 InnoDB支持外键,而MyISAM不支持
    • 表锁 InnoDB支持行锁表锁,而MyISAM只支持表锁
    • 全文索引 InnoDB不支持全文索引,但是可以用插件来实现相应的功能,而MyISAM是本身就支持全本索引
    • 行数 InnoDB获取行数时,需要扫全表。而MyISAM保存了当前表的总行数,直接读取即可。

    所以,简单总结一下,MyISAM只适用于查询大于更新的场景,如果你的系统查询的情况占绝大多数(例如报表系统)就可以使用MyISAM来存储,除此之外,都建议使用InnoDB。

     

    展开全文
  • 存储引擎 很多文章都是直接开始介绍有哪些存储引擎,并没有去介绍存储引擎本身。那么究竟什么是存储引擎?不知道大家有没有想过,MySQL是如何存储...种类很多,但是常用的存储引擎目前就只有InnoDB和MyISAM,我也会着

    存储引擎

    很多文章都是直接开始介绍有哪些存储引擎,并没有去介绍存储引擎本身。那么究竟什么是存储引擎?不知道大家有没有想过,MySQL是如何存储我们丢进去的数据的?

    其实存储引擎也很简单,我认为就是一种存储解决方案,实现了新增数据、更新数据和建立索引等等功能。

    有哪些已有的存储引擎可以让我们选择呢?

    InnoDB、MyISAM、Memory、CSV、Archive、Blackhole、Merge、Federated、Example

    种类很多,但是常用的存储引擎目前就只有InnoDB和MyISAM,我也会着重来介绍这两种存储引擎。

    InnoDB是目前使用最广的MySQL存储引擎,MySQL从5.5版本开始InnoDB就已经是默认的存储引擎了。那你知道为什么InnoDB被广泛的使用呢?先把这个问题放一放,我们先来了解一下InnoDB存储引擎的底层原理。

    InnoDB的内存架构主要分为三大块,缓冲池(Buffer Pool)、重做缓冲池(Redo Log Buffer)和额外内存池

    缓冲池

    InnoDB为了做数据的持久化,会将数据存储到磁盘上。但是面对大量的请求时,CPU的处理速度和磁盘的IO速度之间差距太大,为了提高整体的效率, InnoDB引入了缓冲池

    当有请求来查询数据时,如果缓存池中没有,就会去磁盘中查找,将匹配到的数据放入缓存池中。同样的,如果有请求来修改数据,MySQL并不会直接去修改磁盘,而是会修改已经在缓冲池的页中的数据,然后再将数据刷回磁盘,这就是缓冲池的作用,加速读,加速写,减少与磁盘的IO交互。

    缓冲池说白了就是把磁盘中的数据丢到内存,那既然是内存就会存在没有内存空间可以分配的情况。所以缓冲池采用了LRU算法,在缓冲池中没有空闲的页时,来进行页的淘汰。但是采用这种算法会带来一个问题叫做缓冲池污染

    当你在进行批量扫描甚至全表扫描时,可能会将缓冲池中的热点页全部替换出去。这样以来可能会导致MySQL的性能断崖式下降。所以InnoDB对LRU做了一些优化,规避了这个问题。

    MySQL采用日志先行,在真正写数据之前,会首先记录一个日志,叫Redo Log,会定期的使用CheckPoint技术将新的Redo Log刷入磁盘,这个后面会讲。

    除了数据之外,里面还存储了索引页、Undo页、插入缓冲、自适应哈希索引、InnoDB锁信息和数据字典。下面选几个比较重要的来简单聊一聊。

    插入缓冲

    插入缓冲针对的操作是更新或者插入,我们考虑最坏的情况,那就是需要更新的数据都不在缓冲池中。那么此时会有下面两种方案。

    1. 来一条数据就直接写入磁盘
    2. 等数据达到某个阈值(例如50条)才批量的写入磁盘

    很明显,第二种方案要好一点,减少了与磁盘IO的交互。

    两次写

    鉴于都聊到了插入缓冲,我就不得不需要提一嘴两次写,因为我认为这两个InnoDB的特性是相辅相成的。

    插入缓冲提高了MySQL的性能,而两次写则在此基础上提高了数据的可靠性。我们知道,当数据还在缓冲池中的时候,当机器宕机了,发生了写失效,有Redo Log来进行恢复。但是如果是在从缓冲池中将数据刷回磁盘的时候宕机了呢?

    这种情况叫做部分写失效,此时重做日志就无法解决问题。

    图片来源于网络, 侵删

    在刷脏页时,并不是直接刷入磁盘,而是copy到内存中的Doublewrite Buffer中,然后再拷贝至磁盘共享表空间(你可以就理解为磁盘)中,每次写入1M,等copy完成后,再将Doublewrite Buffer中的页写入磁盘文件。

    有了两次写机制,即使在刷脏页时宕机了,在实例恢复的时候也可以从共享表空间中找到Doublewrite Buffer的页副本,直接将其覆盖原来的数据页即可。

    自适应哈希索引

    自适应索引就跟JVM在运行过程中,会动态的把某些热点代码编译成Machine Code一样,InnoDB会监控对所有索引的查询,对热点访问的页建立哈希索引,以此来提升访问速度。

    你可能多次看到了一个关键字,接下来那我们就来聊一下页是什么?

    ,是InnoDB中数据管理的最小单位。当我们查询数据时,其是以页为单位,将磁盘中的数据加载到缓冲池中的。同理,更新数据也是以页为单位,将我们对数据的修改刷回磁盘。每页的默认大小为16k,每页中包含了若干行的数据,页的结构如下图所示。

    图片来源于网络, 侵删

    不用太纠结每个区是干嘛的,我们只需要知道这样设计的好处在哪儿。每一页的数据,可以通过FileHeader中的上一下和下一页的数据,页与页之间可以形成双向链表。因为在实际的物理存储上,数据并不是连续存储的。你可以把他理解成G1的Region在内存中的分布。

    而一页中所包含的行数据,行与行之间则形成了单向链表。我们存入的行数据最终会到User Records中,当然最初User Records并不占据任何存储空间。随着我们存入的数据越来越多,User Records会越来越大,Free Space的空间会越来越小,直到被占用完,就会申请新的数据页。

    User Records中的数据,是按照主键id来进行排序的,当我们按照主键来进行查找时,会沿着这个单向链表一直往后找,

    重做日志缓冲

    上面聊过,InnoDB中缓冲池中的页数据更新会先于磁盘数据更新的,InnoDB也会采用日志先行(Write Ahead Log)策略来刷新数据,什么意思呢?当事务开始时,会先记录Redo Log到Redo Log Buffer中,然后再更新缓冲池页数据。

    Redo Log Buffer中的数据会按照一定的频率写到重做日志中去。被更改过的页就会被标记成脏页,InnoDB会根据CheckPoint机制来将脏页刷到磁盘。

    日志

    上面提到了Redo log,这一小节就专门来讲一讲日志,日志分为如下两个维度。

    MySQL层面

    InnoDB层面

    MySQL日志

    MySQL的日志可以分为错误日志、二进制文件、查询日志和满查询日志。

    • 错误日志 很好理解,就是服务运行过程中发生的严重错误日志。当我们的数据库无法启动时,就可以来这里看看具体不能启动的原因是什么
    • 二进制文件 它有另外一个名字你应该熟悉,叫Binlog,其记录了对数据库所有的更改。
    • 查询日志 记录了来自客户端的所有语句
    • 慢查询日志 这里记录了所有响应时间超过阈值的SQL语句,这个阈值我们可以自己设置,参数为long_query_time,其默认值为10s,且默认是关闭的状态,需要手动的打开。

    InnoDB日志

    InnoDB日志就只有两种,Redo Log和Undo Log,

    • Redo Log 重做日志,用于记录事务操作的变化,且记录的是修改之后的值。不管事务是否提交都会记录下来。例如在更新数据时,会先将更新的记录写到Redo Log中,再更新缓存中页中的数据。然后按照设置的更新策略,将内存中的数据刷回磁盘。

    • Undo Log 记录的是记录的事务开始之前的一个版本,可用于事务失败之后发生的回滚。

    Redo Log记录的是具体某个数据页上的修改,只能在当前Server使用,而Binlog可以理解为可以给其他类型的存储引擎使用。这也是Binlog的一个重要作用,那就是主从复制,另外一个作用是数据恢复

    上面提到过,Binlog中记录了所有对数据库的修改,其记录日志有三种格式。分别是Statement、Row和MixedLevel。

    • Statement 记录所有会修改数据的SQL,其只会记录SQL,并不需要记录下这个SQL影响的所有行,减少了日志量,提高了性能。但是由于只是记录执行语句,不能保证在Slave节点上能够正确执行,所以还需要额外的记录一些上下文信息
    • Row 只保存被修改的记录,与Statement只记录执行SQL来比较,Row会产生大量的日志。但是Row不用记录上下文信息了,只需要关注被改成啥样就行。
    • MixedLevel 就是Statement和Row混合使用。

    具体使用哪种日志,需要根据实际情况来决定。例如一条UPDATE语句更新了很多的数据,采用Statement会更加节省空间,但是相对的,Row会更加的可靠。

    InnoDB和MyISAM的区别

    由于MyISAM并不常用,我也不打算去深究其底层的一些原理和实现。我们在这里简单的对比一下这两个存储引擎的区别就好。我们分点来一点点描述。

    • 事务 InnoDB支持事务、回滚、事务安全和奔溃恢复。而MyISAM不支持,但查询的速度要比InnoDB更快
    • 主键 InnoDB规定,如果没有设置主键,就自动的生成一个6字节的主键,而MyISAM允许没有任何索引和主键的存在,索引就是行的地址
    • 外键 InnoDB支持外键,而MyISAM不支持
    • 表锁 InnoDB支持行锁表锁,而MyISAM只支持表锁
    • 全文索引 InnoDB不支持全文索引,但是可以用插件来实现相应的功能,而MyISAM是本身就支持全本索引
    • 行数 InnoDB获取行数时,需要扫全表。而MyISAM保存了当前表的总行数,直接读取即可。

    所以,简单总结一下,MyISAM只适用于查询大于更新的场景,如果你的系统查询的情况占绝大多数(例如报表系统)就可以使用MyISAM来存储,除此之外,都建议使用InnoDB。

    End

    由于时间的原因,本文只是简单的聊了聊InnoDB的整体架构,并没有很深入的去聊某些点。例如InnoDB是如何改进来解决缓冲池污染的,其算法具体是什么,checkpoint是如何工作的等等,只是做一个简单的了解,之后如果有时间的话再细聊。

    往期文章:

    如果你觉得这篇文章对你有帮助,还麻烦点个赞关个注分个享留个言

    也可以微信搜索公众号【SH的全栈笔记】,当然也可以直接扫描二维码关注

    展开全文
  • MySQL存储引擎MyISAM和InnoDB底层索引结构

    万次阅读 多人点赞 2018-10-10 11:29:36
    二 MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的 三 MyISAM主键索引与辅助索引的结构 1. 主键索引: 2. 辅助(非主键)索引: 四 InnoDB主键索引与辅助索引的结构 1. 主键索引: 2. 辅助(非主键)...

    目录

    一 存储引擎作用于什么对象

    二 MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的

    三 MyISAM主键索引与辅助索引的结构

    1. 主键索引:

    2. 辅助(非主键)索引:

    四 InnoDB主键索引与辅助索引的结构

    1. 主键索引:

    2. 辅助(非主键)索引:

    五 InnoDB索引结构需要注意的点


    PS:为了更好地理解本文内容,我强烈建议先阅读完我的上一篇文章深入理解MySQL索引底层数据结构与算法

     

    一 存储引擎作用于什么对象

    存储引擎是作用在表上的,而不是数据库。

     

    二 MyISAM和InnoDB对索引和数据的存储在磁盘上是如何体现的

    先来看下面创建的两张表信息,role表使用的存储引擎是MyISAM,而user使用的是InnoDB:

    再来看下两张表在磁盘中的索引文件和数据文件:

    1. role表有三个文件,对应如下:

    • role.frm:表结构文件
    • role.MYD:数据文件(MyISAM Data)
    • role.MYI:索引文件(MyISAM Index)

    2. user表有两个文件,对应如下:

    • user.frm:表结构文件
    • user.ibd:索引和数据文件(InnoDB Data)

    也由于两种引擎对索引和数据的存储方式的不同,我们也称MyISAM的索引为非聚集索引,InnoDB的索引为聚集索引。

     

    三 MyISAM主键索引与辅助索引的结构

    我们先列举一部分数据出来分析,如下:

    上面已经说明了MyISAM引擎的索引文件和数据文件是分离的,我们接着看一下下面两种索引结构异同。

    1. 主键索引:

    上一篇文章已经介绍过数据库索引是采用B+Tree存储,并且只在叶子节点存储数据,在MyISAM引擎中叶子结点存储的数据其实是索引和数据的文件指针两类。

    如下图中我们以Col1列作为主键建立索引,对应的叶子结点储存形式可以看一下表格。

    索引值

    15

    18

    20

    30

    49

    50

    文件指针

    0x07

    0x56

    0x6A

    0xF3

    0x90

    0x77

    通过索引查找数据的流程:先从索引文件中查找到索引节点,从中拿到数据的文件指针,再到数据文件中通过文件指针定位了具体的数据。

    2. 辅助(非主键)索引:

    以Col2列建立索引,得到的辅助索引结构跟上面的主键索引的结构是相同的。

     

    四 InnoDB主键索引与辅助索引的结构

    1. 主键索引:

    我们已经知道InnoDB索引是聚集索引,它的索引和数据是存入同一个.idb文件中的,因此它的索引结构是在同一个树节点中同时存放索引和数据,如下图中最底层的叶子节点有三行数据,对应于数据表中的Col1、Col2、Col3数据项。

    2. 辅助(非主键)索引:

    这次我们以数据表中的Col3列的字符串数据建立辅助索引,它的索引结构跟主键索引的结构有很大差别,我们来看下面的图:

    在最底层的叶子结点有两行数据,第一行的字符串是辅助索引,按照ASCII码进行排序,第二行的整数是主键的值。

     

    五 InnoDB索引结构需要注意的点

    1. 数据文件本身就是索引文件

    2. 表数据文件本身就是按B+Tree组织的一个索引结构文件

    3. 聚集索引中叶节点包含了完整的数据记录

    4. InnoDB表必须要有主键,并且推荐使用整型自增主键

    正如我们上面介绍InnoDB存储结构,索引与数据是共同存储的,不管是主键索引还是辅助索引,在查找时都是通过先查找到索引节点才能拿到相对应的数据,如果我们在设计表结构时没有显式指定索引列的话,MySQL会从表中选择数据不重复的列建立索引,如果没有符合的列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,并且这个字段长度为6个字节,类型为整型。

    那为什么推荐使用整型自增主键而不是选择UUID?

    • UUID是字符串,比整型消耗更多的存储空间;
    • 在B+树中进行查找时需要跟经过的节点值比较大小,整型数据的比较运算比字符串更快速;
    • 自增的整型索引在磁盘中会连续存储,在读取一页数据时也是连续;UUID是随机产生的,读取的上下两行数据存储是分散的,不适合执行where id > 5 && id < 20的条件查询语句。
    • 在插入或删除数据时,整型自增主键会在叶子结点的末尾建立新的叶子节点,不会破坏左侧子树的结构;UUID主键很容易出现这样的情况,B+树为了维持自身的特性,有可能会进行结构的重构,消耗更多的时间。
    • 为什么非主键索引结构叶子节点存储的是主键值?

    保证数据一致性和节省存储空间,可以这么理解:商城系统订单表会存储一个用户ID作为关联外键,而不推荐存储完整的用户信息,因为当我们用户表中的信息(真实名称、手机号、收货地址···)修改后,不需要再次维护订单表的用户数据,同时也节省了存储空间。

     

    展开全文
  • 很多文章都是直接开始介绍有哪些存储引擎,并没有去介绍存储引擎本身。那么究竟什么是存储引擎?不知道大家有没有想过,MySQL是如何存储我们丢进去的数据的? 存储引擎 ...InnoDB、MyISAM、Memory、
  • 一、存储引擎作用于什么对象二、MyIsam和InnoDB对索引和数据的存储在磁盘上是如何体现的?三、MyIsam主键索引和辅助索引(非主键索引)的结构1、主键索引2、辅助索引(非主键)索引四、InnoDB主键索引与辅助索引的...
  • innodb支持事务高并发操作,外键,行级锁,早期不支持全文索引,后来mysql5.6后全面支持。 底层: 两者底层都采用的数据结构是B+树,但是实现方式又略有不同。MyIsam的B+树采用的是非聚集索引(辅助索引),...
  • 局部性原理:取一个数据的同时,计算机认为你马上会去取相邻的数据,所以为了节约磁盘Io,计算机会一次取出一页数据,这里有个 “页” 的概念,这样其实去数据的同时会把相邻的数据也取...那么InnoDb默认的页的大...
  • 【第一章】innodb行格式 1、我们要查某一条或者某多条数据的时候,是怎么样在计算机里面(innodb)进行查询运算的? 首先我们要知道一条数据显示在屏幕上其实是一个逻辑的一个视界。 我们查询数据,首先是将数据...
  • 存储引擎 很多文章都是直接开始介绍有哪些存储引擎,并没有去介绍存储引擎本身。那么究竟什么是存储引擎?不知道大家有没有想过,MySQL是如何存储...种类很多,但是常用的存储引擎目前就只有InnoDB和MyISAM,我也会着
  • 存储引擎 很多文章都是直接开始介绍有哪些存储引擎,并没有去介绍存储引擎本身。那么究竟什么是存储引擎?不知道大家有没有想过,MySQL是如何存储我们丢进去的...种类很多,但是常用的存储引擎目前就只有InnoDB和M...
  • MySQL innoDB索引底层原理详解

    万次阅读 多人点赞 2016-09-02 21:25:22
    本文介绍MySQL的InnoDB索引相对底层原理相关知识,涉及到B+Tree索引和Hash索引,但本文主要介绍B+Tree索引,其中包括聚簇索引和非聚簇索引,InnoDB数据页结构详解,B+Tree索引的使用以及优化,同时还有B+Tree索引的...
  • 1、索引数据结构2、InnoDB索引的设计(1)计算机原理(2)查找过程(3)聚族索引与二级索引(4)创建索引及索引的类型(5)索引的缺点(6)外键及作用 1、索引数据结构 哈希索引(Hash indexes)采用哈希表来对键值...
  • 浅析Innodb事务底层实现原理

    千次阅读 2019-07-11 16:42:57
    MySQL只有innodb引擎支持事务,因此这篇文章也是以innodb为背景写的。 一、事务并发会带来什么问题? 脏读:读到了其他事务未提交的数据 不可重复读:当前事务先进行了一次数据读取,然后再次读取到的数据是别的...
  • 摘要本文介绍MySQL的InnoDB索引相对底层原理相关知识,涉及到B+Tree索引和Hash索引,但本文主要介绍B+Tree索引,其中包括聚簇索引和非聚簇索引,InnoDB数据页结构详解,B+Tree索引的使用以及优化,同时还有B+Tree...
  • 本文主要从数据结构的角度,详细介绍InnoDB行记录格式和数据页的实现原理,从底层看清InnoDB存储引擎。 本文主要内容是根据掘金小册《从根儿上理解 MySQL》整理而来。如想详细了解,建议购买掘金小册阅读。 ...
  • B+树 再到B+树在Myisam和Innodb中的体现形式,会提到索引失效的情况,以及创建索引时的一些注意事项及其原理。采用大量的图片+部分文字更加清晰的描述。其中部分图片来源于咕泡学院公开课。 准备 提到my...
  • 1.Innodb 引擎的底层实现(聚集索引方式) InnoDB 是聚集索引方式,因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树,如左下图所示,而 B+树的叶子节点存储的是主键 ID 对应...
  • mysql存储引擎MyISAM与InnoDB底层数据结构的区别主要有,在磁盘上存储的文件以及存储索引以及组织存储索引的方式不同; MyISAM索引文件和数据文件是分离的(非聚集),索引的叶节点存放的是对应索引在文件系统中的...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 33,492
精华内容 13,396
关键字:

innodb底层