MySQL引擎——从B树到InnoDB、MyISAM全面分析

最新推荐文章于 2023-06-17 08:42:00 发布

山鸡哥w

最新推荐文章于 2023-06-17 08:42:00 发布

阅读量385

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_34528297/article/details/108016521

版权

mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文深入解析了MySQL的存储引擎，重点介绍了B树的不同变体，包括B树、B-树、B+树和B*树的特性与区别。此外，还讲解了操作系统中的局部性原理和磁盘预读策略。接着，对比分析了MyISAM和InnoDB引擎的存储方式和索引结构，强调了InnoDB的聚簇索引和二级索引以及回表查询概念。最后，讨论了各种类型的索引，如主键、唯一索引、普通索引、组合索引和全文索引，以及如何进行索引优化。

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

这里写目录标题

了解MySQL的存储引擎才能知道MySQL的存储原理、索引原理，知其然需知其所以然。

1. 树

深入学习之前先了解一下B树 B-树 B+树 B*树。

1.1B树

B树其实就是二叉搜索树，每个非叶子结点最多只有两个子节点。右子节点比父节点大，左子节点比父节点小。

搜索原理：从根节点开始，如果比根节点大则进入右子节点查询，如果比根节点小则进入左子节点查询。循环操作直至找到对应数据。
二叉搜索树是不平衡的算法：树的高度太高时，相当于线性的，降低树的高度可以

在这里插入图片描述

1.2 B-树

为了减少树的高度提高查询的效率，B-树是一种多路搜索树，不是二叉树。
在这里插入图片描述

所有的非叶子结点最多有M个儿子（且M>2）。
根结点的儿子数为[2, M]，其它非叶子结点的儿子数为[M/2, M]。
每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）。
非叶子结点的关键字个数=指向儿子的指针个数-1。
非叶子结点的关键字中从左到右由大到小排序。即A[1]<A[2]<A[3],…,A[k-1]<A[k]。
非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])范围的子树，最后一个指针P[M]指向大于随后一个关键字A[M-1]范围的值。
关键字集合分布在整颗树中，并且只会在节点中出现一次。
搜索可能在非子叶节点或者子叶节点结束，即非子叶节点也存储数据的身，这个与B+树有根本区别。
所有叶子结点位于同一层。

特点：

关键字集合分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
其搜索性能等价于在关键字全集内做一次二分查找；
自动层次控制；

1.3 B+树

它是B-树的变体，也是多路搜索树。

在这里插入图片描述

其定义基本与B-树同，除了：
非叶子结点的子树指针与关键字个数相同；
非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树
（B-树是开区间）；
为所有叶子结点增加一个链指针；
所有关键字都在叶子结点出现；

特点

所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
不可能在非叶子结点命中；
非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
更适合文件索引系统

1.4 B*树

是B+树的变体，在B+树的非根和非叶子结点再增加指向兄弟的指针

在这里插入图片描述

1.5 小结

B-树:多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

2. 操作系统相关知识

局部性原理：当一个数据被用到的时候，其附近的数据也很大概率会马上被用到。
磁盘预读：根据局部性原理，在读取磁盘数据的时候通常会进行预读，即把一页的数据都读取加载到内存中。
一页数据一般为4K的整数倍，页是计算机管理存储器的逻辑块，时下主流操作系统中一页的大小通常为4K，主存与磁盘进行数据交换的以页为单位。
在B+树的结构中，只在叶子节点存储数据，在非叶子节点中只存储的索引，在非叶子节点中可以有更大的空间储存更多的索引，这样B+树的出度d就可以大大的增加，从而降低的B+树的高度h，B树中一个节点的大小为一个page的大小，也就是一次IO的读取，h越小IO的次数就可以减少：

3. MyISAM和InnoDB

MySQL中有MyISAM和InnoDB两个存储引擎:

