MySql-01-一条sql

MySql Comments

MySQL整体架构如下所示，下面将从一个SQL语句的执行来看看它经历了什么

image-20210718145041739

Server层

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接，连接默认端口是3306，可通过修改my.cnf配置文件指定端口

连接命令：mysql -h$ip -P$port -u$user -p

如何查看 MySQL 服务被多少个客户端连接了？

1
2
3
4
5
6
7
8
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User            | Host      | db   | Command | Time | State                  | Info             |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
|  5 | event_scheduler | localhost | NULL | Daemon  |  121 | Waiting on empty queue | NULL             |
|  8 | root            | localhost | NULL | Query   |    0 | init                   | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)

共有两个用户连接了 MySQL 服务，其中 id 为 5 的用户的 Command 列的状态为 Daemon ，意味着该用户是 MySQL守护进程用户，空闲的时长是 121 秒(Time 列)，另外一个就是我登录并执行查询语句

Command：连接的当前命令，可能的取值包括：

  • Sleep：连接处于空闲状态。
  • Query：正在执行查询操作。
  • Connect：连接正在建立中。
  • Execute：正在执行存储过程或函数。
  • Binlog Dump：正在读取二进制日志。
  • Daemon：表示当前连接是一个后台守护进程，通常是 MySQL 服务器的内部线程或任务

如果客户端太长时间没动静，连接器会自动断开。由参数wait_timeout控制的，默认值是8小时(28800秒)

1
2
3
4
5
6
7
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.04 sec)
  1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
  2. 如果你用的是 MySQL 5.7 或更新版本，可在每次执行一个比较大的操作后，通过执 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态
  3. 一个处于空闲状态的连接被服务端主动断开后，kill connection +6; 这个客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到这样的报错ERROR 2013 (HY000): Lost connection to MySQL server during query

MySQL 的连接数有限制吗？

MySQL 服务支持的最大连接数由 max_connections 参数控制，比如我的 MySQL 服务默认是 151 个，超过这个值，系统就会拒绝接下来的连接请求，并报错提示Too many connections。

1
2
3
4
5
6
7
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

怎么解决长连接占用内存的问题？

MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

  1. 定期断开长连接。既然断开连接后就会释放连接占用的内存资源，可以定期断开长连接。
    • 设置wait_timeout自动释放长时间空闲连接
  2. 客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令，那么当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection() 函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

连接建立完成后，就可以执行 select 语句了，执行查询缓存。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中，key 是查询的语句，value 是查询的结果

  • MySql8.0 之后查询缓存被删掉
  • 如果是频繁更新的表格，那么缓存基本没用
1
2
3
4
#关闭缓存
SET GLOBAL query_cache_type = OFF;
#查看缓存是否被禁用
SHOW VARIABLES LIKE 'query_cache_type';

分析器

分析器的作用就是将 Mysql 语句解析成数据库自己认识的样子

第一件事情，词法分析。MySQL 会根据你输入的字符串识别出关键字出来，构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

第二件事情，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

如果我们输入的 SQL 语句语法不对，就会在解析器这个阶段报错

1
2
mysql> show variables likes 'max_connections';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'likes 'max_connections'' at line 1

注意，表不存在或者字段不存在，并不是在分析器里做的

执行SQL

经过分析器后，接着就要进入执行 SQL 查询语句的流程了，每条SELECT 查询语句流程主要可以分为下面这三个阶段：

  • prepare 阶段，也就是预处理阶段；
  • optimize 阶段，也就是优化阶段；
  • execute 阶段，也就是执行阶段；
预处理阶段

预处理阶段将执行

  • 检查 SQL 查询语句中的表或者字段是否存在；
  • 将 select * 中的 * 符号，扩展为表上的所有列；

比如表格不存在

1
2
mysql> select * from test;
ERROR 1046 (3D000): No database selected
优化器

