MySQL--InnoDB笔记

1.InnoDB存储引擎

• DML (Data manipulation Language)遵循 ACID 模式，并且事务具有commit、rollback和cash-recovery的功能以保护用户的数据.
• 行级锁 (row-level lock)和Oracle风格的 一致性读 (consistent reads)提升了多用户的并发性和性能.
• InnoDB表会根据主键在磁盘上排列数据以优化查询.每一个InnoDB表都有一个称为 聚簇索引 (clustered index)的主键索引来组织数据以减少主键查询时的I/O操作.
• 为了维护数据的完整性，InnoDB支持 外键 (FOREGIN KEY)约束.有了外键，将检查插入、更新和删除操作，以确保它们不会导致不同表之间的不一致.

3.使用InnoDB的好处

这里只挑选了一些个人认为对比较重要的点，更多细节请参考 Benefits of Using InnoDB Tables .

• 故障恢复 (crash recovery).不管你的服务器因为硬件还是软件的问题发生了故障，不管当时数据库正在发生什么，你不需要在重启数据库之后做任何特殊的操作.InnoDB的故障恢复机制会自动的完成故障前已经提交的修改，而故障前未提交的数据将被丢弃.
• 缓存池 (buffer pool):当数据被访问时，InnoDB引擎会在主存中维护自己的缓存池来缓存表以及索引数据.频繁使用的数据将直接从内存中访问.该缓存能适用于多种不同类型的信息，并能提升处理速度.在一些专用的数据库服务器上，通常超过80%的物理内存会分配给缓存池.
• 外键 (foreign keys):当把相关联的数据拆分到不同的表中时，可以使用外键.当修改或者删除数据时，在其他表的相关联的数据将会被自动修改或删除.当尝试向一个二级表中插入数据而没有修正主表中的数据时，脏数据将被自动剔除.
• 当你为你的数据库中的每个表设计了适当的主键列时，包含这些列的操作将自动被优化.当在 WHERE 语句、 ORDER BY 语句、 GROUP BY 语句和 join 操作中引用这些主键列时会变得非常快.
• 插入、修改和删除会被一种称为 change buffering 的自动机制优化.InnoDB不仅允许并发的读写同一张表，它还会对修改进行缓存以提高磁盘的I/O效率.
• 你可以在对性能和可靠性产生很小影响的情况下创建和删除索引.

4.InnoDB表的最佳实践

• 为每一张表指定一个主键.最好使用会被频繁查询的一列或多列，如果没有明显的主键也可以使用一个被标记为 自增(auto-increment) 的列.
• 当基于指定的ID值从多个表中拉取数据时要使用join操作.为提高性能，可以将这些做join操作的列都定义为 外键 ，并在每个表中都将这些列声明为相同的数据类型.定义为外键可以确保这些列会被索引化，这样可以提高性能.
• 关闭 自动提交 (autocommit).一秒钟几百次提交可能影响性能(受限于你的存储设备的写入速度).
• 将相关联的 DML 操作放入 事务 中.
• 不要使用 锁表(LOCK TABLES) 语句.InnoDB可以同时处理多个读写同一张表的会话而不会影响可靠性和高性能.为了独占式的获取一些列的写入权限，可以使用 SELECT ... FOR UPDATE 语法来锁住你想要修改的列.
• 开启 innodb_file_per_table 选项，使一个表的数据和索引放入不同的文件中(也被称为 独立表空间 )，而不是放入 系统表空间(system tablespace) 中.这个设置会要求使用其他的特性，比如表 压缩(compression) (默认开启的).
• 评估你的数据和访问模式是否受益于InnoDB的 表压缩 功能.
• 使用 --sql_mode=NO_ENGINE_SUBSTITUTION 参数运行你的服务器以防止表被不同的存储引擎创建.

2.InnoDB和ACID

ACID 模式是一系列强调对业务数据和 关键任务应用程序 很重要的可靠性方面的数据库设计原则.

• A:atomicity，原子性.
• C:consistency，一致性.
• I:isolation，隔离性.
• D:durability，持久性.

3.InnoDB多版本

