interview/MySQL篇.md

MYSQL优化

慢查询

如何定位慢查询

表象:页面加载过慢,接口压测响应时间过长(超过1S)

慢查询日志记录功能:

慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒)的所有SQL语句的日志

如果要开启慢查询日志，需要在MySQL的配置文件 (/etc/my.cnf) 中配置如下信息

配置完毕之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息

/var/lib/mysal/localhost-slow.log

如何分析慢查询的SQL语句

导致慢查询的因素大致包括一下几点:

聚合查询 多表查询 表数据量过大查询 深度分页查询

其中前三个问题都可以都看查看SQL语句的执行计划寻找到慢的原因 深度分页查询需要借助于覆盖索引进行优化

possible_keys: 当前SQL可能会使用到的索引信息

key: 当前SQL实际命中的索引信息

key_len:索引的占用情况

type:SQL的连接类型 性能由好到差为

• ​ const: 根据主键索引查询

• ​ eq_ref:主键索引查询或者唯一索引查询 查询只返回一条数据

• ​ ref : 根据其他索引查询 查询数据可能是多条

• ​ range:范围查询（最低要求）

• ​ index:全索引树扫描

• ​ all:全表扫描

Extra:额外的优化建议

​ Using where Using Index :性能好表现；查询使用了索引,需要的数据都在索引列中能找到,不需要回表查询数据

​ Using index Condition:性能存在优化空间，查询使用了索引,但是需要回表查询数据 由此提示说明索引的使用有优化的空间

什么是索引

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构(B+树)，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。

动态演示回顾

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

B-Tree

B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。以一颗最大度数(max-degree)为5(5阶)的b-tree为例，那这个B树每个节点最多存储4个key

B+Tree

B+Tree相对于B-Tree有几点不同：

1）B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了；

2）B+树查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当；

3）B+树便于范围查询（最重要的原因，范围查找是数据库的常态）

B树在提高了IO性能的同时并没有解决元素遍历时候效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

聚簇索引与二级索引（聚簇索引）

聚簇索引：Clustered Index 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 该索引必须要有而且只能有一个

非聚簇索引：Secondary Index 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 该索引可以存在多个

聚簇索引的选择规则:

• 如果存在主键，主键索引就是聚簇索引。
• 如果不存在主键，将使用第一个唯一 (UNIQUE) 索引作为聚簇索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚簇索引。

什么是Mysql回表

select * from dept where deptName = 'xxx'

Mysql回表指的是在InnoDB存储引擎下，二级索引查询到的索引列，如果需要查找所有列的数据，则需要到主键索引里面去取出数据。这个过程就称为回表。因为行的数据都是存在主键B+tree的叶子节点里面，二级索引的B+树叶子节点都是存放的(索引列,主键)

什么是覆盖索引

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到

使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *

eg:如下数据库表 其中id 为主键索引 name字段添加了普通索引

id	name	gender	createTime
2	Arm	1	2024-01-01
3	Lily	0	2024-01-03
5	Rose	0	2023-01-01
6	Zoo	1	2023-12-01
8	Doc	1	2023-12-03
11	Lee	1	2022-12-01

select * from user where id = 1 --->覆盖索引

select id，name from user where name =' Arm' --覆盖索引

select id，name，gender from tb user where name =' Arm' --非覆盖索引 需要回表查询

分析如下图:

select * from tb user where id = 1 --->覆盖索引

select id，name from tb user where name =' Arm' --覆盖索引

select id，name，gender from tb user where name =' Arm' --非覆盖索引 需要回表查询

扩展: MySQL超大分页/深度如何处理?

可以使用覆盖索引解决

在数据量比较大时，如果进行limit分页查询在查询时，越往后，分页查询效率越低

SELECT * FROM `emp` LIMIT 0,10;
SELECT * FROM `emp` LIMIT 400000,10;

当在进行分页查询时，如果执行 limit 400000,10，此时需要MySQL排序前400010 记录，仅仅返回400000 - 400010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路:

优化思路: 一般分页查询时,通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

SELECT * 
FROM `emp` t, (SELECT id from `emp` ORDER BY id LIMIT 400000,10 ) a
WHERE t.id = a.id

内连接：
select column from a,b where 关联条件;
select column from a inner join  b  on 关联条件;

