MySQL Innodb 存储引擎

￡神魔★判官ぃ 2022-01-15 13:37 340阅读 0赞

[为什么80%的码农都做不了架构师？>>> ][80_] ![hot3.png][]

master thread的县城优先级别最高.其内部由几个循环(loop)组成:主循环(loop)、后台循环(background loop)、刷新循环(flush loop)、暂停循环(suspend loop)。master thread 会根据数-据库运行的状态在loop,background loop、flush loop 和suspend loop 中进行切换.  
  
每秒一次的操作:  
1、日志缓冲刷新到磁盘，即使这个事务还没有提交(总是).  
2、合并插入缓冲(可能)  
3、至多刷新100个InnoDB的缓冲池中的脏页到磁盘(可能)。  
4、如果当前没有用户活动,切换到background loop(可能).  
  
即使某个事务还没有提交,innodb存储引擎仍然会每秒将重做日志缓冲中的内容刷新到重做日志文件.这一点是必须知道的,这可以很好地解释为什么再大的事务commit的时间也是很快的。  
合并插入缓冲(insert buffer) 并不是每秒都发生。Innodb存储引擎会判断当前一秒内发生的IO次数是否小于5次，如果小于5次,innodb 认为当前的IO压力很小，可以执行合并插入缓冲的操作.  
同样，刷新100个脏页也不是每秒都在发生。innodb存储引擎通过判断当前缓冲池中脏页的比例(buf\_get\_modified\_ratio\_pct)是否超过配置文件中innodb\_max\_dirty\_pages\_pct这个参数(默认为90，代表%90)如果超过了这个阀值，Innodb存储引擎认为需要做磁盘同步操作，将100个脏页写入磁盘.  
  
  
  
