MySQL5：MySQL数据存储文件；MylSAM中索引和数据是两个独立的文件，它是如何通过索引找到数据呢？聚集索引/聚簇索引，InnoDB中二级索引为什么不存地址而是存键值？row ID如何理解？

已于 2023-10-29 23:17:50 修改
阅读量610 收藏 2
点赞数 17
分类专栏： MySQL 文章标签： mysql MyISAM InnoDB 聚集索引
于 2023-10-26 23:23:43 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41929714/article/details/134048930
版权

MySQL5：MySQL数据存储文件；MylSAM中索引和数据是两个独立的文件，它是如何通过索引找到数据呢？聚集索引/聚簇索引，InnoDB中二级索引为什么不存地址而是存键值？row ID如何理解？

MySQL是一个支持插件式存储引擎的数据库。在MySQL里面，每个表在创建的时候都可以指定它所使用的存储引擎。

主要关注一下最常用的两个存储引擎，MylSAM和InnoDB的索引的实现。

MySQL数据存储文件

MySQL的数据都是文件的形式存放在磁盘中的，MySQL数据库文件的位置是由datadir参数决定的，这些位置可能因具体的MySQL版本和安装方式而有所不同，用户也可以通过MySQL的配置文件(my.cnf)来设置MySQL数据库文件的位置。

下面命令可以查看到数据文件存储的位置

show global variables like 'datadir%';

我这里用的是Windows的
在这里插入图片描述
可以看到，在Windows系统中，数据文件是在C:\ProgramData\MySQL\MySQL Server 5.7\Data
在这里插入图片描述
在这里插入图片描述
在MySQL的数据目录下，每个数据库对应一个文件夹，对应数据库的表文件就存在对应命名的文件夹中，以test数据库为例，点击进入该文件夹，可以发现数据表文件
在这里插入图片描述
在这里我们能看到，每张InnoDB的表有两个文件(.frm和.ibd)，MylSAM的表
有三个文件(.frm、.MYD、.MYI)。
数据文件：

  • .frm文件：是表结构定义文件，它存储了表的元数据信息，包括表的字段、索引等。.frm是MySQL里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成。
  • .MYD文件：是数据文件，它存储了实际的表数据。
  • .MYI文件：是索引文件，它存储了表的索引信息，用于加快查询速度。

.frm文件是表结构定义文件，它存储了表的元数据信息，包括表的字段、索引等。.frm是MySQL里面表结构定义的文件，不管你建表的时候选用任何一个存储引擎都会生成。我们主要看一下其他两个文件是如何实现MySQL不同的存储引擎的索引的。

MySQL中不同存储引擎索引落地方式

MylSAM

在MylSAM里面，另外有两个文件:

  • .MYD文件：D代表Data，是MylSAM的数据文件。存放实际的表数据记录，比如stu_myisam.MYD表的所有的表数据。
  • .MYI文件：I代表Index，是MylSAM的索引文件。存放索引，比如我们在id字段上面创建了一个主键索引，那么主键索引就是在这个索引文件里面。一个索引就会有一棵B+Tree，所有的B+Tree都在这个.MYI文件里面。

MylSAM中索引和数据是两个独立的文件，它是如何通过索引找到数据呢？

MylSAM的B+Tree里面，叶子节点存储的是数据文件对应的磁盘地址。所以从索引文件.MYI中找到键值后，会到数据文件.MYD中获取相应的数据记录。
在这里插入图片描述
在MylSAM里面，其他的索引也在这个.MYI文件里面。 
非主键索引跟主键索引存储和检索数据的方式是没有任何区别的，没有主次之分，二者都是在索引文件里面找到磁盘地址，然后到数据文件里面获取数据。 在这里插入图片描述
这个就是MylSAM里面的索引落地的形式。但是在的InnoDB里面是不一样的。

InnoDB

在InnoDB中，除了.frm文件，就只有一个.ibd文件，也就是说它的索引和数据都存放在这个文件里。也就是说在索引的叶子节点上，它是直接存储了我们的数据，不再是存储地址。 这也就是为什么说在InnoDB中索引即数据，数据即索引，就是这个原因。

InnoDB中多个索引，每个索引都会把完整记录在叶子节点放一份吗？

并不会，因为这会带来额外的存储空间浪费和计算消耗。一张InnoDB的表可能有很多个多索引，总不可能每个索引的叶子节点上都存一份数据，因为这样每增加一个索引会导致磁盘的空间占用成倍增加；而且在新增数据的时候，还需要在多颗B+Tree上面操作，既浪费空间，又浪费时间。
完整记录肯定是只有—份的。

