【MySQL】索引与事务

文章目录

📄前言：
本文是个人在学习过程中对MySQL索引和事务的总结，如有不足之处，欢迎指正。

一. 索引

1. 索引的概念

MySQL索引是一类特殊的文件，它包含着对表中所有记录的引用，是为了提高查询速度对数据库表中某一字段附加的标识。从本质上来说，索引其实是一种数据结构。它类似于一篇文章、一本书籍、一个文档的目录，通过目录我们能够了解文章的主要内容和准确定位，由此迅速找到我们所需的信息。在数据库中，索引也允许数据库服务器寻找找到表中的特定数据，而不用遍历数据库中的所有记录。

2. 引入索引的必要性及应用场景

数据库为什么要引入索引呢？
在正常情况下，我们通过一个 Select查询语句 查询数据库表中的某一记录时，默认是进行全表遍历，直到找到匹配的记录为止；而当我们对表的某一字段引入索引且查询条件包含该字段时，则能够通过索引一次或几次快速定位到记录在表中的位置，从而大大减少了数据的查询次数。
注意：由于数据库的记录是保存到硬盘上的，由于索引的存在，减少的表查询次数相当于减少了硬盘的 I/O 次数，因此能够明显减少查询的耗时。原因是一次硬盘 I/O 的所花费的时间大概相当于 10w 次内存访问的时间。

什么情况下适合为表字段增加索引呢？

1. 频繁作为 where 条件查询的字段。例如：进行大小比较，区间取值，使用 like 模糊查询等等。
2. 外键字段。增加索引可以提高表连接和条件过滤的效率。
3. 使用 order by 进行排序的字段
4. 使用 group by 子句进行聚合查询的字段。增加索引可以避免额外的排序操作。
5. …

那么什么情况不适合为字段增加索引呢？

1. 频繁进行更新操作的字段。如：插入、修改、删除等，每次的修改操作都可能会导致索引的数据结构进行调整，如果表的数据量比较大且结构的调整频率比较高，会带来大量的系统开销。
2. 查询频率不高的字段。若添加索引的字段没有作为条件查询的字段，则默认还是进行全表查询，这样一来也失去了添加索引的意义。
3. 数据量比较小的表。由于维护索引这个数据结构也会带来一定的硬盘资源，若表的数据量不大则没有必要创建索引。
4. …

3. MySQL的索引操作及注意事项

在 MySQL中，索引可以分为 自动创建和手动创建 2种形式。
什么情况下会自动创建索引呢？
1.由 primary key 约束的字段 2. 由 unique 约束的字段 3. 由 foreigh key 约束的字段

索引的相关操作如下：

showindexfrom 表名 --查看索引createindex 索引名字 on 表名(字段名)--创建索引dropindex 索引名字 on 表名(字段名)--删除索引

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

createtable student (
    id intprimarykeyauto_increment,
    name varchar(30),
    age int);createindex age_index on student(age);showindexfrom student;

注意：

• 索引通常要在表设计阶段就进行创建，原因是当表的数据达到一定数量时，构建索引的数据结构需要花费大量时间，可能导致正在运行的数据库卡死，影响其他客户端对数据库的访问。
• 手动创建的索引可以删除，自动创建的索引不能删除（如 primary key， unique等约束的字段）。

4. 索引的实现

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

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

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

可以发现，上述结构的 B+树的高度只有2，而红黑树的高度达到了4。当数据量越大时，B+树的高度会明显比红黑树更低，因此数据查询的平均时间更少。（上述红黑树图片出自：刘冲博主的博客）

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

4.1 B树（B-树）

B树又称作 B-树（此处 - 不是减号的意思，而是作为一个连接符，通常读作“杆”），它是一种 N叉搜索树，每个结点最多可以分出 N+1 个子树，其中每个结点代表了一个区间，可以保存若干条记录。（如下图）

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

• B树每个结点区间可以保存若干个 key，且每个区间内的 key 都是是有序的。
• 每个结点拥有的子树数量是不确定，每个结点能保存多少条记录也是不确定的（区间大小）。
• 若一个区间有 N 个key，则该结点最多可以分出 N + 1个子树，其中每个子树所代表的区间范围分别是 小于父亲结点区间第一个值、介于两个值之间、大于最后一个值。
• 若一棵树拥有 N 个key，最好情况下红黑树查询、修改的时间复杂度为 O(log₂ N)，B树的时间复杂度为 O(logₐ N)。

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

4.2 B+树

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

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

• B+树一个具有 N 个key的结点会分出 N个子树，其中一个 key 可能会重复在其子树中。
• 每个结点分出的 N个区间中元素是有序的，且父节点的每个 key 可能以最大值出现子树区间的最后一个位置（如上图所示），也可能以最小值出现在子树区间的第一个位置。
• 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 缓存到内存中，由于内存的访问速度相比于硬盘差了几个数量级，因此可以迅速定位到查询位置，进一步提高查询速度。

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

二. 事务

1. 什么是事务、为什么需要事务

什么是事务？

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部执行成功，要么全部执行失败。这一特性称为事务的原子性。

