0


MySQL数据库的索引以及事务详解

课前导读:

本章设计到MySQL数据库的索引和事务操作,索引操作设计的概念内容比较多,但是他涉及到数据库的内部运行效率和使用空间等多方面知识,相比比较重要,也需要进行相关学习。而事务操作更不用多说,是我们进行数据库操作的保障!这方面只是主要是便于面试准备!


一、索引

1、概念

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

2、作用

  • 数据库中的表、数据、索引之间的关系,类似于书架上的图书、书籍内容和书籍目录的关系
  • 索引所起的作用类似书籍目录,可用于快速定位、检索数据
  • 索引对于提高数据库的性能有很大的帮助
  • 对我们的MySQL操作来说索引最大的作用就是提高运行效率,但不是所有索引都能提高效率的

3、索引操作

(1)查看索引:show index from 表名;

例如:show index from adc;

但我们会想,明明没有创建索引为什么能查到索引呢,这时候我们就要看看这个表中的字段约束了。

可以看见id是primary key主键的,idnumber 是唯一的,这就是他们拥有索引的原因了,主键非空且唯一,需要索引进来判断他的唯一性,所以MySQL会自动帮我们创建索引,unique也是这样的。

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

例如:create index name_index on adc(name);

可以看见和上面相比,我们创建了name的索引了,我们就可以通过name来进行查询了。

但是如果表中有很多数据(成千上万)的时候进行索引创建时,是一个危险操作,因为这样创建会吃掉很多磁盘io,会花掉很多时间,但创建的这段时间数据库是无法使用的,会带来很大的损失,所以我们最好在建表时进行索引的创建!

(3)删除索引:drop index 索引名字 on 表名;

例如:drop index name_index on adc;

这个删除操作和创建索引一样是个危险操作,会吃掉大量磁盘io。需要在建表时决定好是否需要这个索引!

4、索引的运行

咱们把索引创建好了之后,不需要手动使用,直接查询的时候就会自动的走索引,sql内部通过数据库的执行引擎来进行执行的,执行引擎会自动评估,哪种成本最低,速度最快~

5、索引的数据结构:B+树

索引的根本目的在于提高查找速度

1、B树的特点:每个数据在他的根节点上,比较次数虽然和二叉树相比没怎么减少(一个节点上可能需要比较多次),但是读写硬盘的次数减少了。(B树的每个节点都是在硬盘上的),但是b树还不够,我们就引入了B+树

2、B+树介绍:B+树也是N叉搜索树,但在这之上又有一些新的特点,B+树就是为了索引这个场景,量身制作的数据结构。

特点:

(1)B+树也是一个N叉搜索树,每个节点上可能包含N个key,N个key划分出N个区间,最后一个key就相当于最大值。

(2)父元素的key会在子元素中重复出现,并且是以最大值的姿态出现的!

这样的重复出现,导致叶子节点就包含了所有数据的全集,非叶子节点中的所有值都会在叶子节点中体现出来!!!

(3)它会把叶子节点用类似链表的方式进行首尾相连~

这样通过类似链表的连接,就可以更有利于我们进行范围查询了!所有的查询,最后都是要落到叶子节点上的,无论查询哪个元素,中间比较的次数都差不多,查询操作比较均衡!

(4)由于所有的key都会在叶子节点中体现,因此非叶子节点,不必存表的真实记录(不必存数据行),只需要把所有的数据行给放到叶子节点上即可,非叶子节点只需要存索引的值

(由于非叶子节点只存了简单id,没有存一整行,这就意味着非叶子节点,占用的空间是大大降低的,有可能在内存中可以放进去缓存,更进一步降低了硬盘的io,提高查询速度!!)

如果大家对数据库索引这方面还想进行更深入了解,可以看以下这本书。


二、事务

1、事务认识:

事务简单理解就是可以把sql给打包到一起,变成一个整体进行操作。

例如转账操作需要双方进行操作,需要让转账人进行-金额,使接收人进行+金额,如果有一个失误,就会出现问题,所以我们需要把他进行一个整形进行操作,同时进行。要么一起执行,要么都不执行。如果事务中间执行失误了,就让一条都不执行,但它并不是没有执行,而是遇到了错误回复到执行之前的样子,看起来好像没执行一样。

2、事务操作:

回滚(rollback):回滚就是把执行过的操作逆向恢复回去。

(数据库会把执行的每个操作记录下来,如果某个数据出错,就会把事务中前面的操作进行回滚,根据之前进行的操作,进行逆操作(前面是插入,现在就删除,前面是删除,现在就插入))。

开启事务:start transaction;

中间写sql语句

commit; (开启事务后,sql不会立即执行,而是攒着等commit一起执行,保证事务的原子性)

rollback 主动回滚事务。

3、事务四大特性:

(1)原子性

事务的初心

(2)一致性

事务执行前后,都得是合法的数据,不能出现失误情况。

(3)持久性

事务操作产生的修改都会写入到硬盘中,就算是关机了,数据一样存在硬盘中。

(4)隔离性

一个数据库服务器,同时执行多个事务的时候,事务之间的相互影响程度。

4、MySQL的隔离性

隔离性详解:mysql服务器如果要同时给多个用户提供服务,此时多个客户端之间,可能会同时发生事务,尤其是这多个事务同时操作同一个数据库的同一个表的时候,就可以有问题。

如果隔离性越高,就意味着事务之间的并发程度越低,执行效率越慢,但是数据的准确性越高。例如:算钱的时候,一定要数据准确

如果隔离性越低,就意味着事务之间的并发程度越高,执行效率越快,但是数据的准确性越低。例如:抖音、b站等的点赞数,需要执行速率快,但不需要太准确。

mysql提供了不同挡位(可以控制隔离性的高低、并发程度的高低、执行效率的高低、数据正确性的高低等)

不同程度遇到的不同问题:

1、脏读(dirty data):读脏数据,意思就是读到了有问题的数据,完全并发的情况下,没有任何限制就会出现脏读问题。

解决办法:降低并发现,提高隔离性,给写"加锁";

2、不可程度读:在一个事务操作中,连续两次读到的数据不一样,就是因为边读数据,边修改数据导致。

解决办法:进一步降低并发性,提高隔离性,给读"加锁";

3、幻读:在同一个事务中,两次读到的结果集不一样,在上面加锁的情况下,写不一样的内容,导致读到的情况不一样

解决问题:串行化,彻底舍弃并发性,实现完全隔离。

MySQL提供了四个隔离级别:

1、read uncommitted 不做任何限制,事务之间都是随意并发的,并发程度最高,隔离性最低,会出现脏读+不可重复读+幻读问题

2、read committed 给写加锁,并发程度降低,隔离性提高,解决了脏读问题,但会出现不可重复读+幻读问题

3、repeatable read 给写和读都加锁,并发程度又降低,隔离性又提高,解决了脏读+不可重复读问题,但会出现幻读问题。

4、serializable 严格串行化,并发程度最低,隔离性最高,解决了脏读+不可重复读+幻读问题,但执行速度时最慢的。

mysql就可以根据具体问题,来决定使用哪个隔离级别,通过mysql配置文件进行调整。


总结:

本篇博客重点在于对索引操作和事务操作的总结和归纳,篇幅又臭又长,但是对面试找工作有很大的帮助,所以我觉得有必要进行相关了解和归纳,希望本博主的内容能对你有用,谢谢!

标签: 数据库

本文转载自: https://blog.csdn.net/qq_62958114/article/details/128265440
版权归原作者 程序员X. 所有, 如有侵权,请联系我们删除。

“MySQL数据库的索引以及事务详解”的评论:

还没有评论