1、【跟Mic学Java】Mysql 的binlog 有几种格式？听完茅塞顿开，原来面试题应该这么回答_哔哩哔哩_bilibili

# MySQL binlog格式详解：Statement、Row和Mixed三种格式对比

## 引言

一个工作了**两年**的粉丝去面试，被问到**binlog**的格式问题。

这个问题其实一般情况下，**开发人员**的关注度会比较少。以后在涉及到**数据同步、主从同步**的时候，才会去关注到。

不过这个问题在我整理的**大厂后端面试指南**里面是有的。还可以在评论区的置顶中去领取。

## 一、binlog格式概述

### 1.1 什么是binlog？

**binlog（Binary Log）**是MySQL的二进制日志文件，用于记录所有对数据库的修改操作。

**binlog的作用：**
- **主从复制**：用于主从数据库之间的数据同步
- **数据恢复**：用于数据库的增量备份和恢复
- **数据同步**：用于数据同步到其他系统

### 1.2 binlog格式类型

**准确来说，binlog有三种格式：Statement、Row和Mixed。**

## 二、Statement格式

### 2.1 Statement格式的特点

**Statement是记录的是SQL的原文。**

**Statement格式示例：**

```sql
-- binlog中记录的是SQL语句本身
UPDATE users SET balance = 1000 WHERE id = 1;
INSERT INTO orders (user_id, amount) VALUES (1, 100);
DELETE FROM users WHERE id = 2;
```

### 2.2 Statement格式的优点

**它的好处是：**

1. **不需要记录每一行的变化**
   - 只记录SQL语句本身
   - 不记录具体的数据变化

2. **减少了binlog的日志量**
   - 日志文件较小
   - 存储空间占用少

3. **节约了IO，同样提升了性能**
   - 写入binlog的数据量少
   - IO开销小
   - 性能好

### 2.3 Statement格式的缺点

**但是呢，因为SQL的执行是有上下文的。因此在保存的时候，需要保存相关的一些信息。**

**同时还有一些使用函数之类的语句是无法被记录复制的。**

**问题场景：**

```sql
-- 问题1：上下文依赖
SET @var = 1;
UPDATE users SET balance = balance + @var WHERE id = 1;
-- Statement格式无法记录变量的值

-- 问题2：函数依赖
UPDATE users SET create_time = NOW() WHERE id = 1;
-- Statement格式在主从复制时，NOW()函数的值可能不同

-- 问题3：不确定函数
UPDATE users SET id = UUID() WHERE name = 'test';
-- UUID()函数每次执行结果不同，无法正确复制
```

**Statement格式的局限性：**

1. **上下文依赖**：无法记录变量、用户定义函数等
2. **函数依赖**：NOW()、RAND()等函数在主从复制时可能不一致
3. **不确定函数**：UUID()等函数每次执行结果不同
4. **存储过程/触发器**：可能无法正确复制

## 三、Row格式

### 3.1 Row格式的特点

**第二个是Row。它不会记录SQL的上下文信息。**

**仅仅是保留哪一条记录被修改。记录单元为每一行的改动。**

**Row格式示例：**

```sql
-- binlog中记录的是每一行的数据变化
-- 更新前：id=1, name='张三', age=20
-- 更新后：id=1, name='张三', age=21
```

### 3.2 Row格式的优点

**基本上可以全部记下来。**

**优点：**

1. **完整性**：记录每一行的完整数据变化
2. **准确性**：不依赖SQL上下文，数据准确
3. **可恢复性**：可以完整恢复数据
4. **无函数依赖**：不受函数影响

### 3.3 Row格式的缺点

**但是由于有很多操作会导致大量行的改动，因此这种模式下文件保存信息会比较多，日志量会比较大。**

**问题场景：**

```sql
-- 更新1000万条数据
UPDATE users SET status = 1 WHERE age > 18;
-- Row格式需要记录1000万行的数据变化
-- 日志文件会非常大
```

**Row格式的局限性：**

1. **日志量大**：每条记录的变化都要记录，日志文件大
2. **IO开销大**：写入binlog的数据量大，IO开销大
3. **性能影响**：可能影响主库的写入性能
4. **存储成本**：需要更多的存储空间

## 四、Mixed格式

### 4.1 Mixed格式的特点

**第三种是Mixed，它是一种折中的方案。**

**普通操作使用Statement记录，当无法使用Statement记录的时候会使用Row。**

### 4.2 Mixed格式的工作原理

**Mixed格式的决策逻辑：**

1. **默认使用Statement**：大多数操作使用Statement格式
2. **特殊情况使用Row**：当Statement无法正确复制时，自动切换到Row格式

**自动切换的场景：**

```sql
-- 以下情况会自动切换到Row格式：

-- 1. 使用不确定函数
UPDATE users SET id = UUID() WHERE name = 'test';

-- 2. 使用系统变量
UPDATE users SET balance = balance + @var WHERE id = 1;

-- 3. 使用存储过程/触发器
CALL update_user_balance(1, 100);

-- 4. 使用临时表
CREATE TEMPORARY TABLE temp_table ...
```

### 4.3 Mixed格式的优缺点

**优点：**
- **平衡性能和准确性**：大多数情况使用Statement，性能好
- **自动切换**：特殊情况自动使用Row，保证准确性
- **灵活性**：根据情况自动选择最合适的格式

