下面是对innodb使用手册的翻译以及部分的个人理解。

在遇到不懂的概念时，可以通过mysql 术语表直接查询dev.mysql.com/doc/refman/…获取认知，这里都用非常简明的描述清晰表达了。

使用手册原文：dev.mysql.com/doc/refman/…。

Innodb简介

InnoDB是兼顾高可靠和高性能的通用存储引擎。

主要的优势：

DML操作遵循ACID模型。事物有commit，rollback，crash-recovery功能来保护。

行级别的锁和oracle风格的一致性读提高了多用户场景下的并发度和性能。

Innodb基于主键来组织在磁盘中的数据。每个innodb表都有一个主键索引被称为聚集(clusted)索引来组织数据，以提供最小的I/O主键查询操作。

为了维护数据的完整性，Innodb也支持foreign key约束。通过foreign key的约束可以解决对相关表的不一致问题。

支持的特性列表

特性	支持	特性	支持
B-tree 索引	是	foreign key	是
备份/按时间点 恢复	是(通过server来实现，而不是innodb引擎)	全文检索	是
数据库集群	否	地理空间类型	是
聚集索引	是	地理空间支持	是
压缩数据	是	hash索引	不支持（应用hash index在内部的自适应哈希索引特性上(Adaptive hash index)）
数据cache	是	index cahce	是
数据加密	是	locking granularty	行
MVCC	是	副本支持	是(通过server来实现，而不是innodb引擎)
单个表空间存储限制	64TB	T-tree 索引	否
事物	是	数据字典的统计	是

Innodb表优势

• 如果server突然退出，用户无需做特殊处理。Innodb可以自动从已经提交的事物中crash recover。并且取消没有提交的变更。

• innodb通过数据访问的方式维护自己的buffer pool，用于缓存表和索引中的数据。经常使用的数据会从内存中直接获取。再专用的数据库server上，有80%的内存被分配到了buffer pool。

• 如果用户将相关的数据分派到了不同的表中，可以使用foreign key来做关联。

• 如果在内存或在硬盘中的数据损坏了，那么可以通过checksum来在使用前提醒你数据是损坏的。

• 当你为表设置里合理的主键索引，那么通过引用主键索引where、order by 与group by和join可以非常快。

• 通过一种称为change buffer的自动化机制。innodb不仅仅支持对同一个表的并发读写，它也缓存变更的数据，用于streamline磁盘IO。

• 对性能的收益并不局限于对达标的long-running查询。当对于同一张表的行反复读取时，自适应哈希索引可以让这种类型的查询加速。

• 支持对索引和数据表的压缩。

• 可以在执行创建或者删除索引或者其他DDL语句时候尽可能少第影响到性能和可用性。

• 删除一个 file-per-table的空间非常迅速。

  file-per-table对于操作系统来说就是一个文件。对innodb来说是包含了数据和索引的tablespace。文件以后缀idb结尾。

  好处是可以最大化单表大小与多表读写性能。

  dev.mysql.com/doc/refman/…

• 使用DYNAMIC行类型可以更有效去存储BLOB以及长文本字段数据。
• 可以通过查询INFORMATION_SCHEMA下面的表来监控内部的情况。
• 可以通过查询PERFORMANCE SCHEMA下面的表来查看性能细节。

Innodb表最佳实践

• 为每个表制定最常用的字段作为主键，如果没有明显的主键可以使用自增。
• 当需要从多表中通过某些唯一ID拉取数据时使用joins。为了提高join的性能可以指定foreign keys，指定后可以保证这些依赖的列被索引。
• 关闭autocommit。每秒提交数百次会限制性能（由于磁盘的写入速度限制）。
• 将相关的DML操作放在一起包括在START TRANSACTION和COMMIT语句内。虽然不想频繁提交，但是也不应该因为插入、更新和删除语句执行数小时而不提交。
• 不要使用锁表的语句。如果需要排他访问数行，可以使用SELECT...FOR UPDATE语句。
• 评估压缩是否会影响到性能。

InnoDB和ACID模型

ACID是一套数据库设计的准则。在实际环境中，如果允许一部分的数据丢失或者不一致可以稍微放开对ACID的限制以达到更好的性能和吞吐。

• A: atomicity 主要涉及到的是Innodb的事物。

  包括autocommit配置，commit语句，rollback语句。

• C: consistency 一致性主要涉及到的是Innodb在crash时候保护数据。

  对于mysql的特性是doublewirte buffer。

  Innodb crash recovery。

• I: Isolation 主要影响到的是innodb的事物。尤其是事物之间的隔离级别。涉及到的特性包括：

  autocommit设置

  事物隔离级别的设定 set. transaction语句。

  low-level的锁细节。

