发布于 由admin

二进制日志（Binlog）格式：Statement, Row, Mixed

好嘞，各位程序猿、攻城狮们，欢迎来到今天的 "Binlog 格式奇妙之旅"！ 🚀 今天咱们不谈那些枯燥的理论，就聊聊 MySQL 数据库里那些“记录在案”的小秘密——二进制日志（Binlog）。

想象一下，Binlog 就像是数据库的“黑匣子”，记录了你对数据库做的每一件“坏事”和“好事”，比如增删改数据、创建删除表等等。有了它，你可以搞事情之后“时光倒流”，恢复数据，也可以把数据同步到其他地方，实现主从复制，简直是居家旅行、数据库运维的必备神器！

但是，这个“黑匣子”里的内容可不是随便乱写的，它有三种不同的“记录方式”，也就是三种 Binlog 格式：Statement、Row 和 Mixed。今天咱们就来扒一扒这三种格式的底裤，看看它们各自有什么优缺点，以及在什么情况下应该选择哪一种。

第一幕：Statement 格式——“简洁派的记录者” 📝

Statement 格式，顾名思义，就是记录你执行的 SQL 语句。就像一个简洁派的日记作者，只记录了你“做了什么”，而没记录你“怎么做的”。

举个例子，假设你执行了一条 SQL 语句：

UPDATE products SET price = price * 1.1 WHERE category = '电子产品';

如果 Binlog 格式是 Statement，那么它就会直接记录下这条 SQL 语句。 简单粗暴，是不是？

优点：

  • 体积小巧： 相比其他格式，Statement 格式记录的内容最少，Binlog 文件体积自然也就小巧玲珑，节省磁盘空间。
  • 网络传输快： 在主从复制场景下，需要把 Binlog 传输到从库。体积小意味着传输速度快，可以减少主从延迟。

缺点：

  • “薛定谔的同步”： 这是 Statement 格式最大的坑！有些 SQL 语句在不同的环境下执行，结果可能不一样！😱 比如，使用了 NOW()、RAND() 等函数的 SQL 语句，或者使用了存储过程、触发器等，都会导致主从数据不一致。

    • NOW() 的困扰： 如果在主库执行 SQL 时 NOW() 的时间是 10:00，而从库执行这条 SQL 时 NOW() 的时间变成了 10:01，那结果就悲剧了。
    • RAND() 的魔咒： RAND() 函数会生成随机数，主从库生成的随机数不一样，数据同步就会出问题。
    • 存储过程和触发器的陷阱： 存储过程和触发器的执行结果可能受到环境变量的影响，也会导致主从数据不一致。

  • 锁的烦恼： 为了保证数据的一致性，Statement 格式在某些情况下需要加锁，可能会影响数据库的并发性能。

适用场景：

  • 不涉及不确定性函数的 SQL 语句： 如果你的 SQL 语句都很“老实”，不使用 NOW()、RAND() 等函数，也不使用存储过程、触发器，那么 Statement 格式还是可以考虑的。
  • 对数据一致性要求不高： 如果你可以容忍主从数据偶尔出现一些小偏差，那么 Statement 格式也是可以接受的。

总结： Statement 格式就像一个“渣男”，优点是简洁高效，缺点是容易翻车。使用的时候要慎之又慎，不然可能会让你掉进数据不一致的坑里。 🕳️

第二幕：Row 格式——“事无巨细的记录者” 🕵️‍♀️

Row 格式就像一个“老妈子”，事无巨细地记录每一行数据的变化。它记录了每一行数据被修改前后的值，就像一个忠实的“数据备份员”。

还是上面的例子：

UPDATE products SET price = price * 1.1 WHERE category = '电子产品';

如果 Binlog 格式是 Row，那么它会记录下每一行被修改的产品的 ID、修改前的价格、修改后的价格等等，就像下面这样：

### UPDATE `products`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_unsigned=0 */
###   @2='笔记本电脑' /* VAR string meta=128 nullable=1 is_binary=0 */
###   @3=5000.00 /* DECIMAL meta=65535 nullable=1 is_unsigned=0 */
### SET
###   @2='笔记本电脑' /* VAR string meta=128 nullable=1 is_binary=0 */
###   @3=5500.00 /* DECIMAL meta=65535 nullable=1 is_unsigned=0 */

优点：

  • 数据一致性高： 这是 Row 格式最大的优点！无论你的 SQL 语句多么复杂，使用了什么不确定性函数，Row 格式都能保证主从数据的一致性。 💯
  • 恢复数据方便： 由于 Row 格式记录了每一行数据的变化，因此可以很方便地恢复数据，即使是误操作导致的数据丢失，也能轻松找回。

缺点：

  • 体积庞大： 相比 Statement 格式，Row 格式记录的内容更多，Binlog 文件体积自然也就更大，占用更多的磁盘空间。
  • 网络传输慢： 在主从复制场景下，需要传输更多的 Binlog 数据，可能会导致主从延迟增加。
  • 性能损耗： 记录每一行数据的变化需要额外的开销，可能会对数据库的性能产生一定的影响。