当语句被解析成自己认识的样子之后，并不是立即就执行，而是按照自己认为效率最高的方式去执行，即最优查询路径

  1. 索引使用：如果表中存在适当的索引，则 MySQL 可以使用索引来查找需要更新的行，从而避免全表扫描，提高查询效率。

    要想知道使用哪个索引，可以使用 explain <sql>

    1
    2
    3
    4
    5
    6
    7
    mysql> explain select * from students where id = 1;
    +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    | id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
    +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    |  1 | SIMPLE      | students | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
    +----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
    1 row in set, 1 warning (0.00 sec)

  2. 优化 WHERE 子句：WHERE 子句用于指定需要更新的行。MySQL 优化器会根据 WHERE 子句的复杂度和索引情况来选择最优的查询路径。

  3. 最小化日志写入：MySQL 的 InnoDB 存储引擎支持事务和回滚机制，更新操作会生成事务日志，影响性能。优化器会尝试最小化日志写入，提高性能。

  4. 减少锁的使用：更新操作可能会引起表锁或行锁，锁的使用会对性能产生负面影响。优化器会尝试减少锁的使用

执行器

当优化器选出最优索引等步骤后，会去调用存储引擎接口，开始执行被MySQL解析过和优化过的SQL语句，用三种方式执行过程

  • 主键索引查询
  • 全表扫描
  • 索引下推
主键索引查询
1
2
3
4
5
6
7
mysql> explain select * from students where id = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

查询条件用到主键索引，而且是等值查询，同时主键 id 是唯一，不会有 id 相同的记录，所以优化器决定选用访问类型为 const 进行查询，也就是使用主键索引查询一条记录，那么执行器与存储引擎的执行流程是这样的：

  • 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录。
  • 存储引擎通过主键索引的 B+ 树结构定位到 id = 1的第一条记录，如果记录是不存在的，就会向执行器上报记录找不到的错误，然后查询结束。如果记录是存在的，就会将记录返回给执行器；(符合索引的记录)
  • 执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，否则跳过该记录。(符合条件的记录)
全表扫描
1
2
3
4
5
6
7
8
9
select * from product where name = 'iphone';

mysql> explain select * from students where name = 'iphone';
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为 ALL 进行查询，也就是全表扫描的方式查询，那么这时执行器与存储引擎的执行流程是这样的：

  • 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；
  • 执行器会判断读到的这条记录的 name 是不是 iphone，如果不是则跳过；如果是则将记录发给客户的（是的没错，Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）。
  • 执行器查询的过程是一个 while 循环，因为优化器选择的访问类型为 all，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器(Server层)，执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；
  • 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器(Server层) 返回了读取完毕的信息；
  • 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。
索引下推

索引下推能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select * from students;
+----+------------+------+------------+-------+
| id | name       | age  | student_id | score |
+----+------------+------+------------+-------+
|  1 | John Doe   |   20 | 2021001    |    20 |
|  2 | Jane Smith |   22 | 2021002    |    95 |
|  3 | Alice      |   20 | 1          |    16 |
|  4 | Bob        |   22 | 2          |    91 |
|  5 | Charlie    |   21 | 3          |     9 |
|  6 | David      |   19 | 4          |    73 |
|  7 | Emma       |   23 | 5          |    34 |
|  8 | Frank      |   20 | 6          |    53 |
|  9 | Grace      |   22 | 7          |    63 |
| 10 | Henry      |   21 | 8          |    54 |
+----+------------+------+------------+-------+
10 rows in set (0.00 sec)

执行如下的sql语句

1
2
3
4
5
6
7
mysql> explain SELECT * FROM students FORCE INDEX (idx_age_score) WHERE age > 19 AND score = 53;
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | students | NULL       | range | idx_age_score | idx_age_score | 5       | NULL |    9 |    10.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

