【MySQL】索引和事物

索引和事物

索引
- 索引是什么
- 索引的基本操作
- 索引部分原理
- - 数据结构讨论
  - B-树
  - B+树
- MySQL的索引实现
事物
- 事物的概念
- 事物的使用
- 事物的四大特性(ACID)
- 事物并发问题
- 事物的隔离级别

索引

索引是什么

在正常情况下, 数据库去搜索数据, 都是通过一行行的遍历, 然后找到符合要求的行并且筛选出来, 很明显这样的操作对于查询频繁的表来说, 效率是非常低的. 同时我们的数据库都是将数据存储在硬盘上的, 而每一次访问硬盘的操作, 开销是非常大的, 因此更不用说频繁的访问硬盘了.

因此为了优化查询操作, 数据库添加了索引机制, 索引是针对于查询操作的一种特殊的优化手段, 它主要是通过一些数据结构去给表中的数据进行管理, 从而便于查找.

实际上索引就类似于书本中的目录, 我们看出的时候能够通过目录来快速找到我们想看的东西, 而不是要一页一页的翻. 那么索引也是如此,它能够让我们在查找数据的时候, 快速的找到目标数据, 而不是一行一行遍历

但是世界上没有完美的东西, 既然有优点, 自然就会有对应的代价, 使用索引的代价有如下几点:

索引是一个额外的文件, 因此是需要占用额外空间的
索引内部采用了一定的数据结构来维护数据, 因此每一次对表中的数据进行修改的时候, 都需要重新维护数据结构

那此时可能有人要说了: 你这个索引就只能让我的查找操作变快, 其他三个增加删除修改操作都会变慢, 那这样看我死亏啊, 你这个索引也不咋滴啊

实际上在一个真实的系统中, 修改的操作相对于查找操作来说, 次数是比较少的.

例如在一个学生管理系统中, 我们想一下什么时候才会去插入信息? 学生入学的时候, 一年一次. 什么时候删除信息? 学生毕业的时候, 也是一年一次. 什么时候修改信息? 似乎没有什么固定的时间, 但是不常发生. 而对于查询操作呢? 可能一天里面就要看好几次, 要么是学生要去看看自己的课表什么的, 要么就是老师要查询一下信息之类的.

并且我们可以手动的去选择在一些查找非常频繁的表中去创建索引, 而不是一定要在所有表里面创建索引.

总而言之, 索引的使用并不是说我们无脑的去使用即可, 基本上来说有如下的注意事项:

索引一般在查找频繁的表中使用, 如果涉及到的修改操作较多, 则不推荐使用索引
对于一些数据量很小的表, 没有必要使用索引, 因为此时遍历的开销可能还不及维护索引的开销
索引会占据额外的硬盘空间, 在硬盘空间不足时, 不推荐使用索引

索引的基本操作

索引的基本操作, 无非就是查看, 创建和删除, 通过下面三个简单的语句就可以实现

-- 查看索引
show index from 表名;-- 插入索引
create index 索引名 on 表名(列名);-- 删除索引
drop index 索引名 on 表名;

这里我们可以看到, 我们的索引是创建在列上面的, 因此对于一个查询频繁的表, 我们需要找到其查询频繁的列, 从而设定索引

下面是一个示范的例子

-- 创建表
drop table if exists user;
create table user(id int primary key,username varchar(20) unique,age int,password varchar(20)
);-- 在年龄列创建索引
create index idx_age on user(age);-- 查看索引
show index from user;

此时运行发现, 我们的索引居然有三个

在这里插入图片描述

分别在 id, username 和 age 上都创建了一个索引. 这实际上就涉及到了 MySQL 自动创建索引的机制, 它会自动的在主键, unique以及外键上创建一个索引.

为什么给三个约束的列创建索引, 其实也很好理解, 这三个约束限定的列, 基本上都是查询比较频繁的列, 因此就创建索引来增加查询速度.

同时, 由数据库自动创建在主键约束列上面的索引, 我们是没有办法删除的

在这里插入图片描述

那么此时我们再来测试一下外键, 我们先执行下面的 SQL

create table book(id int primary key,title varchar(20),user_id int,foreign key (user_id) references user(id)
);show index from book;

