面试题：MySQL性能优化的架构层面考量

Question

假设你负责一个高并发读写的MySQL数据库应用，目前性能遇到瓶颈。从架构层面出发，讨论如何通过主从复制、读写分离、分库分表等技术来优化整体性能。详细阐述每种技术在实际应用中的配置要点、潜在问题及解决方案，并且说明如何根据业务场景选择合适的架构优化组合。

Answer 1

主从复制

1. 配置要点
   • 主库配置：在my.cnf文件中，开启二进制日志（log-bin），设置唯一的服务器ID（server-id）。例如：

[mysqld]
log-bin=mysql-bin
server-id=1

- **从库配置**：同样设置唯一的服务器ID（与主库不同），配置`relay-log`用于记录从主库接收到的日志。例如：

[mysqld]
server-id=2
relay-log=mysql-relay-bin

- **建立连接**：在主库上创建用于复制的账号，并记录主库的二进制日志文件名和位置。在从库上使用`CHANGE MASTER TO`语句配置主库信息，然后启动从库复制线程。

2. 潜在问题 - 数据同步延迟：高并发写入时，主库产生日志速度快，从库应用日志可能跟不上。 - 主从数据不一致：网络波动、复制过程中异常等可能导致主从数据状态不一致。 3. 解决方案 - 针对同步延迟：增加从库数量分担负载，优化从库硬件性能，采用半同步复制提高数据同步实时性。 - 针对数据不一致：定期进行主从数据比对（如使用pt-table-checksum工具），出现不一致时及时修复，可通过重新配置从库复制或手动同步数据。

读写分离

1. 配置要点
   • 引入中间件：常用的如MyCat、Sharding - JDBC等。以MyCat为例，在其配置文件（server.xml和schema.xml）中定义数据库连接池、读写规则等。
   • 读写规则配置：根据SQL语句类型（SELECT为读，其他为写），将读请求路由到从库，写请求路由到主库。
2. 潜在问题
   • 读库数据不一致：由于主从复制延迟，读库可能读到旧数据。
   • 中间件性能瓶颈：如果中间件处理能力不足，可能成为系统性能瓶颈。
3. 解决方案
   • 针对读库数据不一致：对于实时性要求高的读请求，可直接路由到主库；或者在业务层做缓存，降低对数据库读的频率。
   • 针对中间件性能瓶颈：优化中间件配置，根据业务量合理调整中间件集群规模。

分库分表

1. 配置要点
   • 垂直分库：按业务模块将不同的表分到不同的数据库。例如，用户相关表、订单相关表分别放在不同库。
   • 垂直分表：将大表中不常用或字段长度大的字段拆分到新表。如用户表中，将用户简介等大字段拆分。
   • 水平分表：根据某一维度（如用户ID取模）将数据分散到多个表。在代码或中间件中配置分表规则。
   • 水平分库：同样根据某一维度（如地区、时间等）将数据分散到多个数据库。
2. 潜在问题
   • 跨库跨表操作复杂：关联查询、事务处理变得困难。
   • 数据迁移和扩容复杂：增加新的库表时，数据需要重新分布。
3. 解决方案
   • 针对跨库跨表操作复杂：尽量避免复杂的跨库跨表操作，必要时可通过中间件提供的分布式事务解决方案（如XA协议），或者在业务层进行多次操作并保证数据一致性。
   • 针对数据迁移和扩容复杂：采用预分区方式预留足够的库表空间，使用工具（如pt - online - schema - change）进行在线数据迁移。

架构优化组合选择

1. 读多写少场景：优先采用主从复制 + 读写分离，适当结合缓存，可满足大部分性能需求。如新闻资讯类应用，文章一经发布很少修改，大量用户读取。
2. 读写均衡场景：除主从复制和读写分离外，考虑垂直分库分表，将不同业务模块的数据库分离，降低单个数据库压力。如电商系统，用户模块、商品模块等可垂直分库。
3. 数据量巨大且读写都频繁场景：综合运用主从复制、读写分离、水平分库分表。例如社交平台，用户量和数据量庞大，读写操作频繁，通过水平分库分表按用户ID等维度分散数据，结合主从复制和读写分离提升性能。