面试MySQL事务相关

约定不等于承诺〃 2023-09-28 23:17 45阅读 0赞

### 事务的四个特征（ACID） ###

事务具有四个特征：原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（ Isolation ）和持续性（ Durability ）。这四个特性简称为 ACID 特性。

1 、原子性。事务是数据库的逻辑工作单位，事务中包含的各操作要么都做，要么都不做

2 、一致性。事 务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。因此当数据库只包含成功事务提交的结果时，就说数据库处于一致性状态。如果数据库系统 运行中发生故障，有些事务尚未完成就被迫中断，这些未完成事务对数据库所做的修改有一部分已写入物理数据库，这时数据库就处于一种不正确的状态，或者说是 不一致的状态。

3 、隔离性。一个事务的执行不能其它事务干扰。即一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务之间不能互相干扰。

4 、持续性。也称永久性，指一个事务一旦提交，它对数据库中的数据的改变就应该是永久性的。接下来的其它操作或故障不应该对其执行结果有任何影响。

### Mysql的四种隔离级别 ###

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读） mysql默认隔离级别

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

### 数据库存储引擎 ###

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。  
  
现在许多数据库管理系统都支持多种不同的存储引擎。[MySQL][] 的核心就是存储引擎。  
  
MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySql存储引擎：

![2ad3ce7b78644e5abd84570d71515143.png][]

Mysql在5.1之前默认存储引擎是MyISAM；在此之后默认存储引擎是InnoDB

查看默认存储引擎

查看当前mysql默认引擎: show variables like '%engine%';

### InnoDB存储引擎 ###

1.InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID）

事务的ACID属性：即原子性、一致性、隔离性、持久性

a.原子性：原子性也就是说这组语句要么全部执行，要么全部不执行，如果事务执行到一半出现错误，数据库就要回滚到事务开始执行的地方。

实现：主要是基于MySQ日志系统的redo和undo机制。事务是一组SQL语句，里面有选择，查询、删除等功能。每条语句执行会有一个节点。例如，删除语句执行后，在事务中有个记录保存下来，这个记录中储存了我们什么时候做了什么事。如果出错了，就会回滚到原来的位置，redo里面已经存储了我做过什么事了，然后逆向执行一遍就可以了。

b.一致性：事务开始前和结束后，数据库的完整性约束没有被破坏。（eg:比如A向B转账，不可能A扣了钱，B却没有收到）

c.隔离性：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰；

如果不考虑隔离性则会出现几个问题：

i、脏读：是指在一个事务处理过程里读取了另一个未提交的事务中的数据（当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致）；（读取了另一个事务未提交的脏数据）

ii、不可重复读：在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了；（读取了前一个事务提交的数据，查询的都是同一个数据项）

iii、虚读（幻读）：是事务非独立执行时发生的一种现象（eg:事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样）；（读取了前一个事务提交的数据，针对一批数据整体）

d.持久性：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚

2.InnoDB是mySQL默认的存储引擎，默认的隔离级别是RR，并且在RR的隔离级别下更近一步，通过多版本并发控制（MVCC）解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此InnoDB的RR隔离级别其实实现了串行化级别的效果，而保留了比较好的并发性能。

MySQL数据库为我们提供的四种隔离级别：

a、Serializable（串行化）：可避免脏读、不可重复读、幻读的发生；

b、Repeatable read（可重复读）：可避免脏读、不可重复读的发生；

c、Read committed（读已提交）：可避免脏读的发生；

d、Read uncommitted（读未提交）：最低级别，任何情况都无法保证；

从a----d隔离级别由高到低，级别越高，执行效率越低

3.InnoDB支持行级锁。行级锁可以最大程度的支持并发，行级锁是由存储引擎层实现的。

锁：锁的主要作用是管理共享资源的并发访问，用于实现事务的隔离性

类型：共享锁（读锁）、独占锁（写锁)

MySQL锁的力度：表级锁（开销小、并发性低），通常在服务器层实现

行级锁（开销大、并发性高），只会在存储引擎层面进行实现

4、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何基于磁盘的关系型数据库引擎所不能匹敌的

5、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）；

6、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按照主键顺序存放，如果没有显示在表定义时指定主键。InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键

7、InnoDB被用在众多需要高性能的大型数据库站点上