此时可以看到有两个约束, 此时我们再尝试删除外键索引

在这里插入图片描述

也会发现无法删除

创建索引以及删除索引, 同样有可能是一个危险操作. 为什么?

因为如果要创建或者删除索引, 那么此时就需要对数据进行重新整理. 那么此时如果数据量很大, 自然整理的开销就是非常大的, 甚至于会直接将装载着数据库的主机卡死.

因此一般来说, 索引的约定是在创建表的时候就约定好的, 后续不再修改

那么假如我就是想要在一个数据量比较大的表里创建一个索引, 有没有什么办法呢?

当然也是有的, 我们就通过另外一台机器来干这个活就行, 我们在另外一个机器的数据库上创建一个带有新索引的表, 然后把数据慢慢的迁移到新数据库上, 最后迁移完成后, 替换数据库即可.

索引部分原理

数据结构讨论

我们上面说过, 索引是通过一定的数据结构来实现的, 那么它是通过什么数据结构来实现的呢? 我们先从一些基本的数据结构开始看.

首先是线性结构, 顺序表, 链表, 栈以及队列. 这些似乎也不怎么用看了, 直接就是可以忽略掉的, 毕竟它们本身就不擅长查找.

那此时我们就从擅长查找的结构来看, 哈希表, AVL树, 红黑树.

首先来看哈希表, 很明显哈希表作为查找结构非常优秀, 但是对于数据库来说并没有特别好, 为什么?

实际上就是因为它无法符合数据库搜索的两个基本操作, 一个是范围查询, 一个是模糊匹配.

我们的哈希表, 是通过映射来定位到一个精确的元素, 此时里面的元素可以说是无序的, 那么我们想要范围查询就很难. 同时也因为其是精确定位到元素, 此时模糊匹配自然也是很难做的.

那 AVL树和红黑树可以吗? 实际上虽然可以, 但是依旧是不太适合数据库, 为什么?

首先我们要想, 对于这两棵树, 影响它们查找效率的核心是什么? 很明显是树的高度, 而树的高度又是由什么决定的呢? 很明显是节点的数量, 换句话说就是数据的数量.

而对于数据库来说, 数据量那必然是很大的, 那么此时对于 AVL树和红黑树这样的基于二叉树的结构, 此时高度的增长就会十分恐怖, 从而影响他们的查找效率. 同时由于我们的数据库的数据是存储在硬盘中的, 每一次访问节点都是一次额外的硬盘IO, 而一次硬盘IO开销则相当于上万次的简单运算操作, 此时的开销自然就是非常大的了.

那此时就有人问了: 那有没有办法可以让这个树的分叉变多? 那这样高度不就会变低了吗?

实际上, 我们接下来要了解的一个新的数据结构, 就是一个 N 叉树的结构, 它就是B树, 有时也被写作B-树.

B-树

首先需要知道的是, 这个B-树它的叫法是B树, 而不是 B 减树, 不要把它作为B+树的对立体. 这个-不是减号, 而是连接符的意思.

B树主要就是根据其值, 来划分出了一个一个的范围, 如下图所示

在这里插入图片描述

它的每一个节点中都有 N 个 Key, 并且根据 N 个 Key 划分出了 N + 1 个区间, 分别作为下一层的节点. 同时它的分裂也并不是无限制的, 达到一定规模后就会停止. 同时当我们的 Key 增多后, 它也会自我进行分裂, 同理删除过多 Key 后, 他也会自行合并. 具体的实现可以根据场景而定.

此时我们可以看到, 假设我们通过B-树来维护数据库的索引, 那么此时无论是树的高度, 还是硬盘IO的次数, 都得到了改善. 此时我们通过一次硬盘IO, 就可以访问到多个值, 并且获取多个区间, 而不是和红黑树那样, 每一次访问都只能获取到一个值, 只能获取两个区间.

例如我想要寻找12, 首先我们会读取顶层的节点, 然后找到< 15的区间, 随后访问对应节点, 找到15.

B+树

B+树, 是为了数据库量身定做的一种对于B树的改良版本. 它同样是一个 N 叉搜索树, 它每一个节点也有 N 个 Key, 但是它的区间却只分成了 N 个区间.

它的分法是什么呢? 我们可以看以下几点:

