MySQL 索引事务

目录

• 1. 索引
• • 1.1 概念
  • 1.2 索引的使用
  • 1.2.1 查看索引
  • • 1.2.2 创建索引
    • 1.2.3 删除索引
    • 1.2.4 索引背后的数据结果
• 2. 事务
• • 2.1 概念
  • 2.2 事务的特性
  • • 2.2.1 四大特性
    • 2.2.2 隔离性
    • • 脏读问题
      • 不可重复读
      • 幻读
    • 2.2.3 事务的隔离级别

1.1 概念

索引，是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据。其实就像是文章目录一样，你想看哪个部分，你就可以通过目录（索引）很快找到你想要的部分。

1.2 索引的使用

1.2.1 查看索引

使用 show index from 表名，我们以 student 表为例：

show index from student;

圈起来的三块表示是 student 表中，这个 PRIMARY 是主键自动创建的索引，针对的是 id 这一列。

1.2.2 创建索引

我们使用 create index 索引名 on 表名(列名);

create index index_student_name on student(name);

此时就多出了一个索引。
我们这里再多说一句，创建索引一般是在创建表的时候就把索引设定好，如果说表里已经有很多数据了，那么索引最好就别动了，原因也很简单，比如你现在有一本很厚的书，没有目录，现在让你搞一个目录出来，那么就要花费你大量的时间（资源），可能得搞很久才能搞完。

1.2.3 删除索引

drop index 索引名 on 表名;

drop index index_student_name on student;

此时我们就完成了删除索引操作。

1.2.4 索引背后的数据结果

B+ 树，这个就是为数据库索引量身定做的数据结构。
我们先了解 B+ 树 的前身：B 树。
B 树 也叫做 B- 树，此处的 ‘-’ 不是 减号，是 杠，前晚不要念成 B 减树。
B 树可以认为是一个 N 叉 搜索树：

当节点的子树多了，节点保存的 key 多了，意味着在同样的 key 的个数的前提下，B树的高度就会比二叉搜索树低很多。树的高度越高，进行查询比较的时候，访问磁盘的次数就越多。之所以这样,主要有以下几点原因:

• 树形结构通常用来实现索引,树的节点会存储在磁盘上。
• 查询时需要从根节点开始遍历,访问各个节点。
• 节点存储在磁盘上,访问节点需要磁盘IO。
• 树越高,从根到叶子的路径越长,需要访问的节点数越多。
• 访问更多节点,意味着需要更多的磁盘IO操作。
• 磁盘IO是比较耗时的操作,访问次数越多,查询耗时越长。

B+ 树在 B 树 的基础上又作出改进，也是 N 叉搜索树：

此时我们可以发现， B+ 树的所有数据都是包含在叶子节点中的。
B+ 树的特点：

1. 一个节点，可以存储 N 个 key， N 个 key 划分出了 N 个区间（不是 N + 1 个）
2. 每个节点中的 key 的值，都会在子节点中存在（同时该 key 是子节点的最大值）
3. B+ 树的叶子节点是首尾相连的，类似于一个链表
4. 由于叶子节点是完整的数据集合，只在叶子节点这里存储数据表的每一行数据，而非叶子节点只存 key 本身即可。

由这些特点，我们来总结一下 B+ 树的优势：

• 当前一个节点保存更多的 key，最终树的高度是相对更矮的，查询的时候减少了 IO 次数（和 B 树是一样的）
• 所有的查询最后都会落在叶子节点上，换句话说，所有数据的查询的 IO 次数是一样的，所以查询也就比较稳定，就可以让程序员对于执行效率有一个更准确的评估。
• B+ 树的所有的叶子结点构成了一个链表，此时比较方便进行范围查询。
• 由于数据都在叶子节点上，非叶子结点只存储 key，导致非叶子结点占用空间是比较小的，那么这些非叶子结点就可能在内存中缓存（或者缓存一部分），这样就又进一步减少了 IO 的次数。

当然，我们上面画的图是默认 id 是表的主键了。但是如果这个表有多个索引，针对 id 有主键索引，针对 name 又有一个索引的话，表的数据还是按照 id 为主键，构建出 B+ 树，通过叶子结点组织所有的数据行。其次针对 name 这一列，会构建出另外一个 B+ 树，但是这个 B+ 树的叶子结点就不在存储这一行的完整数据，而是存储主键 id。此时如果根据 name 来查询，查到的叶子结点只是主键 id，还需要通过主键 id，去主键的 B+ 树立再查一次才能得到数据（这个过程称为“回表”，都是 MySQL 自动完成的，用户感知不到）。

2.1 概念

我们先设想一个场景：假设有一张 account（账户）表：account(id, balance)，现在有 id = 1 的用户，balance 为 1000,；id = 2 的用户，balance 为 0。现在 1 给 2 转账 500，此时要完成这个操作需要完成两步：
1）update account set balance = balance - 500 where id = 1;
2）update account set balance = balance + 500 where id = 2;
此时考虑一个极端场景，假设在执行转账的过程中，执行完 1） 之后，数据库崩溃了/主机宕机了，此时 2）操作就没有执行，也就是说 1 的钱扣了，但是 2 的钱没到账，显然这是很不科学的。而事务就是为了解决这个问题的。
事务的本质就是把多条 SQL 语句给打包成一个整体，要么全部执行成功，要么就一个都不执行，不会出现只执行一部分这种状态。这句话的全部执行成功好理解，但是一个都不执行不是真的一个都不执行，而是看起来就像没执行一样，意思就是执行了，但是执行一半出错了，然后选择恢复成未执行的状态（恢复现场，或者说按了一个 ctrl + z），这恢复数据的操作称为“回滚rollback”。进行回滚的时候，为了知道回滚到什么样的状态，需要额外的操作来记录书屋中的操作步骤，数据库中有专门的用来记录事务的日志。
此时我们就可以将上面转账的 1）2）操作作为一个事务，就可以在出现异常的时候把数据还原回来。
我们使用 start transaction 来开启事务，通过 commit 来提交事务，到 commit 这一步就相当于事务就执行完了：

