2015-02-14 16:49

原创声明：本作品采用知识共享署名-非商业性使用 3.0 版本许可协议进行许可，欢迎转载，演绎，但是必须保留本文的署名（包含链接），且不得用于商业目的。

什么是存储引擎？ 存储引擎是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。存储引擎在数据库系统中所处的位置如下图所示：

MySQL中支持多种类型的存储引擎如MyISAM、InnoDB、CSV、Memory等等，可以在mysql命令行中通过 show engine 命令查看当前数据库支持的存储引擎，MySQL5.5支持的存储引擎如下图所示：

从上图中可以看到MySQL5.5的默认存储引擎是InnoDB。

设置存储引擎

下面通过一个实例说明如何给数据库中的表设置存储引擎。

在数据库中创建如下表：

1
2
3
4
5
6
7
8

CREATE TABLE IF NOT EXISTS products (
    productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    productCode  CHAR(3)       NOT NULL DEFAULT '',
    name         VARCHAR(30)   NOT NULL DEFAULT '',
    quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
    price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
    PRIMARY KEY  (productID)
);

上述建表语句中我们并没有指定存储引擎，但是数据库会自动会给products表选择默认的存储引擎，我们可以用 SHOW CREATE TABLE products \G 命令查看products表在建表时选择的存储引擎：

从图中可以看到products表在建表时设置的存储引擎是 InnoDB。 我们再用 show table status like 'products'\G; 命令确认此时的表所使用的存储引擎：

表的存储引擎也可以用命令 Alter table tableName engine=engineName 进行修改，比如将表products的存储引擎从InnoDB改为MyISAM：

1

alter table products engine=MyISAM;

然后我们再用来看一下此时products表的存储引擎：

当然，也可以在建表的时候设置表的存储引擎类型，基本语法如下：

1
2
3

Create table tableName(
...
) engine = engineName

以上述products表为例，要将存储引擎修改为MyISAM，只需将建表语句修改为：

1
2
3
4
5
6
7
8

CREATE TABLE IF NOT EXISTS products (
    productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    productCode  CHAR(3)       NOT NULL DEFAULT '',
    name         VARCHAR(30)   NOT NULL DEFAULT '',
    quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
    price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
    PRIMARY KEY  (productID)
) engine=MyISAM

再来确定一下products表此时的使用的存储引擎：

如何选择合适的存储引擎

既然有这么多存储引擎可供选择，那么在设计时需要决定选择何种存储引擎来存储数据。最常用的存储引擎是InnoDB和MyISAM，至于到底用哪种存储引擎比较好,这个问题是没有定论的，需要根据你的需求和环境来衡量,比如是否需要支持事务操作、是否支持行锁、是否支持全文搜索等等。下面来具体讲解一下每种存储引擎的特点以及适用场景。

MyISAM引擎

MyISAM将每个表存储为三个文件：frm-表格定义文件、MYD(MYData)-数据文件、MYI(MYIndex)-索引文件。MyISAM的格式是平台通用的，所以用户可以在不同架构上相互拷贝数据文件和索引文件。MyISAM表可以包含动态行和静态行。MySQL会根据表定义决定选用何种行格式。MyISAM表的可容纳的行总数，一般只受限于数据库的可用磁盘空间大小，以及操作系统允许创建的最大文件大小。

在MySQL5.5之前，MyISAM引擎是MySQL的默认存储引擎。从5.5之后，默认引擎改成了InnoDB。

MyISAM表的特征：

• 不支持事务（No transactions）
• 不支持行级锁（row level），只支持表锁（Table level）
• 支持Insert和Select的并发执行
• 支持3类索引：B-Tree，R-Tree（空间索引），full-text（全文索引）
• 不支持外键（Foreign keys）
• 支持延迟更新索引
• 不支持数据自动恢复（No automated data recovery）
• 压缩存储
• 只有索引被缓存到内存中（Only indexes are cached in memory）
• 高速读取

下面再补充说明上述几个特征。

MyISAM不支持事务。事实上，MyISAM表甚至不能保证单个语句会执行完成。如果在多行update过程中有错误发生，一些行会被更新，而另外一些行不会。

