开开心心带你学习MySQL数据库之第八篇

索引和事务
~~ 数据库运行的原理知识 + 面试题

索引

索引(index) => 目录
索引存在的意义,就是为了加快查找速度!!(省略了遍历的过程)
查找速度是快了，但是付出了一定的代价!!
1.需要付出额外的空间代价来保存索引数据
2.索引可能会拖慢新增,删除,修改的速度
~~ 以写小论文为例
论文最前面要生成目录(word,能自动生成目录)
如果word没有这个功能,手动维护,一定会非常麻烦的

整体来说,还是认为索引是利大于弊的~~
实际开发中,查询场景一般要比增删改频率高很多!!!

索引的使用

~~ 查看索引
show index from 表名;

~~ 创建索引
create index 索引名 on 表名(列名);
创建主键约束(PRIMARY KEY),唯一约束(UNIQUE),外键约束(FOREIGN KEY)时,会自动创建对应列的索引
创建索引操作,可能很危险!!!
如果表里的数据很大, 这个建立索引的开销也会很大!!!
比如有一本很厚的书,现在让你给这个书手动的弄一份目录出来~~ 工作量巨大
~~ 好的做法,是创建表之初就把索引设定好
~~ 如果表里已经有很多数据,索引就别动了

~~ 删除索引
drop index 索引名 on 表名;
和创建索引类似,删除索引也可能存在风险!!!

探索索引背后的数据结构

可以用来加快查询的数据结构?
~~ 二叉搜索树
~~ 哈希表
实际上, MySQL并非使用以上的两个数据结构
原因:
二叉搜索树 => 如果元素个数多了,树的高度就会比较高~~ 树的高度相当于比较次数,对于数据库来说,则是IO访问次数,数据库里的数据在硬盘上.
哈希表 => 哈希表虽然查询的快,但是不能支持范围查询,也不能支持模糊匹配
B+ 树 => 索引的关键结构 ~~为了数据库索引,量身定做的数据结构

B+ 树
先了解B树,再来了解B+ 树

B树

B树,也叫做B-树~~ 此处–是“连接符",不是减号!!!千万不要念成B减树
B树可以认为是一个N叉搜索树,与二叉搜索树(二叉搜索树的度为2)进行类比了解
树的度 => 所有节点的度的最大值~~ 节点的度是有几个孩子(孩子节点个数)

当节点的子树多了,节点上保存的key多了意味着在同样key 的个数的前提下B 树的高度就要比二叉搜索树低很多!!
树的高度越高,进行查询比较的时候访问磁盘的次数就越多!!!效率就越低了

B+ 树

B+ 树,在B树的基础上又做出改进 ~~(也是N叉搜索树)

B+树的特点
1.一个节点,可以存储N个key,N个key划分出了N个区间~~(而不是N+1个区间).
2.每个节点中的 key 的值,都会在子节点中也存在(同时该key是子节点的最大值).
3.B+树的叶子节点,是首尾相连,类似于一个链表.
~~ 由于B+树特点1,2,3导致整个树的所有数据都是包含在叶子节点中的,(所有非叶子节点中的key最终都会出现在叶子节点中).
4.由于叶子节点,是完整的数据集合,只在叶子节点这里存储数据表的每一行的数据.而非叶子节点,只存key值本身即可.

B+树的优势
1.当前一个节点保存更多的key,最终树的高度是相对更矮的.查询的时候减少了IO(这里IO特指硬盘的访问)访问次数.(和B树是一样的).
2.所有的查询最终都会落到叶子节点上.(查询任何一个数据,经过的IO访问次数,是一样的) => 稳定 ~~ 这个稳定,是很关键的,稳定能够让程序猿对于程序的执行效率有一个更准确的评估
3.B+树的所有的叶子节点,构成链表,此时比较方便进行范围查询
4.由于数据都在叶子节点上,非叶子节点，只存储key,导致非叶子节点，占用空间是比较小的~~这些非叶子节点就可能在内存中缓存(或者是缓存一部分).又进一步减少了IO的次数!

如果这个表里有多个索引呢?针对id有主键索引,针对name又有一个索引表的数据
~~ 还是按照id 为主键,构建出B+树通过叶子节点组织所有的数据行
~~ 其次,针对 name 这一列,会构建另外一个B+树但是这个B+树的叶子节点就不再存储这一行的完整数据,而是存主键id 是啥
~~ 此时,如果你根据 name 来查询,查到叶子节点得到的只是主键 id,还需要再通过主键 id 去主键的B+树里再查一次~~ (查两次B+树)
(上述过程称为"回表",这个过程,都是MySQL 自动完成的,用户感知不到)

