MySQL 中的常见日志类型

错误日志（Error Log）

基本概念

记录 MySQL 服务器启动、运行和关闭过程中的问题

详细特点

• 默认启用，无法关闭
• 记录内容：启动/关闭信息、关键警告、错误消息
• 默认位置：数据目录下的 hostname.err 文件
• 配置参数：log_error

应用场景

• 诊断服务器崩溃原因
• 监控 MySQL 的运行状态
• 识别配置问题和安全风险

二进制日志（Binary Log）

基本概念

记录所有更改数据的 SQL 语句，以事件形式存储

详细特点

• 记录类型：基于语句（statement）、基于行（row）或混合模式

• 写入时机：在事务完成后才进行写入，如果事务中途崩溃，且事务尚未提交，则不会记录

• 事务安全：通过两阶段提交与 InnoDB 存储引擎协同

  两阶段提交

  1. 第一阶段：准备阶段（Prepare Phase）

     1. 写入 redo log：事务执行过程中，所有修改先行记录到内存中的 redo log buffer
     2. 刷盘操作：将 redo log buffer 内容刷新到磁盘上的 redo log 文件（==持久化==）
     3. 标记状态：将 redo log 标记为 prepare 状态
     4. 等待确认：此时事务尚未提交完成，修改对其他会话不可见

  2. 第二阶段：提交阶段（Commit Phase）

     1. 写入 binlog：将事务的 binlog 写入到磁盘

     2. redo log 提交：在 bin log 写入成功后，将 redo log 状态从 prepare 改为 commit

     3. 完成提交：事务提交完成，修改对其他会话可见

        ​ 崩溃恢复机制

  ---

  当 MySQL 服务器崩溃后重启时，会进行崩溃恢复，检查 redo log：

  1. 只有 prepare 记录，无 bin log 记录：事务回滚
  2. 有 prepare 记录，且有对应的 bin log 记录：事务提交
  3. redo log 已经提交：事务已完成，无需特殊处理

• 格式：二进制格式，非文本

• 追加写入：一个文件写满后创建新文件

• 配置参数：log_bin、binlog_format、sync_binlog等

应用场景

• ==主从复制的数据源==
• 时间点恢复（point-in-time recovery）
• 审计数据变更

重做日志（Redo Log）

基本概念

InnoDB存储引擎特有的物理日志，记录页面的物理更改

详细特点

• 循环写入：固定大小的文件组，写满后覆盖最早的记录

• 物理日志：记录对哪个数据页做了什么修改

• WAL机制：先写日志，再修改数据页

  WAL（Write-Ahead Logging）机制

  这是数据库系统中广泛应用的一种日志机制，它是事务性数据库（如InnoDB）实现持久性和原子性的关键技术

  基本原理

  WAL机制的核心原则是：数据修改前必须先写日志

  1. “写前”承诺：任何数据页的修改必须先将这些修改记录到持久化的日志中
  2. 延迟数据写入：任何数据页的修改可以稍后进行
  3. 顺序写入日志：日志文件采用顺序追加方式写入，性能远高于随机写入

  工作流程

  1. 生成日志记录：当事务需要修改数据时，生成WAL记录（在MySQL的InnoDB中就是redo log记录）
  2. 写入日志缓冲：先将日志记录写入内存中的日志缓冲区
  3. 持久化日志：在特定实际（如事务提交时），确保日志记录持久化到磁盘
  4. 延迟数据写入：真正的数据页修改可以在后台异步完成
  5. 检查点处理：定期创建检查点，将脏页刷新到磁盘

  WAL的优势

  1. 提高性能：
     1. 日志时顺序写入的，比随机IO快得多
     2. 多个事务的日志可以批量写入
     3. 多次修改同一数据页只需一次物理写入
  2. 保证数据安全：
     1. 即使系统在数据页写入前崩溃，也可以通过重放日志恢复
  3. 支持高并发：
     1. 日志先行减少了对数据页的争用批量处理提高了整体吞吐量

  在MySQL InnoDB中的实现

  在MySQL的InnoDB存储引擎中，WAL机制通过redo log实现：

  1. redo log buffer：内存中的缓冲区
  2. redo log files：固定大小的循环磁盘日志文件组
  3. innodb_flush_log_at_trx_commit参数控制日志刷新策略

  WAL与事务处理

  WAL是ACID中持久性(Durability)的关键实现机制，通过确保所有修改在真正应用到数据文件之前，都已经安全记录在日志中，从而在系统崩溃后能够恢复到一致状态。

  这种”先写日志，后写数据”的模式是现代高性能数据库系统的基石，在保证数据安全的同时显著提升了写操作性能。

