谈谈数据库中 MyISAM 与 InnoDB 区别

MyISAM 存储引擎

MyISAM 是 MySQL 中传统的存储引擎类型，基于 ISAM（Indexed Sequential Access Method，有索引的顺序访问方法）架构发展而来。ISAM 是存储记录和文件的标准方法。与其他存储引擎相比，MyISAM 拥有大多数检查和修复表格的工具。MyISAM 表格可以被压缩，且支持全文搜索。

需要注意的是，MyISAM 不是事务安全的，也不支持外键。如果事务回滚，将造成不完全回滚，不具有原子性。因此，如果业务执行大量的 SELECT 查询，MyISAM 是更好的选择。

MyISAM 是 ISAM 表的新版本，具备如下扩展特性：

  • 二进制层次的可移植性。
  • 支持 NULL 列索引。
  • 对变长行比 ISAM 表有更少的碎片。
  • 支持大文件。
  • 更好的索引压缩。
  • 更好的键值统计分布。
  • 更好和更快的 AUTO_INCREMENT 处理。

InnoDB 存储引擎

InnoDB 是事务安全的存储引擎类型。它与 BDB 类型具有相同的特性，并且支持外键。InnoDB 表格速度很快，具有比 BDB 更丰富的特性，因此如果需要一个事务安全的存储引擎，建议使用它。

如果你的数据执行大量的 INSERT 或 UPDATE 操作，出于性能方面的考虑，应该使用 InnoDB 表。对于支持事务的 InnoDB 类型表，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的。如果程序没有显式调用 BEGIN 开始事务，会导致每插入一条数据就自动 Commit，严重影响速度。可以在执行 SQL 前调用 BEGIN，将多条 SQL 形成一个事务（即使 AUTOCOMMIT 打开也可以），这将大大提高性能。

MyISAM 与 InnoDB 对比

具体区别可以参考下表：

特性MyISAMInnoDB
构成上区别每个 MyISAM 表在磁盘上存储成三个文件，文件名为表名，扩展名代表文件类型：
1. .frm 文件存储表定义；
2. .MYD (MYData.html) 数据文件的扩展名；
3. .MYI (MYIndex.html) 索引文件的扩展名。		基于磁盘的资源是 InnoDB 表空间数据文件和它的日志文件。InnoDB 表的大小只受限于操作系统文件的大小（旧版本一般为 2GB）。
事务处理方面强调的是性能，其执行速度比 InnoDB 类型更快，但是不提供事务支持。提供事务支持，支持外键等高级数据库功能。
锁机制表级锁。行级锁。
注意：InnoDB 表的行锁也不是绝对的。如果在执行一个 SQL 语句时 MySQL 不能确定要扫描的范围，InnoDB 表同样会锁全表。例如：update table set num=1 where name like "%aaa%"。
操作性能如果执行大量的 SELECT，MyISAM 是更好的选择。1. 如果数据执行大量的 INSERT 或 UPDATE，出于性能考虑，应该使用 InnoDB 表。
2. DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行地删除。
3. LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的。解决方法是首先把 InnoDB 表改成 MyISAM 表，导入数据后再改成 InnoDB 表，但对于使用了额外 InnoDB 特性（例如外键）的表不适用。
AUTO_INCREMENT 字段可以和其他字段一起建立联合索引。必须包含只有该字段的索引。
全文索引支持。不支持 FULLTEXT 类型的索引。
可移植性二进制数据文件可以在不同操作系统中迁移。-

细节与具体实现的差别

以下是一些细节和具体实现的差别补充：

  1. 全文索引：InnoDB 不支持 FULLTEXT 类型的索引。
  2. 行数统计：InnoDB 中不保存表的具体行数。也就是说，执行 select count(*.html) from table 时，InnoDB 要扫描一遍整个表来计算有多少行；但是 MyISAM 只要简单地读出保存好的行数即可。注意的是，当 count(*.html) 语句包含 where 条件时，两种表的操作是一样的。
  3. 自增字段索引：对于 AUTO_INCREMENT 类型的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中，可以和其他字段一起建立联合索引。
  4. 删除操作：DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行地删除。
  5. 数据导入：LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的。解决方法是首先把 InnoDB 表改成 MyISAM 表，导入数据后再改成 InnoDB 表，但对于使用了额外的 InnoDB 特性（例如外键）的表不适用。

总结

综上所述，任何一种表都不是万能的。只有针对业务类型来恰当选择合适的表类型，才能最大程度地发挥 MySQL 的性能优势。

说明：本文部分内容基于较早期的 MySQL 版本（如 5.1 及之前）。

  1. 默认引擎：MySQL 5.5 版本之后，默认存储引擎已改为 InnoDB。
  2. 全文索引：MySQL 5.6 版本之后，InnoDB 也开始支持 FULLTEXT 全文索引。
  3. 文件大小：现代版本中 InnoDB 表空间大小限制已远超 2GB。
  4. 命令支持：LOAD TABLE FROM MASTER 在新版本中已废弃或移除。
    请根据实际使用的数据库版本参考上述特性。