1.多版本

InnoDB是一个 多版本存储引擎(multi-versioned storage engine) ：它保存了被修改行的老版本信息来支持像并发性和rollback这样的事务性功能.该信息被存储在表空间中一个叫 回滚段(rollback segment) 的数据结构之中.InnoDB使用这些信息来支持事务回滚中的 undo 操作，也使用这些信息来为 一致性读(consistent read)操作 构建一行数据记录的早期版本.

InnoDB会为数据库中的每一行数据添加3个字段:

DB_TRX_ID：6-byte大小，用来存储上一个插入或者修改当前行的事务id.删除也被当做一个特殊的修改，用一个bit位来标记当前数据行已被删除.

DB_ROLL_PTR：7-byte大小，该指针指向一个回滚段中的undo日志记录.如果当前行被修改，那么undo日志中包含重建修改前数据的所有必要信息.

DB_ROW_ID：6-byte大小，存储一个自增id值.如果InnoDB自动生成 聚簇索引 (用户没有指定主键的情况下)，索引中就会包含该字段的值.

回滚段 中的undo日志分为插入和修改. 插入undo日志 只在 事务回滚 时使用，并且事务一旦提交就可以丢弃. 修改undo日志 不仅用于 事务回滚 ，也会用于 一致性读 ，只有当没有事务需要使用 修改undo日志 来为 一致性读 构建数据记录早期版本的 快照(snapshot) 时才能进行丢弃.因此，要经常提交你的事务，包括那些只与读操作相关的事务.否则，InnoDB不会丢弃 修改undo日志 ，这样就会导致 回滚段 变得越来越大以至于填满你的 表空间(tablespace) .一个 回滚段 中的 undo 日志记录的物理大小一般来说会比被插入或被修改的相应的数据行要小，你可以据此来推算 回滚段 需要的空间大小.

在InnoDB的多版本方案中，当你用 SQL 语句删除一个数据行的时候，它并不会马上从数据库中被物理删除.只有当为删除操作建立的 修改undo日志 被丢弃之后InnoDB才会物理的删除相应的数据行和它的索引.这种移除操作被称为 清除(purge) ，它非常快，通常采用和执行删除操作的SQL语句相同的时间顺序.

如果以恒定的速率在表中小批量的插入和删除数据行， 清除线程(purge thread) 会因为来不及处理清除操作而开始滞后，表就会因为那些已经‘死亡’的数据行而变得越来越大，进而产生disk-bound并且会变得很慢.可以通过调整 innodb_max_purge_lag 来避免上述情况，但是应该根据具体情况进行分析.

2.多版本和二级索引

InnoDB的 MVCC(multi-version concurrency control) 对待 二级索引(secondary index) 和 聚簇索引(clustered index) 不太一样.在 聚簇索引 中的数据是实时更新，并且他们包含隐藏的系统列.二级索引数据不包含隐藏的系统列也不是实时更新的.

当一个 二级索引列 被修改时，老的 二级索引 数据被标记为删除，新的数据会被插入，最终被标记为删除的数据会被 清除 .当一个 二级索引 记录 被标记删除或 二级索引页 被一个新的事务修改时，InnoDB将会在 聚簇索引 中查询数据库记录.在 聚簇索引 中会检查记录的 DB_TRX_ID 字段，以便在读事务开始后如果记录被其他事务修改的情况下返回正确的 undo 日志.

如果一个 二级索引记录 被标记为删除或者 二级索引页 被一个新的事务修改， 覆盖索引(covering index) 技术将不会被使用.InnoDB会在 聚簇索引 中查找记录，而不是直接从索引结构中返回数据.

如果启用了 index condition pushdown(ICP) 优化，并且可以仅使用索引中的字段来评估部分Where条件，则mysql服务器仍会将这部分Where条件下推到存储引擎中，并使用索引对其进行评估.如果找不到匹配的记录，那么就避免了去 聚簇索引 中查找.如果找到了匹配的记录，即使是被标记为删除的记录，InnoDB都会到 聚簇索引 中查找记录.

TO BE CONTINUED...