MySQL：MySQL InnoDB VS MyISAM索引实现

OceanStar的学习笔记

已于 2022-09-15 22:06:36 修改

阅读量326

点赞数

分类专栏：数据库文章标签： mysql b树数据结构

于 2020-10-20 19:44:53 首次发布

原文链接：https://www.cxyxiaowu.com/7584.html

版权

数据库专栏收录该内容

289 篇文章 20 订阅

订阅专栏

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

概述

案例：假设有一张学生表，id为主键
在这里插入图片描述
在MyISAM引擎中的实现（二级索引也是这样实现的）

在InnoDB中的实现

实现

MySQL数据存储文件

首先，MySQL的数据都是以文件的形式存放在磁盘中的，我们可以找到这个数据目录的地址。在MySQL中有一个参数，我们来看一下：

show VARIABLES LIKE 'datadir';

每个数据库有一个目录，我们新建了一个叫做 test的数据库，那么这里就有一个 test的文件夹。这个数据库里面我们又建了 5 张表：archive、innodb、memory、myisam、csv。我们进入 test的目录，发现这里面有一些跟我们创建的表名对应的文件。

在这里我们能看到，每张 InnoDB 的表有两个文件（.frm 和.ibd），MyISAM 的表有三个文件（.frm、.MYD、.MYI）。

有一个是相同的文件，.frm。.frm是MySQL里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成。我们主要看一下其他两个文件是怎样实现MySQL的不同的存储引擎的索引的

MyISAM索引实现

举例

假设表一共有三列：col1、col2、col3。col1是主键索引，col2是辅助索引。那么其原理图如下：

下图是以col1为主键的MyISAM表的主键索引
下图是以col2为辅助索引

可以看出：

MyISAM的索引文件仅仅保存数据记录的地址（一颗B+树，data域仅仅保存数据的地址）
- 因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
- MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- 这种索引方式也叫做“非聚集”的。非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已：
  - 主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。
  - 表数据存储在独立的地方：
    - 这两颗B+树的叶子节点都使用一个地址指向真正的表数据，
    - 对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

落地

在 MyISAM 里面，另外有两个文件：

一个是.MYD 文件，D 代表 Data，是 MyISAM 的数据文件，存放数据记录，比如我们的 user_myisam 表的所有的表数据。
一个是.MYI 文件，I 代表 Index，是 MyISAM 的索引文件，存放索引，比如我们在 id 字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。

也就是说，在MyISAM中，索引和数据是两个独立的文件，那我们怎么根据索引找到数据呢？

MyISAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址，所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录
在MyISAM里面，辅助索引也在这个.MYI里面。辅助索引和主键索引检索存储引擎和检索数据的方式是没有任何区别的，一样是在索引文件里面找到磁盘地址，然后到数据文件里面去检索数据

在这里插入图片描述

InnoDB索引实现

原理

InnoDB只有一个文件(.ibd文件)，那索引放在哪里呢？

在InnoDB里面，它是以主键为索引来组织数据的存储的，所以索引文件和数据文件是同一个文件，都在.ibd文件里面。
在InnoDB的主键索引的叶子节点上，它直接存储了我们的数据

问题：主键之外的索引，比如我们在name字段上面建立的普通索引，是怎么存储和检索到数据的呢？

在这里插入图片描述
InnoDB中，主键索引和辅助索引是由一个主次之分的。

辅助索引存储的是辅助索引和主键值，如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据。

比如我们用 name 索引查询 name= ‘青山’，它会在叶子节点找到主键值，也就是 id=1，然后再到主键索引的叶子节点拿到数据

为什么在辅助索引里面存储的是主键值而不是主键的磁盘地址呢？如果主键的数据类型比较大，是不是比存地址更消耗空间呢？

我们前面说到 B Tree 是怎么实现一个节点存储多个关键字，还保持平衡的呢？

因为有分叉和合并的操作，这个时候键值地址会发生变化，所以在辅助索引里面不能存储地址

如果一张表没有主键怎么办？

如果我们显示定义了主键(PRIMARY KEY)，那么InnoDB会选择主键作为聚集索引
如果没有显示定义主键，那么InnoDB会选择第一个不包含NULL值的唯一索引作为主键索引
如果也没有这样的索引，那么InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而递增

select _rowid name from t2;

InnoDB VS MyISQL

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

（1）第一个重大区别是InnoDB的数据文件本身就是索引文件。

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

比如下图为主键索引（同时也是数据文件）示意图：
在这里插入图片描述
可以看出：

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

（2）第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。

比如下图为
定义在Col3上的一个辅助索引：
在这里插入图片描述

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

InnoDV VS MyISAM

InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引
InnoDB的主键索引的叶子节点存储行数据，因此主键索引非常高效
MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据

问题

问：为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

hash：虽然可以快速定位，但是没有顺序，IO复杂度高。[???]
二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高
红黑树：树的高度随着数据量增加而增加，IO代价高。

问：为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，也只会分裂很少一部分。并且能够减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

问：为什么不建议使用过长的字段作为主键？

因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

问：为什么不使用UUID作为主键ID

UUID(通用唯一标识码)不依赖于任何第三方系统，所以在性能和可用性上都比较好，一般会使用它生成Request ID来标识单次请求，但是如果用它来作为数据库主键，会存在如下问题。

（1）首先，生成的ID最好具有单调递增性，也就是有序的，而UUDI不具备这个特点。

为什么ID要是有序的呢？

因为在系统设计时，ID有可能成为排序的字段。
另一个原因是ID有序也会提升数据的写入性能
- 我们知道，MySQL InnoDB存储引擎使用B+树存储索引数据，而主键也是一种索引。索引数据在B+树中是有序排列的。如下图，图中的2、10、26都是记录的ID，也是索引数据
- 这时，当插入的下一条记录的ID是递增的时候，比如插入30时，数据库只需要把它追加到后面就好了。但是如果插入的数据是无序的，比如ID是13，那么数据库就要查找13应该插入的位置，在挪动13后面的数据，这就造成了多余的数据移动的开销。
  - 机械硬盘在完成随机写时，需要先”寻道“找到写入的位置，这个过程是非常耗时的。而顺序写就不需要寻道，会大大提升索引的写入性能

UUID不能作为ID的另一个原因是它不具备业务含义

其实现实世界中使用的 ID 中都包含有一些有意义的数据，这些数据会出现在 ID 的固定的位置上。比如说我们使用的身份证的前六位是地区编号；7～14 位是身份证持有人的生日；从手机号码的的前三位就可以看出这个手机号隶属于哪一个运营商
而如果生成的ID可以被反解，那么从反解出来的信息中我们可以对ID来做验证，我们可以从中知道这个ID的生成时间，从那个机房的发号器中生成的，为哪个业务服务的，对于问题的排查有一定的帮助。

最后，UUID是由32个16进制数字组成的字符粗，如果作为主键使用比较耗费空间

总结：UUDI方案由很大的局限性，而twitter提出的snowflakes算法完全可以弥补UUID存在的不足，它不仅算法简单易实现，也满足ID所需要的全局唯一性，单调递增性，还包含一定的业务上的意义