理解 Mysql 架构 _mysql的nbservice是干嘛的-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_39165055/article/details/147106726

全面理解Mysql架构

MySQL的内部组件结构

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

客户端

连接工具（Navicat、TablePlus、JDBC 等等）其实都是 MySQL客户端(Client)，主要用于发送执行sql语句的请求。

服务端

大体来说，MySQL 服务端可以分为 Server 层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

而存储引擎层负责数据的存储和检索。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL5.5.5 版本开始成为了默认存储引擎。也就是说，你执行 create table 建表的时候，如果不指定引擎类型，默认使用的就是InnoDB。不过，你也可以通过指定存储引擎的类型来选择别的引擎，比如在 create table语句中使用 engine=memory, 来指定使用内存引擎创建表。

存储引擎层

负责数据的存储和检索。
MySQL 支持多种存储引擎，如 InnoDB、MyISAM、Memory 等，每种引擎都有其特点和适用场景。
InnoDB 是 MySQL 的默认存储引擎，它支持事务、行级锁定和外键约束。InnoDB 有自己的日志系统，称为 redo log（重做日志） 和 undo log（撤销日志）。redo log 用于保证事务的持久性，在数据库崩溃后可以用来恢复数据；undo log 用于支持事务的原子性和多版本并发控制（MVCC）。

Server 层

负责处理 SQL 语句、解析、优化、缓存等。
负责权限管理、用户认证等。
提供了各种 SQL 函数和存储过程。
提供了复制、备份、恢复等高级功能。
Server 层有自己的日志系统，称为 binlog（归档日志）。binlog 记录了所有修改数据库数据的 SQL 语句（如 INSERT、UPDATE、DELETE 等）的信息，但不包括 SELECT 和 SHOW 这类查询语句。binlog 主要用于复制和恢复操作。

连接器

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

mysql -h$ip -P$port -u$user -p

分析器

SQL语句经过分析器分析之后，会生成一个这样的语法树

词法分析：

主要负责从 SQL 语句中提取关键字，比如：查询的表，字段名，查询条件等等。
词法分析阶段是从 information_schema 里面获得表的结构信息的。

语法分析：

判断输入的SQL 语句是否满足 MySQL 语法
如果 SQL 语法错误，经常会见到这样的错误提醒 You have an error in your SQL syntax ，一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。

优化器

经过了分析器，若SQL语句正确，就会进入优化器。优化器的作用是在基于同一个查询语句的多个查询方案中找出效率最高的。比如，在表里面有多个索引的时候，决定使用哪个索引，在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

执行器

开始执行的时候，要先判断一下你对这个表有没有执行查询的权限，如果没有，就会返回没有权限的错误。如果有权限，就打开表继续执行。这是一种安全机制，确保只有被授权的用户才能访问和操作数据。

注意：

如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。
在语法分析过程中，解析器会进行一些初步的权限检查 precheck，例如验证用户是否有权访问指定的数据库和表。
有些时候，SQL语句要操作的表不只是SQL字面上那些。SQL执行过程中可能会有触发器这种在运行时才能确定的过程，precheck是不能对这种运行时涉及到的表进行权限校验的，所以需要在执行器阶段进行权限检查。

SQL查询语句是如何执行的？

 select * from user where id=10;

Select执行流程

SQL更新语句是如何执行的？

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

查询语句的那一套流程，更新语句也是同样会走一遍。执行语句前要先连接数据库，这是连接器的工作。在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们要讨论的主角：redo log（重做日志）和 binlog（归档日志）

Update执行流程

有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的update 语句时的内部流程。

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

下图是update 语句 update T set c=c+1 where ID=2的执行流程图

Mysql Log

redo log

redo log 作用

MySQL 的更新操作如果每次都直接写入磁盘，确实会面临较高的 I/O 成本和查找成本。因为每次更新都需要涉及多次磁盘 I/O 操作，性能开销很大

将数据页从磁盘加载到内存
修改内存中的数据
将修改后的数据页写回磁盘
磁盘需要定位到具体的数据位置

