[mysql必备面试题]-InnoDB和MyISAM引擎的区别

InnoDB

是 MySQL 默认的事务型存储引擎，只有在需要它不支持的特性时，才考虑使用其它存储引擎。

实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。

主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。

内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。

MyISAM

设计简单，数据以紧密格式存储。对于只读数据，或者表比较小、可以容忍修复操作，则依然可以使用它。

提供了大量的特性，包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作，但是和事务恢复以及崩溃恢复不同，可能导致一些数据丢失，而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。

比较

InnoDB和MyISAM是MySQL中最常用的两种存储引擎，它们在功能和性能上有一些显著的区别

1. 事务支持：

   • InnoDB支持事务，具有提交（commit）和回滚（rollback）的能力，可以保证一组操作要么全部成功，要么全部失败。
   • MyISAM不支持事务。

2. 外键约束：

   • InnoDB支持外键约束，可以保证数据的完整性和一致性。
   • MyISAM不支持外键约束。

3. 锁的粒度：

   • InnoDB支持行级锁，可以对某一行的记录进行加锁，从而允许其他用户对同一表中的其他记录进行并发操作。
   • MyISAM支持表级锁，当一个用户对表进行写操作时，其他用户不能对该表进行任何操作。

4. 崩溃恢复：

   • InnoDB采用MVCC（多版本并发控制），具有更好的崩溃恢复能力。
   • MyISAM在崩溃恢复方面不如InnoDB，因为它不使用事务日志。

5. 索引类型：

   • InnoDB支持全文索引（自MySQL 5.6.4版本开始）。
   • MyISAM支持全文索引，并且比InnoDB的全文索引更成熟。

6. 表空间：

   • InnoDB存储引擎可以将数据存储在一个单独的表空间内，这个表空间可以是一个单独的文件或者是由多个文件组成的。
   • MyISAM存储引擎将表存储在一个单独的文件中，每个表都有三个文件，文件名都和表名相同，扩展名分别是.frm（存储表定义）、.MYD (MYData，存储数据)、.MYI (MYIndex，存储索引)。

7. 性能：

   • 对于只读或以读为主的工作负载，MyISAM通常比InnoDB快。
   • 对于更新频繁、需要事务支持的场景，InnoDB是更好的选择。

8. 行数统计：

   • MyISAM在打开表的时候会把表的总行数记录在磁盘上，因此查询COUNT(*)的时候速度非常快。
   • InnoDB没有记录表的总行数，因此查询COUNT(*)的时候需要全表扫描。

9. 内存使用：

   • InnoDB使用更多的内存来缓存数据和索引，以便更快地处理事务。
   • MyISAM使用较少的内存，因为它不支持事务。

10. 表大小限制：

    • MyISAM表的大小限制为4GB（如果使用16KB的块）。
    • InnoDB表的大小限制为64TB。

在选择存储引擎时，需要根据应用程序的需求、性能要求和数据完整性需求来决定使用InnoDB还是MyISAM。通常情况下，如果需要事务支持、外键约束或者崩溃恢复能力，应该选择InnoDB。如果应用程序以读取为主，不需要事务支持，MyISAM可能是一个更好的选择。