面试高频：MySQL索引

1 概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引， 并指定索引的类型，各类索引有各自的数据结构实现。（具体细节在后续的数据库原理课程讲解）

2 作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。

索引所起的作用类似书籍目录，可用于快速定位、检索数据。

索引对于提高数据库的性能有很大的帮助。 

3 使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

1. 数据量较大，且经常对这些列进行条件查询。
2. 该数据库表的插入操作，及对这些列的修改操作频率较低。
3. 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

4 使用

创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建 对应列的索引。

查看索引

show index from 表名;

案例：查看学生表已有的索引

show index from student;

PRIMARY 是主键自动生成的索引（不需要手动输入，只要建表的时候，指定了主键，就会自动生成主键索引）

创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名 on 表名(字段名);

案例：创建班级表中，name字段的索引

 create index idx_classes_name on classes(name);

温馨提示：

创建索引,也是一个“危险操作”

如果是针对空表,或者表中的数据比较少(几千,几万 .... )创建索引,就谈不上危险不危险.

一旦表的数据量比较大,千万级别 ..... 此时创建索引操作,就可能会触发大量的硬盘IO,直接把机器就搞的卡死住了 .....

在最初建表的时候,都要有哪些索引,提前规划好,创建好 .....

万一某个表,确实没有提前创建索引,现在又有了很多数据了,非加索引不可,此时咋办呢 ??

删除索引

drop index 索引名 on 表名;

案例：删除班级表中name字段的索引

drop index idx_classes_name on classes;

只能删除,咱们自己创建的索引.不能删除 自动生成的

删除索引,也是危险操作 !!!要能够慎重对待 ~~

5.数据库索引

二叉搜索树和哈希表,都不适合给 数据库 做索引.

1)二叉搜索树

最大的问题在于“二叉”当要保存的元素多的时候,就会使整个树的高度变的比较高~

一旦高度高了,比较次数就会变多 ~~硬盘上 ~~

2)哈希表

最大的问题在于,只能进行“相等”查询.无法进行><这样的”范围查询”,也无法进行like
模糊查询.

3）B+树

为数据库量身定做的数据结构 ~~

这里主要的目的,不是为了减少比较的次数,而是要减少 硬盘IO的次数.

非叶子节点，只需要存储key值，不需要存储数据行，消耗空间不大

1）不同于B树.
B树是有N个key,划分成N+1个区间

B+树是有N个key,划分出N个区间~

2）父节点中的key的值,会在下面的子节点中再次出现 ~~ (以子节点中的最大值的身份)

重复出现的做法,看起来好像是浪费空间
实际上非常有用 ~~

1. 快速查找：重复键可以帮助加速查找操作。由于父节点中的键是子节点中键的最大值，能够有效地引导搜索路径。

2. 范围查询：在进行范围查询时，父节点的键可以帮助快速定位到包含范围的子节点，从而加速范围查询的过程。

3. 一致性：这种做法有助于保持树的平衡和一致性，使得所有节点都能保持相同的结构，简化插入和删除操作。

虽然这种重复看起来像是浪费空间，但实际上它为高效的查找和查询操作提供了很大的帮助。

3）B+树把叶子结点,像链表一样首尾相连了 ~~
此时,进行“范围查询”就会非常方便 !!!

B+树的优势

1.N叉搜索树,高度比较低,此时硬盘IO次数就比较少.
2.叶子结点是全集,并且用链表结构连接,非常便于范围查询 ~~

3.B+树,所有的查询都是要落到叶子结点上完成的 ~~ 任何一次查询,经历的IO次数和比较次数都是差不多的,查询的开销稳定的 ~~

4.由于B+树,叶子结点是全集,非叶子节点上不必存储“数据行”,只需要存储索引列的key即可.
使得非叶子节点,消耗的空间比较少 ~~

6.面试模拟

谈谈对于数据库索引的理解 ~~

如果能连续讲30分钟,基本上就稳了 !!!
最关键的是,能否有某个点,打动面试官 ~~

1)索引是啥,解决啥问题的~

索引相当于书的目录,能够提高查询的速度

2)索引付出了什么代价
a)需要更多的存储空间
b)可能会影响增删改的效率(不是一定会影响)
整体来说,索引利大于弊,日常开发还是会经常使用的.

3)如何使用sql 操作索引,是否有注意事项

a) show index from表名;查看索引 (主键,外键,unique,会自动生成索引)

b) create index 索引名 on表名(列名)
c)drop index 索引名 on表名;

4)索引背后的数据结构=>B+树特点和优势 .-

特点:
a)N叉搜索树,每个节点上包含N个key,划分出N个区间.
b)每个父节点中的元素,都会下沉到子节点中,作为该子节点中最大值的角色来存在.
c)叶子结点这一层就构成了数据集合的全集 ~~
d)使用类似于链表这样的结构,把叶子结点串起来.

优势
a)N叉搜索树,高度比较低,降低了硬盘IO次数
b)范围查询非常方便&高效
c)所有的查询都落到叶子结点上,开销非常稳定.容易预估成本.
d)叶子结点存储数据行,非叶子节点只存储索引列的key值,非叶子节点占据空间小,可以加载到内存中
进一步的减少查询时IO的访问次数.

 

如果您觉得有失偏颇请您在评论区指正，如果您觉得不错的话留个好评再走吧！！

您的鼓励就是对我最大的支持!  ！ ！