MySQL的三种常见的存储引擎

目录

一、引言

 二、InnoDB存储引擎

三、MyISAM存储引擎

四、MEMORY存储引擎

五、怎样选择存储引擎

五.MyISAM索引与InnoDB索引的区别

1.InnoDB索引

 2.MyISAM索引

3.总结

---

一、引言

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

查看存储引擎的命令：show engines；

 二、InnoDB存储引擎

InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。现在是mysql的默认存储引擎。

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

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

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

InnoDB采用的索引为B+树索引。

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

三、MyISAM存储引擎

MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。

MyISAM的表存储成3个文件。文件的名字与表名相同。拓展名为frm、MYD、MYI

.frm文件:存储表的结构

.MYD文件:存储数据

.MYI文件:存储索引

基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。

静态型:MyISAM的默认存储格式，它的字段是固定长度的；

动态型:包含变长字段，记录的长度不是固定的；

压缩型:需要用到myisampack工具，占用的磁盘空间较小。

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

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

四、MEMORY存储引擎

MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。

每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中。

这样有利于数据的快速处理，提高整个表的效率。服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。

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

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

五、怎样选择存储引擎

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

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

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

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

五.MyISAM索引与InnoDB索引的区别

上面已经提到了，InnoDB和MyISAM都使用的是B+树索引。但是他们实现索引的数据存储方式不同。

1.InnoDB索引

InnoDB 存储引擎采用“聚集索引”的数据存储方式实现B-Tree索引，所谓“聚集”，就是指数据行和相邻的键值紧凑地存储在一起。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

而对于二级索引，InnoDB采用的方式是在叶子页中保存主键值，通过这个主键值来回表（上图）查询到一条完整记录，因此按辅助索引检索实际上进行了二次查询。

 

 2.MyISAM索引

MyISAM索引文件（.MYI）和数据文件(.MYD)是分离的，索引文件仅保存数据记录的地址。

索引文件仅保存记录所在页的指针（物理位置），通过这些地址来读取页，进而读取被索引的行。

 二级索引跟主键索引实现方式相同，都是保存数据的地址。

3.总结

MyISAM索引与InnoDB索引的区别？
1.都使用的是B+树索引结构，数据存储方式：InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
2.InnoDB的主键索引的叶子节点data域存储着完整数据，key为主键，因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。
3.InnoDB非主键索引的叶子节点存储的是主键值，因此查询时做到覆盖索引会非常高效。MyISAM的非主键索引叶子节点依旧存放的是地址，需要再次寻址才能得到数据。