数据库概论之 MySQL 索引事务

雨点打透心脏的1/2处 2024-03-26 17:02 49阅读 0赞

#### 数据库概论之 MySQL 的索引事务 ####

*  一 . 索引
 *   *  1.1 索引的引入
     *  1.2 索引的使用
     *   *  1.2.1 查看索引
         *  1.2.2 创建索引
         *  1.2.3 删除索引
     *  1.3 索引使用的具体数据结构
     *   *  1.3.1 B 树
         *  1.3.2 B+ 树
 *  二 . 事务的基本特性
 *   *  2.1 最核心的特性 : 原子性
     *  2.2 一致性
     *  2.3 持久性
     *  2.4 隔离性
     *   *  隔离问题
         *   *  脏读
             *  不可重复读
             *  幻读
         *  隔离级别
 *  三 . 事务的操作

> 大家好，这篇文章就会将 MySQL 最后的一点基础知识进行收尾 , 后续大家也要关注一些数据库的其他方面知识 , 不能仅局限于我的博客里面的这些知识 . 我分享的内容在数据库当中都是一些非常基础、非常小白的操作 , 大家下来还是要深入学习的 .  
> ![在这里插入图片描述][65073f2a57364e6e8dcb487617e2182f.png]

## 一 . 索引 ##

### 1.1 索引的引入 ###

我们之前也介绍过 , 使用外键的时候 , 通过索引来查询 , 速度就会比较快

> 索引实际上就是目录 , 目录的意义就是加快查找的意义

索引本质上是通过一些复杂的数据结构把所有待查询的记录给组织起来了 , 从而加快查找的速度  
我们之前也学习过一些查找的数据结构 :

1.  顺序表 : O(N)
2.  链表 : O(N)

> 谈谈 ArrayList 和 LinkedList 的区别
> 
> 1.  ArrayList 是顺序表 , LinkedList 是链表
> 2.  ArrayList 支持按下标随机访问 , LinkedList 不支持

ArrayList 查找速度较快这个说法是错误的 , 查找指的是 indexOf 而不是 get

> LinkedList 增加删除元素较快这个说法也是错误的 , 链表往中间位置插入元素 , 时间复杂度是 O(1) , 但是 LinkedList 不是 , 标准库在设计 LinkedList 的时候有些问题 , 就没发挥出链表的优势 . 比如  
> LinkedList list = new LinkedList<>();  
> list.add(4 , “hello”);  
> 这个操作是从头遍历到 4 号元素再插入 , 虽然插入操作是 O(1) , 但是遍历操作的时间复杂度是 O(N)

1.  栈 : 不是用来查找的
2.  队列 : 不是用来查找的
3.  二叉树 : O(N)
4.  二叉搜素树 : O(N)~O(logN) , 极端情况下就变成了单枝树 , 就相当于遍历链表
5.  堆(优先级队列) : 不是用来查找的
6.  哈希表 : O(1)

目前来看 , 二叉搜索树和哈希表看起来还是比较快的 , 虽然普通的二叉搜索树查找的时间复杂度是 O(N) , 但是有 plus 版本 - AVL 树 / 红黑树 (他们俩的时间复杂度都是 O(logN))  
但是数据库索引使用的数据结构还不是他们俩 , 因为 :  
红黑树作为一个二叉树 , 如果元素数量多了 , 树的高度就会比较高 , 树的高度一旦高了 , 比较次数就会增加 , 由于数据库每次访问数据都要访问磁盘 , 比较次数过多就会造成查询速度变慢  
哈希表虽然查的确实快 , 不过他只能查询相等的情况 , 不能进行 < / > / 范围查询 / 模糊匹配的操作

索引实际上就是引入额外的数据结构 , 来加快查询速率的

1.  消耗额外空间
2.  虽然引入了索引加快了查询速度 , 但是拖慢了增删改的速度

> 假如索引就相当于书中添加了目录 , 如果再添加新的内容 , 目录也是需要更改的

索引也不是那么好用的 , 主要还是看场景是否合适 (多读少写的情况)

1.  对于空间不紧张的情况下 (空间换时间)
2.  查询频繁 , 增删改不频繁

### 1.2 索引的使用 ###

#### 1.2.1 查看索引 ####

查看索引 : show index from 表名;  
![image.png][]  
我们可以看到 , 我们没添加过索引 , 但是他自己就带了索引  
一个表的主键会自动带上索引

> 像主键这种 , 需要保证记录不重复 . 每次插入新记录 , 都需要查询一下已有记录 , 看看新纪录是否存在 , 频繁的查询必然要搭配约束

