阿里架构师讲面试:最详细的数据库工作原理

796

Mysql架构

简单来说 MySQL 主要分为 Server 层和存储引擎层:

**Server层:**主要包括连接器、查询缓存、分析器、优化器、执行器等,所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图,函数等,还有一个通用的日志模块 binglog 日志模块。

存储引擎层: 主要负责数据的存储和读取,采用可以替换的插件式架构,支持 InnoDB、MyISAM、Memory 等多个存储引擎,其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB,它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。

上面的图就是mysql的内部架构,可以清楚的看到Mysql是由连接器,SQL接口,解析器,优化器,缓存,存储引擎组成的。下面是关于上述部件的介绍:

  • Connectors

与其他编程语言中的sql语句进行交互,如php、java等。

  • Management Serveices & Utilities

系统管理和控制工具。

  • Connection Pool

管理缓冲用户连接,线程处理等需要缓存的需求。

  • SQL Interface(SQL接口)

接受用户的SQL命令,并且返回用户需要查询的结果。比如select from就是调用SQL Interface

  • Parser(解析器)

如果没有命中缓存,那么就进入分析器,主要就是分析 SQL 语句是拿来干嘛,也就是解析该语句生成语法树,会分为两步:

  1. 第一步:词法分析, 一条 SQL 语句有多个字符串组成,首先要提取关键字,比如 select,提出查询的表,提出字段名,提出查询条件等等。做完这些操作后,就会进入第二步。
  2. 第二步:语法分析,主要就是判断你输入的 SQL 是否正确,是否符合 MySQL 的语法。
  • Optimizer (查询优化器)

SQL语句在查询之前会使用查询优化器对查询进行优化(产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返回结果,实际上不一定最优)。用一个例子就可以理解:

select uid,name from user where gender = 1;

这个select 查询先根据where语句进行选取,而不是先将表全部查询出来以后再进行gender过滤。

这个select查询先根据uid和name进行属性投影,而不是将属性全部取出以后再进行过滤。

将这两个查询条件联接起来生成最终查询结果。

  • Cache和Buffer (查询缓存)

如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据。

  • Engine (存储引擎)

存储引擎是MySql中具体的与文件打交道的子系统。Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制。

SQL 语句执行过程

数据库通常不会被直接使用,而是由其他编程语言通过SQL语句调用mysql,由mysql处理并返回执行结果。那么Mysql接受到SQL语句后,又是如何处理的呢?

首先程序的请求会通过mysql的connectors与其进行交互,请求到达后,会暂时存放在连接池(connection pool)中并由管理器(Management Serveices & Utilities)管理。当该请求从等待队列进入到处理队列,管理器会将该请求丢给SQL接口(SQL Interface)。SQL接口接收到请求后,它会将请求进行hash处理并与缓存中的结果进行对比,如果完全匹配则通过缓存直接返回处理结果;否则,需要完整的走一趟流程:

  1. 由SQL接口丢给后面的解释器(Parser),上面已经说到,解释器会判断SQL语句正确与否,若正确则将其转化为数据结构。
  2. 解释器处理完,便来到后面的优化器(Optimizer),它会产生多种执行计划,最终数据库会选择最优化的方案去执行,尽快返会结果。
  3. 确定最优执行计划后,SQL语句此时便可以交由存储引擎(Engine)处理,存储引擎将会到后端的存储设备中取得相应的数据,并原路返回给程序。

这里有几点需要注意:

(1)如何缓存查询数据?

存储引擎处理完数据,并将其返回给程序的同时,它还会将一份数据保留在缓存中,以便更快速的处理下一次相同的请求。具体情况是,mysql会将查询的语句、执行结果等进行hash,并保留在cache中,等待下次查询。

(2)buffer与cache的区别?

从上面的图可以看到,缓存那里实际上有buffer和cache两个,那它们之间是否有什么不同呢?简单的说就是,buffer是写缓存,cache是读缓存。

(3)如何判断缓存中是否已缓存需要的数据

