MySQL数据库的存储引擎【InnoDB&MyISAM】

原文地址 https://my.oschina.net/junn/blog/183341

数据库的存储引擎

---

MySQL有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE

MySQL 支持数个存储引擎作为对不同表的类型的处理器。MySQL 存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎：

• MyISAN管理非事务表。它提供高速存储和检索，以及全文搜索能力。MyISAN在所有 MySQL 配置里被支持，它是默认的存储引擎，除非你配置 MySQL 默认使用另外一个引擎

• MEMORY 存储引擎提供"内存中"表。MERGE 存储引擎允许集合将被处理同样的 MyISAN表作为一个单独的表。就像 MyISAN一样，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 支持。

• ARCHIVE 存储引擎被用来存储无索引得，非常小地覆盖存储的大量数据

• CSV 存储引擎把数据以逗号分隔的格式存储在文本文件中

• BLACKHOLE 存储引擎接受但不存储数据，并且检索总是返回一个空集

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

1. 常用的 MyISAN和 InnoBD

---

	MyISAM	InnoDB
构成上的区别	每个 MyISAN在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)	基于磁盘的资源是 InnoDB 表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
事务处理上方面	MyISAN类型的表强调的是性能，其执行数度比 InnoDB 类型更快，但是不提供事务支持	InnoDB 提供事务支持事务，外部键（foreign key）等高级数据库功能
SELECT，UPDATE，INSERT，Delete 操作	如果执行大量的 SELECT，MyISAN是更好的选择	1.如果你的数据执行大量的INSERT 或 UPDATE，出于性能方面的考虑，应该使用InnoDB 表 2.DELETE FROM table 时，InnoDB 不会重新建立表，而是一行一行的删除 3.LOAD TABLE FROM MASTER 操作对 InnoDB 是不起作用的，解决方法是首先把 InnoDB 表改成 MyISAN表，导入数据后再改成 InnoDB 表，但是对于使用的额外的 InnoDB 特性（例如外键）的表不适用
AUTO_INCREMENT的操作	每表一个AUTO_INCREMEN 列的内部处理 MyISAM 为 INSERT 和 UPDATE 操作自动更新这一列。这使得 AUTO_INCREMENT 列更快（至少10%）。在序列顶的值被删除之后就不能再利用。(当 AUTO_INCREMENT 列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况） AUTO_INCREMENT 值可用 ALTER TABLE 或 myisamch 来重置 对于 AUTO_INCREMENT 类型的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAN表中，可以和其他字段一起建立联合索引，更好和更快的 auto_increment 处理	如果你为一个表指定 AUTO_INCREMENT 列，在数据词典里的 InnoDB 表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中，而不是存在磁盘上 关于该计算器的算法实现，请参考 AUTO_INCREMENT列在InnoDB里如何工作
表的具体行数	select count( * ) from table，MyISAN只要简单的读出保存好的行数，注意的是，当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%”

2. MySQL 存储引擎 MyISAN与 InnoDB 的区别

---

• 一、InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了

• 二、MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

• 三、InnoDB支持外键，MyISAM不支持

• 四、MySQL 在 5.1 之前版本默认存储引擎是 MyISAM，5.1 之后版本默认存储引擎是 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%’

关于 MyISAN与 InnoDB 选择使用

MyISAN和 InnoDB 是 Mysql 数据库提供的两种存储引擎。InnoDB 会支持一些关系数据库的高级功能，如事务功能和行级锁，MYISAM不支持。MYISAM的性能更优，占用的存储空间少。所以，选择何种存储引擎，视具体应用而定

如果你的应用程序一定要使用事务，毫无疑问你要选择 InnoDB 引擎。但要注意，InnoDB 的行级锁是有条件的。在where条件没有使用主键时，照样会锁全表。比如 DELETE FROM mytable 这样的删除语句

如果你的应用程序对查询性能要求较高，就要使用 MyISAN了。MyISAN索引和数据是分开的，而且其索引是压缩的，可以更好地利用内存。所以它的查询性能明显优于 InnoDB 。压缩后的索引也能节约一些磁盘空间。MyISAN拥有全文索引的功能，这可以极大地优化LIKE查询的效率

一些说法

现在一般都是选用 InnoDB 了，主要是 MyISAN的全表锁，读写串行问题，并发效率锁表，效率低 MyISAN对于读写密集型应用一般是不会去选用的

关于Mysql数据库默认的存储引擎

MyISAM 和 InnoDB 是 MySQL 的两种存储引擎。如果是默认安装，那就应该是 InnoDB，你可以在 my.ini 文件中找到 default-storage-engine=InnoDB；当然你可以在建表时指定相应的存储引擎。通过show create table [表名] 可以看见相应信息

3. Mysql中InnoDB和MyISAM的比较

---

MyISAM：

• 每个 MyISAM 在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)

