MySQL索引事务

最新推荐文章于 2024-03-21 20:27:45 发布

努力学习.java

最新推荐文章于 2024-03-21 20:27:45 发布

阅读量605

点赞数 41

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_61688804/article/details/126221391

版权

MySQL 专栏收录该内容

2 篇文章 1 订阅

订阅专栏

在这里插入图片描述

🎉🎉🎉写在前面：
博主主页：🌹🌹🌹戳一戳，欢迎大佬指点！
博主秋秋：QQ：1477649017 欢迎志同道合的朋友一起加油喔💪
目标梦想：进大厂，立志成为一个牛掰的Java程序猿，虽然现在还是一个小菜鸟嘿嘿
-----------------------------谢谢你这么帅气美丽还给我点赞！比个心-----------------------------

一，索引

1，什么是索引

索引是一种特殊的文件，它包含着对数据表里面的所有记录的引用指针

可以对表中的一列或者是多列创建索引，并指定索引类型，各类索引背后有自己专属的数据结构支撑。

2，索引的作用

索引就类似于我们书的目录，可以快速定位我们的数据，大大提高我们查询的效率。

当我们没有使用索引的时候，如果想要查询一条记录，那就需要顺序遍历整个数据表，一行行遍历，然后一列列的去对比我们的要求，最终找到想要查询的记录。但是当我们引用索引后，我们就可以快速定位到我们的指定列的信息，进而找到我们的这条列信息所在行。运用索引查询的效率会高很多。

3，使用场景

我们常说，有舍有得，那么对于索引也是一样，我们在获得高效的查询速度的同时，也付出了相应的代价。

1，索引既然是文件，那么自然是要额外消耗我们的磁盘空间的。

2，虽然提高了查询效率，但是降低了增，删，改的效率，因为这个时候除了要修改数据外，还要调整我们的索引。

但是尽管存在上述的问题，索引还是很值得我们使用的，因为在实际工作中，查询操作的频率会高于我们的增删改。

说完了索引所带来的一些负面的影响，那么它的使用场景自然就是要尽量避开这些…

所以，当我们是非条件查询列，或者经常做增删改而不是查询，又或者磁盘空间不是很足的情况下，都是不建议创建索引的。

4，索引的使用

对于一个表而言，当我们定义主键约束，唯一约束，外键约束的时候会自动给该列创建索引。

//1，创建索引
create index 索引名 on 表名(列名);

对于创建索引的这个操作，其实也是很低效的，因为它需要创建出对应的数据结构。所以对于一些数据量比较大的表，在实际开发环境中，一定要在开始就把索引规划好，一些需要创建索引的列最开始就创建好，后面插入数据同步更新索引就好。不要等到有了一定数据量之后，再来创建索引，这个时候效率低是一回事，也有可能会把服务器卡死之类的。

//2，查看索引
show index from 表名;

//3，删除索引
drop index 索引名 on 表名;

5，背后数据结构[面试，重点]

我们说索引是为了提高我们的查询效率，那么其背后的数据结构肯定是适合查找操作的，目前可能我们能想到的适合查找操作的数据结构有二叉搜索树，哈希表，那是不是呢？

1，哈希表

对于哈希表而言，如果是查找相等的数据，查找时间复杂度确实是O(1)，但是如果是> < between and
这些范围查找，它是做不到的，所以它不合适做索引的数据结构。

2，二叉搜索树

普通的二叉搜索树是有可能会出现单分支的情况的，所以后面就有了AVL树，红黑树（比较平衡的二叉搜索树），把二叉搜索树的查询时间复杂度最坏情况降到了O(logn)。但是数据库肯定是会存放很多数据的，那么一旦数据太多，一个节点存放一个数据，树的高度就会很高，那么查询的次数就会相应的变多，不巧的是数据库的数据都是存放在硬盘上的，所以每次查询都是要去读取硬盘的，读取硬盘的速度又比较低，所以就显得不是很适合了。

所以既然没有现成的适合的数据结构，那么就只有创造一个专门的数据结构了，也就有了B+树。

了解B+树之前，我们先看看B树是啥？

B树又可以叫做N叉搜索树，每个节点上最多可能会有N-1个值，N-1个值，那就把这个节点划分为了N个区间，每一个区间下，又会衍生出子节点，子节点的值就在这个范围内。这种N叉树的结构，一个节点可以存多个值，相对于二叉搜索树，把树的高度大大降低，查询次数也就会相应降低，降低了硬盘IO。

但是为什么不选择B树呢，因为它涉及到一个分裂和合并的过程，如果说你插入了值，那么可能范围符合但是该节点已经放满，所以可能会需要分裂出一个新的节点来进行存放。同理删除值后如果某个节点的元素个数太少，符合范围条件的节点需要进行合并，整个过程的操作比较复杂。

B+树：

对于B+树来说，也是一个N叉搜索树，只不过每个节点上就N个值，分出N个区间，并且父节点的元素会在子节点中以最大值或者最小值的形式出现在子节点中。最后最重要的一个特点，在最底层的叶子节点上，B+树会把所有的叶子节点以链表的形式连接起来。其实我们会发现，B+树的叶子节点上，已经保存了整棵树所有的值，并且是排序好的。所以这个时候，对于我们的范围查找就很友好了，只需要去遍历链表，然后根据范围截取出子链表就好了。

