Mysql索引

最新推荐文章于 2024-10-01 17:33:05 发布

老皇甫

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量959

点赞数 20

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

本文链接：https://blog.csdn.net/m0_73233932/article/details/142343443

版权

Mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

(一)索引

要明白为什么会有索引，就看索引的存在带来了什么变化，我们先看普通的查询过程

（1）普通select 查询过程

1：先遍历表

2：把当前的行给带入到条件中，看条件是否成立

3：条件成立，这样的行就保留，不成立就丢弃

那么问题就来了，如果一个表的数据非常多，那么遍历表的开销是很高的，因为数据库中的数据都存储在硬盘上，所以每次遍历表，都会读取硬盘，并且我们都知道读取硬盘的速度，比读取内存的速度是要慢几个数量级的，所以如果一个表中数据众多，那么这种遍历表的查找方式，是非常低效的

(2)什么是索引

官方对索引的定义是：索引（index）是帮助Mysql高效获取数据的数据结构，当表中数据量越大，索引对性能的影响愈大，索引就类似书籍中的目录，是帮助我们提升查找数据效率的重要手段

（3）索引的类型

如果索引按照字段特性分类的话，索引可大致分为，主键索引，唯一索引，普通索引，全文索引

1：主键索引

见名知义，主键索引是在创建主键的时候，自动创建的索引，主键的唯一性和非空性，非常适合作为索引来提升查找效率

当我们将一个列设置为主键的时候，这个列就是主键索引

create table class (
id int primary key,
name varchar(10) );

2：唯一索引

与主键索引类似，是我们将列设置为唯一的时候自动创建的索引

create table class (
id int primary key,
name varchar(10) ) unique;

3:普通索引

没有唯一性约束，允许空值，是最基本的索引类型

（1）创建索引

1：create index 索引名 on 表名（列名）；

2：alter table 表名 add index 索引名(列名);

（2）查看索引

show index from 表名；

（3）删除索引

drop index 索引名 on 表名;

4：全文索引

全文索引主要针对的是文本文件，比如varchar(),text类型

创建全文索引与普通索引类似

1：CREATE FULLTEXT INDEX 索引名 ON 表名(列名);

2：ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(列名);

（二）索引机制

（1）索引文件

当我们给列添加完索引之后，mysql主要通过B树算法（有时是其他算法）来生成一个索引文件，在查询数据库时，会找到索引文件进行遍历，使用的是折半查找的方式，从而大大提升文件查找速率。

（2）索引代价

我们知道创建索引后会产生一个索引文件，它就必定会占用一定的磁盘空间，更重要的是我们在进行增删改（DML）的操作时，需要同时对索引文件进行更改，这就降低了增删改的效率。

（3）什么列不适合创建索引

上面已经说过索引会降低 DML的效率，所以第一个就是需要频繁更新的列，最开始我们又说过，当表中的数据越多对效率影响越明显，但是当表中数据很少的时候，就不仅对查找效率提升微乎其微，并且还会影响数据库的性能，索引主要目的时帮助快速定位唯一或少量重复的数据，所以若数据重复度很高，索引效果就不会很好

（三）索引底层数据结构

（1）哈希

哈希表是一种非常优秀的查询数据的结构，在等值查询中表现的非常优秀效率为O（1），但在数据库中显然仅仅是等值查询并不能满足要求

（2）二叉查找树与AVL树

二叉查找树

如果将二叉树作为存储的数据结果，那么在一个普通的情况下查找的效率是O(log(n))，如下图

但如果在极端的情况下，如果插入顺序正好是有序，可能会退化成一个链表，如下图，这时查找的效率会和链表一样是O(n)

AVL树（左右子树深度差距不能超过1）

平衡树（AVL）树可以解决二叉查找树退化成链表问题

当我们按照顺序插入1，2，3的时候，如果按照正常插入会变成右-右型，但此时左右高度差大于1，AVL树就需要进行下图的左旋操作，来使左右子树高度不超过1

左-左型同理

但此时AVL树一个结点只能存储一个元素，当元素非常多的时候，就使得要么树的度非常大（结点拥有子树个数的最大值），要么树的高度非常大，甚至两者都足够大才行，这就使得内存访问外存次数非常多，在时间效率上限制非常严重，为了解决这个问题，就引入了多路查找树的概念（打破一个结点只能存取一个元素）

（3）多路查找树（B树）

多路查找树：其每一个结点的孩子树可以多于两个，并且每一个结点处可以存储多个元素

3.1 2-3树（3阶B树）

2-3树（多路查找树的一种形式）：其每一个结点都拥有两个孩子(有两个孩子的结点叫2结点)或三个孩子（有三个孩子的叫3结点）

2结点：一个2结点包含一个元素和两个孩子或没有孩子（没有孩子就没有孩子，有孩子就必须有俩孩子），左子树包含的元素小于该元素，右子树包含的元素大于该元素

3结点：一个3结点包含一小一大两个元素和三个孩子或没有孩子（没有孩子就没有孩子，有孩子就必须有仨孩子），左子树包含的元素小于该元素，右子树包含的元素大于该元素，中间子树包含介于两元素中间的元素

