前言
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作为存储引擎时，我们最好︰
- 手动建立主键索引
- 尽量利用主键索引查询
自增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
可能注意到我的标题有聚集索引和非聚集索引
具体这两部分的区分是
- 聚集索引（聚簇索引）：表中的数据都会有一个主键，即使不创建主键，系统也会帮你创建一个隐式的主键。这是因为innodb是把数据存放在B+树中的，而B+树的键值就是主键，在B+树的叶子节点中，存储了表中所有的数据。
这种以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。`在B+树中，叶子节点存储整条记录的数据，这样的索引为聚集索引。` - 非聚集索引（非聚簇索引）：
以主键以外的列值作为键值构建的B+树索引，我们称之为非聚集索引。叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。
