MyISAM和InnoDB区别

  • MySQL5.5版本之前，MyISAM是MySQL的默认存储引擎，而从MySQL5.5版本开始，InnoDB成为了默认存储引擎。
  • MyISAM和InnoDB之间的区别主要在以下几个方面：
    • 是否支持行级锁
    • 是否支持事务
    • 是否支持外键
    • 是否支持数据库异常崩溃后的安全恢复
    • 是否支持MVCC（多版本并发控制）
    • 索引实现不一样
    • 性能差别
    • 数据缓存策略和实现机制不同

是否支持行级锁

  • MyISAM只支持表级锁（table-level locking），而InnoDB支持行级锁（row-level locking）。
  • 这意味着在MyISAM中，当一个线程对表进行写操作时，整个表都会被锁定，其他线程无法进行读写操作。而InnoDB允许多个线程同时对同一表的不同记录进行读写操作，提高了并发性能。

是否支持事务

  • MyISAM不支持事务，而InnoDB支持事务。
  • InnoDB提供事务支持，实现了SQL标准定义了四个隔离级别（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE），具有提交（commit）和回滚（rollback）事务的能力。且InnoDB默认使用的REPEATABLE READ可以解决幻读问题。

是否支持外键

  • MyISAM不支持外键，而InnoDB支持外键。
  • InnoDB允许在表之间定义外键约束，确保数据的完整性和一致性。但现在不推荐在实际项目中使用外键约束，因为它会增加数据库的复杂性和维护成本，且在分布式数据库环境中可能会导致性能问题。

是否支持数据库异常崩溃后的安全恢复

  • MyISAM不支持数据库异常崩溃后的安全恢复，而InnoDB支持数据库异常崩溃后的安全恢复。
  • InnoDB使用了重做日志（redo log）和回滚日志（undo log）来实现崩溃恢复机制。

是否支持MVCC（多版本并发控制）

  • MyISAM不支持MVCC，而InnoDB支持MVCC。
  • MVCC：数据库不强迫所有事务排队，而是给数据保存多个历史版本。每个事务只看到“属于自己时间线”的版本。

索引实现不一样

  • MyISAM和InnoDB都是使用B+Tree作为索引结构，但是两者的实现方式不太一样。
  • MyISAM用的是非聚簇索引（Non-clustered Index）：
    • 数据存在.MYD文件
    • 索引存在.MYI文件
    • 索引的叶子节点存的是数据文件的物理地址（偏移量）
  • InnoDB用的是聚簇索引（Clustered Index）：
    • InnoDB 的主键索引：
      • 是一棵 B+Tree
      • 叶子节点存的就是完整行数据
    • InnoDB的二级索引：
      • 也是一棵B+Tree
      • 叶子节点存的是主键值
  • 总结：
    • MyISAM：索引告诉你“数据在哪”
    • InnoDB：数据就是主键索引本身

性能差别

  • InnoDB的性能比MyISAM更强大，不管是在读写混合模式下还是只读模式下，随着CPU核数的增加，InnoDB的读写能力呈线性增长。MyISAM因为读写不能并发，它的处理能力跟核数没关系。

数据缓存策略和实现机制不同

  • InnoDB使用缓冲池（Buffer Pool）缓存数据页和索引页，MyISAM使用键缓存（Key Cache）仅缓存索引页而不缓存数据页。

总结

特性 MyISAM InnoDB
锁机制 表级锁 行级锁
事务支持 不支持 支持
外键支持 不支持 支持
崩溃恢复 不支持 支持
MVCC支持 不支持 支持
索引实现 非聚簇索引 聚簇索引
性能 适合读多写少的场景 适合读写混合的场景