InnoDB中完整记录放哪个索引的叶子节点？

其中有一个特殊的索引，这个索引就叫'聚集索引(聚簇索引)'，所谓聚集索引就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的，它的叶子节点上存放完整的记录行。

聚集索引/聚簇索引

'聚集索引(聚簇索引)'，就是索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的，它的叶子节点上存放完整的记录行。比如字典的目录是按拼音排序的，内容也是按拼音排序的，按拼音排序的这种目录就叫聚集索引。

InnoDB组织数据的方式就是(聚集)索引组织表(clustered index organize table)。 如果说一张表创建了主键索引，那么这个主键索引就是聚集索引，决定数据行的物理存储顺序。
在这里插入图片描述

InnoDB中其它索引(二级索引)，叶子节点上没有数据怎么检索完整数据？

InnoDB中，主键索引和辅助索引是有一个主次之分的。如果有主键索引，那么主键索引就是聚集索引，其他的索引统一叫做'二级索引(secondary index)'。

二级索引的叶子节点不存放完整的数据，那检索数据必然要到主键索引的叶子节点上拿到它的所有记录，也就是说，他需要存一个东西，能够完成它到达主键索引的叶子节点取数据的东西。

二级索引存储的是二级索引的键值，例如在name上建立索引，节点上存的是name的值，'Susan'等等(很明显，它的键值逻辑顺序跟物理行的顺序不一致)。二级索引的叶子节点存的是这条记录对应的主键的值。比如Susan id = 3。

二级索引需要先扫描自己的B+Tree，然后再根据二级索引的叶子节点找到主键索引的叶子节点拿到数据。从这个角度来说，因为主键索引比二级索引少扫描了一棵B+Tree(避免了回表)，它的速度相对会快一些。

回表

回表：非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。

二级索引检索数据的流程是这样的：
当我们用name索引查询一条记录，它会在二级索引的叶子节点找到name=Susan，拿到主键值，也就是id = 3，然后再到主键索引的叶子节点拿到数据。

在这里插入图片描述

InnoDB中二级索引为什么不存地址而是存键值？

因为地址会变化。当你的数据不断地新增和修改的时候，B+Tree也在不断地分裂和合并，那么数据的地址也会对应的会发生变化，那如果其他索引记录的是地址，其它索引的B+Tree都要改一遍，太麻烦了，所以其它索引存了一个不变的值，就是聚集索引的值。

InnoDB中如果一张表没有主键怎么办？那完整的记录放在哪个索引的叶子节点？或者, 这张表根本没有索引呢？数据放在哪里？

MySQL官网介绍：https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html
在这里插入图片描述

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

row ID如何理解？

  • 当表中存在一个数字类型的单列的主键时，row ID就是指这个主键列。
  • 当表中不存在主键，但存在一个数字类型的非空唯一列时，row ID其实就是指的是对应非空唯一列。

可以这样理解：
row ID并不是真实存在的列，row ID本质是一个非空、唯一的列的别名。

以下三种情况不能使用row ID查询：

  1. 主键列或者非空唯一列的类型不是数字类型——主键是聚集索引
  2. 主键是联合主键——主键是聚集索引
  3. 唯一列不是非空的，row ID没有指向任何一列，而是创建了一个6字节的隐藏列，作为聚集索引(不能显式查到，不代表不存在)

可以下面语句查询一张表的row ID的信息

select _rowid from stu_innodb;

如果这张表什么索引都没，查询_rowid会查不到，虽然不能显式查到，不代表不存在
在这里插入图片描述在这里插入图片描述
这个时候在id上加一个唯一索引，并且不允许为null
在这里插入图片描述
再次查询
在这里插入图片描述
这里可以看到row ID的查询结果就是刚加的唯一索引id的结果集，所以row ID本质是一个非空、唯一的列的别名。
在这里插入图片描述

MySQL合集

MySQL1：MySQL发展史，MySQL流行分支及其对应存储引擎
MySQL2：MySQL中一条查询SQL是如何执行的？
MySQL3：MySQL中一条更新SQL是如何执行的？
MySQL4：索引是什么；索引类型；索引存储模型发展：1.二分查找，2.二叉查找树，3.平衡二叉树，4.多路平衡查找树，5. B+树，6.索引为什么不用红黑树？7.InnoDB的hash索引指什么？
MySQL5：MySQL数据存储文件；MylSAM中索引和数据是两个独立的文件，它是如何通过索引找到数据呢？聚集索引/聚簇索引，InnoDB中二级索引为什么不存地址而是存键值？row ID如何理解？
MySQL6：索引使用原则，联合索引，联合主键/复合主键，覆盖索引、什么是回表？索引条件下推，索引的创建与使用，索引的创建与使用，索引失效

