mysql基础架构

原创已于 2025-03-01 21:41:14 修改 · 1.3k 阅读

17 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#mysql #数据库

于 2024-07-15 06:41:03 首次发布

Mysql体系架构

在这里插入图片描述

Server层

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

连接池

连接器

通过工具或者命令连接Mysql:mysql -h$ip -P$port -u$user -p。如果密码失败会收到"Access denied for user"，然后客户端程序结束执行。密码正确，一个用户成功建立连接，即使这时候你用管理员账号修改这个用户的权限，也不会影响当前连接的权限。只有再次建立连接的时候才会使用新的权限。

连接池

每次连接都需要创建连接的话，结束连接进行销毁的时候，高并发下非常的浪费资源和性能。

当使用连接池之后，每次建立连接的时候，会去向连接池代理建立连接。连接池首先判断是否有空余的连接，如果有就返回，如果没有空余的连接，就去判断链接数是否已经到了最大，如果没有就去新建一个连接，如果已经到了最大链接数，就会让程序去等待，如果在等待时候有连接的释放或者归还，则可以使用连接，如果没有就返回错误。当线程使用完连接，就会将其归还给连接池，此时判断此连接是否已经超过最大引用次数，如果达到则销毁，如果没有则归还给连接池供其他线程使用。

MySQL的默认连接数为100(max_connection)，可以根据使用的最大链接数(max_used_connection)来动态调整，理论上最大的链接数为16384（和Redis集群的hash槽一样）。

空闲连接的处理

连接完成之后，如果没有后续的操作，这个连接就处于空闲状态，可以通过 show processlist 查看当前空闲的连接。长时间不操作，连接器会自动断开，默认8小时。可以通过参数wait_timeout去控制。已被断开的连接，如果客户端再次发起请求的时候会提示Lost
connection to MySQL server during query。只有重连才可以再次执行请求。

数据库中长连接是指连接之后，客户端持续请求使用同一个连接；短连接则是每次执行完很少的几次请求就断开连接，下次再从新建立一个连接。但是如果全部是用长连接的话，Mysql的内存就涨的特别快。mysql执行过程中临时用的内存是管理在连接对象里面的。只有断开连接的时候才能释放。长期这样操作就会出现OOM。Mysql异常重启。

解决方案1:定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

解决方案2:如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

select查询语句会以key-value存储在内存中。key是查询语句，value是查询结果。正常来说不建议使用查询缓存，如果表经常更新，每次更新操作的时候都会清空表上的所有查询缓存。需要注意Mysql8.0之后不存在查询缓存这个模块

分析器

mysql首先会进行语法分析。语法分析器根据语法规则判断输入的sql是否满足Mysql的语法。如果不满足会收到You have an error in your SQL syntax的错误提示。一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容。
在此也会校验表是否存在，字段是否存在等。

下图是分析器对sql的分析过程步骤：

在这里插入图片描述
SQL语句经过分析器分析之后，会生成一个类似这样的语法树

至此我们分析器的工作任务也基本圆满了。接下来进入到优化器

优化器

优化器就是表里面如果有多个索引，决定使用那个索引。或者在多表联查的时候，决定各个表的连接顺序。通过优化器之后，那最终的sql执行方案就确定下来了

执行器（sql接口）

会首先判断有没有对这个表的操作权限权限存在的话就根据表的引擎定义去调用引擎提供的接口去操作存储引擎最终把处理的结果返回给客户端

存储引擎

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL5.5.5 版本开始成为了默认存储引擎。

InnoDB和MyISAM的区别

事物和外键

MyISAM不支持事务和外键，提供高速的存储和检索，适合大量的select操作

InnoDB支持事务和外键，适合大量的insert和update操作。强调的是安全和完整性。

锁机制

InnoDB支持行级锁，锁定具体的记录，基于索引来加锁实现。

MyISAM支持的是表级锁，锁定整张表。

索引结构

InnoDB使用的聚集索引(聚簇索引)，索引和记录是在一起存储的，即缓存索引，也缓存记录。

MyISAM使用的是非聚集索引，索引和记录分开。

并发处理能力

MyISAM使用的是表锁，会导致表操作并发效率很低。读之间并不阻塞，读写阻塞。

InnoDB读写阻塞与隔离级别有关，可以采用多版本控制(MVCC)来支持并发

存储文件

InnoDB对应两个文件，一个是.frm表结构，一个是.ibd数据库。最大支持64TB

MyISAM对应三个文件，一个是.frm表结构，一个MYD数据文件，一个是MYI索引文件。最大是256TB。一个数据是首先从MYI遍历B+树找到数据的磁盘地址，人后去MYD文件找到具体数据。

