MySQL中的索引和事务（数据库系列5）-CSDN博客

本文链接：https://blog.csdn.net/weixin_61599986/article/details/130792754

本文介绍了数据库索引的概念、作用、使用方法和背后的数据结构B-树与B+树，强调了它们在提高查询效率和节省空间之间的权衡。同时，文章讲解了事务的基本概念，包括原子性、一致性和持久性，并探讨了并发执行事务可能导致的脏读、不可重复读和幻读问题，以及不同级别的事务隔离性。

摘要由CSDN通过智能技术生成

前言：

1.索引

1.1 索引的概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针，可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。大家可以直接将索引理解为一本书里的目录。

1.2索引的作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
索引所引起的作用类似书籍目录，可用于快速定位，检索数据。
索引对于提高数据库的性能有很大的帮助。

其实索引存在的意义就是为了加快查找速度！！！

但是要注意的是查找速度是快了，但是付出了一定的代价，他需要付出额外的空间代价来保存索引数据，而且索引可能会拖慢新增、删除和修改的速度。

1.3索引的使用场景

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

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

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

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

1.4索引的使用

1.4.1查看索引

语法：

show index from 表名；

案例演示：

这里我们可以看到他是根据id来创建索引的，这里小编给大家说一下为什么我们还没有创建索引呢，他就显示已经有一个索引了，其实当我们在创建表的时候我会指定一些主键约束（primary key）、唯一约束（union）、外键约束（foreign key）时，就会自动创建对应列的索引。

由于我们student表中id是主键所以会给我们自动创建索引。

1.4.2创建索引

语法：

crate index 索引名 on 表名（列名）；

案例演示：

我们下面来根据student表中的name列来创建出一个索引。

注意：
小编这里又要强调了，创建索引危险系数很高！！！如果表里的数据很大，这个建立索引的开销就会很大！！！

1.4.3删除索引

语法：

drop index 索引名 on 表名;

案例演示：

删除之后我们再次查看索引的时候就只剩下我们最初的那个索引了。当然了这里删除索引的操作也是非常危险的操作！！！大家一定要慎重。

1.5索引背后的数据结构

到这里相信大家对索引或多或少都有了一定的了解了，那么这里大家一起来和小编思考一个问题，我们之前学习数据结构的时候学习了很多中查询，其中有哈希表和二叉搜索树，这里有些同学就猜那么我们这里的索引背后是不是就是他两个其中的一个呢？这里小编个大家明确一点，不是！！！哈希表虽然查询速度快，但是它不支持范围查询，不支持模糊匹配；二叉搜索树如果元素个数多了，树的高度就会比较高，树的高度高了相对于比较次数来说就多了，对于数据库来说，则是IO访问次数，他就会多次访问硬盘；所以就都不太建议使用这两者。那么我们又是用什么方法来组织索引的呢？这里我们来学习一种新的数据结构叫B+树。

首先在学习B+树之前我们先来了解一下B-树，这里小编给大家说一下这个B-树的读音他不读B减树，而是读作B杠树，此处的-是“连接符”，不是减号！！！切记不能读错了，那么接下来小编就带着大家一起来看一下这个B-树到底是啥吧！

1.5.1 B-树

我们之前学习了一种树叫做二叉树相信大部分同学都还记得二叉树的特点，那就是树的度为二的就叫二叉树。那么我们这里的B-树是一个怎样的树呢？我们可以理解B-树就是一颗N叉搜索树，如果还不能理解的同学看一下下面的这张图相信大家很快就可以理解了。

我们在每一个结点中保存的值都是父结点的两个值之间的范围。此时我们会发现B-树的一个好处就是相比起二叉搜索树来说她的高度降低了很多，树的高度越高，进行查询的时候访问磁盘的次数就会越多，反之就会越少。所以这样的数据结构就会大大降低我访问磁盘的次数，从而有效的达到了搜索的效果。那么什么又是B+树呢？他与B-树有什么区别呢？我接着往下看。

1.5.2 B+树

其实B+树就是B-树的一个改进，它也是一个N叉搜索树。但是与B-树的不同点在于整个树的所有数据都是包含在叶子节点中的，所有非叶子节点中的key都会出现在叶子节点中。我们看一下下面B+树的示意图：

而且它的叶子结点之间会连接在一起的，就像是一个列表一样将每个叶子结点都给串了起来。

B+树的特点：

