【面试】MySQL 中InnoDB与MyISAM的区别是什么?

Love The Way You Lie 2023-10-13 23:36 31阅读 0赞

#### 文章目录 ####

*  前言
 *  一、数据库存储引擎
 *  二、如何知道自己的数据库用的什么引擎呢？
 *  三、存储引擎原理
 *  四、B 树和 B+ 树
 *  五、MyISAM
 *  六、InnoDB
 *  七、InnoDB与MyISAM的区别
 *  总结

## 前言 ##

许多同学都把 MySQL 作为自己的数据库，但是可能用过最多的就是 SQL 语句，以及一些 ORM 的写法，而对底层的实现了解甚少，比如上述问题中，InnoDB 和 MyISAM 分别是什么，可能都不是非常清楚。然而在一些大型公司（比如腾讯）的面试题中，可能会高频率地出现这类的问题，所以对于这类问题的正确理解，就显得非常重要了。

其实 InnoDB 和 MyISAM 是 MySQL 的两个「存储引擎」。

## 一、数据库存储引擎 ##

> 数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

## 二、如何知道自己的数据库用的什么引擎呢？ ##

SHOW ENGINES;

## 三、存储引擎原理 ##

首先针对可能面试会问到的问题「MyISAM 和 InnoDB 两种引擎所使用的索引的数据结构是什么」做一个回答：

都是 B+ 树，不过区别在于：

*  MyISAM 中 B+ 树的数据结构存储的内容是**实际数据的地址值**，它的索引和实际数据是分开的，只不过使用索引指向了实际数据。这种索引的模式被称为非聚集索引。
 *  InnoDB 中 B+ 树的数据结构中存储的都是**实际的数据**，这种索引有被称为聚集索引。

## 四、B 树和 B+ 树 ##

那么什么是 B+ 树？  
![在这里插入图片描述][df9e23121a654baaa269fe078691dadd.png]  
B+ 树是 B 树的一个变种，对于 B 树来说：

B 树属于多叉树又名平衡多路查找树，其规则是：

*  所有节点关键字是按递增次序排列，并遵循左小右大原则
 *  子节点数：非叶节点的子节点数>1，且<=M ，且M>=2，空树除外（注：M阶代表一个树节点最多有多少个查找路径，M=M 路,当 M=2 则是 2 叉树,M=3 则是 3 叉）
 *  关键字数：枝节点的关键字数量大于等于 ceil(m/2)-1 个且小于等于 M-1 个（注：ceil() 是个朝正无穷方向取整的函数 如 ceil(1.1)结果为 2)
 *  叶节点的指针为空且叶节点具有相同的深度

而对于 B+ 树：

*  B+ 树是 B 树的一个升级版，相对于 B 树来说 B+ 树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。

## 五、MyISAM ##

回到 MyISAM，其索引结构如下图所示，由于 MyISAM 的索引文件仅仅保存数据记录的地址。在 MyISAM 中，主索引和辅助索引（Secondary key）在结构上没有任何区别：  
![在这里插入图片描述][6bb5eb116c434d7cbfa26a29513e0c80.png]  
MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定的 Key 存在，则取出其 data 域的值，然后以 data 域的值为地址，读取相应数据记录。

## 六、InnoDB ##

对于 InnoDB 来说，表数据文件本身就是按 B+Tree 组织的一个索引结构，这棵树的叶节点 data 域保存了完整的数据记录。  
![在这里插入图片描述][13db21a42519439ea8ee906ebfb9150f.png]

由于 InnoDB 利用的数据库主键作为索引 Key，所以 InnoDB 数据表文件本身就是主索引，且因为 InnoDB 数据文件需要按照主键聚集，所以使用 InnoDB 作为数据引擎的表需要有个主键，如果没有显式指定的话 MySQL 会尝试自动选择一个可以唯一标识数据的列作为主键，如果无法找到，则会生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

## 七、InnoDB与MyISAM的区别 ##

*  InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；
 *  InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；
 *  InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
 *  InnoDB不保存表的具体行数，执行select count(\*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
 *  Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高

<table> 
 <thead> 
  <tr> 
   <th align="left"></th> 
   <th align="left">MyISAM</th> 
   <th align="left">InnoDB</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">构成上的区别：</td> 
   <td align="left">每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。 .frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。 索引文件的扩展名是.MYI (MYIndex)。</td> 
   <td align="left">基于磁盘的资源是InnoDB表空间数据文件和它的日志文件，InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB</td> 
  </tr> 
  <tr> 
   <td align="left">事务处理上方面:</td> 
   <td align="left">MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持</td> 
   <td align="left">InnoDB提供事务支持事务，外部键(foreign key)等高级数据库功能</td> 
  </tr> 
  <tr> 
   <td align="left"><code>SELECT UPDATE,INSERT，Delete</code>操作</td> 
   <td align="left">如果执行大量的SELECT，MyISAM是更好的选择</td> 
   <td align="left">1.如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表 <br><br>2.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。<br><br> 3.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不适用</td> 
  </tr> 
  <tr> 
   <td align="left">对****AUTO_INCREMENT的操作</td> 
   <td align="left">每表一个AUTO_INCREMEN列的内部处理。 MyISAM为INSERT和UPDATE操作自动更新这一列**。这使得AUTO_INCREMENT列更快(至少10%)。在序列顶的值被删除之后就不能再利用。(当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况)。 AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置 对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引 更好和更快的auto_increment处理</td> 
   <td align="left">如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。 自动增长计数器仅被存储在主内存中，而不是存在磁盘上 关于该计算器的算法实现，请参考 AUTO_INCREMENT列在InnoDB里如何工作</td> 
  </tr> 
  <tr> 
   <td align="left">表的具体行数</td> 
   <td align="left">select count() from table,MyISAM只要简单的读出保存好的行数，注意的是，当count()语句包含 where条件时，两种表的操作是一样的</td> 
   <td align="left">InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行</td> 
  </tr> 
  <tr> 
   <td align="left">锁</td> 
   <td align="left">表锁</td> 
   <td align="left">提供行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表， 例如update table set num=1 where name like “%aaa%”</td> 
  </tr> 
 </tbody> 
</table>

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

*  InnoDB : 是Mysql的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
 *  MyISAM ： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
 *  MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据环境下，可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表，用以快速得到访问结果。
 *  MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制，并且通过将不同的表分布在多个磁盘上，可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

## 总结 ##

对于面试题来说，一般只会被要求回答到 InnoDB 和 MyISAM 在使用上的区别，不过如果需要深究一下为什么会有那些区别的话，就需要了解其底层的实现原理，顺便还需要对于 B+ 树有一定的了解，相信读者在读完本文后已经可以比较清晰地了解其背后的原理概要了，离拿到希望的 Offer 又近了一步。

[df9e23121a654baaa269fe078691dadd.png]: https://img-blog.csdnimg.cn/df9e23121a654baaa269fe078691dadd.png
[6bb5eb116c434d7cbfa26a29513e0c80.png]: https://img-blog.csdnimg.cn/6bb5eb116c434d7cbfa26a29513e0c80.png
[13db21a42519439ea8ee906ebfb9150f.png]: https://img-blog.csdnimg.cn/13db21a42519439ea8ee906ebfb9150f.png