unique 和 外键约束的列 , 也会自动带上索引

#### 1.2.2 创建索引 ####

创建索引 : create index 索引名 on 表名(列名);  
![image.png][image.png 1]

#### 1.2.3 删除索引 ####

删除索引 : drop index 索引名 on 表名;  
![image.png][image.png 2]

--------------------

注意 : 创建索引和删除索引也是一个危险操作 ! 尤其是针对一个已经包含很大数据量的表进行操作的时候  
如果是针对一个数据量非常大的表 , 创建索引就会导致大规模磁盘 IO , 直接就把主机的磁盘 IO 吃满 , 主机就有可能卡了 , 无法对线上服务进行相应

### 1.3 索引使用的具体数据结构 ###

我们之前说不能使用红黑树的原因是因为树的高度每增加一层 , 就要多出许多磁盘 IO 操作 , 那么我们让树的高度矮一点呢 ?  
这就是我们的核心思路 : N 叉搜索树 , 和二叉搜索树类似 , 只不过是节点的度多了(该节点的叶子树)

#### 1.3.1 B 树 ####

![image.png][image.png 3]  
但是在实现数据库的时候 , 一般也不会去使用 B 树 , 而是使用 B 树的改进版本 -> B+ 树

> 有些教材上将 B 树叫做 B-树 , 这个 - 是连接符

#### 1.3.2 B+ 树 ####

![image.png][image.png 4]  
B+ 树可以说是为了实现数据库索引量身定做的数据结构

1.  查询速度快 : 类似于二叉搜索树的查询
2.  单个节点存储更多的数据 , 因此树的高度比较低 , 比较次数就少了
3.  所有的叶子结点使用链表首尾相连 , 非常适合范围查找

![image.png][image.png 5]

1.  每一个数据行只需要保存在叶子节点上就够了 , 非叶子节点不必存储真实的数据行 , 只需要存储用来做索引的 id 即可

> 一个表里面有很多列 , 几十列都有可能 .  
> 一行数据占用的空间比较大 , 只能放在磁盘上  
> 做索引的常见情况是拿出其中的某个 id 做索引  
> 因为单拎出来某个 id 会比整个行小很多

![image.png][image.png 6]  
此时非叶子节点占用的空间小 , 就有了在内存中缓存非叶子节点的可能性  
如果把非叶子节点都在内存中缓存了(哪怕一部分也好) , 也会大大的提升速率  
而 B 树是把所有信息都存储在了每个节点上 , 导致速率降低

B+ 树的实现比较麻烦 , 最复杂的部分就是叶子的分裂和合并  
我们只需要理解 : (高频面试题)

1.  数据库的索引为什么使用 B+ 树而不是红黑树 / 哈希表
2.  理解 B+ 树的结构特点
3.  理解 B+ 树的优势

## 二 . 事务的基本特性 ##

在我们的实际操作数据库的过程中 , 有些操作 , 希望是一个整体

比如 : 你谈了个对象 , 一般都是这个流程 : 给彩礼 -> 领证 -> 办酒席  
那么我们希望这个流程是一个整体 , 一旦中间某个过程出现问题 , 就希望能够让这些操作的影响降低到最低  
比如说 : 彩礼给完了 , 但是这个时候女生发现男生是个渣男 , 后悔了 , 不想结婚了 , 女方反悔 , 就想要回彩礼  
那要是彩礼给了 , 证也领了 , 女方想反悔 , 这个时候就需要先把彩礼给退了 , 还需要去民政局办个离婚证

那在这样的场景下 , 我们就希望能够一气呵成 , 当做一个整体来进行 . 如果执行中间过程中 , 出现异常 , 就需要把前面已经进行的操作进行 回退 / 恢复 , 恢复成好像根本没操作过的样子 .  
这三个步骤就可以视为一个事务 , 把多个操作打包成一个操作 , 其中任意一个步骤执行失败 , 都会进行回退 , 使这里的影响降到最低

数据库上也涉及到与事务很多的相关操作 , 最典型的就是转账  
account(id,name,money)  
1 张三 1000  
2 李四 2000  
张三给李四转 500 块钱  
\-> 给张三 -500 SQL1 执行减的操作  
给李四 +500 SQL1 执行减的操作

那现在有一种情况 : 如果在执行完 SQL1 (账户 -500) 之后 , 恰好再执行 SQL2 (账户 +500) 的过程中 , 数据库出问题了 (比如断电了) , 这就变成了张三的账户变成了 500 , 李四的账户还是 2000 , 这 500 块钱就转丢了  
我们的解决方案就是要不两个都执行完 , 要不就两个都不执行  
这就涉及到了事物的最核心的特性 : 原子性

