96 lines
4.5 KiB
Markdown
96 lines
4.5 KiB
Markdown
---
|
||
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):主从延迟怎么处理、什么时候分库分表、分片键怎么选。
|