每一个节点的 Key 中, 最后一个 Key 是当前子树的最大值
父节点上的 Key 会以最大值的身份在子节点上再次出现
最下一层叶子节点, 会被链表串起来

如下图所示, 就是一棵B+树

在这里插入图片描述

可以看到, 它的最高层节点中的 17 就是整个树的最大值, 同时分为两个区间, 一个是<= 8, 另一个则是<=17, 其余的也是同理.

此时可能有人就要问了: 那你的这些 Key 都会重复出现, 那你这些数据不都反复存了? 这也太浪费了吧.

实际上, 对于B+树来说, 只有叶子节点层是用于存储真实数据的, 其他非叶子节点只是用于存储一个用于排序的的 Key 而已. 例如我们的表中 id 是主键, 那么就可以拿 id 来作为 Key 进行分组. 并且此时由于 Key 是不会占据多少空间的, 我们还可以选择将非叶子节点全部放入内存中, 从而减少IO次数, 加快搜索的速度.

同时, 由于其用于存储数据的叶子节点, 由链表一个一个的串起来了, 如果要进行范围查询, 或者是遍历, 也是非常简单的, 直接找到起点往后走就行了.

对于B树和B+树来说, B+树还有一个优势就是它的搜索时间是稳定的.

如果是B树, 它的真实数据藏在树的各个角落, 此时如果运气好, 节点在高的地方, 查找的就快, 运气不好, 节点在下面, 那么找的就慢. 而对于B+树来说, 无论是什么数据, 都存在叶子节点上面, 此时要走的层数都是一样的, 时间开销就是一个稳定的数值了.

MySQL的索引实现

实际上MySQL的索引实现, 并不只有B+树的情况, 这主要由于 MySQL 有一个模块叫做存储引擎. 其中不同版本的存储引擎, 有着不同的实现. 不过我们目前最常用的存储引擎 Innodb 中, 其内部索引的实现就是B+树的, 因此我们暂时就了解一下B+树即可

事物

事物的概念

在日常的开发中, 往往会涉及到一些情景, 它们的操作需要一气呵成的完成的. 换句话说, 这种操作虽然是分步执行的, 但是却又不可拆分.

最经典的例子就是银行转账的例子, 假如张三转给李四 500 块, 此时主要就是分为两个步骤:

张三的余额 -500
李四的余额 +500

但是此时, 假如第一步执行后, 系统崩溃了, 此时就会导致这 500 块钱, "莫名奇妙"的消失了. 此时我们最好的解决办法就是, 让这两个操作能够成为一个原子操作, 要么两个都成功, 要么两个都失败.

在实际的系统开发中, 会有很多这样的操作需要保证它们能够同时成功或者同时失败, 因此数据库为了保证SQL的执行中也能考虑到这一点, 它就引入了事物.

事物的使用

事物的使用还是比较简单的, 主要涉及到的就是三个操作: 1. 开启事物 2. 提交事物 3. 回滚事物

其中开启事物指的就是, 接下来操作都会在一个事物中, 直到我的操作全部做完, 此时就可以提交事物, 保存我们在事物中的执行结果.

回滚事物指的是, 如果系统在中途崩溃, 或者我们自己手动的检测出了问题, 此时就可以触发回滚操作, 回滚指的就是将数据库的数据还原到开启事物前的状态. 其中这个回滚操作即可以是数据库自动回滚的, 也可以是我们手动回滚的

其中主要用到的就是下面三条指令

-- 开启事物
start transaction;-- 回滚事物
rollback;-- 提交事物
commit;

但是一般来说, 我们不会再数据库的命令行中直接去进行事物的操作, 而是会通过代码来触发. 所以我们这里了解一下即可.

此时可能有人要问了: 这个事物的回滚, 它是怎么做到的呢?

实际上也非常简单, 直接通过日志的方式, 将各个关键操作存储下来就行, 后续要回滚的时候, 就根据这些存储下来的操作把数据改回去就行. 同时, 这些日志是存储在文件中, 换句话说就是硬盘里面, 而硬盘里面的数据, 即使掉电了, 也不会丢失. 因此即使主机掉电, 此时主机重启后, MySQL自动读取到日志里面发现有要回滚的东西, 就会自动执行回滚操作了.