2-3树示例如下图所示

元素的插入与删除

B树中元素的插入与删除非常麻烦，每插入或删除一个元素，都可能会对B树的结构进行调整，这也是为什么创建索引会降低DML的效率

下面进行一个简单的2-3树的插入和删除的示例

插入：2-3树的插入与二叉排序树类似，插入操作一定发生在叶子结点上，但与二叉排序树不同的是，2-3树元素的插入操作可能会对该树的其余结构发生连锁反应

对于空树与2结点的插入不再赘述，接下来我们讨论对3结点插入的情况（因为3结点已经是2-3树结点的最大容量，因此需要将其拆分，且将树中的两元素或插入元素的三者中选择其一向上移动一层。复杂情况也在于此）

如图需要向左下图中插入元素5。经过遍历可得到元素5比8小比4大，因此它应该是需要插入在拥有6、7元素的3结点位置。问题就在于，6和7结点已经3结点，不能再加。此时发现它的双亲结点4是个2结点，因此考虑让它升级为3结点,样它就得有三个孩子，于是就想到，将6、7结点拆分，让6与4结成3结点，将5成为它的中间孩子，将7成为它的右孩子，如右下图所示。

上图是一种情况，另外一种情况假如说要将元素插入一个3结点，但双亲结点也是3结点该怎么插入，若是双亲结点的双亲结点也是3结点，又该怎么插入。这里就不难想象B树的插入操作是非常复杂的，因此不推荐频繁更新的列作为索引来使用

删除：删除操作与插入操作类似，都可能会影响到B树的结构

如下图此时若我们删除结点1，结点4就不满足2结点的定义，此时我们需要左旋，将6称为双亲，4为6的左孩子，7为6的右孩子

上述我们只是讨论了删除结点的双亲是2结点，且拥有一个3结点右孩子的情形，还有其他情形如

1：删除双亲是2结点，它的右孩子也是2结点

2：删除结点双亲是3结点

3：删除的结点位于满二叉树上

4：删除元素位于非叶子的分支结点

......

此时我们明白B树的删除操作同样是非常复杂的，还会改变原有树的结构，这也是为什么创建索引会使DML操作效率变低的主要原因

3.2 2-3-4树（4阶B树）

2-3-4树是2-3树的一个扩展，加入了一个4结点的使用

4结点：一个4结点包含小中大3个元素和4个孩子或没有孩子（没有孩子就没有孩子，有孩子就必须有四个孩子），若结点有孩子，左子树包含小于最小元素的元素，第二子树包含大于最小元素，小于第二元素的元素，第三子树包含大于第二元素，小于最大元素的元素，右子树包含大于最大元素的元素。

2-3-4树示例如下图所示

3.3B树

B树（B-Tree）：是一种平衡的多路查找树，2-3树和2-3-4树都是B树的特例。结点最大的孩子的数目成为B树的阶，所以2-3树是3阶B树，2-3-4树是4阶B树。

B树的查询过程

还是以刚才的2-3-4树为例，如果我们要查找7，首先从外存（硬盘）中读取到根节点，3，5，8，比较后发现没有7，但在5和8之间，因此再通过a3读取外存的6，7结点，比较后查找到所需要的元素

当初我们说过，内存访问外存的次数过多，会造成时间效率上的瓶颈（因为访问外存比访问内存慢几个数量级），那么B树是怎么解决访问外存频繁的问题呢？

外存：我们的硬盘，是将所有的信息分割成大小相等的页面，每次硬盘读取的都是一个或多个完整的页面，在现代硬盘中一个页面的大小大概能达到4096（4KB）字节或更大，以提升存储效率

如果要处理的硬盘的数据量非常大，无法一次全部装入到内存中，此时我们对B树进行调整，使得B树的阶数，与硬盘存储的页面大小相匹配，假如说我们有一个B树的阶是1001，高度是2，假如它可以存储超过100万个关键字，我们只要让根节点保留在内存中，那么这个树中，我们寻找一个数据至多需要访问两次内存（高度为2）即可

如下图，如果我们想找到9495，就只需要进行两次IO即可，然后我们再想想如果我们将数据结构改为普通的二叉查找树，将会进行多少次IO

通过这种方式，在有限的内存下，我们每一次IO，都可以获取到最大数量的数据，与二叉树相比大大减少了IO的次数（因为每个结点有更多元素），从而提升了性能，可以说，多路查找树，是专门为查找而生的。

（4）B+树

4.1B树的缺陷

尽管我们前面说到了B树的许多的优势，但B树依然是有缺陷的，假如我们要中序遍历一个B树中的所有元素，还是以上面2-3-4树中的示例图为例

我们中序遍历一遍所需要的操作是，页面2—>页面1—>页面3—>页面1—>页面4—>页面1—>页面5,每次我们经过结点遍历时，都会对结点中的元素进行一次遍历（访问一次页面就比较遍历一次），假如我们要对数据库进行范围查找，难道要进行这么多次的遍历吗？显然这是非常糟糕，那么怎么样让我们遍历时每个元素只访问一次呢？