Files

96 lines
4.5 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
---
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):主从延迟怎么处理、什么时候分库分表、分片键怎么选。