Ricardo的博客

我本逍遥一散人

MySQL B+Tree具体落地形式

这里主要讲解的是MySQL根据B+Tree索引结构不同的两种存储引擎(MYISAM 和 INNODB)的实现。

首先找到MySQL保存数据的文件夹,看看MySQL是如何保存数据的:

wei xin tu pian 20211002215539 - MySQL B+Tree具体落地形式

进入到这个目录下,这个目录下保存的是所有数据库,再进入到具体的一个数据库目录下。就能够看到MySQL存储数据和索引的文件了。

这里我创建了两张表,user_innod和user_myisam,分别指定索引为innodb和myisam。对于每张表,MySQL会创建相应的文件保存数据和索引,具体如下:

wei xin tu pian 20211002215657 - MySQL B+Tree具体落地形式

从图中可以看出:

MYISAM存储引擎存储数据库数据,一共有三个文件:
Frm:表的定义文件。
MYD:数据文件,所有的数据保存在这个文件中。
MYI:索引文件。

Innodb存储引擎存储数据库数据,一共有两个文件(没有专门保存数据的文件):
Frm文件: 表的定义文件。
Ibd文件:数据和索引存储文件。数据以主键进行聚集存储,把真正的数据保存在叶子节点中。

MyISAM存储引擎

在MYISAM存储引擎中,数据和索引的关系如下:

20200516185027444 - MySQL B+Tree具体落地形式

如何查找数据的呢?
如果要查询id = 40的数据:先根据MyISAM索引文件(如上图左)去找id = 40的节点,通过这个节点的数据区拿到真正保存数据的磁盘地址,再通过这个地址从MYD数据文件(如上图右)中加载对应的记录。

如果有多个索引,表现形式如下:

20200516185926534 - MySQL B+Tree具体落地形式

所以在MYISAM存储引擎中,主键索引和辅助索引是同级别的,没有主次之分。

Innodb存储引擎

Innodb主键索引为聚集索引,首先简单理解一下聚集索引的概念:数据库表行中数据的物理顺序和键值的逻辑顺序相同。

Innodb以主键索引来聚集组织数据的存储,下面看看Innodb是如何组织数据的。

20200516190622724 - MySQL B+Tree具体落地形式

如上图中,叶子节点的数据区保存的就是真实的数据,在通过索引进行检索的时候,命中叶子节点,就可以直接从叶子节点中取出行数据。mysql5.5版本之前默认采用的是MyISAM引擎,5.5之后默认采用的是innodb引擎。

在innodb中,辅助索引的格式如下图所示?

20200516201940584 - MySQL B+Tree具体落地形式

如上图,主键索引的叶子节点保存的是真正的数据。而辅助索引叶子节点的数据区保存的是主键索引关键字的值。

假如要查询name = C 的数据,其搜索过程如下:

  • 先在辅助索引中通过C查询最后找到主键id = 9.
  • 在主键索引中搜索id为9的数据,最终在主键索引的叶子节点中获取到真正的数据。
  • 所以通过辅助索引进行检索,需要检索两次索引。

回表: 在辅助索引中一次查询到主键的值后,再次回到主键索引查找数据的过程叫回表

之所以这样设计,一个原因就是:如果和MyISAM一样在主键索引和辅助索引的叶子节点中都存放数据行指针,一旦数据发生迁移,则需要去重新组织维护所有的索引。

InnoDB必有主键索引:

  • 如果已定义主键,则直接选择主键作为主键索引
  • 如果不存在主键,则使用不带有NULL值的唯一索引作为主键索引
  • 如果也不存在不带NULL值的唯一索引,则选择内置的6字节长的ROWID作为主键索引(递增,不可被选用 < 此为与Oracle InnoDB的不同)
  • InnoDB建议使用递增的主键
  • 递增的主键,在插入时近似顺序写入,会有更高的效率。
  • 数据页到达阈值时只需要分配新的页,不会导致页分裂,索引也更易于维护

缺点:在高并发工作负载时,可能导致间隙锁竞争,调优参数:innodb_autoinc_lock_mode

把Innodb 和 MYISAM区别放在一张图中看,就如下所示:

20200516204245909 - MySQL B+Tree具体落地形式

从二分查找法到b树系列

上一篇

创建索引的几大原则

下一篇
评论
发表评论 说点什么
还没有评论
90
0