tag: MySQL

【资料图】

excerpt: 了解 MySQL 的架构是使用 MySQL的重要前提。

1. 基础架构

第一层：连接层

负责与 MySQL 客户端之间的通信，提供如连接处理，身份验证等功能。

第二层：核心服务层

通常叫做 SQL Layer。在 MySQL 数据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。存储过程，触发器，视图等功能也在这一层完成。

第三层：存储引擎层

通常叫做 Storage Engine Layer，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在 MySQL 中的数据。就像 Linux 众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎 API 来与它们交互的。这个接口隐藏了各个存储引擎不同的地方。对于查询层尽可能的透明。这个 API 包含了很多底层的操作。如开始一个事物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器的请求。

第四层：数据存储层

主要是将数据存储在运行于裸设备的文件系统之上，存储引擎将数据的读写功能提交到数据存储层，由它来跟文件系统交互完成数据读写。

2. 逻辑模块

实际上，前文中提到的每一层中都含有各自的很多小模块，其大致的模块结构划分如下所示。

接下来我们就针对核心服务层(SQL Layer)和存储引擎层(Storage Engine Layer)做一个简单的分析。

2.1 核心服务层(SQL Layer)

核心服务层由连接器，缓存，分析器，优化器，执行器等小的模块组成，也包括 MySQL 的大多数核心功能区以及所有内置函数。接下来介绍一些核心模块：

Connectors

指的是不同语言与MySQL的交互模块，如mysql-connector-php、mysql-connector-java等。

Management Serveices & Utilities

Mysql 的系统管理和控制工具，这些工具主要在 MySQL Enterprise Edition 版本中提供，详情可以参考网页：

https://www.mysql.com/products/enterprise/

Connection Pool

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

负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，

接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

SQL Interface: SQL接口

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

在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。

Parser: 解析器

Query 请求经过 SQL接口层后就进入 SQL 解析器中，在解析器中 SQL 命令将会被验证和解析。解析器是由 Lex 和 YACC 实现的，是一个很长的脚本，主要功能是：

a. 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。

b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

Optimizer: 查询优化器

SQL 语句真正进入执行阶段之前，MySQL 会使用查询优化器对查询进行优化。根据客户端请求的 SQL 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，从多种执行方案中找到一个最优的策略，并告诉后面的程序(数据库引擎)如何取得这个 SQL 语句的结果。优化器使用的是 “选取-投影-联接” 策略进行查询。

Cache&Buffer：查询缓存

Cache 主要的功能是将客户端提交给 MySQL 的 Select 类的 Query 请求的返回结果集缓存到内存中，并生成一个 Hash 数与该 Query 做对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

Storage Engine: 存储引擎接口

存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，诸如 InnoDB, MyISAM 等都是这个抽象的具体实现。

MySQL 区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

2.1.1 SQL 语句执行过程

MySQL 整个查询执行过程，总的来说分为 6 个步骤 :

连接：客户端向 MySQL 服务器发送一条查询请求，与connectors交互：连接池认证相关处理。缓存：服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段。解析：服务器进行SQL解析(词法语法)、预处理。优化：再由优化器生成对应的执行计划。执行：MySQL 根据执行计划，调用存储引擎的 API来执行查询。结果：将结果返回给客户端，同时缓存查询结果。

首先程序的请求会通过 mysql 的 connectors 与其进行交互，请求到处后，会暂时存放在连接池（connection pool)中并由处理器（Management Serveices & Utilities）管理。当该请求从等待队列进入到处理队列，管理器会将该请求丢给SQL接口（SQL Interface）。

SQL接口接收到请求后，它会将请求进行hash处理并与缓存中的结果进行对比，如果完全匹配则通过缓存直接返回处理结果；否则，需要完整的走一趟流程：

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

2.1.2 核心服务层子模块

2.1.2.1. Connectors ：客户端/服务端通信协议

MySQL 客户端/服务端通信协议是 “半双工” 的，在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置 max_allowed_packet参数，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用 SELECT * 以及加上 LIMIT 限制的原因之一。