start transaction;update account set balance = balance - 500 where id = 1;update account set balance = balance + 500 where id = 2;commit;

2.2 事务的特性

2.2.1 四大特性

1. 原子性：务中的操作要么全部执行成功,要么全部失败回滚,该事务中涉及的对数据库的更新必须视为一个整体,不能部分执行成功或部分失败。
2. 一致性：事务必须使数据库从一个一致性状态转换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须保证数据库中数据的完整性约束。比如上述的转账操作，1 转账 500 后还有 500，2 收到转账后有 500，此时没问题；但是如果 1 转账后是 500,2 收到转账后是 5000，这就不对了。
3. 持久性：事务修改的内容是写到硬盘上的，持久存在的，重启也不会丢失。
4. 隔离性：这个隔离性是为了解决并发执行事务而引起的问题。

2.2.2 隔离性

接下来我们就来说说并发处理事务，可能有哪些问题，以及这些问题数据库的隔离性是怎样解决的。

脏读问题

一个事务A正在对数据进行修改的过程中，还没提交之前，另外一个事务B，也对这个数据进行了读取，此时 B 的读取操作就称为 脏读，读到的数据也称为 脏数据，这里的 脏 是无效的意思。举个例子，假设正在考试，学生1 在做一道选择题，在 C 和 D 选项中徘徊不定，虽然写下了 C 但是还没确定，此时学生 2 偷瞄了同学1 的答案，于是写了 C，但是同学1 思考中确定了 D 是正确答案，又是改成了 D，那么此时 同学2 的偷瞄就相当于是脏读，读到的 C 就相当于是 脏数据。
为了解决脏读问题，MySQL 引入了“写操作加锁” 这样的机制，就相当于同学1 与 同学2 约好了，同学1 在没确定答案前，先用手挡住，确定了答案在把手拿开，同学2 才能查看。此时当同学1 琢磨答案（写的时候）同学2 没发读，也就是 同学1 的 写操作 与 同学2 的读操作没发并发了。这个给写加锁操作，就降低了并发程度（降低了效率），但是提高了隔离性（提高了数据的准确性）。

不可重复读

事务1 已经提交了数据，事务2 开始去读取数据。在读取过程中，事务3 又提交了新的数据，此时就意味着同一个事务 2 之内，多次读取数据，读出来的结果是不相同的（预期是一个事务中多次读取的结果是一样的）。
举个例子：
假设一个电影院的网络订票系统,显示某场电影还剩10张票。小王查看了票数后准备订票,此时另一个用户小李先订走了2张票。小王完成选座后提交订单时,系统显示该场电影仅剩8张票。
这个例子中:

• 小王查询票数10张,是一个读操作。
• 小李订票减少剩余票数,是一个更新操作。
• 小王再次读取剩余票数变为8张,与第一次读到的10张不一致。

这就造成了不可重复读现象。同一个查询在事务执行过程中返回了不同结果。我们可以在小王的事务开始时,直接对需要查询的电影订票数字段加上行锁。这可以阻止其他事务对订票数进行更新,直到小王的事务完成。从而避免了小王多次读取订票数据不一致的情况。这就相当于对读操作进行加锁。
通过这个读加锁进一步的降低了事务的并发能力，提高了事务的隔离性。

幻读

当前我们已经约定了写加锁和读加锁，解决了脏读和不可重复读问题，此时还存在幻读问题。
所谓的幻读就是在读加锁和写加锁的前提下，一个事务两次读取同一个数据，发现读取的数据值是一样的，但是结果集不一样。
举个例子，由于约定了读加锁，1先生在 2先生读的时候，不能修改代码，但是 1先生不想闲着，于是就要想办法做点事情，他发现 2先生读的是 Student.java ，那么他就创建一个 Teacher.java，然后接编写这里面的代码。这样的话就可能导致 2先生或者其他正在读 Student.java 的时候，虽然每次读取数据内容一样，但是突然就多出一个 Teacher.java。
为了解决幻读问题，数据库采用 串行化 的方式，彻底放弃并发处理事务，一个接一个的串行的处理事务。这样做并发程度最低（效率最低），隔离性最高（数据准确性最高）

2.2.3 事务的隔离级别

对应上述的脏读、不可重复读、幻读问题，MySQL 提供了 4 种隔离级别：

1. 读未提交（read uncommitted）：此时没有任何所限制。并发程度最高（效率最高），隔离性最低（准确性最低）
2. 读已提交（read committed）：给写加锁了，并发程度降低了，隔离性提高了
3. 可重复读（repeatable read）:给读和写都加锁，并发程度又降低了，隔离性又提高了
4. 串行化（serializable）：串行化，并发程度最低，隔离性最高

这四个级别属于 MySQL 内置的机制，可以通过修改 MySQL 的配置文件，来设置当前 MySQL 的工作在那种状态下，默认是 可重复读。这几个隔离级别的选择需要我们根据实际需要，在准确性和效率之间进行权衡。

来源地址：https://blog.csdn.net/weixin_58697177/article/details/132239970