• Durability

  对持久化更多影响的是硬件的配置。对于mysql来说包含以下特性：

  double write buffer

  innodb_flush_log_at_trx_commit变量

  sync_binlog变量

  innodb_file_per_table变量

  对于存储设备的wite buffer如硬盘驱动，SSD以及RAID阵列

  一个有备用电池的存储设备。

  mysql运行的操作系统，尤其是是不是支持fsync系统调用

  一个UPS(uninterruptible pwer supply)保护供电

  你的备份策略，例如频率类型以及保留策略

  对于分布式的应用，还与机房的硬件与网络连接有关

InnoDB 多版本 Multi-Versioning

innodb是一个多版本的存储引擎。通过保存有变化的行的历史版本信息完成并发控制和回滚。这些信息存储在系统表空间或者undo表空间的回滚段中。当需要回滚时，innodb使用回滚段中的数据去执行undo操作。这个信息也被用于构建更早的版本，用于一致性读。

undo log内存的不是历史版本，而是undo操作

在内部，每一行都会增加额外的标记

• 6字节 DB_TRX_ID。用于记录最新一次的更新或者新增事物。另外，删除在内部实际上也是一种更新。只是有一个特殊的位被标记。
• 7字节 DB_ROLL_PTR 用于指向回滚段中的undo log
• 6字节 DB_ROW_ID 当Innodb自动去省城一个聚集索引时会包含这一列。

在回滚段中的undo logs被分为insert和update undo logs。insert的undo logs只在事物回滚时有效，只要事物一提交就可以丢弃这个日志。update的undo log还被应用在一致性读上，和insert undo log不同的是这个日志是当innodb中不再有事物需要利用undo log去构建之前的版本，才可以丢弃掉这些日志。更细节的内容会在undo log一节中讨论。

建议你定期提交事物，包括对于那些只涉及到一致性读取的事物。否则，像上边提高到，innodb不能丢弃那些update的undo logs，进而导致回滚段会变得过大，把它所在的表空间给填满。

undo log实际占用的物理2空间大小通常要小于与其关联的inset或update行。你可以用这个信息来计算回滚段需要的大小。

在innodb的多版本计划中，对于删除了的行并不是在执行完语句后就直接执行了。而是需要等待为了删除而做的相关的数据和索引的更新undo log废弃，才可以清除。这个删除的操作术语叫做purge，这种删除动作会非常快。通常与执行删除语句的时间顺序相同。

当你用大致相同的速率去小批量添加和删除行的时候，purge线程会开始滞后，这会导致数据表因为一些死亡数据而变得越来越大，然后会让所有操作都受限于磁盘并且变得缓慢。当发生了这种情况时，需要限制新增操作，然后可以给purge线程分配更多的资源，比如可以通过调节innodb_max_purge_lag这个系统变量。

Multi-versioning和二级索引

与聚集索引不同，二级索引上没有各种隐藏列，也不能在行上直接更新。当一个耳机索引被更新时，旧记录会被增加删除标记，并且插入一个新的记录。有删除标记的记录将在之后被清理掉。当一个二级索引行被标记为了已删除，Innodb会查询聚集索引，检测这行数据版本号DB_TRX_ID，然后顺着undo log链去获取正确的版本数据。

当一个二级索引被标记了删除，covering index(覆盖索引)将不能被使用，需要去聚集索引中获取数据。（注：这一点其实比较好理解，因为并不知道当前覆盖索引对应的数据是不是当前需要的版本）。

当 index condition pushdown(ICP)优化被开启，当where语句中被评估可以使用到索引，实际还是会使用索引去查询。如果没有命中的数据则直接放弃，如果有会根据记录上是否有删除标记来决定是不是需要回到聚集索引去查询。

InnoDB Architecture

有几个可以注意到的点：

1. 系统表空间中的undo log应该是回滚段的地址。（有待确定）
2. 有O_DIRECTR为什么还有fsync

stackoverflow.com/questions/4…

InnoDB In-Memory 结构

Buffer Pool

buffer pool是在主存中的一块空间。当数据或索引被访问时会被存储在这里。可以借此来保障频繁读取到的数据会命中缓存，可以非常明显提升处理速度。在专用的服务器中，有80%的物理内存是经常被分配给buffer pool的。为了更高效地读操作，buffer pool被分割成为pages，pages内可以包含多行。为了更有效的缓存管理，buffer pool被实现成为pages的链表，根据链表中的年龄会去淘汰不常使用的数据。淘汰的算法使用的是一种LRU算法的变种。

Buffer Pool LRU 算法

当需要增加一个新页时，最不常用的页会被剔除，一个新的页会被插入到列表的中间。

• 头部是young pages
• 尾部时old pages

这个算法保持经常访问的页在new list内即young空间。old sublist内保存的事不常用的页，这些页是淘汰时候的候选集。

在默认情况下，算法执行的过程为：

