A look at InnoDB for Mysql 5.7

InnoDB的优势

• DML 遵循ACID
• 行级锁，使用MVCC保证读一致
• 使用主键索引管理磁盘上的数据，加快查找速度
• 使用外键维持数据的完整性

InnoDB表 最佳实践

• 主键的设定：把查询用的最多的column， 并且不怎么改动的设成主键；如果没有可以设置自增列为主键。
• 如果是基于一个列进行多表联查，使用join. 并且把join的列设置成外键，数据类型也要一致。这么做是因为外键会给列加索引，外键也会把删除和更新传到相关表。
• 关闭autocommit(可能是log flush的影响，待查)
• 把一系列的DML语句放在一个事务中。
• 不要使用lock tables语句。可以使用select .. for update 只针对某些row锁定更新。
• 开启innodb_file_per_table， 这样表的数据和索引是单独文件存在。
• 根据需求，可以使用ROW_FORMAT=COMPRESSED来压缩数据。

InnoDB 和 ACID 模型

• 使用innodb, mysql将十分接近ACID模型，避免软硬件的失效带来的灾难。
• ACID就不说了

MVCC

• innodb使用rollback segment的数据结构来管理版本信息。主要服务回退和一致性读。
• innodb 会在每行后面加三个隐藏字段
  • 6B DB_TRX_ID 最新事务ID. 这个字段里面有个位标记行是否被删除
  • 7B DB_ROLL_PTR roll pointer 指向rollback segment的undo log记录。 更新一条记录时，undo log 会记录如何回滚这个更新的记录。
  • 6B DB_ROW_ID 行ID, 递增。如果聚簇索引是innodb自动生成的，那么索引里包含row id. 否则DB_ROW_ID不会再任何索引里出现。
• 在rollback segment中的undo log 分为insert和update undo logs. insert undo log 在事务回滚时就不需要了，但是update undo log需要在MVVC保持consistent read后，才能丢弃。
• 在innodb, delete sql不会立即删除物理数据；仅在update undo log被purge的时候才会删除行数据和对应的索引
• purge是一个定时垃圾处理undo log的后台任务，可以有一个或多个线程。purge是滞后操作的，所以批量删除数据太多，可能引起rollback segment过大，这时可以增加线程innodb_max_purge_lag
• 多版本和二级索引