面试题：MySQL主备库使用不同存储引擎进行复制时可能面临哪些兼容性问题？

Question

在《主备库使用不同存储引擎的MySQL复制实践》场景下，举例说明主库采用InnoDB存储引擎，备库采用MyISAM存储引擎，在数据一致性、事务处理等方面可能出现哪些兼容性问题以及相应的解决方案是什么？

Answer 1

数据一致性问题及解决方案

1. 自增主键不一致
   • 问题：InnoDB自增锁机制与MyISAM不同。InnoDB是轻量级锁，在某些情况下自增值分配可能与MyISAM不同，导致主备库自增主键值不一致。例如，在高并发插入时，InnoDB的自增锁粒度小，可能出现跳号现象，而MyISAM是表级锁，自增行为较为顺序。
   • 解决方案：在主库上配置 innodb_autoinc_lock_mode=0，强制InnoDB使用传统的自增锁模式，与MyISAM类似，以保证自增主键在主备库上的一致性。但这种方式会降低InnoDB的并发插入性能，需要权衡。
2. 存储格式差异
   • 问题：InnoDB和MyISAM对数据的存储格式有差异，例如日期时间类型存储的内部表示可能不同。当主库写入数据后，备库在应用主库日志时，可能因存储格式转换导致数据细微差异，如毫秒级别的时间精度丢失。
   • 解决方案：在应用层对数据进行规范化处理，确保写入主库的数据格式是备库MyISAM能够准确兼容的。同时，在数据库设计时，尽量避免使用可能因存储引擎差异导致数据转换问题的数据类型。

事务处理兼容性问题及解决方案

1. 事务支持差异
   • 问题：InnoDB支持事务，而MyISAM不支持事务。当主库执行事务性操作时，备库无法按照事务的方式应用日志。例如，主库开启一个事务并执行多条插入语句，然后提交事务，在备库上，由于MyISAM不支持事务，这些操作会以非事务方式执行，如果其中某条语句失败，不会回滚之前已执行的语句，导致数据不一致。
   • 解决方案：在主库设计时，尽量将大事务拆分成多个小的逻辑单元，并且在每个小单元执行后进行错误检查和适当处理。同时，在备库上，通过应用层逻辑（如触发器）来模拟事务的部分功能，对可能出现的错误进行补偿操作，确保数据一致性。
2. 锁机制不同
   • 问题：InnoDB支持行级锁，MyISAM支持表级锁。主库上基于行级锁的并发控制逻辑，在备库上应用时，由于表级锁粒度大，可能导致并发性能下降，甚至出现死锁（虽然MyISAM死锁情况相对较少，但在特定场景下可能因主库并发逻辑在备库转换后出现死锁）。例如，主库上两个事务分别对不同行进行操作，在备库上可能因表级锁导致相互等待。
   • 解决方案：在应用层对并发操作进行优化，减少主库上不必要的锁竞争，同时在备库上合理调整应用主库日志的顺序，避免锁等待和死锁情况的发生。可以通过在备库上启用多线程复制，利用多个线程并行应用主库日志，提高并发处理能力，降低锁争用的影响。