客户端建立与 MySQL 服务的连接，是由连接器Connectors来完成的。除此之外，连接器 Connectors 还负责从客户端获取权限、维持和管理连接等。连接命令为：

$mysql -hlocalhost -P3306 -u$user -p$passwdWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 8.0.29 Source distributionCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type "help;" or "\h" for help. Type "\c" to clear the current input statement.

复制

验证通过后，连接器会到权限表里面查出你拥有的权限，之后这个连接里面的权限判断逻辑，都将依赖于此时读到的权限，一个用户成功建立连接后，即使管理员对这个用户的权限做了修改，也不会影响已经存在连接的权限，只有再新建的连接才会使用新的权限设置。

连接完成后，如果你没有后续的动作，这个连接就处于空闲状态，你可以在 show processlist 命令中看到它。

mysql> show processlist;+----+-----------------+-----------+-------+---------+------+------------------------+------------------+| Id | User            | Host      | db    | Command | Time | State                  | Info             |+----+-----------------+-----------+-------+---------+------+------------------------+------------------+|  5 | event_scheduler | localhost | NULL  | Daemon  |  196 | Waiting on empty queue | NULL             ||  8 | root            | localhost | NULL  | Sleep   |   46 |                        | NULL             ||  9 | root            | localhost | mysql | Query   |    0 | init                   | show processlist |+----+-----------------+-----------+-------+---------+------+------------------------+------------------+

复制

客户端如果太长时间没动静，连接器就会自动将它断开；这个时间是由参数 wait_timeout 控制的，默认值是8小时。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒：Lost connection to MySQL server during query。

客户端与数据库的连接分为长连接和短连接。长连接是指连接建立成功后，如果客户端持续有请求，则一直使用同一个连接；短连接则是指客户端每次执行完很少的几次查询就断开连接，下次查询再重新建立新的连接。

建立连接的过程通常是比较复杂的，建议在使用中要尽量减少建立连接的动作，尽量使用长连接。但是全部使用长连接后，有时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

怎么解决这个问题呢？可以考虑以下两种方案：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2.1.2.2. 查询缓存

在解析一个查询语句前，如果查询缓存是打开的，那么 MySQL 会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存存放在一个引用表 (不要理解成table，可以认为是类似于 HashMap 的数据结构)，通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同 (例如 : 空格、注释)，都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。比如函数 NOW() 或者 CURRENT_DATE() 会因为不同的查询时间，返回不同的查询结果；包含 CURRENT_USER 或者 CONNECION_ID() 的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

MySQL 查询缓存系统会跟踪查询中涉及的每个表，如果这些表 (数据或结构) 发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿，而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外。

另外查询缓存由两个特性需要注意：

任何的查询语句在开始之前都必须经过检查，即使这条 SQL 语句永远不会命中缓存。两个SQL语句，只要相差哪怕是一个字符（例如 大小写不一样：多一个空格等），那么两个SQL将使用不同的cache。

基于此，并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，特别是写密集型应用，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。可以尝试打开查询缓存，并在数据库设计上做一些优化 :

用多个小表代替一个大表，注意不要过度设计。批量插入代替循环单条插入。合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适。可以通过 SQL_CACHE 和 SQL_NO_CACHE 来控制某个查询语句是否需要进行缓存。

注 : SQL_NO_CACHE 是禁止缓存查询结果，但并不意味着 cache 不作为结果返回给 query，之前的缓存结果之后也可以查询到

mysql> SELECT SQL_CACHE COUNT(*) FROM a;+----------+| COUNT(*) |+----------+|    98304 |+----------+1 row in set, 1 warning (0.01 sec)mysql> SELECT SQL_NO_CACHE COUNT(*) FROM a;+----------+| COUNT(*) |+----------+|    98304 |+----------+1 row in set, 1 warning (0.02 sec)

复制

