面试题：如何优化 MySQL 复制中存在非事务型表时的性能与可靠性

Question

假设在一个复杂的 MySQL 复制拓扑结构中，存在多个非事务型表，从性能和可靠性两方面出发，详细说明你会采取哪些措施来进行优化，包括但不限于参数调整、架构设计以及复制方式的选择等方面。

Answer 1

参数调整

1. innodb_flush_log_at_trx_commit：对于非事务表虽然不受此参数对事务持久性的直接影响，但该参数设置会影响整体I/O性能。设置为2时，日志每秒写一次并刷盘，在性能和可靠性上有较好平衡，兼顾性能的同时也有一定故障恢复能力。
2. sync_binlog：若设为1，每次事务提交时二进制日志都同步到磁盘，可靠性高但性能损耗大；设为0由操作系统决定何时同步，性能好但崩溃时可能丢失二进制日志。可根据业务对数据一致性要求，在0 - N（N为大于1的整数）之间取值，N值越大性能越好但故障时可能丢失N个事务的二进制日志。
3. key_buffer_size：非事务型表（如MyISAM）使用键缓存来缓存索引块。合理设置此参数，根据服务器内存和表的索引大小调整，若设置过小，索引I/O频繁影响性能；过大则浪费内存。

架构设计

1. 读写分离：通过引入中间件（如MaxScale、Mycat等）实现读写分离，主库负责写操作，从库负责读操作。对于非事务型表，读操作可分布到多个从库，减轻主库压力，提高整体性能。同时从库可设置不同的缓存策略进一步优化读性能。
2. 分库分表：按业务逻辑对非事务型表进行分库分表。水平分表可按数据范围（如时间、ID范围）将数据分散到不同表，降低单个表的大小和查询压力；垂直分表将不常用字段拆分到单独表，减少数据冗余和单次查询的数据量。分库可将不同业务相关的非事务型表分布到不同数据库实例，减少单个实例的负载。
3. 增加缓存：在应用层和数据库层之间引入缓存（如Redis）。对于查询频繁的非事务型表数据，将查询结果缓存起来，应用先从缓存读取数据，减少数据库的读压力。缓存可设置合理的过期时间，以保证数据的一致性。

复制方式选择

1. 基于行的复制（ROW）：对于非事务型表，ROW复制方式记录数据行的变化，相比基于语句的复制（STATEMENT），它能更准确地复制数据，尤其是在复杂数据操作（如触发器、函数等）情况下。ROW复制减少了由于不同数据库环境导致的复制不一致问题，提高了可靠性。同时在某些复杂查询场景下，由于记录行变化，能更高效地应用到从库，提升性能。
2. 半同步复制：在主从复制架构中采用半同步复制，主库在提交事务前，等待至少一个从库接收到并写入中继日志，相比异步复制，它提高了数据的可靠性，降低了主库崩溃时数据丢失的风险。虽然半同步复制会增加一定的延迟，但在可接受范围内能较好地平衡性能和可靠性。