• 3/8的空间时分配给old sublist
• 队列的midpoint是新队列的尾和旧队列的头的交汇处
• 当innodb读取一个页到buffer pool时，他实际上是插入到了中间点。一个页可以被读到是因为执行了某种初始化操作，如用户执行了SQL，或者Innodb执行了read-ahead操作。
• 访问一个在old sublist中的页会让这个页插入到new sublist的头部。如果这个页是因为用户的初始化操作进入到buffer pool，那么首次访问就会立刻将他转移到young sublist。如果这个页是因为read-ahead操作。可能不会立刻有访问，甚至直到页面被剔除都不会有。
• 随着数据库的访问动作，在old 队列中的页就会被淘汰。

默认情况下，页被立即移动到new sublist意味着他可以在buffer pool中存活更久。一次因为mysqldump而执行的table scan或者一个没有where条件的SELECT语句都会导致有大量的数据涌入而挤走大量的数据。甚至这些新数据在之后并不会被用到。相似地，对于在后台运行的read-ahead线程提前读到的页可能只被读一次。这些情况会导致经常使用的页面被移动到old sublist中并最终被淘汰。这些可以用过对buffer pool 配置，以及对预读的配置来实现。

可以通过show engine innodb status来查看buffer pool的情况。

Buffer Pool LRU 配置

这里只介绍可配置的内容有哪些，具体的配置方法，官网有很详细的描述

• buffer pool设置的越大，innodb越像一个内存数据库，只从硬盘中读取一次数据，在之后的读取中就都可以使用。

• 在64位的系统中，如果有足够的内存，你可以将buffer pool拆分成为多个实例。这样可以减少在高并发场景下的竞争。

• 通过配置抵抗scan，在遇到经常需要刷入大量数据的情况，你也可以保存经常访问的数据在buffer pool中。

• 你可以控制怎样或者什么时候去提前获取页。

• 你可以控制什么时候flushing发生，还有控制flushing的频率是不是随着系统的负载调整。

• 你可以配置innodb保存当前的状态。在服务重启的时候可以避免比较长时间warm up。

  监控与指标解释

Change Buffer

当页没有在buffer pool中时，change buffer用于缓存对二级索引的变更。INSERT，UPDATE和DELETE操作带来的变更将在之后这些页面被其他读操作装载到buffer pool中时合并。

不像是聚集索引，二级索引通常是不唯一的，对于插入操作来说也通常是相当随机的。对于删除和更新来说也可能操作的不是索引树的相邻位置。在之后借助于其他的读操作将页面加载到缓存中，再合并change buffer上的变更可以解决随机I/O的问题。

在系统空闲或者在shutdwon之前将更新的索引页写入磁盘。purge operation可以将连续的索引页写入磁盘，这要比单值写要高效很多。

当许多影响的行和很多二级索引被更新时，change buffer合并可能会持续几个小时。在这期间，磁盘的I/O会上涨并会直接导致因为磁盘导致的性能下降。change buffer merging也可以在提交事物时持续进行，甚至可以在系统关闭和重启时发生。

在内存空间中，change buffer占据一部分的buffer pool。在次盘上，change buffer是作为系统表空间的一部分。

当索引中存在倒序的列，change buffer是不能生效的。

Configuring Change Buffer

因为change buffer能够减少随机磁盘I/O，因此对于I/O密集型的场景是比较有效的。比如对于有大量DML语句的场景如批量插入的场景。但是它毕竟还是占据了一部分的buffer pool，减少了能够缓存的page的数量，因此如果buffer pool可以很好适配数据集，或者只有很少一部分的二级索引，则可以关闭这个change buffer。

后边关于监控和更详细的配置 暂略

Adaptive Hash Index

对于经常访问的数据可以使用不定长度的前缀来构建哈希索引。如果innodb发现可以通过hash索引来提高查询效率，它会自动构建。当然，也并不是所有类型的workload都适用，比如对于高并发的join，和like操作符。可能会因为频繁竞争导致性能下降。

在mysql 5.7中，自适应哈希是分区的。每个索引被绑定在特定分区上。每个分区被独立的latch保护。在之前的版本是一个latch，会导致比较多的竞争。目前这个值默认为8个，最多可以设置为512个。

Log Buffer

log buffer大小默认为16MB。存储的是要写到日志文件中的数据。这部分数据会周期性刷到磁盘中。更大的log buffer可以支持大事物，不需要在事物提交前就刷redo log到磁盘中。因此如果你有事物需要更新，插入和删除多行数据时可以调大log buffer来减少磁盘I/O。

InnoDB On-Disk 结构

Tables

Creating InnoDB Tables

只需要在建表时候，使用engine = innodb即可。

.frm文件

mysql存储数据的目录信息在.frm文件中。不像是其他的存储引擎。innodb还在自己的系统表空间中存储了对应的数据目录。当删除表或者数据库时，会删除掉这个文件。不能简单通过移动.frm文件来移动数据表。

想要移动的话，还是需要对idb文件做一些操作，如删除掉跟当前系统表空间的联系，再链接上新的表空间。dev.mysql.com/doc/refman/…

 Creating Tables Externally

可以将数据表挂在外部的目录上。可以使用NFS。暂略。