可以在 SELECT 语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，都可以指定不进行查询缓存，使用 SQL_NO_CACHE 选项。对于那些变化不频繁的表，查询操作很固定，可以将该查询操作缓存起来，这样每次执行的时候不实际访问表和执行查询，只是从缓存获得结果，可以有效地改善查询的性能，使用 SQL_CACHE 选项。

查看开启缓存的情况，可以知道query_cache_size的设置是否合理

mysql> SHOW VARIABLES LIKE "%query_cache%";+------------------------------+-----------+|  Variable_name               | Value     |+------------------------------+-----------+| have_query_cache| YES|+------------------------------+-----------+| query_cache_limit| 1048576|+------------------------------+-----------+| query_cache_min_res_unit| 4096|+------------------------------+-----------+| query_cache_size| 134217728 |+------------------------------+-----------+| query_cache_type| ON|+------------------------------+-----------+| query_cache_wlock_invalidate | OFF|+------------------------------+-----------+

复制

查询服务器关于 query_cache 的配置:

query_cache_limit：超出此大小的查询将不被缓存

query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。

query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）

query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下：

0：OFF 相当于禁用了

1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存

2：DENAND 则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。

query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成在读表获取结果。

对于查询缓存的一些操作

FLUSH QUERY CACHE : 清理查询缓存内存碎片

RESET QUERY CACHE : 从查询缓存中移出所有查询

FLUSH TABLES : 关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

如果查询缓存碎片率超过20%，可以用 flush query cache 整理缓存碎片，或者试试减小 query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%

查询缓存利用率在 25 %以下的话说明 query_cache_size 设置过大，可以适当减小：查询缓存利用率在 80% 以上而且Qcache_lowmem_prunes > 50 的话说明 query_cache_size 可能有点小，要不就是碎片太多

查询缓存命中率：Qcache_hits / (Qcache_hits + Qcache_inserts) * 100%

Query Cache的限制

所有子查询中的外部查询 SQL 不能被 Cache在 procedure，function 以及 trigger 中的 Query 不能被 Cache包含其他很多每次执行可能得到不一样的结果的函数的 Query 不能被 Cache

2.1.2.3. Analyzer 分析器

如果查询缓存未命中，就要开始执行语句了。首先，MySQL 需要对 SQL 语句进行解析。

词法分析

SQL语句是由多个字符串和空格组成的，MySQL 需要识别出里面的字符串所代表的含义，哪些是空串，哪些是注释，哪些是表名，哪些是字段等等。

语法分析

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这SQL语句是否满足 MySQL 语法。如果你输入的 SQL 语句有问题，就会收到 You have an error in your SQL syntax 的错误提醒，比如下面这个语句 from 写成了 form。

2.1.2.4. Optimizer 优化器：查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且由优化器将其转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果, MySQL 使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。

在 MySQL 可以通过查询当前会话的 last_query_cost 的值来得到其计算当前查询的成本。

mysql> SELECT * FROM b_product WHERE total_price BETWEEN 580000 AND 680000;mysql> SHOW STATUS LIKE "last_query_cost"; +-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| Last_query_cost | 77.973000 |+-----------------+-----------+1 row in set (0.00 sec)

复制

有非常多的原因会导致 MySQL 选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本(用户自定义函数、存储过程)、MySQL 认为的最优跟我们想的不一样，我们希望执行时间尽可能短，但 MySQL 值选择它认为成本小的，但成本小并不意味着执行时间短。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划 :

在表里面有多个索引的时候，决定使用哪个索引；重新定义表的关联顺序 (多张表关联查询时，并不一定按照 SQL 中指定的顺序进行，但有一些技巧可以指定关联顺序)优化 MIN() 和 MAX()函数 (找某列的最小值，如果该列有索引，只需要查找 B+Tree索引 最左端，反之则可以找到最大值)提前终止查询 (比如 : 使用 Limit 时，查找到满足数量的结果集后会立即终止查询)优化排序 (在老版本 MySQL 会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多)

比如你执行下面这样的语句，这个语句是执行两个表的 join：

