MySQL索引与事务

前言：

本篇主要介绍MySQL中背后的数据结构，和实际我们使用时会涉及到的逻辑情况，当然，我们对于背后的数据结构不要求实现它，只需了解即可。

索引：

我们先来看官方对其的解释：索引是帮助MySQL高效获取数据的数据结构。

当使用select查询的时候，会先遍历表，之后把当前行带入到遍历条件中，看是否成立。如果表很大，这样成本非常高。索引属于针对查询操作引入的优化手段，可以通过索引来加快查询速度，避免针对表的遍历。

1、索引的代价

所以索引是能提高查询速度的，但是也有代价。

• 占用更多空间，生成索引，是需要一系列的数据结构，以及一系列额外的数据，来存储到硬盘空间中的。
• 但是可能会降低插入修改删除速度

但是实际开发中，查询的频率比插入删除的频率高很多。

2、索引分类

这里有4种索引(可能会有更多的索引)：

索引类型
主键索引
唯一索引
单值索引
符合索引

我们在创建索引中讨论。

3、 索引相关操作

3.1 查看索引

此时我们先创建一张表：

-- 若存在数据库mydb则删除
drop database if exists mydb;
-- 创建数据库mydb
create database mydb;
 
-- 选择数据库mydb
use mydb;
 
-- 创建学生表
create table student (
    id int,
    name varchar(20)
);

查询该表索引语句：

show index from student;

运行结果：

3.2 创建索引

创建索引有两种方式：主动创建 和 被动创建

3.2.1 被动创建

其实当我们在给一个字段添加约束的时候就已经创建了索引，比如此时我们我们在创建一个表的时候为某字段添加了主键约束：

drop table student;

create table student(
    id int primary key,  # 添加主键
    name varchar(20)
);

show index from student; # 查看索引

运行结果：

所以此时如果查询其他列还是逐个遍历。 

此时我们再观察外键约束：

drop table student;

create table class (
    id int primary key,
    name varchar(20)
);

create table student (
    id int primary key,
    name varchar(20),
    classId int,
    foreign key(classId) references class(id)
);

show index from student; # 查看索引

运行结果：

3.2.2 主动创建

此时我们也可以主动创建索引，比如我们对student表中的name字段创建索引：

# 为name字段创建索引
create index idx_student_name on student(name);

运行结果：

当然我们还有其他方式创建索引，但是我们没有必要全部掌握，当我们需要使用时再去查询也是可以的。 

创建索引操作也是危险操作，创建索引的时候，需要针对现有的数据进行大规模的重新整理。 如果当前是一个空表，或者数据不多，创建索引都没啥问题；如果这个表本身就很大，创建索引很容易把数据库服务器给卡住。

一般来说，创建索引都是在创建表的时候就规划好了，如果用了很久的表想增加索引，就需要慎重了。

这里有一个小技巧：另外搞一个机器，部署MySQL服务器，也创建同样的表，并把表上的索引创建好，再把之前的机器上的数据导入到新的MySQL服务器上，此时不能影响原来服务器的运转，当所有数据都导入完毕，就可以使用新的数据库替换旧的数据库了。

3.3 删除索引

手动创建的索引可以手动删除，但是自动生成的索引是不能删除的！

# 语法
drop index 索引名 on 表名;

# 删除name字段的索引
drop index idx_student_name on student;

# 此时尝试删除主动创建的索引
drop index PRIMARY on student; # 删除自动创建的索引

运行结果：

删除索引操作也是危险操作，所以要慎重使用。 

实现索引的数据结构(B树)： 

MySQL内部有一个存储引擎，存储引擎模块提供了很多版本实现。Innodb 是当前最常用的 MySQL存储引擎，索引底层数据结构也就是B+树。

但是了解B+树前需要先了解B树，这里作者偷个懒，有一个博主讲解的很好，我们可以看他的视频：B树(B-树) - 来由, 定义, 插入, 构建_哔哩哔哩_bilibili

当然，他也讲解了B+树：数据结构合集 - B+树_哔哩哔哩_bilibili

B+树就是为了方便得到有顺序的数据，这样不需要中序遍历即可得到顺序化数据。

B - 树 不是 B减树，而是连接符，也是B树。

这里来稍微总结一下：

B树也是平衡搜索树，是一颗多叉平衡搜索树。

比如目前是m阶B树，每个节点最多有m个分支，m - 1 个元素。

对于跟节点：最少有一个元素，两个分支。

对于其他节点：最少有 m / 2 个分支(向上取整)，每个节点有 m / 2 - 1 个元素。

