MySQL 索引原理详解

多云&秋雨

已于 2024-03-19 14:23:28 修改

阅读量1k

点赞数 3

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

于 2022-08-01 00:30:41 首次发布

本文链接：https://blog.csdn.net/weixin_41716183/article/details/126089315

版权

MySql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、索引是什么？

1.1 索引是什么

当一张表有 500 万条数据，在没有索引的 name 字段上执行一个查询

select * from user_innodb where name ='jim';

如果 name 字段上面有索引呢？

添加索引：

ALTER TABLE user_innodb DROP INDEX idx_name;
 ALTER TABLE user_innodb ADD INDEX idx_name (name);

索引的创建是需要消耗时间的。
有索引的查询和没有索引的查询相比，效率相差几十倍。
索引到底是什么呢？为什么可以对我们的查询产生这么大的影响？创建索引的时候做了什么事情？

1.1.1.索引图解

定义：数据库索引，是数据库管理系统（DBMS）中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
在这里插入图片描述

数据是以文件的形式存放在磁盘上面的，每一行数据都有它的磁盘地址。如果没有索引的话，我们要从 500 万行数据里面检索一条数据，只能依次遍历这张表的全部数据（循环调用存储引擎的读取下一行数据的接口），直到找到这条数据。

但是我们有了索引之后，只需要在索引里面去检索这条数据就行了，因为它是一种特殊的专门用来快速检索的数据结构，我们找到数据存放的磁盘地址以后，就可以拿到数据了。

这个很容易理解，就像我们从一本 500 页的书里面去找特定的一小节的内容，肯定不可能从第一页开始翻。

这本书会有专门的目录，它可能只有几页的内容，它是按页码来组织的，可以根据拼音或者偏旁部首来查找，我们只要确定内容对应的页码，就能很快地找到我们想要的内容。

1.1.2.索引类型

那在数据表上面，怎么创建一个索引？建表的时候指定，或者 alter table，也可以使用工具
在这里插入图片描述
第一个是索引的名称，第二个是索引的列，比如我们是要对 id 创建索引还是对 name创建索引。后面两个很重要，一个叫索引类型
在 InnoDB 中，索引类型有三种，普通索引、唯一索引（主键索引是特殊的唯一索引）、全文索引。

普通（Normal）：也叫非唯一索引，是最普通的索引，没有任何的限制。
唯一（Unique）：唯一索引要求键值不能重复。另外需要注意的是，主键索引是一种特殊的唯一索引，它还多了一个限制条件，要求键值不能为空。主键索引用 primay key创建。
全文（Fulltext）：针对比较大的数据，比如我们存放的是消息内容，有几 KB 的数据的这种情况，如果要解决 like 查询效率低的问题，可以创建全文索引。只有文本类型的字段才可以创建全文索引，比如 char、varchar、text。

create table m3 ( name varchar(50), fulltext index(name) );
select * from fulltext_test where match(content) against('索引原理' IN NATURAL LANGUAGE MODE);

在 5.6 的版本之后，MyISAM 和 InnoDB 都支持全文索引。但是 MySQL 自带的全文索引功能使用限制还是比较多，建议用其他的搜索引擎方案（比如ElasticSearch）。

二、索引存储模型推演

我们说索引是一种数据结构，那么它到底应该选择一种什么数据结构，才能实现数据的高效检索呢？

2.1 二分查找

很火的猜数字游戏，猜现在是100以内的几，最后通过不断缩小范围，锁定数字

这个就是二分查找的一种思想，也叫折半查找，每一次，我们都把候选数据缩小了一半。如果数据已经排过序的话，这种方式效率比较高
所以可以考虑用有序数组作为索引的数据结构。
有序数组的等值查询和比较查询效率非常高，但是更新数据的时候会出现一个问题，可能要挪动大量的数据（改变 index），所以只适合存储静态的数据。
为了支持频繁的修改，比如插入数据，我们需要采用链表。链表的话，如果是单链表，它的查找效率还是不够高。
所以，有没有可以使用二分查找的链表呢？
为了解决这个问题，BST（Binary Search Tree）也就是我们所说的二叉查找树诞生了

2.2 二叉查找树(BST Binary Search Tree)

