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

前言

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作为存储引擎时，我们最好︰

1. 手动建立主键索引
2. 尽量利用主键索引查询

自增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

可能注意到我的标题有聚集索引和非聚集索引

具体这两部分的区分是

1. 聚集索引（聚簇索引）：表中的数据都会有一个主键，即使不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。``在B+树中，叶子节点存储整条记录的数据，这样的索引为聚集索引。

2. 非聚集索引（非聚簇索引）：以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。