事务：

事务是一组被认为是单个逻辑工作单元的SQL操作，他们要么全部成功执行，要么全部不执行。事务可以包含一个或多个数据库（如：插入、更新、删除等），这些操作要么全部生效，要么全部回滚，以保证数据的一致性和完整性。

这里举个栗子：比如转账的时候，银行现在卡里扣钱，之后在另外一个账户里赚钱。这是两个步骤，如果此时执行完第一步程序崩溃，最终你的卡里钱扣了，另一个账户并没有收到钱，事务就是为了避免这个情况。

注意：回滚用日志的方式记录事务中的关键操作(将内容打印到文件中去)，即使主机掉电也不影响(回滚的日志已经在文件中了)，一旦重新启动，就会发现回滚日志中有一些需要进行回滚的操作，这样就完成了回滚。

1、使用事务：

我们先来了解事物的语法：

# 开启事务
begin    
# 或者
start transaction

# 提交事务
commit

# 回滚事务
rollback

这里我们就模拟上面的转账的场景，来模拟事物的场景:

李四向张三借了1000元，张三原来有3000元，李四原来有1000元。

create table account (
    id int primary KEY auto_increment,
    name varchar(20) not null,
    money decimal(7,2)
);

# 插入数据
insert into account(name,money) values('张三',3000);
insert into account(name,money) values('李四',1000);

# 此时开启事务
start transaction;

# 更新账户
update account set money = money - 1000 where name = '张三';

出错了
update account set money = money - 1000 where name = '李四'  

# 回滚
rollback;

此时我们如果没有出错，就可以提交事务，就是使用commit进行提交。

所以回滚就是回滚到距离最近的上一次commit的步骤或者 start transaction ，这样就保证了一件实务不会进行到一半崩溃而数据也只保存了一半。

2、事务的隔离级别：

MySQL支持多种事务隔离级别，用于控制事务之间的隔离程度。包括以下几种：

1.读未提交(Read Uncommitted)：这是最低的级别。在这个级别下，一个事务可以读取另一个事务未提交的数据。这意味着可能会出现脏读的情况。

举个栗子：当我在写文件的时候，另一个人在抄我的文件，我写完了(不算晚全写完，我又检查了一下)以后他抄完了，但是此时我们发现我写错了于是开始修改，但是另一个人并没有修改文件。

2. 读已提交(Read Committed)：这个级别确保了一个事物只能读取到其他事务已经提交的数据，从而避免了脏读的问题。这个级别下，当一个事务正在修改的时候，其他事务不能读取到未提交的数据。但可能出现重复读的问题。

3.可重复读(Repeatable Read)：这个级别确保了在同一个事务内多次读取同一个数据时，结果是一样的。它通过在读取某行数据时加锁来实现，这样其他事务无法在此期间修改这一行数据，从而避免了不可重复读。但可能出现幻读的问题。

4.串行化(Serializable)：这是最高的隔离级别。它通过强制事务串行执行来避免并发任何问题，确保了数据的一致性。然而，由于事务必须按顺序执行，可能会导致性能下降。

解释：

• 脏读(Dirty Read)：这就是我上面举的那个例子(抄文件)，在隔离级别为“读未提交”可能出现的问题。
• 重复读(Non-repeatable Read)：这是隔离级别为“读已提交”时可能出现的问题。在一个事务中多次读取同一行数据的过程中，另一个事物也对这个事务也对这部分数据进行了修改。如果此时事务再次读取这一行数据，那么它将读到与第一次读取时不同的数据。这就是不可重复读。
• 幻读(Phantom Read)：这是隔离级别为“可重复读”时可能出现的问题。在一个事务多次读取表中的数据过程中，另一个事务插入了新的行。如果此时第一个事务再次读取表中的所有数据，那么它将看到一些原本不存在的行，这就是幻读。

不可重复读：同一事务中，多次读取同一条记录时，数据可能发生了修改。比如第一次是X，第二次是Y。这里的重点是同一条记录的数据被修改了。

幻读：是指在同一个事务中，多次执行相同的查询语句时，结果集的行数发生了变化。例如，第一次查询某个条件下的记录集有10条，另一个事务插入了几条符合该条件的新记录并提交，第二次查询时，结果集变成了12条。这里的重点是结果集的行数发生了变化，因为有新的记录插入或删除。

注意：默认级别为可重复读。

总结：

这些知识都不难，主要还是我们要多去练习，有其他错误请大佬在评论区指正，制作不易，大家可以点点赞。