MySql篇

程序员企鹅君

于 2024-04-26 00:29:05 发布

阅读量907

点赞数 22

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/a821230/article/details/138202742

版权

索引

B-树

定义：

1、根节点至少包含两个孩子

2、每个节点最多包含m个孩子(m >= 2)，m为树的深度

3、除了根节点和叶子节点，其他节点至少有ceil(m/2)个孩子，ceil函数为取上限，例如ceil(1.2)=2，就是小数位多少，都入，不是四舍五入

4、叶子节点的高度相同

如果我们需要寻找key为28的数据，会经历3次磁盘I/O操作过程

PS：

1、我们从上图看到B树和二分搜索树有一点相似的地方，数据是有序的，也就是按照关键字进行排序

2、非叶子节点包含key和value，以及指向其子节点地址的指针

3、叶子节点只有key和value

B+树

非叶子节点的子树指针与关键字个数相同
非叶子节点的子树指针P[i]，指向关键字值的子树
非叶子节点仅用来索引，数据都保存在叶子节点中
所有叶子节点均有一个链指针指向下一个叶子节点（紫色块之间有链指针连接，从左到右升序排列）

B+树与B-树的区别

B+树是B树的一种优化
1、B树的每个结点都存储了key和data，B+树的data存储在叶子节点上。非叶子节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，磁盘IO操作次数更少。查询效率更高
2、B+树查询路径都是从非叶子结点，到叶子节点。效率比较稳定
3、B+树叶子结点是一个链表，扫描全表数据速度更快（只需要遍历叶子节点，并且范围查询也有优化）

因为B+树的这些好处，在 MySQL 中，MyISAM 引擎和 InnoDB 引擎都是使用 B+Tree 作为索引结构,

但是，两者的实现方式不太一样。MyISAM 引擎是使用的非聚簇索引，InnoDB 引擎使用的是聚簇索引

一棵B+树可以存放多少条数据？

假设B+树高度为2，一行数据记录大小为1k(实际上很多互联网业务数据就是1k左右)，单个叶子节点(页)中记录数16K/1K=16

B+树存在的数据总量 = 根节点节点指针数量 * 每页保存的数据量

假设主键为bigint，长度为8字节，指针大小在InnoDB源码中为6字节，这样一共14字节，我们一个页中存放16384/14=1170。那么一棵高度为

2的B+树，能存放1170*16=18720条这样的数据。

以此类推，一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

索引类型

按照数据模型维度划分：

B-Tree索引：B树索引是MySQL中最常见的索引类型，适用于大部分场景。它支持等值查询、范围查询和前缀匹配。
哈希索引：哈希索引是一种基于哈希表实现的索引，类似键值对的形式，一次即可定位, 等值查询非常快，但是不支持范围查询和前缀匹配
全文索引：全文索引是一种用于文本数据模糊查询的特殊索引，它基于倒排索引实现。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。效率较低，通常使用搜索引擎如 ElasticSearch 代替。

从底层存储方式维度划分：

聚簇索引（聚集索引）：索引结构和数据一起存放的索引，InnoDB 中的主键索引就属于聚簇索引。
非聚簇索引（非聚集索引）：索引结构和数据分开存放的索引，二级索引(辅助索引)就属于非聚簇索引。MySQL 的 MyISAM 引擎，不管主键还是非主键，使用的都是非聚簇索引。

聚簇索引(密集索引)

行数据和主键索引存储在一起，辅助键索引只存储辅助键和主键，而不保存数据

InnoDB使用的是聚簇索引，行数据保存在叶子节点上。如果通过where id = 5，直接通过主键索引找到对应的关键字，然后返回行数据

如果通过where name = tom，首先通过辅助键索引找到对应id = 5，然后再通过主键索引找到行数据

PS：

1).如果存在主键，主键就是密集索引

2).如果没有主键，表中第一个唯一非空索引为密集索引

3).如果以上都没有，InnoDB生成一个隐藏主键作为密集索引，是一个6字节的列，随着数据的插入自增

所以，InnoDB必须有个密集索引，这是因为非主键索引叶子节点不保存行数据，而是保存着主键值

非聚簇索引(稀疏索引)：

B+树叶子节点存储的是指向数据的指针

MyISAM使用的为非聚簇索引，主键索引保存了主键，非主键索引保存了非主键，而数据行保存在其他位置，检索的过程都是通过叶子节点内

保存的地址找到对应的数据行。

InnoDB为什么使用聚簇索引呢？

