侧边栏壁纸
博主头像
Ysfun博主等级

一名热爱技术、喜欢折腾的小小程序猿

  • 累计撰写 42 篇文章
  • 累计创建 14 个标签
  • 累计收到 21 条评论

目 录CONTENT

文章目录

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

Ysfun
2022-06-17 / 0 评论 / 0 点赞 / 43 阅读 / 1,366 字

本文从事务外键索引存储文件日志count行数主键八个方面系统地对比了在MySQLInnoDB存储引擎和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、ibdfrm是表定义文件,ibd是数据文件。
  • MyISAM:存储文件有frm、myd、myifrm是表定义文件,myd是数据文件,myi是索引文件。

5. 日志

  • InnoDB:有redo log,使用 InnoDB 的数据库在异常崩溃后,数据库重新启动的时候会保证数据库恢复到崩溃前的状态,这个恢复的过程依赖于 redo logredo 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 中记录的计数器值;取这两者的最大值作为新值。

0

评论区