高级篇六、索引的数据结构

坚持每天学习一点

已于 2024-01-03 22:34:38 修改

阅读量376

点赞数

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

于 2023-04-15 10:44:19 首次发布

本文链接：https://blog.csdn.net/hc1285653662/article/details/130148844

版权

MySQL 专栏收录该内容

27 篇文章 5 订阅

订阅专栏

文章详细介绍了索引在数据库中的作用，如提高检索效率，以及不同类型的索引，如聚簇和非聚簇索引。讨论了InnoDB和MyISAM引擎的索引方案，强调了B+Tree在减少磁盘IO次数方面的优势。此外，还提到了Hash索引的适用性和局限性，以及平衡树如AVL和B+Tree的特性。

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

1、为什么要使用索引？

提供 MySQL的检索速率！

2. 索引及其优缺点

2.1 定义

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构

索引的本质：索引是数据结构

2.2 优点

1）提高数据检索的效率，降低数据库的IO成本
2）可以加速表和表之间的连接（对于有依赖关系的子表和父表联合查询时，可以提高查询速度。）
3）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

2.3 缺点

1）创建索引和维护索引要耗费时间
2）索引需要占磁盘空间
3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

3、InnoDB中索引的推演

3.1 索引之前的查找

所有页中顺序查找，非常耗时

3.2 设计索引

3.3 常见的索引概念

索引按照物理实现方式，索引可以分为 2 种：聚簇和非聚簇索引

1、聚簇索引（主键索引）

2、非聚簇索引（二级索引、辅助索引）

4、MyISAM中的索引方案

MyISAM引擎使用 B+Tree 作为索引结构，叶子节点的data域存放的是 数据记录的地址
在这里插入图片描述

5、索引的代价

空间上的代价
每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用 16KB 的存储空间，一棵很大的B+树由许多数据页组成，那就是很大的一片存储空间。
时间上的代价
每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。

B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还
是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。

6、MySQL数据结构选择的合理性

从MySQL的角度讲，不得不考虑的一个现实问题就是磁盘IO，如果我们能让索引的数据结构尽量减少硬盘的I/O操作，所消耗的时间也就越小。可以说：磁盘的I/O次数对索引的使用效率至关重要

查找都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量比较大的时候，索引的大小有可能几个G甚至更多，为了减少索引在内存中的占用，索引都是存储在外部磁盘上的。当我们利用索引查询的时候，不可能将整个索引全部加载到内存，只能逐一加载，那么MySQL衡量查询效率的标准就是磁盘的IO次数

6.1 全表遍历

6.2 Hash结构

在这里插入图片描述
上图中哈希函数h有可能将两个不同的关键字映射到相同的位置，这叫做碰撞，在数据库中一般采用链接法来解决。在链接法中，将散列到同一槽位的元素放在一个链表中，如下图所示：

Hash结构效率高，那为什么索引结构要设计成树型呢？
在这里插入图片描述

Hash索引的适用性：
InnoDB本身不支持Hash索引，但是提供自适应的Hash索引。采用自适应 Hash 索引目的是方便根据 SQL 的查询条件加速定位到叶子节点，特别是当 B+ 树比较深的时候，通过自适应 Hash 索引可以明显提高数据的检索效率。

6.3 二叉搜索树

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。
在这里插入图片描述

为了提高查询效率，就需要减少磁盘IO数。为了减少磁盘IO的次数，就需要尽量降低树的高度，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好。

6.4 AVL树（平衡二叉树）

左右子树的高度差不能超过 1
在这里插入图片描述

6.5 B-Tree

B树的英文是Balance Tree，也就是多路平衡查找树，它的高度远小于平衡二叉树的高度

B 树的结构如下图所示：
在这里插入图片描述
一个 M 阶的 B 树（M>2）有以下的特性：

根节点的儿子数的范围是 [2,M]。
每个中间节点包含 k-1 个关键字和 k 个孩子，孩子的数量 = 关键字的数量 +1，k 的取值范围为[ceil(M/2), M]。
叶子节点包括 k-1 个关键字（叶子节点没有孩子），k 的取值范围为 [ceil(M/2), M]。
假设中间节点节点的关键字为：Key[1], Key[2], …, Key[k-1]，且关键字按照升序排序，即 Key[i]<Key[i+1]。此时 k-1 个关键字相当于划分了 k 个范围，也就是对应着 k 个指针，即为：P[1], P[2], …,P[k]，其中 P[1] 指向关键字小于 Key[1] 的子树，P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树，P[k]指向关键字大于 Key[k-1] 的子树。
所有叶子节点位于同一层。