适用场景：

  • 对数据一致性要求高： 如果你对数据一致性要求非常高，不允许出现任何偏差，那么 Row 格式是你的最佳选择。
  • 需要频繁恢复数据： 如果你的业务需要频繁地进行数据恢复，那么 Row 格式可以让你事半功倍。
  • 使用了不确定性函数的 SQL 语句： 如果你的 SQL 语句使用了 NOW()、RAND() 等函数，或者使用了存储过程、触发器，那么必须使用 Row 格式，以保证数据的一致性。

总结： Row 格式就像一个“靠谱的老实人”，优点是数据一致性高，缺点是体积庞大。如果你的业务对数据一致性要求高，并且可以容忍一定的性能损耗，那么 Row 格式是你的不二之选。 👍

第三幕：Mixed 格式——“左右逢源的记录者” 🤹

Mixed 格式就像一个“八面玲珑”的家伙，它会根据不同的情况，自动选择 Statement 格式或 Row 格式。 它可以说是前两种格式的结合体。

具体来说，Mixed 格式会判断你执行的 SQL 语句是否会引起数据不一致，如果不会，就使用 Statement 格式，如果会，就使用 Row 格式。

优点：

  • 兼顾了性能和数据一致性： Mixed 格式可以在保证数据一致性的前提下，尽量减少 Binlog 文件的大小，提高性能。
  • 灵活性高： Mixed 格式可以根据不同的场景，自动选择合适的记录方式，避免了手动切换的麻烦。

缺点：

  • 判断逻辑复杂： Mixed 格式需要判断 SQL 语句是否会引起数据不一致，这个判断逻辑比较复杂，可能会引入一些 bug。
  • 兼容性问题： 在某些特殊情况下，Mixed 格式可能会出现兼容性问题。

适用场景：

  • 对性能和数据一致性都有要求： 如果你既想保证数据一致性，又想尽量提高性能，那么 Mixed 格式是一个不错的选择。
  • 不想手动切换 Binlog 格式： 如果你懒得手动切换 Binlog 格式，那么 Mixed 格式可以让你省心省力。

总结： Mixed 格式就像一个“折衷主义者”，优点是兼顾了性能和数据一致性，缺点是判断逻辑复杂。如果你对性能和数据一致性都有要求，并且不想手动切换 Binlog 格式，那么 Mixed 格式可以考虑一下。 🤝

一个表格总结一下：

特性 Statement Row Mixed
数据一致性 低 高 中
文件大小 小 大 中
性能损耗 低 高 中
适用场景 不涉及不确定性函数，对数据一致性要求不高 对数据一致性要求高，需要频繁恢复数据 对性能和数据一致性都有要求
风险 数据不一致 文件过大，网络传输慢，性能损耗 判断逻辑复杂，兼容性问题

举个例子，让你彻底明白：

假设你正在经营一家电商网站，数据库里有一张 orders 表，记录了用户的订单信息。

  • 场景一： 你执行了一条简单的 SQL 语句，更新了某个订单的状态：

    UPDATE orders SET status = '已发货' WHERE order_id = 12345;

    这条 SQL 语句不会引起数据不一致，所以无论你选择哪种 Binlog 格式，都不会有问题。但是，如果选择 Statement 格式，Binlog 文件会更小，传输速度也会更快。

  • 场景二： 你执行了一条使用了 NOW() 函数的 SQL 语句，记录了订单的发货时间：

    UPDATE orders SET delivery_time = NOW() WHERE order_id = 12345;

    这条 SQL 语句会引起数据不一致，因为主从库执行这条 SQL 语句时 NOW() 的时间可能不一样。所以，你必须选择 Row 格式或 Mixed 格式，以保证数据的一致性。

  • 场景三： 你执行了一条复杂的存储过程，里面包含了很多 SQL 语句，并且使用了 RAND() 函数：

    CALL update_order_status(12345);

    这条 SQL 语句肯定会引起数据不一致，所以你必须选择 Row 格式，以保证数据的一致性。

温馨提示：

  • MySQL 5.7 默认的 Binlog 格式是 Statement，MySQL 8.0 默认的 Binlog 格式是 Row。
  • 你可以通过修改 my.cnf 文件来修改 Binlog 格式。
  • 在修改 Binlog 格式之前，一定要充分了解你的业务场景，选择最合适的格式。

总结：

Binlog 格式的选择是一个需要权衡利弊的过程。你需要根据你的业务场景，选择最合适的格式，以保证数据的一致性和性能。

希望今天的 "Binlog 格式奇妙之旅" 能让你对 Binlog 格式有更深入的了解。 记住，没有最好的格式，只有最适合你的格式。 祝你使用 Binlog 愉快！ 🎉

希望这篇文章对你有帮助！ 如果还有什么疑问，欢迎随时提问。 😊

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注