其中: 子查询中根据查询id再根据id排序走的是覆盖索引 也即能够在索引中直接找到数据 再跟之前的表进行关联查询做等价查询,性能得到提升

创建索引的规则

• 针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据(增加用户体验)
• 针对于常作为查询条件 (where)、排序 (order by) 、分组(group by) 操作的字段建立索引。
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效的场景

• 违反最左前缀法则
• 范围查询右边的列，不能使用索引(将范围查询列放在索引最后)
• 不要在索列上进行运算操作，索引将失效
• 字符串不加单引号，造成索引失效。(类型转换)
• 以%开头的Like模糊查询，索引失效

你对SQL优化的经验

表的设计优化[参考阿里开发手册]

• 设置合适的数值 (tinyint int bigint)，要根据实际情况选择
• 比如设置合适的字符串类型 (char和varchar) char定长效率高，varchar可变长度，效率稍低

SQL语句的优化

• SELECT语句务必指明字段名称 (避免直接使用select *)
• SQL语句要避免造成索引失效的写法
• 尽量用union all代替union union会多一次过滤，效率低
• 避免在where子句中对字段进行表达式(函数)操作
• Join优化 能用inner join 就不用left join right join，如必须使用一定要以小表为驱动 内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序

主从复制和读写分离

​ 如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响 可以采用读写分离的架构。读写分离解决的是，数据库的写入，影响了查询的效率。

分库分表

业务背景

主从复制主要目的是读写分离,即可以分担单个数据库节点的访问压力,但无法缓解海量数据的存储问题 如下图所示:

什么业务情况下使用了分库分表？

• 项目业务数据逐渐增多，或业务发展比较迅速 单表的数据量达1000W或2G以后
• 优化已解决不了性能问题（主从读写分离、查询索引…）
• IO瓶颈（磁盘IO、网络IO）、CPU瓶颈（聚合查询、连接数太多）

分库分表的策略

分片规则

algorithm-expression: ds_${user_id % 2}

shardingAlgorithmName: t_order_item_inline

https://shardingsphere.apache.org/document/legacy/3.x/document/cn/features/sharding/

垂直拆分

垂直分库: 专库专用 在拆分之前，一个数据库由多个数据表构成，每个表对应着不同的业务。而拆分之后，则是按

​ 照业务将表进行归类，分布到不同的数据库中，从而将压力分散至不同的数据库

垂直分表: 以字段为依据，根据字段属性将不同字段拆分到不同表中。

拆分规则：

• 把不常用的字段单独放在一张表
• 把text，blob等大字段拆分出来放在附表中

特点：

• 冷热数据分离
• 减少IO过渡争抢，两表互不影响

水平拆分

水平分库: 水平分片又称为横向拆分。 相对于垂直分片，它不再将数据根据业务逻辑分类，而是通过某个字段

（或 某几个字段），根据某种规则将数据分散至多个库或表中，每个分片仅包含数据的一部分。 例如：根据主键

分片，偶数主键的记录放入0库（或表），奇数主键的记录放入1库（或表），如下图所示。

水平分片从理论上突破了单机数据量处理的瓶颈，并且扩展相对自由，是分库分表的标准解决方案

水平分表: 将一个表的数据拆分到多个表中(可以在同一个库内)。

特点：

• 优化单一表数据量过大而产生的性能问题;
• 避免IO争抢并减少锁表的几率;

挑战与问题

合理采用分表，可以在降低单表数据量的情况下，尽量使用本地事务，善于使用同库不同表可有效避免分布式事务带来的麻烦。 在不能避免跨库事务的场景，有些业务仍然需要保持事务的一致性。 而基于XA的分布式事务由于在并发度高的场景中性能无法满足需要，并未被互联网巨头大规模使用，他们大多采用最终一致性的柔性事务代替强一致事务。

解决方案

利用数据库中间件 eg sharding-sphere mycat

总结

你们项目用过分库分表吗?

业务介绍

• 根据自己简历上的项目，想一个数据量较大业务（请求数多或业务累积大）
• 达到了什么样的量级（单表1000万或超过20G）

具体拆分策略

• 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
• 水平分表，解决单表存储和性能的问题
• 垂直分库，根据业务进行拆分，高并发下提高磁盘IO和网络连接数
• 垂直分表，冷热数据分离，多表互不影响

