MySQL 索引和事务

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

索引和事务

索引

概念

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

作用

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

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

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

索引的操作

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

查看索引

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

showindexfrom student;

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

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

创建索引

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

createindex name_index on student(name);

运行结果如下：

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

删除索引

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

dropindex name_index on student;

运行结果如下：

可以看到我们之前创建的索引现在已经被删除了。

索引背后的数据结构

索引可以很大程度上的加快查找速度，所以索引背后的数据结构就是为了加快查找速度的。也就是使用多叉搜索树，因为这样的话树的高度就下降了，搜索速度就快了。在数据库当中的数据结构使用的是 B+树（最常见的数据结构，就是在 B树 的基础上又得到了提升），在索引当中也写出来了：

B+树，每个节点上都包含多个 key 值，每个节点有 N 个 key，就又分为了 N 个区间，父节点的值都会在子节点中体现。非叶子节点的每个值，最终都会在叶子节点中体现出来，父节点中的值，会作为子结点中的最大值（最小值），这个图是最大值的情况，最下面的叶子节点，就使用链表进行按顺序连接：

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

事务

作用

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

举例

A 给 B 转账 500元，如下图表示：

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

事务的使用

1. 通过 start transaction; 来开启事务。
2. 执行多条 SQL 语句
3. 回滚或提交：rollback/commit;*rollback 即是全部失败（也就是回滚），*commit 即是全部成功。

事务的基本特征

1. 原子性。
2. 一致性，就是在事务执行之前，和执行之后，数据库中的数据都得是合理合法的。就像转账之后，账户余额不能为负数。
3. 持久性，事务一旦提交之后，就持久化存储起来了。
4. 隔离性：就是事务并发执行的时候，产生的情况。

脏读第一种情况（写的过程中不能读）

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

处理脏读

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

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

就像是我们在 GitHub 上面提交代码，然后其他人通过 GitHub 来读代码，就像下图这样：

在提交第二次代码之前的前四个读操作，读到的都是旧代码，然后最后一次读代码读到的就是第二次提交的新代码：

为了避免发生这种情况，读操作的时候，也就不能执行写操作了。通过给读的时候也加锁，就解决了不可重复读的问题。 这样的话，就会让事务的隔离性更高，但是并发性也就更低了。

幻读问题

就像脏读的第二种情况，如果加锁后，剩下的时间还可以用来做其他。比如对另外一个表进行修改，可以很大程度提高资源的利用率，如下图所示：

这样操作之后，锁的力度就不会太大。就像疫情封控，如果有一个人确诊，只分一栋楼就行了，如果确诊多了，就封一个小区。
但是这样读的时候，发现数量变了，本来只有一个代码文件，后来又多了一个。也就是一个事务在执行的时候进行多次查询，多次查询的结果不一样（多了或者少了），这也是一种特殊的不可重复读，彻底解决的这种问题，就是进行串行化执行，就是在读操作的时候，就不能写了。隔离性最高，并发性最低，数据最可靠，速度最慢。

MySQL 中事务的隔离级别

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

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

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