存储引擎-MySQL

一 、存储引擎作用于什么对象

存储引擎是作用在表上的，而不是数据库。

二 、MyISAM和InnoDB对索引和数据的存储

先来看下面创建的两张表信息，role表使用的存储引擎是MyISAM，而user使用的是InnoDB：

（role表）MyISAM	（user表）InnoDB
role.frm：   表结构文件 role.MYD：数据文件（MyISAM Data） role.MYI：  索引文件（MyISAM Index）	user.frm：表结构文件 user.ibd：索引和数据文件（InnoDB Data）
数据和索引是在不同文件中分开存储的	数据和索引是在同一个文件中存储的

 也由于两种引擎对索引和数据的存储方式的不同，我们也称MyISAM的索引为非聚集索引，InnoDB的索引为聚集索引。

三、MyISAM存储引擎

它是MySQL5.5之前的默认存储引擎

优势：访问速度快

适用场景：对事务的完整性没有要求，或以select，insert为主的应用基本都可以选用MYISAM。在Web、数据仓库中应用广泛。

1. 主键索引：

之前介绍过MySQL数据库索引是采用B+Tree存储，它只会在叶子节点存储数据，在MyISAM引擎中叶子结点存储的数据其实是索引和数据的文件指针两类。

如下图中我们以Col1列作为主键建立索引，通过索引查找数据的流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。 

2. 辅助（非主键）索引：

以Col2列建立索引，得到的辅助索引结构跟上面的主键索引的结构是相同的，不再描述

四、InnoDB存储引擎

MySQL5.5之后的默认存储引擎

应用场景：如果应用对事务的完整性有较高的要求，在并发条件下要求数据的一致性，数据操作中包含读、插入、删除、更新，那InnoDB是最好的选择。在计费系统、财务系统等对数据的准确性要求较高的系统中被广泛应用。

优点：提供了具有提交（Commit）、回滚（Rollback）、崩溃恢复能力的事务安全，支持外键。

缺点：相比较于MyISAM，写的处理效率差一点，并且会占用更多的磁盘空间来存储数据和索引

1. 主键索引：

我们已经知道InnoDB索引是聚集索引，它的索引和数据是存入同一个.idb文件中的，因此它的索引结构是在同一个树节点中同时存放索引和数据，如下图中最底层的叶子节点有三行数据，对应于数据表中的Col1、Col2、Col3数据项。 

2. 非主键（辅助）索引： 

这次我们以数据表中的Col3列的字符串数据建立辅助索引，它的索引结构跟主键索引的结构有很大差别，我们来看下面的图：

在最底层的叶子结点有两行数据，第一行的字符串是辅助索引，按照ASCII码进行排序，第二行的整数是主键的值。通过辅助索引找到对应的主键后，又会去对应的主键索引结构中找到对应的叶节点数据（具体的行数据）。

五、Memory存储引擎

适用场景：内容变化不频繁的代码表，作为统计操作的中间结果表，便于利用它速率快的优势高效的对中间结果进分析。

MEMORY存储引擎将表中的数据存储到内存中，每个memory表对应一个磁盘文件，格式是.frm

特点：由于他的数据是存放在内存中的，并且默认使用HASH索引，所以它的访问速度特别快，同时也造成了他的缺点，就是数据库服务一旦关闭，数据就会丢失，另外对表的大小有限制。

每个memary表中可存储数据量的大小，受到max_heap_table_size变量的约束，他的初始值是16MB，可以在定义Memary表的时候通过max_rows指定表的最大行数。

六、InnoDB索引结构需要注意的点

1、为什么建议InnoDB必须建主键，并且推荐自增的整型主键 ？

正如我们上面介绍InnoDB存储结构，索引与数据是共同存储的，不管是主键索引还是辅助索引，在查找时都是通过先查找到索引节点才能拿到相对应的数据，如果我们在设计表结构时没有显式指定索引列的话，MySQL会从表中选择数据不重复的列建立索引，如果没有符合的列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，并且这个字段长度为6个字节，类型为整型。

那为什么推荐使用整型自增主键而不是选择UUID？

（1）UUID是字符串，比整型消耗更多的存储空间；
（2）在B+树中进行查找时需要跟经过的节点值比较大小，字符串需要转化为ASCII后再进行索引的比较，整型数据的比较运算比字符串更快速；
（3）自增的整型索引在磁盘中会连续存储，在读取一页数据时也是连续；UUID是随机产生的，读取的上下两行数据存储是分散的，不适合执行范围查询语句。
（4）在插入或删除数据时，整型自增主键会在叶子结点的末尾建立新的叶子节点，不会破坏左侧子树的结构；UUID主键很容易出现这样的情况，B+树为了维持自身的特性，有可能会进行结构的重构(分裂、平衡)，消耗更多的时间。

2、为什么非主键索引结构叶子节点存储的是主键值？

1. 保持一致性	当数据库表进行DML操作（insert、update、delete）时，同一行记录的页地址会发生改变，因非主键索引保存的是主键的值，无需进行更改。
2. 节省存储空间：	Innodb数据本身就已经汇聚到主键索引所在的B+树上了， 如果普通索引还继续再保存一份数据，就会导致有多少索引就要存多少份数据。

七、存储引擎总结

1、InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2、InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件和索引是分离的，索引保存的是数据文件的指针。

3、Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高；

4、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

八、如何选择

1、是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
2、如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读，写也挺频繁，请使用InnoDB。
3、系统奔溃后，MyISAM恢复起来更困难，能否接受；
4、MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。