在第一次查询后,mysql便将查询语句以及查询结果进行hash处理并保留在缓存中,SQL查询到达之后,对其进行同样的hash处理后,将两个hash值进行对照,如果一样,则命中,从缓存中返回查询结果;否则,需要整个流程走一遍。

参考:

yq.aliyun.com/articles/50…

Select语句执行流程

  • 连接器

第一步,客户端会先连接到数据库,这个时候就是连接器来接待。它负责跟客户端建立连接、获取权限、维持和管理连接。

如果用户名密码正确,连接器就到权限表查询你所拥有的的权限。之后这个连接里面的权限判断,都依赖于此时读到的权限。

这就意味着,一个用户成功建立连接后,被修改了权限,也不会影响已经存在的连接的权限。修改完成后,只有再重新建立的连接才会使用到新的权限设置。

建立连接的过程通常是比较复杂的,所以我建议你在使用中要尽量减少建立连接的动作,也就是尽量使用长连接。

  • 查询缓存

连接建立好了以后,就可以执行 select 语句了,执行逻辑进入第二步:查询缓存。

MySQL 拿到一个查询语句,会先查询缓存,先校验这个语句是否执行过,以 key-value 的形式存在内存里, Key 是查询语句,Value 是结果集。如果缓存 key 被命中,就会直接返回给客户端,如果没有命中,就会执行后续的操作,完成后也会把结果缓存起来,方便下一次调用。当然在真正执行缓存查询的时候还是会校验用户的权限,是否有该表的查询条件。

Mysql 查询不建议使用缓存,因为对于经常更新的数据来说,缓存的有效时间太短了,往往带来的效果并不好,对于不经常更新的数据来说,使用缓存还是可以的,Mysql 8.0 版本后删除了缓存的功能,官方也是认为该功能在实际的应用场景比较少,所以干脆直接删掉了。

  • 分析器

如果没有命中缓存,那么就进入分析器,主要就是分析 SQL 语句是拿来干嘛,也就是解析该语句生成语法树,会分为两步:

  1. 第一步:词法分析, 一条 SQL 语句有多个字符串组成,首先要提取关键字,比如 select,提出查询的表,提出字段名,提出查询条件等等。做完这些操作后,就会进入第二步。
  2. 第二步:语法分析,主要就是判断你输入的 SQL 是否正确,是否符合 MySQL 的语法。主要就是判断你输入的 SQL 是否正确,是否符合 MySQL 的语法。
  • 优化器

经过了分析器分析,MySQL 知道你要干啥了,在开始执行之前,还要先经过优化器的处理。

优化器的作用就是它认为最优的执行方案去执行(虽然有时候也不是最优),比如多个索引的时候该如何选择索引,多表查询的时候如何选择关联顺序等。

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

比如这条语句,既可以先从表 t1 里面取出 c=10 的记录的 ID 值,再根据 ID 值关联到表 t2,再判断 t2 里面 d 的值是否等于 20。也可以先从表 t2 里面取出 d=20 的记录的 ID 值,再根据 ID 值关联到 t1,再判断 t1 里面 c 的值是否等于 10。

这两种的执行逻辑结果是一样的,但是执行效率会有不同,而优化器就是决定使用哪种方案。

  • 执行器(管理器)

当选择了执行方案后,MySQL 就准备开始执行了,首先执行前会校验该用户有没有权限,如果没有权限,就会返回错误信息,如果有权限,就会去调用引擎的接口,返回接口执行的结果。这里的权限其实就是第一步客户端连接到连接器然后去查询出来的权限信息。

引用:mp.weixin.qq.com/s?__biz=MzU…

Update等语句执行流程

InnoDB 引擎更新数据的操作其实还会涉及到两个日志模块的操作:主要就是 binlog 以及 redolog 的操作。

  • 账本与记账板