mysql> SELECT * FROM order_master JOIN order_detail USING (order_id) WHERE order_master.pay_status = 0 AND order_detail.detail_id = 1558963262141624521;

复制

既可以先从表 order_master 里面取出 pay_status = 0 的记录的 order_id 值，再根据 order_id 值关联到表 order_detail，再判断 order_detail 里面 detail_id 的值是否等于 1558963262141624521。

也可以先从表 order_detail 里面取出 detail_id = 1558963262141624521 的记录的 order_id 值，再根据 order_id 值关联到 order_master，再判断 order_master 里面 pay_status 的值是否等于 0。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。优化器阶段完成后，这个语句的执行方案就确定下来了，然后进入执行器阶段。

2.1.2.5. 查询执行引擎 Actuator

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为 handler API。查询过程中的每一张表由一个 handler 实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个 handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作

开始执行SQL语句:

mysql> select * from user_info  where id = 1;

复制

判断是否有查询权限有就继续执行没有就返回权限错误。

例如判断当前连接对这个表 user_info 有没有执行查询的权限，如果没有，就会返回没有权限的错误。错误如下（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

执行器根据表的引擎定义去掉用引擎接口

如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。对于没有有索引的表使用全表扫描API。返回后，调用引擎接口取下一行，重复相同的判断逻辑，直到取到这个表的最后一行。执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

全表扫描接口：

//初始化全表扫描virtual int rnd_init (bool scan);//从表中读取下一行virtual int rnd_next (byte* buf);

复制

对于有索引的表，使用索引相关接口：

第一次调用读取索引第一条内容接口（ha_index_first）。之后循环取满足索引条件的下一行接口（ha_index_next）。

通过索引访问接口：

//使用索引前调用该方法int ha_foo::index_init(uint keynr, bool sorted)//使用索引后调用该方法int ha_foo::index_end(uint keynr, bool sorted)//读取索引第一条内容int ha_index_first(uchar * buf);//读取索引下一条内容int ha_index_next(uchar * buf);//读取索引前一条内容int ha_index_prev(uchar * buf);//读取索引最后一条内容int ha_index_last(uchar * buf);//给定一个key基于索引读取内容int index_read(uchar * buf, const uchar * key, uint key_len,  enum ha_rkey_function find_flag)

复制

2.1.2.6. 返回结果给客户端

查询执行的最后一个阶段就是将结果返回给客户端。即使查询不到数据，MySQL 仍然会返回这个查询的相关信息，比如该查询影响到的行数以及执行时间等。如果查询缓存被打开且这个查询可以被缓存，MySQL也会将结果存放到缓存中。

结果集返回客户端是一个增量且逐步返回的过程。有可能 MySQL 在生成第一条结果时，就开始向客户端逐步返回结果集。这样服务端就无须存储太多结果而消耗过多内存，也可以让客户端第一时间获得返回结果。需要注意的是，结果集中的每一行都会以一个满足客户端/服务器通信协议的数据包发送，再通过 TCP协议 进行传输，在传输过程中，可能对 MySQL 的数据包进行缓存然后批量发送

2.3 存储引擎层

存储引擎层包含一个存储引擎插件管理器以及若干 MySQL 存储引擎。目前使用最多的 MySQL 存储引擎是 InnoDB。

从InnoDB存储引擎的存储结构看，所有数据都被逻辑地放在一个空间中，称之为表空间(tablespace)、段(Segment)、区(extent)、页(page)、行(Row)组成，页在一些文档中也被称之为块(block)。

表空间内，segment , extent 和 page 之间的关系是环环相扣的，其中页(page)是表空间的最小分配单位。一个页(page) 默认大小是16 KB，一个区(extent)管理64个page, 大小为1 MB，而段(Segment)可以管理很多 extent 以及额外的32个碎页(frag page), 碎页是为了节省空间而定义的。

3. MySQL 日志系统

