【MySQL】索引

概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现

作用

1. 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
2. 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
3. 索引对于提高数据库的性能有很大的帮助

优势和劣势

优势 : 提高查询的速度
劣势: 占据额外的硬盘空间,可能会拖慢插入删除修改的速度

具体操作方式

创建索引

自动

在创建表时给字段加主键 外键``unique约束时回自动创建索引

1. 主键约束 : 主键约束要求数据非null且不重复 我们每插入一个数据时 数据库都要进行一次查询看是否有重复的元素 因此数据库会为我们自动创建索引来加快这种查询速度
2. 外键约束: 在向从表中插入数据时,会在主表中查询是否有从表中约束的值,此时数据库会进行一次查询操作 因此数据库会为我们自动创建索引来加快这种查询速度
3. unique约束: unique约束后要求数据不可以重复 因此每插入一次数据都会进行一次查询 看是否有重复的元素 因此数据库会为我们自动创建索引来加快这种查询速度

手动创建

create index 索引名 on 表名(列名)

查看索引

show index from 表名

删除索引

drop index 索引名 on 表名

删除索引只能删除我们手动创建的索引 数据库为我们自动生成的索引无法删除

如果对数据量很大的数据库进行插入/删除索引的操作时 可能会把数据库卡死

索引的数据结构

我们针对innodb这个存储引擎来讨论MySQL索引的数据结构

哈希表

我们在学习数据结构时 哈希表是一种非常强大的数据结构
它可以在o(1)的时间复杂度完成查找操作

但是根据哈希表的存储结构来看 他存储的数据都是分散分布在表中
如果我们只针对于查询特定的一个元素 那么哈希表无疑非常适合
但是如果我们要查找一个范围内的数据 比如说查找 小于15岁的学生 这时我们就无法快速的查找到该范围内的所有数据 因此哈希表并不适合我们索引

二叉搜索树

二叉搜索树的特点是每个节点的左树都比自身小 右节点都比自身大
但是二叉搜索树非常考验插入数据的顺序

例如 我们的数据是按照从大到小的顺序插入 此时树结构就不会出现 反之出现的是一种类似于链结构的数据结构 这样并没有优化硬盘的I/O次数 所以不用考虑

平衡二叉树

平衡二叉树在二叉搜索树的基础上 解决了我们因为插入元素顺序造成的伪树结构 使树的左右两个树的层级最多相差一 保证二叉树平衡

平衡二叉树的查找性能接近于二分查找 暑假复杂度为O(log2n)

但是又会出现问题

1. 当数据量大的时候 树的高度就会增加 而树的高度对应着每条数据查找时和硬盘的I/O次数 因此在数据量大时 查询效率会大幅降低
2. 和哈希表一样 平衡二叉树也不支持范围的快速查找 ,范围查找需要多次遍历二叉树 效率很低

B树

根据二叉树的缺陷 数据量越大 树的高度越高 那么我们有没有一种方式来降低树的高度呢?
这时就可以采用B树

B树有以下的特点:

1. 每个结点的值(索引) 都是按递增次序排列存放的，并遵循左小右大原则。

2. 根结点 的 子节点 个数为 [2，M]。

3. 除 根结点 以外 的 非叶子结点 的子节点个数 为[ Math.ceil(M/2)，M]。 Math.ceil() 为向上取整。

4. 每个 非叶子结点 的值(索引) 个数 = 子节点个数 -1 。最小为 Math.ceil(M/2)-1 最大为 M-1 个。

5. B树的所有叶子结点都位于同一层。

每个节点都可以存放多个数据 此时树的高度就会大大降低 查找效率就会变高

缺陷

B树虽然降低了树的高度 使磁盘I/O数量降低 但是B树还是没有解决范围查找的缺陷 如果要查询一个范围内的数据,每次查找还需要从根节点遍历树

B+ 树

在B树的基础上 又提出了B+树
B+树在B树的基础上 解决了范围查询的问题

如图所示 B+树有以下特点

1. 只有叶子节点储存数据 其他节点只保存索引
2. 叶子节点包含整棵树的所有元素
3. 叶子节点会被连接成双向链表

这样的话不仅继承了B树降低树的高度的优点 还能够进行范围查询

例如我们要查询年龄在18岁到25岁之间的数据,我们只需要找到18岁的数据 再找到25岁的数据 根据叶子节点的双向链表就能找到18到25岁之间的所有数据

因此数据库索引就采用了B+树

优点

1. 擅长范围查询
2. 所有的查询都落在叶子节点上 比较次数是均衡的 查询时间是稳定的
3. 叶子节点上储存了所有数据 其他节点就可以只保存索引