【从删库到跑路 MySQL总结篇】索引的详细使用(1)

举个栗子1：
输入命令：create table student(id int primary key,name varchar(20));
查看当前表的结果，如下图：

我们可以看到上表中存在主键id，mysql中当表中存在主键的时候系统就会自动地给这个列来创建索引；同时由于主键是不允许重复的，因此要想插入或者修改数据的话，就需要先进行查询，来看看插入或者删除后的结果是否存在。
举个栗子2：
输入命令：create table student(id int unique,name varchar(20));和show index from student;
查询结果如下：

可以看到上述中，如果使用unique约束的话也会自动生成索引。

举个栗子3测试数据的sql语句:
create table classes(classes_Id int primary key,name varchar(20));
create table student(id int primary key,name varchar(20),classes_Id int,foreign key (classes_Id) references classes(classes_Id));

// 查看索引
show index from classes;
show index from student;

举个栗子3：
查询结果如下：

其中classes表和student表中各有一个主键约束自动创建的索引，同时student表中存在一个外键约束自动创建的索引。

如上图，外键约束也是会自动生成索引的；当然，外键约束也是会涉及到自动查询的。这里我们来举两个栗子，在这之前，我们再来回顾一下下面需要用到的student表和classes表，请看：

现在来进行外键约束也是会涉及到自动查询的的举例。
举个栗子1：当我们需要在学生表中插入数据的时候就需要查询classes_Id在classes表中是否存在，此处的查询就需要用到classes表的classes_Id(即classes表中主键自动申城的索引)。
举个栗子2：当我们想要删除classes表中的一条记录，我们就需要需要查询classes表中的classes_Id是否在student表中存在。如果存在的话，此时就是删除失败（删除失败原因就是子表对父表的反向约束作用）。而此处的查询就用到了student表中的classes这一列对应的索引（此索引就是外键约束自动生成的）。

上述三种情况：主键、unique、外键都会使表自动创建出索引。所以此时我们就不需要再对单独的这一列创建单独的索引了。

创建索引（危险危险）

手动给指定的列创建索引语法格式：create index 索引名 on 表名(字段名);

这里依然使用上述的两张表（student表和classes表），请看：

下面我们来简单的创建索引：
输入命令：create index idx_student_name on student(name);
创建索引结果如下：

我们来查看一下我们刚刚创建的索引，输入命令：show index from student;

如上图我们可以观察到我们刚刚新创建出来的索引（在上图中的最后一行）。

然而这里我们不得不提到：创建索引的操作是非常危险的。（真是的，怎么我写个sql语句就这么困难呢？到处都是危险操作）。
如果我们创建的索引所在的表的数据是空的或者表中的数据本身就不多，此时我们创建索引的操作就不是危险操作
但是，如果表是非空的，并且表中的数据量是非常庞大的，那么此时如果我们创建索引的话就会引起非常庞大的硬盘IO操作。甚至会消耗大量的cpu资源，从而进一步导致数据库被卡死。
所以我们创建表的时候一定要想好到底要不要创建索引，提前考虑好表的结构中的哪一列需要创建索引，因为后期一旦我们再想创建索引的话就有点不切实际了，所以凡事要趁早，提前做好规划。

删除索引（比较危险）

删除索引只能删除手动创建的索引。

删除索引语法格式（指定要删除的索引名和表名即可）：drop index 索引名 on 表名;
还是来举个栗子，现在我们要删除刚刚给student表中的name创建的索引，如果你忘记了刚刚student表结构，那么请看下图来回忆以下：

输入命令： drop index idx_student_name on student;
结果如下（检验是否删除索引成功），请看下图：

可以看到我们刚刚创建的idx_student_name索引删除成功了。

上面我们提到过只能删除手动创建的索引，我们试试能不能删除student表自动创建的索引classesId，输入命令：drop index classes_Id on student;结果如下：

报错原因：外键约束需要索引classes_Id。
由此我们可以看出我们只能删除手动创建的索引，而自动创建的索引是不能被删除的。

删除索引这个操作也是比较危险的，不能轻易删除索引。

假设有这样一个场景：现在我们有一个有很多很多数据的表，同时这个数据库此时是处于生产环境的一个数据库。但是我们现在必须要创建删除索引。此时怎么办呢？我们要知道数据库服务器往往不仅仅是单台服务器，为了整个系统的稳定性，通常会搞出多个mysql服务节点，这些节点的数据往往都是一样的，能够提出相同的服务。（以防万一某个mysql服务器节点挂了，而其它服务器依然可以很正常运行）

此时我们可以这样做：先准备好一个新的mysql服务器，把表和索引都创建好，然后把数据都导过来，再把要替换的mysql服务器关闭掉，然后再把新的mysql服务器替换掉就好了。

二、索引底层数据结构

MySQL底层的数据结构并不是一个定式，而是取决于MySQL使用哪个存储引擎。

那什么又是存储引擎呢？
MySQL这个程序中包含了许多模块：有的模块是负责解析sql的；有的模块是负责网络通信的；而有的模块则是用来存储数据的。我们用来存储数据的相关的模块称为存储引擎（本质上就是代码中的一个模块，这个模块中包含了若干个代码文件以及一堆具体的代码）。
而具体如何来进行数据的存储，MySQL支持多种存储方案。Innodb是当下最主流的一种存储方案。
数据库中组织数据所使用的数据结构是在硬盘上的。（这里就与内存中的数据结构就有所区别了）

内存上的数据结构（比如我们平常实现的链表、二叉树、顺序表等都是存储在代码内存中的）对于访问操作来是是非常不敏感的。（其实内存中的数据结构进行访问所占用的时间并不多，真正占用时间的而是找数据的过程）
硬盘上的数据结构对于访问操作来说是比较敏感的，读写一次硬盘的开销是远远大于内存的，此时我们就不得不考虑访问所带来的开销了且读写一次硬盘相当于读写约10000次内存。

