位置：首页-资讯-数据库

MySQL索引与事务问题

2023-10-02 16:19

短信预约 -IT技能 免费直播动态提醒

下一节将会讲到JDBC相关编程，

敬请期待~~~~~

1. 索引

MySQL索引是面试里经常涉及到的问题，是相当重要的，下面我们对MySQL这里面的索引来进行一个说明

1.1 什么是索引

①理论上：

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

②实际生活例子：

a.书的目录是索引，我们可以根据书的目录找到相应的页面
b.同学的学号是索引，班主任可以通过学号找到对应的同学

1.2索引的使用场景

①创建索引，需要考虑以下场景：数据量较大，且经常对这些列进行条件查询。该数据库表的插入操作，及对这些列的修改操作频率较低。索引会占用额外的磁盘空间。满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。 ②关于索引与sql的先关操作： a.查看索引： show index from+表名
我们查询student1这个表，发现它里面已经自带了索引，为什么它会自带索引呢？

因为我们在建表的时候设置了主键，除了主键之外，要是用unique的话，也会自带索引，如下图所视：

b.为某个列来创建一个索引

create index 索引名字 on 表名（列名）

注意！！！要是创建索引我们一般是在创建表的时候一起创建，如下图所示：

而我们一般不会在已经完成了表的创建后去为它添加索引，因为若是这个时候，表中已经有很多数据，就很容易把数据库给搞垮。下图这种已经创建表后在创建索引是不值得被推崇的，是很危险的！！！

c.删除索引的操作:

drop index 索引名字 on;这个操作同上一个创建索引一样，当数据库里已经有很多的内容时，贸然删除，可能最终会导致数据库搞垮；

删除索引后，原表中的索引就不复存在,注意！！！这是相当危险的行为

1.3索引相关代价

索引的主要意义是进行查找，主要目的是提高查找的效率，但随着查找效率的提高，随之会伴随着一些代价。我们以上面生活中的实例来谈谈先关的代价：

①对于书的目录而言：

增加目录确实加快了索引的查找速度，提高了查找效率，但是你也能发现，它同时浪费了一定数目的纸张，换言之，随着对查询的数据量的增大，却占用了越来越多的额外存储空间

②对于学生学号而言：

为每个学生命名学号，确实方便了老师查找同学信息，但是随着学生人数人多，就需要更多的额外空间去记录学生的学号。

既然如此，那我们为什么还要用索引呢？？？

我们会很轻易地发现，一本书只要上市了就没有更改目录的机会，而对于学生而言，只有很小的概率会迎来转学生，也就是增添学号的几率微乎其微，而我们却经常进行通过目录找页码和通过学号找同学的一系列操作，所以，从大体程度上而言，索引的利远大于它的弊。

1.4索引背后的数据结构（B+树）

①引入：

因为索引的根本目的是提高查询的效率，因此我们必须要实现遍历的操作，而此处的遍历并不是指指针般按照地址值进行遍历，而是按照值进行遍历

②为什么要选择B+树来作为索引背后的数据结构呢？

（1）为什么不使用二叉树（搜索树）？

因为，对于二叉树最大的问题，就是当元素多了的时候，树的高度自然就高了，而对于数据库而言，每次比较都意味着磁盘要进行IO

（2）为什么不用哈希表？

虽然哈希表的时间效率达到了O(1)，但是对于哈希表而言，只是更适合于查找某值即对相等的情况作出相应的判定，而不适合于查找某个范围之类的问题

（3）为什么不用堆？

因为堆更多是用来找堆顶元素，即最大值或者最小值的情况

（4）为什么要用B+树？

因为类似于B树，B+树这样的树，也称为多叉搜索树，相对于普通的二叉搜索树而言，它的高度就自然下降了。

在进行解释之前，我们先来了解一下，这两种树是什么？

①什么是B树（B树又称B-树）？

a.B树的特点：对于B树而言，它的每个节点上，都会存储N个Key值，N个key值就划分出了N+1个区间，每个区间都对应着一个子树。

b.B树的查找过程：

在B树查找的过程中，先从根结点出发，根据将要查询的值锁定它存在的区间。这个时候很多人就会说，在锁定区间的过程中也会进行多次比较，那么二叉搜索树为什么不行？

c.为什么二叉搜索树不行？

因为对于二叉搜索树而言，每个节点比较次数都是和高度相关的，都是父亲节点与儿子节点相比较，但对于B树而言，由于每个子树是一个范围，且同一层有多个范围，所以它的高度就相对小了，但是每个节点的比较次数也同样增多了。而对于比较次数而言，进行IO的次数，才是我们更关心的，而磁盘进行IO是以节点为单位进行比较的