为了解决这个问题，MySQL 采用了 WAL（Write-Ahead Logging，预写式日志）技术。WAL 的核心思想是：

先将修改操作记录到日志中（redo log）
等合适的时候再将修改应用到磁盘上的数据文件

这种方式的优势在于：既保证了数据持久性，又大大提高了数据库的写入性能

日志是追加写入的，顺序 I/O 比随机 I/O 快很多
日志文件通常较小，写入速度更快
可以在内存中积累多个修改，然后批量写入磁盘
即使系统崩溃，也可以通过重放日志来恢复数据

redo log 的写入策略

# 查看innodb_flush_log_at_trx_commit参数值：
show variables like 'innodb_flush_log_at_trx_commit';

# 设置innodb_flush_log_at_trx_commit参数值(也可以在my.ini或my.cnf文件里配置)：
set global innodb_flush_log_at_trx_commit=1;

innodb_flush_log_at_trx_commit：这个参数控制 redo log 的写入策略，它有三种可能取值：

设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据。
设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，但是效率稍微差一点，线上系统推荐这个设置。
设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据。

查看 innodb_flush_log_at_trx_commit

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用操作系统函数 write 写到文件系统的 page cache，然后调用操作系统函数 fsync 持久化到磁盘文件。

redo log 的写入流程

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。

binlog

MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的 redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为binlog（归档日志）。

binlog作用

binlog二进制日志记录保存了所有执行过的修改操作语句，不保存查询操作。如果 MySQL 服务意外停止，可通过二进制日志文件排查，用户操作或表结构操作，从而来恢复数据库数据。

启动binlog记录功能，会影响服务器性能，但如果需要恢复数据或主从复制功能，则好处则大于对服务器的影响。

# 查看binlog相关参数
2 show variables like '%log_bin%';

binlog写入磁盘机制

binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。

为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。

也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。

还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后

才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

undo log

undo log（回滚日志）‌是数据库事务管理中的一个重要组成部分，主要用于在事务执行过程中记录数据修改前的状态，以便在事务失败或需要回滚时能够恢复到事务开始前的状态。Undo Log确保了事务的原子性，即事务中的操作要么全部成功，要么全部失败，不会出现部分操作成功的情况。

undo log 作用

‌事务回滚‌：Undo Log记录了事务在修改数据之前的原始状态，用于在事务回滚时撤销未完成的修改，确保事务的原子性。当事务执行失败或用户显式要求回滚时，系统会通过Undo Log将所有修改的数据恢复到事务开始前的状态‌
多版本并发控制（MVCC）‌：Undo Log为其他事务提供一致性读视图，避免读写冲突，确保并发操作的一致性和隔离性。在InnoDB存储引擎中，Undo Log通过记录每次更新操作前的数据状态，为其他事务提供旧版本的数据，从而实现MVCC‌
数据恢复‌：在数据库发生故障时，Undo Log可以帮助恢复数据到一致的状态，确保数据的完整性和一致性。例如，在系统崩溃后重启时，可以通过Undo Log回滚未完成的事务，确保数据不会因为系统故障而丢失‌

Mysql内部两阶段提交（内部XA）

为了让两份日志之间的逻辑一致，mysql会采用两阶段提交。

两阶段提交是一种分布式系统中常用的一致性协议，用于确保不同节点之间的事务操作能够保持一致性。它通常包括以下两个阶段：

准备阶段（Prepare Phase）：在这个阶段，协调者节点向所有参与者节点发送一个准备请求，要求参与者节点准备好执行事务操作。参与者节点在收到准备请求后会执行事务操作，并将操作的执行结果反馈给协调者节点。
提交阶段（Commit Phase）：在所有参与者节点都已经准备好执行事务操作的情况下，协调者节点向所有参与者节点发送一个提交请求。参与者节点在收到提交请求后，如果确认操作可以提交，则执行提交操作；否则执行回滚操作。