--- id: backend.mysql.ops-scale title: 运维与扩展:慢查询、主从复制、读写分离、分库分表 type: lesson domain: backend stack: [mysql] tags: [慢查询, 主从复制, 读写分离, 分库分表, 在线 DDL] level: intermediate prerequisites: [backend.mysql.tx-locks-mvcc] est_hours: 3 related: [backend.mysql.scenario.deep-pagination, backend.mysql.interview] week: [14] order: 3 --- # 运维与扩展:慢查询、主从复制、读写分离、分库分表 ## 解释说明 **慢查询** - 打开慢查询日志:`slow_query_log = ON`、`long_query_time = 1`(秒);用 `mysqldumpslow` 或 `pt-query-digest` 汇总,按“总耗时”排序优先优化。 - 排查顺序:EXPLAIN → 看是否全表扫描 / filesort / 临时表 → 加或调整索引 → 改写 SQL → 考虑缓存或改表结构。 **主从复制** - 主库写 binlog,从库的 IO 线程拉取到 relay log,SQL 线程重放。默认是**异步复制**,主库宕机可能丢最后一部分数据;半同步复制要求至少一个从库收到后才返回。 - binlog 格式推荐 `ROW`(记录行的变化,最准确)。 - **主从延迟**:大事务、从库单线程重放、从库负载高。MySQL 5.7+ 支持并行复制。 **读写分离** - 写走主库,读走从库。问题是**刚写完立刻读**可能读到旧数据。常见做法:写后短时间内的读走主库;关键业务(支付结果、订单详情)强制读主库。 - 实现方式:应用里配两个连接池,或者用中间件(ProxySQL、ShardingSphere 等)。 **分库分表** - 什么时候考虑:单表数据量很大且索引已优化仍然慢、单库写入达到瓶颈。先考虑归档历史数据、加缓存、读写分离,分库分表是最后手段。 - 分片键:选查询最常用的维度(如 `user_id`),让大部分查询只落在一个分片上。 - 带来的问题:跨分片查询和排序分页、跨分片事务、全局唯一 ID(雪花算法)、扩容时的数据迁移。 **在线 DDL**:MySQL 8 很多 DDL 支持 `ALGORITHM=INPLACE / INSTANT`;大表变更常用 gh-ost、pt-online-schema-change。具体哪些操作支持 INSTANT 以官方文档为准:https://dev.mysql.com/doc/refman/8.4/en/innodb-online-ddl-operations.html ## 案例 - 某报表接口慢:慢日志显示一条 `GROUP BY` 语句每天累计耗时最高,EXPLAIN 发现 `Using temporary; Using filesort`,按分组列加联合索引后耗时明显下降;更大的统计改成每天定时汇总到统计表。 - 用户下单后立刻跳转订单详情,偶尔显示“订单不存在”:读写分离的主从延迟导致。订单详情页改为读主库。 ## 代码示例 应用层读写分离的最小实现(Python,演示思路,示意代码): ```python import contextvars import time _force_primary_until = contextvars.ContextVar("force_primary_until", default=0.0) class Router: """写走主库;写后 2 秒内同一请求上下文的读也走主库,避免主从延迟读到旧数据。""" def __init__(self, primary, replicas, sticky_seconds: float = 2.0): self.primary, self.replicas, self.sticky = primary, replicas, sticky_seconds self._i = 0 def for_write(self): _force_primary_until.set(time.monotonic() + self.sticky) return self.primary def for_read(self, strong: bool = False): if strong or not self.replicas or time.monotonic() < _force_primary_until.get(): return self.primary self._i = (self._i + 1) % len(self.replicas) # 简单轮询 return self.replicas[self._i] r = Router(primary="primary", replicas=["replica-1", "replica-2"]) assert r.for_read() in ("replica-1", "replica-2") r.for_write() assert r.for_read() == "primary" # 刚写完,读主库 assert r.for_read(strong=True) == "primary" # 关键业务强制读主库 ``` ## 易错点 - **一上来就分库分表**:复杂度大幅上升。先归档、索引、缓存、读写分离。 - **分片键选错**:大部分查询都要扫所有分片,性能反而更差。 - **从库当备份**:误删数据会立刻同步到从库,备份要单独做(全量 + binlog)并定期演练恢复。 - **大事务导致主从延迟**:批量操作分批提交。 - **直接在大表上执行 `ALTER TABLE`**:可能长时间锁表或引起复制延迟,先确认算法,必要时用在线变更工具,在低峰期执行。 ## 面试常问 见 [MySQL 面试题](interview.md):主从延迟怎么处理、什么时候分库分表、分片键怎么选。