InnoDB主要特性、概念和架构

Innodb的主要特性

• DML操作遵循ACID模型，事务支持commit，rollback和crash recovery，从而保护用户数据
• 基于行锁以及类似oracle的一致性读，提升了多用户的并发和性能
• 基于主键对查询进行优化
• 为了维护数据的完整性，InnoDB也支持外键。如果使用了外键，insert，update以及delete操作都会得到检查，以确保不会导致多表之间的数据不一致。
• 可以将InnoDB的表和Mysql其它存储引擎的表混合使用，甚至在同一个sql语句当中。比如：可以使用join操作将InnoDB表和MEMORY中的表进行合并。
• InnoDB被设计成，在处理存在大量数据列的时候，可以更好地利用CPU，并使性能最高
  • 最大存储大小：64T
  • MVCC：支持（MVCC多版本并发控制介绍： http://www.cnblogs.com/gaojian/p/3295951.html ）
  • 事务：支持
  • 锁粒度：行
  • 地理空间数据类型：支持
  • 地理空间索引：支持
  • B-tree索引：支持
  • T-tree索引：不支持
  • Hash索引：不支持
  • Full Text Search索引：支持
  • 聚合索引：支持
  • 数据缓存：支持
  • 索引缓存：支持
  • 数据压缩：支持
  • 数据加密：支持
  • 集群数据库：不支持
  • 复制数据库：支持
  • 外键：支持
  • 备份/实时恢复：支持
  • 查询缓存：支持
  • 数据字段的更新统计：支持
• InnoDB存储引擎维护了它自己的buffer pool，用来在内存中缓存数据和索引。默认innodb_file_per_table是打开的，那么每个新的InnoDB表和索引都存储在独立的文件中

默认的Mysql存储引擎

• InnoDB支持crash recovery，以便在数据库出现故障的时候，可以直接进行重启即可，数据不会丢失。在恢复的时候，可以提交已经commit的请求，回滚任何还未commit的请求。
• InnoDB的buffer pool缓存了表和索引数据，经常使用的数据直接在内存中进行处理。这个缓存用于多个信息类型，并加快处理速度。
• 如果我们把相关数据拆分在多个表中，可以使用外键来保证参考完整性。

使用Innodb表的好处

• Crash Recovery：从灾难中恢复功能。
• InnoDB的buffer pool 缓存了访问过的表和索引数据。经常使用的数据直接从内存中进行处理。这个缓存用于许多类型的信息，以便加快处理速度。
• 外键的支持：将关联数据拆分到多张表中的时候，可以使用外键来确保参照完整性。Update或Delete数据，在其它表中的关联数据也会自动被更新或删除。如果尝试在secondary表中插入在primary表中没有相应数据的记录，这些记录自动会被剔除。
• 如果数据在内存或磁盘中被破坏了，一个checksum的机制可以检测到这一点并在你使用数据之前进行告警。
• 如果我们为每张表设计了合适的primary key，那么哪些关联了这些primary key的字段会自动被优化。在WHRER、ORDER BY，GROUP BY，JOIN子句中使用主键，能大大加快操作速度。
• 对于insert，update和delete操作，Innodb自动使用chang buffering机制进行优化。InnoDB不仅允许并发读写来访问同一张表，它还缓存磁盘I/O改变的数据。
• 性能的优化不仅仅局限于运行时间长的大表查询，对于从一张表中相同的一些记录经常被访问，一个称为Adaptive Hash Index机制会被使用，从而使得这些查询更快，因为它们成为了hash表了。
• 可以对表和相关联的索引进行压缩
• 可以创建和删除索引，而不会对性能和可用性带来很大的影响。
• 清空file_per_table表非常快，而且能够释放磁盘空间，以便操作系统进行复用，而不只是释放system tablespace的空间，导致只有InnoDB能够复用它。
• InnoDB对于BLOB和long text字段的布局更加高效，因为它采用了DYNAMIC行格式。
• 可以通过查看 INFORMATION_SCHEMA中的表来查看InnoDB内部的工作情况。
• 可以通过查询 Performance Schema表来监控InnoDB的性能细节。
• InnoDB可以使用FULLTEXT的索引类型来执行全文（full-text）搜索
• InnoDB对于只读或大部分为读的任务具备更好的性能。在autocommit模式下，自动对InnoDB查询进行优化；而且我们可以显示设置事务为只读的，START TRANSACTION READ ONLY；以便提升性能。

Innodb表的最佳实践

• 使用最经常查询的字段作为表的主键，如果没有显示的主键，应该设置一个auto-increment 字段。
• 如果需要从多张表中获取数据，多张表之间使用某个ID进行关联，这个时候需要使用join。为了提升join性能，可以在join列上面建立外键，并且这些字段的类型在这些表中都是一样的。增加外键，可以确保参照字段都被索引了，这样可以提升性能。外键可以对delete或update传递所有受影响的表上，并且在父表没有对应ID的情况下，可以阻止在子表插入数据。
• 关闭autocommit。一秒钟commit几百次会严重影响性能。

