面试题：MariaDB binlog中等难度调优问题

Question

在MariaDB中，简述binlog的写入模式（如ROW、STATEMENT、MIXED）及其各自的优缺点，在性能调优场景下，如何根据业务特点选择合适的写入模式？

Answer 1

binlog写入模式

1. ROW模式
   • 原理：记录数据行的实际变化，即每一行数据的修改都会被记录到binlog中。
   • 优点：
     • 数据恢复精确，基于行的复制可以准确地还原数据的变化，即使是复杂的事务也能完整无误地重现。
     • 对于一些基于触发器、存储过程等导致数据隐式修改的场景，能更准确地记录数据变化，避免数据不一致问题。
   • 缺点：
     • 日志量较大，因为每行数据的变动都要记录，特别是大表数据修改时，会产生大量的binlog日志，占用较多磁盘空间和网络带宽。
     • 主从复制时，由于日志量大，可能会导致网络传输压力增加，影响复制性能。
2. STATEMENT模式
   • 原理：记录执行的SQL语句，主库将执行的SQL语句写入binlog，从库在复制时执行相同的SQL语句。
   • 优点：
     • 日志量小，只记录SQL语句，相比于ROW模式，占用的磁盘空间和网络带宽较少，在网络带宽有限或磁盘空间紧张的情况下更有优势。
     • 执行效率高，因为只记录SQL语句，不需要像ROW模式那样详细记录每行数据的变化，写入binlog的操作相对简单。
   • 缺点：
     • 可能存在数据不一致问题，某些SQL语句在主从库上执行结果可能不同，比如使用函数NOW()获取当前时间，主从库执行时间可能有差异，导致数据不一致。
     • 对于一些复杂的操作，如触发器、存储过程等，在主从库上的执行结果可能因环境差异而不同，影响数据一致性。
3. MIXED模式
   • 原理：结合了ROW和STATEMENT模式，MariaDB会根据SQL语句的特性自动选择使用哪种模式记录binlog。对于一些确定性的SQL语句（如不涉及函数等可能导致主从执行结果不一致的语句），使用STATEMENT模式记录；对于不确定性的SQL语句或对数据一致性要求较高的操作，使用ROW模式记录。
   • 优点：
     • 兼顾了日志量和数据一致性，能根据不同的SQL语句特性选择合适的记录模式，在保证数据一致性的同时，尽量减少日志量。
     • 适应多种业务场景，对于不同类型的业务操作，能灵活选择记录模式，提高复制的稳定性和效率。
   • 缺点：
     • 选择模式的规则可能较为复杂，对于一些边缘情况，可能难以准确判断应该使用哪种模式，需要深入了解数据库内部机制才能准确把握。

根据业务特点选择合适的写入模式

1. 对于数据一致性要求极高的业务
   • 选择：ROW模式。
   • 原因：如金融业务，涉及资金的转移等操作，数据的准确性和一致性至关重要。ROW模式能精确记录每一行数据的变化，确保主从库数据完全一致，避免因数据不一致导致的资金风险等问题。
2. 对于日志量敏感，对数据一致性要求相对不那么严格的业务
   • 选择：STATEMENT模式。
   • 原因：例如一些简单的网站统计业务，主要记录访问量等数据，偶尔的主从数据差异（极小概率）对业务影响不大。这种情况下，STATEMENT模式的小日志量优势能显著减少磁盘空间占用和网络带宽消耗，提高系统整体性能。
3. 业务场景复杂，既有对数据一致性要求高的操作，又有对日志量敏感的操作
   • 选择：MIXED模式。
   • 原因：例如电商业务，在商品信息修改时，对数据一致性要求高，适合ROW模式；而在记录订单浏览量等操作时，对数据一致性要求相对较低，适合STATEMENT模式。MIXED模式能根据不同操作的特点自动选择合适的记录模式，满足复杂业务场景的需求。