8、InnoDB中不保存表的行数（eg:select count(\*)from table时，InnoDB需要扫描一遍整个表来计算有多少行）；清空整个表时，InnoDB是一行一行的删除，效率非常慢；

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib\_logfile0和ib\_logfile1的5MB大小的日志文件。

### Mysql与B+树： ###

当我们发现SQL执行很慢的时候，自然而然想到的就是加索引。对于范围查询，索引的底层结构就是B+树：

*  树简介、树种类
 *  B-树、B+树简介
 *  B+树插入
 *  B+树查找
 *  B+树删除
 *  B+树经典面试题

#### 树简介、树种类： ####

树跟数组、链表、堆栈一样，是一种数据结构。它由有限个节点，组成具有层次关系的集合。因为它看起来像一棵树，所以得其名。一颗普通的树如下:

![29237f2755784f50bda69a52c0fc453c.png][]

树是包含n(n为整数，大于0)个结点， n-1条边的有穷集，它有以下特点：

*  每个结点或者无子结点或者只有有限个子结点;
 *  有一个特殊的结点,它没有父结点，称为根结点;
 *  每一个非根节点有且只有一个父节点;
 *  树里面没有环路

一些有关于树的概念：

*  结点的度：一个结点含有的子结点个数称为该结点的度;
 *  树的度：一棵树中，最大结点的度称为树的度;
 *  父结点：若一个结点含有子结点，则这个结点称为其子结点的父结点;
 *  深度：对于任意结点n,n的深度为从根到n的唯一路径长，根结点的深度为0;
 *  高度：对于任意结点n,n的高度为从n到一片树叶的最长路径长，所有树叶的高度为0;

按照有序性，可以分为有序树和无序树：

*  无序树：树中任意节点的子结点之间没有顺序关系
 *  有序树：树中任意节点的子结点之间有顺序关系

按照节点包含子树个数，可以分为B树和二叉树，二叉树可以分为以下几种：

*  二叉树：每个节点最多含有两个子树的树称为二叉树;
 *  二叉查找树：首先它是一颗二叉树，若左子树不空，则左子树上所有结点的值均小于它的根结点的值;若右子树不空，则右子树上所有结点的值均大于它的根结点的值;左、右子树也分别为二叉排序树;
 *  满二叉树：叶节点除外的所有节点均含有两个子树的树被称为满二叉树;
 *  完全二叉树：如果一颗二叉树除去最后一层节点为满二叉树，且最后一层的结点依次从左到右分布
 *  霍夫曼树：带权路径最短的二叉树。
 *  红黑树：红黑树是一颗特殊的二叉查找树，每个节点都是黑色或者红色，根节点、叶子节点是黑色。如果一个节点是红色的，则它的子节点必须是黑色的。
 *  平衡二叉树(AVL)：一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。

#### **B-树、B+树简介** ####

**B-树简介**  
B-树，也称为B树，是一种平衡的多叉树(可以对比一下平衡二叉查找树)，它比较适用于对外查找。看下这几个概念哈：

*  阶数：一个节点最多有多少个孩子节点。(一般用字母m表示)
 *  关键字：节点上的数值就是关键字
 *  度：一个节点拥有的子节点的数量。

一颗m阶的B-树，有以下特征：

*  根结点至少有两个子女;
 *  每个非根节点所包含的关键字个数 j 满足：⌈m/2⌉ - 1 <= j <= m - 1.(⌈⌉表示向上取整)
 *  有k个关键字(关键字按递增次序排列)的非叶结点恰好有k+1个孩子。
 *  所有的叶子结点都位于同一层。

一棵简单的B-树如下：

![2b6a7dff845e4aac990f96a3e3934d3c.png][]

**B+ 树简介**  
B+树是B-树的变体，也是一颗多路搜索树。一棵m阶的B+树主要有这些特点：

*  每个结点至多有m个子女;
 *  非根节点关键值个数范围：⌈m/2⌉ - 1 <= k <= m-1
 *  相邻叶子节点是通过指针连起来的，并且是关键字大小排序的。

一颗3阶的B+树如下：![e66e637c3395498fb2308e444a79b3ae.png][]

B+树和B-树的主要区别如下：