age 字段能用到联合索引，但是 reward 字段则无法利用到索引，可以在 索引 查看原因。在不使用索引下推(MySQL 5.6 之前的版本）时，执行器与存储引擎的执行流程是这样的：

  • Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，定位到 age > 19 的第一条记录；
  • 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
  • Server 层在判断该记录的 score 是否等于 53，如果成立则将其发送给客户端；否则跳过该记录；
  • 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；
  • 如此往复，直到存储引擎把表中的所有记录读完。

没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server，接着 Server 再判断该记录的 score 是否等于 53。而使用索引下推后，判断记录的 score 是否等于 53 的工作交给了存储引擎层，过程如下 ：

  • Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 19 的第一条记录；
  • 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列(score列）的条件（score 是否等于 53）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。
  • Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。
  • 如此往复，直到存储引擎把表中的所有记录读完。

使用了索引下推后，虽然 score 列无法使用到联合索引，但因为它包含在联合索引(age，score)里，所以直接在存储引擎过滤出满足 score = 53 的记录后，才去执行回表操作获取整个记录。相比于没有使用索引下推，节省了很多回表操作。如果 Extr 部分显示了 “Using index condition”，说明使用了索引下推。

这里强制使用联合索引实现索引下推，MySQL 优化器根据查询的复杂度、数据分布、索引统计信息等因素决定执行计划

bin log

MySQL的二进制日志bin log是服务层文件而不是存储引擎，记录数据库事务的二进制文件，它可以用于数据恢复、复制和备份等操作。在MySQL中，bin log文件的大小是由max_binlog_size参数来控制的，默认值为1073741824(即1GB)

1
2
3
4
5
6
7
mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.02 sec)

既然是文件，就会有以下问题

  1. 文件的作用
  2. 文件路径与大小
  3. 文件的内容
  4. 文件是如何清理的
  5. 文件是如何写入的

接下来一一解答：

内容

MySQL 在完成一条更新操作后，Server 层还会生成一条 binlog，等之后事务提交的时候，会将该事物执行过程中产生的所有 binlog 统一写入 binlog 文件，以下是会被写入二进制日志 binlog 的一些操作：

  1. 数据更改操作(DML)：包括插入(INSERT)、更新(UPDATE)、删除(DELETE)等操作。以便进行数据恢复或复制。
  2. 数据定义操作(DDL)：某些数据定义语句也会被写入二进制日志，如创建表(CREATE TABLE)、修改表结构(ALTER TABLE)、删除表(DROP TABLE)等。记录数据库结构的更改。
  3. 事务控制语句：事务的开始(BEGIN、START TRANSACTION)、提交(COMMIT)和回滚(ROLLBACK)，以便进行事务的恢复和复制。
  4. 系统变量修改：一些修改数据库全局或会话级别系统变量的语句(如 SET GLOBAL 和 SET SESSION)也会被写入二进制日志。

那么不会写入的就有

  1. 查询语句(SELECT)：由于查询语句不会对数据进行修改，因此没必要将其记录到二进制日志中。
  2. 系统函数调用：对于仅调用系统函数的操作，例如获取当前时间或执行数学运算等，通常不会被写入二进制日志。
  3. 非事务性操作：对于没有显式或隐式事务包装的操作，例如单个的插入、更新或删除语句，如果没有事务的参与，它们可能不会被写入二进制日志。暂时不太理解
  4. 临时表的数据更改：对于临时表的数据更改，如果没有启用 binlog 的 row 格式，这些操作可能不会被写入二进制日志。
格式

这里说到的binlog的格式，其实就是binlog记录的内容的格式

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

    • 不需要记录每一行的变化，减少了 bin log 日志量，节约了 IO , 从而提高了性能
    • 在某些情况下会导致主从数据不一致，比如执行sysdate()、sleep()等

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

    • 不会出现某些特定情况下的存储过程、或 function、或 trigger 的调用和触发无法被正确复制的问题
    • 会产生大量的日志，尤其是 alter table 的时候会让日志暴涨

  3. MIXED：基于 STATMENT 和 ROW 两种模式的混合复制( mixed-based replication, MBR )，一般的复制使用 STATEMENT 模式保存 bin log ，对于 STATEMENT 模式无法复制的操作使用 ROW 模式保存 bin log

修改MySQL的binlog格式步骤

  1. 打开MySQL配置文件（my.cnf或my.ini）。
  2. 定位到[mysqld]部分，该部分包含MySQL服务器的全局配置。
  3. 添加或修改 binlog_format
  4. 保存并关闭配置文件。
  5. 重启MySQL服务器，以使配置更改生效
写入时机

bin log 的刷盘策略通过sync_binlog来修改，默认为 0，表示先写入 os cache，

1
2
3
4
5
6
7
mysql> show variables like 'sync_binlog';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)
  • 0 表示在提交事务的时候，数据不会直接到磁盘中。如果宕机，bin log数据仍然会丢失。
  • 1表示每次事务提交时，数据会写入磁盘并刷新到持久化存储设备。这确保了在事务提交后，二进制日志的内容已经持久化，并且可以用于数据恢复和复制。

