InnoDB与MyISAM总结和对比

比眉伴天荒 2022-08-05 00:54 171阅读 0赞

Mysql的数据库引擎在我的博客中曾经有过陈述，但只是对所有引擎的一个简单叙述，而InnoDB与MyISAM数据库引擎是我们平时使用最多的，有必要进行详细全面的介绍和对比，以便我们在选择数据库引擎时能够根据需求更好地选择。

### InnoDB ###

#### 特点： ####

**支持ACID事务**(具有提交，回滚和崩溃恢复能力)，支持存储过程，视图，**支持行级锁定**，**支持外键**，处理巨大数据量时拥有最大性能(可以说它的CPU效率是其他基于磁盘关系数据库引擎所不能匹敌的)，表可以是任何尺寸，**不支持全文类型的索引**，插入缓冲、二次写、自适应哈希索引（三个特性详细介绍见: InnoDB的三个关键特性 ([http://blog.csdn.net/z702143700/article/details/46049101][http_blog.csdn.net_z702143700_article_details_46049101])。

#### 应用场景： ####

1.  以INSERT、UPDATE为主的应用。
2.  在需要高性能的大型数据库的应用中。
3.  更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
4.  事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
5.  自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
6.  外键约束。MySQL支持外键的存储引擎只有InnoDB。
7.  支持自动增加列AUTO\_INCREMENT属性。

注：InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”，select count(\*) 、order by等也会锁表。而**行级锁，一般只在涉及到索引的时候才会被使用**。  
比如：where条件中含索引字段时，就是行锁。其他情况都是表锁。

### MyISAM ###

#### 特点： ####

较高的查询速度，表级锁定；BLOB和TEXT列可以被索引；不支持事务，不支持外键；支持全文索引，支持3种不同的存储格式：静态(固定长度)表、动态表、压缩表；

静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意：在某些情况下可能需要返回字段后的空格，而使用这种格式时后面到空格会被自动处理掉。  
动态表包含变长字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁到更新删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。  
　　压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

#### 应用场景： ####

1.  对事务完整性没有要求或者以SELECT为主的应用。
2.  无事务要求，但对效率要求比较高的应用。

注：NULL值被允许在索引的列中，这个占每个键的0-1个字节。

### InnoDB和MyISAM对比： ###

1.  InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事，InnoDB 只需要几分钟。但是在某些场景MyISAM似乎更方便高效，因为它可以直接拷贝frm.MYD,MYI文件，然后在对于版本的数据中启动就可以使用了。比如，数据表损坏，迁移，还原数据只要拷贝文件就可以了。而InnoDB要考虑字典数据的影响，拷贝文件的方式无效，如果导出.sql文件，会耗费大量的时间。
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.  MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
7.  InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。
8.  MyISAM表是使用B型树索引，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，也叫做“非聚集索引”。
9.  InnoDB也使用B+Tree作为索引结构，这棵树的叶节点data域保存了完整的数据记录。它的索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引，也叫做“聚集索引”。MyISAM和InnoDB索引实现详细对比 ,见博客[http://blog.csdn.net/z702143700/article/details/46049101][http_blog.csdn.net_z702143700_article_details_46049101]

[http_blog.csdn.net_z702143700_article_details_46049101]: http://blog.csdn.net/z702143700/article/details/46049101