*  B-树内部节点是保存数据的;而B+树内部节点是不保存数据的，只作索引作用，它的叶子节点才保存数据。
 *  B+树相邻的叶子节点之间是通过链表指针连起来的，B-树却不是。
 *  查找过程中，B-树在找到具体的数值以后就结束，而B+树则需要通过索引找到叶子结点中的数据才结束
 *  B-树中任何一个关键字出现且只出现在一个结点中，而B+树可以出现多次。

**B+树的插入**  
B+树插入要记住这几个步骤：

*  1.B+树插入都是在叶子结点进行的，就是插入前，需要先找到要插入的叶子结点。
 *  2.如果被插入关键字的叶子节点，当前含有的关键字数量是小于阶数m，则直接插入。
 *  3.如果插入关键字后，叶子节点当前含有的关键字数目等于阶数m，则插，该节点开始「分裂」为两个新的节点，一个节点包含⌊m/2⌋ 个关键字，另外一个关键字包含⌈m/2⌉个关键值。(⌊m/2⌋表示向下取整，⌈m/2⌉表示向上取整，如⌈3/2⌉=2)。
 *  4.分裂后，需要将第⌈m/2⌉的关键字上移到父结点。如果这时候父结点中包含的关键字个数小于m，则插入操作完成。
 *  5.分裂后，需要将⌈m/2⌉的关键字上移到父结点。如果父结点中包含的关键字个数等于m，则继续分裂父结点。

以一颗4阶的B+树为例子吧，4阶的话，关键值最多3(m-1)个。假设插入以下数据43，48，36，32,37,49,28.  
1.在空树中插入43

![2fe4f9db38ab4198b6c161abc8d59e6e.png][]

这时候根结点就一个关键值，此时它是根结点也是叶子结点。  
2.依次插入48，36

![b99559816a314c8c9d79c695eb8a1317.png][]

这时候跟节点拥有3个关键字，已经满了  
3.继续插入 32，发现当前节点关键字已经不小于阶数4了，于是分裂 第⌈4/2⌉=2(下标0,1,2)个，也即43上移到父节点。

![d3f8917fd60d4ed2952142a24fe47ce1.png][]

4.继续插入37，49，前节点关键字都是还没满的，直接插入，如下：

![71d8e62661574d72951faef7407941bf.png][]

5.最后插入28，发现当前节点关键字也是不小于阶数4了，于是分裂，于是分裂， 第 ⌈4/2⌉=2个，也就是36上移到父节点，因父子节点只有2个关键值，还是小于4的，所以不用继续分裂，插入完成

![63b4823d088a4569aac3aa1b5bd5ebf2.png][]

**B+树的查找**  
因为B+树的数据都是在叶子节点上的，内部节点只是指针索引的作用，因此，查找过程需要搜索到叶子节点上。还是以这颗B+树为例：

![9cb4b8434ed447a0a4844b356329ec2d.png][]

**B+ 树单值查询**  
假设我们要查的值为32.  
第一次磁盘 I/O，查找磁盘块1，即根节点(36,43),因为32小于36，因此访问根节点的左边第一个孩子节点 ，第二次磁盘 I/O, 查找磁盘块2，即根节点的第一个孩子节点，获得区间(28,32),遍历即可得32。

![e630b5cbe0c7fbe455c7f68c238493e5.gif][]

**B+ 树范围查询**  
假设我们要查找区间 \[32,40\]区间的值.  
第一步先访问根节点，发现区间的左端点32小于36,则访问根节点的第一个左子树(28,32);

第二步访问节点(28,32)，找到32，于是开始遍历链表，把\[32,40\]区间值找出来，这也是B+树比B-树高效的地方。

**B+树的删除**  
B+树删除关键字，分这几种情况

*  找到包含关键值的结点，如果关键字个数大于⌈m/2⌉-1，直接删除即可;
 *  找到包含关键值的结点,如果关键字个数大于⌈m/2⌉-1，并且关键值是当前节点的最大(小)值，并且该关键值存在父子节点中，那么删除该关键字，同时需要相应调整父节点的值。
 *  找到包含关键值的结点，如果删除该关键字后，关键字个数小于⌈m/2⌉，并且其兄弟结点有多余的关键字，则从其兄弟结点借用关键字
 *  找到包含关键值的结点，如果删除该关键字后，关键字个数小于⌈m/2⌉，并且其兄弟结点没有多余的关键字，则与兄弟结点合并。