MVCC多版本并发控制

mysql在并发的情况下，会引起脏读，幻读，不可重复读等一系列的问题，为解决这些问题，引入了mvcc的机制。本文就详细看看mvcc是怎么解决脏读，幻读等问题的。

1、 数据库事务

1.1 事务

事务是操作数据库的最小单元，将【多个任务作为单个逻辑工作单元】执行的一系列数据库操作，他们作为一个整体一起向数据库提交，要么都执行、要么都不执行。

大白话解释：

事务就是当要完成一件事件，这件事又包含多个任务的时候，只有当所有的任务都执行成功，则认为这个事情是成功；只要有其中一个任务没有执行成功，则认为这件事执行失败，其他的执行成功的任务也要回滚到未执行的状态。

• 开启事务【开始记录一个事情中的多个任务】
• 执行事务【正常情况下，一条语句就是一个任务】
• 提交事务【成功】| 回滚事务【失败】

' fill='%23FFFFFF'%3E%3Crect x='249' y='126' width='1' height='1'%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E)

事务的作用：保证数据的最终一致性。

1.2 事务四大特性

事务四大特性即ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

• 原子性（Atomicity）

事务是操作数据库的最小单元，作为一个整体被执行，包含一个事务中的所有操作要么全部都执行，要么全部失败回滚。

• 一致性（Consistency）

事务必须使数据库从一个一致性状态转换到另一个一致性状态，即在事务开始之前和事务结束以后，数据不会被破坏，保持一致性。

假如A账户给B账户转100块钱，不管事务是否成功，A账户给B账户的总金额是不变的。

• 隔离性（Isolation）

当多个事务并发访问数据库时，一个事务不应该被其他事务干扰，多个并发事务之间是相互隔离的。

• 持久性（Durability）

事务一旦完成后被提交，该事务对数据库所作的操作更改，将持久地保存在数据库之中。

1.3 并发下的事务问题

虽然事务能保持数据最终一致性，但是在并发下执行事务，发会引起脏读、不可重复读、幻读等问题。

• 脏读【读取未提交数据-ReadUncommited】

如果一个事务读取到了另一个未提交事务修改过的数据，称发生了脏读。

一般事务的脏读都是拿转账的案例说明，这里也转账和取款为案例：

| 时间 | 事务A：转账 | 事务B:取款 | | :--- | :------------------------------------------------- | :----------------------------------------- | | 1 | | 开始事务 | | 2 | 开始事务 | | | 3 | | 查询账户余额为10000元 | | 4 | | 执行取款操作，取款3000元，余额更改为7000元 | | 5 | 查询账户余额为7000元（产生脏读） | | | 6 | | 取款失败，回滚事务，余额还原为10000元 | | 7 | 转入5000元，余额被更改为12000元（脏读的7000+5000） | | | 8 | 提交事务 | |

从上述执行过程的结果，最后账户余额为12000元，但是实际上B取款失败，余额为10000，加上A转入的5000元，账户最终的余额应该为15000元，平白无故少了3000元，这就是脏读。银行肯定是不允许这种事情发生的

• 不可重复读【前后多次读取，数据内容不一致-ReadCommited】

同一个事务内，前后多次读取，读取到的数据内容不一致，称之为不可重复读。

还是以转账的案例：

| 时间 | 事务A：查询 | 事务B:取款 | | :--- | :------------------------------ | :----------------------------------------- | | 1 | 开始事务 | | | 2 | 第一次查询，账户的余额为10000元 | | | 3 | | 开始事务 | | 4 | | 执行取款操作，取款3000元，余额更改为7000元 | | 5 | | 提交事务 | | 6 | 第二次查询，账户的余额为7000元 | | | 7 | 提交事务 | |

从上述案例描述中可以看出，事务A执行的过程中，事务B修改了账户余额，导致事务A中的两次查询结果不一致，这就是不可重复读，对于事务A而言莫名其妙的余额变少了，那肯定不干......

• 幻读【前后多次读取，数据总量不一致-ReadReptable】

事务A执行多次读取操作过程中，由于在事务提交之前，事务B（insert/delete/update）写入了一些符合事务A的查询条件的记录，导致事务A在之后的查询结果与之前的结果不一致，这种情况称之为幻读。