加锁和并发。MyISAM表对整张表加锁，读操作会加共享锁，写操作会加排他锁。但是支持查询时在该表内插入新行，也就是所谓的Insert和Select的并发执行（需要设置concurrent insert）。

索引特性多种索引。MyISAM表支持B-Tree,R-Tree,以及full-text索引。值得注意的是MyISAM表索引长度不能超过1000字节。

延迟更新索引。当使用delay_key_write创建的MyISAM表时，在查询结束后，不会将索引的改变数据立即写入磁盘，而是先存放在键缓冲区中缓存。在清理缓冲区或者关闭表时才会将索引块刷到磁盘。对于数据经常改变，并且使用频繁的表，该模式很大程度上提高了表的处理性能。不过，如果服务器或者系统崩溃时，索引肯定损坏，并需要修复。这时可以使用如上的修复工具进行修复。延迟特性可以被全局配置也可以为个别表单独配置。

不支持数据自动恢复。如果MySQL服务器崩溃或者掉电，就应该在使用之前用check table mytable和repair table mytable来检查并修复错误。如果有大型表，这个过程可能会持续几个小时。当服务离线时，也可以使用MyISAMchk命令行工具检查和修复表。

压缩操作。压缩表是不能被改变的(除非是解压后，修改然后再压缩表)。压缩表占用的磁盘空间很小,降低磁盘I/O，提高表的处理性能。同时压缩也支持索引，但注意索引也是只读的。

只有索引被缓存到内存中。MyISAM只缓存了MySQL进程内部的索引，并保持在键缓冲区。操作系统缓存了表的数据，因此在MySQL5.0中，要想获取数据，就需要操作系统的调用，这个过程消耗很大。

总的来说，MyISAM引擎适用如下场景：

1
2
3
4

1) 数据库并发数量不大
2) 读操作为主（20%写，80%读）
3) SQL简单
4) 数据量大

InnoDB引擎

MySQL中使用最为广泛的除了MyISAM表之外就是InnoDB引擎了。InnoDB引擎是专为事务处理设计的。InnoDB将所有的数据共同存储在一个或几个数据文件中，这种数据文件一般称为表空间。在MySQL4.1及更新版本中InnoDB支持每个表和相关索引存储为单独的分离的文件。

InnoDB表的特征：

• 支持事务（支持事务SQL 92的4个事务隔离级别，默认的级别是repeatable）
• 支持行级锁（InnoDB的行锁机制是通过索引来完成的）
• 支持MVCC（数据多版本读取）
• 支持外键
• 优化的缓存（缓存索引和数据）
• 未压缩的索引（因此可能会比MyISAM表的索引大很多）

外键。在MySQL5.0中，InnoDB是唯一支持外键的存储引擎。其他存储引擎在CREATE TABLE时可以创建外键，但是不会约束。

总的来说，InnoDB引擎适合以下场景：

1
2
3

1) 数据库读写并发大
2）写操作多
3）事务、外键等特殊需求

那么对于数据库的select、insert、update、delete操作，MyISAM和InnoDB引擎哪个性能更高呢？一般来讲：

1
2
3
4

select: MyISAM 更优
insert: InnoDB 更优
update: InnoDB 更优
delete: InnoDB 更优

Memory引擎

Memory引擎，顾名思义就是一个将数据存储在内存中的存储引擎。所以一旦MySQL或者主机崩溃后，Memory的表只有表结构仍然会保留，相关数据将全部丢失。

Memory引擎有以下特点

• 支持hash索引(Memory引擎的默认索引)，它使得查询非常快速。
• 使用表级锁，只支持较低的并发。
• 不支持text或blob类型，也只支持固定大小行。
• 重启后，如果打开binlog会写一条truncate table又保证主从数据一致由于不写盘，写入速度很可观。

MyISAM、InnoDB、Memory对比：

Ref

• Which is faster, InnoDB or MyISAM?

• What’s the difference between MyISAM and InnoDB?

• What are the main differences between InnoDB and MyISAM?

• MyISAM versus InnoDB

• MyIsam Vs Innodb Vs Memory – Mysql Storage Engine Comparison