面试八股文
~~对于实际工作,用处不大(工作中不会让你实现一个B+树的),但是面试老考(只能尽量掌握)

事务

经典场景: 转账
account(id, balance)
id balance
1 1000
2 0
1给2转账500
步骤:
1)update account set balance = balance - 500 where id = 1;
2)update account set balance = balance + 500 where id = 2;
假设,在执行转账过程中,执行完1之后,数据库崩溃了/主机宕机此时这个转账就僵硬了!
~~ 1的钱扣了,但是2的钱没到账!
事务就是为了解决上述的问题
~~ 事务的本质就是把多个sqI 语句给打包成一个整体
~~ 要么全都执行成功,要么就一个都不执行,而不会出现"执行一半"这样的中间状态!!!

淘宝买东西~~ 下单
~~ 下订单的同时,要进行支付
~~ 你这边账户扣钱了,同时订单表中也会生成一个对应的数据

“把多个sqI 语句给打包成一个整体” => 事务的原子性(atom) => 计算机中不可分割的基本单位
“一个都不执行” => 不是真的没执行,而是"看起来好像没执行一样" => 具体是执行了,执行一半出错了,出错之后,选择了恢复现场,把数据还原成未执行之前的状态了,类似于ctrl+z ~~ 这个恢复数据的操作,称为“回滚" (rollback)

进行回滚的时候,是怎么知道回滚是恢复到啥样的状态呢??
此处是需要额外的部分来记录事务中的操作步骤(数据库里专门有个用来记录事务的日志)
正因为如此,使用事务的时候,执行sql的开销是更大的,效率是更低的!!!
~~ 只要是执行失败,都会触发回滚

使用

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

-- 开启事务
start transaction;-- 中间就是要执行的每一步操作
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;-- 提交事务,到这一步,相当于事务就执行完了
commit;

事务使用起来容易,理解起来困难
⭐️⭐️⭐️数据库的事务,有四个关键的特性(面试八股文,~~ 经典面试题):
1.原子性~~ (事务最核心的特性)~~ 事务的初心
2.一致性~~ 事务执行前后,数据得是靠谱的 ~~
3.持久性~~ 事务修改的内容是写到硬盘上的,持久存在的,重启也不丢失
4.隔离性⭐️⭐️⭐️⭐️⭐️
~~ 这个隔离性是为了解决"并发"执行事务,引起的问题
例子:一个餐馆(服务器),同一时刻要给多个顾客(客户端)提供服务
这些顾客提出的请求,是"一个接一个"的来的嘛?还是一股脑一起来了一波?? => 都有可能
“一股脑一起来了一波“ => 此时,服务器同时处理多个客户端的请求,就称为“并发"(齐头并进的感觉)
~~ 数据库也是服务器,就有可能多个客户端都给服务器提交事务
~~ 数据库就需要并发的处理多个事务
~~ 如果并发的这些事务,是修改不同的表/不同的数据,没啥事~~
~~ 如果修改的是同一个表/同一个数据 => 可能会带来一定的问题的!!!~~
~~ 比如多个客户端一起尝试对同一个账户进行转账操作,此时就可能会把这个数据给搞乱了
事务的隔离性,存在的意义就是为了在数据库并发处理事务的时候不会有问题(即使有问题,问题也不大)
那么并发处理事务,可能会有哪些问题.以及这些问题数据库的隔离性是怎样解决的~~ (挺麻烦的)

并发执行事务可能产生的问题

—.脏读问题

场景: 我在图书馆写代码(上课要交的作业),在我写的过程中,有一个同班同学,在我身后经过,他喵了一眼我的屏幕~~ 看到了我的代码中写了一些内容,然后他就走了~~ 他走了之后,可能会出现~~ 我发现我的代码写的有问题,不符合题意,又改了~~ 又或者我写的代码没有问题,又没改.
一个事务A正在对数据进行修改的过程中,还没提交之前,另外一个事务B,也对同一个数据进行了读取.此时B的读操作就称为“脏读"，读到的数据也称为“脏数据”~~ 脏的意思,是"无效",不是埋汰的意思.
~~ 为啥说无效?很可能,A回头又把数据给改了.

为了解决脏读问题, mysql 引入"写操作加锁"这样的机制
大概场景就是: 我和同学商量好~~ 我写代码的过程中,你别来看~~ 等我改完,提交到码云上,你再通过我的码云来看 => 写的时候不能看(给写操作加锁)，写完了才能看.
当我写的时候,同学没法读,意味着我的"写操作"和同学的"读操作"不能并发了~~(不能同时执行了).
这个给写加锁操作,就降低了并发程度(降低了效率),提高了隔离性(提高了数据的准确性).

