MySQL存储引擎——MyISAM与InnoDB区别

MyISAM与InnoDB简述

MyISAM与InnoDB是很多人在使用MySQL时最常用的两种表类型,两种类型各有优势,要视情况而定。

具体差别是:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型表强调性能,其执行速度比InnoDB快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

具体实现与细节差别

  1. InnoDB不支持FULLTEXT类型的索引;
  2. InnoDB中不保存表的具体行数,也就是说当执行select count(*) from table时,InnoDB会扫描一遍整张表来计算有多少行,但是MyISAM只需要简单地读出保存好的行数即可。需要注意的是count(*)语句中包含where条件时两种表的操作是一致的。
  3. 对于AUTO_INCREMENT类型的字段,InnoDB中必须包含只有该字段的索引,而在MyISAM中可以和其他字段一起建立联合索引。
  4. DELETE FROM table时,InnoDB不会重新建立表,而是一行行删除;
  5. LOAD TABLE FROM MASTER操作对于InnoDB是不起作用的,解决方法是首先把InnoDB转换成MyISAM表,导入数据后再转成InnoDB表,但是对于额外的InnoDB特性(如外键)的表是不适用的。
  6. InnoDB表的行锁也不是绝对的,假如在执行一个SQL语句时MySQL不能确定要扫描的范围,此时InnoDB依旧会锁全表,例如update table set num=1 where name like '%aaa%'。

两种类型最主要的差别是InnoDB支持事务处理与外键和行级锁

其实从MySQL的用户角度来讲,二者如何选择主要是看个人需求了,如果数据库平台达到需求:99.9%的稳定性,方便的扩展性和高可用性来说,MyISAM绝对是首选。 原因如下:

  1. 平台上大多承载的是读多写少的项目,而MyISAM的读性能比InnoDB要强不少;
  2. MyISAM的索引和数据是分开的,并且索引是有压缩的,这样内存使用率就提高了不少。能加载更多索引,而InnoDB是索引和数据紧密捆绑的,没有使用压缩从而会造成InnoDB比MyISAM的体积庞大不少。
  3. 经常会出现开发人员不小心update一个表的where语句写的范围不对,导致整张表都不能正常使用,这是MyISAM的优越性就体现出来了,随便从当天拷贝的压缩包中取出对应表的文件,随便放到一个数据库目录下,然后dump成SQL文件导回主库,并把对应的binlog补上。如果是InnoDB就没有这么快的速度了。通常情况下一个数据库实例最小也有几个G大小。
  4. 从接触的应用逻辑来讲,select count(*)和order by是最频繁的,大概占到整个SQL语句的60%以上的操作,而这种操作InnoDB其实也是会锁表的,很多人以为InnoDB是行级锁,那个只是where对它主键有效,非主键的都是会锁表的。
  5. 如果是定期导某些表的数据,用MyISAM的话会很方便,只需要发给他们对应表的frm、MYI、MYD文件,然后在对应版本的数据库中启动即可,而InnoDB则需要导出xxx.sql文件来执行。(说明:frm文件保存的是表的定义,MYD文件是表的内容,MYI是表的索引数据信息)
  6. 如果和MyISAM比Insert操作的话,InnoDB还达不到MyISAM的写性能,如果是基于索引的update操作,虽然MyISAM会逊色与InnoDB,但是那么多高并发的写,从库能否追的上也是一个大问题。通常情况下会实现多实例分库分表架构来解决。
  7. 使用事务的项目一般就用InnoDB。大项目总量约几个亿的rows的某一类型(如日志等)业务表会使用MyISAM。
发布于 2019-04-04

文章被以下专栏收录