• 事务提交时刷新到磁盘

• 配置参数：innodb_log_file_size、innodb_log_files_in_group等

应用场景

• 崩溃恢复：系统意外关闭后的数据恢复
• 提高写入性能：批量延迟写入磁盘
• 事务持久性保证

撤销日志（Undo Log）

基本概念

InnoDB特有的逻辑日志，记录事务修改前的数据状态。

详细特点

1. 主要存储于系统表空间或独立Undo表空间
2. 提供数据的前镜像(before image)
3. 支持事务的原子性和隔离性
4. MVCC机制的基础
5. 配置参数：innodb_undo_directory、innodb_undo_logs等

应用场景

1. 事务回滚操作

2. 实现多版本并发控制(MVCC)

   多版本并发控制(MVCC, Multi-Version Concurrency Control)

   一、MVCC的核心思想

   为每个事务提供数据的“快照”（Snapshot）。当一个事务读取数据时，它看到的是某个时间点的数据版本，而不是其他事务正在修改的版本。

   • 读操作不会阻塞写操作
   • 写操作也不会阻塞读操作
   • 每次写入新数据时，并不是修改原有的数据记录，而是生成一个新的版本
   • 旧版本数据用于满足并发事务的读请求

   二、MVCC的实现机制（以 InnoDB 引擎为例）

   MVCC 通常基于以下几个字段实现：

   隐藏字段

   每行数据都会隐藏地维护两个版本号：

   • trx_id（创建版本）：标识插入该记录的事务ID
   • roll_pointer：指向旧版本的回滚日志，用于构建数据的历史版本
   • 有些实现（如PostgreSQL）会显式维护xmin和xmax

   undo log（回滚日志）

   记录旧版本的数据。写操作发生时会写入 undo log，通过它可以回溯出历史版本。

   Read View（读视图）

   • 当事务开始时，会生成一个Read View，记录当前活跃的事务ID列表

   • 用于判断某行记录的版本对当前事务是否“可见”

     • 什么是Read View（读视图）

       Read View 是 MVCC（多版本并发控制）机制中一个非常核心的概念，尤其在像 MySQL 的 InnoDB 存储引擎中被明确使用和讨论。它本质上是事务在执行查询时，用来判断哪些数据版本（行记录）对当前事务是可见的一个数据结构或机制。

       可以把 Read View 理解为事务在某一时刻给整个数据库拍下的一个“快照”的可见性规则定义。

     • Read View 的关键组成部分（以 InnoDB 为例）

       • m_ids (或 trx_ids): 一个列表或集合，包含在创建此 Read View 时，系统中所有**活跃（未提交）**的读写事务的 ID。

       • m_low_limit_id (或 up_limit_id): 创建此 Read View 时，系统中已分配的下一个事务 ID。也就是说，大于或等于这个 ID 的事务，都是在 Read View 创建之后才开始的。

       • m_high_limit_id (或 low_limit_id): m_ids 列表中事务 ID 的最小值。也就是创建此 Read View 时，系统中最早启动的那个未提交事务的 ID。

       • m_creator_trx_id: 创建这个 Read View 的事务自身的 ID。

     • 可见性判断规则

       1. 判断 row_trx_id 是否是创建者自身的 trx_id？

       • 如果是 (row_trx_id == m_creator_trx_id)，则该版本可见。（事务总能看到自己的修改，无论是否提交）。

       2. 判断 row_trx_id 是否小于 m_high_limit_id？

       • 如果是 (row_trx_id < m_high_limit_id)，则说明创建该版本的事务在 Read View 创建时已经提交了，该版本可见。

       3. 判断 row_trx_id 是否大于或等于 m_low_limit_id？

       • 如果是 (row_trx_id >= m_low_limit_id)，则说明创建该版本的事务在 Read View 创建时尚未开始，该版本不可见。

       4. 判断 row_trx_id 是否在 m_ids 列表中？

       • 如果在 (row_trx_id is in m_ids)，则说明创建该版本的事务在 Read View 创建时还是活跃（未提交）状态，该版本不可见。
       • 如果不在，则说明创建该版本的事务在 Read View 创建时已经提交了（虽然它可能是在 m_high_limit_id 之后开始的，但在 Read View 创建前就结束了），该版本可见。

     • READ COMMITTED(读已提交) 和 REPEATABLE READ(可重复读) 的区别

       它们之间的区别主要体现在Read View的创建时机，具体来说：

       • 读已提交在每个SELECT语句执行前都会创建一个新的Read View，这意味着在一个事务内部，后续的查询可以看到其他事务在此期间提交的修改，从而导致不可重复读问题

       • 可重复读在事务中的仅在第一个 SELECT 语句执行时创建一次，并且在整个事务期间都复用这个 Read View，从而避免了不可重复读和大部分幻读

