本文参考了极客时间里林晓斌老师的【MySQL实战45讲】

一、一条查询语句的执行流程

1.MySQL逻辑架构

MySQL的逻辑架构图

Server层包含MySQL的大多数核心服务,和所有内置函数,所有跨存储引擎功能的实现

存储引擎层负责数据的存储和提取,包括InnoDB、MyISAM、Memory等多个存储引擎,InnoDB从MySQL5.5.5版本开始成为了默认存储引擎,可以自由选择存储引擎:engine=InnoDB

create table `t` {

}engine=InnoDB

2.连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

mysql -h 主机名 -P 端口号 -u 用户名 -p密码 

例如:mysql 【-h localhost P 3306】 -u root -p564445 ,若是登录本地的,【】内的可以不写。-p和密码中间不能加空格,也可以-p之后直接回车再输入密码,这时候密码是隐藏的,安全性更高

3.查询缓存

MySQL拿到一个查询请求后,会先到查询缓存看看,之前是不是执行过这条语句。之前执行过的语句及其结果可能会以key-value对的形式,被直接缓存在内存中。key是查询的语句,value是查询的结果。如果查询能够直接在这个缓存中找到key,那么这个value就会被直接返回给客户端。如果语句不在查询缓存中,就会继续后面的执行阶段。执行完成后,执行结果会被存入查询缓存中。如果查询命中缓存,MySQL不需要执行后面的复杂操作,就可以直接返回结果,效率更高。

但查询缓存更适合业务上有一张静态表的情况,很长时间才会更新一次,如果更新太频繁,表上的查询缓存会被清空,导致的使用率不高。MySQL8.0版本直接将查询缓存的整块功能删掉了,从8.0版本开始彻底没有这个功能了。

4.分析器

分析器会对语句做“词法分析”,识别里面的各种字符代表什么,语法规则是否正确等等,一般语法错误会提示第一个出现错误的位置

5.优化器

优化器是在表里面有多个索引的时候,决定使用哪个索引;或者在一个语句有多表关联(join)的时候,决定各个表的连接顺序。不同的执行顺序会导致效率的高低,通常会选择效率更高的情况,但是也会出现选错的情况

6.执行器

  1. 先判断对表T有没有执行权限,没有权限会报错
  2. 然后使用引擎提供的接口,开始执行逻辑
  3. 然后将结果返回给客户端

二、一条更新语句的执行流程

建表

create table `T`(
ID int primary key,
c int
)engine=InnoDB;

将ID=2的值加1

update T set c=c+1 where ID=2;

执行流程

  1. 连接数据库
  2. 因为有更新,所以会清空缓存数据,导致命中率不高,这也是为什么前面建议不适用查询缓存的原因
  3. 分析器通过词法和语法解析知道这是一条更新语句
  4. 优化器决定使用ID这个索引
  5. 执行器负责执行逻辑,找到这一行然后更新

更新流程和查询流程大体上的执行顺序相似,但是更新流程还涉及到两个重要的日志模块:redo log(重做日志)和bin log(归档日志)

1.redo log模块

先分析问题,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程IO成本、查找成本都很高

这时候就需要redo log了,当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到redo log里,并更新内存,这个时候更新就算完成了。InnoDB引擎会在空闲的时候,将这个操作记录批量更新到磁盘里面,减少对磁盘的IO操作

这其实就是MySQL里经常说到的WAL技术,WAL的全称是Write-Ahead-Logging(预写式日志),它的关键点就是先写日志,再写磁盘

InnoDB的redo log大小固定,比如可以配置一组4个文件,每个文件1GB,总共4GB,从头到尾循环写入,如下图

redo log循环写入

write pos是当前正在记录的位置,从0号文件到3号文件,然后循环回0号文件,checkpoint是将当前数据更新到磁盘。

有了redo log,InnoDB可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,称为crash-safe

2.binlog模块

上面的redo log是InnoDB存储引擎层特有的日志,而binlog则属于Server层。因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM,但是MyISAM没有crash-safe的能力,binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的,既然只依靠binlog是没有crash-safe能力的,所以InnoDB使用另外一套日志系统,也就是redo log来实现crash-safe能力

redo log和binlog的三个不同点:

  1. redo log是InnoDB引擎特有的;binlog是MySQL的Server层实现的,所有引擎都可以使用
  2. redo log是物理日志,记录的是“在某个数据页上做了什么修改”;binlog是逻辑日志,记录的是这个语句的原始逻辑,比如“给ID=2这一行的c字段加1”
  3. redo log是循环写的,会把之前的覆盖掉,空间固定会用完;binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个,并不会覆盖以前的日志

