【MySQL】索引与事务

1.❤️❤️前言~🥳🎉🎉🎉

Hello, Hello~ 亲爱的朋友们👋👋，这里是E绵绵呀✍️✍️。

如果你喜欢这篇文章，请别吝啬你的点赞❤️❤️和收藏📖📖。如果你对我的内容感兴趣，记得关注我👀👀以便不错过每一篇精彩。

当然，如果在阅读中发现任何问题或疑问，我非常欢迎你在评论区留言指正🗨️🗨️。让我们共同努力，一起进步！

加油，一起CHIN UP！💪💪

🔗个人主页：E绵绵的博客
📚所属专栏：

1. JAVA知识点专栏

深入探索JAVA的核心概念与技术细节

2.JAVA题目练习

实战演练，巩固JAVA编程技能

3.c语言知识点专栏

揭示c语言的底层逻辑与高级特性

4.c语言题目练习

挑战自我，提升c语言编程能力

5.Mysql数据库专栏

了解Mysql知识点，提升数据库管理能力

6.html5知识点专栏

学习前端知识，更好的运用它

7. css3知识点专栏

在学习html5的基础上更加熟练运用前端

8.JavaScript专栏

在学习html5和css3的基础上使我们的前端使用更高级

📘 持续更新中，敬请期待❤️❤️

该文章有部分材料借鉴于【MySQL】索引与事务

2. 索引

2.1 索引的概念

索引是一种特殊的文件，用于加速数据库的查询操作。它类似于书籍的目录，通过保存表中某列或多列的排序引用指针，帮助快速定位数据。

2.2 引入索引的必要性

数据库为什么要引入索引呢？
在正常情况下，我们通过一个 Select查询语句查询数据库表中的某一列记录时，默认是进行全表遍历，直到找到匹配的记录为止；而当我们对表的某一列引入索引且查询条件包含该字段时，则能够通过索引一次或几次快速定位到记录在表中的位置，从而大大减少了数据的查询次数。

注意：由于数据库的记录是保存到硬盘上的，由于索引的存在，减少的表查询次数相当于减少了硬盘的 I/O 次数，因此能够明显减少查询的耗时。原因是一次硬盘 I/O 的所花费的时间大概相当于 10w 次内存访问的时间。

2.3 索引的用法

索引可以分为 自动创建和手动创建 2种形式。

什么情况下会自动创建索引？

主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）会自动为对应列创建索引。并且该索引删除不了

索引的相关操作如下：

查看索引：

SHOW INDEX FROM 表名;
-- 示例：查看学生表的索引
SHOW INDEX FROM student;

创建普通索引：

CREATE INDEX 索引名 ON 表名(字段名);
-- 示例：为班级表的name字段创建索引
CREATE INDEX idx_classes_name ON classes(name);

删除索引：

DROP INDEX 索引名 ON 表名;
-- 示例：删除班级表name字段的索引
DROP INDEX idx_classes_name ON classes;

如下图所示：手动创建一个索引并查看表的索引

create table student (id int primary key auto_increment,name varchar(30),age int
);
create index age_index on student(age); 
show index from student;

注意：

索引通常要在表设计阶段就进行创建设计好，原因是当表的数据达到一定数量时，之后创建或者删除索引的数据结构就需要花费大量时间（因为要遍历整个表），可能导致正在运行的数据库卡死，影响其他客户端对数据库的访问。
如果要解决该问题，就要搞个新的机器.创建空表,创建改动之后的索引，把旧机器上的数据平稳的导入到新机器中，使用新机器代替旧机器.

2.4 索引的原理

大家可以回忆一下，在常见的数据结构中，哪些能够提高查询的效率呢？
线性表、栈、队列、堆（这些都不行）
平衡二叉树（各节点左右子树高度平衡的二叉搜索树）、红黑树、哈希表（这些都可以）。

以上能提高查询的数据结构各有什么特点？

哈希表：擅长精准匹配，若查询条件为 “=”则能够在O（1）的时间定位到数据位置；不擅长范围查询、模糊查询，如：<，>，%等。
平衡二叉树：擅长精准匹配，也擅长范围查询、模糊查询（根据查询值和结点值选择左右分支）。但由于平衡二叉树对树结构要求非常严格，任何修改操作都可能会触发树结构的调整，造成大量的开销。
红黑树：擅长精准匹配，也擅长范围查询、模糊查询（由于红黑结点的存在，树可以出现一定程序的不平衡状态，且不会触发调整）。

