MySQL数据库:索引和事务_mysql数据库索引和事务

在草稿箱中躺了一个月,直到今天,才将它完成,希望对大家有所帮助,如果觉得有用的话,不妨点赞 +收藏 ,你的支持就是对我最大的鼓励 ! (*^▽^*)

目录

1. 索引

1.1 索引的概念

1.2 作用

1.3 使用场景

1.4 索引背后的数据结构(B+树)

2. 事务

2.1 事务的概念

2.2 事务的特性(原子性,一致性,持久性,隔离性)

3. 并发执行事务可能遇到的问题

3.1 脏读

3.2 不可重复读

3.3 幻读

3.4  具体落地解决上面的三种问题(事务隔离级别)

---

1. 索引

1.1 索引的概念

       索引就是目录,目的是为了提高 查询 的效率,但是会拖慢增删改的效率. 创建索引,也需要时间和空间的开销.

1.2 作用

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

       

1.3 使用场景

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

        大部分场景中,查询是高频操作,增删改是低频操作.但是如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引.

1.4 索引背后的数据结构(B+树)

       我们可能不禁会想到,是二叉搜索树吗?是哈希表吗?

       其实都不是.

       为什么不是二叉搜索树?

       虽然红黑树的查找时间复杂度为 log(n) ,但是一旦数据量比较大, log(n) 其实也不算小(此处的每一次基本操作都是读磁盘,索引信息都是存在磁盘里的)

       为什么不是哈希表?

       哈希表虽然比较次数小了,但是只能处理 "相等"的查询,实际上SQL中经常出现范围查询.

       站在索引背后真正的大佬出现!! B+树(有没有一种毕加索的既视感,开个丸笑~~),它不是二叉搜索树,而是一棵n叉搜索树.

1) 在介绍B+树之前,先介绍B树:

       B树,也写作B-树(不是读作)

      节点中存储了数据的完整信息,存的是整个对象.

      特点:

      ① 每个节点上可以有多个数据

      ② 每个节点上的(分叉树)子树的个数,就是该节点存储的数据个数+1(根节点).

      ③ 保证子树节点中的数据的值,要处在父节点的两个值之间.

2) 关于B+树

       B+树来说,子节点中存储的最大值(最小值),是在父节点中出现过的(这样做就能让叶子结点包含整个数据的全集)

       针对叶子结点来说,只是在叶子结点上存储了数据,非叶子节点只包含了索引的信息.

       B+树相比于B树来说,优势就比较明显了:

       ① 更高效的处理范围查找.

       ② 数据存储在叶子节点上,非叶子节点只包含索引列信息(非叶子节点占据的空间就非常小了,可以直接放到内存里).

3) 下面再介绍一下聚簇索引和非聚簇索引:

       聚簇索引: 构建索引的这个树上的每个节点,不光存了索引列的信息,还存了这一条记录的完整内容.

       非聚簇索引: 索引这个树上的每个节点,除了存索引列信息之外,再就是存了该记录的行号(主键id),并没有存完整信息.

2. 事务

       在引入事务的概念之前,我们先来设想一下这样的场景,假如数据库中存有两个人,A和B,他们的账户余额分别为1000和800,现在A给B转账50元.

       转账这个操作,可以分为两步: ① 先把A的余额减50 ② 再把B的余额加50

       本来转的好好的,但是忽然出现了断电/断网/程序崩溃/主机重启等等情况,俗话说,技术再牛逼,也抵不过挖掘机的一铲子啊,此时,只执行了 ①没有执行② 这个时候该怎么办呢?

       为了解决上面的问题,于是引入了事务.

2.1 事务的概念

       事务就是把若干个SQL的操作打包成了一个整体,实际执行的时候,如果这个整体执行了一半,就遇到了突发情况,MySQL就能保证突发情况恢复之后,数据库的数据没有受到破坏.

       不是说一定能保证所有的操作都能一口气执行完,实际上是通过"回滚"(Roll Back)机制来对数据进行还原.MySQL中有一个机制 binlog,记录了每一个MySQL的具体操作,就可以依据这个历史记录,把执行一半的事务给还原回去.