### 2.1 最核心的特性 : 原子性 ###

以前的人们认为 : 原子是不可分割的最小的单位 , 后来就使用 “原子” 这个概念来指代这种不可分割的内容  
原子性指的是 : 当前事务的操作要么同时成功，要么同时失败

我们上面没说的 “要不两个都执行完 , 要不就两个都不执行” , 难道 “两个都不执行” 是真的没执行吗 ?  
并不是 , 这里的方式是通过恢复的方式把之前操作造成的影响给还原了 , 还原的过程叫做回滚 (rollback)

数据库的事务回滚是如何做到的呢 ?  
数据库中的每个操作在内部都有记录 , 尤其是事务内部的操作 .  
如果事务中间出现问题 , 就可以根据之前的记录 , 来进行恢复了 (类似于 : Git)

### 2.2 一致性 ###

一致性指的就是数据一直是对的 , 比如刚才的案例 :  
必须是张三 -500 , 李四就得 +500  
不能出错 , 没有纰漏

### 2.3 持久性 ###

持久性指的就是 : 事务进行的操作都会写进磁盘 , 只要事务执行成功 , 造成的修改 , 就是持久化保存的了 . 哪怕重启主机 , 这样的修改也还是存在的

### 2.4 隔离性 ###

描述多个事务 并发执行 的时候所产生的情况  
比如 : 之前疫情的隔离有好几种级别

1.  定点医院隔离 : 隔离效果最好 , 成本最高
2.  指定酒店隔离 : 隔离效果比较好 , 成本比较高
3.  居家隔离 / 在学校隔离 : 隔离效果一般 , 成本比较低
4.  不隔离 : 没有啥隔离效果

这些不同隔离级别严格情况不同 , 所以效果也是不同的  
隔离就是为了避免相互之间产生影响

数据库中的事务彼此之间也是可能会相互造成影响的 , 事务的隔离性也是在描述事物执行过程中 , 影响情况能接受到什么程度  
我们刚才也提到了 , 运行环境是在并发环境的情况下 , 那如果事务是一个一个执行的 , 那就还好 . 但是如果事务是并发执行的 , 相互之间可能就会造成影响

> 并发 : 简单粗暴的理解成两个事务在同时执行  
> 比如 : 我们开了两个控制台 (一个数据库服务器可以给多个客户端提供服务)  
> 这个时候就可能会涉及到说多个客户端同时尝试操作同一个表 , 就可能产生这种并发事务的情况

假设我们现在有一张售票表  
售票表(剩余票数)  
1  
客户端1 可能要执行 “买票事务”

1.  先检查剩余的票数是否 >0
2.  如果 >0 就修改售票表里面的计数 , 让计数 -1

客户端2 可能要执行 “买票事务”

1.  先检查剩余的票数是否 >0
2.  如果 >0 就修改售票表里面的计数 , 让计数 -1

如果两个客户端不加任何限制 , 同时执行 , 就会可能带来麻烦  
客户端 1 和 客户端 2 同时检查了剩余票数是否 >0 , 然后接下来客户端 1 手速快 , 先把票抢了 , 票就变成 0 了  
然后客户端 2 紧随其后 , 也把票抢了 , 但是此时票已经是 0 了 , 再 -1 就变成 -1 了 , 就发生错误了  
这个现象就叫做 “超卖”

这种问题就是 : 两个事物之间产生了干扰和影响 , 为了避免相互干扰 , 就引入了隔离性 , 通过隔离性来降低上述的影响  
但是存在一种问题 : 我们并发执行是为了提高效率 , 但是引入了隔离性 , 带来的问题就是数据更准了 , 但是效率降低了

为了解决并发执行事务带来的问题 , MySQL 等数据库引入了 “隔离级别” , 可以让用户自行选择一个适合自己当前业务场景的级别

#### 隔离问题 ####

先研究一下 , 并发执行事务的时候还会有哪些问题 ?

##### 脏读 #####

脏数据指的就是数据被污染了 , 不准确了 .  
举个栗子 : 我在写代码 , 在我写的过程中 , 写了个 Student 类 , 写了一些属性和方法  
在我写的过程中 , 室友在我身后暗中观察 , 看了一会就走了  
室友走了之后 , 我就把 Student 里面的实现给改动了  
此时这个同学读到的就是脏数据 , 上述的暗中观察的过程 , 就叫做 “脏读”

按照数据库的说法来说 , 就是 : 一个事务 A , 在执行过程中 , 对数据进行了一系列的修改 , 在提交到数据库之前 (完成事务之前) , 另一个事物 B 读取了对应的数据 , 此时这个事务 B 读到的数据都是一些临时结果 , 后续事务 A 可能马上就把数据修改了 , 此时 B 的读取行为就是 “脏读”