作为存储系统，日志系统是 MySQL 服务的重要组成部分，记录着数据库运行期间各种状态信息。MySQL 日志主要包括错误日志、查询日志、慢查询日志、二进制日志、事务日志几大类。其中除了事务日志位于 MySQL 引擎层外，其他的日志处于 MySQL Server层。

一般数据库开发人员，需要重点关注的是二进制日志( binlog )和事务日志(包括 redo log 和 undo log)，本文接下来会详细介绍这三种日志。

3.2.1 binlog

binlog 又称为归档日志，属于逻辑日志，是以二进制的形式记录的是这个语句的原始逻辑。主要用于复制（Master-Slave 主从同步）、恢复和审计。

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

binlog 是 Server 层实现的，意味着所有引擎都可以使用 binlog 日志binlog 通过追加的方式写入的，可通过配置参数 max_binlog_size 设置每个 binlog 文件的大小，当文件大小大于给定值后，日志会发生滚动，之后的日志记录到新的文件上。

3.2.1.1 binglog 配置

show variables like "%binlog%";+------------------------------------------------+----------------------+| Variable_name                                  | Value                |+------------------------------------------------+----------------------+| binlog_cache_size                              | 32768                || binlog_checksum                                | CRC32                || binlog_direct_non_transactional_updates        | OFF                  || binlog_encryption                              | OFF                  || binlog_error_action                            | ABORT_SERVER         || binlog_expire_logs_auto_purge                  | ON                   || binlog_expire_logs_seconds                     | 2592000              || binlog_format                                  | ROW                  || binlog_group_commit_sync_delay                 | 0                    || binlog_group_commit_sync_no_delay_count        | 0                    || binlog_gtid_simple_recovery                    | ON                   || binlog_max_flush_queue_time                    | 0                    || binlog_order_commits                           | ON                   || binlog_rotate_encryption_master_key_at_startup | OFF                  || binlog_row_event_max_size                      | 8192                 || binlog_row_image                               | FULL                 || binlog_row_metadata                            | MINIMAL              || binlog_row_value_options                       |                      || binlog_rows_query_log_events                   | OFF                  || binlog_stmt_cache_size                         | 32768                || binlog_transaction_compression                 | OFF                  || binlog_transaction_compression_level_zstd      | 3                    || binlog_transaction_dependency_history_size     | 25000                || binlog_transaction_dependency_tracking         | COMMIT_ORDER         || innodb_api_enable_binlog                       | OFF                  || log_statements_unsafe_for_binlog               | ON                   || max_binlog_cache_size                          | 18446744073709547520 || max_binlog_size                                | 1073741824           || max_binlog_stmt_cache_size                     | 18446744073709547520 || sync_binlog                                    | 1                    |+------------------------------------------------+----------------------+

复制

sync_binlog=0 的时候，表示每次提交事务都只写 page cache ,不会持久化到硬盘; sync_binlog=1 的时候，表示每日提交事务之后都会写 page cache，并且持久化到硬盘 ,保证 MySQL 异常重启后 binlog 不丢失； sync_binlog=N 的时候，表示当积累N次事务之后就会一次性写入硬盘。

$cat /etc/my.conf.d/mysql.conf[mysqld]binlog_format = mixed #设置日志格式log-bin = /data/mysql/logs/mysql-bin.log  #设置日志路径，注意路经需要mysql用户有权限写expire_logs_days = 7 #设置binlog清理时间max_binlog_size = 100m #binlog每个日志文件大小binlog_cache_size = 4m #binlog缓存大小max_binlog_cache_size = 512m #最大binlog缓存大小

复制

3.2.1.2 binlog 格式

binlog的格式也有三种：STATEMENT、ROW、MIXED 。

1. STATMENT模式：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。

优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。

缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

2. ROW: 基于行的复制(row-based replication, RBR)：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。

优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。

缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。

3. MIXED混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

3.2.2 undo log

undo log 是一种用于撤销回退的日志，在事务没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log 来进行回退。undo log 主要被用来存储事务中数据更新前的状态，以便回滚和保持其他事务的数据一致性。