2.2 事务的特性(原子性,一致性,持久性,隔离性)

1) 原子性

        举个栗子:

        创建一个学生表,同时往里插入一些记录,此处在业务中认为创建学生表并插入10个记录,这是一件事,就可以通过事务把多个SQL打包成一个.

        如果这其中的代码执行了一半,Java程序崩溃了,此时就相当于事务没有完全执行完,MySQL就会自动把前面已经执行了的操作进行还原,还原到事务执行之前的模样.

        这么做的目的就是为了保证这些SQL是一个整体,要么全都执行,要么全都不执行,不能存在执行一半,剩一半没执行的中间状态.

2) 一致性

       保证数据库的数据在执行事务之前和之后,都是合理的.

       合理性通过人工约定的,比如可以通过约束实现.假如A只有100块,但是要给B转账200,这个时候就是不合理的,就会回滚到转账之前的状态.      

3) 持久性

       一旦事务执行成功,此时这样的数据就是持久保存在磁盘上,就算重启主机,也不会改变.

4) 隔离性

       考虑多个事务并发执行的时候,多线程.

       MySQL也是支持并发的,可以有多个客户端同时来执行一些SQL,如果是普通的SQL,MySQL自身可以保证并发执行的结果是对的.如果是多个事务(包含多个SQL),此时并发执行事务,就可能出现一些问题,我们下面就来介绍具体都会出现哪些问题.

3. 并发执行事务可能遇到的问题

3.1 脏读

       第一个事务A在修改数据,在事务执行一半的时候,第二个事务B来读取了这个数据,结果事务A 继续执行的时候把刚才的数据给改了,此时B读取到的数据就叫做"脏数据"(错误的数据)

       解决脏读问题的思路为: "写加锁",事务A在修改的时候,事务B不能去读数据,直到事务A把数据修改完了,提交了之后,事务B才能读.

       我在写博客的时候,大家不能看(写加锁)我的草稿,我发布之后,大家点开链接来读,就能避免读取到数据的中间状态.

3.2 不可重复读

       在一个事务中,分两次读数据,读取到的两个结果不一样,就叫不可重复读,这是因为在读的时候,其他的事务又在修改数据了.

       我发布博客之后,有位大佬在读的时候读了一半,一刷新,发现博客内容和刚才的不一样了,就是因为大佬在读博客的过程中,我又提交了新的版本.

       通过"读加锁"来解决不可重复读问题.如果一个事务在读数据的过程中,其他的事务不能修改.

       大佬在读博客的时候,我不能修改内容,必须得等大佬把内容读完,告诉我读完了,我再修改.

引入了读加锁和写加锁,降低了事务之间的并发程度,同时提高了事务之间的隔离性.

3.3 幻读

        如果一个事务A在读取数据的过程中,另外一个事务B对已读取的数据确实没修改,但是可能会对这个表进行插入/删除操作,此时就可能导致A分两次读到的结果集不一样. 

       虽然大佬在读我的第 "3.3幻读" 模块的时候,我没改这部分内容,但是我又新增了一个"3.0求一键三连"的模块,大佬读的时候可能就发现,这咋突然冒出个"求一键三连".

        

       为了解决幻读问题,方案叫做"串行化",事务和事务之间彻底串行执行,不涉及并发了.

3.4  具体落地解决上面的三种问题(事务隔离级别)

并发执行事务会有上面我们说的几种问题,具体怎么解决,可以通过MySQL的事务隔离级别来实现.

       (1) read uncommitted: 没有任何限制,并发程度最高,隔离程度最低.

       (2) read committed: 相当于对写加锁,可以处理脏读问题,但是仍然存在不可重复读和幻读,并发程度降低了,隔离程度提高了.

       (3) repeatable read: 相当于对于写和读都加锁,可以处理脏读和不可重复读问题,但是仍然存在幻读,并发程度降低了,隔离程度提高了.(MySQL的默认级别)

       (4) Serializable: 串行化执行,并发程度最低的,隔离程度最高.