mysql的MyISAM和InnoDB对比，底层索引结构

左手的ㄟ右手 2022-04-05 15:56 278阅读 0赞

# 先整体概述下 #

<table style="width:500px;"> 
 <tbody> 
  <tr> 
   <td>&nbsp;</td> 
   <td>InnoDB</td> 
   <td>MyISAM</td> 
  </tr> 
  <tr> 
   <td>事务</td> 
   <td>支持</td> 
   <td>不支持，<span style="color:#ff0000;">回滚将造成不完全回滚，不具有原子性</span></td> 
  </tr> 
  <tr> 
   <td>mvcc</td> 
   <td>支持，辅助事务，可用于全库备份</td> 
   <td>不支持，全库备份需要使用全局锁</td> 
  </tr> 
  <tr> 
   <td>锁</td> 
   <td> <p style="margin-left:0cm;">默认行锁，也支持表锁</p> </td> 
   <td>仅支持表锁</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>支持</td> 
   <td> <p style="margin-left:0cm;">不支持外键</p> </td> 
  </tr> 
  <tr> 
   <td>delete操作</td> 
   <td>一行一行的删除</td> 
   <td><span style="color:#ff0000;">先drop表，然后重建表</span></td> 
  </tr> 
  <tr> 
   <td>文件系统</td> 
   <td>把数据和索引存放在表空间里面</td> 
   <td>frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是MYI (MYIndex)引伸</td> 
  </tr> 
  <tr> 
   <td>主键索引</td> 
   <td>使用聚集索引，主键和列值数据直接在一起，所以建议使用自增bigint 类型 主键</td> 
   <td>索引数据中存储数据所在地址，<span style="color:#f33b45;">主键也不是聚集索引</span></td> 
  </tr> 
  <tr> 
   <td>非主键索引</td> 
   <td>辅助索引data域存储相应记录主键的值而不是地址</td> 
   <td>data域保存数据记录的地址</td> 
  </tr> 
  <tr> 
   <td>count(*)</td> 
   <td>及时统计</td> 
   <td>单独保存，总行数，直接返回</td> 
  </tr> 
  <tr> 
   <td>跨平台</td> 
   <td>可直接拷贝使用</td> 
   <td>很难拷贝</td> 
  </tr> 
  <tr> 
   <td>压缩性</td> 
   <td>不能再被压缩</td> 
   <td>可以压缩</td> 
  </tr> 
 </tbody> 
</table>

选择

如果系统读少，写多的时候，尤其是并发写入高的时候，对原子性和事务要求的时候，必须使用InnoDB，所以推荐innodb

如果系统读多，写少。对原子性要求低。那么MyISAM最好的选择。且MyISAM恢复速度快。可直接用备份覆盖恢复。Innodb可以创建没有主键的表，此时innodb会自动生成一个不可见的[主键][Link 1]列 ，最好还是加上自增主键列

# 索引实现 #

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。

## MyISAM索引实现 ##

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

![20181221180816245][]

图8

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

![20181221180816264][]

图9

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

## InnoDB索引实现 ##

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

![20181221180816282][]

图10

图10是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

![20181221180816297][]

图11

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

hash索引

存储图解存储示意图如下：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70][]

上图中哈希函数h有可能将两个不同的关键字映射到相同的位置，这叫做碰撞，在数据库中一般采用**链接法**来解决。在链接法中，将散列到同一槽位的元素放在一个链表中。

[Link 1]: https://www.baidu.com/s?wd=%E4%B8%BB%E9%94%AE&tn=SE_PcZhidaonwhc_ngpagmjz&rsv_dl=gh_pc_zhidao
[20181221180816245]: /images/20220405/4b4d895e7fad4f1393e08a44ea4be680.png
[20181221180816264]: /images/20220405/e5c6028dec9e428e9d817368f8a94158.png
[20181221180816282]: /images/20220405/9d46df312c914a7f9196025eecc6d230.png
[20181221180816297]: /images/20220405/16db8edad8dd4730a2e708e924f9a1a6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2gyNjA0Mzk2NzM5_size_16_color_FFFFFF_t_70]: /images/20220405/89b75438ea6142ef84c32eb222a07766.png

发表评论取消回复

表情：

评论列表（有 0 条评论，278人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL存储引擎 (包括 InnoDB 和 MyISAM 对比)

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式

逃离我推掉我的手/ 2023年01月09日 15:39/ 0 赞/ 182 阅读

相关 [MySQL] 存储引擎InnoDB和MyISAM对比

存储引擎是数据库的核心，对mysql来说，存储引擎是以插件的形式运行的。虽然mysql支持种类繁多的存储引擎，但是常用的就那么几种。这篇文章主要是对其进行一个总结和对比。

灰太狼/ 2022年11月26日 01:55/ 0 赞/ 224 阅读

相关 MyISAM和InnoDB索引实现对比

MyISAM索引实现 MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。如图： ![MyISAM主键索引][MyISAM] 这

雨点打透心脏的1/2处/ 2022年08月05日 00:54/ 0 赞/ 213 阅读

相关 InnoDB与MyISAM总结和对比

Mysql的数据库引擎在我的博客中曾经有过陈述，但只是对所有引擎的一个简单叙述，而InnoDB与MyISAM数据库引擎是我们平时使用最多的，有必要进行详细全面的介绍和对比，以便

比眉伴天荒/ 2022年08月05日 00:54/ 0 赞/ 203 阅读

相关 mysql中innodb和myisam对比及索引原理区别

InnoDB和MyISAM是很多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，5.7之后就不一样了 1、事务和外键 InnoDB具有事务，支持4个事务隔离级

红太狼/ 2022年04月14日 03:21/ 0 赞/ 219 阅读

相关 mysql的MyISAM和InnoDB对比，底层索引结构

先整体概述下 <table style="width:500px;"> <tbody> <tr> <td> </td> <td>

左手的ㄟ右手/ 2022年04月05日 15:56/ 0 赞/ 279 阅读

相关 MySQL中Innodb和Myisam对比及索引原理区别

1、事务和外键 InnoDB具有事务，支持4个事务隔离级别，回滚，崩溃修复能力和多版本并发的事务安全，包括ACID。如果应用中需要执行大量的INSERT或UP

迈不过友情╰/ 2022年03月07日 09:18/ 0 赞/ 258 阅读

相关 MyISAM与InnoDB对比

MyISAM对比InnoDB 简介区别表锁差异数据库文件差异索引差异简介 1、MyISAM：默认表类

偏执的太偏执、/ 2021年11月11日 07:01/ 0 赞/ 367 阅读

相关 MyIsam和InnoDb索引

Mysql优化主要是索引的优化 1. 平衡多路搜索树B树（B-tree）上面讲到了二叉树，它的搜索时间复杂度为`O(log2N)`，所以它的搜索效率和树的深度有关

小鱼儿/ 2021年10月15日 01:17/ 0 赞/ 381 阅读

相关 MySQL存储引擎MyISAM和InnoDB底层索引结构

[https://blog.csdn.net/u010922732/article/details/82994253][https_blog.csdn.net_u0109227

浅浅的花香味﹌/ 2021年09月18日 14:04/ 0 赞/ 395 阅读