深入浅出MYSQL索引实现机制

最新推荐文章于 2023-12-12 11:44:58 发布

农村外出务工男

最新推荐文章于 2023-12-12 11:44:58 发布

阅读量499

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/charry_a/article/details/89073402

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、什么是索引

提到数据库索引，我想你并不陌生，在日常工作中会经常接触到。比如我们的某一个SQL查询语句响应很慢，你可能第一反应是给这个SQL 加个索引吧，那么到底什么是索引昵，今天我们就来聊一下这个话题（本文以MYSQL 5.6 为例）。

索引是为了加速对表中数据的检索速度而创建的一种数据结构，索引是在存储引擎层实现的，在MYSQL里面，不同的存储引擎实现索引的方式不太一样，本文以主流的INNODB存储引擎为例进行说明。比如我们有一个user表，主键id对应有一个索引，形如

上图是一个主键索引和具体数据之间的映射关系。

二、索引有什么优势、劣势

首先，如果没有索引，MYSQL是如何来检索数据的昵，下面我们举一个简单的例子，比如我们要对上面的user表进行精确匹配，假设查找name="张三"的记录，

select * from user where name = "张三"

目前我们的表只有2条记录，假设这2条记录都存在某一页上，因为我们并没有对name列建立索引，MYSQL就只有挨个扫描进行数据比对，极端情况下，假设我们有成千上万的记录，这些数据存储在很多页上，如果我们要检索某行数据，我们首先需要定位到数据存储在哪一页，然后从所在的页内中查找相应的记录才进行扫描，而如果我们没有建立索引，也只能笨办法一个一个扫描，因为要遍历所有的数据页，所以这种方式显然是非常耗时的。那么，有没有一种方式能够加快数据的检索速度昵，索引闪亮登场了。

那么，使用索引有什么优势昵？首先索引可以减少存储引擎需要扫描的数据量，上面的例子已经明显感觉到，全表扫描需要扫描的数据量有多恐怖，其次，可以在一定情况下避免对数据进行排序，当我们使用order by 进行排序的时候，mysql首先会判断内存能不能装得下你要排序返回得数据，如果可以，就直接在内存里面计算不需要借助于临时表，并且，恰好如果你要排序的字段本身就是索引字段，而索引在INNODB的叶子节点存储的数据又是有序的，那么连在内存里面排序这一步都可以省掉，直接从叶子节点扫描满足的数据即可。

事物总有二面性，索引可以帮助我们更快的检索我们想要的数据，但是使用索引也是有代价的。当我们的表数据非常大的时候，一棵B+Tree所需要的空间也是非常大的，而且每一个索引就是一棵B+Tree，显然，这些数据放在内存是不现实的，索引真正落地的地方还是磁盘，这是索引空间上的代价。而当我们对某一列建立了索引之后，当我们对这一列进行修改的时候，MSQL 需要维护索引, 比如对记录的修改，可能会造成页节点的分裂，申请新的数据页等，这是索引时间上的代价。所以，索引并不是越多越好，最好结合业务合理设置索引。

三、为什么是B+Tree

索引的出现是为了提高查询效率，但是实现索引的底层数据结构却有很多种，比如哈希表，有序数组，树等，那么INNODB为什么要使用B+Tree来实现昵？

3.1 哈希表

假如我们使用哈希表来实现，哈希表是一种K-V存储数据的结构，我们输入待匹配的Key，就能找到对应的Value，当多个Key经过Hash之后出现冲突的时候，很简单，后面我跟一个链表，当我们检索一个数据的时候，首先根据检索的Key 计算其HashCode然后找到对应数组的下标，然后根据Equals进行比较，找到我们需要的数据。看起来哈希表查找数据的效率很高，但是这仅仅出现在等值查询的情况，假设我们查找user表里面，age 在【10，2000】的结果，哈希表做起来就吃力了，只能进行全表扫描。而显然，这种范围查询在我们的业务中是非常常见的。所以，哈希数据结构不合适做索引。

3.2 有序数组

