Mysql结构及过程

Mysql结构及过程

1.Mysql执行过程简述

当执行一条查询的SQl的时候大概发生了一下的步骤：

1. 客户端发送查询语句给服务器。
2. 服务器首先检查缓存中是否存在该查询，若存在，返回缓存中存在的结果。若是不存在就进行下一步。
3. 服务器进行SQl的解析、语法检测和预处理，再由优化器生成对应的执行计划。
4. Mysql的执行器根据优化器生成的执行计划执行，调用存储引擎的接口进行查询。
5. 服务器将查询的结果返回客户端。

 这样直接看流程图，并不能直观的感受到两阶段提交的作用，我们分两步来理解两阶段提交起到的作用。

第一步：没有两阶段提交会发生什么？

  在没有两阶段提交的情况下，事务对redo log和bin log的写入顺序为：

1、事务执行过程中，写入redo log

2、事务完成后，写入bin log

  问题非常明显，如果在第二步执行时，MySQL宕机了，那么就会出现redo log中显示事务已经提交了，而bin log中并不存在对应的事务信息。

  此时，MySQL主机通过redo log恢复，MySQL从机通过bin log恢复，这就导致了MySQL主从架构下，主从机数据不一致的问题。

第二步：两阶段提交是如何解决这个问题的？

  两阶段提交将redo log的提交拆分为两次提交，此时事务对redo log和bin log的写入顺序为

1、事务执行过程中，写入redo log并标记redo log为prepare阶段

2、事务完成后，写入bin log

3、写完bin log后，标记redo log为commit阶段

  在这样的顺序下，即使在第二步执行时，MySQL宕机了，MySQL主机在恢复时，会有三种情况：

1、redo log的状态为commit，代表bin log也成功写入了，则提交事务

2、redo log的状态为prepare，但bin log中存在对应的事务信息，依旧提交事务

3、redo log的状态为prepare，并且bin log中不存在对应的事务信息，则回滚事务。

可以看到，两阶段提交成功保证了MySQL主从机的数据一致性。

执行连接器

开始执行这条sql时，会检查该语句是否有权限，若是没有权限就直接返回错误信息，有权限会进行下一步，校验权限的这一步是在图一的连接器进行的，对连接用户权限的校验。

执行检索内存

相连建立之后，履行查询语句的时候，会先行检索内存，Mysql会先行冗余这个sql与否履行过，以此Key-Value的形式平缓使用内存中，Key是检索预定，Value是结果集。

假如内存key遭击中，便会间接回到给客户端，假如没命中，便会履行后续的操作，完工之后亦会将结果内存上去，当下一次进行查询的时候也是如此的循环操作。

执行分析器

分析器主要有两步：（1）词法分析（2）语法分析

词法分析主要执行提炼关键性字，比如select，提交检索的表，提交字段名，提交检索条件。

语法分析主要执行辨别你输出的sql与否准确，是否合乎mysql的语法。

执行优化器

查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法，最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。

生成执行计划的过程会消耗较多的时间，特别是存在许多可选的执行计划时。如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存。

当相似的语句再次被输入服务器时，就可以直接使用已缓存的执行计划，从而跳过SQL语句生成执行计划的整个过程，进而可以提高语句的执行速度。

MySQL使用基于成本的查询优化器。它会尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最少的一个。

执行执行器

由优化器生成的执行计划，交由执行器进行执行，执行器调用存储引擎的接口，存储引擎获取数据并返回，结束整个查询的过程。

这里只讲解了select的过程，对于update这些修改数据或者删除数据的操作，会涉及到事务，会使用两个日志模块，redo log和binlog日志。

以前的Mysql的默认存储引擎MyISAM引擎是没redo log的，而现在的默认存储引擎InnoDB引擎便是透过redo 复杂度来拥护事务的，保证事务能够准确的回滚或者提交，保证事务的ACID。

2.InnoDB底层原理解读

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

2.1.缓冲池

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

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

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

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

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

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

2.1.1 InnoDB的缓冲池缓存什么？有什么用？

缓存表数据与索引数据，把磁盘上的数据加载到缓冲池，避免每次访问都进行磁盘IO，起到加速访问的作用。 

速度快，那为啥不把所有数据都放到缓冲池里？

凡事都具备两面性，抛开数据易失性不说，访问快速的反面是存储容量小：

（1）缓存访问快，但容量小，数据库存储了200G数据，缓存容量可能只有64G；

（2）内存访问快，但容量小，买一台笔记本磁盘有2T，内存可能只有16G；

因此，只能把“最热”的数据放到“最近”的地方，以“最大限度”的降低磁盘访问。 

如何管理与淘汰缓冲池，使得性能最大化呢？ 

在介绍具体细节之前，先介绍下“预读”的概念。

2.1.2 什么是预读？