Innodb存储引擎每10秒的操作  
1、刷新100个脏页到磁盘(可能)  
2、合并至多5个插入缓冲(总是)  
3、将日志缓冲刷新到磁盘(总是)  
4、删除无用的Undo页(总是)  
5、刷新100个或者10个脏页到磁盘(总是)  
在以上的过程中，innodb存储引擎会先判断过去10秒之内磁盘的IO操作是否小于200次。如果是，innodb存储引擎认为当前有足够的磁盘IO操作能力.因此将100个脏页刷新到磁盘。接着，Innodb 存储引擎会合并插入缓冲。不同于每1秒操作时可能发生的合并插入缓冲操作，这次的合并插入缓冲操作总会在这个阶段进行。之后Innodb存储引擎会执行一次将日志缓冲刷新到磁盘的操作，这与每秒发生的操作是一样的。  
接着InnoDB存储引擎会执行一步full purge操作，即删除无用的Undo页。对表执行update、delete这类的操作时，原先的行被标记为删除，但是因为一致性读(consistent read)的关系，需要保留这些版本的信息。但是在full perge 过程中，Innodb存储引擎会判断当前事务系统中已删除的行是否可以删除，比如有时候可能还有查询操作需要读取之前版本undo信息，如果可以，innodb会立即将其删除.从源代码中可以发现，innodb存储引擎在操作full purge的时，每次最多删除20个undo页.  
然后，Innodb存储引擎会判断缓冲池中脏页的比例(buf\_get\_modified\_ratio\_pct），如果有超过70%的脏页，则只需刷新10%的脏页到磁盘。  
最后，Innodb存储引擎会产生一个检查点(checkpoint)，innodb存储引擎的检查点也成为模糊检查点(fuzzy checkpoint)。Innodb存储引擎在checkpoint时并不会把所有缓冲池中的脏页都写入磁盘，因为这样可能对性能产生影响，而只是将最老日志序列号(oldest LSN)的页写入磁盘.  
  
接着来看background loop，若当前没有用户活动(数据库空闲时)或者数据库关闭时，就会切换到这个循环。这个循环会执行以下操作:  
1、删除无用的Undo页(总是)  
2、合并20个插入缓冲(总是)  
3、跳回到主循环(总是)  
4、不断刷新100个页，直到符合条件(可能，跳转到flush loop中完成)  
如果flush llop中也没有事情可以做了，innodb存储引擎会切换到suspend\_loop，将master thread挂起，等待事件的发生，若启用了Innodb存储引擎.却没有使用任何Innodb存储引擎的表，那么master thread总是处于挂起状态.  
从Innodb plugin 开始，用show engine innodb status 可以查看当前master thread的状态信息，如下所示：这里可以看到主循环执行了 6300568;

![复制代码][11204838_sJC9.gif]

mysql> show engine innodb status\G; *************************** 1. row *************************** Type: InnoDB
      Name: 
    Status: ===================================== 2015-02-13 13:42:44 2ac95fdf3940 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 14 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 6300568 srv_active, 0 srv_shutdown, 5298516 srv_idle
    srv_master_thread log flush and writes: 11599055

![复制代码][11204838_sJC9.gif]

日均月异随着市场上磁盘设备越来越先进;IO读写越发的高了起来...innodb存储引擎如果安装以前的方式:(1秒内处理100个页的写入和20个插入缓冲合并;未免过于迟缓了)所以Innodb存储引擎开始提供一个参数；用来表示磁盘IO的吞吐量，参数为Innodb\_io\_capacity，默认值为200.对于刷新到磁盘的数量，会按照Innodb\_io\_capacity的百分比来刷新相对数量的页。规则如下:  
1、在合并插入缓冲时，合并插入缓冲的数量为Innodb\_io\_capacity数值5%  
2、在从缓冲区刷新脏页时,刷新脏页的数量为innodb\_io\_capacity。  
  
另外一个问题是参数innodb\_max\_dirty\_pages\_pct的默认值，在mysql5.1版本之前(包括5.1)，该值得默认值为90，意味着脏页占缓冲池的90%。目前该值默认是75%;  
另外一个参数是innodb\_adaptive\_flushing(自适应地刷新)，该值影响每1秒刷新脏页的数量。原来的刷新规则是:如果脏页在缓冲池所占的比例小于innodb\_max\_dirty\_pages\_pct时，不刷新脏页;大于Innodb\_max\_dirty\_pages\_pct时，刷新100个脏页，而innodb\_adaptive\_flushting参数的引入，innodb存储引擎会通过一个名为buf\_flush\_get\_desired\_flush\_rate的函数来判断需要刷新脏页最合适的数量。而buf\_flush\_get\_desired\_flush\_rate是通过判断产生重做日志的速度来判断最合适的刷新脏页的数量。因此当脏页的比例小于Innodb\_max\_dirty\_pages\_pct时，也会刷新一定量的脏页。  
  
  
  
关键性  
Innodb存储引擎的关键性特性包括插入缓冲、两次写(double wirte)、自适应哈希索引(adaptive hash index)。  
插入缓冲  
插入缓冲是Innodb存储引擎关键特性;Insert Buffer和数据页一样，也是物理页的一个组成部分;  
聚集索引一般是顺序的，不需要磁盘的随机读取，  
在很多情况下，一张表有多个非聚集的辅助索引(secondary index)  
InnoDB存储引擎开创性设计了插入缓冲，对于非聚集索引的插入或更新操作，不是每一次直接插入索引页中。而是先判断插入的非聚集索引页是否在缓冲池中。如果在，则直接插入；如果不在，则先放入一个插入缓冲区中，好似欺骗数据库这个非聚集索引已经查到叶子节点了.然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对非聚集索引执行插入和修改操作的性能。  
插入缓冲的使用需要满足以下两个条件:  
1、索引是辅助索引。  
2、索引不是唯一的。  
辅助索引不能是唯一的，因为在把它插入到插入缓冲时，我们并不去查找索引页的情况。如果查找肯定会出现离散读的情况，插入缓冲就失去了意义.  
Ibuf: size 1, free list len 0, seg size 2, 38 merges  
seg size显示当前插入缓冲的大小为2\*16KB  
free list len 0  
free list len 代表了空闲列表的长度。  
目前插入缓冲存在一个问题是，在写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认情况下最大可占用1/2的缓冲池内存。  
修改IBUF\_POOL\_SIZE\_PER\_MAX\_SIZE就可以对插入缓冲的大小进行控制;列如；将IBUF\_POOL\_SIZE\_PER\_MAX\_SIZE改为3，则最大只能使用1/3的缓冲池内存.  
  
两次写  
如果说插入缓冲带给Innodb存储引擎的是性能，那么两次写带给Innodb存储引擎的数据的可靠性，当数据库戎机时，可能发生数据库正在写一个页面，而这个页只能写一部分(比如16K的页，只写前4K的页)的情况，我们称之为部分写失效(partial page write);  
注意:重做日志中记录的是对物理操作，如偏移量800，写'aaa'记录。如果这个页本身已经损坏了,再对其进行重做是没有意义的。这就是说，在应用(apply)重做日志之前，我们需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是doublewrite.innodb存储引擎doublewerite；  
doublewrite由两部分组成:一部分是内存中的doublewrite buffer，大小为2MB；另一部分是物理磁盘上共享表空间中连续的128个页;即两个区(extent)，大小同样为2MB；当缓冲池的脏页刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先拷贝到内存中的doublewrite buffer，之后通过doublewrite再分两次，每次写入1MB到共享表空间的物理磁盘上,然后马上调用fsync函数 ,同步磁盘，避免缓冲写带来的问题。在这个过程中，因为doublewrite页是连续的，因此这个过程是顺序写的.开销并不是很大。在完成doublewrite页的写入后，再将doublewrite buffer中的页写入各个表空间文件中，此时的写入则是离散的。可以通过以下命令观察double write运行状况;

![复制代码][11204838_sJC9.gif]

mysql> show global status like 'innodb_dblwr%'\G; *************************** 1. row *************************** Variable_name: Innodb_dblwr_pages_written
            Value: 10005304 *************************** 2. row *************************** Variable_name: Innodb_dblwr_writes
            Value: 3272391 2 rows in set (0.01 sec)

![复制代码][11204838_sJC9.gif]

可以看到，doublewrite 一共写了10005304个页，但实际的写入次数为3272391，如果你发现你的系统在高峰时Innodb\_dblwr\_pages\_written:Innodb\_dblwr\_writes远小于64:1，那么说明你的系统写入压力并不是很高.  
如果操作系统在将页写入磁盘的过程中崩溃了，在恢复过程中,Innodb存储引擎可以从共享表中的doublewrite找到改页的一个副本，将其拷贝到表空间文件，再应用重做日志。  
参数skip\_innodb\_doublewrite可以禁止使用两次写功能，这时可能会发生前面提及的写失效问题。  
注意：有些文件系统本身就提供了部分写失效的防范机制，如ZFS文件系统。在这种情况下,我们就不要启用doublewrite了。  
自适应哈希索引  
哈希(hash)是一种非常快的查找方法，一般情况下查找时间复杂度为o(1)。常用于join操作，如SQL Server 和Oracle中的哈希连接(hash join)。但是SQL Server和Oracle等常见的数据库并不支持哈希索引(hash index)。MySQL的Heap存储引擎默认的索引类型为哈希，而Innodb存储引擎提出了另外一种实现方法，自适应hash index(adaptive hash index)。  
Innodb存储引擎会监控对表上索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立hash index，所以称之为自适应(adaptive)的。自适应哈希索引通过缓冲池的B+tree构造而来,因此建立的速度很快.  
Hash table size 9461399, node heap has 2933 buffer(s)  
7767.23 hash searches/s, 130.87 non-hash searches/s  
这里主要输出的信息有:包括自适应哈希索引的大小、使用情况、每秒使用自适应hash index搜索的情况。值得注意的是，hash索引只能用来搜索等值的查询，如select \* from table where index\_col = 'xxx' 而对其他查找的类型,如范围查找,是不能使用的。因此这里出现了non-hash searches/s的情况。用hash searches：non-hash searches命令可以大概了解使用哈希索引后的效率.  
我们可以通过参数innodb\_adaptive\_hash\_index来禁用或启动此特性，默认为开启。

注意:mysql一条查询语句只能运行在一颗/一核CPU上;所以一条复杂的查询语句;不如多条简单查询语句性能好;

转载于:https://my.oschina.net/liting/blog/385774

[80_]: https://my.oschina.net/u/2663968/blog/3051541
[hot3.png]: /images/20220115/25b38dff7a134552b9aca082307aa2aa.png
[11204838_sJC9.gif]: /images/20220115/ae80363067894d4d955504a002286477.png