【ACDU翻译】MySQL 17.2.1.1 基于语句和基于行的复制的优缺点

每种二进制日志格式都有优点和缺点。对于大多数用户来说，混合复制格式应该提供数据完整性和性能的最佳组合。但是，如果您想在执行某些任务时利用特定于基于语句或基于行的复制格式的功能，您可以使用本节中的信息，其中提供了它们相对优缺点的摘要，以确定最适合您的需求。

• 基于语句的复制的优点
• 基于语句的复制的缺点
• 基于行的复制的优点
• 基于行的复制的缺点

基于语句的复制的优点

• 成熟的技术。
• 写入日志文件的数据更少。当更新或删除影响许多行时，这会导致 日志文件所需的存储空间*大大减少。*这也意味着可以更快地完成从备份中获取和恢复的工作。
• 日志文件包含进行任何更改的所有语句，因此它们可用于审计数据库。

基于语句的复制的缺点

• 对 SBR 不安全的语句。 并非所有修改数据的语句（例如 、 和 statements）都可以使用基于语句的复制进行复制。使用基于语句的复制时，任何不确定的行为都难以复制。此类数据修改语言 (DML) 语句的示例包括： INSERT DELETEUPDATEREPLACE

  • 依赖于非确定性的可加载函数或存储程序的语句，因为此类函数或存储程序返回的值或取决于提供给它的参数以外的因素。（然而，基于行的复制只是复制函数或存储程序返回的值，因此它对表行和数据的影响在源和副本上是相同的。）请参阅 第 17.5.1.16 节，“调用功能的复制”，了解更多信息。

  • DELETEand UPDATE使用LIMIT不带 an 的子句的 语句ORDER BY是不确定的。请参阅 第 17.5.1.18 节，“复制和限制”。

  • 锁定使用or 选项的 读取语句 ( SELECT ... FOR UPDATEand )。请参阅 使用 NOWAIT 和 SKIP LOCKED 锁定读取并发。 SELECT ... FOR SHARENOWAIT``SKIP LOCKED

  • 确定性可加载函数必须应用于副本。

  • 使用基于语句的复制无法正确复制使用以下任何函数的语句：

    • LOAD_FILE()
    • UUID(), UUID_SHORT()
    • USER()
    • FOUND_ROWS()
    • SYSDATE()（除非源和副本都使用该 --sysdate-is-now 选项启动）
    • GET_LOCK()
    • IS_FREE_LOCK()
    • IS_USED_LOCK()
    • MASTER_POS_WAIT()
    • RAND()
    • RELEASE_LOCK()
    • SOURCE_POS_WAIT()
    • SLEEP()
    • VERSION()

    但是，使用基于语句的复制可以正确复制所有其他功能，包括 NOW()等等。

    有关更多信息，请参阅 第 17.5.1.14 节，“复制和系统函数”。

  使用基于语句的复制无法正确复制的语句会记录如下警告：

  [Warning] Statement is not safe to log in statement format.
  复制

  在这种情况下，也会向客户发出类似的警告。客户端可以使用 SHOW WARNINGS.

• INSERT ... SELECT与基于行的复制相比，它需要更多的行级锁。

• UPDATE需要表扫描的语句（因为 WHERE子句中没有使用索引）必须锁定比基于行的复制更多的行。

• For InnoDB： INSERT使用 AUTO_INCREMENT阻塞其他非冲突语句的INSERT 语句。

• 对于复杂的语句，在更新或插入行之前，必须在副本上评估和执行该语句。使用基于行的复制，副本只需要修改受影响的行，而不是执行完整的语句。

• 如果对副本的评估存在错误，尤其是在执行复杂语句时，基于语句的复制可能会随着时间的推移慢慢增加受影响行的误差幅度。请参阅 第 17.5.1.29 节，“复制期间的副本错误”。

• 存储函数以与 NOW()调用语句相同的值执行。但是，存储过程并非如此。

• 确定性可加载函数必须应用于副本。

• 表定义在源和副本上必须（几乎）相同。有关详细信息，请参阅 第 17.5.1.9 节，“在源和副本上具有不同表定义的复制”。

• 从 MySQL 8.0.22 开始，从 MySQL 授权表（通过连接列表或子查询）读取数据但不修改它们的 DML 操作在 MySQL 授权表上作为非锁定读取执行，因此对于基于语句的读取是不安全的复制。有关详细信息，请参阅 授予表并发性。

基于行的复制的优点

• 所有更改都可以复制。这是最安全的复制形式。

  笔记

  更新系统模式中的信息的语句 mysql（例如 GRANT， REVOKE触发器、存储例程（包括存储过程）和视图的操作）都使用基于语句的复制复制到副本。

  对于诸如 CREATE TABLE ... SELECT的CREATE 语句，从表定义生成语句并使用基于语句的格式进行复制，而行插入使用基于行的格式进行复制。

• 对于以下类型的语句，源上需要更少的行锁，从而实现更高的并发性：

  • INSERT ... SELECT
  • INSERT与声明 AUTO_INCREMENT
  • UPDATE或 带有不使用键或不更改大多数检查行的子句的 DELETE语句 。WHERE

• INSERT对于任何、 UPDATE或 DELETE语句 ， 副本上需要的行锁更少 。

基于行的复制的缺点

• RBR 可以生成更多必须记录的数据。要复制 DML 语句（例如 UPDATEor DELETE语句），基于语句的复制仅将语句写入二进制日志。相比之下，基于行的复制将每个更改的行写入二进制日志。如果语句更改了很多行，基于行的复制可能会向二进制日志写入更多数据；即使对于回滚的语句也是如此。这也意味着制作和恢复备份可能需要更多时间。另外，写入数据时二进制日志被锁定的时间较长，可能会导致并发问题。利用 binlog_row_image=minimal大大减少劣势。

• 生成大 BLOB值的确定性可加载函数使用基于行的复制进行复制比使用基于语句的复制花费更长的时间。这是因为 BLOB记录的是列值，而不是生成数据的语句。

• 您无法在副本上看到从源接收并执行了哪些语句。但是，您可以使用带有选项的 mysqlbinlog--base64-output=DECODE-ROWS 和--verbose.

  或者，使用该 binlog_rows_query_log_events 变量，如果启用 该变量，则在使用该选项 时将Rows_query带有语句的事件添加到 mysqlbinlog输出 。-vv

• 对于使用存储引擎的表，与将它们作为语句应用时相比，将它们作为基于行的事件应用到二进制日志时，语句MyISAM 的副本需要更强的锁定。INSERT这意味着MyISAM在使用基于行的复制时不支持对表进行并发插入。