磁盘读写，并不是按需读取，而是按页读取，一次至少读一页数据（一般是4K），如果未来要读取的数据就在页中，就能够省去后续的磁盘IO，提高效率。

预读为什么有效？

数据访问，通常都遵循“集中读写”的原则，使用一些数据，大概率会使用附近的数据，这就是所谓的“局部性原理”，它表明提前加载是有效的，确实能够减少磁盘IO。

按页(4K)读取，和InnoDB的缓冲池设计有啥关系？

（1）磁盘访问按页读取能够提高性能，所以缓冲池一般也是按页缓存数据；

（2）预读机制启示了我们，能把一些“可能要访问”的页提前加入缓冲池，避免未来的磁盘IO操作；

InnoDB是以什么算法，来管理这些缓冲页呢？

最容易想到的，就是LRU(Least recently used)。

画外音：memcache，OS都会用LRU来进行页置换管理，但MySQL的玩法并不一样。

2.1.3 LRU算法 

传统的LRU是如何进行缓冲页管理？ 

最常见的玩法是，把入缓冲池的页放到LRU的头部，作为最近访问的元素，从而最晚被淘汰。这里又分两种情况：

（1）页已经在缓冲池里，那就只做“移至”LRU头部的动作，而没有页被淘汰；

（2）页不在缓冲池里，除了做“放入”LRU头部的动作，还要做“淘汰”LRU尾部页的动作； ​​​​​​​

如上图，假如管理缓冲池的LRU长度为10，缓冲了页号为1，3，5…，40，7的页。

假如，接下来要访问的数据在页号为4的页中：

（1）页号为4的页，本来就在缓冲池里；

（2）把页号为4的页，放到LRU的头部即可，没有页被淘汰；

画外音：为了减少数据移动，LRU一般用链表实现。

假如，再接下来要访问的数据在页号为50的页中：

（1）页号为50的页，原来不在缓冲池里；

（2）把页号为50的页，放到LRU头部，同时淘汰尾部页号为7的页；

传统的LRU缓冲池算法十分直观，OS，memcache等很多软件都在用，MySQL为啥这么矫情，不能直接用呢？

这里有两个问题：

（1）预读失效；

（2）缓冲池污染；

2.1.4 什么是预读失效？

由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据，称为预读失效。 

如何对预读失效进行优化？

要优化预读失效，思路是：

（1）让预读失败的页，停留在缓冲池LRU里的时间尽可能短；

（2）让真正被读取的页，才挪到缓冲池LRU的头部；

以保证，真正被读取的热数据留在缓冲池里的时间尽可能长。

具体方法是：

（1）将LRU分为两个部分：

新生代(new sublist)

老生代(old sublist)

（2）新老生代收尾相连，即：新生代的尾(tail)连接着老生代的头(head)；

（3）新页（例如被预读的页）加入缓冲池时，只加入到老生代头部：

如果数据真正被读取（预读成功），才会加入到新生代的头部

如果数据没有被读取，则会比新生代里的“热数据页”更早被淘汰出缓冲池

举个例子，整个缓冲池LRU如上图：

（1）整个LRU长度是10；

（2）前70%是新生代；

（3）后30%是老生代；

（4）新老生代首尾相连；

假如有一个页号为50的新页被预读加入缓冲池：

（1）50只会从老生代头部插入，老生代尾部（也是整体尾部）的页会被淘汰掉；

（2）假设50这一页不会被真正读取，即预读失败，它将比新生代的数据更早淘汰出缓冲池；

假如50这一页立刻被读取到，例如SQL访问了页内的行row数据：

（1）它会被立刻加入到新生代的头部；

（2）新生代的页会被挤到老生代，此时并不会有页面被真正淘汰；

改进版缓冲池LRU能够很好的解决“预读失败”的问题。

画外音：但也不要因噎废食，因为害怕预读失败而取消预读策略，大部分情况下，局部性原理是成立的，预读是有效的。

新老生代改进版LRU仍然解决不了缓冲池污染的问题。

2.1.5 什么是MySQL缓冲池污染？

当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。

例如，有一个数据量较大的用户表，当执行：

select * from user where name like "%shenjian%";

虽然结果集可能只有少量数据，但这类like不能命中索引，必须全表扫描，就需要访问大量的页：

（1）把页加到缓冲池（插入老生代头部）；

（2）从页里读出相关的row（插入新生代头部）；

（3）row里的name字段和字符串shenjian进行比较，如果符合条件，加入到结果集中；

（4）…直到扫描完所有页中的所有row…

如此一来，所有的数据页都会被加载到新生代的头部，但只会访问一次，真正的热数据被大量换出。

怎么这类扫码大量数据导致的缓冲池污染问题呢？

MySQL缓冲池加入了一个“老生代停留时间窗口”的机制：

（1）假设T=老生代停留时间窗口；

（2）插入老生代头部的页，即使立刻被访问，并不会立刻放入新生代头部；

