03_mysql整体架构

原创已于 2022-04-06 21:16:48 修改 · 804 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2022-04-06 16:55:35 首次发布

mysql 专栏收录该内容

4 篇文章

订阅专栏

整体架构
server层
2.1 连接器
2.2 查询缓存
2.3 分析器
2.4 优化器
2.5 执行器
存储引擎层
3.1 内存管理
3.2 InnoDB引擎的四大特性
3.2.1 插入缓存
3.2.2 二次写（double write）
3.2.3 自适应哈希
3.2.4 预读
3.3 InnoDB与MyISAM的对比
日志系统
4.1 binlog（归档日志）
4.1 redo log（重做日志）
4.3 redo log 与 binlog区别
4.4 日志的更新流程
4.5 如果数据库误操作, 如何执行数据恢复?
4.6 主从复制
4.6.1 背景
4.6.2 介绍
4.6.3 作用
4.6.4 原理
4.6.5 延时分析
参考文献
整体架构

MySQL 大体上可分为 Server 层和存储引擎层两部分。其中：

Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 Server 层的大多数核心功能；
而存储引擎层负责数据的存储和读取，它支持 InnodDB、MyISAM、Memory 等多种存储引擎。从 MySQL 5.5 开始，默认使用 InnoDB 引擎。
2. server层

存储过程、触发器、试图和内置函数都在这一层。

这里以执行一条查询语句为例，看一下 SQL 语句执行的流程：

2.1 连接器

连接器负责和MYSQL客户端使用TCP建立连接，获取用户权限、维持和管理连接等。

一般可以通过此命令来连接，然后输入密码即可。

mysql -h ip -p port -u username -p
长连接：连接成功后，如果客户端持续有请求，则一直使用同一个连接
优点：在连接时间客户端一直使用同一个连接，避免多次创建连接资源的消耗
缺点：使用的内存被连接对象管理，这些资源只有在断开连接的时候才会释放，长时间不释放会导致内存溢出，被系统强行杀死
针对缺点改进：应该定期断开大连接或者执行大查询后断开连接
2.2 查询缓存

如果收到的是查询请求，则会先到查询缓存中查看，之前是否执行过这条语句，如果有就将值直接返回给客户端。

但是一般都不建议使用，因为对于表的任意一次修改，都会使缓存失效，使用缓存的效率往往得不到体现(适合静态表)。而且，在 MySQL 8.0 中，查询缓存模块已经被删掉了。

2.3 分析器

如果没有命中查询缓存，那就会真正执行查询。这时分析器会对 SQL 语句进行解析，首先执行词法分析，识别语句中的关键字、表名、列名等。

然后，执行语法分析，根据语法规则，判断 SQL 语句是否符合 MySQL 的语法。

2.4 优化器

然后还会经过优化器的处理，例如

在表中有多个索引时，决定使用哪个索引；
一个语句中有多表关联时，决定各个表的连接顺序。
优化器会在保证逻辑结果一直的情况下，尽量使得效率最高。

2.5 执行器

先判断权限，查看是否对该表有查询的权限。
如果设置了慢查询会在对应的日志中看到row_examined来表示扫描的行数
一些场景下(索引)执行器调用一次引擎扫描多行，所以引擎的扫描次数和rows_examined并不相同
执行语句。
3. 存储引擎层

以下主要介绍InnoDB存储引擎。

3.1 内存管理

InnoDB缓冲池管理使用LRU(最近最少使用)算法来管理数据页（页默认大小为16kb）并在此基础上做了优化。

优化：将LRU以5:3的比例分为2个部分，young区域占5/8，old区域占3/8。LRU_指向old区域的头部，也就是在链表的5/8处。
如果访问的数据页是在young区域，跟优化前一样，将访问的数据页放到链表头部。
如果访问的是不存在LRU链表中的数据页，会存放到old区域的头部，也就是5/8处。
如果访问的数据页存在old区域，每次访问会做如下判断
如果这个数据页在LRU中存在的时间超过1秒，就把它移动到链表的头部。
如果这个数据页在LRU中存在的时间短于1秒，位置保持不变。1秒这个时间，是由参数innodb_old_blocks_time控制的。默认值是1000，单位是毫秒
优化LRU的原因：
防止索引或者数据的扫描操作时会访问到很多数据页，这也数据页可能不是活跃的数据，用传统LRU会导致活跃数据被移除，性能低下。
扫描过程中，新插入的数据放在old区，由于是顺序扫描，一个页第一次访问和最后一次访问间隔时间不会超过1秒，因此还会保持在old区。再继续扫描后续的数据页，之前的这个数据页之后不会被扫描，也不会被放到链表的首部，很快就会被淘汰。优化策略的最大收益就是对young区完全没有影响，从而保证缓存区的查询命中率
3.2 InnoDB引擎的四大特性

3.2.1 插入缓存

定义