sync_binlog仅控制二进制日志的刷新策略，并不影响事务的持久性。事务的持久性仍然由存储引擎和文件系统的设置决定 事务

文件过大

当binlog文件过大时，可能会导致存储空间不足或者文件系统的限制，解决方案：

  1. 改变binlog文件路径：可以将binlog文件存储在具有更大空间的磁盘分区上，或者使用分布式文件系统来扩展存储容量

    • log_bin：指定binlog文件的基本名称，不包括文件扩展名。默认情况下，它的值为mysql-bin。例如，如果设置为mysql-bin，则生成的binlog文件名为mysql-bin.000001、mysql-bin.000002等。

    • log_bin_index：指定binlog索引文件的名称。默认情况下，它的值为与log_bin参数相同的基本名称，附加.index扩展名。例如，如果log_bin为mysql-bin，则索引文件名为mysql-bin.index。

    • datadir：指定MySQL数据目录的路径，即数据库文件所在的目录。binlog文件默认存储在数据目录下的data子目录中

  2. 压缩binlog文件：使用压缩算法对binlog文件进行压缩，以减少文件的大小。通过设置binlog_compression参数启用压缩。

  3. 定期删除旧的binlog文件：可以通过定期清理和删除旧的binlog文件来释放空间。可以使用PURGE BINARY LOGS语句删除过期的binlog文件。

    1
    2
    3
    4
    #删除早于xxx时间文件
    PURGE BINARY LOGS BEFORE '2022-01-01 00:00:00';
    #删除指定binlog文件
    PURGE BINARY LOGS TO 'mysql-bin.000003';

  4. 调整binlog文件的生命周期：可以通过调整expire_logs_days参数来限制binlog文件的保留时间。将其设置为较短的值可以减少文件的积累。

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'expire_logs_days';
    +------------------+-------+
    | Variable_name    | Value |
    +------------------+-------+
    | expire_logs_days | 0     |
    +------------------+-------+
    1 row in set (0.00 sec)

    0 表示不会自动删除binlog日志

  5. 启用binlog文件的循环复用：可以使用log_bin_trust_function_creators参数启用binlog文件的循环复用。当binlog文件达到最大大小限制时，会重新使用最旧的文件，以避免无限增长。

    1
    2
    3
    4
    5
    6
    7
    mysql> show variables like 'log_bin_trust_function_creators';
    +---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | log_bin_trust_function_creators | OFF   |
    +---------------------------------+-------+
    1 row in set (0.00 sec)

说完了binlog，那么接着上面一条语句的执行，以Innodb为例来说明接下来的操作

bin log 并不属于存储引擎，与存储引擎的 redo log 是有区别的：

性质 redo log bin log
文件大小 redo log 的大小是固定的（配置中也可以设置，一般默认的就足够了） bin log 可通过配置参数max_binlog_size设置每个bin log文件的大小
实现方式 redo log是InnoDB引擎层实现的（也就是说是 Innodb 存储引擎独有的） bin log是 MySQL 层实现的，所有引擎都可以使用 bin log日志
记录方式 redo log 采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。 bin log通过追加的方式记录，当文件大小大于给定值后，后续的日志会记录到新的文件上
使用场景 redo log适用于崩溃恢复(crash-safe) bin log适用于主从复制和数据恢复