undo log 在事务开始前产生；事务在提交时，并不会立刻删除 undo log，innodb 会将该事务对应的 undo log 放入到删除列表中，后面会通过后台线程 purge thread 进行回收处理。undo log 属于逻辑日志，记录一个变化过程。例如执行一个 delete，undo log 会记录一个 insert；执行一个 update，undolog 会记录一个相反的 update。

undo log 采用段的方式管理和记录。在 innodb 数据文件中包含一种 rollback segment 回滚段，内部包含 1024 个 undo log segment。可以通过下面一组参数来控制Undo log存储。

show variables like "%innodb_undo%";+----------------------------------+-------+| Variable_name                    | Value |+----------------------------------+-------+| Innodb_undo_tablespaces_total    | 2     || Innodb_undo_tablespaces_implicit | 2     || Innodb_undo_tablespaces_explicit | 0     || Innodb_undo_tablespaces_active   | 2     |+----------------------------------+-------+4 rows in set (0.01 sec)

复制

3.2.2.1 提供多版本并发控制(MVCC)

MVCC 全称 Multi-Version Concurrency Control，即多版本并发控制。是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

最早的数据库系统，只有读读之间可以并发，读写，写读，写写都要阻塞。引入 MVCC 之后，只有写写之间相互阻塞，其他三种操作都可以并行，这样大幅度提高了数据库的并发度。MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

在 InnDB 的 MVCC 实现中，与 PostgresDB 在数据行上实现多版本不同，是在 undo log 中实现的，通过 undo log 可以找回数据的历史版本。

找回的数据历史版本可以提供给用户读(按照隔离级别的定义，有些读请求只能看到比较老的数据版本)，也可以在回滚的时候覆盖数据页上的数据。

在InnoDB内部中，会记录一个全局的活跃读写事务数组，其主要用来判断事务的可见性。

3.2.3 redo log

redo log 是 InnoDB 引擎特有的物理日志，记录的是数据页的物理修改，即用于记录事务操作的变化，不管事务是否提交都会记录下来。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，InnoDB 存储引擎会使用 redo log 恢复到掉宕机前的时刻，以此来保证数据的完整性。这个能力称为 crash-safe。

由于 redo log 属于物理日志的特性，恢复速度远快于逻辑日志。而 binlog 和 undo log 属于的逻辑日志。

在一条更新SQL语句进行执行的时候，InnoDB 引擎会把更新记录写到 redo log 日志中, 并更新内存，这时更新完成。同时 InnoDB 引擎在适当的时候，将这个操作记录更新到磁盘里。

在MySQL中，每次的更新操作都需要写进磁盘，然后磁盘也要找到对应记录，进行更新，整个过程的IO成本、查找成本都很高。可使WAL(Write-Ahead-Logging)技术，他的关键点是先写日志，再写磁盘。

3.2.3.1 redo log 流程：

提交事务 -> 日志写入relog log buffer -> os buffer -> 写入磁盘的log file -> 根据 checkpoint 更新磁盘中的数据

为了确保每次日志都能写入到事务日志文件中，redo log 写入磁盘时，必须进行一次操作系统 fsync 操作 (即 fsync()系统调用, MySQL是工作在用户空间的，Redo Log buffer 也就处于用户空间的内存中)，防止 redo log 只是写入操作系统磁盘缓存中。参数innodb_flush_log_at_trx_commit 可以控制 redo log 日志刷新到磁盘策略。

innodb_flush_log_at_trx_commit 有3种值：0、1、2，默认为1。但注意，这个变量只是控制commit动作是否刷新log buffer到磁盘。

当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。

I/O Master线程这是InnoDB一个在后台运行的主线程。它做的主要工作包括但不限于：刷新日志缓冲，合并插入缓冲，刷新脏页等。Master线程大致分为每秒运行一次的操作和每10秒运行一次的操作。master thread中刷新数据，属于checkpoint的一种。所以如果在master thread在刷新日志的间隙，DB出现故障那么将丢失掉这部分数据。