（3）只有满足“被访问”并且“在老生代停留时间”大于T，才会被放入新生代头部； 

继续举例，假如批量数据扫描，有51，52，53，54，55等五个页面将要依次被访问。 

如果没有“老生代停留时间窗口”的策略，这些批量被访问的页面，会换出大量热数据。

加入“老生代停留时间窗口”策略后，短时间内被大量加载的页，并不会立刻插入新生代头部，而是优先淘汰那些，短期内仅仅访问了一次的页。

而只有在老生代呆的时间足够久，停留时间大于T，才会被插入新生代头部。

2.1.6 上述原理，对应InnoDB里哪些参数？

有三个比较重要的参数。

参数：innodb_buffer_pool_size

介绍：配置缓冲池的大小，在内存允许的情况下，DBA往往会建议调大这个参数，越多数据和索引放到内存里，数据库的性能会越好。

参数：innodb_old_blocks_pct

介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。

画外音：如果把这个参数设为100，就退化为普通LRU了。

参数：innodb_old_blocks_time

介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。

2.1.7 总结

（1）缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；

（2）缓冲池通常以页(page)为单位缓存数据；

（3）缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；

（4）InnoDB对普通LRU进行了优化：

将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题

页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

2.2.插入缓冲

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

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

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

2.2.1简单回顾一下buffer pool：

1. MySQL数据存储包含内存与磁盘两个部分；
2. 内存缓冲池(buffer pool)以页为单位，缓存最热的数据页(data page)与索引页(index page)；
3. InnoDB以变种LRU算法管理缓冲池，并能够解决“预读失效”与“缓冲池污染”的问题； 

毫无疑问，对于读请求，缓冲池能够减少磁盘IO，提升性能。问题来了，那写请求呢？

情况一

假如要修改页号为4的索引页，而这个页正好在缓冲池内。

如上图序号1-2：

（1）直接修改缓冲池中的页，一次内存操作；

（2）写入redo log，一次磁盘顺序写操作；

这样的效率是最高的。

画外音：像写日志这种顺序写，每秒几万次没问题。 

是否会出现一致性问题呢？

并不会。

读取，会命中缓冲池的页；

（2）缓冲池LRU数据淘汰，会将“脏页”刷回磁盘；

（3）数据库异常奔溃，能够从redo log中恢复数据；

什么时候缓冲池中的页，会刷到磁盘上呢？

定期刷磁盘，而不是每次刷磁盘，能够降低磁盘IO，提升MySQL的性能。

画外音：批量写，是常见的优化手段。

情况二

假如要修改页号为40的索引页，而这个页正好不在缓冲池内。

此时麻烦一点，如上图需要1-3：

（1）先把需要为40的索引页，从磁盘加载到缓冲池，一次磁盘随机读操作；

（2）修改缓冲池中的页，一次内存操作；

（3）写入redo log，一次磁盘顺序写操作； 

没有命中缓冲池的时候，至少产生一次磁盘IO，对于写多读少的业务场景，是否还有优化的空间呢？ 

这即是InnoDB考虑的问题，又是本文将要讨论的写缓冲(change buffer)。

画外音：从名字容易看出，写缓冲是降低磁盘IO，提升数据库写性能的一种机制。

2.2.2什么是InnoDB的写缓冲？

在MySQL5.5之前，叫插入缓冲(insert buffer)，只针对insert做了优化；现在对delete和update也有效，叫做写缓冲(change buffer)。 

它是一种应用在非唯一普通索引页(non-unique secondary index page)不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更(buffer changes)，等未来数据被读取时，再将数据合并(merge)恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。

画外音：R了狗了，这个句子，好长。

InnoDB加入写缓冲优化，上文“情况二”流程会有什么变化？

假如要修改页号为40的索引页，而这个页正好不在缓冲池内。

加入写缓冲优化后，流程优化为：

（1）在写缓冲中记录这个操作，一次内存操作；

（2）写入redo log，一次磁盘顺序写操作；

其性能与，这个索引页在缓冲池中，相近。

画外音：可以看到，40这一页，并没有加载到缓冲池中。

2.2.3 是否会出现一致性问题呢？

也不会。

（1）数据库异常奔溃，能够从redo log中恢复数据；

（2）写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间；

（3）数据读取时，有另外的流程，将数据合并到缓冲池； 

不妨设，稍后的一个时间，有请求查询索引页40的数据。

此时的流程如序号1-3：

（1）载入索引页，缓冲池未命中，这次磁盘IO不可避免；

（2）从写缓冲读取相关信息；

（3）恢复索引页，放到缓冲池LRU里；

画外音：可以看到，40这一页，在真正被读取时，才会被加载到缓冲池中。

2.2.4为什么写缓冲优化，仅适用于非唯一普通索引页呢？

InnoDB里，聚集索引(clustered index)和普通索引(secondary index)的异同，《1分钟了解MyISAM与InnoDB的索引差异》有详尽的叙述，不再展开。 

