想进大厂，这些MySQL索引底层知识你必须掌握_

最新推荐文章于 2024-10-10 21:13:48 发布

2301_79987255

最新推荐文章于 2024-10-10 21:13:48 发布

阅读量795

点赞数 22

分类专栏：程序员文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2301_79987255/article/details/138411993

版权

程序员专栏收录该内容

201 篇文章 0 订阅

订阅专栏

文章详细介绍了MySQL中Memory引擎和InnoDB对哈希索引和B-Tree索引的支持，区分了聚簇索引和非聚簇索引，探讨了索引的不同类型如主键索引、普通索引和组合索引，以及为何选择B+树作为索引结构。还涉及了回表查询和覆盖索引的概念，并提到了Java进阶学习资源。

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

在MySQL中，只有Memory引擎显示支持哈希索引，这是Memory引擎的默认索引，Memory引擎同时也支持B-Tree索引，指得一提的是，Memory引擎是支持非唯一哈希索引的，如果多个列的哈希值相同，索引会以链表的方式存放多个记录指针到同一个哈希条目中。HASH时间复杂度O(1)，链表时间复杂度是O(n)

InnoDB支持Hash索引吗？

InnoDB用户无法手动创建哈希索引，这一层上说，InnoDB确实不支持哈希索引；
InnoDB会自调优(self-tuning)，如果判定建立自适应哈希索引(Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB自己会建立相关哈希索引，这一层上说，InnoDB又是支持哈希索引的；

（3）全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

2.物理存储维度

（1）聚簇索引（主键索引）：每个InnoDB表都有一个聚簇索引，聚簇索引使用B+树构建，叶子节点存储的数据是整行记录。一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下：

如果表定义了PK，则PK就是聚集索引；
如果表没有定义PK，则第一个非空unique列是聚集索引；
否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

（2）非聚簇索引（二级索引）：非聚簇索引就是以非主键创建的索引，叶子节点存储的是主键和索引列。

3.逻辑维度

（1）主键索引：主键索引一般都是在创建表的时候指定，「一个表只有一个主键索引」，特点是「唯一、非空」。

（2）普通索引：普通索引唯一的作用就是加快查询。

（3）组合索引：组合索引是创建一个「多个字段的索引」，这个概念是相对于上上面的单列索引而言，组合索引查询遵循「最左前缀原则」。

（4）唯一索引：唯一索引具有的特点就是唯一性，可以在创建表的时候指定，也可以在创建表后创建。

（5）空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

三、为什么选择B+树作为索引结构？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而偏偏是B+树呢？

1.为什么不使用哈希结构？

我们知道哈希结构，类似k-v结构，也就是，key和value是一对一关系。它用于「等值查询」还可以，但是范围查询它是无能为力的哦。

2.为什么不使用二叉树呢？

当数据量大时，树的高度会比较高（树的高度决定着它的IO操作次数，IO操作耗时大），查询会比较慢。
每个磁盘块（节点/页）保存的数据太小（IO本来是耗时操作，每次IO只能读取到一个关键字，显然不合适）
如果二叉树特殊化为一个链表，相当于全表扫描

没有很好的利用操作磁盘IO的数据交换特性，也没有利用好磁盘IO的预读能力（空间局部性原理），从而带来频繁的IO操作。

在这里插入图片描述

3.为什么不使用B树呢？

B树的搜索：从根节点开始，对节点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子节点；重复，直到所对应的儿子指针为空,或已经是叶子节点。 关键字集合分布在整颗树中 ，即叶子节点和非叶子节点都存放数据，搜索可能在非叶子节点结束。其搜索性能等价于在关键字全集内做一次二分查找。

在这里插入图片描述

假设检索26，先把磁盘块1加载到内存中，然后26与28和46比较，26比28小，然后基于P1子节点引用，P1是指向磁盘块2的一个指针地址，基于P1引用可以通过顺序IO快速加载磁盘块2，然后26与19和23比，26大于23，通过P3子节点引用，加载磁盘块7。然后命中，基于节点数据区加载数据。

B树的特点：

不再是二叉搜索，而是m叉搜索；
叶子节点，非叶子节点，都存储数据；
中序遍历，可以获得所有节点；

名词解释：

局部性原理：软件设计要尽量遵循 “数据读取集中”与“使用到一个数据，大概率会使用其附近的数据”，这样磁盘预读能充分提高磁盘IO；

磁盘预读能力：磁盘读写并不是按需读取，而是按页预读，一次会读一页的数据，每次加载更多的数据，如果未来要读取的数据就在这一页中，可以避免未来的磁盘IO，提高效率；

数据交换特性：操作系统去硬盘读取一次，做一次I/O交换，一次交换数据是4k（Linux默认页大小），交换单位以页为单位，1页就是4k（索引按数据页为单位读写的，在InnoDB中，每个数据页的大小默认是16KB）

3.为什么使用B+树？

它是B-Tree数的变体，也是一种多路搜索树B+Tree和B-Tree基本相同，区别在于B-Tree树非叶子节点和叶子节点都可以存放数据，而B+Tree树关键字存储在叶子节点上，非叶子节点不存真正的数据。（B+树中根到每一个节点的路径长度一样，因此查询速度更稳定；而B树不是这样）

叶子之间，增加了链表，获取所有节点，不再需要中序遍历，直接遍历叶子节点就行；

在这里插入图片描述

比如查找28，其实图顶端的28是索引，并不是真实数据，他会继续往下找。

B+Tree与B-Tree比较

①B+Tree范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯；

②B+Tree磁盘读写能力更强（叶子节点不保存真实数据，因此一个磁盘块能保存的关键字更多，因此每次加载的关键字越多）

③B+Tree扫表和扫库能力更强（B-Tree树需要扫描整颗树，B+Tree树只需要扫描叶子节点）

四、B+树索引搜索过程

准备数据：

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into employee values(1,'小红',43,'2021-01-20','0');
insert into employee values(2,'铁蛋',48,'2021-01-21','0');
insert into employee values(3,'张三',36,'2020-01-21','1');
insert into employee values(4,'李四',32,'2020-01-21','0');
insert into employee values(5,'王老五',37,'2020-01-21','1');
insert into employee values(6,'赵六',49,'2021-01-21','0');
insert into employee values(7,'小丑',28,'2021-01-21','1');

索引结构图：
在这里插入图片描述

（1）非聚簇索引（二级索引）