**B+树经典面试题**

*  InnoDB一棵B+树可以存放多少行数据?
 *  为什么索引结构默认使用B+树，而不是hash，二叉树，红黑树，B-树?
 *  B-树和B+树的区别

**InnoDB一棵B+树可以存放多少行数据?**  
这个问题的简单回答是：约2千万行。

*  在计算机中，磁盘存储数据最小单元是扇区，一个扇区的大小是512字节。
 *  文件系统中，最小单位是块，一个块大小就是4k;
 *  InnoDB存储引擎最小储存单元是页，一页大小就是16k。

![ed9bd9e4671f4351a00a974bcfa92d30.png][]

因为B+树叶子存的是数据，内部节点存的是键值+指针。索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而再去数据页中找到需要的数据;

假设B+树的高度为2的话，即有一个根结点和若干个叶子结点。这棵B+树的存放总记录数为=根结点指针数\*单个叶子节点记录行数。

*  如果一行记录的数据大小为1k，那么单个叶子节点可以存的记录数 =16k/1k =16.
 *  非叶子节点内存放多少指针呢?我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，所以就是8+6=14字节，16k/14B =16\*1024B/14B = 1170

因此，一棵高度为2的B+树，能存放1170 \* 16=18720条这样的数据记录。同理一棵高度为3的B+树，能存放1170 \*1170 \*16 =21902400，也就是说，可以存放两千万左右的记录。B+树高度一般为1-3层，已经满足千万级别的数据存储。  
**为什么索引结构默认使用B+树，而不是B-Tree，Hash哈希，二叉树，红黑树?**  
简单版回答如下：

*  Hash哈希，只适合等值查询，不适合范围查询。
 *  一般二叉树，可能会特殊化为一个链表，相当于全表扫描。
 *  红黑树，是一种特化的平衡二叉树，MySQL 数据量很大的时候，索引的体积也会很大，内存放不下的而从磁盘读取，树的层次太高的话，读取磁盘的次数就多了。
 *  B-Tree，叶子节点和非叶子节点都保存数据，相同的数据量，B+树更矮壮，也是就说，相同的数据量，B+树数据结构，查询磁盘的次数会更少。

**B-树和B+树的区别**

[MySQL]: http://c.biancheng.net/mysql/
[2ad3ce7b78644e5abd84570d71515143.png]: https://img-blog.csdnimg.cn/2ad3ce7b78644e5abd84570d71515143.png
[29237f2755784f50bda69a52c0fc453c.png]: https://img-blog.csdnimg.cn/29237f2755784f50bda69a52c0fc453c.png
[2b6a7dff845e4aac990f96a3e3934d3c.png]: https://img-blog.csdnimg.cn/2b6a7dff845e4aac990f96a3e3934d3c.png
[e66e637c3395498fb2308e444a79b3ae.png]: https://img-blog.csdnimg.cn/e66e637c3395498fb2308e444a79b3ae.png
[2fe4f9db38ab4198b6c161abc8d59e6e.png]: https://img-blog.csdnimg.cn/2fe4f9db38ab4198b6c161abc8d59e6e.png
[b99559816a314c8c9d79c695eb8a1317.png]: https://img-blog.csdnimg.cn/b99559816a314c8c9d79c695eb8a1317.png
[d3f8917fd60d4ed2952142a24fe47ce1.png]: https://img-blog.csdnimg.cn/d3f8917fd60d4ed2952142a24fe47ce1.png
[71d8e62661574d72951faef7407941bf.png]: https://img-blog.csdnimg.cn/71d8e62661574d72951faef7407941bf.png
[63b4823d088a4569aac3aa1b5bd5ebf2.png]: https://img-blog.csdnimg.cn/63b4823d088a4569aac3aa1b5bd5ebf2.png
[9cb4b8434ed447a0a4844b356329ec2d.png]: https://img-blog.csdnimg.cn/9cb4b8434ed447a0a4844b356329ec2d.png
[e630b5cbe0c7fbe455c7f68c238493e5.gif]: https://img-blog.csdnimg.cn/img_convert/e630b5cbe0c7fbe455c7f68c238493e5.gif
[ed9bd9e4671f4351a00a974bcfa92d30.png]: https://img-blog.csdnimg.cn/ed9bd9e4671f4351a00a974bcfa92d30.png