MySql索引

小乐乐的天台

已于 2022-11-09 09:19:40 修改

阅读量381

点赞数 1

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

于 2020-07-29 00:00:16 首次发布

本文链接：https://blog.csdn.net/qq_41931364/article/details/107505174

版权

mysql 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

MySql索引

1、索引
10、辅助知识

1、索引

1.1、什么是索引

概念：索引是帮助MySql高效获取数据的排好序的数据结构。

1.2、索引的数据结构

1.2.1、二叉树

二叉树特点：节点左孩子比节点的值小，节点右孩子比节点的值大。
二叉树简介：在使用这种数据结构时，就相当于是将数据简单排了个序，可以减少查找数据的次数。
二叉树缺点：缺点就是当数据是递增或者递减的时候会出现如下图所示的情况，成为了一个链表，MySql索引底层没有用二叉树作为索引的存储结构。

在这里插入图片描述

1.2.2、红黑树

红黑树特点：在二叉树的特点上增加了一个自动平衡，也叫平衡二叉树。
红黑树简介：它会自己在插入数据的时候做一个平衡，当我们顺序插入数据1、2、3、4、5、6、7时，二叉树出现的是上图的情况，而红黑树是如下的情况。
这块对于红黑树的颜色什么的大家不要纠结，这块现在主要想表达的目的是红黑树的高度是不可控的（在这种特殊的情况下）。

在这里插入图片描述

红黑树缺点：高度不可控，当一个表的数据量非常的大的时候，树的高度就很高，查找的次数就会变多，IO就会变多，也是非常消耗性能的，所以MySql索引底层没有用红黑树作为索引的存储结构。

1.2.3、Hash

Hash结构就是当查找一条数据时，先对条件进行Hash，然后将Hash后的值全部放入内存中做比对，如下图所示，只需做一个Hash并且将Hash后的所有值进行IO放到内存中进行比对即可，所以，再某些时候Hash的效率比B+Tree高，因为有可能一次hash之后就找到了想要的数据，hash图详情如下所示：

在这里插入图片描述

1.2.4、B-Tree树

B-Tree特点：
1、树的高度是可控的，叶子节点的深度都是一致的，叶节点的指针为空。
2、所有索引元素不重复。
3、节点中的数据索引从左到右底层排序。

B-Tree简介：B-Tree树将一个树的节点存放成许多个类似于key-value形式的节点，使得树的高度可控，但节点所占用的空间很大，详情如下图所示：

在这里插入图片描述

B-Tree缺点：
1、非叶子节点也存储数据，存储的数据量有限。
2、各个叶子节点之间不能相互访问。

1.2.5、B+Tree树

B+Tree特点：
1、非叶子节点不存储数据，存储了一些冗余数据，为了构建二叉树，存放更多的数据。
2、叶子节点包含所有的所有的数据节点。
3、叶子节点有指针连接，叶子节点之间也可以互相访问。

B+Tree简介：B+Tree是B-Tree一种升级变种，这里所展示的B+Tree是Mysql特有的，它在原本的B+Tree上做了许多的优化，使得存储的数据量变大，叶子节点之间也有了指针式的访问性，这里的双箭头是MySql优化的，原本的B+Tree是单箭头，详情如下图所示：

原始的B+Tree如下图所示：

在这里插入图片描述

Mysql优化后的B+Tree

1.3、B+Tree索引

1.3.1、B+Tree在MySql中的节点默认大小

在数据库中可以通过这个Sql语句查看MySql的节点默认大小：

show global status like 'Innodb_page_size';

查询之后，大小约为16384个字节，也就是16kb，可以修改这个参数，但一般不推荐修改如图所示

在这里插入图片描述

1.3.2、一个B+Tree能放多少条数据

由于上面我们看到B+Tree一个节点默认大小为16kb，再看B+Tre e的图示，发现前两行的索引结构是这样的，先是一个索引元素，再是一个存放下一个节点的地址空间：

在这里插入图片描述

比如我们索引列用bigint存放时，一个bigint是8个字节，一个存放地址的空间大概放6字节，那么16kb/(8byte+6byte)~=1170，约等于1170，索引可以存放1170个索引元素，显而易见，下面也可以存放1170个同样的节点，最后一行的存放的是数据，一条数据可能有很多，但对于几十个、几百个字段来说的话，基本上数据大小不会大于1kb，我们现在就拿1kb来说，那么一个节点就会存16条数据，所以，累乘下来就是1170 * 1170 * 16=21902400，大约是两千多万条数据。

1.4、联合索引

1.4.1、什么是联合索引

一张表的多个字段组成的索引叫联合索引。
就比如(student_number,name,age)

1.4.2、联合索引的模样

由于它是B+Tree存储，所以它需要排好序，先从第一个字段排序，在按照第二个字段，再第三个字段，（这样排序之后会出现一个最佳左前缀的问题，详情请看下面问答）详情如下图所示：

在这里插入图片描述

1.4.3、什么是联合索引的最佳左前缀原理

由于上图我们可以看出，要想使用此索引，必须从第一个索引字段开始，不能跨字段查找，如果破坏了这个规则，将无法使用索引。举例：
如果(student_number,name,age)是一个联合索引，观察下面几条Sql语句。

// 这几个查询语句就可以使用到索引
where student_number = "111" and name = "sss"
where student_number = "111" and name = "sss" and age = 30

// 下面这几行sql语句不能走索引，因为它跳过了索引中间的属性
// 跳过了student_number列
where name = "sss" and age = 30
// 跳过了name列
where student_number = "111" and age = 30

10、辅助知识

10.1、如果没有索引

当没有索引时，在执行一条Sql语句时是对一张表的逐条语句去比对查询结果的，有如下两个缺点：
缺点1：所有的记录都会比对，比对的量比较大。
缺点2：由于一张数据表的记录在磁盘上的位置不一定是相邻的，所以要进行多次的io操作，io操作是相当消耗性能的（因为数据表中的记录可能插入一条数据之后，过了很长时间才插入的下一条记录，然而磁盘上巨鹿数据是按照磁道顺序记录的，所以数据表的记录不一定在磁盘上相邻）。

10.2、为什么MySql使用的是B+Tree而不使用B-Tree

在B-Tree中，从头节点开始就存数据，如果一条数据的大小是1kb而的话，那么头节点只能存放16条数据，设定树为3层，那么B-Tree的三层存放的数据条目为16^3=4096条，而前面我们计算过，同样为3层时，每个节点的最大值为16kb时，B+Tree树存放的数据是两千多万条，，所以，在相等条件下，B+Tree存放的数据远远大于B-Tree树，所需要的查询效率就更高，所以选择B+Tree。

10.3、聚集索引和非聚集索引

聚集索引也叫聚簇索引，非聚集索引也叫非聚簇索引。
聚集索引：索引和数据在同一个文件中。
非聚集索引：索引和数据存放在两个不同的文件中。
所以Myisam存储引擎就用的是非聚集索引，Innodb存储引擎就用的是聚集索引。
Mysql存储引擎

10.4、为什么建议Innodb表建立一个主键，并且推荐使用整型的自增主键？

10.4.1、为什么要建立主键

Innodb底层使用的是B+Tree树存储的数据，索引需要一个索引列。
如果我们建立了主键：它会直接使用主键做索引。
如果我们没有主键：存储引擎会从第一列开始找，找出一列的所有数据都不想等的那一列做主键，那么问题就来了，万一后面有新数据插入，作为索引的列又得重新找，如果没有一列的数据是唯一的，Innodb会帮助我们建立一个索引列，显然无论是啥效率都不会很高。