mysql的逻辑架构
首先我们来看一下mysql的逻辑架构图
server层包括连接器，查询缓存，分析器，优化器，执行器等，内置函数（例如时间函数，数学函数等）和存储过程，触发器，事务等都在这一层实现。
引擎层负责数据的存储和提取，包括很多我们常用的引擎如Innodb，MyISAM，Memory等。
连接器
我们使用下面命令来连接数据库：
mysql -uxxxx -pxxxx
连接器接收到命令后，完成如下操作：
- 判断用户名和密码是否正确，如果错误，会报 "Access denied for user"的错误，然后结束本次会话。
- 用户名密码正确，会去权限表中查看权限，然后把权限写入此次连接的进程中。这意味在这连接成功后，即使你用管理员账号对这个用户做了修改，也不会影响此次连接的权限。
一个连接默认保存时间是8个小时，由参数wait_timeout决定。也就是说如果不操作，过了8小时连接器会自动断开连接，再次操作，则会报 "LOST CONNECTION"的错误。
缓存
mysql拿到一个请求后会先看缓存中是否有，如果有则直接返回，如果没有，再往下执行，执行完成后，把结果放入缓存，如果是复杂的查询，效率会非常的高。 但mysql的缓存有一个非常大的问题： 只要对一个表更新，这个表上的所有缓存都会失效。 所以缓存的命中率非常低。在大多数情况下，不建议使用缓存。
mysql8.0以上版本直接将查询缓存的整快功能都去掉了。
分析器
分析器主要是对sql语句进行解析，分析出关键字，让mysql知道你要做什么，如果sql语句有语法错误，会抛错。
优化器
优化器主要目的是生成执行计划。比如语句：
SELECT a,b FROM t WHERE a=1 AND b=1
- 可以根据a索引，找到所有a=1的数据，然后再判断b是否等于1
- 也可以根据b索引，找到所有b=1的数据，然后再判断a是否等于1
- 甚至可以全表扫描，找出所有a=1 并且 b=1的数据
至于具体使用哪种执行计划，就是优化器根据效率最高来做判断的了。
执行器
执行期在拿到执行计划后，会先做一个权限的判断，看用户是否对表有操作权限，如果没有权限，会抛错。如果有权限，就打开表调用引擎接口获取数据。
小练习
mysql当中已经有了缓存，为什么我们还要用redis，memcache等第三方的缓存呢？
定位分析sql语句
在工作中，我们是不是有时会遇到查询返回非常慢的情况，那么这种情况如何定位慢sql，并且优化呢？
定位慢sql
定位慢sql有以下两种方案：
- 通过慢查询日志确定慢查询
- 通过show processlist查看正在执行的查询
慢查询日志
mysql慢查询日志是记录执行时间超过设置的阀值的SQL语句，可以使用如下命令来查看是否开启
show variables like '%slow_query_log%';
慢查询日志有四个比较关键的参数：
- slow_query_log：是否开启慢查询日志。
- long_query_time：慢查询日志设置的时间阀值，超过这个阀值会被记录到日志中。
- show_query_log_file: 慢查询日志记录的文件
- log_queries_not_using_indexes: 是否把没有走索引的sql语句也记录到日志中。
在开启慢查询日志之前，我们先在表里插入一下数据
drop table if exists `slow_log`;
CREATE TABLE `slow_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`)
) ENGINE=InnoDB;
drop procedure if exists slow_log_insert;
delimiter ;;
create procedure slow_log_insert()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into slow_log(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call slow_log_insert();
我们来开启一下慢查询：
set global slow_query_log=1; -- 在本会话中打开慢日志
set global long_query_time=0.005; -- 在本会话中设置阀值时间为5ms
set global slow_query_log_file='/tmp/mysql_slow.log'; -- 设置慢日志的文件路径
然后执行sql语句
select * from slow_log;
select * from slow_log where a = 5;
发现只有前一条sql语句记录到了慢查询日志里面
日志中比较重要的参数如下：
- Query_time: 执行时间
- Lock_time: 等待表锁时间
- Rows_sent: 语句返回行数
- Rows_examined：语句执行期间从存储引擎读取行数
当然在生成上，慢日志中的内容会很多，我们可以使用mysqldumpslow 来对慢日志进行分析和汇总。
查看正在查询的慢查询
有时候，慢查询还在进行，但数据库负载已经偏高了，这时候可以用 show processlist 来找出慢查询。 如果有PROCESS权限，可以看到在执行的语句。如果没有则只能看到本次会话中的执行语句。
我们开启两个会话，然后执行下面语句
| 会话1 | 会话2 |
|---|---|
| SELECT SLEEP(100) | |
| SHOW PROCESSLIST |
会话2的显示结果如下：
这里对几个重要参数解释一下：
- id: 会话的id
- Command: 现在会话的状态
- Time： 已执行的时间
- Info：执行的语句
我们可以使用 "kill [query] id" 命令来终止执行
kill 27
或
kill query 27
"kill 27 和 kill query 27" 的区别在于"kill 27"是结束id为27的会话。"kill query 27"表示结束会话27的本次操作，而保留会话27。
分析SQL语句
通过上面的两个步骤我们已经找到了慢sql语句，现在我们要进行进行分析了，那么如何分析SQL语句呢？我们可以使用以下三种工具进行分析：
- explain 获取mysql的执行计划
- show profile 获取每个环节mysql的执行时间
- trace 查看优化器的执行计划，获取每种可能性所需要的代价
explain 获取mysql的执行计划
EXPLAIN SELECT * FROM slow_log WHERE a=1
执行结果如下：
我们重点看以下如下几个参数：
- select_type 查询类型
- type 本次查询表的连接类型
- key 所用到的索引
- rows 扫描的行数
- Extra 其他附加信息
当key为空的时候表示没有用到索引，可以考虑优化了。 当Extra出现如下几个情况，也可以考虑优化。
| 值 | 解释 | sql例子 |
|---|---|---|
| Using filesort | 是用外部排序，而非索引排序 | EXPLAIN select b from slow_log order by b |
| Using temporary | 创建了临时表 | EXPLAIN SELECT b FROM slow_log group by b order by null |
| Using join buffer (flat, BNL join) | 关联查询中，被驱动表字段没有索引 | EXPLAIN SELECT * FROM slow_log AS s1 INNER JOIN slow_log AS s2 ON s1.b=s2.b |
show profile 获取每个环节mysql的执行时间
有的时候，我们需要确认到底是哪个环节出问题了，此时explain就不是那么好用了。我们需要使用show profile。
show profile使用步骤如下：
- 查看是否支持 profile:
SHOW VARIABLES LIKE '%profiling%';
- have_profiling 表示支持 profile
- profiling 表示暂未开启 profile
- 我们来开启profile
SET profiling=1
上面的命令只是在本次会话中开启，如果需要全局开的，可以在命令中加上"GLOBAL"
SET GLOBAL profiling=1
- 执行sql语句
SELECT a FROM slow_log WHERE a=1
- 确定sql的query id
SHOW PROFILES;
- 查看sql执行详情
SHOW PROFILE FOR QUERY 2;
trace 查看优化器的执行计划，获取每种可能性所需要的代价
我们使用explain可以看到执行计划，但是explain并不能告诉我们为什么选择了A方案而不是B方案。 我们可以使用trace来知道执行方案的细节。
ps:
- trace只支持mysql5.6及以上版本。
- 开启trace会影响到服务器的性能，所以我们一般只是在需要调试时开启。
trace 使用步骤如下：
- 打开trace
- 执行sql语句
- 获取sql语句的执行计划明细
- 关闭trace
接下来我们用一个例子来说明一下trace是怎么使用的。 有下面一条sql语句：
SELECT a,b FROM slow_log WHERE a>8000;
因为a上面有索引，按照大部分人的常识，应该会走a索引，但是我们用explain工具查看，发现这条语句，竟然使用了全表扫描。
那么接下来我们用trace来分析一下这条sql的细节。
1.打开trace,并且以json格式输出
SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on;
2.执行sql语句：
SELECT a,b FROM slow_log WHERE a>8000;
3.获取结果：
SELECT * FROM information_schema.OPTIMIZER_TRACE
返回的结果如下：
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `slow_log`.`a` AS `a`,`slow_log`.`b` AS `b` from `slow_log` where (`slow_log`.`a` > 8000)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`slow_log`.`a` > 8000)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`slow_log`.`a` > 8000)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`slow_log`.`a` > 8000)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`slow_log`.`a` > 8000)"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`slow_log`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`slow_log`",
"range_analysis": {
"table_scan": {
"rows": 10337,
"cost": 2092.5
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "a",
"usable": true,
"key_parts": [
"a",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "a",
"ranges": [
"8000 < a"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 2000,
"cost": 2401,
"chosen": false,
"cause": "cost"
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`slow_log`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 10337,
"access_type": "scan",
"resulting_rows": 10337,
"cost": 2090.4,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 10337,
"cost_for_plan": 2090.4,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`slow_log`.`a` > 8000)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`slow_log`",
"attached": "(`slow_log`.`a` > 8000)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`slow_log`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
这个结果主要分为三个部分：
- join_preparation：准备阶段，对应逻辑图中的分析器
- join_optimization：优化阶段， 对应逻辑图中的优化器
- join_execution：执行阶段，对应逻辑图中的执行器
这里我们重点来看一下join_optimization的内容：
上面的表格中，rows和cost最为重要
- rows 预计扫描行数
- costs 消耗的资料
所以我们可以得出
| 类型 | 扫描行数 | 消耗资源 |
|---|---|---|
| 全表扫描 | 10337 | 2092.5 |
| 使用"a"索引 | 2000 | 2401 |
我们发现全表扫描比使用"a"索引消耗的资源少，所以mysql使用了全表扫描的方案。
4.关闭trace
SET SESSION optimizer_trace="enabled=off"
mysql索引
B+树索引
大家都知道，在mysql中使用的最多索引就是B+树索引，那么今天我们就来了解一下B+树索引他的数据结构到底是什么样子的。在介绍B+树之前我们先来聊一聊其他我们常用的索引。 比如我们现在有数据[1,2,3,5,6,7,9]。
顺序表
我们先把这个数据放在顺序表中。得到如下结构图：
接下来我们要查找是否存在数字6， 可以使用二分法查找。
他的时间复杂度为O{logn}。查询效率非常高。但是插入的效率就不是特别好了，每次插入都需要把后面的元素向后移动一位，而删除则是把后面的元素向前一位，修改可以看作是删除和插入的合集操作。 插入数字4的逻辑结构图如下：
删除数字5的逻辑结构图如下：
所以因为顺序表他的插入效率太低，最好是用来存储那些一次插入不常变的或只做增量递增的数据。
二叉树
二叉树特点:左节点的值小于根节点，右节点的值大于根节点,并且每个节点共有两课树 我们根据二叉树的逻辑结构建立如下结构的二叉树：
二叉树的插入不像顺序表那么复杂，他只需要找到插入数字在树中的位置，修改根节点和自身的索引即可。 举个栗子： 我们要插入数字8,我找到值为7的节点，把7的右节点指向8，8的右节点指向9。
二叉树的删除也比较简单，只需要把删除节点左子树最大节点或右子树最小节点移上来代替自己即可。 举个栗子： 我们要删除数字数字5，我们可以把3移动上来，或用6移动上来。
聊完了二叉树的插入与删除，我们再来聊一下二叉树的查询，和层级相关，上图中二叉树的层级为3，所以他查询一个数字，最多只要三次。但是二叉树并不只只有这一种建立方法，他只要满足“左节点的值小于根节点，右节点的值大于根节点,并且每个节点共有两课树”就可以，我们来看一个比较极端的二叉树结构图。他的层级为7，而且没办法使用二分法，只能逐个查找，效率就非常低了。
平衡二叉树
上一节我们知道，二叉树的搜索和自身的层级有很大的关系，层级越少，检索效率越高。我们这里引出了平衡二叉树： 平衡二叉树是一种二叉树，其中每一个节点的左子树和右子树的高度差之多等于1。而左子树深度减去右子树的深度的值称为平衡因子BF。 BF只可以是（-1，0，1），如果不在这三个值的范围内，则需要翻转。 我们来看一个平衡二叉树的栗子，现在要构建用平衡二叉树构建 [3,2,1,4,5]的数组：
刚开始插入“3”和“2”的时候我们很正常的构建，到插入“1”后，发现3节点的平衡因子变成了2 需要调整，于是向右旋转。再插入“4”，没有发生变化，插入“5”时，“3”节点为“-2”右不平衡了，于是向左旋转。使树继续达到平衡。
上面的栗子是完全平衡二叉树（AVL），但平衡二叉树维护树平衡的效率过高，所以很多系统中采用红黑树，红黑树是AVL树的改进版本。具体实现方法，这里就不介绍了。
B树
我们前面讨论的各种数据结构，处理树都是在内存中，因此考虑的都是内存中的运算时间复杂度。但对于mysql而言，大部分数据是存放在硬盘上的，所以硬盘的读取次数是影响性能的关键因素。对于通一个检索，我们读取硬盘几百次和读取硬盘几次是有本质差别的。 我们之前所说的树都只存放一个元素。当数据非常多的时候，树必定会非常大，而且深度非常的深，使得读硬盘的次数非常多，这是非常影响检索效率的。所以这使我们不得不打破一个节点只存一个元素的限制，这就是B树的由来。 我们用B树来构建[1,2,3,5,6,7,9]：
B+树
虽然我们上面说了B树的很多优点，但B树还是有很多优化的空间，这里我们拿B+树说明一下。
我们来用B+树来构建数组[1,2,3,5,6,7,9]。
我们来分析一下B+树相对于B树有什么不同点，以及有什么优势：
- B+树所有的叶子节点存数据，非叶子节点只存key。使得非叶子节点能存储更多的数据，使树的层级更浅了，增加了查询的效率
- 各叶子节点用指针相连，提高了遍历和范围查询的效率。
Innodb的索引类别
我们先来创建一张表，并且插入一些数据：
drop table if exists t8;
CREATE TABLE `t8` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` char(2) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t8(a,b) values (1,'a'),(2,'b'),(3,'c'),(5,'e'),(6,'f'),(7,'g'),(9,'i');
然后使用查询语句，得到如下结果：
SELECT * FROM t8
聚簇索引
“聚簇索引”又称为“主键索引”，主要是如下结构：
可以看到，聚簇索引有两个特点：
- 根据主键按照B+树的结构构建
- 每个叶子节点包含争行数据
二级索引
“二级索引”也称为“普通索引”，主要结构如下：
可以看到，二级索引有以下两个特点：
- 根据a创建B+树结构
- 叶子节点每个字段保存自己和主键ID
回表
我们用下面sql语句查询数据：
SELECT a,b FROM t8 WHERE a=1
这条语句的执行顺序如下：
- 在二级索引中通过a=1查找出id=1
- 然后再用id=1在聚簇索引中查找出(a=1,b=a)的数据 我们把 回到主键索引树搜索的过程，称之为回表
覆盖索引
我们把t8表的二级索引a，改成(a,b)索引，二级索引结构如下：
alter table t8 drop index idx_a;
create index idx_ab on t8(a,b);
我们再用下面的sql语句查询数据：
SELECT a,b FROM t8 WHERE a=1
这条语句查询顺序如下
- 再二级索引中通过a=1 找出（a=1,b=a）
我们看，这里只查询了二级索引，没有回表。 我们把没有回表的查找称为“覆盖索引”。 因为覆盖索引可以减少查询硬盘的次数，显著提升性能，所以覆盖索引是一个常用的性能优化手段。
小练习
在show_log表中，我们执行sql语句：
SELECT a,b FROM slow_log WHERE a>8000;
为什么全表扫描相比于"a"索引扫描的行数多，但消耗的资源反而少？如何优化？
快照读在四种隔离级别中的区别
说明
mysql有两种读的方式，快照读和当前读。
- 快照读通俗讲就是读某一个时刻的数据，一般为简单的select操作(不包括 select ... lock in share mode, select ... for update)
- 当前读通俗将就是读最新时刻的数据，操作包括select ... lock in share mode，select ... for update，insert，update，delete
mysql有四种隔离级别：
- read uncommitted(读未提交)
- read committed(读提交)
- repeatable read(可重复读)
- serializable(串行化)
在这一章，我们不考虑当前读，重点分析一下快照读在四种隔离级别的应用。 快照读在四个隔离级别中应用，会存在三种读的问题：
| 类型 | 说明 |
|---|---|
| 脏读 | 事务A读取了事务B未提交的数据。 |
| 不可重复读 | 事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。 |
| 幻读 | 事务A首先根据条件索引得到N条数据，然后事务B增添了M条符合A搜索条件的数据，导致事务A再次搜索发现有N+M条数据 |
接下来我们就来分析一下这四种隔离级别和这三个读问题的关系： 我们先创建一张表：
DROP TABLE if exists `tran`;
CREATE TABLE `tran` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
INSERT INTO `tran`(a,b) VALUES(1,1);
然后我们开启两个事务，分别执行如下sql语句：
| 事务A | 事务B |
|---|---|
| 设置隔离级别 | 设置隔离级别 |
| BEGIN; | BEGIN |
| SELECT b FROM `tran` WHERE a=1; | |
| UPDATE `tran` SET b=2 WHERE a=1; | |
| INSERT INTO `tran`(a,b) VALUES(1,3); | |
| SELECT b FROM `tran` WHERE a=1; // 记为VAL1 | |
| COMMIT; | |
| SELECT b FROM `tran` WHERE a=1; // 记为VAL2 | |
| COMMIT; |
当VAL1中包含 b=2时，表示读到了未提交的数据，有“脏读”的问题。 当VAL2中包含 b=2时，表示两次读取的数据不一致，有“不可重复读”的问题。 当VAL2中包含 b=3时，表示读到了新插入的行，有“幻读”的问题。
我们使用下面语句来设置隔离级别
set session transaction isolation level [隔离级别];
// read uncommitted，read committed，repeatable read, serializable
在执行结束之后我们可以使用下面的sql语句来初始化表的状态
TRUNCATE TABLE `tran`;
INSERT INTO `tran`(a,b) VALUES(1,1);
我们分别设置四种不同的隔离级别，执行上面的sql，得到如下的结果：
| 隔离级别 | VAL1 | VAL2 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|---|---|
| read uncommitted | 2,3 | 2,3 | Y | Y | Y |
| read committed | 1 | 2,3 | N | Y | Y |
| repeatable read | 1 | 1 | N | N | N |
| serializable | 1 | 1 | N | N | N |
PS:
- 1.在mysql中 “repeatable read”的隔离级别是没有幻读的（网上有很多文章在这个知识点上有错误）
- 2.在“read uncommitted(读未提交)”，读的始终是最新的数据，所以快照读和当前读是一样的。
- 3.在“serializable(串行)”隔离级别下，事务B执行到 “UPDATE” 语句时会阻塞住，必须要等到事务A “COMMIT” 之后才能继续执行。说明在“serializable”隔离级别下，事务会 在“SELECT”语句中加上了锁，所以在此隔离级别中，不存在快照读，所有的读都是当前读。
快照读的实现原理
我们在上一节了解到，mysql四个隔离级别中，只有RC和RR用到了快照读。这一节我们就来分析一下他们是怎么实现的。
术语说明
在系统中，每个事务都有唯一的事务id，叫做"transaction id",在事务开始的时候，是向系统申请的，是严格递增的。
每一行数据，也会用多个版本。每次更新一个事务都会产生一个新的版本，并且把transaction id 赋值给当前数据，叫做"row tx_id"。当然旧的版本会保留。
这里我们使用上一节的"tran"，里面有一条数据（id,a,b）= (1,1,1)，这里的"row tx_id" = 10，存储的逻辑图如下：
现在我们执行下面的修改语句:
UPDATE `tran` SET b=2 WHERE a=1; // "transaction id"=20
存储的逻辑图如下：
PS：方框里面的就是undo log（回滚日志）,当事务失败时，我们做逆向操作，把undo log中的数据回填就去就可以实现事务的回滚了。
在了解了innerdb的存储逻辑之后，我们来分析“RR”隔离级别。在开启一个新事务的时候，事务会生成一个一致性视图（Read-View）。 里面主要包含三个四个参数：
- transaction id: 本次事务的id
- trx_list: 系统中活跃的事务数组
- up_limit_id: trx_list中的最小事务数组id
- low_limit_id: 当前系统已经创建过的最大事务+1
每一行的"row tx_id"在一致性视图(Read_View)大概可以分为三种情况
-
- "row tx_id" < up_limit_id: 落在绿色区间，表示已提交事务或当前自己的事务，这个数据是可见的。
-
- "row tx_id" >= low_limit_id:落在红色区间，表示这个版本是将来事务生成的，不可见
-
- up_limit_id<="row tx_id"<low_limit_id: 落在黄色区间，此处分为两种情况
- 3.1. "row tx_id" 在 trx_list 中表示事务还未提交，不可见
- 3.2. "row tx_id" 不在 trx_list 中表示事务已提交，可见
我们还是使用上一节里的“tran”表，里面有一条数据（id,a,b）= (1,1,1)，这里的"row tx_id" = 10，系统目前的事务id为20，我们执行如下语句：
| 事务A | 事务B | 事务C |
|---|---|---|
| Start transaction with consistent snapshot | ||
| Start transaction with consistent snapshot | ||
| UPDATE `tran` SET b=2 WHERE a=1; | ||
| SELECT b FROM `tran` WHERE a=1; // VAL1 | ||
| COMMIT; | ||
| SELECT b FROM `tran` WHERE a=1; // VAL2 | ||
| Start transaction with consistent snapshot | ||
| UPDATE `tran` SET b=3 WHERE a=1; | ||
| SELECT b FROM `tran` WHERE a=1; // VAL3 | ||
| COMMIT; | ||
| SELECT b FROM `tran` WHERE a=1; // VAL4 |
我们把上面的sql语句转换成逻辑图如下：
在图中我们可以知道Read-View是在事务开始的时候生成的。 我们可以得到Read-View如下：
- transaction id: 21
- trx_list: [20, 21]
- up_limit_id: 20
- low_limit_id: 22
逻辑分析如下：
- 最初的数据，"row tx_id" = 10，小于 up_limit_id，表示已提交事务，走了“1”逻辑，可见
- 事务A的事务id为20，“row tx_id”也为20，等于up_limit_id，而小于low_limit_id，但在trx_list中存在，表示事务未提交，所以走的是“3.1”逻辑，不可见
- 事务C的事务id为22，“row tx_id”也为22，等于low_limit_id，表示是未来事务，所以走的是“2”逻辑，不可见
所以在RR隔离级别下面，VAL1，VAL2，VAL3, VAL4的值都为“1”，
我们再来分析一下RC隔离级别，RC隔离级别和RR隔离级别判断逻辑是一致的，唯一区别是，RR隔离级别在事务开始的时候生成"Read-View", 而RC隔离级别是在每次“SELECT”语句时生成"Read-View"。
RC隔离级别逻辑图如下：
WX20190926-201003.png
- transaction id: 21
- trx_list: [20, 21]
- up_limit_id: 20
- low_limit_id: 22
分析：
- "row tx_id" = 20，在up_limit_id<="row tx_id" < low_limit_id，并且 在trx_list中，所以为未提交事务走逻辑“3.1”，不可见
- "row tx_id" = 10，"row tx_id"<up_limit_id,为已提交事务，走逻辑“1”，可见。
获取VAL2时 Read-View如下：
- transaction id: 21
- trx_list: [21]
- up_limit_id: 21
- low_limit_id: 22
分析：
- "row tx_id" = 20，"row tx_id"<up_limit_id,为已提交事务，走逻辑“1”，可见。
获取VAL3时 Read-View如下：
- transaction id: 21
- trx_list: [21,22]
- up_limit_id: 21
- low_limit_id: 23
分析：
- "row tx_id" = 22，在up_limit_id<="row tx_id" < low_limit_id，并且 在trx_list中，所以为未提交事务走逻辑“3.1”，不可见
- "row tx_id" = 20，"row tx_id"<up_limit_id,为已提交事务，走逻辑“1”，可见。
获取VAL4时 Read-View如下：
- transaction id: 21
- trx_list: [21]
- up_limit_id: 21
- low_limit_id: 23
分析：
- "row tx_id" = 22，在up_limit_id<="row tx_id" < low_limit_id，并且 不在trx_list中，所以为未提交事务走逻辑“3.2”，可见
所以在RC隔离级别下，我们可以得出结论： VAL1=1，VAL2=2，VAL3=2，VAL4=3。
参考文档
慕课网 《一线数据库工程师带你深入理解 MySQL》 s.imooc.com/W2749EM
极客时间 《MYSQL实战45讲》time.geekbang.org/column/intr…
《大话数据结构》
小错误修正
在公司做分享后，小伙伴raywang对于RR隔离级别下是存在幻读，并且也给出了自己的例子，我们还是根据那张tran表，初始值还是（a=1，b=1）
| 事务A | 事务B |
|---|---|
| BEGIN; | |
| BEGIN; | |
| SELECT b FROM tran WHERE a=1; | |
| INSERT INTO tran(a,b) VALUES(1,2); | |
| COMMIT; | |
| update tran set b=b+10 where a=1; | |
| SELECT b FROM tran WHERE a=1; // VAL | |
| COMMIT; |
我们可以看到最终VAL结果有2个值 “11和12”，产生了幻读
那么为什么会幻读？我们来分析画张逻辑图分析一下
这里的关键是update语句，把两行数据都改了，使他们的tx_id为事务A的id。 所以满足之前说的条件1（小于up_limit_id或为自己本身时可见），所以可以被查出来。
当然在RR隔离级别下面也同样会出现不可重复读的情况。我们依然根据那张tran表，初始值还是（a=1，b=1），来看下面的例子：
| 事务A | 事务B |
|---|---|
| BEGIN; | |
| BEGIN; | |
| SELECT a,b FROM tran WHERE id=1; | |
| update tran set a=2 where id=1; | |
| COMMIT; | |
| update tran set b=2 where id=1; | |
| SELECT a,b FROM tran WHERE id=1; // VAL | |
| COMMIT; |
我们在事务A里面只修改了b=2的值，并没有修改a的值，但却把B事务修改的a读出来了。至于具体原因和上面的一样，这里可交给各位读者自行分析。 所以我们可以得出结论： 在RR隔离级别下，当一个事务修改了别的事务修改/新增过的数据时，可能会出现不可重复读和幻读。
最后，感谢 raywang 给出的非常宝贵的建议，使得这次分享更加的圆满。