最后，最厉害的一个点，既然我们的叶子节点是全集数据，所以我们就只需要把我们的每一行记录关联到每一个叶子节点上，然后非叶子节点上就只存放索引列的值，所以相应的非叶子节点占用的空间就比较小，这个时候就可以直接把非叶子节点缓存到内存里面，然后直接在内存里面去遍历查找我们的非叶子节点(索引列)，找到相应的范围之后去硬盘访问相应的记录即可，这个时候相对于B树，查找速度又加快了，硬盘IO也进一步得到了降低。

二，事务

1，什么是事务

科学家刚发现原子的时候曾说原子不可再分(虽然后被推翻)，但是后面计算机里面就沿用了这个说法，用来表示一些不能再分割的基本单位。

那么在数据库里面，我们希望有些操作能够以原子的方式进行，要么都能执行成功，要么就都不执行，也就是只能是一个整体的被执行，这样的一组具有原子性的操作我们就称之为事务。事务就是来保证原子性的，原子性就是事务的核心。

2，为什么使用事务

我们想象一个场景，假如你在某个小店里买东西，然后在你手机支付付款成功的时候，某企鹅公司的服务器突然挂掉了(纯属假设啊)，导致商家那边没有收到款，但是你这边属实是已经付出去了，这个时候你就拼命的给商家解释呀，但是商家却只认为你是一个大骗子！你是解释也解释不清…

所以，为了防止此类问题的出现，就有了事务，我们可以把转账付款之类的操作使用事务来进行控制，要么操作都能被执行，也就是转账与另一方收款都正常；要么就都不执行，就是中间出现问题，数据会回滚到最初的状态。

总结，事务存在的意义就是能够使得我们多个数据库操作(SQL语句)能够以原子的方式去执行。在事务的执行过程中，MySQL会记录每一步操作都干了啥，当执行过程中出现问题的时候，我们的回滚机制rollback可以让我们的数据恢复如初，也就是相当于没有执行过一样。

3，使用

start transaction;//开始事务
//中间执行sql
-- 小王账户减少100
update accout set money=money-100 where name = '小王';
-- 小明账户增加100
update accout set money=money+100 where name = '小明';
//最后提交或者是回滚
commit/rollback;

这只是简单的介绍下相关的sql语句，一般事务都是用代码来进行操作的。

4，事务的特性[面试，重点]

4.1，原子性

事物的根本所在，能够将多个SQL打包成一个整体，要么就都执行成功，要么就都不执行。

4.2，一致性

事务在执行前后，能够保证数据是一致的状态，即所有的操作与结果都能对的上。

4.3，持久性

事务对于数据的操作，都是实打实的修改了硬盘数据的，具有持久的效果。

4.4，隔离性(最复杂)

当多个事务在并发执行的时候，他们之间能够保证隔离，也就是相互不影响。

并发性：我们对于数据库服务器的操作也是依靠着客户端的指令来进行的，那么如果出现多个客户端同时对数据库发了事务让它执行，那么此时就会并发执行，也就是要同时做多件事情。

服务器并发执行事务，如果说操作的是不同的数据库不同的表那就还好，如果是操作的同一个数据库同一张表，那么就会出现冲突了。我们的隔离性也就是为了解决这些冲突，让事务在并发执行的时候，尽量不出问题或者把影响降低到最小。

并发过程中可能出现的几种问题：【注意下面的读取数据，修改数据针对的是记录与记录之间】

【1，脏读问题】

假设有两个事务，事务A在修改数据，事务B同时在读取数据，那么在事务A提交修改之前，可能事务B就已经读取完毕，但是事务A后面又进行了修改，所以事务B读取到的数据就不是最终的修改版本，也就是相当于读到了“脏"数据。

那如何解决脏读问题呢？

写操作加锁，也就是约定好在事务A在进行写操作的时候，事务B不能读取数据，只能等到事务A修改完毕确定提交之后，事务B才能去读取数据。执行写操作枷锁之后，并发性降低(效率降低)，隔离性提高(数据准确性提高)。

【2，不可重复读】

之前事务A与事务B已经约定好了事务A在修改的时候事务B不能读取数据，但是这只是限定了我在写的时候你不能读。现在可能存在这样一种情况，事务A提交修改，事务B开始读取数据，但是事务A在事务B读取的过程中，同时来进行修改，这就导致事务B在读取的时候突然发现数据变了，也就是对于同一个数据可能两次读取到的不一样，不能重复的读取。

那么如何解决不可重复读的问题呢？

读操作枷锁，也就是约定好事务B在进行读取的时候，事务A不能进行修改，也即是达到在同一个事务里面，多次读取同一个数据的结果应该一样的这么一个要求。
执行读操作加锁之后，加上之前的读操作加锁，并发性就进一步的降低，隔离性进一步的提高。

【3，幻读问题】

幻读是不可重复读的一种特殊情况。解决不可重复读已经要求了事务B在读数据的时候事务A不能修改数据，那么现在事务A闲不住，去增加删除修改别的记录的信息，但是等事务B再次读取的时候会发现表中的数据个数变了，也就是结果集变了。实例体现的话就是当一个事务去条件查询某个数据发现不存在，但是执行插入的时候又插入不进去说已存在该数据，前后就好像出现了幻影的感觉。

那么如何解决幻读的问题呢？