单机存储引擎到mysql的思考二

痛定思痛。 2023-07-19 08:22 12阅读 0赞

> 上一篇中我们主要介绍mysql存储引擎物理结构进而引发对索引的  
> 思考  
> 但是并发事务和锁机制又是怎样的，下面我们来探索一下

<table> 
 <thead> 
  <tr> 
   <th>标题</th> 
   <th>网址</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>单机存储引擎到mysql的思考一</td> 
   <td><a href="https://blog.csdn.net/xk4848123/article/details/105166855" rel="nofollow">https://blog.csdn.net/xk4848123/article/details/105166855</a></td> 
  </tr> 
  <tr> 
   <td>单机存储引擎到mysql的思考二</td> 
   <td><a href="https://blog.csdn.net/xk4848123/article/details/105195921" rel="nofollow">https://blog.csdn.net/xk4848123/article/details/105195921</a></td> 
  </tr> 
  <tr> 
   <td>单机存储引擎到mysql的思考三</td> 
   <td><a href="https://blog.csdn.net/xk4848123/article/details/105207797" rel="nofollow">https://blog.csdn.net/xk4848123/article/details/105207797</a></td> 
  </tr> 
 </tbody> 
</table>

## 一条sql语句是如何执行的 ##

`•连接器`： 身份认证和权限相关(登录 MySQL 的时候)。  
`•查询缓存`: 执行查询语句的时候，会先查询缓存。  
`•分析器`: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。  
`•优化器`： 按照 MySQL 认为最优的方案去执行。  
`•执行器`: 执行语句，然后从存储引擎返回数据。

## 事务并发问题？ ##

MySQL默认开启自动提交，每执行一条sql语句就自动提交事务。在MySQL中使用事务首先要关闭自动提交，使用手动提交的方式原子执行多个sql语句。当多个事务执行时，并发问题也就出现了。

## 锁和MVCC ##

锁大体分为排他锁和共享锁，具体来说就是写锁和读锁（lock in share mode，共享读锁，不可写）  
老版本MySQL中使用的MyISAM引擎只支持表锁，不管线程操作那一个行的数据，都给这张表加锁，可能其他线程只是操作对该行无任何影响也不能同时进行，只能等待获取锁。而新版本中的InnoDB引擎减小了锁的粒度，支持行锁，使用哪些行的数据就给对应行上锁，只要其他线程的操作与上锁行无关即可同时操作，增大了并发效率。  
InnoDB中有`意向锁，索引行锁，间隙锁`

<table> 
 <thead> 
  <tr> 
   <th>锁</th> 
   <th>解释</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>意向锁</td> 
   <td>加锁时先提前声明一个意向，并获取表级别的意向锁，如果获取成功，则稍后将要或正在(才被允许)，对该表的某些行加锁了。</td> 
  </tr> 
  <tr> 
   <td>索引行锁</td> 
   <td>例子：执行一条SQL语句 SELECT sid FROM table_student WHERE sid = 123;如果sid存在索引（InnoDB会自动根据主键创建一个聚簇索引），则根据索引锁定相应的行，防止在检索过程中其他线程对该行进行修改。如果该字段没有索引，则会锁定整个表。</td> 
  </tr> 
  <tr> 
   <td>间隙锁</td> 
   <td>MySQL默认情况下使用是REPETABLE READ作为默认事物隔离级别的，该隔离级别会发生幻读问题。因此InnoDB为了防止幻读问题使用了间隙锁（Gap locks）。间隙锁通俗来讲就是查询一个范围内的数据时给范围内字段的间隙加锁，防止幻影行的插入。</td> 
  </tr> 
 </tbody> 
</table>

**next-key**是间隙锁和索引行锁的组合，主要是为了防止幻读  
1）、如果更新条件没有走索引，此时会进行全表扫描，扫表的时候，要阻止其他任何的更新操作，所以上升为表锁。  
2）、如果更新条件为索引字段，但是并非唯一索引（包括主键索引），那么此时更新会使用Next-Key Lock。使用Next-Key Lock的原因：  
a）、首先要保证在符合条件的记录上加上排他锁，会锁定当前非唯一索引和对应的主键索引的值；  
b）、还要保证锁定的区间不能插入新的数据。  
3）、如果更新条件为唯一索引，则使用Record Lock（记录锁）。  
大家注意上面的锁对普通的`select.....`是无效的，这种属于快照读，在后文中会讲解。RR隔离级别一下`select...from...lock in share mode`才能加间隙避免幻读。  
幻读表现：一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。  
针对幻读两点需要说明：  
1、在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，幻读只在当前读下才会出现。  
2、幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。

当然还有一种方式解决幻读，就是MVCC（MVCC多版本并发控制），子RR隔离级别下读事务会保存一个**Read View（快照）**，第一次读形成Read View，第二次还会沿用这个Read View。而在RC隔离条件下每次都会形成一个新的Read View。

看了上面的next-key大家已经领略到了锁的风骚了，但是加锁就会有等待。MVCC给每个表加两个隐式的列，其中一列存储行被修改的“时间”，另一列存储删除的“时间”  
RR隔离级别下  
`Select`  
a） 行的修改版本号小于等于该事务号。  
b） 行的删除版本号要么没有被定义，要么 大于事务的版本号。  
`Insert、Update、Delete`  
都是用来更新版本号，这里就不赘述了。

大家思考一下**行的修改版本号小于等于该事务号**和 **行的删除版本号要么没有被定义，要么大于事务的版本号**是不是已经很好的规避了幻读。

## 故障处理 ##

那么我们的mysql是如何回滚事务，又是如何在宕机的时候重新恢复数据的呢，还有主从复制又是怎么实现的，大家会说通过日志，那具体每种对应哪个日志呢。