好了，我们现在来看看索引到底是使用哪种数据结构比较合适吧！在这之前我们首先要明白索引是用来干什么的，嗯对，索引的目的其实就是方便我们进行快速的查找。回顾我们以往学到的数据结果中，能够做到快速查找的一个是二叉搜索树（带有平衡机制的二叉搜索树），另外一个就是哈希表了。
但是哈希表并不适合作为索引的底层结构，因为哈希表（哈希表基本执行过程就是把给定的key通过hash函数映射出一个具体的下标才能定位到具体位置）无法做到范围查询或者说模糊查询，哈希表只能完成精确查询的任务。

那红黑树其实也不适合作为索引的数据结构，虽然红黑数种的元素是有序的，可以进行范围查询，但是之所以红黑树不能作为索引的数据结构最大的问题就是红黑树的高度（当元素比较多的时候，树就会变的非常高、或者非常深），而树的深度越深比较的次数就越多。而索引这样的数据结构是存储在硬盘上的，每次比较都意味着硬盘IO操作（读写一次硬盘就相当于约10000次内存读写）。所以红黑树不适合作为索引的底层数据结构

在MySQL中的Innodb存储引擎中，索引的底层数据结构是B+树；而在其它存储引擎中，也可能会用到hash索引，此时就只能应对精准匹配查询的这种情况（所以hash并不是不能作为索引，只是丧失了部分功能而已）。

想要清楚什么是B+树，我们就需要先知道什么是B - 树。

B-树

B - 树（本质是N叉搜索树）：B 树的一个节点上可以保存多个key，N个key就可以延伸出N+1个分支。查找元素的流程就是拿着要查找的元素从根节点出发（判断要查找的元素是否在根节点上），如果不存在的话，我们就需要看看这个元素最终落到哪个区间内，然后沿着这个区间的路线继续往后找，如果最终的叶子节点依然不存在我们要查找的元素，那么这个要查找的元素就真的不存在了。

与二叉搜索树相比，B - 树的每个节点可以存储多个元素，在元素总个数相同的情况下，B - 树的节点数和高度都会大大降低；同时B - 树在同一个层次中会存在多个分叉，这也会降低节点数和树的高度。

对于数据库来说，每个节点上的数据都需要从数据库中读取出来才能进行比较（每个节点访问的时候都会进行硬盘的IO操作。）随着B树的节点减少的同时，节点的访问次数就会减少，硬盘IO操作也当然会随之减少。还有一点我们需要知道：一个节点中有一个key和一个节点中有多个key的节点访问时的硬盘IO操作的开销是差不多的。

B树的插入元素和删除元素会涉及到拆分和合并的操作，解释如下：
B树中的一个节点的确可以保存多个key，但是也不可以无限的进行存储，当key的数量到达一定程度的时候，此时就需要把这个节点进行拆分，即把这个节点中的一部分key以树的子节点的方式进行重新组织，这样就可以保证每个节点中的key的数量不会太多（当然也会衍生出新的叶子几点）。
B树的合并操作：B树的合并操作一般是在节点删除后执行的，因为当删除一个节点时，会导致 B 树的高度降低，可能会破坏 B 树的平衡性，需要对 B 树进行合并操作以保持平衡。
关于B树的拆分和合并操作：具体什么时候进行拆拆分，怎么拆分；什么时候合并，怎么合并需要看具体的实现，同时不同场景下会有不同的应对策略。

B+树

好了，现在我们来了解下数据库中索引的主角：B+树（N叉搜索树）。B+树在B树的基础上又做出了一些改进。

B+树特点：

• N个key分出了N个区间，每个节点上的最后一个key就是最大值（也可能是第一个key，即最小值）。
• 父节点中的key会在子节点中重复出现（以最大值或者最小值的身份），这样就会出现一个效果，即叶子节点这一层的的数据包含了整个数据的全集。
• 把叶子节点进行链表方式的首尾相连，此时叶子节点这样的一个连接方式就可以快速找到上一个或者下一个节点，方便进行范围查询（只要能确定开头和结尾，开头和结尾中间的子链表就是我们查询出来的结果）。

最后

看完美团、字节、腾讯这三家的面试问题，是不是感觉问的特别多，可能咱们又得开启面试造火箭、工作拧螺丝的模式去准备下一次的面试了。

开篇有提及我可是足足背下了1000道题目，多少还是有点用的呢，我看了下，上面这些问题大部分都能从我背的题里找到的，所以今天给大家分享一下互联网工程师必备的面试1000题。

注意不论是我说的互联网面试1000题，还是后面提及的算法与数据结构、设计模式以及更多的Java学习笔记等，皆可分享给各位朋友

互联网工程师必备的面试1000题

而且从上面三家来看，算法与数据结构是必备不可少的呀，因此我建议大家可以去刷刷这本左程云大佬著作的《程序员代码面试指南 IT名企算法与数据结构题目最优解》，里面近200道真实出现过的经典代码面试题。

，皆可分享给各位朋友

[外链图片转存中…(img-ROiCnpN9-1714522636236)]

互联网工程师必备的面试1000题

而且从上面三家来看，算法与数据结构是必备不可少的呀，因此我建议大家可以去刷刷这本左程云大佬著作的《程序员代码面试指南 IT名企算法与数据结构题目最优解》，里面近200道真实出现过的经典代码面试题。

[外链图片转存中…(img-xRY7riCU-1714522636237)]

本文已被CODING开源项目：【一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码】收录