孤独求学人

MySQL 的 binlog（二进制日志）用于记录对数据库进行的所有更改操作，主要用于数据恢复、主从复制和审计。binlog 有三种格式，每种格式在不同的场景下有各自的优点和缺点：

1. STATEMENT 格式

在 STATEMENT 格式下，MySQL 将每一条修改数据的 SQL 语句记录到 binlog 中，而不是记录具体的行级数据变化。换句话说，binlog 中记录的内容就是执行的 SQL 语句。

优点：

• 占用空间小：相比其他格式，STATEMENT 格式只记录 SQL 语句，binlog 文件的大小通常较小。
• 性能较好：因为它只记录 SQL 语句，而不是每一行数据的变化，生成和写入 binlog 的开销较小，尤其是当修改了大量数据时，binlog 的生成速度和磁盘 I/O 负担较轻。
• 适合大多数简单的操作：对于简单的 INSERT、UPDATE、DELETE 语句，使用 STATEMENT 格式足够高效。

缺点：

• 非确定性行为可能出错：如果 SQL 语句在执行时存在非确定性，如包含 NOW()、UUID()、RAND() 这样的函数，或者依赖于自定义的用户变量，STATEMENT 可能导致主从复制的不一致。
• 复杂语句的重放风险：某些复杂的 SQL 语句（如 INSERT ... SELECT、触发器、存储过程等）在主从复制或数据恢复时可能会引发问题，因为这些语句的执行顺序和环境依赖于运行时的上下文。
• 性能副作用：某些语句在主库上执行一次，可能会在从库上执行多次，增加了从库的负担。例如，涉及到大规模表的 UPDATE 和 DELETE 操作。

2. ROW 格式

在 ROW 格式下，MySQL 记录的是每一行的具体数据变化。对于每一条 UPDATE、DELETE 或 INSERT 语句，binlog 会记录受到影响的每一行的旧值和新值，而不记录原始的 SQL 语句。

优点：

• 数据一致性高：因为记录的是行级数据变化，复制时不依赖于 SQL 语句的执行环境，能确保主从数据库之间的数据完全一致。
• 支持所有类型的操作：无论 SQL 语句多么复杂（如带有存储过程、触发器、非确定性函数等），ROW 格式都能准确地记录并重放。
• 适合高并发和复杂场景：对于需要保证数据一致性和复杂操作场景下，ROW 格式非常可靠，主从复制时不会出现意外的执行差异。

缺点：

• 占用空间大：由于记录的是每一行的变化，而不是 SQL 语句，尤其在大规模更新或删除操作时，binlog 文件可能会非常大。
• 性能开销大：生成 binlog 的过程比较繁重，尤其是在涉及到修改大量行的场景时，会明显增加磁盘 I/O 和 CPU 的负担。
• 审核不便：由于 binlog 中只记录了数据行的变化，而没有原始的 SQL 语句，分析 binlog 文件以了解具体的 SQL 操作变得更加困难。

3. MIXED 格式

MIXED 是 STATEMENT 和 ROW 两种格式的结合体，MySQL 会根据执行的 SQL 语句的具体情况选择使用 STATEMENT 或 ROW 记录格式：

• 对于大部分确定性的操作，MySQL 会使用 STATEMENT 格式；
• 对于一些可能导致复制不一致的语句（如 UUID()、NOW()、RAND()、AUTO_INCREMENT 等），会自动切换为 ROW 格式。

优点：

• 自动选择最优方案：通过结合 STATEMENT 和 ROW 格式，MIXED 可以在大多数情况下有效降低 binlog 文件的大小，并且在需要时保证数据一致性。
• 减少非确定性语句的错误：对于可能引发主从不一致的语句，它会自动选择 ROW 格式，避免了 STATEMENT 格式的潜在问题。
• 适合各种场景：MIXED 格式结合了两者的优点，适用于大多数数据库环境，可以在性能和一致性之间取得平衡。

缺点：

• 性能开销和空间占用折中：虽然 MIXED 格式在大多数情况下能选择较优的记录方式，但在复杂的操作场景下，仍然可能导致较大的 binlog 文件和较高的性能开销。
• 难以预测格式切换：开发人员无法完全控制 MIXED 何时选择 ROW 还是 STATEMENT，有时可能导致意外的 ROW 记录增加 binlog 大小。

总结

格式	优点	缺点	适用场景
STATEMENT	占用空间小，性能高	复制数据不一致，复杂语句有问题	适用于简单、确定性较高的操作
ROW	复制数据一致性好，支持所有语句	占用空间大，性能开销高	需要确保主从一致、操作复杂时
MIXED	结合两者优点，自动选择最佳方式	性能和空间折中，无法控制记录格式	适合大多数场景，特别是包含复杂语句和高并发环境

具体选择哪种 binlog 格式，取决于你的业务需求：

• 如果对数据一致性要求极高，且可以接受较高的磁盘和性能开销，可以选择 ROW 格式。
• 如果主要以性能为导向，且操作较为简单，可以考虑使用 STATEMENT 格式。
• 而在大多数情况下，MIXED 格式通常是一个较为平衡的选择，它能够根据不同的操作类型动态选择合适的记录方式