InnoDB存储结构

在这里插入图片描述

内存结构

Buffer Pool:

缓存池，简称BP。以page页为单位，page页默认大小16k。BP底层采用链表数据结构管理Page。在innoDB访问表记录和索引记录时会在page页中缓存，以后使用的时候可以减少磁盘io操作，提升效率。

Page管理

page根据状态可以分为三种类型。

free page:空闲的page，未被使用
clean page: 被使用page，数据没有被修改过的
dirty page: 脏页，被使用page，数据被修改过，页中的数据和磁盘的数据产生了不一样。

针对上述三种page类型，通过三种链表结构来维护和管理

free list: 空闲缓冲区，管理free page。
flush list: 需要刷新到磁盘的缓冲区，管理dirty page。内部page按照修改时间排序。越早的越先进行刷盘操作。先从尾部开始。脏页在两个里面，但是互相不影响。lru负责管理page的可用性和释放，而flush负责脏页的刷盘。 lru一些淘汰的操作也会出发flush的刷盘操作。
lru list: 正在使用的缓冲区，管理的clean page和dirty page。缓冲区以midpoint为基点。前面的链表成为new列表区，存放经常访问的数据，占63%；后面的链表成为old列表区，存放使用较少的数据，占37%。

改进型LRU算法维护

普通的lru算法:末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰

改进的lru算法:链表分为new和old两个部分，加入元素不是从头部开始的，是从中间midpint位置插入，如果数据很快被访问，那么page就会向new列表头部移动，如果数据没有被访问，会逐步向old尾部移动，等待淘汰。

每当有新的page读取到Buffer Pool时，InnoDB引擎会判断是否有空闲页，是否足够，如果有就将free page从free list删除，放入lru列表中。没有空闲页就会根据lru算法进行淘汰。

Buffer Pool 配置参数

show variables like '%innodb_page_size%' //查看page页大小

show variables like '%innodb_old%' //查看lru list 列表参数

show variables like '%innodb_buffer%' //查看Buffer Pool参数

建议:将innodb_buffer_pool_size设置为总内存的60%-80%，innodb_buffer_pool_instances可以设置为多个，这样避免缓存争夺。

Change Buffer

写缓存区，简称CB。在进行DML操作的时候，如果BP没有对应的的page数据，并不会立刻将磁盘页加载到缓冲池，而是在CB记录缓冲变更，等未来数据被读取的时候，在将数据合并并恢复到BP中。

默认占用Buffer Pool空间的25%，最大允许占50%，可以根据实际业务进行调整，参数innodb_change_buffer_max_size;

当更新一条记录的时候Buffer Pool存在，直接在Buffer Pool修改，一次内存操作；如果Buffer Pool不存在，会直接在Change Buffer进行一次内存操作，不用再去磁盘查询数据，避免了一次磁盘IO(磁盘随机读写操作)。当下次查询记录的时候，会先进行磁盘的读取，再从Change Buffer中读取信息，然后进行合并，最终载入Buffer Pool。除此之外，当CB内存不足的时候，后台master线程会发起合并操作。

如果根据一个二级索引删除数据，并且这个数据不在BP里面，然后这个索引页也不再内存中，这样CB会缓存这个delete操作，但是BP没有影响，BP中没有这个数据的行，依然获取读取磁盘的。这样才能返回正确的操作行数。

Change Buffer的限制

首先得要求是二级索引。如果不是二级索引到话，那前面change buffer存在意义又是什么呢？没有啥可优化的地方。那不如不要这个change buffer。因为二级索引，本身在磁盘上，在B树的非叶子结点上。

只适用于非唯一普通索引页。因为:如果在索引设置了唯一性，在进行修改的时候，必须要做唯一性的校验，此时需要读取磁盘的数据到Buffer Pool。

Adaptive Hash Index

自适应哈希索引，用于优化对BP数据的查询。InnoDB存储引擎会监控表索引的查找，如果观察到建立哈希索引可以带来速度的提升，则建立哈希索引，所以称之为自适应。InnoDB存储引擎会自动根据访问的频率和模式来为某些页建立哈希索引。

Log Buffer

日志缓冲区，用来保存要写入磁盘的log文件(Redo/Undo)的数据，日志缓冲区的内容定期刷新到磁盘log文件中。日志缓冲区默认大小为16m,缓冲区满时也会自动将其刷新到磁盘中，当遇到BLOB或多行更新的大事务操作时，增加日志可以节省磁盘IO。