如果索引设置了唯一(unique)属性，在进行修改操作时，InnoDB必须进行唯一性检查。也就是说，索引页即使不在缓冲池，磁盘上的页读取无法避免(否则怎么校验是否唯一？)，此时就应该直接把相应的页放入缓冲池再进行修改，而不应该再整写缓冲这个幺蛾子。

除了数据页被访问，还有哪些场景会触发刷写缓冲中的数据呢？

还有这么几种情况，会刷写缓冲中的数据：

（1）有一个后台线程，会认为数据库空闲时；

（2）数据库缓冲池不够用时；

（3）数据库正常关闭时；

（4）redo log写满时；

画外音：几乎不会出现redo log写满，此时整个数据库处于无法写入的不可用状态。

什么业务场景，适合开启InnoDB的写缓冲机制？

先说什么时候不适合，如上文分析，当：

（1）数据库都是唯一索引；

（2）或者，写入一个数据后，会立刻读取它；

这两类场景，在写操作进行时（进行后），本来就要进行进行页读取，本来相应页面就要入缓冲池，此时写缓存反倒成了负担，增加了复杂度。 

什么时候适合使用写缓冲，如果：

（1）数据库大部分是非唯一索引；

（2）业务是写多读少，或者不是写后立刻读取；

可以使用写缓冲，将原本每次写入都需要进行磁盘IO的SQL，优化定期批量写磁盘。

画外音：例如，账单流水业务。

上述原理，对应InnoDB里哪些参数？

有两个比较重要的参数。

参数：innodb_change_buffer_max_size

介绍：配置写缓冲的大小，占整个缓冲池的比例，默认值是25%，最大值是50%。

画外音：写多读少的业务，才需要调大这个值，读多写少的业务，25%其实也多了。

参数：innodb_change_buffering

介绍：配置哪些写操作启用写缓冲，可以设置成all/none/inserts/deletes等。

2.3.两次写

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

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

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

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

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

2.3.1 redo log 为什么不能恢复部分写失效

我们知道，innodb的数据页一般大小是16KB，MySQL存取数据的最小单位也是页，而操作系统并不能保障一个数据页的原子性，也就是说当写入数据时，有可能在一个页中写入一半时（比如8K）数据库宕机，这种情况称为部分写失效（partial page write），从而导致数据丢失。
      大家也许会问，难道我不可以根据redo log进行数据恢复吗？答案是肯定的也是否定的，要分为两种情况：

1. 数据库宕机，物理文件完好无损，是可以通过redo log进行崩溃恢复。
2. 数据库宕机，正在刷新到磁盘的页发生partial page write，而正好在磁盘上的这个数据页由于宕机发生损坏，这时就无法通过redo log进行数据恢复了，为什么？我们必须要清楚的认识到，redo log里记录的是对页的物理操作！比如一条redo记录"page number  xx，偏移量 800  写记录 “this is abc”"，那当页损坏时，这条redo记录还有意义吗？于是在这种特殊情况下，doublewrite就派上用场啦！ 

2.3.2 doublewrite体系结构及工作流程

 doublewrite由两部分组成，一部分为内存中的doublewrite buffer，其大小为2MB，另一部分是磁盘上共享表空间（ibdata x）中连续的128个页，即2个区（extent），大小也是2M。doublewrite工作流程如下：
        1、当一系列机制（main函数触发、checkpoint等）触发数据缓冲池中的脏页进行刷新时，并不直接写磁盘，而是会通过memcpy函数将脏页先复制到内存中的doublewrite buffer,之后通过doublewrite buffer再分两次、每次1MB顺序写入共享表空间的物理磁盘上，由于在这个过程中，doublewrite页的存储时连续的，因此写入磁盘为顺序写，性能很高。（顺序写入表空间，性能很快）
        2、马上调用fsync函数，同步脏页进磁盘
    再将脏页写入实际的各个表空间文件，这时写入就是离散的了。各模块协作情况如下图（第一步应为脏页产生的redo记录logbuffer，然后logbuffer写入redo log file，为简化次要步骤直接连线表示）： 

2.4.自适应哈希索引

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

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

继续回答水友提问（最近问MySQL的多）：

网上看到不同的资料，有的说InnoDB支持哈希索引，有的说不支持，到底哪个是正确的呢？ 

对于InnoDB的哈希索引，确切的应该这么说：

1. InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引；
2. InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB自己会建立相关哈希索引，这一层上说，InnoDB又是支持哈希索引的； 

那什么是自适应哈希索引(Adaptive Hash Index, AHI)呢？原理又是怎样的呢？咱们先从一个例子开始。 不妨设有InnoDB数据表：t(id PK, name KEY, sex, flag)画外音：id是主键，name建了普通索引。 假设表中有四条记录：

1, shenjian, m, A

3, zhangsan, m, A

5, lisi, m, A