Inndb 存储引擎

到了存储引擎就到了实际与磁盘打交道的地方，如果在写入过程中出现崩溃，那么就会出现：

  1. 未提交事务，写入后崩溃(比如修改三个数据，程序还没修改完，但数据库已经将其中一个或两个数据的变动写入磁盘，此时出现崩溃)
  2. 已提交事务，写入前崩溃(程序已经修改完三个数据，但数据库还未将全部三个数据的变动都写入磁盘，此时出现崩溃）

由于写入中间状态与崩溃都无法避免，只能在崩溃恢复后采取补救措施，于是就提出来写日志的方式。但这里又有问题，在提交事务写写日志还是在在提交之后写日志？

如果所有对数据的真实修改都必须发生在事务提交之后，即成功写入日志之后。在此之前，即使磁盘IO有足够的空闲，即使某个事务修改的数据量非常庞大，占用了大量的内存缓冲区，都不允许在事务提交前写入磁盘，因此这种方式对数据库性能的提升十分不利。提出了“提前写入日志(Write-Ahead Logging)的日志改进方案，允许在事务提交之前写入变动数据的意思

而对于提前写入磁盘，在数据库崩溃后需要回滚的数据，给出的解决办法是增加另外一种被称为Undo Log的日志类型，当变动数据写入磁盘前，必须先记录Undo Log，以便在事务回滚或者崩溃恢复时根据Undo Log对提前写入的数据变动进行擦除。

redo log

知道了redo log日志，接下来来看看它是如何解决崩溃的

首先了解一个东西叫 redo log buffer，也就是该文件缓存用来加快IO，既然是文件缓存就会有刷盘策略

  • 延迟写(Delayed Write): 在延迟写策略下，当事务提交时，相关的redo log记录会先写入redo log buffer，而不是立即写入磁盘的redo log文件。只有在满足一定条件时，才会将redo log buffer中的日志刷写到磁盘。这种策略可以提高事务的执行性能，减少磁盘IO操作的频率。常见的条件包括事务提交频率、日志量大小等。
  • 异步刷新（Asynchronous Flushing）：在异步刷新策略下，当事务提交时，相关的redo log记录会先写入redo log buffer，并触发一个后台线程，负责将redo log buffer中的日志异步刷新到磁盘的redo log文件中。这个刷新操作不会阻塞事务的提交，提高了事务的并发性能。异步刷新策略通常通过一定的策略控制，例如设置合适的刷新间隔、刷新量、I/O线程的数量等。

刷盘策略配置

  • innodb_flush_log_at_trx_commit默认值为1
    • 0：不进行刷盘操作，性能最高，但存在数据丢失的风险，使用异步刷新。
    • 1(默认)：每次事务提交时立即刷写到磁盘，最安全，但性能较低。
    • 2：每次事务提交时将日志写入操作系统缓存，但不等待刷写到磁盘，性能较高，但存在数据丢失的风险。
  • innodb_flush_log_at_timeout：用于控制异步刷新策略的超时时间。
    • 当innodb_flush_log_at_trx_commit设置为2时，该参数指定了多久后如果没有新的事务提交，则将日志刷写到磁盘，默认值为1秒

提交事务成功的标志是什么？指缓存 redo log buffer 刷入到了磁盘中？

答：只有当 redo log 完全持久化到磁盘中之后，MySQL才会将该事务标记为以提交，这个过程称为 redo log 的持久化

如果存在多个事务，是否存在多个redo log buffer ？

答：每个数据库实例，只有一个 redo log buffer

redo log 是固定大小的，通常以ib_logfile0、ib_logfile1等文件名的形式存在于数据目录下。每个redo log文件的大小由MySQL配置参数innodb_log_file_size控制，默认大小为48MB。比如一组 4 个文件，每个文件的大小是 48MB，总共就可以记录 216MB 的操作

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+
1 row in set (0.10 sec)

从头开始写，写到末尾就又回到开头循环写，如下面这个图所示

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

在一个类似环的地方，write pos 会不断的记录新的操作，而 checkpoint 在后面不断的追赶(将记录更新到数据文件)。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe

当 write pos 追上 checkpoint(redo log满了)怎么办? 为了避免Redo Log被写满，MySQL会暂停新的写入操作，直到有足够的空间可用

有了 redo log 日志之后， update 的逻辑是怎样的？

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

所以更新操作并不是把某条记录查询到内存中对其做修改就行，而是将对应记录所在页都加载到内存中，然后通过执行器(是服务层而不是直接由引擎直接更新的！！！)，这里分为了两阶段提交

  • 如果先写 redo log 后写 binlog。假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。这时候 binlog 里面就没有记录这个语句。如果用这个 binlog 恢复临时库的话，就会少了这一次更新
  • 如果先写 binlog 后写 redo log。如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。

所以，为了保证事务分为三步操作：

  1. redo log 的 prepare阶段
  2. 写binlog 阶段
  3. redo log 的 commit 阶段

当在第2步之前崩溃时，重启恢复后发现没有commit，回滚，备份恢复没有binlog

当在第3步之前崩溃时，重启恢复后发现没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份恢复有binlog

既然bin log就有所有的操作，不能解决崩溃问题，为什么还要redo log?

  • 假设先提交事务再写binlog，可能事务提交数据更新之后数据库崩了，还没来得及写binlog。binlog进行主从复制数据不一致
  • 假设先写binlog再提交事务更新数据库，有可能写binlog成功之后数据库崩掉而导致数据库更新失败，这样也会导致主从数据库不一致或者无法恢复数据库。

bin log 只记录逻辑操作，并无操作状态，即无法确定该操作是否完成。redo log是有状态的，不会直接检查binlog。只有在redo log状态为prepare时，才会去检查binlog是否存在，否则只校验redo log是否是 commit 就可以了。

buffer Pool

在上一节中提到，查询与更新都会先查询一下缓存，判断是否存在数据，如果没有则将磁盘数据页加载到内容中，而这个内存也是一个很重要的InnoDB 组件 buffer Pool

img

查看缓冲池大小

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.03 sec)

