Mysql的两种存储引擎详细分析及区别（全）

Mysql的两种存储引擎详细分析及区别（全）

MySQL的存储引擎是数据库系统中负责数据存储和检索的核心组件。其中，MyISAM和InnoDB是最常用的两种存储引擎，它们在数据存储结构、事务支持、锁机制等方面存在显著差异。本文将深入分析这两种存储引擎的原理和特点，并对比它们的适用场景。

前言

MySQL有多种存储引擎（MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。）
但是常用的主要是这两种，分别为myism和innodb
补充说明一下
这两种数据结构都是用的b+树

B+树的非叶子节点不存储数据，只有叶子节点才存储数据
而B树的非叶子和叶子节点都会存储数据，会导致非叶子节点存储的索引值会更少，树的高度相对会比B+树高，平均的I/O效率会比较低
所以使用B+树作为索引的数据结构，再加上B+树的叶子节点之间会有指针相连，也方便进行范围查找

1. MyISAM（非聚集索引）

• 不支持事务，但是操作是有原子性的
• 不支持外键，支持表锁，每次操作的时候都是锁住整张表
  具体关于sql的锁机制
  可看我之前的文章
  Mysql中各类锁的机制图文详细解析（全）
• update会有表锁，所以并发量小，因为会进行阻塞。所以
  如果执行大量的SELECT，MyISAM是更好的选择
• 采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性
• MyISAM表格可以被压缩，而且它们
  支持全文搜索
• MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引
• MyISAM缓存有表meta-data（行数等），因此在做COUNT(*)的数据查询的时候比较快，但是如果加了where条件，就会比较慢
• 对于自增长的字段，在MyISAM表中可以和其他字段一起建立联合索引
  主键索引
  
  辅助索引
  
  对于上述两幅索引图来说
  MyISAM存储引擎在使用索引查询数据时，
  会先根据索引查找到数据地址，再根据地址查询到具体的数据
  。并且主键索引和辅助索引没有太多区别。
  两个表都是差不多，查找的思路也都是根据索引找到数据地址，在通过数据地址找到数据

2. InnoDB（聚集索引）

与MyISAM相比，InnoDB的最大特色就是支持了ACID兼容的事务（Transaction）功能。

• 支持外键
• 支持行锁，采用MVCC来支持高并发，有可能死锁
• 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引
• InnoDB用于事务处理，具有ACID事务支持等特性。如果在应用中执行大量insert和update操作，可选择。
• 不支持全文搜索
  主键索引
  
  辅助索引
  
  InnoDB中主键索引的叶子节点的数据区域存储的是
  数据记录
  ，辅助索引存储的是
  主键值
  。
  主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；
  因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问主键索引；
  最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
  Innodb中的主键索引和实际数据时绑定在一起的，也就是说Innodb的一个表一定要有主键索引，如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引(用户不可见)。（用户没有指定的话会自己找生产一个隐藏列Row_id来充当默认主键）
  另外，Innodb的主键索引要比MyISAM的主键索引查询效率要高(少一次磁盘IO)，并且比辅助索引也要高很多。
  所以，我们在使用Innodb作为存储引擎时，我们最好︰

2. 手动建立主键索引
3. 尽量利用主键索引查询
   自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）

3. 总结

区别 MyISAM InnoDB
结构 每个MyISAM在磁盘上存储成三个文件（扩展名指出文件类型）。.frm文件存储表定义，也就是存储结构的信息。.MYD文件存放数据文件。.MYI存放索引文件 表空间数据文件和它的日志文件。没有了myd与myi，只有.idb放了索引位置以及表的信息位置
事务 强调的是性能，不提供事务支持 提供事务支持事务，外部键等高级数据库功能
CRUD 查询很合适 增加或者更新合适。删除的时候，是一行一行删除
表的具体行数 select count(*) from table，可以很好的读取 不保存表的具体行数，要扫描一遍整个表来计算有多少行
AUTO_INCREMENT 可以和其他字段一起建立联合索引 必须包含只有该字段的索引
全文索引 支持 不支持
外键 不支持 支持
锁 表锁 提供行锁（执行一个SQL语句时MySQL不能确定要扫描的范围，同样会锁全表， 例如update table set num=1 where name like "%aaa%）

MyISAM：

优点：查询数据相对较快，适合大量的select，可以全文索引。

缺点：不支持事务，不支持外键，并发量较小，不适合大量update
InnoDB：

优点：支持事务，支持外键，并发量较大，适合大量update

缺点：查询数据相对较快，不适合大量的select
可能注意到我的标题有聚集索引和非聚集索引
具体这两部分的区分是
2.
聚集索引（聚簇索引）：表中的数据都会有一个主键，即使不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。
这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。``在B+树中，叶子节点存储整条记录的数据，这样的索引为聚集索引。
4.
非聚集索引（非聚簇索引）：
以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引
。叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据
聚集索引查找数据的过程，我们称为回表
。