现在再来看看加上和两个日志模块后,update语句在执行器的执行流程

update语句在执行器的执行流程
  1. 执行器先找引擎取ID=2这一行。ID是主键,引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回
  2. 执行器拿到引擎给的行数据,把这个值加上1,比如原来是c,现在就是c+1,得到新的一行数据,再调用引擎接口写入这行新数据
  3. 引擎将这行新数据更新到内存中,同时将这个更新操作记录到redo log里面,此时redo log处于prepare状态。然后告知执行器执行完成了,随时可以提交事务
  4. 执行器生成这个操作的binlog,并把binlog写入磁盘
  5. 执行器调用引擎的提交事务接口,引擎把刚刚写入的redo log改成提交(commit)状态,更新完成

3.两阶段提交

上述最后三步中,将redo log的写入拆成了两个步骤:prepare和commit,即两阶段提交,两阶段提交的目的是:为了让两份日志之间的逻辑一致

先来看看数据恢复过程。binlog会记录所有的逻辑操作,如果设定的是可以恢复一个月,那么备份系统中一定会保存最近一个月的所有binlog,前面讲到binlog可以追加写入,并不会覆盖以前的日志,当要找回数据时:

  1. 找到最近一次的备份恢复到临时库
  2. 从备份的时间点开始,将备份的binlog依次取出来,重放到误删表之前的时刻
  3. 按需恢复到线上库

为什么一定是两阶段提交呢?

可以使用binlog替代redo log进行数据恢复吗?不可以,Innodb利用WAL技术进行数据恢复,write ahead logging技术依赖于物理日志进行数据恢复,binlog不是物理日志是逻辑日志,因此无法使用

可以只使用redo log而不使用binlog吗?不可以,redo log是循环写,写到末尾要回到开头继续写,这样的日志无法保留历史记录,会被覆盖掉,无法进行数据复制

由于redo log和binlog是两个独立的逻辑,如果不用两阶段提交,要么就是先写完redolog再写binlog,或者采用反过来的顺序。若在写完第一个日志后,第二个日志还没有写完期间发生了crash,数据库的状态可能和用日志恢复出来的数据库的状态不一致,下面是不采用两阶段提交的情况:

  1. 先写redo log后写binlog。
    假设在redo log写完,binlog还没有写完的时候,MySQL进程异常重启。由于前面说过,redo log写完之后,系统即使崩溃,仍然能够把数据恢复回来,所以恢复后这一行c的值是1。但是由于binlog没写完就crash了,这时候binlog里面就没有记录这个语句。因此,之后备份日志的时候,存起来的binlog里面就没有这条语句。然后会发现,如果需要用这个binlog来恢复临时库的话,由于这个语句的binlog丢失,这个临时库就会少了这一次更新,恢复出来的这一行c的值就是0,与原库的值不同
  2. 先写binlog后写redo log。
    如果在binlog写完之后crash,由于redo log还没写,崩溃恢复以后这个事务无效,所以这一行c的值是0。但是binlog里面已经记录了“把c从0改成1”这个日志。所以,在之后用binlog来恢复的时候就多了一个事务出来,恢复出来的这一行c的值就是1,与原库的值不同

由上述分析可见,如果不采用两阶段提交,数据库的状态就有可能和用它的日志恢复出来的库的状态不一致

采用两阶段提交后:

  1. redo log好没写入之前崩溃,这时binlog也还没写入,恢复数据不受影响
  2. redo log写好了,binlog还没写崩溃时,这时redo log处于prepare状态,还没有提交,恢复时事务会回滚,binlog也还没有记录,所以不会影响
  3. redo log已经有了commit标识,则直接提交事务,同时因为binlog有记录,则恢复数据也不受影响

三、事务隔离

在MySQL中,事务是在引擎层实现的,MySQl的原生引擎MyISAM不支持事务,而InnoDB支持。

1.事务四大特性

ACID(Atomicity、Consistency、Isolation、Durability,即原子性、一致性、隔离性、持久性)

  1. 原子性(Atomicity):事务开始后的所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位

  2. 一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 。比如A向B转账,不可能A扣了钱,B却没收到

  3. 隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账

  4. 持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚

2.事务的并发问题