以student表中的数据为例：

依次执行下面这两个语句

#查询语句
select * from student  where id > 2;
#写入语句
insert into student(id,c_id,name,sex,score) value(6,2,'吕布','男',89);

时间	事务A：读取	事务B:写入
1	开始事务
2	第一次执行查询语句，结果为3条数据结果
3		开始事务
4		执行写入语句，插入一条ID为6的数据
5		提交事务
6	第二次执行查询语句，结果为4条数据结果
7	提交事务

从上述案例描述中可以看出，事务A在前后两次执行的过程中，由于事务B插入了满足查询语句的数据，导致事务A两次查询结果的总数不一样，这就是幻读。

• 总结

一般我们再理解幻读与不可重复读的时候，容易混淆，其实只需要分清一点就可以，

一般而言：幻读是指查询数据的【条数总量】不一致，不可重复读是指查询数据的数据内容不一致。

1.4 事务的四大隔离级别

数据库设计了四种隔离级别：串行化(Serializable)、可重复读(Repeatable read)、读已提交(Read committed)、读未提交(Read uncommitted)，用来解决并发事务存在的脏读、不可重复读、幻读等问题。

• 读未提交(Read uncommitted)

在读未提交的隔离级别下，所有事务能够读取【其他事务未提交】的数据。

读取其他事务未提交的数据，会造成脏读。因此在该种隔离级别下，不能解决脏读、不可重复读和幻读。

• 读已提交(Read committed)

在读已提交的隔离级别下，所有事务只能读取【其他事务已经提交】的数据。Oracle和SQL Server的默认的隔离级别。读已提交能够解决脏读的现象，但是还是会有不可重复读、幻读的问题

读已提交会有一个事务的前后多次的查询中却返回了不同内容的数据的现象。

• 可重复读(Repeatable read)

在可重复读的隔离级别下，限制了读取数据的时候，不可以进行修改，所有事务前后多次的读取到的数据内容是不变的。mysql的默认事务隔离级别

这种隔离级别解决了重复读的问题，但是读取范围数据的时候，是可以add数据的，所以还是会造成某个事务前后多次读取到的数据总量不一致的现象，从而产生幻读。

针对以上问题，一般我们也可以使用间隙锁和临键锁来解决幻读问题，这个以后再讲

• 串行化(Serializable)

事务最高的隔离级别，在串行化的隔离级别下，所有的事务顺序执行，不存在任何冲突，可以避免脏读、不可重复读与幻读所有并发问题。

但是串行化的隔离级别，会导致大量的操作超时和锁竞争，从而大大降低数据库的性能，一般不使用这样事务隔离级别。

四种隔离级别存在的并发问题如下：

【 ×】表示未解决，【√】表示已解决

隔离级别	脏读	不可重复读	幻读
读未提交(Read uncommitted)	×	×	×
读已提交(Read committed) RC级别下MVCC解决脏读	√	×	×
可重复读(Repeatable read) RC级别下MVCC解决不可重复读	√	√	×
串行化(Serializable)	√	√	√

2、MVCC基础概念

数据库通过加锁，可以实现事务的隔离性，串行化隔离级别就是加锁实现的，但是加锁会降低数据库性能。

因此，数据库引入了MVCC多版本并发控制，在读取数据不用加锁的情况下，实现读取数据的同时可以修改数据，修改数据时同时可以读取数据。

2.1 什么是MVCC