3. 支持READ COMMITTED、REPEATABLE READ等隔离级别

通用查询日志（General Query Log）

基本概念

记录服务器接收到的每一个SQL语句，以及客户端的连接和断开信息；

详细特点

记录内容

1. 记录客户端连接服务器的时间
2. 记录客户端断开连接的时间
3. 记录客户端发送给服务器的每一个SQL语句（无论是否执行）

配置文件参数

1. general_log：ON/OFF，启用/禁用
2. general_log_file：指定日志文件的路径和名称
3. log_output：TABLE, FILE, NONE，指定日志输出目的地，可以同时输出到文件和mysql.general_log表

应用场景

主要用于调试和审计，例如追踪某个特定时间点执行了哪些查询，或者找出是哪个应用发送了错误的 SQL。

注意事项

1. 对服务器性能影响较大，因为它记录所有查询，会产生大量 I/O。

2. 日志文件会迅速增长，占用大量磁盘空间。

3. 通常只在需要调试问题时短暂开启，问题解决后立即关闭。

慢查询日志（Slow Query Log）

基本概念

用于记录执行时间超过指定阈值的 SQL 查询。

详细特点

记录内容

1. 执行时间超过 long_query_time 秒的 SQL 语句。
2. 可选地，还可以记录没有使用索引的查询 (通过 log_queries_not_using_indexes 参数控制)。
3. 查询执行的用户、主机、时间、锁定时间、发送的行数、检查的行数等。

配置文件中参数

1. slow_query_log：ON/OFF，启用或禁用
2. slow_query_log_file：指定日志文件的路径和名称
3. long_query_time：定义“慢”查询的阈值，单位秒，可以有小数
4. log_queries_not_using_indexes：ON/OFF，是否记录未使用索引的查询，即使它们执行时间不长
5. log_output：TABLE, FILE, NONE，同通用查询日志
6. min_examined_row_limit：查询检查的行数少于此值，即使超过 long_query_time 也不记录

应用场景

性能优化的关键工具，用于找出系统中效率低下的 SQL 语句，以便进行优化（如添加索引、改写查询等）

注意事项

对性能的影响相对较小（取决于long_query_time的设置和慢查询的数量），建议在生产环境中长期开启