通过上述数据结构的特点我们可以知道：红黑树貌似是最适合作为数据库索引的数据结构；但在实际情况中，往往使用 B+树作为数据库索引的实现。原因是红黑树本质上也是一种二叉树，当数据量很大时（比如有 1亿条数据），红黑树也具有不小的高度，这直接影响了硬盘的 I/O次数和查询的平均时间。相比之下，B+树是一种 n叉搜索树（即每个结点可以分出 N 个子树，并且一个结点代表了一个区间，可以存储若干个 key），因此树的高度相比红黑树就大大降低了。（如下图）

可以发现，上述结构的 B+树的高度只有2，而红黑树的高度达到了4。当数据量越大时，B+树的高度会明显比红黑树更低，因此数据查询的平均时间更少。

除了 N叉的特点，B+树还具有红黑树没有的其他特性，在这些特性的辅助之下，B+树无疑更适合作为 MySQL索引的实现。在正式讲解 B+树之前，我们需要了解一下什么是 B树。

B树（B-树）

B树又称作 B-树（此处 - 不是减号的意思，而是作为一个连接符），它是一种 N叉搜索树，每个结点最多可以分出 N+1 个子树，其中每个结点代表了一个区间（如下图）

通过上图我们可以发现 B树有以下特点：

1.B树每个结点区间可以保存若干个 key，且每个区间内的 key 都是是有序的。
2.若一个区间有 N 个key，则该结点最多可以分出 N + 1个子树，其中每个子树所代表的区间范围分别是小于父亲结点区间第一个值、介于两个值之间、大于最后一个值。

在实际情况中，当一个结点区间 key 的数量较多时，有可能会触发树结构的调整，从而将一个区间的 key 分裂成更多的子树；当一个区间内 key 的数量较少时，可能会将该区间内的结点合并到父结点所在的区间。

B+树

B+树是一种改进的 B树，它同样是一种 N叉搜索树，每个结点最多可以分出 N 个子树，每个结点代表了一个区间。B+树与B树最大的区别就是 B+树区间内的每个 key 都会出现其子树中重复出现。（如下图）

通过上图我们可以发现 B+树有以下特点：

1.B+树一个具有 N 个key的结点会分出 N个子树，其中一个 key 可能会重复在其子树中。
2.每个结点分出的 N个区间中元素是有序的，且父节点的每个 key 可能以最大值出现子树区间的最后一个位置（如上图所示），也可能以最小值出现在子树区间的第一个位置。
3.B+树的所有 key 最终都会落到树的最后一层，且所有的叶子结点都以双向链表的形式串起来，即叶子结点包含数据的全集。

同B树一样，当区间内 key 过多或过少都可能触发结点的分裂或合并。

数据库之前使用 B+树作为索引的实现，是因为它具有以下优点（相对于 B树、红黑树、哈希表）：

1.B+树是一颗 N叉搜索树，树的高度是有限的，可以较大程度地减少硬盘的 I/O次数。

2.B+树非常擅长范围查询。由于 B+树所有叶子结点都通过链表连接，因此当通过层层比较找到范围端点在叶子结点所处的位置时，就能够轻松完成范围查询。相比之下，B树和红黑树进行范围查询时则需要多次回溯才能找到范围内的所有记录。

3. B+树查询数据的时间开销是稳定的。由于所有的查询最终都会落到叶子结点上，因此不会像 B树或红黑树一样出现时慢时快的现象。

4.叶子结点是数据全集，因此数据表的行记录都只存储在叶子结点上，而非叶子结点只存储数据表中某个字段的 key 。因为我们不仅有叶子节点还有非叶子节点，那么我们这存储的内存会不会特别多？虽然 B+树多存储的非叶子结点的key会消耗额外硬盘空间，但每个 key 相比于一条行记录所占空间是非常小的（假如为 id 添加索引，每个id占4个字节，数据表有 10亿条数据，这些 key所占的空间仅接近 4GB），这些的 key 所占空间对硬盘来说是很小的，远不如叶子节点的存储行记录，所以无需担心这方面。因此我们在进行数据库查询时，可以将 key 缓存到内存中（它所占的gb因为不多所以刚好适合缓存到内存中），由于内存的访问速度相比于硬盘快很多，因此可以迅速定位到查询位置，进一步提高查询速度。

