MySQL数据库索引

比眉伴天荒 2024-04-01 19:23 65阅读 0赞

##  ##

#### 文章目录 ####

*  
 *   *  一、索引的概念及作用
     *   *  概念
         *  作用
     *  二、索引的应用场景
     *  三、索引的相关语法
     *   *  1.查询索引
         *  2.创建索引
         *  3.删除索引
         *  注意
     *  四、索引背后的数据结构
     *   *  什么是B+树
         *  B+树有什么特点
         *  采用B+树结构能为索引带来什么好处
     *  五、索引的分类
     *   *  1.唯一索引（unique键对应的字段）
         *  2.主键索引（primary key 对应的字段）
         *  3.聚集索引
     *  六、索引在MySQL中使用方式
     *  参考

### 一、索引的概念及作用 ###

#### 概念 ####

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

**作动词来讲**，索引是**一种利用某种规则的数据结构与实际的数据关系加快数据查找的功能**。索引数据节点中有着实际文件的位置，原因是索引是根据特定的规则和算法构建的。

在查找的时候遵循索引的规则可以快速查找对应数据的节点，从而达到快速查找数据的效果。

其实宏观来讲，索引是具体的某项技术，在生活中索引的使用无处不在，比如说，书本中的目录，读书时的座位号，考试编号等等。只要是通过特定结构能够加快查找效率基本上就可以算是索引。

#### 作用 ####

帮助快速定位、检索数据。

### 二、索引的应用场景 ###

1.  **数据量较大**，且经常对这些列进行**条件查询**
2.  该数据库表的**插入操作**，及对这些列的**修改操作频率较低**。

不适用的情景：

1.  对于**插入、删除数据频率高**的表，不适用索引
2.  对于某列**修改频率高**的，该列不适用索引
3.  通过某列或某几列的**条件查询频率高**的，可以对这些列创建索引

说明：

1.  提高了空间的开销：虽然很多时候提高了查询效率，但同时也提高了增删改的开销，因为构造索引也就需要额外的硬盘空间来保存。
2.  多数情况下，查的概率远大于增删改的频率。当数量非常大的时候，我们可以将查询数据看成主要矛盾，增删改数据看成次要矛盾，问题的解决主要看主要矛盾。
3.  索引不是有了一定好，要付出一定代价的。具体是不是要设置索引需要看数据量。具体问题具体分析

### 三、索引的相关语法 ###

#### 1.查询索引 ####

语法：`show index from 表名;`

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-n67Biy1E-1668592666424)(F:\\typora插图\\image-20221116172613512.png)\]][img-n67Biy1E-1668592666424_F_typora_image-20221116172613512.png]

#### 2.创建索引 ####

语法：`create index 索引名 on 表名(字段名);`

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EBqiK08u-1668592666425)(F:\\typora插图\\image-20221116173006502.png)\]][img-EBqiK08u-1668592666425_F_typora_image-20221116173006502.png]

#### 3.删除索引 ####

语法：`drop index 索引名 on 表名;`

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LKu3iMKP-1668592666426)(F:\\typora插图\\image-20221116173044459.png)\]][img-LKu3iMKP-1668592666426_F_typora_image-20221116173044459.png]

#### 注意 ####

1.  对于一个表，如果没有显示定义索引。但是表中这一列有主键、唯一键、外键这一列就会自动创建索引。
2.  当数据量非常大的时候，创建索引、删除索引是危险的操作。原因是这样很容易把磁盘的IO吃满。

### 四、索引背后的数据结构 ###

不同的数据库，不同的引擎，里边存储数据的结构还有差异。这里我们介绍的是mysql中索引背后的数据结构——B+树。

#### 什么是B+树 ####

B+树是一种平衡的多路搜索树。

![\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gvIXLS7d-1668592666427)(F:\\typora插图\\B+树.png)\]][img-gvIXLS7d-1668592666427_F_typora_B_.png]

#### B+树有什么特点 ####

1.  B+树是一个N叉搜索树，每个节点都可能包含n个key，N个key划分出n个区间，最后一个key是最大值。
2.  父元素的key会在子元素中重复出现，并且是以最大值的姿态进行出现。
3.  所有元素会以叶子结点，用类似链表的方式，首尾相连。