• MyISAM表格可以被压缩，而且它们支持全文搜索。不支持事务，而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。在进行updata时进行表锁，并发量相对较小。如果执行大量的SELECT，MyISAM是更好的选择

• MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而 InnoDB 是索引和数据是紧密捆绑的，没有使用压缩从而会造成 InnoDB 比 MyISAM 体积庞大不小

• MyISAM 缓存在内存的是索引，不是数据。而InnoDB缓存在内存的是数据，相对来说，服务器内存越大，InnoDB发挥的优势越大

优点：查询数据相对较快，适合大量的select，可以全文索引。

缺点：不支持事务，不支持外键，并发量较小，不适合大量update

InnoDB：

这种类型是事务安全的。它与 BDB 类型具有相同的特性，它们还支持外键。InnoDB表格速度很快。具有比BDB还丰富的特性，因此如果需要一个事务安全的存储引擎，建议使用它。在update时表进行行锁，并发量相对较大。如果你的数据执行大量的 INSERT 或 UPDATE，出于性能方面的考虑，应该使用InnoDB表。

优点：支持事务，支持外键，并发量较大，适合大量update

缺点：查询数据相对较快，不适合大量的select

对于支持事物的 InnoDB 类型的表，影响速度的主要原因是 AUTOCOMMIT 默认设置是打开的，而且程序没有显式调用 BEGIN 开始事务，导致每插入一条都自动 Commit，严重影响了速度。可以在执行 sql 前调用 begin，多条 sql 形成一个事物（即使 autocommit 打开也可以），将大大提高性能。

基本的差别为：MyISAM 类型不支持事务处理等高级处理，而 InnoDB 类型支持。

MyISAM 类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。

InnoDB和MyISAM更新比较：

InnoDB 的数据组织就是按照主键建成的一个B+树，如果没有显示的定义主键，那么 InnoDB 会选区一个not null unique key，作为主键，如果还是没有，那么 InnoDB 会创建一个 6字节的主键，主键索引到页不是具体的行位置

不是递增的主键会使得插入的速度很慢，例如使用手机号或身份证号做为主键，所以善用AUTO_INCREMENT可以极大提高查询效率

表大不可怕，可怕的是 count 或者高偏移 limit，可以将大的 limit big 换成 limit max_id， xxxxx

Limit 0 1000 | limit 1001 1000 | limit 2001 1000

Limit 0 1000 | where id > max_id1 limit 1000 | where id > max_id2 limit 1000

对于 InnoDB 来说，按照某列分表，想在单台服务器上提高性能是没有意义的

插入的速度和查询的速度有时候是不可调和的矛盾

说 InnoDB 不适合做 count 是不对的，MyISAM 也是一样的慢，只不过 MyISAM 将正表的行数缓存起来，所以 count 整表很快，如果有查询条件，并且不是主键查询，那就没有什么区别，主键 count 慢的原因是 InnoDB 是按照主键组织的，按照主键 count 的时候，会加载数据

InnoDB 的页式存储会使得 InnoDB 更容易做整表缓存和热备份

如果表索引很多，那么InnoDB 的更新速度要大于 MyISAM ，因为 InnoDB 的辅助索引关联的是表的主键，是一个逻辑的值，而MyISAM的所有索引关联的是数据的物理位置，更新时有可能数据的物理位置发生变化，如果发生变化，那么所有的索引都要做更新

InnoDB 中不保存表的具体行数，也就是说，执行select count( * ) from table时，InnoDB 要扫描一遍整个表来计算有多少行，但是MyISAM 只要简单的读出保存好的行数即可。注意的是，当count( * )语句包含 where条件时，两种表的操作是一样的