MVCC(Mutil-Version Concurrency Control)，多版本并发控制。是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问。用于支持读已提交(RC）和可重复读(RR）隔离级别的实现。

MVCC在MySQL InnoDB引擎中的实现主要是为了在处理读-写冲突时提高数据库并发性能，记录读已提交和可重复读这两种隔离级别下事务操作版本连的过程。

• 数据库并发场景一般有三种：

• • 读-读：不存在任何问题，不需要并发控制
  • 读-写：有线程安全问题，可能会造成事务隔离性问题，可能会有脏读，幻读，不可重复读
  • 写-写：有线程安全问题，可能会存在更新丢失问题。

• MVCC主要是用来解决【读-写】冲突的无锁并发控制，可以解决以下问题：

• • 在并发读写数据时，可以做到在读操作时不用阻塞写操作，写操作不用阻塞读操作，提高数据库并发读写的性能。
  • 可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决【写-写】引起的更新丢失问题。

• MVCC与锁的组合：

一般数据库中都会采用以上MVCC与锁的两种组合来解决并发场景的问题，以此最大限度的提高数据库性能。

• - MVCC + 悲观锁：MVCC解决读-写冲突，悲观锁解决写-写冲突。
  • MVCC + 乐观锁：MVCC解决读-写冲突，乐观锁解决写-写冲突。

通过上述描述，MVCC的作用可以概括为就是为了解决【读写冲突】，提高数据库性能的，而MVCC的实现又依赖于六个概念：【隐式字段】【undo日志】【版本链】【快照读和当前读】【读视图】。

2.2 隐式字段

在InnoDB存储引擎，针对每行记录都有固定的两个隐藏列【DB_TRX_ID】【DB_ROLL_PTR】以及一个可能存在的隐藏列【DB_ROW_ID】。

隐式字段	描述	是否必须存在
DB_TRX_ID	事物Id，也叫事物版本号，占用6byte的标识，事务开启之前，从数据库获得一个自增长的事务ID，用其判断事务的执行顺序	是
DB_ROLL_PTR	占用7byte，回滚指针，指向这条记录的上一个版本的undo log记录，存储于回滚段（rollback segment）中	是
DB_ROW_ID	隐含的自增ID（隐藏主键），如果表中没有主键和非NULL唯一键时，则会生成一个单调递增的行ID作为聚簇索引	否

2.3 undo日志

一种用于撤销回退的日志，在事务开始之前，会先记录存放到 Undo 日志文件里，备份起来，当事务回滚时或者数据库崩溃时用于回滚事务。

undo日志的主要作用是事务回滚和实现MVCC快照读。

InnoDB 并不会真正地去开辟空间存储多个版本的行记录，只是借助 undo log 记录每次写操作的反向操作。所以B+ 索引树上对应的记录只会有一个最新版本，InnoDB 可以根据 undo log 得到数据的历史版本，从而实现多版本控制。

2.4 版本链

一致性非锁定读是通过 MVCC 来实现的。但是MVCC 没有一个统一的实现标准，所以各个存储引擎的实现机制不尽相同。InnoDB 存储引擎中 MVCC 的实现是通过 undo log 来完成的

当事务对某一行数据进行改动时，会产生一条Undo日志，多个事务同时操作一条记录时，就会产生多个版本的Undo日志，这些日志通过回滚指针（DB_ROLL_PTR）连成一个链表，称为版本链。

只要有事务写入数据时，就会产生一条对应的 undo log，一条 undo log 对应这行数据的一个版本，当这行数据有多个版本时，就会有多条 undo log 日志，undo log 之间通过回滚指针（DB_ROLL_PTR）连接，这样就形成了一个 undo log 版本链。

2.5 快照读和当前读

• 快照读【Consistent Read】

也叫普通读，读取的是记录数据的可见版本，不加锁，不加锁的普通select语句都是快照读。

快照读的执行方式是生成 ReadView，直接利用 MVCC 机制来进行读取，并不会对记录进行加锁。

如下语句：

  select * from table;

• 当前读

也称锁定读【Locking Read】，读取的是记录数据的最新版本，并且需要先获取对应记录的锁。如下语句：

  SELECT * FROM student lock in share mode; # 共享 锁
  SELECT * FROM student FOR UPDATE; # 排他锁
  INSERT INTO student values ...  # 排他锁
  DELETE FROM student WHERE ...  # 排他锁
  UPDATE student SET ...  # 排他锁

2.6 读视图【Read View】

Read View提供了某一时刻事务系统的快照，主要是用来做可见性判断, 里面保存了【对本事务不可见的其他活跃事务】。

当事务在开始执行的时候，会产生一个读视图（Read View），用来判断当前事务可见哪个版本的数据，即可见性判断。

实际上在innodb中，每个SQL语句执行前都会生成一个Read View。

ReadView是“快照读”SQL执行时MVCC提取数据的依据快照读就是最普通的Select查询SQL语句

MySQL5.7源码中对Read View定义了四个属性，如下：

• creator_trx_id

当前read view创建者的事务ID

• m_ids

当前系统中所有的活跃事务的 id，活跃事务指的是当前系统中开启了事务，但还没有提交的事务;

• m_low_limit_id

表示在生成ReadView时，当前系统中活跃的读写事务中最小的事务id，即m_ids中的最小值。

• m_up_limit_id

当前系统中事务的 id 值最大的那个事务 id +1，也就是系统中下一个要生成的事务 id。

3、不同隔离级别MVCC实现原理

3.1 读视图可见性判断规则

将Read View中的活跃事务Id按照大小放在坐标轴上表示的话，如下图：

当一个事务读取某条数据时，会通过DB_TRX_ID【Uodo日志的事务Id】在坐标轴上的位置来进行可见性规则判断，如下：

• DB_TRX_ID < m_low_limit_id

表示DB_TRX_ID对应这条数据【Undo日志】是在当前事务开启之前，其他的事务就已经将该条数据修改了并提交了事务(事务的 id 值是递增的)，所以当前事务【开启Read View的事务】能读取到。

• DB_TRX_ID >= m_up_limit_id

表示在当前事务【creator_trx_id】开启以后，有新的事务开启，并且新的事务修改了这行数据的值并提交了事务，因为这是【creator_trx_id】后面的事务修改提交的数据，所以当前事务【creator_trx_id】是不能读取到的。

• m_low_limit_id =< DB_TRX_ID < m_up_limit_id

DB_TRX_ID 在 m_ids 数组中表示DB_TRX_ID【写Undo日志的事务】 和当前事务【creator_trx_id】是在同

一时刻开启的事务

​ DB_TRX_ID 不等于creator_trx_idDB_TRX_ID事务修改了数据的值，并提交了事务，所以当前事务【creator_trx_id】不能读取到。

DB_TRX_ID 等于creator_trx_id

​ 表明数据【Undo日志】 是自己生成的，因此是可见的

DB_TRX_ID 不在 m_ids 数组中

表示的是在当前事务【creator_trx_id】开启之前，其他事务【DB_TRX_ID】将数据修改后就已经提交了事务，所以当前事务能读取到。

总结：InnoDB 实现MVCC是通过Read View与Undo Log 实现的，Undo Log 保存了历史快照，形成版版本链，Read View可见性规则判断当前版本的数据是否可见。

InnnoDB执行查询语句的具体步骤为：

• 执行语句之前获取查询事务自己的事务Id，即事务版本号。
• 通过事务id获取Read View
• 查询存储的数据，将其事务Id与Read View中的事务版本号进行比较
• 不符合Read View的可见性规则，则读取Undo log中历史快照数据
• 找到当前事务能够读取的数据返回

而在实际的使用过程中，Read View在不同的隔离级别下是得工作方式是不一样。

3.2 读已提交（RC）MVCC实现原理

在读已提交(Read committed)的隔离级别下实现MVCC，同一个事务里面，【每一次查询都会产生一个新的Read View副本】，这样可能造成同一个事务里前后读取数据可能不一致的问题（不可重复读并发问题）。

还是按照上述案例来说明一下：

• 【第一步】：准备一条原始数据

• 【第二步】：假设现在有事务 A【DB_TRX_ID = 20】 和事务 B 【DB_TRX_ID = 30】并发执行

  #事务A：
  select name from user where id = 1;
  #事务B：
  update user set name = 'edwin' where id = 1;
  

执行过程为

| 时间 | 事务A | 事务B | | :--- | :---------------------------------------------- | :----------------------------------------------------- | | 1 | 开始事务 | | | 2 | 第一次查询：select name from user where id = 1; | | | 3 | | 开始事务 | | 4 | | 执行修改：update user set name = 'edwin' where id = 1; | | 5 | | 提交事务 | | 6 | 第二次查询：select name from user where id = 1; | | | 7 | 提交事务 | |

版本链为：

案例结果分析：

上述案例在在读已提交(Read committed)的隔离级别下实现，同一个事务里面，【每一次查询都会产生一个新的Read View副本】。所以第二步实际上产生了三个Read View

	m_ids	m_low_limit_id	m_up_limit_id	creator_trx_id
事务A：第一次查询Read View	[20,30]	20	31	20
事务B：Read View	[20,30]	20	31	30
事务A：第二次查询Read View	[20]	20	31	20

通过可见性判断：

• 事务A第一次查询时

读视图可见性判断规则：DB_TRX_ID < m_low_limit_id表示DB_TRX_ID对应这条数据【Undo日志】是在当前事务开启之前，其他的事务就已经将该条数据修改了并提交了事务(事务的 id 值是递增的)，所以当前事务【开启Read View的事务】能读取到。

版本链中的日志事务Id【DB_TRX_ID = 10】 < 最小活跃事务ID【m_low_limit_id=20】说明【DB_TRX_ID = 10】这条数据是事务A开启前就已经写入并提交，因此可以读取到DB_TRX_ID = 10这条版本链中的数据。即name = 星河。

• 事务A第二次查询时

读视图可见性判断规则：m_low_limit_id =< DB_TRX_ID < m_up_limit_id且DB_TRX_ID 不在 m_ids 数组中 表示的是在当前事务【creator_trx_id】开启之前，其他事务【DB_TRX_ID】将数据修改后就已经提交了事务，所以当前事务能读取到。

此时事务B已经提交，版本链中最新版本为DB_TRX_ID = 30，而可见性规则中虽然满足

【m_low_limit_id=20】=<【DB_TRX_ID=30】<【m_up_limit_id=31】但是【DB_TRX_ID=30】不在m_ids集合[20]中，因此事务A的第二次查询可以读取【DB_TRX_ID=30】的数据，即name = edwin。

案例总结：

通过上述案例说明，同一个事务A的两个相同查询，第一次结果为星河，第二次结果为edwin，因此在读已提交（RC）隔离级别下，存在不可重复读并发问题。

3.3 可重复读（RR）MVCC实现原理

在可重复读(Repeatable read)的隔离级别下实现MVCC，【同一个事务里面，多次查询，都只会产生一个共用Read View】，以此解决不可重复读并发问题。

案例与3.2一样，这里就不重复赘述，可以再看一遍3.2的【第一步】【第二步】，直接进行案例分析

案例结果分析：

由于同一个事务只会产生一个共用Read View，所以可重复读的隔离级别下第二步只产生了两个Read View

上述案例在可重复读(Repeatable read)，【每一次查询都会产生一个新的Read View副本】。所以第二步实际上产生了三个Read View

	m_ids	m_low_limit_id	m_up_limit_id	creator_trx_id
事务A：Read View	[20,30]	20	31	20
事务B：Read View	[20,30]	20	31	30

通过可见性判断：

• 事务A第一次查询时

读视图可见性判断规则：DB_TRX_ID < m_low_limit_id表示DB_TRX_ID对应这条数据【Undo日志】是在当前事务开启之前，其他的事务就已经将该条数据修改了并提交了事务(事务的 id 值是递增的)，所以当前事务【开启Read View的事务】能读取到。

日志事务Id【DB_TRX_ID = 10】 < 最小活跃事务ID【m_low_limit_id=20】，因此可以读取到DB_TRX_ID = 10这条版本链中的数据。即name = 星河。

• 事务A第二次查询时

读视图可见性判断规则：m_low_limit_id =< DB_TRX_ID < m_up_limit_id且DB_TRX_ID 在 m_ids 数组中 但DB_TRX_ID不等于creator_trx_id表示：DB_TRX_ID事务修改了数据的值，并提交了事务，所以当前事务【creator_trx_id】不能读取到。

此时事务B已经提交，版本链中最新版本为DB_TRX_ID = 30，而可见性规则中虽然满足

【m_low_limit_id=20】=<【DB_TRX_ID=30】<【m_up_limit_id=20】并且【DB_TRX_ID=30】也在m_ids集合[20，30]中，但是【DB_TRX_ID=30】不等于事务A的【creator_trx_id=20】，说明DB_TRX_ID=30是同一时刻其他事务提交的，事务A不能读取到，因此事务A只能按照版本链继续往上找，最终读取到【DB_TRX_ID=10】的数据，即name = 星河。

案例总结：

通过上述案例说明，同一个事务A的两个相同查询，结果都为星河，因此在可重复读（RR）隔离级别下，解决了不可重复读并发问题。