【MySQL】索引事务 (索引 索引使用场景 B+树事务使用 事务的特性 隔离性 并发执行产生的问题和解决方法 脏读问题 不可重复读 幻读 隔离级别 read uncommitted ......)

---

索引

索引 index->目录,索引存在的意义就是为了加快查找速度.(省略了遍历的过程)

查找速度变快了,但是付出了一定的代价:

1. 需要付出额外的空间代价来保存索引数据.
2. 索引可能会拖慢新增,删除,修改的速度.

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

使用场景

查看索引:show index from 表名;

创建索引:create index 索引名 on 表名(列名);

创建索引操作很危险,如果表里的数据很大,这个建立索引的开销也会很大.我们应该创建表之初就把索引设定好,如果表里已经有很多数据了索引就别动了.

删除索引:drop index 索引名 on 表名;

和创建索引类似,删除索引也可能存在风险.

上述操作容易,但是需要理解索引索引背后的数据结构,B+树是我们索引的关键数据结构.

B+树

先了解B树,B树也叫B-树(-是连接符),B树可以认为是一个N叉搜索树,

当节点的子树多了,节点上保存的key多了,意味着在同样key的个数的前提下B树的高度要比二叉搜索树低很多.(树的高度越高访问磁盘的次数就越多)

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

B+树会把叶子结点收尾相连,构成了类似于链表的结构.

B+树的特点:

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

所以整个树的所有数据都包含在叶子节点中(所有非叶子节点中的key最终都会出现在叶子节点中).

B+树的优势:

1. 当前一个节点保存更多的key,最终树的高度是相对更矮的,查询的时候减少了IO访问次数.(和B树是一样的)
2. 所有的查询最终都会落到叶子节点上.(查询任何一个数据,经过的IO访问次数是一样的)
3. B+树的所有的叶子节点,构成了链表,比较方便进行范围查询.
4. 由于数据都在叶子节点上,非叶子节点只存储key,导致非叶子节点总用空间是比较小的,这些非叶子节点就可能在内存那种缓存(或者是缓存一部分),又进一步减少了IO的次数.

如果表里有多个索引?针对id有主键索引,针对name又有一个索引.

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

事务

场景:转账

需求: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的钱没到账.

事务就是为了解决上述的问题,事务的本质就是把多个sql语句打包成一个整体(原子性atom:表示不可分割),要么全部执行成功,要么就一个都不执行(不是真的没执行,而是看起来好像没执行一样,即执行了,执行了一半,出错了,选择恢复现场把数据还原成未执行时的状态了,这个恢复数据的操作叫做"回滚"),而不会出现"执行一半"这样的中间状态.

所以上面的转账场景当第一个sql执行完后数据库崩溃当下次数据库重新启动完成后就会自动的把上次修改一半的数据给进行还原.

那么进行回滚的时候,如何知道回滚恢复到什么状态呢?

此处是有额外的部分来记录事务中的操作步骤.(数据库里专门有个用来记录事务的日志)正因为如此使用事务的时候,执行sql的开销是很大的,效率更低.

事务使用

（1）开启事务：
（2）执行多条SQL语句
（3）回滚或提交：rollback/commit;

start transaction;
-- 个人账户减少2000
update accout set money=money-2000 where name = '个人';
-- 游戏账户增加2000
update accout set money=money+2000 where name = '游戏';
commit;

事务的特性

数据库的事务有四个关键的特性:

1. 原子性.(最核心)
2. 一致性:事务执行的前后数据得是靠谱的.
3. 持久性:事务修改的内容是写到硬盘的,重启也不会丢失.
4. 隔离性.

隔离性

隔离性:

隔离性是为了解决"并发"执行事务引起的问题.

1. 并发:
   并发就是服务器同一时刻给多个客户端提供服务,客户端有可能是一个接一个提出请求也可能是一起提出请求,这种场景下服务器同事处理多个客户端的请求就称为"并发".数据库也是服务器,就可能有多个客户端都给数据库提交事务,数据库就需要并发处理事务.
2. 执行事务引起的问题
   如果并发的事务是修改不同的表/不同的数据此时不会出现问题,但是如果修改的是同一张表/同一个数据,可能就会出现一定的问题.比如:多个客户端一起尝试对同一个账户进行转账操作,可能会把这个数据给弄乱了.

事务的隔离性存在的意义就是为了在数据库并发处理事务的时候不会有问题,即使有问题,也会让问题不大.

并发执行产生的问题和解决方法

脏读问题

脏读问题:

一个事务A正在对数据进行修改的过程中,还没提交之前,另一个事务B也对同一个数据进行了读取,此时B的读操作就称为"脏读",读到的数据也称为"脏数据"(脏的意思就是无效),因为A回头可能把数据又改了.

解决方法:

为了解决脏读问题,mysql引入"写操作加锁"这样的机制,即事务A在改数据的时候事务B不能看,等事务A写完了事务B才能看.也就是"写操作"和"读操作"不能并发了,这个写加锁操作,降低了并发程度(降低了效率),提高了隔离性(提高了数据的准确性)

不可重复读

不可重复读:

事务A提交了数据,此时为版本1,然后事务B就开始读取这个数据,于是事务C又修改数据,修改完成后提交版本2,事务B本来在读版本1的数据,读着读着数据变样了突然变成了版本2,此时同一个事务2之内多次读数据,读出的结果是不同的(预期是一个事务总多次读取结果是一样的)这样的问题就叫做"不可重复读"(第二次读取的结果不能复现第一次的结果).

解决方法:

"读加锁"通过读加锁又进一步减低了事务的并发处理能力(处理效率降低),提高了事务的隔离性.(数据的准确性又提高了)

幻读

幻读:

在读加锁和写加锁的前提下,一个事务两次读取同一个数据,发现读取的数据值是一样的,但是结果集不一样.(Student.java代码内容不变,第一次读取到的只有Student.java,第二次却读取到了Student.java和Teacher.java了)这种就称为"幻读".

解决方法:

数据库使用"串行化"这样的方式来解决幻读,彻底放弃并发处理事务,一个接一个的串行的处理事务.这样做并发程度是最低的(效率最慢),隔离性是最高的(准确性也是最高的).

隔离级别

对应上述问题mysql提供了4种隔离级别对应上面的几个情况.

read uncommitted

read uncommitted:没有任何限制,并发最高(效率最高)隔离性最低(准确性最低).

read committed

read committed:给写加锁了,并发程度降低,隔离性提高了.

repeatable read

repeatable read:给写和读都加锁了,并发程度又降低,隔离性又提高了.

serializable

serializable:彻底串行化,并发程度最低,隔离性最高.

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

上面的级别没有好坏,根据业务在准确性和效率之间进行权衡.比如转账的时候,钱不能差,即使慢一点但是准确性要拉满,效率不是关键.还有抖音点赞显示,一个视频有多少赞要求要快,赞的数量差个是个八个都没事,此时追求的效率,准确性不关键.