undo log

记录语句执行前的记录值

将数据加载到 Buffer Pool 的同时还会往 undo log 中插入一条日志，也就是将 id = 1 原来的值记录下来

img

结合bin log，MySQL 在提交事务的时候，不仅会将 redo log buffer 中的数据写入到 redo log 文件中，也会将本次修改的数据记录到 bin log 文件中，同时会将本次修改的bin log文件名和修改的内容在bin log中的位置记录到redo log中，最后还会在redo log写入 commit 标记，表示本次事务被成功的提交了

  1. 准备更新一条 SQL 语句

  2. MySQL(innodb)会先去缓冲池 BufferPool 中去查找这条数据，没找到就会去磁盘中查找，如果查找到就会将这条数据加载到缓冲池 BufferPool 中

  3. 在加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo log 文件中

  4. innodb 会在 Buffer Pool 中执行更新操作

  5. 更新后的数据会记录在 redo log buffer 中

  6. MySQL 提交事务的时候，会将 redo log buffer 中的数据写入到 redo log 文件中，刷磁盘可以通过 innodb_flush_log_at_trx_commit 参数来设置

    • 值为 0 表示不刷入磁盘

    • 值为 1 表示立即刷入磁盘

    • 值为 2 表示先刷到 os cache

  7. myslq 重启的时候会将 redo 日志恢复到缓冲池中

img

MySQL有一个后台线程，在某个时机将 Buffer Pool 中的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了。

img

参考链接

  1. https://pdai.tech/md/db/sql-mysql/sql-mysql-execute.html
  2. https://xiaolincoding.com/mysql/transaction/mvcc.html#读提交是如何工作的
  3. https://time.geekbang.org/column/article/68963