d.B树的实际结构图：

（3）什么是B+树？

a.B+树的结构是什么：

B+树也是一个N叉搜索树，每个节点上都包含了多个key值，每个节点如果又N个key，就分成了N个区间。
b.B+树与B树不同的点：

与B树不同的是，父节点的值，都会在子节点上体现出来非叶子结点的值最终也会在叶子结点体现出来，总的来说就是只要是在B+树里出现过的值，就至少会在叶子结点体现出来。而父亲节点的值，将会作为其子节点的最大值或者最小值，给体现出来。而最下面的叶子结点，就是用链表按顺序进行连接的

c.B+树的实际结构图：

d.为什么说B+树更适合用来索引：

B+树同B树一样，在进行查找的时候，整体的IO次数比较少
2.查询的最终结果是落在叶子节点上，所以每次查询IO次数都是差不多的，查询速度都是稳定的
3.叶子结点是用链表连接起来的，这样更便于范围的查找
4.所有数据存储（载荷）都是放在叶子结点上的，因此非叶子节点中只保存key值即可，即非叶子节点整体占用的空间较小，甚至可以缓存到内存中去

2.事务

2.1什么是事务

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

2.2为什么使用事务

事务的诞生的目的就是为了把若干个独立的操作打包成一个整体来进行操作。

在SQL中，有些任务是由多个sql组合来进行完成的，如果步骤中某个sql出了问题，那么它必然会影响它的前一个或者sql,从而使大家都失去了意义

2.3事务的四个特点

2.3.1原子性

①什么是原子性：

要么都执行完，要么都不执行，事务不能够进行一分再分

②实际中的例子：

转账。当A给B转账1000元时，当发生了转账操作的时候，A余额-1000元，B余额+1000元。这两者是一个整体。正常情况下是不可能出现前者转了后者没到的操作的。

③如何保证原子性？

若是发生了前一个sql发生，而后一个sql出现问题莫名导致中断的这种问题的话。

数据库会自动进行一些“还原”的操作，来消除前面sql带来的影响，最终像原来sql似乎没有执行过一样。

2.3.2一致性

就是指在事务执行前后，数据库中的内容都是合理的。就拿上面所提及的转账操作来说，账户无论怎么转或不转，都不可能出现为负数的情况。

2.3.3持久性

事务一旦发生提交之后，数据就会长久地存储起来，就会写入硬盘中了

2.3.4隔离性（重点理解！！！）

①隔离性是什么?

隔离性是描述事务并发执行的时候所产生的情况。当并发执行多个事务时，就会存在有极大可能尝试/读取同一份数据，就会出现一些问题，而隔离性的出现就是为了解决上述问题

举个例子：

在学校当同学们同时填写在线表格的时候，我们会轻易发现，有时候双方可能填到了同一个位置中

②脏读问题：

a.什么是脏读：

事务A正在对某个数据进行修改，而与此同时，事务B正在读取这个数据，此时事务B读到的数据有很大可能只是临时值，只是修改前的值而不是最终的结果，这就是“脏数据”，而这个操作就是脏读

b.为什么会出现脏读？

就是因为事物A与事务B之间没有进行隔离；从而出现了上面的这些问题

c.应该如何避免脏读？

加上一些约束，使事务间进行有效的隔离。

处理脏读：给写操作枷锁，保证在写的过程中别人无法进行读取，而修改完之后别人才能进行读。一旦这个写锁之后，就增强了隔离性，并发程度就降低了

②不可重复读操作：

a.什么是不可重复读操作？

在脏读中提到：给写操作加锁，我们在写的时候是不能进行读的，但是当读的时候却并没有规定不能有写的操作。

即一个事务，包含了多次读的操作，而每次读的结果不一样

b.如何解决这种情况？

不仅给写的操作加锁，与此同时给读的操作也加上一个锁，这样就避免了这种情况的发生，此时，事务的并发性就又降低了，隔离性就又提高了。

③幻读问题：

a.什么是幻读问题？

一个事务执行的过程进行了多次查询，而多次查询的结果数目不一样，这个操作算是一种特殊的不可重复读

我们在读取的时候，别人又进行了写的操作，与之前不一样的是，写的不是同一个内容，写的是这个事务部分的内容，而此时读着读着就会发现内容的数目变多了。

b.如何解决幻读问题：

彻底串行化。

彻底串行化指的是：写的时候就锁上读操作，读的时候就锁上写操作

这样隔离性最高，并发程度最低，数据最可靠，时间最慢

由此我们也可以看出并发和隔离是不可兼得的