注意：MySQL内部有一个名为存储引擎的模块，存储引擎有很多的版本实现，其中 Innodb 是当前最常用的MySQL存储引擎，它是实现就是使用 B+树实现的（上述 B+树只是一种通用的情况，实际上数据库索引的实现会对 B+树进行更多的优化以提升查询效率）。

3.事务

3.1 为什么需要事务？

假设用户A向用户B转账，需执行两条SQL：

UPDATE account SET money = money - 2000 WHERE name = 'A';
UPDATE account SET money = money + 2000 WHERE name = 'B';

若第一条SQL执行后数据库崩溃，会导致A扣款但B未收款，数据不一致。事务可确保两条SQL要么全部成功，要么全部失败。

3.2 事务的用法

开启事务：
```
START TRANSACTION;
```

执行SQL操作：

-- 示例：转账操作
UPDATE account SET money = money - 2000 WHERE name = '阿里巴巴';
UPDATE account SET money = money + 2000 WHERE name = '四十大盗';

提交或回滚：
```
-- 提交事务（永久生效）
COMMIT;
-- 回滚事务（撤销所有操作并重新开始）
ROLLBACK;
```
注意：上述在MySQL客户端使用的事务回滚，是主动回滚的，它通常要搭配一些条件判断逻辑（条件、循环、变量、函数）来使用；但在实际开发中，更多的是搭配其他编程语言和框架来使用。

3.3事务的特性

1. 原子性：原子性指事务是一个不可分割的最小工作单位，事务中的操作要么全部执行成功，要么在某个操作出错时进行回滚，即看起来就像全部不执行一样。

2.一致性：一致性指事务的执行结果必须从一个一致性状态转移到另一个一致性状态，即事务开始之前和结束之后，数据库中的数据都必须满足所有的完整性约束（如主键约束，外键约束等）。简单的说，就是事务执行前后数据库中的数据都不能出现离谱的现象，要符合预期。

3.持久性：持久性指事务一旦提交成功，它的执行结果将永久保存在数据库中（硬盘），即使出现断电、系统崩溃或关机重启，事务对数据库的修改也不会丢失。

4.隔离性：隔离性指多个事务之间是能够并发执行的，且彼此感觉不到对方的存在，就像各自处于独立的环境中执行一样。下面要重点讲述一下这个特质

3.4事务的并发问题和隔离级别

MySQL是客户端服务器结构的程序，一个MySQL服务器可以同时为多个客户端提供服务，所以就会出现事务的并发执行，虽然事务并发执行可以提高 MySQL处理事务的效率，但可能会出现一些问题，不同的隔离性会引起不同程度的问题：
1.脏读 2. 不可重复读 3. 幻读

脏读指事务A 正在修改某一数据时（写数据），另一个事务B 读取了这尚未提交的数据，当读取完毕后事务A 又对数据进行了修改，此时将导致事务B读到一个过时/无效的数据，造成错误的执行结果。
造成这一现象的根本原因是：一个事务读取了另一事务尚未提交的修改数据。因此，要想解决这一问题就需要对事务进行写加锁，即某一事务在对某块数据进行写操作时，其他的事务不能读取该数据，只能读取已经提交的数据。

不可重复读指在同一个事务中，事务A 第一次读取一些已经提交的数据后，另一事物B 对数据进行了修改并提交，当事务A 再次读取这些数据时，突然发现内容与上一次读取相比发生了变化，导致事务A 在执行过程基于不一致的数据做出不同的决策。
造成这一现象的根本原因是：一个事务在读取某些已经提交的数据时，另一事务对这些数据进行修改并提交，导致同一事务在多次同一数据时得到不同的结果。因此，要想解决这一问题就需要对事务进行读加锁，即某些已被读取的数据，另一事务在前一事务结束前不能对其进行修改。

幻读指事务A 在读取某块已经提交数据时，由于读加锁的存在，其他事务并不能对其进行修改，但没有规定不能修改其他的数据，因此事务B 对其他数据进行修改并提交，当事务A 再次读取某些数据时，发现相比于上次新增了某些数据。出现幻读造成的影响根据现实情况而定，有时幻读是个问题，有时它却不是问题。造成这一现象的根本原因是：两个不同事务处于并发执行的状态。因此，解决这一问题的根本方法就是引入串行化的执行方式，即禁止事务的并发执行，多个事务之间只能按照一定的顺序依次执行。

在MySQL中有4种隔离级别，分别为：读未提交、读已提交、可重复读、串行化。其中这4种隔离级别与对应的3种问题关系如下图：