那怎么解决脏读这个问题呢 ?  
和室友约定好 , 我在编写代码的时候大家不要抄 , 我把代码写完发到寝室群的时候 , 你们再去看  
实际上就是给 读操作加锁 了 , 相当于降低了并发程度 , 降低了效率 , 提高了隔离性  
上面的买票的案例就是脏读产生的

##### 不可重复读 #####

事务 A 先提交了代码 , 事务 B 再去读 . 事务 B 读的过程中 , 又开启了事务 A , 又进行了修改呢  
![image.png][image.png 7]  
事务 A 提交了之后 , 事务 B 才开始读 (读是加锁了) , 然后在 B 的执行过程中 , A 又开启了一次 , 修改了数据 . 此时 B 执行中 , 两次读取操作可能结果就不一致 .  
那么怎样解决呢 ?  
我们重新约定 : 发到寝室群之前大家不要读 , 室友读的时候我也不要去修改代码  
经过这个新的约定之后 , 就变成了这样  
![1676871549964-710cb34b-9f89-49eb-9bf2-2bbb349d2510.png][]  
这样的话 , 隔离性又提高了 , 并发性又降低了 . 数据更准确了 , 但是效率更低了

##### 幻读 #####

我们之前约定了在室友读的时候我不能改代码  
那我也不能闲着 , 不让改 Student , 那我就改 Teacher , 互不打扰不也挺好  
但是这样就会存在问题 :  
如果 Teacher 本身就存在 , 也就罢了 .  
如果是在室友读的过程中 , 新增的 Teacher.java 或者删除了一个已有的内容 , 就会产生幻读问题

事务 B 读取过程中 , 事务 A 对其他数据进行了修改 , 但是没有直接修改 B 读取的数据 , 但是影响到了 B 读取的结果集  
事务 B 两次读取到的结果集不一样 , 这个就是"幻读"  
幻读相当于是不可重复读的特殊情况

> 不可重复度偏重于数据内容 , 幻读偏重于数据结果集

那如何解决幻读问题呢 ? 核心思路就是 "串行化 " , 严格要求室友读代码的时候 , 我不能干任何事  
保证读和写都是严格串行执行的 (一个执行完才能执行另一个)  
它的隔离性最高 , 并发程度最低 , 数据的准确性最好 , 但同时也是速度最慢的

--------------------

为啥要实现并发 , 追求效率 ;  
为啥要实现隔离 , 追求准确 ;  
但是鱼和熊掌不能兼得

#### 隔离级别 ####

MySQL 中给我们提供了四个等级 , 让我们自由选择  
read uncommitted : 没提交的数据也能读到 , 并发能力最强 , 隔离性最弱  
read committed : 只能读取提交之后的数据 , 解决了脏读问题 , 并发能力下降 , 隔离性增加  
repeatable read : 针对读和写都进行限制 , 解决了不可重复读问题 , 并发能力进一步下降 , 隔离性进一步增加  
serializable : 串行化 , 严格的串行执行 , 解决了幻读问题 , 并发能力最低 , 隔离级别最高  
可以根据实际需要 , 在配置文件中 (my.ini) 修改数据库的隔离级别

## 三 . 事务的操作 ##

1 : 开启事务 : start transaction;  
2 : 执行多条 SQL 语句  
3 : 回滚或提交 : rollback(全部失败) / commit (全部成功)

在 1~3 的过程中 , 中间的 SQL 就是一个整体  
实际开发的时候 , 很少通过 SQL 代码来写事务 , 更多的是借助其它编程语言来写事务

[65073f2a57364e6e8dcb487617e2182f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/c2337be9e3e340c0941d11d3eb61d689.png
[image.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/9985624724974285a687c855ec1cff18.png
[image.png 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/8edefe24e88241979bd3d25af963ae82.png
[image.png 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/ec061e57ec404171958ac29fcb1f0242.png
[image.png 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/08fc52e377ce4e23b3faa6b050a10939.png
[image.png 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/13820aacf0264b7abfd59fa99692d984.png
[image.png 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/a68f1a7eb6cc4ae6bf28dc380ad0e53a.png
[image.png 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/bfd4edfec0cc4db9b982aa193981e762.png
[image.png 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/1c77ab7cf4b14cdbaf27695e68431667.png
[1676871549964-710cb34b-9f89-49eb-9bf2-2bbb349d2510.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/26/47d0750e31af45eba88778488f0ca27d.png