密集索引和稀疏索引的区别

最新推荐文章于 2024-05-10 19:09:23 发布

じ小奋斗_

最新推荐文章于 2024-05-10 19:09:23 发布

阅读量797

点赞数

分类专栏： mysql 文章标签：数据库 mysql memcached

本文链接：https://blog.csdn.net/myloverxx/article/details/120390131

版权

mysql 专栏收录该内容

7 篇文章 1 订阅

订阅专栏

密集索引和稀疏索引的区别

密集索引文件中的每个搜索码值都对应一个索引值（大家可以理解为叶子节点保存的不仅仅是键值，还保存了同一记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个排列顺序，所以一个变只能创建一个密集索引。）
稀疏索引文件只为索引码的某些值建立索引项（叶子节点仅保存了键位信息和该行数据的地址，有的稀疏索引只保存了键位信息以及主键，那么定位到叶子节点之后，仍然需要通过地址还有主键信息进一步定位到数据）

咋们来看看他们的图：

请添加图片描述

上面都是对于一般数据库的情况，我们来对MySql做具体分析。它主要有两种存储引擎。一种是MyISAM列外一种是InnoDB当然还有其种，但是这两种是主流。前者不管是主键索引，唯一键索引，或者普通索引其索引均为稀疏索引而InnoDB呢它有且只有一个稀疏索引，那这个密集索引它的选取规则是如何的呢。

innoDB

如果一个主键被定义了那么这个主键会被作为密集索引存在的。
如果没有主键被定义，那该表的第一个唯一非空索引则作为密集索引。
若不满足以上的条件，innoDB内部会生成一个隐藏主键作为密集索引（这个隐藏主键是一个6字节的列，该数据的值，会随数据的插入而自增，也就是说我们的innoDB必须有一个主键，而该主键呢就必须作为唯一的密集索引存在）
非主键索引存储相关键位和其对应的主键值，包含两次查找。（一次是查找次级索引自身，然后再查找主键）

咋们来看下面一张图：

请添加图片描述

咋们可以看到InnoDB使用的是密集索引，将主键组织到一颗B+树中，而行数据就存在叶子节点上。因为InnoDB的主键索引和对应的数据是保存在同一个文件当中的。所以检索的时候在加载叶子节点的主键进入内存的同时也加载了对应的数据。即使用where id = 14 这样的条件查询主键。则按照B+树的条件查询算法呢即可查找到对应的叶子节点（如InnoDB左图红线所示）并获得对应的行数据。若对稀疏索引进行条件筛选，则需要经历两个步骤，第一个步骤是在稀疏索引的B+树中检索该键比如说（InnoDB右图的Ellison）然后就定位到主键信息了。那获取主键信息了之后，还要经历第二步使用主键 where = 14 在B+树中再执行一遍B+树的检索操作，最终再到达叶子节点获取整行的数据

而MyISAM使用的均为稀疏索引，稀疏索引的B+树看上去没有什么不同，节点的结构完全一致，只是存储的内容不一样而已。主键索引的B+树存储了主键，辅助键索引B+树存储的是辅助键，表数据存储在独立的地方，索引跟它的数据是分开存储的。这两个B+树的叶子节点都使用一个地址来指向真正的表数据。（如MyISAM图）对于表数据来说这两个键没有任何的差别，由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

所谓能动手就别吵吵，咋们打开我们的MySql客户端去连接咋们数据库看看。

在这里插入图片描述

现在我已经提前创建好了两张表 shop_info_small , person_info_large 那第一张表咋们使用的树MyISAM

在这里插入图片描述

person_info_large 使用的是InnoDB

在这里插入图片描述

我们的person_info_large是有数据的:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oxMn3sg4-1632113124212)(ms图库/person_info_large数据.png)]

shop_info_large没有数据。

创建了这两张表之后我们去到它的文件目录下，一般他就在MySql根目录的data目录下；并且咋们进入到database_demo数据库里面，然后去列出里面的文件来

在这里插入图片描述

咋们就可以看到person_info_large它是InnoDB存储引擎，它是由两个文件的，分别是 .frm 和 .ibd结尾。而shop_info_samll 它是MyISAM存储引擎它有三个文件分别是 .frm 和 .MYI 和 .MYD结尾。那其中它们都有相同结尾的 .frm 结尾也就是这两张表的结构信息就存储在 .frm 文件中。就够信息就是建表Sql

在这里插入图片描述

而不同的地方就是我们所要关注的地方了。person_info_large InnoDB的它的数据还有索引是存储在 .ibd这个文件下面的。而咋们的MyISAM它们的索引还有数据是分开的索引存在 .MYI文件中，数据存在 .MYD文件中那有些人就问了为啥数据就存在 .MYD 文件下面？就是因为我们的shop_info_small没有任何数据，所以文件大小是 0B

在这里插入图片描述

而它是有索引的，primary_key 和 unique key

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2CmbHOxE-1632113124221)(ms图库/MyISAM使用Sql.png)]

所以它是有值的大小为 1.0 k。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gBk3JRXM-1632113124224)(ms图库/MyISAM数据大小.png)]

那咋们来验证一下，向shop_info_small 添加一条数据。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6VtThnfD-1632113124226)(ms图库/MyISAM添加数据.png)]

添加好之后我们再列出来

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2ZGApLGJ-1632113124230)(ms图库/MyISAM添加数据后.png)]

就可以看到 .MYD 文件是有值的，.MYI的值也变大了。因此我们就可以知道数据确实存在 .MYD 文件中。

因此就可以得出结论

InnoDB它的数据和索引是存在一块的，MyISAM它的数据和索引是分开的。

总结

到这里我们再来看一下刚才之前关于索引的那几个问题是不是更容易回答了**😗*

为什么要使用索引（因为索引能让我们去避免全表扫描去查找数据，提高检索效率）
什么样的数据能成为索引（主键，唯一键等只要是能让数据具备一定区分性的字段都能成为索引）
索引的数据结构（主流是B+树，还有Hash以及BitMap等，其中MySql数据可不支持BitMap索引，同时基InnoDB，MyISAM存储引擎的MySql，不显示支持Hash）
密集索引和稀疏索引的区别

，还有Hash以及BitMap等，其中MySql数据可不支持BitMap索引，同时基InnoDB，MyISAM存储引擎的MySql，不显示支持Hash）

密集索引和稀疏索引的区别

じ小奋斗_

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
密集索引和稀疏索引的区别

密集索引和稀疏索引的区别密集索引文件中的每个搜索码值都对应一个索引值（大家可以理解为叶子节点保存的不仅仅是键值，还保存了同一记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只能有一个排列顺序，所以一个变只能创建一个密集索引。）稀疏索引文件只为索引码的某些值建立索引项（叶子节点仅保存了键位信息和该行数据的地址，有的稀疏索引只保存了键位信息以及主键，那么定位到叶子节点之后，仍然需要通过地址还有主键信息进一步定位到数据）咋们来看看他们的图：上面都是对于一般数据库的情况，我们来对My
复制链接

扫一扫