郝开
关注
专栏目录
官方Mysql-server linux-32位-Java工具类资源
01-08
mysql 8.0是一款数据库管理系统，mysql一般网站开发者或者数据库开发者会用到，mysql拥有体积小巧、读取和存储速度快的特点，新版本完全脱了MylSAM存储引擎，改善了扫描性能，同时还重构了sql分析器，赶快下载使用吧...
MySQL数据库的数据文件保存在哪？MySQL数据存在哪里
xiangxueerfei的博客
09-15 1万+
其中ibdata1、.ibd文件存放在mysql数据库安装目录的data文件夹中，而.frm文件在安装时如果没有设置的话那么默认的存放地址为C:\Documents and Settings\All Users\ApplicationData\MySQL\MySQL Server 5.1\data这个文件路径下的data文件夹内。Python所有方向的技术点做的整理，形成各个领域的知识点汇总，它的用处就在于，你可以按照上面的知识点去找对应的学习资源，保证自己学得较为全面。最后祝大家天天进步！
MySQL 索引
skystep的博客
09-12 727
索引是 帮助MySQL高效获取数据的数据结构。MySQL 数据存储硬盘不是连续存储，记录的存储单位是数据页，每次查询需对数据页的进行读取和加载，免不了 IO 操作，如果遍历全部页，那么意味着进行多次 IO 操作。索引通过实现了一套数据结构，记录了数据页和记录的关系，使得以更少的 IO 次数获取到目标数据页。此外，该数据结构对数据进行了预先排序，满足特定查找算法，可以更快的查询到数据页上的目标记录。因此，索引可以通过减少 IO 操作次数、使用更加快速的查找算法查找到目标记录。
MySQL中的存储文件和IO机制详细解析
最新发布
乙壳虫的博客
08-16 524
例如，当一个页中的数据满时，会触发页的分裂，形成新的页；当查询涉及多个页时（即跨页操作），需要多次从磁盘读取数据，这可能会导致IO操作的增加，从而影响查询的性能。每当需要对数据库中的数据进行增删改查时，MySQL不会直接访问磁盘上的数据，而是首先将数据页从磁盘加载到内存中的缓冲池（Buffer Pool）中进行操作。树的叶子节点包含了表的行数据，而非叶子节点存储的是指向子节点的指针。同时结合底层的数据结构原理，如B+树等，深入探讨数据在磁盘与内存之间的流动过程，理解跨页操作、分页读取对性能的影响。
Mysql的数据存储
不知道你是通过何种途径访问到这里，总之欢迎来到red velet的博客
03-10 3780
mysql数据存储格式，innodb存储引擎会有不同的文件格式和组织形式，Row Format(行记录格式)计算数据长度，COMPACT行记录格式、REDUNDANT、DYNAMIC、COMPRESSED
MySQL索引
qq_58000413的博客
10-30 711
红黑树：红黑树是AVL树的一个升级，损失了部分的性能来提升插入的性能，在红黑树中最低子树跟最高子树之差小于2被即可，在插入的时候，不需要进行N多次的旋转操作，而且还加入了变色的特性，来满足插入和查询的平衡。注意：mysql的innodb存储引擎默认情况下会把所有的数据文件放到表空间中，不会为每一个单独的表保存一份数据文件，如果需要将每一个表单独使用文件保存，设置。在树的结构中，左子树必须小于根节点，右子树必须大于根节点，如果是多叉树的话，从左到右是有序的。基本的索引类型，值可以为空，没有唯一性的限制（
MySQL索引详解
m0_68226879的博客
05-19 1015
聚集索引：也叫聚簇索引（ClusterIndex），一般来说是以主键创建的索引，一张表只能有一个聚集索引，而且只有InnoDB能够创建聚集索引。非聚集索引：也叫普通索引、辅助索引（Secondary Index），除了聚集索引外的索引都是非聚集索引。
淘宝数据魔方技术架构解析.pdf
09-04
数据产品可以通过一个唯一的URL获取到它想要的数据。 淘宝数据魔方技术架构解析揭示了淘宝数据产品在海量数据产品技术架构方面的探索，并展示了淘宝数据产品在数据产品技术架构方面的优势和特点。
Mysql二：深入理解mysql索引原理
热门推荐
严老板的技术梦想博客
01-16 3万+
一、索引的产生 INNODB插入时会根据主键排序，而MyISAM是根据你插入的顺序来排序的； CREATE TABLE t1( a int PRIMARY KEY, b INT, c INT, d INT, e VARCHAR(20) ) ENGINE = INNODB; INSERT into t1 VALUES(4,3,1,1,'d'); INSERT into t1 VALUES(1,1,...
MySQL 中可以存储文件吗？底层原理是什么？
长风破浪会有时的博客
07-17 1918
对于较大的文件，一种常见的做法是将文件存储在文件系统中，而在数据库中存储文件的路径或标识符。底层原理是，当将文件存储到MySQL中时，文件的内容会被转换为二进制数据，并存储在BLOB字段中。BLOB字段可以存储不同大小的二进制数据，包括小型文件和大型文件。可以通过读取文件内容并将其转换为二进制数据，然后将其插入到相应的BLOB字段中。是的，MySQL中可以存储文件。综上所述，MySQL通过BLOB字段提供了存储文件的能力，底层原理是将文件转换为二进制数据，并将其存储在相应的BLOB字段中。
MySQL数据文件介绍及存放位置（*）
weixin_42408447的博客
10-04 1万+
一.MySQL数据库文件介绍 MySQL的每个数据库都对应存放在一个与数据库同名的文件夹中，MySQL数据库文件包括MySQL（server）所建数据库文件和MySQL（server）所用存储引擎创建的数据库文件。 1.MySQL（server）创建并管理的数据库文件： .frm文件：存储数据表的框架结构，文件名与表名相同，每个表对应一个同名frm文件，与操作系统和存储引擎无关，即不管MySQL运行在何种操作系统上，使用何种存储引擎，都有这个文件。 除了必有的.frm文件，根据MySQL所使用的存储引擎的不
MySQL 索引 |ू･ω･` )
码里奥的博客
06-23 410
文章目录一、索引的概念二、索引的作用三、创建索引的原则依据四、索引的分类和创建1.普通索引2.唯一索引3.主键索引4.组合索引（单列索引与多列索引）5.全文索引（FULLTEXT）五、查看索引六、删除索引 一、索引的概念 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。 使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。 索
mysql存储文件
MrLee12的博客
08-09 1万+
使用mysql存储文件 在项目开发过程中，遇到了要使用mysql存储文件的问题，鉴于之前总是在数据库中总是保存字段信息，很少保存文件类型的数据。 在mysql中保存文件类型使用的数据类型主要为MediumBlob，其中也有其他类型数据： ①TinyBlob类型 最大能容纳255B的数据 ②Blob类型 最大能容纳65KB的 ③MediumBlob类型 最大能容纳16MB的数据 ④LongBlob类型 最大能容纳4GB的数据 该类型是mysql数据库中的二进制对象，可以保存图像、音乐、文件等资源。
Mysql数据存储格式分析
码星人
05-13 2142
主要是对Mysql数据的存储格式进行了详解分析
MySQL数据库中.frm和.myi和.myd和.ibd文件是什么文件?
weixin_49114503的博客
08-02 2079
ibd–表数据和索引的文件。该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据。（3）*.MYI–"I"索引信息文件，是表数据文件中任何索引的数据树。.MYD–"D"数据信息文件，是表的数据文件。.CSV –文件记录的就是具体的数据信息。.frm–表定义，是描述表结构的文件。（3）*.sdi–记录了表结构信息。.CSM–文件记录表状态与数据量。.frm–表结构的文件。
mysql 索引基本介绍
wyq2070857323的博客
03-21 1171
索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。select是整张表扫描 速度慢●索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。●索引是表中一列或者若干列值排序的方法。●建立索引的目的是加快对表中记录的查找或排序与普通索引类似，但区别是。
深入理解MySQL数据存储：frm、myd、myi文件详解
一起coding，一起嗨。
07-14 915
深入理解MySQL数据存储：frm、myd、myi文件详解
二级索引为何存主键不存数据的内存地址
qq_52896055的博客
09-04 277
3.支持覆盖索引查询：由于二级索引存储的是主键值，而不是数据的内存地址，所以在某些情况下，可以通过二级索引直接获取到查询所需的数据，而不需要再去访问主键索引或数据页，从而提高查询效率。2.索引更新更高效：当数据行发生更新时，只需要更新对应的二级索引中的主键值，而不需要更新存储数据的内存地址，从而提高了索引的更新效率。1.索引大小更小：存储主键值而不是数据的内存地址可以减小索引的大小，节省存储空间。
多核下MySQL并行存储引擎MTPower：提升数据库性能与索引插入效率
本篇论文深入探讨了在多核时代背景下，如何通过人工智能和机器学习技术改进数据库系统的性能，特别是在开源数据库管理系统MySQL中的并行存储优化。MySQL作为全球广泛使用的数据库系统，其原有的存储引擎如MylSAM和...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值