【MySQL】索引和事务

索引

索引是什么

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

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

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

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

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

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

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

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

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

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

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

索引的基本操作

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

-- 查看索引
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 块, 此时主要就是分为两个步骤:

1. 张三的余额 -500
2. 李四的余额 +500

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

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

事务的使用

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

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

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

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

-- 开启事物
start transaction;

-- 回滚事物
rollback;

-- 提交事物
commit;

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

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

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

事务的四大特性(ACID)

事务并不有着我们上面介绍到的原子性, 它同样还有着其他三个核心特性, 这四个特性被共成为事务的四大特性, 简称 ACID. 下面我们来了解一下

• 原子性(Atomicity): 事务中的所有操作, 要么同时成功, 要么同时失败
• 持久性(Durability): 事务中的操作一旦提交, 修改就是持久保存在硬盘中的, 即使断电也不会丢失
• 一致性(Consistency): 事务执行前和执行后的数据都需要符合要求.

一致性稍微有一点抽象, 这里举一个例子. 依旧是银行转账, 假设两个人的钱总数在转账前是 2000, 那么转账后, 这个数字不应该变化, 还应是2000, 以及不能出现钱是负数的情况等

• 隔离性(Isolation): 事务并发执行的时候, 事务之间不会互相影响

事务并发问题

众所周知, 像MySQL这样的数据库系统, 它基本上都是要服务于多个客户端的. 那么假设在同一个时间节点, 多个客户端同时向一个服务器发送请求, 要求进行操作, 此时就涉及到两种选择: 1. 一个一个的干 2. 全部一起干

那么如果要一个一个的干, 势必是效率很低的, 反之, 全部一起干的话, 效率就会高很多. 就和厨房炒菜一样, 炒 10 个菜, 那用 10 个锅当然比 1 个锅快.

那么此时这种多个事务同时进行的情况, 就被称作是并发. 同时, 我们也说过, 没有一个东西能够是完美的, 既然有它的优点, 那么就会有相应的缺点, 也就是会引起一些并发问题.

那么事务的并发, 会引起什么样的问题呢? 下面我们就依次来看

1. 脏读:

脏读, 指的就是读到了无效数据, 那么什么时候会读到无效数据呢?

假如张三正在写作业, 然后此时李四做不出来, 想要来抄抄张三的作业, 然后就在张三后面偷偷的看, 看完了之后就直接抄走了. 结果没想到的是, 张三后面发现自己做错了, 又改掉了自己的答案, 导致李四抄的是一个错误的答案.

此时张三写作业到写出正确作业的过程中, 就可以看作是一个事务, 而李四在张三执行事务的过程中, 读到了一个中间数据, 这个数据后面又被改掉了, 此时李四读到的中间数据就是一个无效数据, 也被称作是脏数据, 因此这个问题叫做脏读, 即可以读取到其他事务没有提交的脏数据.

2. 不可重复读:

发生了上面的问题, 李四和张三就说好, 李四不要偷偷的看答案, 直接和张三说就好了, 张三发给他看. 于是张三写好了一个作业后, 就直接发给了李四, 然后此时李四抄着抄着, 突然发现张三把刚刚发过来的作业撤回了, 换了一个新的, 导致刚刚白抄了.

此时张三在写完第一版作业的时候, 就可以看作是一个事务完成了. 同时它发出第二版的时候, 就相当于又完成了一个事务, 而李四的这个事务则是在一次事务的两次读取中, 获取到了不同的数据. 这个问题就叫做不可重复读, 即在一个事务中多次读取到的数据不同.

3. 幻读:

为了更好的抄作业, 李四这一次就选择直接把张三第一次发的作业存下来, 后面无论怎么改也不管了. 此时确实抄的非常顺利, 但是后面再去看张三发的作业, 发现张三后面又发了第二个作业.

此时就可以看作是李四虽然在自己的事务中读取的数据一直没有变, 但是后续再看张三发的作业的时候, 发现多出了其他的数据, 此时这个问题就叫幻读, 即在一个事务中, 多次查询返回的结果集不同.

---

当然此时可能有人看到这个幻读的例子, 觉得这个似乎并不是一个问题, 实际上也确实如此, 幻读这个问题, 在某些场景下也并不能说是真正的问题, 具体还是依场景而定.

例如我在当前事务中, 需要对带有自增主键目标表插入一个数据, 而此时我由于看不到它的结果集已经多了数据, 会尝试重复插入一个已经存在的主键, 此时就会导致插入失败, 因为主键已经存在了, 此时这个就算作是问题了.

而如果是当前事务只是读一读数据, 那么自然就不会引发什么问题.

事务的隔离级别

为了依次解决上面所提到的三个事务的并发问题, MySQL设置了四大隔离级别, 下面我们来看

• 读未提交: 最低的隔离级别, 事务可以读取到其他事务没有提交的数据修改, 会引起脏读问题
• 读已提交: 事务只能读取到其他事务提交后的修改, 会引起不可重复读问题
• 可重复读: 事务只会读取到事务开始前版本的数据, 保证自己读到的数据不变, 会引起幻读问题
• 串行化: 最高隔离级别, 所有事务串行一个一个的执行, 不会引发任何并发问题, 但是相应地, 效率极低

其中 MySQL 默认的隔离级别是可重复读级别的, 具体使用什么隔离级别可以根据具体需求而定. 如果希望修改隔离级别, 可以通过修改 MySQL 的配置文件来进行.