只对非聚簇索引(非唯一)的插入、删除、修改有效，对于每一次的插入不是写到索引页中，而是先判断插入的非聚簇索引页是否在缓冲池中，如果在则直接插入，如果不在先放到insert buffer对象中，再按照一定的频率进行合并操作，可以把多个插入合并到一个操作中，再写到磁盘。目的是减少随机IO带来的性能损耗。

insert buffer只对insert有效
change buffering是insert buffer的加强版，对insert、delete、update都有效
使用条件：非聚簇索引、非唯一索引
聚集索引（id）一般都是自增的，写入的位置都是顺序的，所以效率很高，不需要这个，但是非聚集索引就等于是随机写，效率较低。
delete 和 update也有自己的buffer，跟insert buffer一样的
merge的执行流程

从磁盘读取数据到内存
从change buffer中读取这个数据页的记录，刷新内存中的数据页
刷新数据页和change buffer中的数据
使用场景

因为merger是真正进行数据更新的时刻，如果change buffer的记录要多，收益就越大。

对于写多读少的情况来说，使用change buffer效果最好
对于写入之后立马读取数据的业务来说，在读的过程中，会立刻触发merge，这样随机访问IO的代价不会减少，反而增加了维护change buffer的代价。
redo log主要节省的是随机写磁盘的IO消耗（转成顺序写），而Change Buffer主要节省的是随机读磁盘的IO消耗

3.2.2 二次写（double write）

如果操作系统或者数据库进程在数据页写磁盘的过程崩溃可以从二次写缓冲中找到数据页的备份来执行crash恢复
3.2.3 自适应哈希

自动监控并且为频繁访问的二级缓存设置哈希值
只适合等值查询（如select * from table where index_col=‘xxx’）
3.2.4 预读

使用两种预读算法来提高I/O性能。

注释：区(extent)由数据页组成

线性预读

将下一个extent提前读取到buffer pool中
InnoDB以64个page为一个extend
随机预读

将下一个extent剩余的page提前读取到buffer pool中
5.5废弃可以启用
3.3 InnoDB与MyISAM的对比

InnoDB支持行级锁、外键、事务、崩溃后的安全恢复、MVCC、不支持全文索引
MyISAM强调的是性能，每次查询都具有原子性，执行性能快与InnoDB
MyISAM适合select密集型的表，而InnoDB适合insert和update密集型的表
4. 日志系统

日志类型：

逻辑日志：存储了逻辑SQL修改语句
物理日志：存储了数据被修改的值
4.1 binlog（归档日志）

另外一种日志是 binlog，它是 MySQL 的 Server 层实现的，任何存储引擎都可以使用。binlog 则没有大小限制，一个文件写完后，写下一个即可。

binlog 主要有三种模式：

statement 格式：记录的是 SQL 语句；不需记录每一行的变化，减少了日志量。
缺点是在某些情况下会导致master-slave中的数据不一致
row 格式：记录的是更新和更新后，行的内容；日志量很大
mixed格式：是statement和row的混合模式，一般操作使用statement，无法使用时使用row
MySQL的BinLog主要有以下两个作用：

数据恢复：数据库数据丢失后，我们可以从某个时间节点的数据备份和该时间点之后的BinLog来恢复数据库的数据；

主从复制：主从复制过程中，主数据库将自身的BinLog发送给从数据库，从数据库通过解析BinLog同步主数据库的数据变更，从而达到主从数据一致；

4.1 redo log（重做日志）

在 MySQL 中，如果每一次的更新操作都需要写入磁盘中，那么整个过程的效率会很低。所以，MySQL 中使用了 Write-Ahead Logging 技术，就是将更新记录先写到日志中，并更新内存（这时内存与磁盘的数据不一致，将这种有差异的数据称为脏页）。而存储引擎会在系统比较空闲时，将这个记录更新到磁盘中。这样可以减少磁盘 io 成本，提高操作速度。

InnoDB 的 redo log 大小是固定的，比如可以配置为一组4个文件，每个文件的大小是1GB，循环写入，如下图：

write pos表示日志当前的记录位置，循环写入，当写完最后一个时，又从第一个继续开始。
check point表示将日志记录的修改写进磁盘的位置，更新完成后，会将check point的数据擦除
[write pos，check point]之间的空闲区域可以写入数据，如果相遇时，需要停止下来执行check point擦除清楚空间
当数据库发生宕机时，只需要把redo log中的数据进行恢复，因为之前的数据已经写入磁盘中，这种能力称为crash-safe
虽然写日志和更新记录都是与磁盘交互，但是 redo log 是顺序写，不用查找数据的位置，并且可以组提交，相比于直接更新记录，很大程度地提高了效率
4.3 redo log 与 binlog区别