二.不可重复读

还是我写代码,同学想看一看怎么做
~~ 约定好,我写的时候,不许看,等我提交了,再通过码云来看(约定好写加锁了)
~~ 我写代码,提交了版本1.此时就有同学开始读这个代码了
~~ 突然发现题目还存在更优解,于是我又打开代码,继续修改代码,然后又提交版本2
~~ 这个同学开始读的过程中,读到的是版本1的代码,读着读着,我提交了版本2,此时这个同学读的代码,刷的一下变样了!!这个问题,叫做"不可重复读" => 第一次和第二次读到的结果不一样.
事务1已经提交了数据.此时事务2开始去读取数据.在读取过程中,事务3又提交了新的数据.
此时意味着同一个事务2之内,多次读数据,读出来的结果是不相同的~~ (预期是一个事务中,多次读取结果得一样)就叫做"不可重复读”~~ 第二次读取的结果不能复现第一次的结果 => 好比看微信余额,第一次看一眼,发现余额是这些,过几秒啥也没做再看一眼,第二次看一眼的时候,余额就变了,钱没了,这就出大问题了,必须确保两次查看时余额不变,才符合现实生活.
~~ 同学发现了这个问题之后,知道了是在他读的过程中,我又改代码了,于是来找我,和我约定
~~ 他/她读代码的时候,我也不能修改!!!
~~ 刚才约定的是,我修改的时侯,提交之前,同学不要读,是给写加锁
~~ 现在是约定,同学读的时候,我不能修改,就是给读加锁
~~ 在同学读完了之后,我就可以修改了,即加锁之后就会进行解锁
通过这个读加锁，又进一步的降低了事务的并发处理能力(处理效率也降低了),提高了事务的隔离性(数据的准确性又提高了)

三.幻读

当前已经约定了读加锁和写加锁,解决了不可重复读和脏读问题
由于约定了读加锁,同学读的时候,我不能改代码了
~~ 我在这干的等着??光摸鱼打王者,有点难受(主要是玩这游戏,输多赢少,本就不太爱玩了,恰好又输了一把)
~~ 所以我想了办法,同学读Student.java,那好,我就创建一个Teacher.java,就写这个代码呗!!
~~ 这样的情况,大多数情况下都没事,少数情况下,个别同学发现了,读代码读着突然多出个Teacher.java这个文件,有的同学就觉得接收不了
在读加锁和写加锁的前提下,一个事务两次读取同一个数据,发现读取的数据值是一样的,但是结果集不一样
~~ (Studentjava 代码内容不变,但是第一次看到的是只有 Student.java 这个文件,第二次看到的是 Student.java 和Teacher.java 这两个文件了…),这种就称为"幻读’.

数据库使用"串行化"这样的方式来解决幻读.彻底放弃并发处理事务,一个接一个的串行的处理事务.这样做,并发程度是最低的(效率最慢的),隔离性是最高的(准确性也是最高的)
~~ 相当于是同学们要求,在他们读代码的时候,我不要摸电脑,必须强制摸鱼打王者!!

上述三个问题脏读,不可重复读,幻读就是并发处理事务的三个典型问题
对应上述问题, mysql提供了4种隔离级别,就对应上面的几个情况
以下是四种隔离级别

read uncommitted	没有进行任何锁限制,并发最高(效率最高),隔离性最低(准确性低)
read committed	给写加锁了,并发程度降低,隔离性提高了
repeatable read	给写和读都加锁,并发程度又降低,隔离性又提高了
serializable	串行化,并发程度最低,隔离性最高

脏读	给写加锁	read committed	并发程度降低,隔离性提高了
不可重复读	给读加锁	repeatable read	并发程度又降低,隔离性又提高了
幻读	彻底串行化	serializable	并发程度最低,隔离性最高

四种隔离级别是mysqI 内置的机制,
~~ 可以通过修改mysqI的配置文件, 来设置当前 mysql 工作在哪种状态下

这几个隔离级别,如何选择???
~~ 这几个级别没有好坏,在准确性和效率之间进行权衡
~~ 看实际需求,看业务场景
~~ 转账的时候,一分钱都不能差,哪怕慢点,也得转对!!!此时准确性要拉满,效率不关键
~~ 抖音,点赞,一个视频有多少赞,要求快,赞的数量差个十个八个,都没事,此时追求的是效率,准确性就不关键
级别是mysqI 内置的机制,
~~ 可以通过修改mysqI的配置文件, 来设置当前 mysql 工作在哪种状态下