Mysql存储引擎底层剖析（详解）

一，InnoDB 引擎

1，MySQL从3.23.34a开始就包含InnoDB存储引擎。   大于等于5 .5之后，默认采用InnoDB引擎

2、将每个表的数据和索引都放在一起放在.ibd文件中。

3、InnoDB有两个表，一个是.ibd文件（存储数据和索引），一个是.frm(存储表结构，MySQL8.0时，合并在表名.ibd中)

4、采用MVCC支持高并发，实现了四个标准的隔离级别，在REPEATED READ中通过实现间隙锁来防止幻读的出现。间隙锁使的InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入

5，如果增加和查询选择MyISAM，如果更新和删除操作，那么应优先选择InnoDB存储引擎。

二，MyISAM存储引擎

1，MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键 ，有一个毫无疑问的缺陷就是 崩溃后无法安全恢复而InnoDB恢复却可以 。

2，MyISAM有三个文件，InnoDB有两个，因为MyISAM将索引和数据分开存放，.MYD(放数据)、.FRM（放表结构）、.MYI（放索引）

3、MyISAM支持全文索引 

4，优势是访问的 速度快 ，对事务完整性没有要求或者以SELECT、  INSERT为主的应用

应用场景：只读应用或者以读为主的业务

5，MyISAM不支持事务和行级锁，而且崩溃后无法安全恢复InnoDB却可以、

三、InnoDB与MyISAM存储引擎的区别 

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住 整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其它行有影响， 适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较 高，而且内存大小对性能有决定性的影响
自带系 统表使 用	Y	N
关注点	性能：节省资源、消耗少、简单业 务	事务：并发写、事务、更大资源
默认安 装	Y	Y
默认使 用	N	Y

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

2.InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败

3.索引的存储结构不同，MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。
每个InnoDB表都有一个聚簇索引 ，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

4、在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。

5、如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。

6、如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

7、除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

8、主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到主索引中检索获得记录、根据在辅助索引树中获取的主键id，到主键索引树检索数据的过程称为回表查询。

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

10、 Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

11、. MyISAM表格可以被压缩后进行查询操作

12、InnoDB支持表、行级锁(默认行级锁），而MyISAM支持表级锁，InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。意思就是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

13、InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列ROWID来充当默认主键），而Myisam可以没有

14、Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
Innodb：frm是表定义文件，ibd是数据文件
Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

InnoDB为什么推荐使用自增ID作为主键？
自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

四、如何选择数据库引擎

1，是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM;
2，如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB

3，系统奔溃后，MyISAM恢复起来更困难，能否接受
4，MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

五、其他引擎

Archive引擎
        只支持Insert和select操作在MySQL5.1之前也不支持索引。每次SELECT查询都需要执行全表扫描。Archive引擎支持行级锁和专用的缓冲区，可以实现高并发的插入。在一个查询开始知道返回表中的所有行数之前，Archive引擎会阻止其他的SELECT操作，以实现一致性读。

Blackhole引擎
        没有任何的存储机制，它会丢弃所有插入的数据，不做任何保存。

CSV引擎
        可以将普通的CSV文件作为MySQL的表处理，但是这种表不支持索引。

Memory引擎
        如果需要快速地访问数据，并且这些数据不会被修改，那么可以使用Memory表，Memory表比MyISAM表快一个数量级，因为其所有的数据都保存在内存中，不需要进行磁盘I/O，Memory表的结构在重启后还会保留，以防数据丢失。
        Memory表支持Hash索引，所以查找操作非常快。Memory表是表级锁，并发写入的性能较低。
MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表，如果表太大会转变为MyISAM表。
        注意Memory表和临时表的区别：临时表是指使用CREATE TEMPORARY TABLE语句创建的表，它可以使用任何存储引擎，而且临时表只在单个连接中可见，当连接断开时，临时表也将不复存在。

Merge引擎
        Merge引擎是由对个MyISAM表合并而来的虚拟表