HR眷恋的数据库高频考题之一 —— 索引

07-03 1009阅读

本篇会加入个人的所谓鱼式疯言

❤️❤️❤️鱼式疯言:❤️❤️❤️此疯言非彼疯言

而是理解过并总结出来通俗易懂的大白话,

小编会尽可能的在每个概念后插入鱼式疯言,帮助大家理解的.

🤭🤭🤭可能说的不是那么严谨.但小编初心是能让更多人能接受我们这个概念！！！

前言

在前几篇文章中，我们主要讲解了关于数据表的CRUD 的各种基础操作，而本篇文章我们讲继续学习数据库的索引

索引很重要, 是我们面试中 HR经常问到的很经典``，很热门`的考题之一。

所以小伙伴们一定要好好理解消化本篇文章哦，说不定你下一次面试的时候，就会遇到这样的考题哦 💖 💖 💖 💖

一. 索引的初识

1. 索引的概念

索引的英文名： index

小伙伴们是不是很眼熟,是的，这个英文单词本质上就是我们 Java数组中常用的下标的含义。

除了这个含义，本质上我们还有一个含义就是：目录。

是的，索引的认识，本质上就是相当于我们新华词典上的目录

但是索引是针对数据库中的某一列来说的，就是说每个不同的列可以构建不同的索引，

就是我们的新华词典的目录一样，有我们的拼音目录，部首目录，生僻字目录等…

居然索引相当于目录 , 我们设想下目录的作用不就是方便我们查找吗？那么数据库索引是不是也是如此呢？？？

2. 索引的作用

索引作用就是加快我们查找速度，提高我们查找的效率。

因为索引是针对某个列来说的,所以小伙伴使用时，你要查询的哪个列要和索引对应的列相统一，才能触发索引的作用，

对于数据库本身来说，索引是有自动创建和手动创建的

. 自动创建索引

当我们创建表时，凡是以下三种情况就会创建我们的索引

主键索引

-- 创建该表
 create table if not exist   my_table (id int  primary key , name varchar(49));
-- 查看表结构
desc  my_table;
-- 查看该表的索引
show index from my_table;

像上面这样我们创建主键时，就会自动生成该字段的索引

这里的 show index from my_table;

小编在下面会在 . 如何使用 SQL 来操作索引中，给小伙伴们细细道来哦 ❣️ ❣️ ❣️ ❣️

小伙伴们只需要知道，这是我们查询表中索引的 SQL语句即可哦

unique 索引

-- 删除前面的表
 drop table if exists my_table;
#  创建该表
create table my_table ( id  int unique , name varchar(45));
-- 查看表结构
 desc my_table;
-- 查看该表中的索引
 show index from my_table;

外键索引

-- 创建父表
create table father ( father_id int primary key , name varchar(23));
-- 创建子表并进行外键连接
 create table son ( son_id  int  primary key   , name varchar(38), 
 father_id int , foreign key(father_id) references  father(father_id));
-- 查看索引
 show index from son;

有个问题想问小伙伴，为什么我们在创建主键， unique ，外键时， MySQL会自动添加索引呢 ?

我们可以回归到 unique 和主键自身的作用来看：他们有一个共同的作用就是保持该字段的数据是唯一的，

为了保证唯一性， MySQL的底层是需要进行查询，从查询中返回该数据是否唯一，居然用到了查询，那么我们就需要索引来加快我们的查询速度。

还有一个就是我们的外键，外键的作用就是保证子表的该字段数据在父表的范围中，父表的该外键字段数据必须含有子表的数据范围

当我们建立好外键连接后

如果需要对子表的数据进行增加和修改，MySQL自身就会查询一遍父表，看子表要新添加的数据在子表中是否存在，存在就添加，不存在就无法添加。

同样当我们需要对父表的数据进行删除和修改时，我们就要查询子表的数据，如果子表中含有该数据，我们就不能对父表和子表中都含有的数据进行删除和修改

居然都要涉及到查询，所以我们MySQL就会对外键自动添加索引，以此来加快查询速度。

关于程序员自身如何手动创建索引，小编在这里就不剧透哦，在如何使用SQL 来操作索引会详细解释哦 💞 💞 💞 💞

鱼式疯言

关于自动创建索引，小编有两点想和小伙伴分享：

索引是针对表中某一个字段而言的，当我们需要对某个字段查询时要和对应字段索引相统一

像我们主键，外键， unique 修饰的字段，他们都涉及到查询， MySQL都会自动创建索引益于查询速度的加快。

MySQL 自动创建的索引是不允许删除的，小编下面也会细细讲解哦

二. 索引的不足

世间万物,凡事都有好的一面和不好的一面，居然索引能加快我们的查询速度，必然也要付出一定的代价

不足一

创建索引时，需要占用额外的空间.

这就好比我们的新华词典中的目录一样，我们目录也是占用纸张页数的

不足二

可能会加快数据表的增删改操作的执行速度。

可能会减慢数据表的增删改操作的执行速度。

也可能不会影响数据表的增删改的操作的执行速度。

鱼式疯言

小编在这里解释下哈 💥 💥 💥 💥

delete from 表名  set 列名=新数据 where 条件 ;

像我们这里涉及到查询操作的时候，就会加快执行速度
像有些删除或者修改了数据之后，我们对应的索引就需要调整 , 就有可能会触发多次硬盘IO , 减慢执行速度。
也有可能和我们索引无关的，就不影响我们的执行速度

三. 如何使用SQL 来操作索引

1. 查看当前索引

. SQL语句
```
show index from 表名 ;
```
. 语句分析

查询索引的关键标识是 show index 后面再配上表名即可。

. 注意事项

虽然和我们查询数据库和数据表中的 show 很像，但是我们需要注意一点是查询当前 数据库和数据表 是需要再后面加上 S 的，而我们的索引是不需要的。

2. 创建索引

在上面的内容中，小编主要讲解了数据表中自动创建索引的内容，而这里要重点我们程序员本身用 SQL 手动创建索引的方式

. SQL语句
```
create index 索引名  on 表名(列名); 
```
. 语句分析

需要创建索引时，标识关键字 create index 跟上索引名，并用 on 来连接。

这和我们创建表和创建数据库不同的是，因为我们的索引是针对列而已的，所以我们需要用 on 指定对应表中的列来创建我们的索引。

. 注意事项

对于索引本身来说是利大于弊的，但是对于手动创建索引来说，其实有些情况是一种危险的操作。

当数据表是空表或者是只有几千行几万行数据时，我们再创建索引的影响不大的。

但是当我们数据表中存放上百万行，千万行数据时，如果再去创建索引时，就会触发硬盘上大量的 IO ，这样会导致整个数据库直接挂掉。

这样的操作就相当于给正在飞的飞机换发动机，这是一个非常危险的操作。

所以当我们需要在数据表中存储的数据规模非常大的时候，就需要对数据库的用哪些列创建索引，有一个具体的规划和预测，以免出现大量数据，当创建数据库就会触发大量硬盘 IO 。

3. 删除索引

. 语句分析
```
drop index 索引名 on 表名;
```
. 语句分析

删除索引的标志关键字就是 drop index ，因为索引名不同表的可能一样，所以我们需要注意后面用 on 来指定指定对应的表

. 注意事项

这里需要注意的一点就是：
1. 小伙伴们手动自己创建的索引是可以删除的，但是 MySQL自动生成的索引是无法删除的。
1. 删除索引也是一个非常危险的操作，和我们上面的创建索引一样，当数据规模很大时，这种操作是相当于给正在飞的飞机上拆发动机。
所以小伙伴一定要考虑好何时删除索引才是解决之道哦

四. 索引背后的数据结构

1. 索引背后数据结构的探讨

小伙伴可以猜想下我们索引背后是哪有一种神秘的数据结构做支持，才能达到像书的目录一样，能加快我们查询的速度。

说到小伙伴学过的关于有查找功能的数据结构无非就是以下两个：

二叉搜索树 : 我们知道对于普通的二叉搜索树来说，一般都不是平衡二叉树，这样就会导致我们的时间复杂度最坏能达到 O(N) , 对于硬盘来说 O(N) 级别的时间复杂度可比内存中 O(N) 的时间复杂度还大的多很多。

并且对于二叉树来说，如果数据规模很大，树的高度就很高，经历的硬盘 IO 次数也是很大的 , 就会大幅度减慢查询速度。

所以二叉搜索树是不适合做我们数据结构背后的索引

哈希表 : 哈希表的本质是讲一组 key 值通过哈希函数转化为我们数组的下标进行存储，但是这个也只能查找某个值，对于我们 SQL x > m 这种范围查找， Hash(x) > Hash(m) 这种结果并不成立的。所以也不适合用作我们索引的数据结构。

那么会是哪种数据结构呢？答案就是我们的 B + 树 …

可以这么说，我们的 B + 树是专门为数据库索引量身定做的一种特殊的数据结构。

2. B+ 树的特点
1. B+树本身是一个N 叉搜索树，一个节点上有 N 个度
1. 每个节点都有多个 K 值，而有多少个 K 就把划分了多少段区域，就有多少个子节点。
1. K 为父节点，下放到子节点时，是作为该节点的最大的 K
1. B+ 树的叶子节点，是该数据表的全集，并且通过链式结构来连接每一个叶子节点
1. 叶子节点存储的数据表中的每一行，非叶子结构存储的是索引列的K .
3. B+ 树的优势
- 因为 B+ 树本身是 N叉树，所以有效的降低了树的高度，从而减少经历硬盘IO 次数。
- B+ 树中每个节点存储的是多个K , 先让硬盘读取该节点，然后在内存进行比较，这样就可以有效的减少经历硬盘IO的次数。
- 叶子节点因为是数据的全集，这样经历硬盘的次数和比较的次数相当，能够更稳定预估结果
- 一个个叶子节点是由链表来连接的，当我们查找某个范围时，就可以利用链式结构快速查找该范围的区间。
- 叶子节点是存储数据表中的每一行，但非叶子节点存储的是索引列的 K ，就可以减少每个节点的存储空间，甚至让内存来读取数据。
  
  鱼式疯言
  
  在这里小编最后想提及一个小概念
  1. 对于我们平常学习的数据库，数据表，以及本篇文章学习的索引，本质上只是一种逻辑结构，而不是我们真正意义上的表或者目录 .
  2. 真正提的上物理结构的，那便是我们的数据结构 , 所以说我们的数据库的这种逻辑结构的背后是需要数据结构这样的物理结构作为支持的
  3. 数据库本身就是一种 “客户端” - “服务器” 结构的程序。
  总结
  - 索引的初识：
    我们知道对于索引就是一本书特殊的 “目录” ，能加快查询的速度
    
    但需要注意的是，我们的索引是针对列而已的，我们指定列查询一定要和索引列一一对应才能，索引的查询效果才能生效。
    
    并且MySQL会对主键，unique ，外键的自动生成索引，且不能删除。
    - 索引的不足：
      唯一两点不足就是：
      1. 占有额外的空间
      2. 可能会影响增删改的速度
      - 如何使用SQL 来操作索引
        1.查看当前索引
        
        show index from 表名;
        
        生成索引
        create index 索引名 on 表名(列名) ;
        
        删除索引
        drop index 索引名 on 表名 ;
        
        注意要点：
        
        生成索引和删除索引在大规模的数据库来说，是危险操作。
        
        删除索引只能删除自己创建的索引，不能删除MySQL生成的索引。
        
        索引背后的数据结构
        本身就是一颗B+树，是一颗为索引而量身定做的一种数据结构。
        
        如果觉得小编写的还不错的咱可支持三连下 (定有回访哦) , 不妥当的咱请评论区指正
        
        希望我的文章能给各位宝子们带来哪怕一点点的收获就是小编创作的最大动力 💖 💖 💖