假如您当了小超市老板,自然会有一个账本记录交易记录,但是可能还要一个赊账记录。因为村里有个姑娘叫小芳,长得美丽又善良。有时候会到你这里白嫖,额,不是,是赊账。你先把记录写在小粉板上,等夜深人静的时候就把粉板的数据同步到归档的账本中。当然粉板也有满的时候,所以当粉板满了就要对账写入账本中,

所以,如果有人要来赊账,或者还账的时候,通常有两种做法:

  1. 直接把账本翻出来,把这次的赊账加上去或者扣除。
  2. 先在粉板上记下这次的帐,等打烊后再把账本翻出来核算。

在生意忙的时候,我们肯定选择后者,因为前者操作太麻烦了。首先,你得找到这个人的赊账总额那条记录。你想想,密密麻麻几十页,掌柜要找到那个名字,可能还得带上老花镜慢慢找,找到之后再拿出算盘计算,最后再将结果写回到账本上。这个时候小芳来赊账,等半天。以后还怎么约小芳到小树林呢?

在 MySQL 中也有这个问题,如果每一次操作都要写进磁盘,然后磁盘也要找到对应的记录,然后再更新。整个过程的 IO 成本,查询成本都很高,为了解决这个问题,MySQL的设计者就用了类似小超市老板粉板的思路来提升更新效率。

而粉板和账本配合的整个过程,其实就是 MySQL 里经常说到的 WAL 技术,WAL 的全称是Write-Ahead Logging,它的关键点就是先写日志,再写磁盘,也就是先写粉板,等不忙的时候再写账本。

  • redo log

首先我们要明确的是binlog 日志是在 server 层的,而redo logInnoDB 特有的。

当有一条记录需要更新的时候,InnoDB引擎就会先把记录写到 redo log(粉板)中,并更新内存,这个时候就算完成了。同时引擎会在适当的时候将这个记录更新到磁盘里,而更新往往是系统比较闲的时候,这就是打烊以后掌柜做的事情。

类似的,InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文件的大小是 1GB,那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写,写到末尾就又回到开头循环写,如下面这个图所示。

write pos 是当前记录的位置,一边写一边后移,写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置,也是往后推移并且循环的,擦除记录前要把记录更新到数据文件。

write pos 和 checkpoint 之间的是“粉板”上还空着的部分,可以用来记录新的操作。如果 write pos 追上 checkpoint,表示“粉板”满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,把 checkpoint 推进一下。

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

要理解 crash-safe 这个概念,可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上,之后即使掌柜忘记了,比如突然停业几天,恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

  • binlog

MySQL 的整体架构其实有两块:一块是 Server 层,还有一块是 引擎层,负责存储相关。前面我们提到的 redo log 是InnoDB 引擎持有的,而 Server 层也有自己的日志,叫 binlog(归档日志)。

那为何会有两份日志呢?

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

假如只有 binlog,当 Server 层 binlog 日志写完后引擎层还没有同步到磁盘就断电了。这个时候重启后 binlog 记录了更新操作,但是引擎层并没有写入磁盘中就导致了从库(主从数据库架构)使用该 binlog 同步数据不一致。

  • 执行流程

有了对两个日志的概念理解,我们就可以继续理解执行器与 InnoDB 引擎执行 update 语句时的内部流程。

  1. Server 层的执行器先调用引擎取出 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在引擎内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。
  2. 执行器拿到数据把这个值 + 1,得到新一行的数据,再调用存储引擎接口写入这行新数据。
  3. InnoDB 引擎将这行数据更新到内存中,同时将这个更新操作所影响的页日志记录到 redo log 中,此时日志处于 prepare 状态,然后会告知 执行器完成了,随时可以提交事务。
  4. 执行器生成这个操作的 binlog ,并把 binlog 写入磁盘。
  5. 执行器继续调用引擎的的提交事务接口,引擎收到请求就把刚刚写入的 redo log 的状态改成提交(commit),更新完成。

最后三步看上去有点“绕”,将 redo log 的写入拆成了两个步骤: prepare 和 commit,这就是"两阶段提交"。

