InnoDB与MyISAM对比

一、各个存储引擎

1、InnoDB引擎

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。行级锁，适合高并发情况

2、MyISAM引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁(myisam改表时会将整个表全锁住)，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。

3、Archive引擎

Archive存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。
Archive表适合日志和数据采集类应用。适合低访问量大数据等情况。
根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。

4、Blackhole引擎

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。

5、CSV引擎

CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。
CSV引擎可以作为一种数据交换的机制，非常有用。
CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。

6、Memory引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。(使用专业的内存数据库更快，如redis)

7、Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

二、InnoDB与MyISAM对比

1. 默认安装：

   InnoDB与MyISAM都是默认会安装的存储引擎

2. 事务支持

   InnoDB默认支持事务

   MyISAM不支持事务

   InnoDB引擎默认会见每一条sql语句封装成事务，自动提交。

3. 主键支持

   • InnoDB不必须有主键，而必须有唯一索引，如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引；如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

   • MyISAM可以没有主键

4. 外键支持

   InnoDB支持外键

   MyISAM不支持外键

   如果见一个包含外键的InnoDB表转换为MyISAM会失败。

5. 存储文件

   InnoDB的存储文件：frm、idb

   • frm是表定义文件
   • idb是数据文件（包括索引）

   MyISAM的存储文件：frm、myd、myi

   • frm是表定义文件
   • myd是数据文件
   • myi是索引文件

   MyISAN是数据和索引是分开存放的，而InnoDB的索引是存储在一起的

6. 索引结构

   InnoDB是聚簇索引，使用B+Tree作为索引结构。

   • 主键索引：InnoDB的索引和数据都存入idb文件中，他的索引结构是在同一个树节点中同时存放索引和数据。
   • 辅助索引：采用在叶子节点保存主键值，通过主键值查找主键索引。辅助索引实际上进行了二次查询。

   MyISAM是非聚簇索引，也是使用B+Tree作为索引结构,但是这两者之间是存在差别的。

   • 叶子节点的数据包含索引与数据文件的指针
   • 主键索引与辅助索引实现方式相同，只要求主键索引唯一、辅助索引的key可以重复
   • 查找流程：先从索引文件中查找到索引节点，从中拿到数据的文件指针，再到数据文件中通过文件指针定位了具体的数据。

   也就是说：InnoDB的B+Tree的主键索引的叶子节点就是数据文件，辅助索引的叶子节点就是主键的值；而MyISAM的B+Tree的主键索引与辅助索引都是数据文件的地址。

7. 行表锁

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

   MyISAM仅支持表锁

8. 全文索引

   Innodb不支持全文索引，而MyISAM支持全文索引，

   在涉及全文索引领域的查询效率上MyISAM速度更快高；

   注：5.7以后的InnoDB支持全文索引了

补充：聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。上图左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

• 聚簇索引的好处：
  • 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。
• 限制：
  • 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。
  • 由于数据物理存储排序方式只能有一种，所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。
  • 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

三、为什么推荐InnoDB引擎使用自增主键？

参考：为什么推荐InnoDB引擎使用自增主键？

InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。**如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。**这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。只要可以，请尽量在InnoDB上采用自增字段做主键。

1.4、面试题：InnoDB中一棵B+树能存多少行数据？

面试题：InnoDB中一棵B+树能存多少行数据？

补充：为什么 MySQL 的索引要使用 B+ 树而不是其它树形结构？

因为 B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。

补充面试题：mysql常见面试题