数据库引擎比较详解--MyISAM与InnoDB

 MyISAM：

• 1.MyISAM索引（非聚集索引）：MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。索引和数据是分开的，并且索引是有压缩的。

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚集”的。

• 2. MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，也不支持外键
• 3. MyISAM 是表级锁不会出现死锁问题
• 4. MyISAM保存表的全部行数
• 5. MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储直接复制。

 

InnoDB：

• 1.  InnoDB索引实现（聚集索引）：InnoDB也使用B+Tree作为索引结构。但是InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键

InnoDB的辅助索引data域存储相应记录主键的值而不是地址，聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

• 2.Innodb 支持事务处理：事务支持commit，rollback和crashrecovery，从而保护用户数
• 3.基于行锁以及类似oracle的一致性读：提升了多用户的并发和性能
• 4. InnoDB也支持外键。如果使用了外键，insert，update以及delete操作都会得到检查，以确保不会导致多表之间的数据不一致。
• 5. InnoDB存储引擎维护了它自己的bufferpool（缓冲池），用来在内存中缓存数据和索引。经常使用的数据直接从内存中进行处理以便加快处理速度。
• 6. InnoDB支持crash recovery（崩溃修复），以便在数据库出现故障的时候，可以直接进行重启即可，数据不会丢失。在恢复的时候，可以提交已经commit的请求，回滚任何还未commit的请求。
• 7. InnoDB 中不保存表的具体行数也就是说，执行selectcount(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是当count(*)语句包含 where条件时，两种引擎的操作是一样的。

聚集索引与非聚集索引：

• 聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。
• 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。
• 按照定义，除了聚集索引以外的索引都是非聚集索引
• 非聚集索引：分成普通索引，唯一索引，全文索引。非聚集索引就像新华字典的偏旁字典，结构顺序与实际存放顺序不一定一致。

MVCC：多版本并发控制

先理解并发和并行的区别

• 并发的关键是你有处理多个任务的能力，不一定要同时。
• 并行的关键是你有同时处理多个任务的能力。
• 所以它们最关键的点就是 是否『同时』。

InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，没开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.

SELECT

InnoDB会根据以下两个条件检查每行记录:
a.InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.
b.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.
只有a,b同时满足的记录，才能返回作为查询结果.

DELETE

InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.