9, wangwu, f, B

如上图，通过前序知识，容易知道InnoDB在主键id上会建立聚集索引(Clustered Index)，叶子存储记录本身，在name上会建立普通索引(Secondary Index)，叶子存储主键值。 发起主键id查询时，能够通过聚集索引，直接定位到行记录。 

select * from t where name='ls';发起普通索引查询时：

1. 会先从普通索引查询出主键（上图右边）；
2. 再由主键，从聚集索引上二次遍历定位到记录（上图左边）。 

不管聚集索引还是普通索引，记录定位的寻路路径(Search Path)都很长。 在MySQL运行的过程中，如果InnoDB发现，有很多SQL存在这类很长的寻路，并且有很多SQL会命中相同的页面(page)，InnoDB会在自己的内存缓冲区(Buffer)里，开辟一块区域，建立自适应哈希所有AHI，以加速查询。

从这个层面上来说，InnoDB的自使用哈希索引，更像“索引的索引”，毕竟其目的是为了加速索引寻路。 

既然是哈希，key是什么，value是什么？key是索引键值（或者键值前缀）。value是索引记录页面位置。 

为啥叫“自适应(adaptive)”哈希索引？系统自己判断“应该可以加速查询”而建立的，不需要用户手动建立，故称“自适应”。 

系统会不会判断失误，是不是一定能加速？不是一定能加速，有时候会误判。 

当业务场景为下面几种情况时：

1. 很多单行记录查询（例如passport，用户中心等业务）
2. 索引范围查询（此时AHI可以快速定位首行记录）
3. 所有记录内存能放得下

AHI往往是有效的。
画外音：任何脱离业务的技术方案，都是耍流氓。 当业务有大量like或者join，AHI的维护反而可能成为负担，降低系统效率，此时可以手动关闭AHI功能。

2.5.页

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

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

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

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

2.6.重做日志缓冲

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

为了确保每次日志都能写入到事务日志文件中，在每次将log buffer中的日志写入日志文件的过程中都会调用一次操作系统的fsync操作(即fsync()系统调用)。

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

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

有个星球水友提问：

我们有一次MySQL崩溃，重启后发现有些已经提交的事务对数据的修改丢失了，不是说事务能保证ACID特性么，想问下什么情况下可能导致“事务已经提交，数据却丢失”呢？ 

这个问题有点复杂，且容我系统性梳理下思路，先从redo log说起吧。

画外音：水友问的是MySQL，支持事务的是InnoDB，本文以InnoDB为例展开叙述，其他数据库不是很了解，但估计原理是相同的。 

2.6.1 为什么要有redo log？

事务提交后，必须将事务对数据页的修改刷(fsync)到磁盘上，才能保证事务的ACID特性。 这个刷盘，是一个随机写，随机写性能较低，如果每次事务提交都刷盘，会极大影响数据库的性能。 

2.6.2 随机写性能差，有什么优化方法呢？

架构设计中有两个常见的优化方法：

1. 先写日志(write log first)，将随机写优化为顺序写；
2. 将每次写优化为批量写；

这两个优化，数据库都用上了。 

先说第一个优化，将对数据的修改先顺序写到日志里，这个日志就是redo log。 

假如某一时刻，数据库崩溃，还没来得及将数据页刷盘，数据库重启时，会重做redo log里的内容，以保证已提交事务对数据的影响被刷到磁盘上。 

一句话，redo log是为了保证已提交事务的ACID特性，同时能够提高数据库性能的技术。

 既然redo log能保证事务的ACID特性，那为什么还会出现，水友提问中出现的“数据库奔溃，丢数据”的问题呢？一起看下redo log的实现细节。 

2.6.3 redo log 三层架构

花了一个丑图，简单说明下redo log的三层架构：

粉色，是InnoDB的一项很重要的内存结构(In-Memory Structure)，日志缓冲区(Log Buffer)，这一层，是MySQL应用程序用户态

屎黄色，是操作系统的缓冲区(OS cache)，这一层，是OS内核态

蓝色，是落盘的日志文件

 redo log最终落盘的步骤如何？

首先，事务提交的时候，会写入Log Buffer，这里调用的是MySQL自己的函数WriteRedoLog； 

接着，只有当MySQL发起系统调用写文件write时，Log Buffer里的数据，才会写到OS cache。注意，MySQL系统调用完write之后，就认为文件已经写完，如果不flush，什么时候落盘，是操作系统决定的；

 最后，由操作系统（当然，MySQL也可以主动flush）将OS cache里的数据，最终fsync到磁盘上； 

操作系统为什么要缓冲数据到OS cache里，而不直接刷盘呢？

这里就是将“每次写”优化为“批量写”，以提高操作系统性能。 

数据库为什么要缓冲数据到Log Buffer里，而不是直接write呢？

这也是“每次写”优化为“批量写”思路的体现，以提高数据库性能。