特点：左子树所有的节点都小于父节点，右子树所有的节点都大于父节点。投影到平面以后，就是一个有序的线性表。
在这里插入图片描述二叉查找树既能够实现快速查找，又能够实现快速插入
缺点：就是它的查找耗时是和这棵树的深度相关的，在最坏的情况下时间复杂度会退化成O(n)
什么情况是最坏的情况呢？
还是刚才的这一批数字，如果我们插入的数据刚好是有序的，5、7、12、14、17、 25。
它会变成链表（我们把这种树叫做“斜树”），这种情况下不能达到加快检索速度的目的，和顺序查找效率是没有区别的。
在这里插入图片描述
因为左右子树深度差太大，这棵树的左子树根本没有节点——也就是它不够平衡。
所以有了我们后来的平衡二叉树，叫做 Balanced binary search trees，或者 AVL 树（AVL 是发明这个数据结构的人的名字缩写）。

2.3 平衡二叉树（AVL Tree）（左旋、右旋）

特点：左右子树深度差绝对值不能超过 1。
在这里插入图片描述 那它的平衡是怎么做到的呢？怎么保证左右子树的深度差不能超过 1 呢？
注意看：当我们插入了 5、7 之后，如果按照二叉查找树的定义，14 肯定是要在 7 的右边的，这个时候根节点 1 的右节点深度会变成 2，但是左节点的深度是 0，因为它没有子节点，所以就会违反平衡二叉树的定义。
那应该怎么办呢？因为它是右节点下面接一个右节点，右-右型，所以这个时候我们要把 7提上去，
这个操作叫做左旋。
在这里插入图片描述
同样的，如果我们插入 14、7、5，这个时候会变成左左型，就会发生右旋操作，把 7提上去。

所以为了保持平衡，AVL 树在插入和更新数据的时候执行了一系列的计算和调整的操作。平衡的问题我们解决了，那么平衡二叉树作为索引怎么查询数据？
在平衡二叉树中，一个节点，它的大小是一个固定的单位，作为索引应该存储什么内容？
它应该存储三块的内容：
**第一个是索引的键值。**比如我们在 id 上面创建了一个索引，我在用 where id =1 的条件查询的时候就会找到索引里面的 id 的这个键值。
第二个是数据的磁盘地址，因为索引的作用就是去查找数据的存放的地址。
第三个左子节点和右子节点的引用，因为是二叉树，它必须还要有左子节点和右子节点的引用，这样我们才能找到下一个节点。比如大于 26 的时候，走右边，到下一个树的节点，继续判断。
在这里插入图片描述
如果是这样存储数据的话，我们来看一下会有什么问题。
首先，对于 InnoDB 来说，索引的数据，是放在硬盘上的。查看数据和索引的大小：

select CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS data_len, CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024),2),'MB') as index_len from information_schema.TABLES where table_schema='yteaher' and table_name='user_innodb';

当我们用树的结构来存储索引的时候，因为拿到一块数据就要在 Server 层比较是不是需要的数据，如果不是的话就要再读一次磁盘。
访问一个节点就要跟磁盘之间发生一次 I/O。InnoDB 操作磁盘的最小的单位是一页（或者叫一个磁
盘块），大小是 16K(16384 字节), 那么，一个树的节点就是 16K 的大小。

缺点：如果我们一个节点只存一个键值+数据+引用，例如整形的字段，可能只用了十几个或者几十个字
节，它远远达不到 16384 字节的容量，所以访问一个树节点，进行一次 IO 的时候，浪费了大量的空
间。
所以如果每个节点存储的数据太少，从索引中找到我们需要的数据，就要访问更多的节点，意味着跟磁盘交互次数就会过多，消耗的时间也越多。
在这里插入图片描述
比如上面这张图，我们一张表里面有 6 条数据，当我们查询 id=66 的时候，要查询两个子节点，就
需要跟磁盘交互 3 次，如果我们有几百万的数据呢？这个时间更加难以估计。
所以解决方案是什么呢？
第一个，就是让每个节点存储更多的数据。这样的话，就会极大地降低树的深度。我们的树就从原来的高瘦高瘦的样子，变成了矮胖矮胖的样子。
这个时候，我们的树就不再是二叉了，而是多叉，或者叫做多路。

