vlambda博客
学习文章列表

      存储引擎是数据库的核心，对于mysql来说，存储引擎是以插件的形式运行的。虽然mysql支持种类繁多的存储引擎，但是常用的就那么几种。这篇文章主要是对其进行一个总结和对比。

我们可以使用命令

show engines;

来看看当前数据库可以支持的存储引擎有哪些。从下图我们可以看出，MySQL支持9种存储引擎，但最常用的也就图中框出的三种，分别为MylSAM存储引擎、MEMORY存储引擎和InnoDB存储引擎。

不同的存储引擎都有各自的特点，以适应不同的需求，如下表所示。为了做出选择，首先要考虑每一个存储引擎提供了哪些不同的功能。

1、MYISAM存储引擎

    在创建数据表的时候可以选择表的存储引擎，如下图所示，为了测试MylSAM存储文件的结构以及特点，我们选择MylSAM。

创建完数据表之后，我们可以MySQL的安装目录的data文件夹中查看存储user表的文件结构，如下图所示：

从图中可以看出，使用这个存储引擎，每个MyISAM在磁盘上存储成三个文件。

（1）frm文件：存储表的定义数据，也就是表的结构(Frame)

（2）MYD文件：存储表具体记录的数据(MYData)

（3）MYI文件：存储表的索引(MYIndex)

      frm和MYI可以存放在不同的目录下。MYI文件用来存储索引，但仅保存记录所在页的指针，索引的结构是B+树结构。下面这张图就是MYI文件保存的机制：

      从上图可以发现，这个存储引擎通过MYI的B+树结构来查找记录页，再根据记录页查找记录。并且支持全文索引、B树索引和数据压缩。

    MylSAM有一个重要的特点那就是不支持事务，但是这也意味着他的存储速度更快，如果你的读写操作允许有错误数据的话，只是追求速度，可以选择这个存储引擎。

2、InnoDB存储引擎

同样的，我们创建一张role表，并设置存储引擎为InnoDB，如下图所示：

我们可以看到在data文件夹有两个文件，分别为role.frm和role.ibd，如下图所示，其中，frm文件存储表结构，idb文件存储表的索引和数据，正因为数据和索引是聚集到一个文件idb中的，所以InnoDB的索引称为聚集索引（B+树的叶子节点包含了完整的数据记录）。

InnoDB存储引擎的特点

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

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

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

常见面试题：

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

      因为Mysql是通过B+树组织这些索引的，如果不设定主键Mysql会默认生成主键比如rowid等；因为整型的存储比字段类型要小，而且正因为是使用的是B+Tree数据结构，在进行查询数据是需要对每个元素进行比较，而整型的对比效率是高于其他数据结构的；而且对于自增的主键而言，由于B+树各个节点是有序排列的，插入数据也非常方便。

比如对于这棵主键索引树：

如果我们插入 ID = 650 的一行数据，那么直接在最右边插入就可以了

      但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间，如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

      但是，如果我们的主键是自增的，每次插入的 ID 都会比前面的大，那么我们每次只需要在后面插入就行， 不需要移动位置、分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。

2.1、主键索引树与非主键索引树

       主键索引树下图所示，可以看出，主键索引叶子节点存储的是主键（key）和数据（value）。

      非主键索引树如下图所示，非主键索引的叶子节点存储的是索引的数据（key,比如名字索引Alice）和主键(value，比如15)

      由上图可以看出非主键索引叶子节点保存的是主键的值，得到主键的值之后再到主键索引树去搜索一遍，这个过程也成为回表。

2.2、联合索引树

联合索引的存储结构：

      联合索引非叶子节点存储的是联合索引（key）和主键（value），其索引的顺序是按索引常见的顺序排的，先按第一个索引排序，再按第二个索引排序…

      对于Hash索引的话，不支持区间范围查询，但是速度的确比B+树要快，B+树的双向链表支持范围查询，但是仅仅简单查询的话，Hash索引还是比B+树索引要快的。