**缺点：**
- **格式不统一**：binlog中可能同时存在两种格式
- **分析复杂**：需要理解两种格式才能分析binlog

## 五、三种格式对比

### 5.1 对比表

| 格式 | 记录内容 | 日志量 | 性能 | 准确性 | 适用场景 |
|------|---------|--------|------|--------|---------|
| **Statement** | SQL语句原文 | 小 | 高 | 较低 | 简单SQL，无函数依赖 |
| **Row** | 每行数据变化 | 大 | 低 | 高 | 复杂SQL，需要准确复制 |
| **Mixed** | 混合使用 | 中等 | 中等 | 高 | 大多数场景（推荐） |

### 5.2 详细对比

**1. 日志量对比：**

```sql
-- 更新1000条数据
UPDATE users SET status = 1 WHERE age > 18;

-- Statement格式：只记录一条SQL语句（约100字节）
-- Row格式：记录1000行数据变化（约100KB）
-- Mixed格式：根据情况，可能是Statement或Row
```

**2. 性能对比：**

- **Statement**：写入binlog的数据量少，性能最好
- **Row**：写入binlog的数据量大，性能较差
- **Mixed**：大多数情况性能好，特殊情况性能较差

**3. 准确性对比：**

- **Statement**：可能因为上下文、函数等原因导致数据不一致
- **Row**：记录完整数据，准确性最高
- **Mixed**：自动选择，准确性高

## 六、配置和使用

### 6.1 查看当前binlog格式

```sql
-- 查看当前binlog格式
SHOW VARIABLES LIKE 'binlog_format';

-- 或者
SELECT @@binlog_format;
```

### 6.2 设置binlog格式

**方式一：配置文件设置**

```ini
# my.cnf配置文件
[mysqld]
binlog_format = MIXED  # 或 STATEMENT、ROW
```

**方式二：动态设置**

```sql
-- 设置全局binlog格式
SET GLOBAL binlog_format = 'MIXED';

-- 设置当前会话binlog格式
SET SESSION binlog_format = 'ROW';
```

**注意：**
- 修改binlog_format需要重启MySQL服务（STATEMENT和ROW之间切换）
- Mixed格式可以在运行时动态切换

### 6.3 查看binlog内容

**使用mysqlbinlog工具查看：**

```bash
# 查看binlog内容
mysqlbinlog /var/log/mysql/mysql-bin.000001

# 查看特定格式的binlog
mysqlbinlog --base64-output=DECODE-ROWS -v /var/log/mysql/mysql-bin.000001
```

## 七、实际应用建议

### 7.1 格式选择建议

**如果大家想加深印象，更好地去应对面试，我认为可以自己搭建一下MySQL的主从集群，然后通过配置不同的binlog格式来查看日志内容。**

**选择建议：**

1. **一般场景**：使用**Mixed格式**（推荐）
   - 平衡性能和准确性
   - MySQL 5.7+默认格式

2. **简单场景**：使用**Statement格式**
   - 没有函数依赖
   - 对性能要求高

3. **复杂场景**：使用**Row格式**
   - 需要准确复制
   - 有函数依赖

### 7.2 最佳实践

1. **默认使用Mixed**：大多数场景使用Mixed格式
2. **监控日志量**：监控binlog日志量，避免过大
3. **定期清理**：定期清理旧的binlog文件
4. **测试验证**：在主从环境中测试不同格式的效果

### 7.3 注意事项

1. **格式切换**：STATEMENT和ROW之间切换需要重启
2. **日志分析**：Mixed格式的binlog分析需要理解两种格式
3. **性能影响**：Row格式可能影响主库性能
4. **存储空间**：Row格式需要更多存储空间

## 八、面试要点

### 8.1 回答框架

**在回答binlog格式问题时，可以从以下几个方面：**

1. **三种格式**：Statement、Row、Mixed
2. **各自特点**：记录内容、日志量、性能、准确性
3. **优缺点**：每种格式的优缺点
4. **使用场景**：什么场景使用什么格式
5. **配置方法**：如何配置和查看

### 8.2 关键点

**关键点：**

- **Statement**：记录SQL原文，日志量小，但可能不准确
- **Row**：记录数据变化，日志量大，但准确性高
- **Mixed**：混合使用，平衡性能和准确性（推荐）

## 九、总结

### 9.1 核心要点

MySQL binlog的三种格式：

1. **Statement格式**：
   - 记录SQL原文
   - 日志量小，性能好
   - 但可能因为上下文、函数等原因不准确

2. **Row格式**：
   - 记录每行数据变化
   - 日志量大，性能较差
   - 但准确性最高

3. **Mixed格式**：
   - 混合使用，自动选择
   - 平衡性能和准确性
   - 推荐使用

### 9.2 关键理解

**关键理解：**

- **没有完美的格式**：需要在性能和准确性之间权衡
- **根据场景选择**：根据实际业务场景选择合适的格式
- **Mixed是推荐**：大多数场景使用Mixed格式
- **实践验证**：通过搭建主从集群验证不同格式的效果

通过深入理解binlog的三种格式，我们可以更好地配置和使用MySQL的主从复制，在面试中给出让面试官满意的答案。

---

*本文深入分析了MySQL binlog的三种格式（Statement、Row、Mixed），包括各自的特点、优缺点、使用场景和配置方法，旨在帮助开发者系统性地理解binlog格式的选择和使用。*