InnoDB内存优化

1、InnoDB用一块内存区做IO缓存池，该缓存池不仅用来缓存索引块，而且也用来缓存数据块，这一点与MyISAM不同，MyISAM只缓存索引块。

2、缓存池逻辑上分为free list：空闲缓存块列表。

flush list :需要刷新到磁盘的缓存块列表。

LRU list:InnoDB正在使用的缓存块，它是InnoDB buffer pool的核心。

3、InnoDB的LRU算法大致原理：LRU list 分为young sublist 和 old sublist ,数据从磁盘读入时，会将该缓存块插入到LRU list的“中点”,即old sublist 的头部；经过一定时间的访问（innodb_old_blocks_time   系统参数决定），该数据块将会由old 转移到young list的头部，也就是LRU list 的头部，随着时间推移，young sublist 和 old sublist 中较少被访问的缓存块将从各自的链表头部逐渐向尾部移动；需要淘汰时，优先从链表尾部淘汰。这种设计是为了防止偶尔被访问的索引块将访问频繁的热快淘汰。

4、innodb_buffer_pool_size的大小决定存储引擎表数据和索引数据的最大缓存区大小，在保证操作系统及其他程序足够内存可用的情况下，越大越好，这样缓存命中率越高，访问磁盘IO就越少性能越高。在专用的服务器上可以将80%的物理内存分配给InnoDB buffer pool ,但一定要注意避免设置过大而导致页交互。

缓存命中率 = （1-innodb_buffer_pool_reads/innodb_buffer_poolread_request)*100

如果命中率太低，则应考虑扩充内存、增加innoDB_buffer_pool_size的值。

5、innodb-old_blocks_time参数决定了缓存数据块有old sublist 转移到 young sublist的快慢。

6、调整缓存池的数量，减少内部对缓存池数据结构的争用。InnoDB_buffer_pool_instances配置参数，对于较大缓存池适当调大参数值。

7、缓存刷新：在InnoDB找不到干净的可用缓存页或检查点被触发等情况下，InnoDB的后台线程就会开始把“脏的缓存页”回写到磁盘文件中，这个过程较刷新。可以通过控制延长数据缓存时间，减缓IO。

8、InnDB doublewrite---解决页内写的一致性。

InnDB doublewrite：为了解决 partial page write 问题 ，当mysql将脏数据flush到data file的时候, 先使用memcopy 将脏数据复制到内存中的double write buffer ，之后通过double write buffer再分2次，每次写入1MB到共享表空间，然后马上调用fsync函数，同步到磁盘上，避免缓冲带来的问题，在这个过程中，doublewrite是顺序写，开销并不大，在完成doublewrite写入后，在将double write buffer写入各表空间文件，这时是离散写入。如果发生了极端情况（断电），InnoDB再次启动后，发现了一个Page数据已经损坏，那么此时就可以从doublewrite buffer中进行数据恢复了。

mysql 为何需要Double Write？有redo log还不够吗？具体参见

redo log解决事务写一致性包括多个页，但是如果发生页写到一半服务器宕机（这时不知道这个页写了多少），需要通过doublewrite的整个页进行覆盖。doubelwrite记录的是完整的数据页，redo是记录的修改的逻辑内容但是没有完整的基础打底数据。

这样的一个redo log取长补短，难道就没有什么问题了吗？其实数据的一致性问题仍然没有解决，如果在redo log应用到磁盘 时，在写一个Page到磁盘时发生了故障，可能导致Page Header的记录数被加1了（表示已此page已恢复完成），但是页内的逻辑日志在发生了故障，这时数据就不一致了。好在这个问题被缩小到了一个页中，比较好解决。而mysql就是用double write来的方法来解决此问题。

由于innodb page是16K，一般系统page是4k,当有个update语句需要对业内记录加1，当第一个4k中记录加1后，系统宕机，重启恢复时候，innodb 不知道从哪里给记录加1，如果给16k里所有记录都加1，就会导致第一个4k里面记录加2，必然导致数据不一致，这个时候double write buffer就解决了这个问题。

  Double Write的思路很简单:

  A. 在覆盖磁盘上的数据前，先将Page的内容写入到磁盘上的其他地方(InnoDB存储引擎中的doublewrite  buffer，这里的buffer不是内存空间，是持久存储上的空间).

  B. 然后再将Page的内容覆盖到磁盘上原来的数据。

  如果在A步骤时系统故障，原来的数据没有被覆盖，还是完整的。
  如果在B步骤时系统故障，原来的数据不完整了，但是新数据已经被完整的写入了doublewrite buffer.  因此系统恢复时就可以用doublewrite buffer中的新Page来覆盖这个不完整的page.

9、通过redo log机制来保证事务更新的一致性和持久性。

10、redo log 机制：具体详情参见

Redo Log 是一个预写日志（WAL），是一种用于在数据库或数据库所在主机发生崩溃时确保数据完整性的技术。Redo Log日志记录必须在数据实际更改(buffer pool中的脏页刷新到数据文件)之前写入磁盘。因此，对数据表做修改时，每个数据记录的修改都会写入Redo Log Buffer中(作为重做日志记录)。当一个页面的修改操作完成时，Redo Log Buffer将执行flush到Redo Log文件操作(但此时可能并未sync到磁盘)。

