InnoDB 和 MyISAM 区别

存储引擎 事务 行级锁 外键
InnoDB √ √ √
MyISAM × × ×

  • InnoDB 支持事务、行级锁，MyISAM 都不支持

  • InnoDB 支持行级锁，MyISAM 只支持表锁

  • InnoDB支持外键，MyISAM 不支持

  • InnoDB是聚簇索引（叶子节点存数据），MyISAM是非聚簇索引（叶子节点存指针）

  • 如果用InnoDB是必须有主键的，主键建议用自增的 id 而不用 uuid，用 uuid 会使得索引变慢。

  • MyISAM 提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的 SELECT 查询，那么 MyISAM 是更好的选择

  • InnoDB 支持热备份

  • MyISAM 崩溃后数据恢复很差

InnoDB

采用 MVCC 来支持高并发，并且实现了四个标准的事务隔离级别，并且通过 间隙锁 策略防止 幻读 的出现，所以不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定

基于聚簇索引建立的，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键索引，所以主键过大的话，其他的索引都会很大

MyISAM

可以存储的行记录数，而 InnoDB 只是估计值

不支持行级锁，读取加共享锁，写入加排他锁，会发生并发插入（在表有读取查询的时候，也可以往表中插入新的记录）

支持全文索引，压缩表（不会对表进行修改操作，比如日志类）

数据以紧密格式存储，所以在某些场合下的性能很好

MyISAM 索引实现

MyISAM（堆组织表）使用的是非聚簇索引、索引和文件分开，随机存储，只能缓存索引 MyISAM 引擎使用 B+Tree 作为索引结构，叶节点的 data 域存放的是数据记录的地址。如图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

InnoDB 索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

在数据库开发中，了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助。例如，知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好做法，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

如何选择存储引擎

  • 事务 如果需要事务，那么 InnoDB（或者XtraDB），如果不需要事务，并且只有 select 和 insert 操作，那么 MyISAM 还不错的选择，一般日志型都用 MySIAM

  • 备份 如果你需要在线热备份，那么 InnoDB 就是基本的要求，如果不需要热备份，那么都无所谓

转换表引擎

  • 最常规的做法： alter table mytable engine = InnoDB; 这个适用任何引擎，可是有个很大的问题，需要执行的时间较长，MySQL 会按照行将数据从原表复制到一张新的表中，在复制期间可能会消耗系统所有的 I/O 能力，同时会在原表上加读锁，所以对有些表操作时需要格外小心。

  • 第二种办法 这个就是导入导出，在导出的SQL文件中修改存储引擎

  • 第三种办法 先创建一个新的存储引擎的表，然后利用 insert .... select 语法来导入数据

# 如果数据量不大，可以这样操作，数据量大，最好分批处理
create table innodb_table like myisam_table;
alter table innodb_table engine = InnoDB;
insert into innodb_table select * from myisam_table;

# 分批处理
start transaction;
insert into innodb_table select * from myisam_table where id between x and y;
commit ;

聚簇索引