Log Buffer写入磁盘，不是直接写入磁盘，而是先写入OS Cache(系统缓存)，然后OS Cache在写入磁盘。Buffer Pool是可以直接进行刷盘操作的。可以看到上图有个标识O_DIRECT。

show variables like '%innodb_flush_log%' Log Buffer未满的时候，一些刷盘机制。
innodb_flush_log_at_trx_commit参数控制日志刷新行为,默认1s

如果为0:每隔1s写入OS Cache。数据会丢失，最多丢失1s的数据。
1:事务提交，立刻写日志文件和刷盘。数据不丢失，但是容易频繁的操作
2:事务提交，立刻写日志文件，但是不刷盘。刷盘是每隔1s刷盘操作。

磁盘结构

系统表空间（System Tablespace）

innodb系统表空间包含innodb数据字典（innodb相关对象的元数据），同时，双写缓冲（doublewrite buffer）、写缓冲（change buffer）和undo日志（undo logs）等也存储于系统表空间中。此外，系统表空间也包含用户在该表空间创建的表和索引等数据。由于系统表空间可以存储多张表，因此，其为一个共享表空间。系统表空间由一个或多个数据文件组成，默认情况下，其包含一个叫ibdata1的系统数据文件，位于mysql数据目录（datadir）下。系统表空间数据文件的位置、大小和数目由innodb_data_home_dir和innodb_data_file_path启动选项控制。针对不同场景，具体举例如下：

参数innodb_data_home_dir未配置，只配置参数innodb_data_file_path：innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend。系统表空间包含ibdata1和ibdata2两个数据文件，二者均位于datadir目录下。
参数innodb_data_home_dir和innodb_data_file_path均进行了配置：

innodb_data_home_dir = /data;innodb_data_file_path=ibdata1:1024M;ibdata2:1024M:autoextend。系统表空间包含ibdata1和ibdata2两个数据文件，二者均位于/data目录下。
参数innodb_data_home_dir位置为空串，只配置参数innodb_data_file_path：

innodb_data_home_dir =;innodb_data_file_path=/d1/ibdata1:1024M;/d2/ibdata2:1024M:autoextend。系统表空间包含ibdata1和ibdata2两个数据文件，ibdata1位于/d1目录下，ibdata2位于/d2目录下。

doublewrite buffer

undo logs

表文件表空间或独立的表空间（File-Per-Table Tablespaces）

表文件表空间是一个单表表空间，该表创建于自己的数据文件中，而非创建于系统表空间中。**当innodb_file_per_table选项开启时，表将被创建于表文件表空间中。否则，innodb将被创建于系统表空间中。**每个表文件表空间由一个.ibd数据文件代表，该文件默认被创建于相应数据库目录中。表文件表空间支持动态（DYNAMIC)和压缩（commpressed)行格式。

通用表空间（General Tablespaces）

通用表空间为通过create tablespace语法创建的共享表空间。通用表空间可以创建于mysql数据目录外的其他表空间，其可以容纳多张表，且其支持所有的行格式。通过create table tab_name … tablespace [=] tablespace_name或alter table tab_name tablespace [=] tablespace_name语法将其添加与通用表空间内。

undo表空间（undo tablespace）

undo表空间由一个或多个包含undo日志的文件组成。innodb_undo_tablespace配置选项控制undo表空间的数目。undo表空间创建于innodb_undo_directory配置选项确定的位置，该选项典型被用于将undo日志放于不同的存储设备上。如果该选项没有确定任何路径，undo表空间则被默认创建于mysql数据目录（datadir）下。

临时表空间（Temporary Tablespace）

用户创建的临时表和磁盘内部临时表创建于共享临时表空间中。innodb_temp_data_file选项确定临时表空间数据文件的相对路径、名字、大小和属性等。如果该选项未确定任何值，默认情况下，系统将在innodb_data_home_dir确定的目录下创建一个叫ibtmp1的自动扩展的数据文件，该文件将稍大于12m。
mysql服务器正常关闭或异常终止初始化时，临时表空间将被移除，并且，mysql服务器每次启动时会被重新创建。当临时表空间被创建时，其被赋予一个动态产生的空间ID（space ID）。如果不能创建临时表空间，
mysql服务器启动将被拒绝。mysql服务器异常终止的情况下，临时表空间将不被移除。这种情况下，DBA能手工移除临时表空间或重启mysql服务器，重启服务器过程中，将自动移除和重新创建临时表空间。
临时表空间并不能存储于裸设备。
这里既然说到了innodb_data_home_dir，那么，就说说这个选项，该选项确定innodb系统表空间数据文件目录路径的共同部分。innodb_file_per_table开启时，该选项设置并不影响表文件表空间的位置。该选项默认值为mysql数据目录。如果你将该选项设置为空串，那么，你可以为innodb_data_file_path设置一个绝对路径值。此外，当为innodb_data_home_dir指定一个值时，需要在尾部添加一个斜杠。