根据WAL日志先行原则，buffer pool中的脏页被刷新到数据文件中之前，需要确保对应LSN号的Redo Log先sync到磁盘文件中，Redo Log的刷盘机制以及脏页的刷盘机制确保了Redo Log总是先于脏页落盘。另外，如果系统参数innodb_flush_log_at_trx_commit设置为1，则每个事务提交时也会将Redo Log sync到磁盘文件中。

什么是Undo？

用于撤消(或还原)对InnoDB中存储的数据的变更及回滚事务，也用于实现多版本控制(mvcc)，通过构建一致性视图(read view)实现对数据库的一致性读。

对数据库的每一次更改，Undo Log都会保存之前版本的数据，每个聚簇（PK）索引记录都有一个指向该修改记录之前版本数据的指针（称为“回滚指针”），每个Undo Log记录都会存储一个回滚指针指向之前版本的数据，另外，每个Undo Log的变更也必须记录到Redo Log中。

2. Update流程

2.1 事务start(事务首次开启)

为这个事务分配事务ID（TRX_ID），该事务ID可能被写入系统表空间的TRX_SYS页面中的最大的事务ID字段（Transaction ID） 
* 如果系统表空间的TRX_SYS页面中的最大的事务ID字段被更新，则该更新会被记录到Redo Log中。

根据分配的TRX_ID创建read view。

2.2 记录修改（每次只修改一行记录）

• 分配Undo Log日志空间
• 拷贝该记录修改之前的值到Undo Log中
• 将Undo Log的修改记录写入Redo Log中
• 在buffer pool中修改数据页，回滚段指针指向Undo Log中该记录之前的版本
• 将该记录对应的数据页变更部分写入Undo Log中，应该是Redo log吧？？？
• buffer pool中该记录修改之后的数据页被标记为”脏页”（需要刷新到磁盘的数据页）

2.3 此时其他事务的修改会怎样？

一旦记录被修改，即使没有提交，其他事务也可能会看到被修改后的记录，这依赖于他们的事务隔离级别而定：

• 如果是RU隔离级别，则使用索引页读取最新版本记录
• 如果是RU隔离级别，则查找记录的最新提交版本
• 如果是RR隔离级别，则查找与其read view相对应的记录版本

任何需要使用索引页来读取比最新的版本记录旧的版本记录时，都必须使用Undo Log来重建之前的版本记录。

2.4 事务提交（显式和隐式提交）

• 事务对应的Undo Log页被设置为”purge”（意味着当这个Undo Log页不再被任何其他事务引用时可以将其清除）
• 将Undo Log的修改记录写入Redo Log中
• Redo Log Buffer刷新到磁盘（是否刷盘取决于系统变量innodb_flush_log_at_trx_commit的设置）

2.5 后台线程刷脏（后台线程连续不断地根据不同触发机制触发刷新）

• 查找最旧的“脏”页面(修改时间最早的页面)并将其添加到flush batch中
• 确保在flush batch中中最新的LSN号已经写入到了Redo Log中且已经落盘
• 如果开启了双写，则先将脏页刷新到双写缓冲区（并等待同步）
• 将每个脏页从buffer pool中写入最终目的地：表空间文件中的

PS：对于后台线程刷脏部分，执行刷新脏页时，与该脏页的事务是否提交无关，只需要确保该页对应LSN号的Redo Log记录落盘，而不会去判断事务的状态是否是提交还是未提交状态，因为，数据页结构中并没有地方单独记录事务的状态(即，无法判断事务是否提交），只是在每行数据中有记录事务号、回滚段指针(所以一个页中也可能包含多个事务的修改记录)。当需要对某个事务进行回滚时，重新从表空间中读取这个未提交的脏页，使用undo log中的反向数据进行反向修改，然后再重新刷脏。

2.6 定期执行Checkpoint

确保比Checkpoint 点更旧(比Checkpoint LSN小)的脏页已刷新到表空间文件，如果存在有比Checkpoint LSN大的脏页，则立即刷新脏页到数据文件中。说白了Checkpoint机制主要作用就是用于刷新脏页。

把Checkpoint LSN写到Redo Log Header中 （从这个Checkpoint LSN开始，之前的Redo Log记录不再需要）。

2.7 后台线程Purge(后台线程连续不断地根据需要定期执行Purge，包括Undo Log和历史链表)

• 查找每个回滚段中不再需要的最旧的Undo Log
• 实际上是从索引中删除任何带有删除标记的记录
• 释放Undo Log页
• 修剪history lists

12、什么时候把缓存更新记录刷新到redo log做持久化？？
通过innodb_flush_log_at_trx_commit参数设置，默认值为1：每个事务提交时，InnoDB立即将缓存中的redo日志回写到日志文件:，并调用操作系统fsync刷新到IO缓存到数据文件。
0：在事务提交时不会触发缓存回写日志文件，而是每秒触发一次。

2：提交事务时，立即将缓存中的redo日志回写日志文件，但并不立即调用fsync刷新IO缓存，而是每秒做一次磁盘IO缓存刷新。

13、innodb_log_buffer_size决定重做日志缓存池的大小默认为8M。在发生大量更新事务时增加该参数大小可以避免在事务提交前执行不必要的日志写入文件。

14、max_connextions最大连接数；open-files-limit最大打开的文件句柄数。

15、back_log连接数的端口缓冲积压数。

16、table_open_cache控制所有SQL执行线程打开表缓存的数量。

17、innodb_lock_wait_timeout行锁的默认超时时间。