MySQL的存储引擎

此文主要介绍了MySQL存储引擎总结，讲解了什么是存储引擎、MyISAM、InnoDB、MEMORY等内容

前言

在数据库中存着一张张有着千丝万缕关系的表，表的设计好坏将直接影响着整个数据库。而在设计表的时候，我们都要首先考虑一个问题，就是使用什么存储引擎。那么，什么是存储引擎呢？

什么是存储引擎？

引擎的概念：引擎是开发程序或系统的核心组件。利用引擎，开发者可以迅速建立、铺设程序所需的功能，或利用其辅助程序的运转。

存储引擎就是指表的类型以及表在计算机上的存储方式。

存储引擎的概念为MySQL的特有，Oracle没有专门的存储引擎的概念，Oracle有OLTP和OLAP模式的区分。

不同的存储引擎决定了MySQL数据库中的表可以用不同的方式存储。我们可以根据数据的特点来选择不同的存储引擎。
在MySQL客户端中，使用以下命令可以查看MySQL支持的引擎：

show engines;

在MySQL中存储引擎有很多种。下面重点关注InnoDB、MyISAM、MEMORY这三种。

InnoDB存储引擎

InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。在MySQL从3.23.34a开始包含InnnoDB。它是MySQL上第一个提供外键约束的表引擎。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。靠后版本的MySQL的默认存储引擎就是InnoDB。

InnoDB存储引擎支AUTO_INCREMENT。自动增长列的值不能为空，并且值必须唯一。MySQL中规定自增列必须为主键。在插入值的时候，如果自动增长列不输入值，则插入的值为自动增长后的值;如果输入的值为0或空(NULL)，则插入的值也是自动增长后的值;如果插入某个确定的值，且该值在前面没有出现过，就可以直接插入。

create table tbx(
sn int(4) primary key 
auto_increment,
other fields...
);
or
create table tbx(
sn int(4) auto_increment,
primary key(sn),
other fields...);

InnoDB还支持外键(FOREIGN KEY)。外键所在的表叫做子表，外键所依赖(REFERENCES)的表叫做父表。父表中被字表外键关联的字段必须为主键。当删除、更新父表中的某条信息时，子表也必须有相应的改变，这是数据库的参照完整性规则。

foreign key(tby's field) 
references tbx(field) 
on update cascade 
on delete cascade;

InnoDB中，创建的表的表结构存储在.frm文件中.

InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

MyISAM存储引擎

MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。MyISAM是基于ISAM（Indexed Sequential Access Method【索引序列存取法】）引擎发展起来的，增加了许多有用的扩展。

MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、MYD、MYI。其实，frm文件存储表的结构;MYD文件存储数据，是MYData的缩写;MYI文件存储索引，是MYIndex的缩写。

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的;动态型包含变长字段，记录的长度不是固定的;压缩型需要用到myisampack工具，占用的磁盘空间较小。

MyISAM的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

MEMORY存储引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。

MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。

注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

怎样选择存储引擎

在实际工作中，选择一个合适的存储引擎是一个比较复杂的问题。每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好。

InnoDB：

支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高(比如银行)，要求实现并发控制(比如售票)，那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交(commit)和回滚(rollback)。

以下场合，使用InnoDB是最理想的选择：
1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。
2.事务。InnoDB存储引擎是支持事务的标准MySQL存储引擎。
3.自动灾难恢复。与其它存储引擎不同，InnoDB表能够自动从灾难中恢复。
4.外键约束。MySQL支持外键的存储引擎只有InnoDB。
5.支持自动增加列AUTO_INCREMENT属性。

MyISAM：

插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

MyISAM表无法处理事务，这就意味着有事务处理需求的表，不能使用MyISAM存储引擎。MyISAM存储引擎特别适合在以下几种情况下使用：

1.查询密集型的表。MyISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点。
2.插入密集型的表。MyISAM的并发插入特性允许同时选择和插入数据。例如：MyISAM存储引擎很适合管理邮件或Web服务器日志数据。

MEMORY：

所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

一般在以下几种情况下使用Memory存储引擎：

1.目标数据较小，而且被非常频繁地访问。在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制Memory表的大小，设置此参数，就可以限制Memory表的最大大小。

2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中。

3.存储在Memory表中的数据如果突然丢失，不会对应用服务产生实质的负面影响。

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。