一个节点，可以存储N个key，N个key划分出了N个区间，而不是N+1个区间。
每个节点中的key的值，都会在子节点中存在。（同时key是子节点中的最大值）
B+树的叶子节点，是首尾相连，类似于一个链表。
由于叶子节点，是完整的数据集合，只在叶子节点这里存储数据表的每一行的数据，而非叶子节点，只存储key值本身即可。

这里小编来给大家解释一下B+树的最后一个特点的意思。

如果我们现在有一个student表，里面存储了一些值，如下所示：

那么在我们的叶子节点中就会存储每一条记录的具体数据，而父结点处只是简单的存储一些key值值本身即可，比如我们这里的id值。

那么有同学有好奇了，我们上面如果不只有一个主键值呢？那么我们在查询的时候该怎么查询，这里我们表的数据还是会按照id为主键，构建出B+树，通过叶子节点组织所有的数据行，其次，针对name这一列，会构建出另一个B+树，但是这个B+树的叶子节点就不再存放这一行的完整数据了，而是存放这个主键id是啥，此时，如果你根据name来查询，查到叶子节点得到的只是主键id，我们还需要再次通过主键id来去主键id所构造出来的B+树上查询。这里我们就相当于查询了两次B+树。上述的这个过程我们称之为“回表”，这个过程都是MySQL自动完成的，用户是感知不到的。

B+树的优势：

当前一个节点保存更多的key，最终树的高度是相对更矮的，查询的时候减少了IO访问次数。（和B树是一样的）
所有的查询最终都会落到叶子节点上，此时我们查询任何数据，经过的IO访问次数都是一样的。这样就保证了稳定性。（这个稳定性对于程序员来说是很关键的，稳定能够让程序猿对于程序的执行效率有一个更准确的评估）
B+树的所有叶子节点，构成链表，此时比较方便进行范围查询。
由于数据都在叶子节点上，非叶子节点只是存储了key值，导致非叶子节点占用的空间是比较小的，这些非叶子节点就可能在内存中缓存（或者是缓存了一部分），又进一步的减少了IO的次数。

上述的B+树就是我们MySQL组织数据的方式，当你看到一张“表”的时候实际上这个表不一定就是按照“表格”的这样的数据结构在硬盘上组织的，也有可能是按照这中树形结构组织的。这里具体的组织方式我们就不做过多的讨论了。

2.事务

2.1事务的概念

我们先来看一个例子，如果有一天A要给B转账5000元，当A的账户这边转完之后A这边的用户余额就会扣掉5000元，但是呢在转的过程中突然我们的系统出现了Bug或者是突然断电了，结果导致B这边就没有收到这笔转账5000元的记录，这样A这边的余额减少了5000元，B这边的余额却没有增加一点，这件事显然是不符合我们的转账逻辑的。那么为了解决上述的问题我们就引入了事务这样一个概念，事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败，其实事务的本质就是把多个SQL语句打包成一个整体，要么全部执行成功，要么就一个都不执行，而不会出现执行一半这种中间状态。在不同的环境中，都可以有事务，对应在数据库中，就是数据库事务。

2.2数据库事务的四个特性

2.2.1原子性

什么是事务的原子性呢？过去人们认为原子是事物能够分割的最小单位，所以就以原子来说明事务是不可分割的单位了。这也就对应了事务被打包成一个整体要么被全部执行，要么一个都不执行，不会出现执行一部分的情况。其中原子性是我们事务最核心的特性。

2.2.2一致性

事务的一致性是指，事务在执行前和执行后数据是可靠的，例如A给B转账5000，如果B这边收到的是5000元说明是可靠的，如果说收到的不是5000，那么这个事务就是一个不可靠的，没有保证事务的一致性。

2.2.3持久性

事务的持久性是指，事务修改过的内容是写到硬盘上的，持久存在的，重启也不会丢失。

2.2.4隔离性

事务的隔离性是指，这个事务为了解决“并发”执行事务所引起的问题，它存在的意义就是为了在数据库并发处理事务的时候不会有问题（即是是有问题，也不会太大）

这里我们又引出了一个新的概念叫做并发。何为并发呢？并发就是指客户端给服务器发出了多份请求，此时服务器同时处理多个客户端发来的请求就是并发。我们可以举个栗子来理解一下：比如此时服务器就是一家餐馆，客户端就是来这家饭店的客人，此时一个餐馆（服务器）要同时给多个顾客（客户端）提供服务，如果餐馆处理这些顾客的要求时是一起处理的，那么此时就是“并发执行”。针对于我们所用到的数据库也是服务器，就可能有多个客户端都给服务器提交事务，数据库此时就需要并发处理多个事务。如果并发这些事务是修改不同的表/不同的数据，那就没有啥问题，但是如果修改的是同一个表/同一个数据，那就可能会带来一些问题！！！让我们接着往下看吧！

