MySQL 索引和事务_Lockey-s

网络投稿 02-07 5942

📢博客主页：Lockey-s 📢欢迎点赞 👍 收藏 ?留言 📝 欢迎讨论！ 📢本文由【Lockey-s】原创！首发于 CSDN🙉🙉🙉 📢 由于博主是在学小白一枚，难免会有错误，有任何问题欢迎评论区留言指出，感激不尽！? 📖精品专栏（不定时更新）?

索引和事务索引概念作用索引的操作查看索引创建索引删除索引索引背后的数据结构事务作用举例事务的使用事务的基本特征脏读第一种情况（写的过程中不能读）处理脏读脏读的第二种情况（不可重复读）幻读问题MySQL 中事务的隔离级别

索引概念

索引是一种特殊的文件，包含对数据表里面所有数据的引用指针，可以对创建的表里面的的一列或多列创建索引。

作用索引的主要作用就是进行查找，提高查找效率。查找效率提高了，但是同时也会付出代价。数据库的索引，也是需要消耗一些额外存储空间的，数据量越大，消耗的额外空间就越多。索引确定之后，后续每次对内容进行增删改的时候，往往也需要同步的调整索引的结构。

索引带来的好处：提高了查找速度。

索引带来的坏处：占用过多的空间，拖慢了增删改的速度。

索引的操作

以之前建的这张学生表为例，这张表我们是没有创建索引的：

查看索引

通过 show index from 表名; 即可查看索引，代码和运行结果如下：

show index from student;

可以看到我们并没有创建索引，但还有显示了索引。这个自带的索引就是主键约束：primary key 带来的，有了主键，MySQL 就会自动创建索引。

不仅主键会自带索引，unique 也是自带索引的

创建索引

就是给一个表创建索引，通过：create index 索引名字 on 表名(列名); 来创建索引。代码如下：

create index name_index on student(name);

运行结果如下：

这里就可以看到我们新创建的索引，不过要注意的是创建索引是一个非常低效的事情，尤其是表里面已经有很多数据的时候，针对服务器上的数据库，如果里面的表没有索引，就不要轻易去创建索引了。不然可能会导致数据库崩溃。

删除索引

删除索引的时候，通过：**drop index 索引名字 on 表名; ** 来删除索引。不过要注意的是，删除索引的时候，如果是数据量很大的数据库，也可能导致数据库崩溃，所以在创建、删除索引的时候，应该在数据库刚开始就调整。代码和结果如下：

drop index name_index on student;

运行结果如下：可以看到我们之前创建的索引现在已经被删除了。

索引背后的数据结构

索引可以很大程度上的加快查找速度，所以索引背后的数据结构就是为了加快查找速度的。也就是使用多叉搜索树，因为这样的话树的高度就下降了，搜索速度就快了。在数据库当中的数据结构使用的是 B+树（最常见的数据结构，就是在 B树的基础上又得到了提升），在索引当中也写出来了： B+树，每个节点上都包含多个 key 值，每个节点有 N 个 key，就又分为了 N 个区间，父节点的值都会在子节点中体现。非叶子节点的每个值，最终都会在叶子节点中体现出来，父节点中的值，会作为子结点中的最大值（最小值），这个图是最大值的情况，最下面的叶子节点，就使用链表进行按顺序连接：

因为使用 B+树进行查找的时候，整体的IO次数也会比较少所有的查询最终都会落到叶子节点上，所以每次查询的IO次数都是差不多的，而且速度也稳定。下面的叶子结点用链表连接之后，进行范围查找就很快所有的数据存储（载荷），都是放在叶子节点上的，非叶子节点中只保存 key 值即可，所以非叶子接单整体占有的空间较小，甚至可以缓存到内存当中。事务作用

事务的作用就是包围了吧若干个独立的操作给打包成一个整体，使其不能分开工作。这种就叫原子性。就是，要么不执行，要么全执行。

举例

A 给 B 转账 500元，如下图表示：如果在执行 SQL 的时候，执行完第一个 SQL，执行第二个 SQL 的时候，数据库崩了、断电了、程序崩了，那么就执行不了第二个 SQL 了。事务就是针对这种情况产生的，发生这种情况的话，由数据库自动执行一些还原性的操作，来消除第一条 SQL 语句带来的影响。

事务的使用通过 start transaction; 来开启事务。执行多条 SQL 语句回滚或提交：rollback/commit; rollback 即是全部失败（也就是回滚），commit 即是全部成功。事务的基本特征原子性。一致性，就是在事务执行之前，和执行之后，数据库中的数据都得是合理合法的。就像转账之后，账户余额不能为负数。持久性，事务一旦提交之后，就持久化存储起来了。隔离性：就是事务并发执行的时候，产生的情况。脏读第一种情况（写的过程中不能读）

脏读就是事务 A 对某个塑胶进行修改的同时，事务 B 去读取了这个数据，也就是读到的是一个临时的结果，而不是最终结果。就像是现在的 “多人填表” 可能 A 填完之后，B 再去修改 A 的数据，A 填的就可能只是一个临时数据。临时数据就是脏数据。出现脏读的原因就是：事务和事务之间，没有任何的隔离，加上了一些约束限制，就可以有效的避免脏读问题

处理脏读

给写操作加锁，在修改的过程中，别人就不能读了（加锁的状态），等修改完之后，别人才能读（接触加锁）。一旦加了写锁之后，意味着事务之间的隔离性就高了，并发性就降低了。

脏读的第二种情况（不可重复读）

就像是我们在 GitHub 上面提交代码，然后其他人通过 GitHub 来读代码，就像下图这样：在提交第二次代码之前的前四个读操作，读到的都是旧代码，然后最后一次读代码读到的就是第二次提交的新代码：为了避免发生这种情况，读操作的时候，也就不能执行写操作了。通过给读的时候也加锁，就解决了不可重复读的问题。这样的话，就会让事务的隔离性更高，但是并发性也就更低了。

幻读问题

就像脏读的第二种情况，如果加锁后，剩下的时间还可以用来做其他。比如对另外一个表进行修改，可以很大程度提高资源的利用率，如下图所示：这样操作之后，锁的力度就不会太大。就像疫情封控，如果有一个人确诊，只分一栋楼就行了，如果确诊多了，就封一个小区。但是这样读的时候，发现数量变了，本来只有一个代码文件，后来又多了一个。也就是一个事务在执行的时候进行多次查询，多次查询的结果不一样（多了或者少了），这也是一种特殊的不可重复读，彻底解决的这种问题，就是进行串行化执行，就是在读操作的时候，就不能写了。隔离性最高，并发性最低，数据最可靠，速度最慢。

MySQL 中事务的隔离级别

可以根据实际要求来调整数据库的隔离级别，通过不同的隔离级别，也就控制了事物之间的隔离性，也就控制了并发程度。

read uncommitted 允许读取未提交的数据，并发程度最高，隔离程度最低。会引入脏读+不可重复读+幻读问题。read committed 只允许读取提交之后的数据，相当于写加锁，并发程度降低一些，隔离程度高了一些，解决了脏读，会引入不可重复读+幻读。repeatable read 相当于给读和写都加锁，并发程度降低了，隔离程度又提高了，解决了脏读和不可重复读，会引入幻读。serializable 串行化，并发程度最低，隔离程度最高，解决了脏读，不可重复读，寒毒问题，但是执行速度最慢。

可以通过修改 my.ini 这个配置文件，来设置当前的隔离级别，根据实际需求场景，来决定使用哪种隔离级别。