假如我们用有序数组来实现，比如一个有序数组 new int [] { 1,2,3,4,8} 当我们检索 age 大于3 且小于 8的数据，首先可以根据二分法找到 3，然后向右遍历，直到数据大于8即可，看起来范围查找的效率可以达到O(LogN)，但是假设我们更新一条数据昵，比如我插入一个6，那么6之后的数据就必须往后移动一位，如果数据量非常大的化，这个代价太大了，所以，有序数组不太适合我们这种数据动态变化的情况。

3.3 二叉查找树

二叉查找树，典型的特征就是从根节点开始，它的左子树的值比根节点的键值小，右子树的值比根节点的键值大，比如下面这就是一棵二叉查找树(网址 https://www.cs.usfca.edu/~galles/visualization/BST.html)

上面是一种比较极端的情况，树型结构成线性了，假设我们需要查找id=10的记录，就需要5次IO, 这还是数据量很少的情况，而磁盘IO往往是最耗时的地方，所有，二叉查找树不适合用来做索引。

3.4 AVL 树(平衡二叉树)

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1，比如下面这棵树

如果在AVL树中进行插入或删除节点，可能导致AVL树失去平衡，AVL树失去平衡之后，可以通过旋转使其恢复平衡，这里不介绍其是如何保持平衡的，具体可以打开上面的网址进行演示。当我们要查找id=9的数据时，首先将id=7加载到内存中，发现 9 > 7 ，然后查找其右子树，发现 9 < 37，接着查找 37 的左子树，直到找到匹配的数据。对于AVL树来说，可能会存在树的高度太深，极端情况下，如果我们要查找的数据在树的最底层，那么IO次数将明显增加，这也不利用数据的检索效率的提升。

3.5 多路平衡查找B-Tree 树/B+Tree

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块，那么二叉树就不合适了，可以考虑使用多叉树。

对于B-Tree（https://www.cs.usfca.edu/~galles/visualization/BTree.html）来说，它的特点其实没有必要去死记硬背，主要核心是 B-Tree 里面每个节点中不仅包含数据的key值，还有data值。下面就是一棵B-Tree

上图中，树的每一个节点都是具体的key-value值，但是，每一个 page的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，最坏的情况是一个 page 保存一条数据，这个时候 B 树退化成二叉树；另外 B 树无法修改字段最大长度，除非调整 page 大小，重建整个数据库，为了解决这些问题，B-Tree树的增强版B+Tree出现了。

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大增加每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

B+节点关键字搜索采用闭合区间
B+非叶节点不保存数据相关信息，只保存关键字和子节点的引用
B+关键字对应的数据保存在叶子节点中
B+叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

四、MYSQL中B+Tree的体现形式

接下来我们以主流的存储引擎Innodb和Myisam为例，分别来看索引是如何体现得。

首先来看Myisam，在Myisam里面，叶子节点存储得是数据记录的指针，并没有存具体的数据，也就是说他的索引和数据是分开存储的，而对应InnoDB来说，我们假设id是某张表的主键，那么以id为索引的B+Tree长下面这样

可以看到，叶子节点存了具体的数据，而假设我们以name列建立了索引，那么这棵B+Tree长下面这样

也就是说，非主键索引的叶子节点存储了对主键索引的指针。也即当我们根据name进行查找的时候，最后都需要进行回表(回主键索引)查一次来获取具体的数据。

五、常用索引介绍

5.1 聚簇索引

“聚簇”就是索引和记录紧密在一起，分开就不是聚簇索引了，所以一张表只能有唯一的聚簇索引，比如一般我们一张表只有一个主键，假设是id,那么以id为索引，可以理解为就算一个聚簇索引。

5.2 唯一索引

唯一索引是指某几个字段合并成得索引，那么这几个字段在某张表里面如果同时出现，必须仅仅出现一次。

5.3 覆盖索引

覆盖所有是指一个索引包含所有需要查询的字段的值，就是说叶子节点的数据已经包含了我需要查询的所有数据，就没有必要再回表(回到主键索引树搜索的过程)查询一次，比如select name,age from T where name="zs";如果我建了一个(name,age)得联合索引，那么该语句不仅可以走索引，而且不用回表查，因为索引树里面已经包含了name和age，而我查询语句需要得数据就是name和age,这种就是覆盖索引。