画外音：这个优化思路，非常常见，高并发的MQ落盘，高并发的业务数据落盘，都可以使用。

 redo log的三层架构，MySQL做了一次批量写优化，OS做了一次批量写优化，确实能极大提升性能，但有什么副作用吗？

画外音：有优点，必有缺点。 

这个副作用，就是可能丢失数据：

1. 事务提交时，将redo log写入Log Buffer，就会认为事务提交成功；
   （2）如果写入Log Buffer的数据，write入OS cache之前，数据库崩溃，就会出现数据丢失；
   （3）如果写入OS cache的数据，fsync入磁盘之前，操作系统奔溃，也可能出现数据丢失；

画外音：如上文所说，应用程序系统调用完write之后（不可能每次write后都立刻flush，这样写日志很蠢），就认为写成功了，操作系统何时fsync，应用程序并不知道，如果操作系统崩溃，数据可能丢失。 

任何脱离业务的技术方案都是耍流氓：

1. 有些业务允许低效，但不允许一丁点数据丢失；
2. 有些业务必须高性能高吞吐，能够容忍少量数据丢失；

MySQL是如何折衷的呢？ 

MySQL有一个参数：innodb_flush_log_at_trx_commit 

能够控制事务提交时，刷redo log的策略。 

目前有三种策略：

策略一：最佳性能(innodb_flush_log_at_trx_commit=0)

每隔一秒，才将Log Buffer中的数据批量write入OS cache，同时MySQL主动fsync。

这种策略，如果数据库奔溃，有一秒的数据丢失。

  策略二：强一致(innodb_flush_log_at_trx_commit=1)

每次事务提交，都将Log Buffer中的数据write入OS cache，同时MySQL主动fsync。

这种策略，是InnoDB的默认配置，为的是保证事务ACID特性。 

策略三：折衷(innodb_flush_log_at_trx_commit=2)

每次事务提交，都将Log Buffer中的数据write入OS cache；每隔一秒，MySQL主动将OS cache中的数据批量fsync。

画外音：磁盘IO次数不确定，因为操作系统的fsync频率并不是MySQL能控制的。这种策略，如果操作系统奔溃，最多有一秒的数据丢失。

画外音：因为OS也会fsync，MySQL主动fsync的周期是一秒，所以最多丢一秒数据。

讲了这么多，回到水友的提问上来，数据库崩溃，重启后丢失了数据，有很大的可能，是将innodb_flush_log_at_trx_commit参数设置为0了，这位水友最好和DBA一起检查一下InnoDB的配置。 

可能有水友要问，高并发的业务，InnoDB运用哪种刷盘策略最合适？
高并发业务，行业最佳实践，是使用第三种折衷配置（=2），这是因为：

1. 配置为2和配置为0，性能差异并不大，因为将数据从Log Buffer拷贝到OS cache，虽然跨越用户态与内核态，但毕竟只是内存的数据拷贝，速度很快；
2. 配置为2和配置为0，安全性差异巨大，操作系统崩溃的概率相比MySQL应用程序崩溃的概率，小很多，设置为2，只要操作系统不奔溃也绝对不会丢数据。

总结
一、为了保证事务的ACID特性，理论上每次事务提交都应该刷盘，但此时效率很低，有两种优化方向：
（1）随机写优化为顺序写；
（2）每次写优化为批量写；

二、redo log是一种顺序写，它有三层架构：
（1）MySQL应用层：Log Buffer
（2）OS内核层：OS cache
（3）OS文件：log file

三、为了满足不用业务对于吞吐量与一致性的需求，MySQL事务提交时刷redo log有三种策略：
（1）0：每秒write一次OS cache，同时fsync刷磁盘，性能好；
（2）1：每次都write入OS cache，同时fsync刷磁盘，一致性好；
（3）2：每次都write入OS cache，每秒fsync刷磁盘，折衷；

四、高并发业务，行业内的最佳实践，是：
innodb_flush_log_at_trx_commit=2

2.7.日志

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

MySQL层面

InnoDB层面

2.8.MySQL日志

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

2.8.1错误日志

错误日志文件对mysql的启动，运行，关闭过程进行了记录，mysql DBA遇到问题，首先查看该日志文件来定位，该日志文件记录了所有的错误信息，和一些警告。来通过SHOW VARIBALES LIKE 'log_error' 来定位该文件。

在错误日志文件得到优化的帮助，因为一些警告说明问题所在，例如：下面错误信息，能告诉用户，需要增大Innodb存储引擎的redo_log:

2.8.2 慢查询日志

这里记录了所有响应时间超过阈值的SQL语句，这个阈值我们可以自己设置，参数为long_query_time，其默认值为10s，且默认是关闭的状态，需要手动的打开。 通过慢查询日志对sql进行捕获，是一个非常有用的优化技巧，当数据库的容量小时，可能因为数据库刚刚建立，此时非常大的可能是数据全被缓存在缓冲池中，sql语句运行的时间非常短，一般是0.5s。

