MySQL InnoDB简介、特性和优势

InnoDB是MySQL默认的存储引擎,支持事务,具有高性能和高可靠性。

一、InnoDB核心优势

  • 支持事务,DML操作遵循ACID模型,具备崩溃恢复能力,保证用户数据安全、完整。
  • 支持行级锁和一致性读,提高了多用户并发性能。
  • 表数据在磁盘上以主键聚簇索引方式存储,这种数据组织方式,在以主键为条件进行查询时,能够最大化地降低IO消耗,提高查询性能。
  • 支持外键约束,保证数据完整性。
二、InnoDB特性
  • 支持事务
  • B-tree索引
  • 聚簇索引
  • 基于时间点的备份、恢复
  • 数据压缩
  • 数据缓存
  • 数据加密
  • 外键支持
  • 全文索引
  • 地理空间类型
  • 地理空间索引
  • 索引缓存
  • 行级锁
  • mvcc
  • 主备复制

三、使用InnoDB表的好处

  1. 不论什么时候、什么原因导致的MySQL进程异常退出,在重启之后,InnoDB引擎都能够做崩溃恢复,重新提交事务或者回滚未提交的事务,保证数据一致性。
  2. InnoDB存储引擎内部使用buffer pool来缓存表和索引数据,大部分数据处理直接在内存中完成,不需要额外的磁盘IO操作,通常一台服务器80%的物理内存分配给buffer pool使用,以提高InnoDB查询性能。
  3. InnoDB支持外键,使用外键约束来保证数据完整性,分布在两个表中的数据,通过外键,其关系将自动得到维护。
  4. 如果磁盘或者内存中的数据损坏,InnoDB提供checksum机制来提示用户。
  5. InnoDB数据组织方式使得基于主键的查询、排序,join,group by等操作性能非常高效。
  6. 插入,更新,删除操作通过change buffer机制优化,不仅允许对同一张表并发读写,并且能够缓存已修改的数据,让随机IO变为顺序IO,以提高性能。
  7. InnoDB表的性能不会因为表太大或者大事务而受到影响。当一个表中某些数据被频繁地访问时,InnoDB提供自适应哈希索引来加快这些频繁访问数据的查找,就好像这些数据是从哈希表中读出来的一样。
  8. 能够对表及其索引进行压缩,以减少磁盘空间占用。
  9. 在线增加、删除索引,不会影响数据库的性能和可用性。
  10. 为表配置单独表空间,清空表的操作会非常快,能够立即释放磁盘空间。
  11. 使用dynamic行格式能够更有效的存储blog和long text类型的数据。
  12. 通过information_schema里面的表,能够监控存储引擎内部工作信息。
  13. 通过performance_schema里面的表,能够监控存储引擎性能方面的细节。
  14. 能够自由地混合使用InnoDB引擎和其他存储引擎,甚至在一个SQL语句里,可以使用多个存储引擎的表。

四、InnoDB表的最佳实践

  1. 为InnoDB表指定一个主键,主键建议是在where条件中最频繁查询的字段,或者指定一个自增的列作为主键。
  2. 将DML相关联的一组操作放到事务里,能够避免事务提交太频繁,另外也要注意,不能在一个事务里做太多的DML操作,以免产生大事务。
  3. 不要使用lock tables语句,InnoDB允许多个事务并发读写同一张表,并且不会损失性能和可靠性。如果想排它地访问某些数据,可以使用select for update语法,对想要更新的行加锁。
  4. 打开innodb_file_per_table选项,每个业务表有自己单独的表空间。
  5. 尝试使用InnoDB表压缩,节省磁盘空间,且不会影响读写性能。
  6. 使用join做多表关联查询时,基于同一个ID值来获取数据。在关联字段上添加外键,确保查询时有索引可以使用。

注:
本文译自MySQL 5.7 官方文档 InnoDB存储引擎一章。

发表评论