• 把相关的DML操作打包到一个事务当中（用START TRANSACTION和 COMMIT包含他们） 。你不希望commit太频繁，但是你也不希望执行大量的INSERT，UPDATE或DELETE语句，经过了几个小时都不发生commit；
• 不使用LOCK TABLES语句；InnoDB可以处理对同一张表同时进行读和写的多个会话，而不会影响稳定性和性能。为了获取多行记录的排它写，可以使用SELECT ... FOR UPDATE语句，它只会锁住表中需要更新的记录。
• 开启innodb_file_per_table选项，这样不同表的数据和索引，存放在不同的文件里面。
• 根据表中的数据和访问方式，评估下对表数据进行压缩是否有好处（使用 CREATE TABLE 语句，指定ROW_FORMAT=COMPRESSED）。
• 使用--sql_mode=NO_ENGINE_SUBSTITUTION选项来启动server，从而防止在 CREATE TABLE中 ENGINE=子句指定了其它的存储引擎。
•  SHOW ENGINES命令，可以查看InnoDB是否为默认的存储引擎；当然也可以查询表INFORMATION_SCHEMA ENGINES。

InnoDB概念和架构

Mysql和ACID模型

• MySQL中的InnoDB存储引擎实现了ACID模型。
• A：atomicity，一般和InnoDB的事务相关。和MySQL相关的特性包括：1）Autocommit设置 2）COMIT语句 3）ROLLBACK语句 4）来自于INFORMATION_SCHEMA的操作性数据
• C：consistency，一般和InnoDB保护数据不受崩溃破坏的内部处理相关。和MySQL相关的特性包括：1）InnoDB的双写缓存（doubewrite buffer） 2）InnoDB的崩溃恢复（Crash recovery）。
• I：Isolation，也一般和InnoDB的事务相关，特别是用在每个事务上的隔离级别。和MySQL相关的特性包括：1）Autocommit设置 2）SET ISOLATION LEVEL 语句 3）InnoDB locking的底层细节。在性能调优时，我们可以通过INFORMATION_SCHEMA中的表看到很多细节。
• D：Durability,一般和硬件配置交互的MySQL软件特性相关。由于CPU，网络，存储设备的性能各异，这个部分要提供具体的指导建议是最困难的。和MySQL相关的特性包括：1）InnoDB的双写缓存，使用 innodb_doublewrite配置选项来关闭和打开。 2） innodb_flush_log_at_trx_commit配置选项 3） sync_binlog选项 4）innodb_file_per_table选项 5）存储设备（磁盘，磁阵，SSD）的写缓存 6）带蓄电池后备电源的存储缓存 7）运行MySQL的操作系统，特别是对fsync()函数的支持 8）UPS的使用 9）备份的策略，比如：备份的频率、类型以及备份的保留期

InnoDB的MVCC

• InnoDB是一个多版本存储引擎：它对更新行的历史记录进行保留，以支持事务特性，比如：并发访问和回滚。这些历史记录保留在rollback segment当中，InnoDB使用这些记录来执行回滚操作，也使用它们为一致性读提供数据的早期版本。
• 在内部，InnoDB为每一行记录增加了3个字段。
  • 一个6字节的DB_TRX_ID字段，记录最近对本行进行insert或update的事务标识。对于delete在内部被作为一种特殊的update操作来对待，delete时，在行记录中会设置一个的特殊的bit位来表示行被删除。
  • 一个7字节的 DB_ROLL_PTR字段，也叫做roll pointer。 它指向rollback segment中的一条undo日志记录。如果行被更新，undo日志记录 需要在行被更新之前，重新生成内容。
  • 一个6字节的DB_ROW_ID字段，当有行插入的时候，它会自动地单调递增。如果InnoDB自动产生了聚合索引，索引会包含ROW ID。否则，DB_ROW_ID不会出现在任何索引上面。
• 在rollback segment中的undo日志被分为insert和update两部分。insert部分只有在rollback时有用，可以在事务提交后进行删除。update部分除了用在rollback上，还用于一致性读上面；它们只有在没有事务需要使用它来构造一致性读所需要的记录早期版本的时候，才会被删除。
• 我们要经常commit事务（即使事务只发起了一致性）。否则，InnoDB无法释放Undo Log里面的数据，rollback日志可能会变得很大。
• 在rollback segment中Undo log记录一般比insert或update的行记录要小。我们可以使用这个信息来估算我们需要多大的rollback segment。
• 在InnoDB的多版本方案中，当你使用delete删除一行记录，MySQL不会立即进行物理删除。InnoDB只有在对应行的Undo 日志失效的时候才会执行真正的物理删除操作。这个操作称之为purge（净化）。这个过程非常快，通常在执行delete语句之后就执行完了。如果某张表以同样的速率进行着小批量的插入和删除操作，purge线程有可能跟不上记录的删除，表最终会因为很多dead记录，变得越来越大，导致很多操作都和磁盘相关，变得越来越慢。在这种情况下，我们可以通过调节innodb_max_purge_lag系统变量来进行调优。这个参数表示有多少 delete或update操作被标记为deleted的行没有被purge，如果超过innodb_max_purge_lag这个值，则delete