Mysql索引数据结构

花灯摇曳丶

已于 2023-10-23 16:13:28 修改

阅读量68

点赞数

分类专栏： Mysql 文章标签： mysql

于 2023-10-11 23:26:14 首次发布

本文链接：https://blog.csdn.net/weixin_44531533/article/details/133754784

版权

Mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL中的索引类型（如二叉树、红黑树、B-Tree、B+Tree），比较了它们的优缺点，并重点讲解了InnoDB和MySAM存储引擎的索引结构。还讨论了为何推荐使用整型自增主键，以及联合索引和最左前缀原则的应用。

摘要由CSDN通过智能技术生成

Mysql索引数据结构

什么是索引?

索引是帮助MySQL高效获取数据的排好序的数据结构.
数据结构分为 : 1.二叉树; 2.红黑树; 3.Hash表; 4. B-Tree;
数据存储在磁盘中的位置是随机的.没有建立合适的索引,那么在进行I/O读取时,效率会及其底下.所以,为了提高查询数据的效率,那么就需要建立合适的索引.

如图.如果没有建立索引,那么进行数据查找会查询7次.
使用二叉树.key为具体的数据,而value则为磁盘中存储数据的地址;在二叉树中进行数据查找之后,就减少了数据查询比对的次数.
在这里插入图片描述
但是如果使用二叉树,那么会产生上图的数据结构.就变成了一个链表结构.不能有效提升查询效率.
推荐一个好用的数据结构网站: https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
可以具体查看各种不同数据结构的实际情况.

所以不推荐使用二叉树.

下面就到了红黑树
在这里插入图片描述
红黑树也称为平衡二叉树.但是也存在缺陷.也就是数据量越多,树的高度会更高,那么查询的效率会越低.
这样,红黑树的数据结构来存储数据也不理想.
为了提升查询效率,对红黑树的层级进行调整优化.

B-Tree 大的节点会存储更多的数据,但是B-Tree仍然不能大量存储数据.
所以,又提出了B+Tree
在这里插入图片描述
如上图为B+Tree的数据结构,非叶子节点不存储具体数据,值存储索引值,只存储索引,用于存储更多的索引数据.另外叶子节点用指针连接,提高区间的访问性能.

B-Tree 和 B+Tree的区别:
在这里插入图片描述

使用二分法来进行数据查询.先通过索引查询到数据具体位置再查询到数据.
mysql 中没一个页的默认大小为16kb可以通过 SHOW GLOBAL STATUS like ‘Innodb_page_size’ 来进行查询结果为 16384 ;
同时,叶子节点之间的双向指针也提高了范围查询的速度.
在这里插入图片描述

在这里插入图片描述
普通的非聚集索引中.只会存储索引字段的数据以及主键索引字段的数据.这是为了保证数据的一致性以及节省存储空间.

为什么Mysql默认页大小为16kb?

为了提升I/O查询效率.

什么是联合索引?

页的大小为16kb能存储多少数据量?

假设用bigint 作为索引主键. 1个bigint的大小占用 8个字节. 15-20中空白的区域存储的是下一个页节点的地址(占6个字节) 那么 16kb存储的索引数量为 16kb ÷ (8b+6b)= 1170 .那么一个非叶子节点就存储1170个索引. 假设叶子节点存储一行记录为1kb.那么一个三层的B+Tree 存储的总的数据量为 1170 × 1170 × 16 = 21902400.
也就说千万级别的数据只需要查询3次即可找到具体数据.
同时mysql 会把非叶子节点的数据存储到内存中进一步提升查询效率;

为什么Mysql把选择B+Tree 而不是B-Tree ?

如果使用B-Tree,那么B-Tree 存储的数据量没有B+Tree 存储的多也就是同样层级的树高度那么B+Tree存储的数量会更多.

MySAM存储引擎

MySAM存储引擎在磁盘中.会存储3个类型的文件.frm,MYD,MYI.
frm:数据表结构相关;
MYD:存储数据;
MYI:存储索引结构;
MySAM引擎在查询数据时会先在MYI文件中找到数据对应磁盘地址然后再根据磁盘地址到MYD文件中查找对应数据.就是一个回表的过程;

InnoDB存储引擎

InnoDB存储引擎中在磁盘中存储2个类型的文件.frm,ibd.
frm:数据表结构相关;
idb数据和索引;
聚集索引(聚簇索引): 叶子节点会包含所有数据结构.
非聚簇索引: 叶子节点不会存储所有数据.也就是索引和数据是分开的.先通过索引查找到对应的数据地址.然后再根据地址到数据文件中找到对应的数据.

为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?

1.mysql在设计表结构时,数据是存储在idb文件中,如果没有设置自增主键,MySQL会采用某个不重复数据的字段作为主键.如果没有找到这样的字段,那么MySQL会创建一个隐藏的索引列来作为主键.
所以,创建一个自增的主键是为了减少MySQL创建索引的开销.
2.MySQL在进行索引查找比对的时候使用ASII码进行数据比对,如果使用UUID 作为主键,那么自增索引在查找数据的效率要比UUID要快.同时,UUID占用的磁盘空间也比自增数字占用的磁盘空间要大.
3.减少B+Tree的页分裂与合并造成的性能损耗.

Hash数据结构

在这里插入图片描述
Hash索引数据,存储数据并存储对应的数据磁盘地址.
Hash数据结构,只能对精确查询进行查询,不支持范围查询,同时也存在hash冲突问题.

联合索引的存储结构

在这里插入图片描述
联合索引的查询方式为:先比较第一个字段,然后再比较第二个字段,接着比较第三个字段

什么是最左前缀原理?

如下图,联合索引的字段为 a,b,c,d.联合索引在进行查询的时候,需要先查询a,接着到b,然后到c,最后到d.
在这里插入图片描述
如果违背最左前缀原则,那么在进行数据查询的时候就会查到异常的数据,就需要全表扫描才能找到对应的数据.
回表:是指在二级索引中查找数据,根据二级索引存储的主键索引,再到聚集索引中查找到所有数据.需要经过两个索引的查询.