MySQL:InnoDB存储引擎和MyISAM的区别？

发表于 更新于 分类于 阅读次数： 评论数： 本文字数： 1.8k

本文从事务，外键，索引， 存储文件，日志，count行数，锁，主键八个方面系统地对比了在MySQL中InnoDB存储引擎和MyISAM存储引擎的区别。

1. 事务

  • InnoDB：支持事务。

  • MyISAM：不支持事务，强调的是性能，每次查询具有原子性，一般情况下其查询数度比InnoDB更快，但是不提供事务支持。

一般情况下MyISAM查询速度比InnoDB快的原因：

答：在查询的时候，InnoDB要维护的东西比MyISAM多很多，具体以下几点：

  • InnoDB缓存数据块，MyISAM只缓存索引块， 这中间还有换进换出的减少；
  • InnoDB寻址要映射到块，再到行，MyISAM记录的直接是文件的OFFSET，定位比InnoDB要快
  • InnoDB还需要维护MVCC一致； 虽然你的场景没有，但他还是需要去检查和维护

2. 外键

  • InnoDB：支持外键
  • MyISAM：不支持外键，对一个包含外键的InnoDB表转为MyISAM会失败

3. 索引

  • InnoDB：聚集索引，数据文件和索引存储在一起，必须要有主键，通过主键查询效率很高，一次查询即可得到完整的行数据。辅助索引存储的数据是对应的主键索引的值，通过辅助索引查询需要两次查询，根据辅助索引查询到对应的主键索引，再根据主键索引查询到对应的行数据（这一过程称之为回表）。
  • MyISAM：非聚集索引，索引和数据文件分开保存，索引保存的是数据文件的指针。主键索引和辅助索引相互独立。

InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

4. 存储文件

  • InnoDB：存储文件有frm、ibd，frm是表定义文件，ibd是数据文件。
  • MyISAM：存储文件有frm、myd、myi，frm是表定义文件，myd是数据文件，myi是索引文件。

5. 日志

  • InnoDB：有redo log，使用 InnoDB 的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态，这个恢复的过程依赖于 redo log 。 redo log 保证事务的持久性， undo log 保证事务的原子性。
  • MyISAM：没有redo log，不支持数据库异常崩溃后的安全恢复。

6. count

  • InnoDB：不保存表的具体行数，如果使用select count(*) from table，就会遍历整个表，消耗相当大，但是在加了where条件后，myisam和innodb处理的方式都一样。
  • MyISAM：使用一个变量保存整个表的行数，如果使用select count(*) from table，会直接读取该变量。

因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。

7. 锁

  • InnoDB：支持行级锁和表锁。行锁是基于索引实现的，如果没有命中索引，也无法使用行锁，会退化到表锁。
  • MyISAM：仅支持表锁，不支持行锁。

8. 主键

  • InnoDB：如果没有设定主键，会自动生成一个6字节的隐藏主键（用户不可见），数据保存在主键索引的叶子节点上。
  • MyISAM：允许不含有主键。

删除部分自增主键后，二者的区别？

  1. 不重启服务器

初始有自增主键1,2,3,4,5，删除4,5后，插入一行新的数据，无论是InnoDB还是MyISAM都会从6开始插入。

  1. 重启服务器

初始有自增主键1,2,3,4,5，删除4,5后，重启服务器，再插入一行新的数据，InnoDB主键会从4开始插入，而MyISAM会从6开始插入。

原因：Innodb表把自增主键的最大ID记录到内存中，重启数据库后，都会导致最大自增ID重置；MyISAM表会把自增主键的最大ID记录到数据文件里，重启MySQL自增主键的最大ID也不会丢失。

说明：mysql8.0版本，重启数据库后，不会导致最大自增ID重置

原因：在 MySQL 8.0 中，AUTO_INCREMENT计数器的逻辑变了，每当计数器的值有变，InnoDB 会将其写入 redo log，保存到引擎专用的系统表中。MySQL 正常关闭后重启：从系统表中获取计数器的值。MySQL 故障后重启：从系统表中获取计数器的值；从最后一个检查点开始扫描 redo log 中记录的计数器值；取这两者的最大值作为新值。

相关文章