相较于B树，B+树有一点区别就是会重复出现元素，这样的重复有什么意义呢？

答：这样的重复出现，使得叶子结点就包含了所有数据的全集。

所有非叶子结点中的值都会在叶子结点中体现出来。

#### 采用B+树结构能为索引带来什么好处 ####

1.  继承了二叉搜索树快速搜索元素的特点，同时把高度降下来后，时间复杂度也降了下来。比较的时候，硬盘IO次数随之减少，查询效率得以提高。
2.  更适合进行范围查询
3.  所有的查询，都是要落在叶子结点上，使得**查询次数比较均衡**（每个节点都是在硬盘上的，B树就存在查询次数不均匀的问题）
4.  由于所有key都会在叶子结点中体现因此非叶子结点，不必存表的真实记录。（不必都存数据行，进一步提高查询效率）【比如，我们可以存主键】

### 五、索引的分类 ###

索引的分类标准不同，分类的结果自然而然也不同。这里我们主要按照数据库功能分类。

#### 1.唯一索引（unique键对应的字段） ####

定义：唯一索引限制了在表对应的唯一索引列上的值是唯一不可重复的。

特点：

1.  一个表可以创建多个唯一索引【多，但分散】
2.  唯一索引的值允许为null
3.  允许多个列建立统一唯一索引【组团】

#### 2.主键索引（primary key 对应的字段） ####

定义：主键索引属于唯一索引的一个特殊种类，一个表的某列创建了主键索引后会具备唯一索引的功能同时还会对该列生成主键约束,所以简单来说主键索引是一种带有主键约束的唯一索引。

特点：

1.  数据库在创建主键同时会自动建立一个唯一索引
2.  每个表最多只能创建一个主键索引
3.  创建了主键索引的列不允许有重复的值，并且不能为null值
4.  创建了主键索引的列可以作为外键

#### 3.聚集索引 ####

定义：聚集索引属于聚簇索引的一个种类，在mysql中InnoDB数据引擎模式主索引就是使用的聚集索引，聚集索引的索引结构中就包含了表的数据。

特点：

1.  一个表只能创建一个聚集索引
2.  聚集索引尽量建在不会经常发生变动的列上，因为一旦列变动同时也会引索引结构变化，而索引结构中也包含者数据的变动
3.  数据库在创建主键时如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，则建立主键时候，同时建立一个唯一的聚集索引

### 六、索引在MySQL中使用方式 ###

> （1）在Mysql中索引是基于B+树的数据结构实现的;
> 
> （2）MyISAM数据引擎，表索引文件和数据文件是分离开的，主索引和辅助索引一样，只是有主键的区别，主索引的键不能重复；InnoDB中表索引本身就是表数据文件，索引节点中存的是表的数据而不是数据引用地址，辅索引完全依赖于主索引，辅索引的每次查找都是找到对应主索引的节点再找到数据;
> 
> （3）InnoDB中必须要有一个聚集索引，而聚集索引在基于主键创建的，所以InnoDB的表必须要有主键，如果没有显示指定主键，Mysql会自动创建一个隐式主键；
> 
> （4）InnoDB除了主索引（聚集索引）之外的索引都是以辅助索引的形式存在，辅助索引每次检索的时候是通过找到主索引再找到数据;

### 参考 ###

> [参考1][1]
> 
> [参考2][2]

[img-n67Biy1E-1668592666424_F_typora_image-20221116172613512.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/d28422bda8d34019be77f258207f67b4.png
[img-EBqiK08u-1668592666425_F_typora_image-20221116173006502.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/4002e97733a84c4eb4007d2aa16929b2.png
[img-LKu3iMKP-1668592666426_F_typora_image-20221116173044459.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/1ffdd76c353a43d59b82e4ac2c1fbc90.png
[img-gvIXLS7d-1668592666427_F_typora_B_.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/fe7dcf814cb940d4ae085362a2c3a8ca.png
[1]: https://zhuanlan.zhihu.com/p/27789389
[2]: http://blog.codinglabs.org/articles/theory-of-mysql-index.html