默认是关闭的，使用命令 set global slow_query_log=1;开启

使用命令 show variables like ‘long_query_time’;  查看超时时间

使用命令 set global long_query_time=4; 设置超时时间

slow_log中query_time增加了对逻辑读(logical reads)和物理读(physical reads)的统计,这里的物理读，值从磁盘进行IO的次数，逻辑读包含了所有的读取，不管是磁盘还是缓冲区。

查看慢sql, 5.1后慢sql存入表中：select * from mysql.slow_log

2.8.3查询日志

查询日志 记录了来自客户端的所有语句，记录了对mysql的请求信息，无论这些信息是否得到正确的执行。5.1之后记录在表中general_log，通过sql语句查询。

2.8.4 二进制文件（优化点）

二进制文件 它有另外一个名字你应该熟悉，叫Binlog，以事件event的形式记录了MySQL的库表结构以及表数据的所有变更信息。通过配置参数log-bin[=name]来启动二进制日志，默认未启动，官方测试开启性能影响1%，考虑可以用复制和数据point-in-time恢复，这些性能的损失是可以接受的。

影响二进制日志记录的信息和行为的配置：

max_binlog_size :指定了单个二进制日志文件的最大值（默认1G），超过后产生一个新的日志文件，后缀名加1，并记录到  .index文件中。

binlog_cache_size:  当使用带有事务的存储引擎时（例如innodb），所有未提交事务的二进制日志记录会被记录到一个缓存中，提交事务后，直接将缓存中的日志写入二进制日志文件，此缓存默认32k.是基于会话（session）实现的,也就是说当一个线程开启一个事务，mysql会自动分配一个缓存，该值设置要小心，不能过大。当事物的记录大于该值时，会把缓冲中的日志写入一个临时文件中，因此该值不能太小。

SHOW GLOBAL STATUS 命令查看binlog_cache_use（记录在缓存中的次数）, binlog_cache_disk_use（记录在磁盘中的次数）的状态，可以判断当前binlog_cache_size设置是否合适。

sync_binlog: 默认情况下，二进制日志不是每次写都同步到磁盘(缓冲写)，因此宕机后，会存在缓冲中的数据丢失。参数sync_binlog=[N],表示每次缓存多少同步到磁盘。如果N是1，表示有一条就同步到磁盘，这时候写操作不适用操作系统缓冲来写二进制日志文件。默认是0，考虑系统的高可用，建议设置为ON，会对系统IO带来影响。

这个参数是对于MySQL系统来说是至关重要的，他不仅影响到Binlog对MySQL所带来的性能损耗，而且还影响到MySQL中数据的完整性。

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。

从以往经验和相关测试来看，对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

show variables like 'sync_binlog';

set global sync_binlog=0;

binlog_do_db和binlog_ignore_db：表示需要写入或者需要忽略写入哪些库的数据日志，默认为空，表示需要同步所有库的日志到二进制日志文件。

log_slave_update：如果当前数据库是复制中的slave角色，则他不会将从master取得并执行的二进制日志写入到自己的二进制日志文件中，如果需要写入，要设置log_slave_update。如果需要搭建master=>slave=>slave架构的复制，则必须设置该参数。

binlog_format：此参数很重要，影响了记录二进制日志的格式（5.1之前没有该参数），其记录了对数据库所有的更改其记录日志有三种格式。分别是Statement、Row和MixedLevel。

Statement 和之前一样记录所有会修改数据的SQL，其只会记录SQL，并不需要记录下这个SQL影响的所有行，减少了日志量，提高了性能。但是由于只是记录执行语句，不能保证在Slave节点上能够正确执行，所以还需要额外的记录一些上下文信息

Row 只保存被修改的记录，与Statement只记录执行SQL来比较，Row会产生大量的日志。但是Row不用记录上下文信息了，只需要关注被改成啥样就行。

MixedLevel 就是Statement和Row混合使用。

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

binlog_format 是个动态参数，可在数据库运行时更改，例如：我们可以把当前会话的binlog_format设置为Row

当然，也可以将全局的binlog_format设置为想要的格式，不过通常这个操作会带来问题，运行时要确保更改后不会对复制带来影响。如:

在通常情况下，我们将参数binlog_format设置为Row,这可以为数据库的恢复和复制带来更好的可靠性。但是不能忽略的一点是，这会带来二进制文件大小的增加，有些语句下的ROW格式可能需要更大的容量。比如我们有两张一样的表，大小都为100W,分别执行UPDATE操作，观察二进制日志大小的变化:在Statement下二进制日志文件只增加了200字节，在Row下增加了13M.

二进制日志主要有一下几种作用：

恢复：某些数据的恢复需要二进制日志，例如在一个数据库全备文件恢复后，用户可以通过二进制文件进行point-in-time的恢复。