为什么需要事务呢（事务的应用场景有哪些）？
假设有以下场景：小明去楼下某个超市购买生活用品，这些东西价值总共200元，正常情况下数据库应该执行两条SQL语句，一条让小明的账户余额 -200，一条让超市老板的账户余额 +200，但假如小明在输入支付密码进行扣款操作时出现了网络异常，就可能出现SQL没有完全执行，导致小明已经显示扣款了但超市老板却没有收到付款的情况。

造成上述现象的原因是：双方完成转账的逻辑必定需要多条SQL进行配合，但本身每条SQL都是独立执行的，因此在极端情况下就会出现某些SQL语句的执行被异常中止。

因此在上述场景中，我们就需要为 SQL操作引入事务，让类似转账这样的操作要么全部执行成功，要么全部执行失败。

2. MySQL中事务的相关操作

MySQL中事务分为 显示事务和隐式事务，默认开启的是隐式事务（自动开启事务）。
在MySQL中，使用 show variables like ‘autocommit’; 查看是否开启隐式事务，若查询结果为 ON 则表示开启，为 OFF表示关闭隐式事务。可以通过 set autocommit = 0; 关闭隐式事务，通过 set autocommit = 1; 开启隐式事务。

show variables like'autocommit';// 查看事务的类别set autocommit =0;// 修改事务的类别show variables like'autocommit';

在MySQL中使用事务非常简单，总的来说可以分为以下三步：
（1）开启事务：start transaction; （2）执行多条SQL语句 （3）提交事务/回滚事务：commit/rollback;

示例如下：

starttransaction;// 开启事务createtable student (
    id intprimarykeyauto_increment,
    name varchar(30),
    age varchar(10));insertinto student values(null,'1','1');insertinto student values(null,'2','2');select*from student;rollback;// 回滚事务select*from student;

执行结果如下：

注意：上述在MySQL客户端使用的事务回滚，通常要搭配一些条件判断逻辑（条件、循环、变量、函数）来使用；但在实际开发中，更多的是搭配其他编程语言和框架来使用。

3. 事务的特性

事务的重要特性：

1. 原子性。原子性指事务是一个不可分割的最小工作单位，事务中的操作要么全部执行成功，要么在某个操作出错时进行回滚，即看起来就像全部不执行一样。
2. 一致性。一致性指事务的执行结果必须从一个一致性状态转移到另一个一致性状态，即事务开始之前和结束之后，数据库中的数据都必须满足所有的完整性约束（如主键约束，外键约束等）。简单的说，就是事务执行前后数据库中的数据都不能出现离谱的现象，要符合预期。
3. 持久性。持久性指事务一旦提交成功，它的执行结果将永久保存在数据库中（硬盘），即使出现断电、系统崩溃或关机重启，事务对数据库的修改也不会丢失。
4. 隔离性。隔离性指多个事务之间是能够并发执行的，且彼此感觉不到对方的存在，就像各自处于独立的环境中执行一样。

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

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

• 脏读 脏读指 事务A 正在修改某一数据时（写数据），另一个 事务B 读取了这尚未提交的数据，当读取完毕后 事务A 又对数据进行了修改，此时将导致 事务B读到一个过时/无效的数据，造成错误的执行结果。

造成这一现象的根本原因是：一个事务读取了另一事务尚未提交的修改数据。因此，要想解决这一问题就需要对事务进行写加锁，即某一事务在对某块数据进行写操作时，其他的事务不能读取该数据，只能读取已经提交的数据。

• 不可重复读 脏读指在同一个事务中，事务A 第一次读取一些已经提交的数据后，另一 事物B 对数据进行了修改并提交，当 事务A 再次读取这些数据时，突然发现内容与上一次读取相比发生了变化，导致 事务A 在执行过程基于不一致的数据做出不同的决策。

造成这一现象的根本原因是：一个事务在读取某些已经提交的数据时，另一事务对这些数据进行修改并提交，导致同一事务在多次同一数据时得到不同的结果。因此，要想解决这一问题就需要对事务进行读加锁，即某些已被读取的数据，另一事务在前一事务结束前不能对其进行修改。

• 幻读 幻读指 事务A 在读取某块已经提交数据时，由于读加锁的存在，其他事务并不能对其进行修改，但没有规定不能修改其他的数据，因此 事务B 对其他数据进行修改并提交，当 事务A 再次读取某些数据时，发现相比于上次新增了某些数据。出现幻读造成的影响根据现实情况而定，有时幻读是个问题，有时它却不是问题。

造成这一现象的根本原因是：两个不同事务处于并发执行的状态。因此，解决这一问题的根本方法就是引入串行化的执行方式，即禁止事务的并发执行，多个事务之间只能按照一定的顺序依次执行。

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

注意：MySQL默认的隔离级别为 repeatable read（可重复读）。

以上就是本篇文章的全部内容了，如果这篇文章对你有些许帮助，你的点赞、收藏和评论就是对我最大的支持。
另外，文章的不足之处，也希望你可以给我一点小小的建议，我会努力检查并改进。