redo log bin log
文件大小大小固定通过配置参数max_binlog_size 设置每个 binlog 文件的大小。
实现方式属于InnoDB引擎 server实现，所有引擎都可以拥有
记录方式循环写，修改到磁盘后，日志会被覆盖掉，无法用于数据回滚、恢复等 binlog 追加写入，binlog 文件写到一定大小后，切换到下一个，并不会覆盖以前的日志；
4.4 日志的更新流程

以执行下面的这条 update 语句为例：

update user set age = age + 1 where id = 1;
下载

执行器和 InnoDB 引擎执行时的内部流程如下：

执行器首先通过存储引擎获取 id 为 2 的这一行数据。如果这一行对应的数据页在内存中，则直接返回给执行器；否则就需要到磁盘中去读取。存储引擎读取到数据后，返回给执行器。
执行器拿到 id = 1 的数据后，将 a 改为 1，再发送给存储引擎。
然后存储引擎将这行数据更新到内存中，同时将这个更新操作记录到 redo log 里，此时 redo log 处于 prepare 状态。然后就告诉执行器完成了操作，随时可以提交事务。
执行器就会生成这个操作的 binlog，并把 binlog 写入到磁盘中。
然后执行器就会通知存储引擎，存储引擎把刚才写入的 redo log 进行提交，如此更新就完成了。
这里 redo log 和 binlog 的写入，就是两阶段提交，可以使 redo log 和 binlog 保持数据一致性。

两阶段提交的不同时刻，如果 MySQL 异常重启，崩溃恢复的判断规则如下：

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交。
如果 redo log 里面的事务只有完整的 prepare，则需要判断对应的事务 binlog 是否存在并完整：
如果是，则提交事务；
否则，回滚事务。
如何判断 binlog 是完整的？

statement 格式的 binlog，最后会有 commit；

row 格式的 binlog，最后会有一个 XID event；

在 MySQL 5.6.2 版本中，引入 binlog-checksum 参数，用来验证 binlog 内容的正确性。
4.5 如果数据库误操作, 如何执行数据恢复?

首先查找LSN（log sequence number，日志序列号）的值，这个值代表数据页被刷新回磁盘的 LSN 的大小。然后再去查看 redo log 的 LSN 的大小。如果数据页中的 LSN 值大说明数据页领先于 redo log 刷新回磁盘，不需要进行恢复。反之需要从redo log中恢复数据。
LSN 不仅只存在于重做日志中，在每个数据页头部也会有对应的 LSN 号，该 LSN 记录当前页最后一次修改的 LSN 号，用于在 recovery 时对比重做日志 LSN 号决定是否对该页进行恢复数据。
4.6 主从复制

4.6.1 背景

在复杂的业务系统中，有的sql语句可能需要锁表，导致不能进行读的操作，那么就很容易影响运行中的业务，使用主从复制，让主库负责写，从库负责读(因为数据库中大多数操作是读操作)，这样即使主库出现了锁表，通过读数据库也可以完成读的操作。
4.6.2 介绍

是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库。
4.6.3 作用

随着架构的扩展，业务量越来越大，I/O访问越来越高，单机无法满足，此时如果左多库的存储，物理服务器增加，负荷增加
读写分离，使数据库能支撑更大的并发，主从各自负责读写，极大程度缓解了锁(X锁和S锁)的消耗。在报表(需要读取大量数据进行汇总)中尤为重要，由于部分报表sql语法非常慢，导致锁表，影响前台的服务。使用读写分离，不会影响前台
S锁：也称共享锁，读锁。互相不阻塞，加锁的事务和其他事务只能读取内容，都无法修改
X锁：排他锁，也成写锁。加锁的事务可以修改内容，其他事务无法访问
可以作为后备数据库，主数据库发生故障后，可以避免数据丢失
4.6.4 原理

主数据库中的bin log二进制文件记录了所有sql
把主数据库中的bin log文件的sql语句复制过去
在从数据库中的relay-log重做日志中再执行一次这些sql语句即可
操作步骤：需要三个线程

主数据库的bin log输出线程：每次有从库连接主数据库时，主库会创建一个线程然后发送binlog内容到从库
从库中有2个线程
从库I/O线程：从库I/O读取主库的binlog的输出线程发送的更新并拷贝这些更新的文件到本地文件包括relay-log文件
从库sql线程：读取relay log中的sql语句执行
4.6.5 延时分析

主从复制都是单线程的操作
binlog顺序写，效率高，slave随机写，效率低
主库并发较高时，slave无法及时处理或者slave中大型query语句产生锁等待，就产生了延时
解决延时方案
在mysql之间加入cache层降低mysql的读压力
不同业务的mysql物理上放在不同机器，分散压力。
读写分离
5. 参考文献

https://www.cnblogs.com/binyang/p/11260138.html

https://blog.csdn.net/qq_40378034/article/details/104091113

https://www.cnblogs.com/zhs0/p/10528520.html

https://www.cnblogs.com/lemon-flm/p/15219993.html