从上面索引过程我们可以看到，对于非主键查询来说，聚簇索引需要经过两次检索，好像效率更低了，那么聚簇索引的优势在哪？

1、行数据和叶子节点保存在一起，会一起被加载到内存，找到叶子节点就可以将数据库返回。

2、辅助索引的叶子节点保存主键的指针，而不使用地址值作为指针，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键

值当作指针会让辅助索引占用更多的空间，InnoDB在移动行时无须更新辅助索引中的这个"指针"。也就是说行的位置（实现中通过16K的Page来定

位，后面会涉及）会随着数据库里数据的修改而发生变化（前面的B+树节点分裂以及Page的分裂），使用聚簇索引就可以保证不管这个主键B+树的

节点如何变化，辅助索引树都不受影响。

页page:

InnoDB数据通过page存储，是最小的存储单位，page默认大小为16k，文件系统最小单位块为4k，通过下面参数设置

](https://img2018.cnblogs.com/blog/1351999/201907/1351999-20190709112105625-1669802737.png)

页可以用来存储数据也可以用来存储key和指针，分别对应非叶子节点和叶子节点。通过非叶子节点的二分查找和指针确定数据在哪一页，进

而查到对应的数据。

索引概念

MySQL中InnoDB引擎要求每张表都有要有一个聚簇索引（clustered index），也称为主键索引（primary key index），它的作用是将数据按照主键值排序，方便快速地访问单条记录。除了聚簇索引外，MySQL还可以有多辅助（二级）索引（secondary index），它们的作用是加速查询和排序操作。

回表查询

InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。

辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。

主键索引

数据表的主键列使用的就是主键索引。B+Tree的叶子节点存放的是主键字段值

通常说的主键索引就是聚簇索引。InnoDB的表要求必须要有聚簇索引：
● 如果表定义了主键，则主键索引就是聚簇索引
● 如果表没有定义主键，则第一个非空unique列作为聚簇索引
● 否则InnoDB会从建一个隐藏的row-id作为聚簇索引

检索过程：直接通过主键索引找到存储的数据

二级索引(辅助索引)

InnoDB二级索引，也叫作辅助索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

检索过程：先通过辅助索引找到主键索引，通过回表查询，然后再找到存储的数据

索引覆盖

在MySQL官网，类似的说法出现在explain查询计划优化章节，即explain的输出结果Extra字段为Usingindex时，能够触发索引覆盖。

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。

最左匹配原则

复合索引使用时遵循最左匹配原则，最左匹配顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

索引下推

我们以市民表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

你已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。当然，这还不错，总比全表扫描要好。然后呢？当然是判断其他条件是否满足。在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

图 3 无索引下推执行流程

图 4 索引下推执行流程

在图 3 和 4 这两个图里面，每一个虚线箭头表示回表一次。图 3 中，在 (name,age) 索引里面我特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。图 4 跟图 3 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。

索引为什么会失效

列类型不匹配：如果索引列和查询条件的数据类型不匹配，例如在一个字符串类型的索引列上执行了数值比较，那么索引就会失效。
函数操作：如果查询条件中使用了函数操作，例如在索引列上使用函数操作或者使用了自定义函数，那么索引也会失效。
索引列值为空：如果查询条件中使用了IS NULL或者IS NOT NULL操作，那么如果索引列上存在NULL值，那么索引就会失效。
表达式操作：如果查询条件中使用了表达式操作，例如对索引列进行加减乘除等操作，那么索引也会失效。
隐式类型转换：如果查询条件中使用了隐式类型转换，例如在一个字符串类型的索引列上执行了数值比较，并且数据库自动将字符串转换为数字类型，那么索引也会失效。
数据量太大：如果表中的数据量太大，那么对于一些非唯一索引列，索引的查询优化器可能会认为扫描整个表比使用索引更加高效，从而导致索引失效。
索引列上存在函数：如果索引列上使用了函数，例如在索引列上使用了UPPER()函数，那么索引也会失效。
最左匹配原则：联合索引要正确使用需满足最左匹配原则，即：符合第一列才会继续判断后面的字段。
使用OR 一列不是索引

正确使用索引

索引字段占用空间越小越好，大文本，大对象不要创建索引
索引并不是越多越好，有维护成本，空间成本

建议单表不超过5个索引
频繁更新的字段不适合作为索引
我们创建索引的字段应该是查询操作非常频繁的字段。
频繁需要排序的字段：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

如何分析sql使用索引情况

在 MySQL 中可以通过 EXPLAIN 关键字模拟优化器执行 SQL语句

explain select 列名 FROM 表名 WHERE 条件 ;

EXPLAIN输出内容如下：

各个字段的含义如下：

列名	含义
id	SELECT的查询序列号
select_type	主要用来说明查询的类型：普通查询、联合查询、子查询等
table	输出的行所引用的表
partitions	如果查询是基于分区表的话，显示查询将访问的分区。，对于未分区的表，值为 NULL
type	代表访问类型，是判断sql执行性能比较关键的一个字段
possible_keys	MySQL可能使用的键(索引)。
key	MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len	所选索引的长度
ref	表示索引的哪一列被使用显示使用哪个列或常数与key一起从表中选择行。
rows	预计要读取的行数
filtered	按表条件过滤后，留存的记录数的百分比
Extra	附加信息

type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

type代表访问类型，是判断sql执行性能比较关键的一个字段，性能从高到低依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system:表仅有一行(=系统表)。这是const联接类型的一个特例。
const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。
ref_or_:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
index_merge:该联接类型表示使用了索引合并优化方法。
unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
range:只检索给定范围的行,使用一个索引来选择行。
index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:对于每个来自于先前的表的行组合,进行完整的表扫描，说明查询就需要优化了。

一般来说，得保证查询至少达到range级别，最好能达到ref。

事务

事务简介

提到事务，你肯定不陌生，和数据库打交道的时候，我们总是会用到事务。最经典的例子就是转账，你要给朋友小王转 100 块钱，而此时你的银行卡只有 100 块钱。

转账过程具体到程序里会有一系列的操作，比如查询余额、做加减法、更新余额等，这些操作必须保证是一体的，不然等程序查完之后，还没做减法之前，你这 100 块钱，完全可以借着这个时间差再查一次，然后再给另外一个朋友转账，如果银行这么整，不就乱了么？这时就要用到“事务”这个概念了。

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
只有使用了 Innodb 数据库引擎的数据库或表才支持事务，比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

事务提交、回滚

事务提交操作

mysql> start transaction;#手动开启事务
mysql> insert into t_user(name) values('pp');
mysql> commit;#commit之后即可改变底层数据库数据, pp成功插入
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
|  3 | pp   |
+----+------+
3 rows in set (0.00 sec)

事务回滚操作

mysql> insert into t_user(name) values('yy');
mysql> rollback;  #事务回滚后，上面的操作也不去执行了，yy没有成功插入
mysql> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | jay  |
|  2 | man  |
|  3 | pp   |
+----+------+
3 rows in set (0.00 sec)

##事务四大特性

一般来说，事务（transaction）是必须满足4个条件（ACID）

原子性（Atomicity）

一致性（Consistency）

隔离性（Isolation）

持久性（Durability）

事务的目的是保障数据的一致性

只有保证了事务的持久性、原子性、隔离性之后，一致性才能得到保障。

也就是说 A、I、D 是手段，C 是目的

原子性：一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

并发事务会带来哪些问题

1.脏读：（读取未提交数据）

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

时间顺序	事务A	事务B
1		开始事务
2	开始事务
3		查询账户余额为2000元
4		取款1000元，余额被更改为1000元（未提交）
5	查询账户余额为1000元（读到了事务B还未提交的脏数据）
6		事务回滚，取款操作发生未知错误，余额变更为2000元
7	转入2000元，余额被更改为3000元（脏读1000+2000）
8	提交事务
备注	按照正常逻辑此时账户应该为4000元

2.不可重复读:（前后多次读取，数据内容不一致）

是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
例如，一个编辑人员两次读取同一文档，但在两次读取之间，作者重写了该文档。当编辑人员第二次读取文档时，文档已更改。原始读取不可重复。如果只有在作者全部完成编写后编辑人员才可以读取文档，则可以避免该问题。

时间顺序	事务A	事务B
1	开始事务
2	第一次查询，小明的年龄为20岁
3		开始事务
4	其他操作
5		更改小明的年龄为30岁
6		提交事务
7	第二次查询，小明的年龄为30岁
备注	按照正确逻辑，事务A前后两次读取到的数据应该一致

3.幻读:（前后多次读取，数据总量不一致）

事务在插入已经检查过不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测获取到的数据如同鬼影一般。

例子1：

时间顺序	事务A	事务B
1	开始事务
2	第一次查询，数据总量为100条
3		开始事务
4	其他操作
5		新增100条数据
6		提交事务
7	第二次查询，数据总量为200条
备注	按照正确逻辑，事务A前后两次读取到的数据总量应该一致，第二次没有插入过查出来却多了100条记录，像见鬼了一样，产生了幻读问题

不可重复读和幻读的区别

（1）不可重复读是读取了其他事务更改的数据，针对update操作
解决：使用行级锁，锁定该行，事务A多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。

（2）幻读是读取了其他事务新增的数据，针对insert与delete操作
解决：使用表级锁，锁定整张表，事务A多次读取数据总量之后才释放该锁，这个时候才允许其他事务新增数据。

幻读和不可重复读都是指的一个事务范围内的操作受到其他事务的影响了。只不过幻读是重点在插入和删除，不可重复读重点在修改

事务隔离性

隔离级别

（1）读未提交：read uncommitted

一个事务还没提交时，它做的变更就能被别的事务看到。
最低的隔离级别，脏读、不可重复读或幻读都有可能发生，数据库隔离级别一般都高于该级别

（2）读已提交：read committed

一个事务提交之后，它做的变更才会被其他事务看到。
可以阻止“脏读”，但是幻读或不可重复读仍有可能发生。

（3）可重复读：repeatable read

一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
可以阻止“脏读”和“不可重复读”，但幻读仍有可能发生。
InnoDB引擎默认隔离级别

（4）串行化：serializable

事务A和事务B，事务A在操作数据库时，事务B只能排队等待, 必须事务A执行完成，才能继续执行
这种隔离级别很少使用，吞吐量低，用户体验差
可以阻止脏读、不可重复读以及幻读。

	脏读	不可重复读	幻读
读未提交 Read uncommitted	可能	可能	可能
读已提交 Read committed	不可能	可能	可能
可重复读 Repeatable read	不可能	不可能	可能
串行化 Serializable	不可能	不可能	不可能

事务实现的原理

事务的原子性是通过undolog来实现的

事务的持久性性是通过redolog来实现的

事务的隔离性是通过(读写锁+MVCC)来实现的

事务的终极大 boss 一致性是通过原子性，持久性，隔离性来实现的！！！

原子性，持久性，隔离性的目的也是为了保障数据的一致性！

总之，ACID只是个概念，事务最终目的是要保障数据的可靠性，一致性。

下面我首先讲实现事务功能的三个技术，分别是日志文件(redo log 和 undo log)，锁技术以及MVCC，然后再讲事务的实现原理，包括原子性是怎么实现的，隔离型是怎么实现的等等。最后在做一个总结，希望大家能够耐心看完

redo log 与 undo log介绍

什么是redo log ?

redo log叫做重做日志，是用来实现事务的持久性。该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中。

– 银行卡账户表 bank –

id	name	balance
1	zhangsan	1000

--理财账户表 finance –

id	name	amount
1	zhangsan	0

start transaction;
select balance from bank where name="zhangsan";
// 生成 重做日志 balance=600
update bank set balance = balance - 400; 
// 生成 重做日志 amount=400
update finance set amount = amount + 400;

redo log作用是什么？

mysql 为了提升性能不会把每次的修改都实时同步到磁盘，而是会先存到Boffer Pool(缓冲池)里头，把这个当作缓存来用。然后使用后台线程去做缓冲池和磁盘之间的同步。

那么问题来了，如果还没来的同步的时候宕机或断电了怎么办？还没来得及执行上面图中红色的操作。这样会导致丢部分已提交事务的修改信息！

所以引入了redo log来记录已成功提交事务的修改信息，并且会把redo log持久化到磁盘，系统重启之后在读取redo log恢复最新数据。

总结：redo log是用来恢复数据的，用于保障，已提交事务的持久化特性（记录了已经提交的操作）

什么是undo log？

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。
还用上面那两张表

每次写入数据或者修改数据之前都会把修改前的信息记录到 undo log。

undo log 有什么作用？

undo log 记录事务修改之前版本的数据信息，因此假如由于系统错误或者rollback操作而回滚的话可以根据undo log的信息来进行回滚到没被修改前的状态。

总结：undo log是用来回滚数据的用于保障，未提交事务的原子性

mysql锁技术以及MVCC基础

mysql锁技术

当有多个请求来读取表中的数据时可以不采取任何操作，但是多个请求里有读请求，又有修改请求时必须有一种措施来进行并发控制。不然很有可能会造成不一致。
读写锁
解决上述问题很简单，只需用两种锁的组合来对读写请求进行控制即可，这两种锁被称为：

共享锁(shared lock),又叫做"读锁"
读锁是可以共享的，或者说多个读请求可以共享一把锁读数据，不会造成阻塞。
排他锁(exclusive lock),又叫做"写锁"
写锁会排斥其他所有获取锁的请求，一直阻塞，直到写入完成释放锁。

读锁写锁
读锁可并行不可并行
写锁不可并行不可并行
总结：通过读写锁，可以做到读读可以并行，但是不能做到写读，写写并行

	读锁	写锁
读锁	可并行	不可并行
写锁	不可并行	不可并行

MVVC 实现原理

MVCC（Multiversion Concurrency Control），多版本并发控制。它和undo log中的版本链息息相关，MVVC通过数据行的多个版本来实现数据库的并发控制。

简单的说就是当前事务查询另一个事务正在更改的行（如果此时读取就会发生脏读），不用加锁等待，而是读取该数据的历史版本，降低响应时间。

MVCC的实现依赖于Undo日志和Read View。下面我们先来详细介绍一下这两个机制。

Undo日志

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

Undo存放在数据库内部的一个特殊段（segment）中，这个段称为Undo段（undo segment）。Undo段位于系统表空间内，也可以设置为Undo表空间。

Undo日志保存了记录修改前的数据，并且用两个隐藏字段trx_id和roll_pointer把这些Undo日志串联起来形成一个历史记录版本链（参考图1）。

Read View

有了undo log就可以读取到记录的历史版本，那么在什么情况下，读取哪个版本的记录呢？这就用到了Read View，它帮我们解决了行的可见性问题。

Read View就是当某个事务在使用MVVC机制进行快照读操作时产生的读视图。该视图是数据库当前所有活跃事务id（还未提交的事务）组成的列表的一个快照。

1.实现原理

四种隔离级别里，读未提交和串行化是不会使用MVVC的，因为读未提交直接读取某个数据的最新数据即可，串行化是通过加锁来读的。

读已提交和可重复读都必须保证读到的数据都是其他事务提交了的，所以，其他事务修改了数据但是还未提交，我们不能够访问该数据，但可以通过MVVC机制读取该记录的历史版本，核心问题就是需要判断版本链中的哪条历史版本是当前事务可见的，这也是ReadView要解决的问题。

Read View包含4个比较重要的内容：

creator_trx_id：创建这个Read View的事务id，Read View和事务是一一对应的。

只有事务对表中的记录做修改时才会为事务分配事务id，否则一个事务中只有读操作，该事务的id默认为0。

trx_ids：表示在生成Read View时当前系统中活跃的事务id列表。提交了的事务不在其中。
up_limit_id：活跃的事务中最小的事务id。
low_limit_id：表示生成Read View时系统应该分配给下一个事务的id值，同样也表示系统中最大的事务id值。

注意：low_limit_id并不是trx_ids中的最大值，事务id是递增分配的。比如，现在有id为1， 2，5这三个事务，之后id为5的事务提交了。那么一个新的读事务在生成ReadView时， trx_ids就包括1和2，up_limit_id的值就是1，low_limit_id的值就是6。

2.Read View规则

版本链

当某个事务有了Read View，访问某条记录时，需要按照下面的步骤判断该记录的哪个版本可见：

如果该版本记录的trx_id和Read View的creator_trx_id相同，意味着该版本的记录是由当前事务修改的，因此该版本可以被当前事务访问
如果该版本记录的trx_id小于Read View的up_limit_id，证明当前事务生成Read View时，此事务已经提交了，所以当前事务可以读取该版本。
如果该版本的trx_id大于等于low_limit_id，证明生成该版本的事务在当前事务生成Read View之后才开启，所以该版本不可以被当前事务访问。
如果被访问版本的trx_id属性值在ReadView的up_limit_id和low_limit_id之间，那就需要判断一下trx_id属性值是不是在trx_ids列表中，如果不在的话才能访问，否则不能访问。

3.整体流程

了解了这些概念之后，我们来看下当查询一条记录的时候，系统如何通过MVCC找到它：

首先获取事务自己的版本号，也就是事务ID；
获取 ReadView；
查询得到的数据，然后与 ReadView 中的事务版本号进行比较；
如果不符合 ReadView 规则，就需要从Undo Log中获取历史快照；
最后返回符合规则的数据。

在隔离级别为读已提交时，一个事务中的每一次SELECT查询都会重新获取一次Read View，而可重复读是第一SELECT操作才会生成Read View，之后的查询操作复用这一个。

导致这两种的差距是因为：可重复读要保证一个事务中相同的SELECT读取的内容是相同的。

五、举例

1.READ

COMMITTED隔离级别下

现在有两个事务id分别为10、20的事务在执行：

-- id为10的事务
begin;
update t set name='李四' where id=1;
update t set name='王五' where id=1;
-- id为20的事务
更新其他行的数据

此刻，表中id为1的记录得到的版本链表如下所示：

此时新来一个事务执行如下操作：

begin;
select * from t where id=1;
-- 事务10、20未提交

查询到的结果为张三。

具体的过程如下：

在执行select语句前，先生成一个Read View，Read View的creator_trx_id为0，trx_ids列表的内容是[10,20]，up_limit_id为10，low_limit_id为21。
查询name为王五的最新版本的记录，按规则进行对比，因为trx_id为10，10刚好是trx_ids中的记录，所以这条记录对当前事务不可见，根据回滚指针得到下一个版本
下一个版本name为李四，也不行
继续找到name为张三的版本，trx_id为8，8小于up_limit_id，所以该版本对当前事务可见，得到最终结果

接下来，再将id为10的事务进行commit提交。然后id为20的事务来更新记录：

begin;
-- id为20的事务
update t set name='赵六' where id=1;
update t set name='钱七' where id=1;

此时版本链更新为：

再到刚才使用READ COMMITTED隔离级别的事务中继续查找这个id 为1的记录，得到的结果为name=王五的那条记录。执行过程如下：

生成Read View，Read View的creator_trx_id为0，trx_ids列表的内容是[20]，up_limit_id为20，low_limit_id为21。
因为前两个版本的记录trx_id为20，存在trx_ids中，所以跳过
到第三条记录时，trx_id为10，小于20，可以读取，所以最终结果为王五

注意：READ COMMITTED，每次读取数据前都生成一个新的ReadView。

2.REPEATABLE READ隔离级别下

假如此时id为10的事务和id为20的事务正在修改，都未提交，修改内容和前面的一样，但是还未提交，此时当前事务做一个查询。

步骤为：

生成Read View，Read View的creator_trx_id为0，trx_ids列表的内容是[10,20]，up_limit_id为10，low_limit_id为21。
trx_id为10和20的都不满足要求
最后查找到name为张三的历史版本的数据

此时，id为10的记录提交事务。

当前事务又需要select id为1的记录，步骤为：

因为是可重复读，且第一次select已经生成过Read View了，所有会复用它，不重新生成。
所以trx_id为10和20的记录依旧不符合规则，最终得到的数据还是张三，符合可重复读的规范

注意：REPEATABLE READ，每次读取都复用第一次生成的Read View

3.如何解决幻读

假设现在有一条数据，id为1

当前活跃的事务有10和20。

此时当前事务启动了，执行如下SQL语句：

begin;
select * from student where id>=1;

在开始前生成Read View，内容如下：creator_trx_id=0，trx_ids= [10,20] ， up_limit_id=10， low_limit_id=21。

由于id大于等于1的数据只有一个，且该数据的trx_id为8，小于up_limit_id，所以可以读取到。

在这之后id为10的事务新增了一行数据，增加了id为2的数据，且提交了。

此时当前线程继续查找id>=1的数据，因为是可重复读，复用刚刚的Read View。

得到两行数据，但是因为id为2的数据trx_id为10，该值在Read View的trx_ids中存在，所以该记录对当前事务不可见，所以最后查询到的数据只有一条记录。

如果当前事务再插入id为2的数据就插不进去，所以说MVVC只解决了一半的幻读问题。

程序员企鹅君

关注

22
点赞
踩
27

收藏

觉得还不错? 一键收藏
0
评论
MySql篇

提到事务，你肯定不陌生，和数据库打交道的时候，我们总是会用到事务。最经典的例子就是转账，你要给朋友小王转 100 块钱，而此时你的银行卡只有 100 块钱。转账过程具体到程序里会有一系列的操作，比如查询余额、做加减法、更新余额等，这些操作必须保证是一体的，不然等程序查完之后，还没做减法之前，你这 100 块钱，完全可以借着这个时间差再查一次，然后再给另外一个朋友转账，如果银行这么整，不就乱了么？这时就要用到“事务”这个概念了。简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。
复制链接

扫一扫