存储架构的演变

在这里插入图片描述
将undo logs与undo tablespace合并;doublewrite buffer双写缓存抽离出去;

后台线程

在这里插入图片描述

IO Thread

在InnoDB中使用了大量的AIO(Async IO)来操作读写，这样可以极大提高数据库的性能。在InnoDB1.0版本之前共有4个IO Thread，分别是write、read、insert buffer、log thread，后来版本将write、read分别增大到了4个。这样一共就有10个了。

read thread: 负责读取操作，将数据从磁盘加载到缓存page页。
write thread: 负责写操作，将缓存脏页刷新到磁盘。
log thread:负责将日志缓冲区刷新到磁盘。
insert buffer thread: 将写缓冲的内容刷新到磁盘里面

Purge Thread

事务提交之后，其使用的undo日志将不在需要，因此需要Purge Thread回收已经分配的undo页。

Page Cleaner Thread

作用是将脏数据刷新到磁盘，脏数据刷盘后相应redo log也就可以覆盖，既可以同步数据，又能达到redo log 循环使用的目的。会调用write thread线程处理。

Master Thread

是InnoDB的主线程，负责调度其他个线程，优先级最高。作用是将缓存池中的数据异步刷新到磁盘，保证数据的一致性。

内部有两个处理，分别是每隔1s和10s处理

1s处理的操作
- 刷新日志缓冲区，刷到磁盘
- 合并写缓冲区数据，根据IO读写压力来觉得是否操作。
- 刷新脏页数据刷到磁盘，根据脏页比例达到75%才操作。show variables like '%innodb_max_dirty_pages_pct%'；//可配置比列 show variables like '%innodb_io_capacity%'//每次刷新多少页
10s的操作
- 脏页刷新到磁盘-无条件
- 合并写缓冲区-无条件
- 刷新日志缓冲区
- 删除无用的undo页。

InnoDB文件存储结构

在这里插入图片描述

InnoDB数据文件存储，分为ibd数据文件–>Segment(段)–>Extent(区)–>Page(页)–>Row(行)