3.并发执行事务所引起的问题

注意：这里下面的所有问题都是由于事务“并发”执行所引起的问题！！！

3.1脏读问题

这里我们为了方便大家理解，给大家举一个例子：

比如我们的A在办公室里面对着电脑在写代码中，结果写着写着后面就飘过去了一个B，B此时就瞄了一眼A的写的代码，看自己可以有啥可以用到的代码，此时B在A的电脑屏幕上看到A写了这样的一段代码：class Student，并且有一些属性（id，name....），然后B就走了，结果就在B刚刚走之后A就修改了代码，他觉得自己写的代码也有点问题，然后就将刚刚代码增加了一些属性，此时B的读操作就是脏读，读到的数据也称之为“脏数据”，此处的“脏”的意思是指无效的意思。

那么为了解决这一问题，我们就想了一个办法在MySQL中引入了一个“写操作加锁”这样的机制，也就是A和B约定好了在A写代码的时候B不允许来瞄A的代码，也就相当于上厕所的时候锁门这样的操作。

注意：

但是给写操作加锁之后也就意味着写操作和读操作不能同时进行了，也就是我们上述所谈论到的这两个操作不能“并发”执行了。此时这个写操作就降低了并发程度（降低了效率），但是同时也提高了隔离性（提高了数据的准确性）！！！

3.2不可重复读

此时我们依旧是A和B两个同事在办公中，此时是二人已经约定好了当A在写代码的时候，B不可以进行读代码，也就是给写加锁，此时A写好代码之后提交到了Gitee上面，然后B就去Gitee上面去读取代码，但是就在B读取的过程中，A发现他有一段代码写的有点问题，重新修改后就又一次上传了Gitee，此时B读着读着，结果一下子代码刷~的一下就变样了，此时B读出来的结果可能就不一样了，像这样的问题我们就称之为不可重复读。

为了解决这一个问题A和B就签订了一个协议：在B读取代码的时候A是不可以修改代码的，此时就是给读进行了加锁。在A写的时候B不可看是给写加锁，此时B在读的时候不能看是给读加锁。

注意：
通过这个“读加锁”操作，又进一步的降低了事务并发处理的能力（处理事务效率也降低了），提高了事务的隔离性（数据的准确性又提高了）！！！

3.3幻读

但是当我们给“写加锁”和“读加锁”这样就可以保证万无一失了吗？当然不是，前面给“写加锁”和“读加锁”已经解决了脏读和不可重复读的问题，但是如果A在此时不在原有的代码上面进行改动，而是新写了一个.java文件呢？比如之前A写了Student.java文件然后上传至Gitee上面了，B去读了Student.java里面的内容，结果A在B读的过程中又写了Teacher.java文件，然后又上传到了Gitee中。此时B在读取的时候突然就发现代码中怎么又多出来一个Teacher.java文件了，也就是在“读加锁”和“写加锁”的前提下，一个事务在两次读取数据的时候，发现数据的值是一样的，但是结果集不一样了，就比如B在第一次读取的时候是只有（Student.java），结果在第二次读取的时候结果变成了（Student.java、Teacher.java）这就称之为“幻读”。

注意：

那么为了解决“幻读”问题，数据库中使用“串行化”来解决这个问题，也就是彻底放弃了“并发”处理事务，一个接着一个的处理事务，这样做，并发程度是最低的（效率最慢），隔离性是最高的（数据的准确性也是最高的）！！！

3.4三种问题的总结

出现的问题	操作	结果	效果
	给写加锁（read uncommitted）	没有进行任何锁限制	并发程度最高（效果最高），隔离性最低（准确性最低）
脏读	给读加锁（read committed）	给写加锁了	并发程度降低，隔离性提高了
不可重复读	repeatable read	给写和读都加锁了	并发程度又降低了，隔离性又提高了
幻读	serializable	串行化	并发程度最低，隔离性最高

结束语：

好了这节有关于索引和事务的一些基础知识小编就介绍到这里啦！如果还是没有理解的同学建议下去之后多多联想一下现实生活中的例子来帮助自己更好的理解此处的知识点，大家继续跟紧小编的步伐，一起往前冲！！！希望这节对大家认识数据库有一定的帮助，想要学习的同学记得关注小编和小编一起学习吧！如果文章中有任何错误也欢迎各位大佬及时为小编指点迷津（在此小编先谢过各位大佬啦！）