如下图所示,绿色代表执行器执行,白色代表 InnoDB 引擎执行:

  • 两阶段提交

为什么必须有“两阶段提交”呢?这是为了让两份日志之间的逻辑一致。要说明这个问题,我们得从文章开头的那个问题说起:怎样让数据库恢复到半个月内任意一秒的状态?

前面我们说过了,binlog 会记录所有的逻辑操作,并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复,那么备份系统中一定会保存最近半个月的所有 binlog,同时系统会定期做整库备份。这里的“定期”取决于系统的重要性,可以是一天一备,也可以是一周一备。

当需要恢复到指定的某一秒时,比如某天下午两点发现中午十二点有一次误删表,需要找回数据,那你可以这么做:

  1. 首先,找到最近的一次全量备份,如果你运气好,可能就是昨天晚上的一个备份,从这个备份恢复到临时库;
  2. 然后,从备份的时间点开始,将备份的 binlog 依次取出来,重放到中午误删表之前的那个时刻。

由于 redo log 和 binlog 是两个独立的逻辑,如果不用两阶段提交,要么就是先写完 redo log 再写 binlog,或者采用反过来的顺序。我们看看这两种方式会有什么问题。

仍然用前面的 update 语句来做例子。假设当前 ID=2 的行,字段 c 的值是 0,再假设执行 update 语句过程中在写完第一个日志后,第二个日志还没有写完期间发生了 crash,会出现什么情况呢?

  • 先写 redo log 后写 binlog

假如在引擎 写完 redo log 后,binlog 没有写完,异常重启,依然可以根据 redo log 日志把数据恢复,但是 binlog 没有记录这个语句。 所以从库 通过 binlog 同步数据就导致没有把这个这行数据同步过来,丢失了这个事务操作,造成数据不一致。

  • 先写 binlog 再写 redo log

如果写完 binlog 后 崩溃,由于 redo log 还没有写,崩溃恢复后这个事务无效,但是 binlog 却有记录。从库根据 这个 binlog 日志就会导致多处一个事务,与主库不一致。

参考:segmentfault.com/a/119000002…

  • 使用两阶段提交如何正确恢复数据

  • 在时刻A崩溃

如果在图中时刻A的地方,也就是写入redo log 处于prepare阶段之后、写binlog之前,发生了崩溃(crash),由于此时binlog还没写,redo log也还没提交,所以崩溃恢复的时候,这个事务会回滚。这时候,binlog还没写,所以也不会传到备库。到这里,大家都可以理解。

  • 在时刻B崩溃

binlog写完,redo log还没commit前发生crash,那崩溃恢复的时候MySQL会怎么处理?

先来看一下崩溃恢复时的判断规则:

如果redo log里面的事务是完整的,也就是已经有了commit标识,则直接提交;

如果redo log里面的事务只有完整的prepare,则判断对应的事务binlog是否存在并完整:

a. 如果是,则提交事务;

b. 否则,回滚事务。

这里说的在时刻B崩溃恢复指的就是2a步骤,因为binlog已经写完,从备库恢复到具体某个时间点的时候binlog中的记录肯定都会恢复到备库中,因此,肯定要把事务提交了,才能保证当前时间和备库恢复到时间点的时候数据是一样的。

  • 数据更新到磁盘是由读取redo log的还是buffer pool

如果数据库正常运行,调入内存的数据页和磁盘的数据页不一致,这一页称为“脏页”。数据最终更新到磁盘中,就是直接把内存中的数据页写入磁盘,不需要用到redo log。

如果是数据库崩溃恢复的场景,InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新,就会将该页读入内存,然后利用redo log更新内存中的数据,更新完后,内存也变为脏页,就回到了第一种状态。

参考:blog.csdn.net/IT_10/artic…

觉得有收获的话帮忙点个赞吧,让有用的知识分享给更多的人

## 欢迎关注掘金号:五点半社

## 关注微信公众号:五点半社(工薪族的财商启蒙)##