Tablesapce:表空间，用于存储多个ibd数据文件，存储表记录和索引。一个文件包含多个段
Segment:用于管理多个Extent，分为数据段(Leaf node segment)、索引段(Non-leaf node segment)、回滚段(Rollback segment)。一个表至少有两个段，一个管理数据，一个管理索引。每多建一个索引，会多两个segment(索引是树结构，创建一个索引肯定有叶子节点和非叶子节点)。
Extent:一个区是固定包含连续的64个页。大小为1M。当表空间不足，需要分配新的也资源，不是一页一页分配，直接分配一个区。
Page:存储多个Row。大小为16K。包含很多种页类型，常见的数据页，undo页，系统页，事务数据页，大的BLOB对象页。
- Page页是文件最基本的单位，无论何种类型的page，都是有page header，page trailer和page body组成
Row:包含了字段值，事务ID，滚动指针(Roll pointer），字段指针(Field pointer）。

系统文件层

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid文件，socket文件等

日志文件

错误日志(Erroe log):默认是开启的。show variables like '%log_error%'；
通用查询日志(General query log):记录一般查询语句,show variables like '%general%'；
二进制文件(binary log):记录对Mysql数据库执行的更改操作，并且记录语句的发生时间，执行时长；但是不记录select、show等不修改数据库的sql，主要用具数据库的恢复和主从复制。show variables like '%log_bin%'；//是否开启 show variables like '%binlog%'；//参数参看 show binary logs；//查看日志文件
慢查询日志(slow query log):记录所有执行超时的查询sql，默认10秒show variables like '%show_query%'；//是否开启 show variables like '%long_query_time%'；//时长

配置文件

Mysql所有的配置信息文件，如my.cnf、my.imi。

数据文件

db.opt文件:记录这个库默认使用的字符集合校验规则
frm文件:存储与表相关的元数据信息，包括表结构的定义信息等，每张表都会有一个frm文件
MYD文件:MyISAM存储引擎专用，存放MyISAM表的数据，每张表都会有一个.MYD文件。
MYI文件:MyISAM存储引擎专用，存放MyISAM表的索引相关信息。每个表都有一个对应的文件
ibd文件和IBDATA文件:存放InnoDB的数据文件(包含索引).InnoDB存储引擎的两种表空间，独享表空间和共享表空间。独享表空间使用.ibd文件来存放数据，且每张表对应一个.ibd文件。共享表空间使用的是.ibdata文件，所有表共同使用一个和多个，可配置化
ibdata1文件:系统表空间和数据文件，存储表元数据，Undo日志等。
ib_logfile0、ib_logfile1:Redo log日志文件。

pid文件

pid文件是mysqlId应用程序在Unix/Linux环境下的一个进程文件，存放着自己的进程id

socket文件

socket文件也是Unix/Linux环境下才有的，用户在Unix/Linux环境下客户端连接可以不通过TCP/IP网络而直接使用Unix socket来连接Mysql

比较重要的日志系统

Undo Log(回滚日志)

数据库事务开始之前，会将要修改的记录放到undo 日志里面，当事务回滚时或者数据库崩溃时，可以利用Undo 日志，撤销未提交事务对数据库产生的影响。

Undo Log在事务开始前产生；事务提交时并不会立刻删除Undo Log,InnoDb会将该事务对应的undo Log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。

Undo Log是一种逻辑日志，记录的是变化过程。比如一条 INSERT 语句，对应一条DELETE 的 undo log ，对于每个 UPDATE 语句，对应一条相反的 UPDATE 的 undo log ，这样在发生错误时，就能回滚到事务之前的数据状态。

Undo Log采用段的方式管理和记录。在InnoDB数据文件中包含一种rollback segment回滚段，内部包含1024个undo log segment，每个事务只会使用一个undo log segment。 在MySQL5.5的时候，只有一个回滚段，那么最大同时支持的事务数量为1024个。在MySQL 5.6开始，InnoDB支持最大128个回滚段，故其支持同时在线的事务限制提高到了 128*1024 。

作用及原理

实现事务的原子性

Undo log 是为了实现事务的原子性而出现的产物。事务处理过程中，如果出现了错误或者用户执行了RollBack语句，Mysql可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

实现多版本控制MVCC

Undo Log在Mysql InnoDB存储引擎中用来实现多版本并发控制。事务未提交之前，Undo lOG保存了未提交的版本数据，Undo Log中的数据可以作为快照供其他并发事务进行快照读。

事务A手动开启事务，执行更新操作，首先会把更新命中的数据备份到 Undo lOG中，如果事务回滚，则可以从Undo lOG中找回

事务B手动开启事务，执行查询操作，会读取Undo日志数据返回，进行快照读。

Redo Log(重做日志)

redo log 是 InnoDB 引擎特有的日志，属于存储引擎层面的。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。redo log 是物理日志，记录的是“在某个数据页上做了什么修改”
redo log 随着事务操作的执行，就会生成redo log，在事务提交时会产生redo log写入log buffer，并不是随着事务的提交就立刻写入磁盘文件。而是等事务操作的脏页写入磁盘之后，Redo log的使命完成了，这时候占用的空间就可以重用(被覆盖写)。

Redo Log的重要参数

-- 设置redo log buffer大小参数，默认16M ，最大值是4096M，最小值为1M。
show variables like '%innodb_log_buffer_size%';

-- 设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置，其中的 ib_logfile0 和 ib_logfile1 即为redo log文件。
show variables like '%innodb_log_group_home_dir%'

-- 设置redo log文件的个数，命名方式如: ib_logfile0, iblogfile1... iblogfileN。默认2个，最大100个。
show variables like '%innodb_log_files_in_group%';

-- 设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。
show variables like '%innodb_log_file_size%';

Redo Log的机制

工作原理

Redo Log是为了实现事务的持久性而出现的产物。防止在发生故障的时间点，尚有脏页未写入表的IDB文件中，在重启mysql服务的时候，根据Redo Log进行重做，从而达到事务的未入磁盘数据进行持久化这一特性

在这里插入图片描述

在更新数据的时候，会将原始数据写入到Undo Buffer里面，将修改后的数据写入到Redo Buffer中。在更新的时候出现问题，可以根据Redo log来进行重做，将表里的数据更新成最新的。

redo log的写入机制

在这里插入图片描述

如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。
Mysql使用WAL 技术： Write-Ahead Logging先写日志(redo log buffer)，再写磁盘(redo logfile)。

当一条记录需要更新的时候，会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。

InnoDB 的 redo log 是固定大小的。比如可以配置为一组 4 个文件，每个文件的大小是 1GB，那么总共就可以记录 4GB 的操作。从头开始写，写到末尾就又回到开头循环写。

write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回溯 0 号文件开头。

checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

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

每个InnoDB存储引擎至少有一个重做日志文件组，每个文件组至少有两个重做日志文件，默认为ib_logfile0,ib_logfile1

Redo Buffer持久化到Redo Log的策略，可通过innodb_flush_log_at_trx_commit 配置在这里插入图片描述

MTR=小事务=一个事务单元。

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

建议配置成2，Mysql挂了也是没有数据损失的，只有整个服务器挂了才会损失1秒的事务提交数据。

重启重启innodb 时，首先会检查磁盘中数据页的 LSN(日志序列号) ，如果数据页的LSN 小于日志中的 LSN ，则会从 checkpoint 开始恢复。还有一种情况，在宕机前正处于checkpoint 的刷盘过程，且数据页的刷盘进度超过了日志页的刷盘进度，此时会出现数据页中记录的 LSN 大于日志中的 LSN，这时超出日志进度的部分将不会重做，因为这本身就表示已经做过的事情，无需再重做。
在这里插入图片描述

Bin Log(二进制日志=归档日志)

# log-bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，则binlog文件默认会放在data数据目录下
log-bin=/xx/xx/xx

#binlog的格式 statement、row、mixed
binlog-format=ROW

#表示没1次执行写入就与磁盘同步，影响性能，为0时，表示事务提交的时候不做刷盘操作，有系统决定
sync-binlog=1

# Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环境中每台mysql服务器的id不能一样，不加启动会报错
server-id=1

# 执行自动删除距离当前15天以前的binlog日志文件的天数， 默认为0， 表示不自动删除
expire-logs-days=15

max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

-- 查看有多少binlog文件
show binary logs;

--查看binlog的配置
show variables like '%log_bin%';

--log_bin：binlog日志是否打开状态
--log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
--log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
--sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数--sql_log_bin来实现。比如说，模拟主从同步复制异常。

BingLog记录模式

最开始是没有InnoDB存储引擎的。Mysql自带的存储引擎MyISAM 没有 crash-safe 的能力。binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。

binlog是记录所有数据库表结构变更以及数据修改的二进制日志，不会记录select和show这类操作。binlog是以事件形式记录，还包含了语句消耗的时间。

binlog文件名默认为“主机名-binlog-序列号”格式，例如“oak-binlog-000001”,也可以在配置文件执行名称。

文件记录模式有三种

STATMENT：基于SQL语句的复制。每一条被修改的数据都会记录到master的binglog中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行一遍，简称语句复制。
- 优点:日志量少，减少io。
- 缺点:如果sql使用了可变变量，类似当前时间、UUID()、SYSDATE()等，这样到从库里面就会变的不一样了。
ROW：基于行的复制，日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
- 优点:清楚的记录每一行数据的细节，能完全实现主从数据同步和数据的恢复
- 缺点:批量的操作，会产生大量的日志，类似加个字段等。性能不如Statement。假设update语句更新10行数据，Statement方式就记录这条update语句，Row方式会记录被修改的10行数据。
MIXED：混合模式复制以上两种模式混合使用，一般会使用STATMENT模式保存binlog，对于STATMENT模式无法复制的操作使用ROW模式保存binlog，Mysql会根据执行的sql语句选择写入模式。

使用场景

主从复制:在主库中开启binlog，这样主库就可以把binlog传递给从库，从库拿到binlog后实现数据恢复达到和主从数据的一致性。
数据恢复:通过mysqlbinlog工具来恢复数据

bin log文件结结构

bin log 文件记录的是对数据库的各种操作，用来表示各种操作的数据结构是log event。不同的修改操作对应不同的logevent。比如常用的log event:Query event 、row event 、xid event等。binlog文件的内容就是各种log event的集合

binlog文件中的log event结构如下:
在这里插入图片描述

日志记录机制

写入机制

根据记录模式触发event事件生成log event(事件触发执行机制)
将事物执行过程中产生的log evevt写入缓冲区，每个事务线程都有一个缓冲区
事务在提交阶段会将产生的log evet写入外部的binlog文件中。
log event保存在一个binlog_cache_mngr数据结构中。在该结构中有两个缓冲区，一个是stme_cache用于存放不支持事务的信息；另一个是trx_cache，用于存放支持事务的信息。
binlog是记录的sql语句的原始逻辑且是追加写，不会覆盖前面的binlog文件日志文件大小根据max_binlog_size控制
不同的事务是按照串行的方式写入到binlog文件中，所以一个事务包含的log event信息在binlog文件中是连续的，中间不会插入其他事务的log evetn。binlog是引擎插件的上层功能，事务提交第一个就会调用binlog功能接口，然后再调用其他存储引擎的功能接口。因此是先写binlog，然后执行innodb的redolog/undo 脏页刷新操作。
binlog写入磁盘机制主要通过 sync_binlog 参数控制，默认值是 0。
- 为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘。虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失。
- 也可以设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全。
- 还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，这种如果机器宕机会丢失N个事务的binlog。

发生以下任何事件时, binlog日志文件会重新生成：

服务器启动或重新启动
服务器刷新日志，执行命令flush logs
日志文件大小达到 max_binlog_size 值，默认值为 1GB

如何查看binglog日志

用mysql自带的命令工具 mysqlbinlog 查看binlog日志内容

# 查看bin-log二进制文件（命令行方式，不用登录mysql）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 

# 查看bin-log二进制文件（带查询条件）
mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000007 start-datetime="2023-01-21 00:00:00" stop-datetime="2023-02-01 00:00:00" start-position="5000" stop-position="20000"

binlog日志文件恢复数据

用binlog日志文件恢复数据其实就是回放执行之前记录在binlog文件里的sql，举一个数据恢复的例子

# 先执行刷新日志的命令生成一个新的binlog文件mysql-binlog.000008，后面我们的修改操作日志都会记录在最新的这个文件里
flush logs;
# 执行两条插入语句
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('4', 'zhuge', '666');
INSERT INTO `test`.`account` (`id`, `name`, `balance`) VALUES ('5', 'zhuge1', '888');
# 假设现在误操作执行了一条删除语句把刚新增的两条数据删掉了
delete from account where id > 3;

现在需要恢复被删除的两条数据，我们先查看binlog日志文件

mysqlbinlog --no-defaults -v --base64-output=decode-rows D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000008

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#230127 23:32:24 server id 1  end_log_pos 291 CRC32 0x4528234f  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1674833544/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1342177280/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#230127 23:32:24 server id 1  end_log_pos 345 CRC32 0x7482741d  Table_map: `test`.`account` mapped to number 99
# at 345
#230127 23:32:24 server id 1  end_log_pos 396 CRC32 0x5e443cf0  Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=4
###   @2='zhuge'
###   @3=666
# at 396
#230127 23:32:24 server id 1  end_log_pos 427 CRC32 0x8a0d8a3c  Xid = 56
COMMIT/*!*/;
# at 427
#230127 23:32:40 server id 1  end_log_pos 492 CRC32 0x5261a37e  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 492
#230127 23:32:40 server id 1  end_log_pos 564 CRC32 0x01086643  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1674833560/*!*/;
BEGIN
/*!*/;
# at 564
#230127 23:32:40 server id 1  end_log_pos 618 CRC32 0xc26b6719  Table_map: `test`.`account` mapped to number 99
# at 618
#230127 23:32:40 server id 1  end_log_pos 670 CRC32 0x8e272176  Write_rows: table id 99 flags: STMT_END_F
### INSERT INTO `test`.`account`
### SET
###   @1=5
###   @2='zhuge1'
###   @3=888
# at 670
#230127 23:32:40 server id 1  end_log_pos 701 CRC32 0xb5e63d00  Xid = 58
COMMIT/*!*/;
# at 701
#230127 23:34:23 server id 1  end_log_pos 766 CRC32 0xa0844501  Anonymous_GTID  last_committed=2        sequence_number=3       rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 766
#230127 23:34:23 server id 1  end_log_pos 838 CRC32 0x687bdf88  Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1674833663/*!*/;
BEGIN
/*!*/;
# at 838
#230127 23:34:23 server id 1  end_log_pos 892 CRC32 0x4f7b7d6a  Table_map: `test`.`account` mapped to number 99
# at 892
#230127 23:34:23 server id 1  end_log_pos 960 CRC32 0xc47ac777  Delete_rows: table id 99 flags: STMT_END_F
### DELETE FROM `test`.`account`
### WHERE
###   @1=4
###   @2='zhuge'
###   @3=666
### DELETE FROM `test`.`account`
### WHERE
###   @1=5
###   @2='zhuge1'
###   @3=888
# at 960
#230127 23:34:23 server id 1  end_log_pos 991 CRC32 0x386699fe  Xid = 65
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
......

找到两条插入数据的sql，每条sql的上下都有BEGIN和COMMIT，我们找到第一条sql BEGIN前面的文件位置标识 at 219(这是文件的位置标识)，再找到第二条sql COMMIT后面的文件位置标识 at 701
我们可以根据文件位置标识来恢复数据，执行如下sql：

mysqlbinlog  --no-defaults --start-position=219 --stop-position=701 --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test

# 补充一个根据时间来恢复数据的命令，我们找到第一条sql BEGIN前面的时间戳标记 SET TIMESTAMP=1674833544，再找到第二条sql COMMIT后面的时间戳标记 SET TIMESTAMP=1674833663，转成datetime格式
mysqlbinlog  --no-defaults --start-datetime="2023-1-27 23:32:24" --stop-datetime="2023-1-27 23:34:23" --database=test D:/dev/mysql-5.7.25-winx64/data/mysql-binlog.000009 | mysql -uroot -p123456 -v test

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志。

在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭。

# 查看错误日志存放位置
show variables like '%log_error%';

通用查询日志

通用查询日志记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给 MySQL 数据库服务器的所有 SQL 指令等，如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功还是失败，MySQL都会将其记录下来。

通用查询日志用来还原操作时的具体场景，可以帮助我们准确定位一些疑难问题，比如重复支付等问题。

general_log_file：通用查询日志记录的位置参数。

show variables like '%general_log%';
# 打开通用查询日志 是否开启日志参数，默认为OFF，处于关闭状态，因为开启会消耗系统资源并且占用磁盘空间。一般不建议开启，只在需要调试查询问题时开启。
SET GLOBAL general_log=on;

各种日志的比较区别

redo log和undo log

假设有A、B两个数据，值分别为1、2，开启事务分别对其进行修改A → 3，B → 4，在提交，过程如下：

事务开始
记录A=3到redo log
修改A=3
记录A=1到undo log
记录B=4到redo log
修改B=4
记录B=2到undo log
将redo log写入磁盘
事务提交

一个事务在执行到一半的时候实例崩溃了，在恢复的时候先恢复redo，再根据redo宕机前没有提交的事务来决定是否回滚undo。

redo log 和binlog的区别

binlog是服务层的功能，redo log是innodb提供的。
redo log是物理日志，记录数据页更新内容，binlog是逻辑日志，记录更新过程
redo log日志是循环写，日志空间大小固定，binlog是追加写。
redo log作为服务器异常宕机后事务数据自动恢复使用，binlog作用于主从复制和数据恢复。binlog没有自动crash-safe功能。

在这里插入图片描述

一个update的执行过程

在这里插入图片描述

客户端发送UPDATE请求

UPDATE users SET name = 'Alice' WHERE id = 1;

解析SQL并生成执行计划
解析器：检查SQL语法，生成抽象语法树（AST）。
优化器：选择最优执行计划（例如使用索引查找id=1的行）。
开启事务
1. 如果未显式开启事务（如BEGIN），InnoDB会隐式开启一个事务（假设autocommit=1）
查找要更新的行
根据WHERE条件（id=1）查找数据页：
1. 若数据页在Buffer Pool中，直接修改。
2. 若不在Buffer Pool，从磁盘加载到Buffer Pool。
写入undo log
生成undo log：将旧数据（修改前的name值）写入undo log。
1. 支持事务回滚（Rollback）。
2. 实现MVCC（多版本并发控制），其他事务可能读取旧版本数据。
更新内存中的数据页
1. 在Buffer Pool中更新数据页的name字段为Alice。
2. 此时数据页变为“脏页”（Dirty Page），但尚未写入磁盘。
写入redo log buffer
生成redo log：记录数据页的物理修改（如“将id=1的name字段从xxx改为Alice”）。
redo log先写入redo log buffer（内存缓冲区）。
事务提交
redo log prepare阶段写入磁盘
两阶段提交（2PC）：为确保事务持久性和日志一致性。
1. 将redo log buffer中的日志标记为prepare状态,告知执行器执行完成，随时可以提交事物。
2. 调用fsync()将redo log写入磁盘（依赖innodb_flush_log_at_trx_commit配置）。
写入binlog
1. 生成binlog：记录逻辑操作（如原始SQL语句或行变更事件）。
2. binlog写入磁盘（依赖sync_binlog配置）。
redo log commit阶段标记为提交
返回客户端成功

在出现异常的情况下binlog 和 redo log先写和后写的区别假设当前存在一个ID=2,字段c=0，将c修改为1

先写 redo log 后写 binlog:
写完redo log的时候出现异常，此时还未写binlog。系统即使崩溃，仍然可以把数据恢复，所以恢复这一行，字段c的值是1.但是由于binlog未写入数据，如果需要用binlog来恢复数据的话，那这个临时库就会少了一次更新。那此时字段c的值就是0，与原库值不同.
先写 binlog 后写 redo log:如果写完binlog之后出现异常，未写入redo log，崩溃恢复之后这个事务是无效的。所以字段c的值应该是0。但是binlog中已经存在语句，所以是用binlog恢复的时候就会多出一条事务出来，字段c的值就变成1.与原库值不同