3.2.3.2 redo log 配置

以下是通过mysql命令行查看参数：

show variables like "%innodb_log%";+------------------------------------+----------+| Variable_name                      | Value    |+------------------------------------+----------+| innodb_log_buffer_size             | 16777216 || innodb_log_checksums               | ON       || innodb_log_compressed_pages        | ON       || innodb_log_file_size               | 50331648 || innodb_log_files_in_group          | 2        || innodb_log_group_home_dir          | ./       || innodb_log_spin_cpu_abs_lwm        | 80       || innodb_log_spin_cpu_pct_hwm        | 50       || innodb_log_wait_for_flush_spin_hwm | 400      || innodb_log_write_ahead_size        | 8192     || innodb_log_writer_threads          | ON       |+------------------------------------+----------+

复制

innodb_log_buffer_size：log buffer的大小，默认8M

innodb_log_file_size：事务日志的大小，默认5M

innodb_log_files_group： 事务日志组中的事务日志文件个数，默认2个

innodb_log_group_home_dir： 事务日志组路径，当前目录表示数据目录

3.2.3.3 redo log 机制

InnoDB的redo log 是固定大小，即记录满了以后就从头循环写。

图中展示了一组 4 个文件的 redo log 日志，checkpoint 是当前要擦除的位置，擦除记录前需要先把对应的数据落盘（更新内存页，等待刷脏页）。write pos 到 checkpoint 之间的部分可以用来记录新的操作，如果 write pos 和 checkpoint 相遇，说明 redo log 已满，这个时候数据库停止进行数据库更新语句的执行，转而进行 redo log 日志同步到磁盘中。checkpoint 到 write pos 之间的部分等待落盘（先更新内存页，然后等待刷脏页）。

若可配置2个redo log日志文件. 每个文件的大小是256M，总共记录就是512M；参数如下:

innodb-log-files-in-group = 2

innodb-log-file-size = 256M

在innodb将log buffer中的redo log block刷到这些log file中时，会以追加写入的方式循环轮训写入。即先在第一个log file（即ib_logfile0）的尾部追加写，直到满了之后向第二个log file（即ib_logfile1）写。当第二个log file满了会清空一部分第一个log file继续写入。

redo log file的大小对innodb的性能影响非常大，设置的太大，恢复的时候就会时间较长，设置的太小，就会导致在写redo log的时候循环切换redo log file。

4. Mysql 配置

4.1 Mysql 环境变量

mysqld 服务器维护两种变量:

4.1.1. 全局变量影响服务器的全局操作：

服务器启动时，将所有全局变量初始化为默认值。可以在配置文件或命令行中指定的选项来更改这些默认值。服务器启动后，通过连接服务器并执行SET GLOBAL var_name语句可以更改动态全局变量。要想更改全局变量，必须具有SUPER权限。

4.1.2. 会话变量影响具体客户端连接相关操作。

服务器还为每个客户端连接维护会话变量。连接时使用相应全局变量的当前值对客户端会话变量进行初始化。客户可以通过SET SESSION var_name 语句来更改动态会话变量。设置会话变量不需要特殊权限，但客户可以只更改自己的会话变量，而不更改其它客户的会话变量。

任何访问全局变量的客户端都可以看见对全局变量的更改。但是，它只影响在更改后连接的从该全局变量初始化相应会话变量的客户端。它不会影响已经连接上的客户端的会话变量(甚至是执行SET GLOBAL语句的客户端)。

要想显式指定是否设置全局或会话变量，使用GLOBAL或SESSION选项：

mysql> SET GLOBAL sort_buffer_size = 10 * 1024 * 1024;mysql> SET SESSION sort_buffer_size = 10 * 1024 * 1024;

复制

两个选项均没有，则语句设置会话变量。

可以通过SHOW VARIABLES语句查看系统变量及其值。

mysql> SHOW VARIABLES;

复制