MySQL 存储引擎 InnoDB 与 Myisam 的六大区别

分类 编程技术

摘要: MySQL 有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：MyISAM、InnoDB、MERGE、MEMORY (HEAP)、BDB (BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。
MySQL 有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：
MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。
MySQL 支持数个存储引擎作为对不同表的类型的处理器。MySQL 存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

  • MyISAM 管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAM 在所有 MySQL 配置里被支持，它是默认的存储引擎，除非你配置 MySQL 默认使用另外一个引擎。
  • MEMORY 存储引擎提供 “内存中” 表。MERGE 存储引擎允许集合将被处理同样的 MyISAM 表作为一个单独的表。就像 MyISAM 一样，MEMORY 和 MERGE 存储引擎处理非事务表，这两个引擎也都被默认包含在 MySQL 中。注释：MEMORY 存储引擎正式地被确定为 HEAP 引擎。
  • InnoDB 和 BDB 存储引擎提供事务安全表。BDB 被包含在为支持它的操作系统发布的 MySQL-Max 二进制分发版里。InnoDB 也默认被包括在所 有 MySQL 5.1 二进制分发版里，你可以按照喜好通过配置 MySQL 来允许或禁止任一引擎。
  • EXAMPLE 存储引擎是一个 “存根” 引擎，它不做什么。你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在 MySQL 源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。
  • NDB Cluster 是被 MySQL Cluster 用来实现分割到多台计算机上的表的存储引擎。它在 MySQL-Max 5.1 二进制分发版里提供。这个存储引擎当前只被 Linux, Solaris, 和 Mac OS X 支持。在未来的 MySQL 分发版中，我们想要添加其它平台对这个引擎的支持，包括 Windows。
  • ARCHIVE 存储引擎被用来无索引地，非常小地覆盖存储的大量数据。
  • CSV 存储引擎把数据以逗号分隔的格式存储在文本文件中。
  • BLACKHOLE 存储引擎接受但不存储数据，并且检索总是返回一个空集。

  • FEDERATED 存储引擎把数据存在远程数据库中。在 MySQL 5.1 中，它只和 MySQL 一起工作，使用 MySQL C Client API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。

    比较常用的是 MyISAM 和 InnoBD

    |

    | MyISAM

    | InnoDB

    | | —- | —- | —- | | 构成上的区别：

    | 每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。

.frm 文件存储表定义。

数据文件的扩展名为.MYD (MYData)。

索引文件的扩展名是.MYI (MYIndex)。

| 基于磁盘的资源是 InnoDB 表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB

| | 事务处理上方面:

| MyISAM 类型的表强调的是性能，其执行数度比 InnoDB 类型更快，但是不提供事务支持

| InnoDB 提供事务支持事务，外部键（foreign key）等高级数据库功能

| | SELECT UPDATE,INSERT，Delete 操作

| 如果执行大量的 SELECT，MyISAM 是更好的选择

| 1. 如果你的数据执行大量的 INSERT 或 UPDATE，出于性能方面的考虑，应该使用 InnoDB 表

2.DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行的删除。

3.LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的，解决方法是首先把 InnoDB 表改成 MyISAM 表，导入数据后再改成 InnoDB 表，但是对于使用的额外的 InnoDB 特性（例如外键）的表不适用

| | 对 AUTO_INCREMENT 的操作

| 每表一个 AUTO_INCREMEN 列的内部处理。

MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列。这使得 AUTO_INCREMENT 列更快（至少 10%）。在序列顶的值被删除之后就不能再利用。(当 AUTO_INCREMENT 列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。

AUTO_INCREMENT 值可用 ALTER TABLE 或 myisamch 来重置

对于 AUTO_INCREMENT 类型的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中，可以和其他字段一起建立联合索引

更好和更快的 auto_increment 处理

| 如果你为一个表指定 AUTO_INCREMENT 列，在数据词典里的 InnoDB 表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。

自动增长计数器仅被存储在主内存中，而不是存在磁盘上

关于该计算器的算法实现，请参考

AUTO_INCREMENT 列在 InnoDB 里如何工作

| | 表的具体行数

| select count () from table,MyISAM 只要简单的读出保存好的行数，注意的是，当 count () 语句包含 where 条件时，两种表的操作是一样的

| InnoDB 中不保存表的具体行数，也就是说，执行 select count (*) from table 时，InnoDB 要扫描一遍整个表来计算有多少行

| | 锁

| 表锁

| 提供行锁 (locking on row level)，提供与 Oracle 类型一致的不加锁读取 (non-locking read in
SELECTs)，另外，InnoDB 表的行锁也不是绝对的，如果在执行一个 SQL 语句时 MySQL 不能确定要扫描的范围，InnoDB 表同样会锁全表， 例如 update table set num=1 where name like “% aaa%” |

MySQL 存储引擎 MyISAM 与 InnoDB 如何选择？

虽然 MySQL 里的存储引擎不只是 MyISAM 与 InnoDB 这两个，但常用的就是它俩了。可能有站长并未注意过 MySQL 的存储引擎，其实存储引擎也是数据库设计里的一大重要点，那么博客系统应该使用哪种存储引擎呢？
下面我们分别来看两种存储引擎的区别。

  • 一、InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而 MyISAM 就不可以了。
  • 二、MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用
  • 三、InnoDB 支持外键，MyISAM 不支持
  • 四、MyISAM 是默认引擎，InnoDB 需要指定
  • 五、InnoDB 不支持 FULLTEXT 类型的索引
  • 六、InnoDB 中不保存表的行数，如 select count () from table 时，InnoDB 需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count () 语句包含 where 条件时 MyISAM 也需要扫描整个表
  • 七、对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字段一起建立联合索引
  • 八、清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表
  • 九、InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like ‘%lee%’