MyISAM引擎：在MyISAM储存引擎中，数据和索引文件是分开储存的，Myisam 的存储文件有三个，后缀名分别是 .frm、.MYD、MYI，其中 .frm 是表的定义文件，.MYD 是数据文件，.MYI 是索引文件。

MyISAM只支持表锁且不支持事务。

MyISAM将索引单独成一个文件，所以读取数据方面的性能更高，索引中存储的是行数据的地址，在索引中查询到对应的数据地址后，还需要根据地址去读取该行数据。(两次查询先索引再根据索引查数据)
InnoDB引擎：在InnoDB中，数据和索引文件是合起来储存的，InnoDB 的存储文件有两个，后缀名分别是 .frm 和 .idb，其中 .frm 是表的定义文件，而 idb 是数据文件。

与MyISAM有明显的区别，在InnoDB实现的索引结构中，索引文件和数据文件是一起的，InnoDB中索引文件中的key就是数据表中的主键索引，因此InnoDB的索引文件也是主索引文件

4. 索引

索引：索引是一种数据结构，为了提高MySQL查询效率的数据结构
主键索引（private key)：主键索引一般都是在创建表的时候回指定，主键具有唯一性以及非空，是InnoDB存储引擎中最常见的索引类型。

如果创建表时候没有创建主键索引，Mysql会查看表中是否有唯一索引，如果有，则会指定非空的唯一索引为主键索引。如果没有，就会生成一个默认的自动增长主键作为主键索引，该主键占据6byte空空间。可以通过select _rowid from tablename 查询对应的主键值。
唯一索引（unique）：唯一索引具有唯一性，可以再创建表的时候指定也可以创建表后再创建，它和主键索引唯一的区别就是唯一索引允许为空，唯一索引更多的是用来保证数据的唯一性。
普通索引（index）：普通索引唯一的作用就是加快查询。
组合索引（index）：一个多字段的索引，遵循最左前缀原则。用多个字段创建一个索引，能够避免回表查询，相对

对比单列索引，组合索引查询效率更高的，但是相对的更占存储空间。

回表查询：InnoDB存储引擎中索引分为聚簇索引和二级索引，主键索引是聚簇索引，其他索引是二级索引。

聚簇索引找那个的叶子结点保存着完整的数据行，通过聚簇索引找到对应的主键时也找到了对应的完整数据，此时可以直接读取需要的数据。

而二级索引的叶子结点并没有保存完整的数据行，它保存的是对应的主键值，通过二级索引查询到主键的值，根据主键的值去查询一次表才能拿到数据（相当于再执行一次聚簇索引查询），这个过程就叫回表查询。两次查询过程将大大降低查询效率。

为了提高效率，可以建立组合索引进行索引覆盖。如上所说，二级索引的叶子结点不包括完整的数据行只包括主键，但是我们查询表的时候往往不需要完整的数据行，而是查询其中几个字段，比如：

 select name,age,sex  from studen where name = "lht";
 # 如果按照普通的索引去查找，会根据name = "lht" 查询到符合的主键，
 # 再根据主键去查询一次表，找到完整的数据行，然后读取 name age sex 等字段的数据
 # 此时我们可以增加一个组合索引：
 alter table studen add index name_age_sex (name,age,sex)

当我们再次执行这个查询操作的时候，二级索引上已经包括了需要查询的 name age sex
这三个字段的值，所以不需要进行回表查询。当然，节省的查询时间是用空间换回来的，
并且组合索引需要耗费性能去维护。

单列索引是按照索引列有序性的进行组织B+树结构的，联合索引创建的时候也是如此，根据左边的进行最开始的排序，如上面例子，先根据name再根据age最后根据sex。

全文索引（fulltext）：是针对一些大的文本字段创建的索引，也叫做全文检索。
聚簇索引和非聚簇索引：是相对于存储引擎的概念

聚簇索引顾名思义就是聚簇在一起，什么聚簇在一起？完整的行数据和索引。在叶子结点中存储的是完整的行数据

非聚簇索引的索引文件和数据问价是分开的，索引需要进行一次回表查询。