问题 描述 举例
脏读 当一个事务正在访问数据,并且对数据进行了修改,而这种修改还没有提交到数据库中,这时,另外一个事务也访问这个修改了但未提交的数据,并且使用了这个数据 张三的工资为5000,事务A中把他的工资改为8000,但事务A尚未提交。与此同时,事务B正在读取张三的工资,读取到张三的工资为8000。随后,事务A发生异常,而回滚了事务。张三的工资又回滚为5000。最后,事务B读取到的张三工资为8000的数据即为脏数据,事务B做了一次脏读
不可重复读 在一个事务内,多次读同一数据。在这个事务还没有结束时,另外一个事务也访问该同一数据。那么,在第一个事务中的两次读数据之间,由于第二个事务的修改,那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的,因此称为是不可重复读 在事务A中,读取到张三的工资为5000,操作没有完成,事务还没提交。与此同时,事务B把张三的工资改为8000,并提交了事务。随后,在事务A中,再次读取张三的工资,此时工资变为8000。在一个事务中前后两次读取的结果并不致,导致了不可重复读
幻读 当事务不是独立执行时发生的一种现象,例如第一个事务对一个表中的数据进行了修改,这种修改涉及到表中的全部数据行。同时,第二个事务也修改这个表中的数据,这种修改是向表中插入/删除一行新数据。那么,以后就会发生操作第一个事务的用户发现表中多出/丢失的数据行,就好象发生了幻觉一样 目前工资为5000的员工有10人,事务A读取所有工资为5000的人数为10人。此时,事务B插入一条工资也为5000的记录。这是,事务A再次读取工资为5000的员工,记录为11人。此时产生了幻读

3.隔离级别

为了解决事务并发问题,就有了“隔离级别”的概念,隔离得越严实,即隔离等级越高,效率越低。Oracle数据库的默认隔离级别是读已提交,MySQL数据库的默认隔离级别是可重复读,可以参考这篇文章:MySQL数据库的默认隔离级别为什么是可重复读?

隔离级别 描述 出现脏读 出现不可重复读 出现幻读 加锁
读未提交(read uncommitted) 即能够读取到没有被提交的数据
读已提交(read committed) 即能够读到那些已经提交的数据
可重复读(repeatable read) 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的
串行化(serializable) “写”会加“写锁”,“读”会加“读锁”,不管多少事务,运行完一个事务的所有子事务之后才可以执行下一个事务

举个例子

隔离级别例子
  1. 若隔离级别是“读未提交”,则V1的值就是2。这时候事务B虽然还没有提交,但是结果已经被A看到了。因此,V2、V3也都是2
  2. 若隔离级别是“读提交”,则V1是1,V2的值是2。事务B的更新在提交后才能被A看到。所以,V3的值也是2
  3. 若隔离级别是“可重复读”,则V1、V2是1,V3是2。之所以V2还是1,遵循的就是这个要求:事务在执行期间看到的数据前后必须是一致的
  4. 若隔离级别是“串行化”,则在事务B执行“将1改成2”的时候,会被锁住。直到事务A提交后,事务B才可以继续执行。所以从A的角度看,V1、V2值是1,V3的值是2

4.事务隔离的实现

以可重复读为例,在MySQL中,每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值,通过回滚操作,都可以得到前一个状态的值。假设一个值从1按顺序改成了2、3、4,在回滚日志里面就会有类似下面的记录

回滚日志

当前值是4,但是在查询这条记录的时候,不同时刻启动的事务会有不同的read-view。如图中看到的,在视图A、B、C里面,这一个记录的值分别是1、2、4,同一条记录在系统中可以存在多个版本,这就是数据库的多版本并发控制 MVCC(Multi-Version Concurrency Control),对于read-view A,要得到1,就必须将当前值依次执行图中所有的回滚操作得到,即使现在有另外一个事务正在将4改成5,这个事务跟read-view A、B、C对应的事务是不会冲突的

5.长事务

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据,所以这个事务提交之前,数据库里面它可能用到的回滚记录都必须保留,这就会导致占用大量存储空间,这是一个不容忽视的问题。

MySQL的事务启动方式有以下几种

  1. 显式启动事务语句,begin或start transaction。配套的提交语句是commit,回滚语句是rollback

  2. set autocommit=0,这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个select语句,这个事务就启动了,而且并不会自动提交。这个事务持续存在直到你主动执行commit或rollback语句,或者断开连接

有些客户端连接框架会默认连接成功后先执行一个set autocommit=0的命令。这就导致接下来的查询都在事务中,如果是长连接,就导致了意外的长事务。因此,建议使用set autocommit=1,通过显式语句的方式来启动事务,在autocommit为1的情况下,用begin显式启动的事务,如果执行commit则提交事务,可以避免长事务