2.4. 多路平衡查找树（B Tree）（分裂、合并）

Balanced Tree
这个就是我们的多路平衡查找树，叫做 B Tree（B 代表平衡）。
跟 AVL 树一样，B 树在枝节点和叶子节点存储键值、数据地址、节点引用。
它有一个特点：分叉数（路数）永远比关键字数多 1。比如我们画的这棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点。
在这里插入图片描述
B Tree 的查找规则是什么样的呢？

只用了 3 次 IO，这个是不是比 AVL 树效率更高呢？
那 B Tree 又是怎么实现一个节点存储多个关键字，还保持平衡的呢？跟 AVL 树有什么区别？

一个好玩的

比如 Max Degree（路数）是 3 的时候，我们插入数据 1、2、3，在插入 3 的时候，本来应该在第一个磁盘块，但是如果一个节点有三个关键字的时候，意味着有 4 个指针，子节点会变成 4 路，所以这个时候必须进行分裂（其实就是 B+Tree）。把中间的数据 2提上去，把 1 和 3 变成 2 的子节点。如果删除节点，会有相反的合并的操作。

注意这里是分裂和合并，跟 AVL 树的左旋和右旋是不一样的。
我们继续插入 4 和 5，B Tree 又会出现分裂和合并的操作。
在这里插入图片描述
从这个里面也能看到，在更新索引的时候会有大量的索引的结构的调整，所以解释了为什么不要在
频繁更新的列上建索引，或者为什么不要更新主键。
节点的分裂和合并，其实就是 InnoDB 页（page）的分裂和合并

2.5 B+树（加强版多路平衡查找树）

B Tree 的效率已经很高了，为什么 MySQL 还要对 B Tree 进行改良，最终使用了B+Tree 呢？
总体上来说，这个 B 树的改良版本解决的问题比 B Tree 更全面。
我们来看一下 InnoDB 里面的 B+树的存储结构：
在这里插入图片描述
MySQL 中的 B+Tree 有三个特点：
1、它的关键字的数量是跟路数相等的；
2、B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据。
目前的认知：我们这要存放的数据是什么？是不是真实数据的地址？
搜索到关键字不会直接返回，会到最后一层的叶子节点。比如我们搜索 id=28，虽然在第一层直接
命中了，但是数据地址在叶子节点上面，所以我还要继续往下搜索，一直到叶子节点。
3、B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个
叶子节点的第一个数据，形成了一个有序链表的结构。

InnoDB 中的 B+Tree 这种特点带来的优势：
1：它是 B Tree 的变种，B Tree 能解决的问题，它都能解决。B Tree 解决的两大问题是什么？（每个节点存储更多关键字；路数更多）
2：扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据）
3： B+Tree 的磁盘读写能力相对于 B Tree 来说更强（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
4：排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
5：效率更加稳定（B+Tree 永远是在叶子节点拿到数据，所以 IO 次数是稳定的）

2.6. 索引方式：真的是用的 B+Tree 吗？

在 Navicat 的工具中，创建索引，索引方式有两种。
HASH：以 KV 的形式检索数据，也就是说，它会根据索引字段生成哈希码和指针，指针指向数据。
在这里插入图片描述
哈希索引特点：
1：它的时间复杂度是 O(1)，查询速度比较快。但是哈希索引里面的数据不是按顺序存储的，所以不能用于排序。
2：我们在查询数据的时候要根据键值计算哈希码，所以它只能支持等值查询（= IN），不支持范围查询（> < >= <= between and）。
3：如果字段重复值很多的时候，会出现大量的哈希冲突（采用拉链法解决），效率会降低。需要注意的是，在 InnoDB 中，不能显示地创建一个哈希索引（所谓的支持哈希索引指的是Adaptive Hash Index).
memory 存储引擎可以使用 Hash 索引。

如果说面试的时候问到了为什么不用红黑树？
红黑树的种种约束保证的是什么？最长路径不超过最短路径的二倍。不太适合于数据库索引。适合
内存的数据机构，例如实现一致性哈希。
因为B Tree 和B+Tree 的特性，它们广泛地用在文件系统和数据库中，例如Windows的 HPFS 文件
系统，Oracel、MySQL、SQLServer 数据库。