Lesson 6:MySQL索引和事务

最新推荐文章于 2024-05-22 10:06:59 发布

刘减减

最新推荐文章于 2024-05-22 10:06:59 发布

阅读量948

点赞数 4

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

本文链接：https://blog.csdn.net/weixin_44258092/article/details/122132071

版权

数据库专栏收录该内容

8 篇文章 1 订阅

订阅专栏

2.1 二叉搜索树、AVL数、红黑树和hash表回顾

2.2 索引的数据结构为什么不是二叉搜索树、AVL数、红黑树和hash表

一、索引相关概念

1.1 索引概念

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

1.2 索引作用

索引类似于书的目录，可以快速定位和检索数据。

1.3 索引使用场景

①数据量较大，且经常对这些列进行条件查询。

②该数据库的插入、修改操作频率较低。

对于插入、删除操作也需要同步修改或调整索引，索引反而会拖慢速度，因此，查找操作多适合使用索引，增加删除修改操作很多，不太适合使用索引。

③索引会占用额外的磁盘空间。

因为构造索引也会占用磁盘空间，因此，如果磁盘空间紧张，不太适合使用索引。

④ 建立索引，是通过指定某个列来建立的。要求这个列的区分度较大，如自增主键这种，就适合做索引，类似于性别这样的列，就不适合做索引。

二、索引中常用的数据结构

2.1 二叉搜索树、AVL数、红黑树和hash表回顾

为了提高查询效率，提出了二叉搜索树，由于二叉搜索树会存在“单枝树”这种情况，查找的时间复杂度是0(n)。

为了避免出现“单枝树”这种情况，对二叉搜索树进行了改进，引入了AVL树。AVL树本质上是一棵二叉平衡搜索树，对于这棵树的任意节点来说，|左子树高度-右子树高度|<=1，避免出现“单枝树”的情况，因此就能保证查找的效率，此时查找的时间复杂度是O(log(N))。这样做也引入了新的问题，随着插入/删除元素的进行，AVL树的规则就可能会被破坏掉，就需要随时调整树的结构，保证这个树始终符合AVL树的要求，这样会降低增加和删除的效率。

为了改进AVL树“查找、插入、删除”效率不均衡的问题，引入了红黑树，红黑树放松了AVL树的规则，从而就能避免触发调整的情况没那么频繁。虽然查找速度比AVL树慢一点，但是总体差异不大，同时可以保证插入和删除效率更高。

哈希表的插入、查找、删除时间复杂度是O(1)，主要是借助了数组取下标的“随机访问”能力。但哈希表会存在哈希冲突问题，解决方式是：①挂链表的方式。②往后找一个空位的方式/线性探测/二次探测。

2.2 索引的数据结构为什么不是二叉搜索树、AVL数、红黑树和hash表

①hash表为什么不可以？

采用hash表会提高查找的速度。由于哈希表只能比较相等，SQL中存在很多的>、<、<=等一些比较，所以使用hash会有很大的局限性。

②二叉搜索树、AVL数、红黑树为什么不可以？

二叉搜索树、AVL数、红黑树的查找效率主要由树的高度决定，由于这些树都是“二叉”的，当元素增加到很多时，高度也会随之增加。但与hash表相比，这些树都能进行范围比较。

2.3 B树和B+树

为了减少树的高度，提出了N叉搜索树。对于N叉搜索树，有两个核心的操作，节点的拆分和节点的合并。N叉搜索树会在某个节点值多的情况下分成多个节点，如果某个节点的值较少，也会和其他节点合并，同时调整树的高度，结构调整非常灵活，可以保证树的高度是均匀分布的。

2.3.1 B树(N叉搜索树)

从上图可以看出B图的特点：

①是一个N叉搜索树，每个节点都包含多个子树。

②每个节点都存在多个值。

③存在于二叉搜索树类似的特点，即左子树的值<根节点<右子树的值。

若是索引采用B树作为数据结构，就得保证所有的节点都能在硬盘里存的下。

2.3.2 B+树

与B树相比，B+树存在两个很明显的特点：

①非叶子节点的值可能会存在重复。正是这一点可以保证在叶子节点这一层，就是完整的数据集合。

②类似于链表的方式，把所有的叶子节点按照顺序连接了起来。

2.3.3 B+树的优点

①非常善于范围查找。

②所有的查询都落在叶子节点上，查找速度比较稳定。

③由于叶子节点是数据的全集，因此就可以把叶子节点存在硬盘上，非叶子节点直接存到内存中，进一步的降低了读取硬盘的速度。

2.4 sql底层如何组织数据

执行create index命令，mysql底层就自动帮我们创建B+树。

	聚簇索引	非聚簇索引
特点	数据本身就是通过B+树的方式组织的，每个叶子节点都直接存一条完整的记录。	先通过类似于表的结构存储所有的数据，B+树的叶子节点存储的是表的行号。
优点	更高效	产生的磁盘碎片更少