复制：其原理与恢复类似，通过复制和执行二进制日志文件，是一台远程的mysql数据库，进行实时同步。

审计：用户可以通过二进制日志文件的信息来进行审计，判断是否有对数据库进行注入的攻击。

2.9.InnoDB日志

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

2.9.1 Redo Log 重做日志

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

2.9.2 Undo Log 

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

1.undo log有两个作用：提供回滚和多个行版本控制(MVCC)。

在数据修改的时候，不仅记录了redo，还记录了相对应的undo，如果因为某些原因导致事务失败或回滚了，可以借助该undo进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。

另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

1. delete/update操作的内部机制

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

• delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
• update分为两种情况：update的列是否是主键列。
  • 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
  • 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

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

2.9.3 多版本并发控制

InnoDB为数据库中的每一行添加了三个隐藏字段：DB_TRX_ID(事务版本号)、DB_ROLL_PTR(回滚指针)、DB_ROW_ID(隐藏ID)。

1. DB_TRX_ID：记录了创建/更新这条数据的事务版本号（版本号会递增）。
2. DB_ROLL_PTR：记录了一个指向undo log中历史版本的数据指针。（用来支持回滚操作）
3. DB_ROW_ID：一个自增的隐藏行ID。

InnoDB基于事务版本号、回滚指针这两个字段，可以在undo log中形成一个单向链表，最新版本的数据放在链表头部，历史数据通过DB_ROLL_PTR(回滚指针)指针进行关联。如下图所示

有了这种结构的数据后，InnoDB可以很方便的管理多个版本的数据，也为MVCC的实现打下来基础。

MVCC解决了哪些问题？

MVCC在InnoDB中具体的实现逻辑是怎样的，以及MVCC解决了哪些问题。

首先，InnoDB在事务开启后执行第一个查询时，会创建一个快照（下文称之为ReadView），这个ReadView包含了以下信息

    m_ids: 活动事务id列表（活动事务指的是已经开始、尚未提交/回滚的事务）

    min_trx_id: 最小活动事务id

    max_trx_id:最大活动事务id

    creator_trx_id:当前事务id

紧接着InnoDB会通过查询语句定位到最新版本的数据行，并根据以下规则获取到可以访问的数据版本。

    如果被访问版本的trx_id，与readview中的creator_trx_id值相同，表明当前事务在访问自己修改过的记录，直接返回该版本的数据；

    如果被访问版本的trx_id，小于readview中的min_trx_id值，表明生成该版本的事务在当前事务生成readview前已经提交，直接返回该版本的数据；

    如果被访问版本的trx_id，大于或等于readview中的max_trx_id值，表明生成该版本的事务在当前事务生成readview后才开启，此时该版本不可以被当前事务访问，需要通过隐藏的回滚指针从undo log中读取历史版本；

    如果被访问版本的trx_id，在readview的min_trx_id和max_trx_id之间，则需要判断trx_id值是否在m_ids列表中？

        如果在：说明readview创建时，创建该版本数据的事务还未提交，因此需要通过回滚指针读取历史版本并返回。

        如果不在：说明readview创建时，创建该版本数据的事务已经提交，所以直接返回该版本的数据；

可重复读隔离级别下，ReadView只会在第一次查询时创建，同一个事务中后续所有的查询共用一个ReadView，由此便解决了不可重复读的问题。

读已提交隔离级别下，每次查询都会创建一个新的ReadView。新建的ReadView会更新creator_trx_id以外的其余字段，因此不可重复读现象依然存在。

但是由于ReadView可以判断出修改此数据的事务是否已经提交，因此可以避免脏读的出现。

其次，从上述MVCC实现逻辑中可以发现，没有任何加锁、获取锁的操作，因此MVCC读操作不会因为等待锁而阻塞（也就是常说的非阻塞读）。

总结

MVCC可以解决脏读、不可重复读，并且实现了非阻塞读的功能。

读已提交隔离级别：每次读操作都会设置和读取自己的新快照（ReadView）。

可重复读隔离级别：同一个事务共用第一次查询时建立的快照（ReadView）。

当前读与快照读

最后扩展一个延伸的知识点，其实Mysql中的读操作可以分为两大类：快照读与当前读。

快照读是指通过MVCC实现的非阻塞读，常见的快照读操作如下：

    select xxx from xxx

当前读也叫加锁读，每次读取数据都是读取数据的最新版本，并且会对其进行加锁。常见的当前读操作如下

    select xxx from xxx lock in share mode (共享锁/读锁)

    select xxx from xxx for update （排它锁/写锁）

    update 、delete、insert

为什么要区分这两种读操作呢？因为MVCC并不能解决幻读的问题。即使是在可重复读级别，通过当前读依然会出现幻读问题。此问题最终是通过间隙锁来解决的。

2.10.InnoDB和MyISAM的区别

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

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

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

End

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