📖 本篇简介：性能优化是面试拉开差距的地方——从慢查询定位到索引、SQL、参数、架构，一整套。本篇精选 20 道高频优化题，覆盖慢查询定位、EXPLAIN、深分页、在线加字段、索引选错、JOIN、count、Buffer Pool、CPU/IO 高排查、大事务、批量写、主从延迟、热点行、压测。前置知识：第 13、14、19、20、21 章；本系列示例基于 MySQL 8.0。
目录
- 1 性能优化总思路
- 题目 1：慢查询怎么定位
- 题目 2：EXPLAIN重点看什么
- 题目 3：深分页LIMIT大偏移怎么优化
- 题目 4：大表加字段怎么不锁表
- 题目 5：优化器选错索引怎么办
- 题目 6：慢JOIN怎么优化
- 题目 7：count大表怎么优化
- 题目 8：Buffer Pool怎么调优
- 题目 9：磁盘IO高怎么排查
- 题目 10：CPU高怎么排查
- 题目 11：锁等待慢怎么优化
- 题目 12：大事务怎么拆小
- 题目 13：批量insert怎么优化
- 题目 14：如何避免回表
- 题目 15：filesort排序慢怎么优化
- 题目 16：连接数过多怎么优化
- 题目 17：主从延迟怎么优化
- 题目 18：热点行更新慢怎么优化
- 题目 19：分库分表前SQL优化清单
- 题目 20：怎么给数据库做压测
- 速查速记
- 常见坑与注意事项
- 复习清单
- 练习题
1 性能优化总思路
性能优化别瞎调参数，按这个顺序：先定位（慢日志/EXPLAIN）→ 再改 SQL/索引 → 再调参数 → 最后架构扩容。
| 层次 | 手段 | 收益 |
|---|---|---|
| SQL | 改写法、加索引 | 最直接 |
| 参数 | Buffer Pool、日志 | 次优 |
| 架构 | 缓存、读写分离、分库 | 根治规模问题 |
原则：别过早优化，先量化瓶颈。
题目 1：慢查询怎么定位
考点
- 慢日志；定位手段。
参考答案
1. 开慢查询日志：long_query_time 阈值；
2. pt-query-digest 分析慢日志 TopN；
3. EXPLAIN 看执行计划；
4. show profile 看耗时分布；
5. 实时：SHOW PROCESSLIST 找正在跑的慢 SQL。
-- 查看慢查询相关参数，本示例基于 MySQL 8.0
SHOW VARIABLES LIKE 'slow_query_log'; -- 是否开启慢日志
SHOW VARIABLES LIKE 'long_query_time'; -- 慢查询阈值(秒)
追问
- 问：怎么快速看最慢的？答：pt-query-digest 按总耗时排序输出 Top。
- 问：慢日志在哪？答：
slow_query_log_file指定路径。
题目 2：EXPLAIN重点看什么
考点
- type、key、rows、Extra。
参考答案
1. type：访问类型，ALL=全表扫最差，至少 range/ref；
2. key：实际用的索引，NULL 表示没用索引；
3. rows：预估扫描行数，越大越慢；
4. Extra：Using filesort、Using temporary 要警惕，Using index 是好事；
5. select_type：复杂查询看是否有派生表。
追问
- 问：type 从好到差？答：const > eq_ref > ref > range > index > ALL。
- 问：Using filesort 一定不好？答：小数据量无所谓，大数据量要优化。
题目 3：深分页LIMIT大偏移怎么优化
考点
- 大 OFFSET；游标/延迟关联。
参考答案
1. 问题：LIMIT 100000,20 要扫前 100020 行再丢；
2. 游标分页：WHERE id<上次最大id ORDER BY id LIMIT 20；
3. 延迟关联：先子查询取主键，再 JOIN 回表；
4. 覆盖索引：子查询走覆盖索引；
5. 禁止跨库深分页。
-- 延迟关联优化深分页，本示例基于 MySQL 8.0
SELECT t.* FROM t_order t
JOIN (SELECT id FROM t_order WHERE user_id=1 ORDER BY id LIMIT 100000, 20) x
ON t.id = x.id; -- 子查询走覆盖索引，再回表只取20行
追问
- 问：游标要记住上次 id？答：前端带上一页最后 id。
- 问：不能用 id 排序？答：用排序列+唯一键做游标。
题目 4：大表加字段怎么不锁表
考点
- 在线 DDL；pt-osc。
参考答案
1. 小表：直接 ALTER，ALGORITHM=INPLACE, LOCK=NONE；
2. 大表：gh-ost / pt-online-schema-change；
3. 原理：影子表 + 增量同步 + 原子换表；
4. 低峰执行：避开高峰；
5. 8.0 原子 DDL：崩溃自动回滚。
追问
- 问：INPLACE 就完全不锁？答：元数据锁窗口仍在，别在高峰。
- 问：加索引呢？答：同样 INPLACE，大表用 gh-ost。
题目 5：优化器选错索引怎么办
考点
- 统计信息；FORCE INDEX。
参考答案
1. 原因：统计信息过期、数据分布倾斜；
2. ANALYZE TABLE 更新统计信息；
3. FORCE INDEX 临时强制走某索引；
4. 改写 SQL：消除导致选错的写法；
5. 优化索引：让最优索引更明显。
追问
- 问：FORCE INDEX 长期方案？答：临时救急，根治靠统计信息/索引设计。
- 问：怎么知道选错？答：EXPLAIN 对比 key 和预期。
题目 6：慢JOIN怎么优化
考点
- 小表驱动大表；N+1。
参考答案
1. 小表驱动大表：被驱动表 Join 列要有索引；
2. 避免 N+1：用 JOIN 或批量 IN，别循环单查；
3. 减少 JOIN 数量：适度冗余宽表；
4. Join Buffer：无索引时用，慢；
5. EXPLAIN：看被驱动表 type 是否 ref/eq_ref。
追问
- 问：为什么小表驱动？答：被驱动表每次都查索引，小表驱动 = 查的次数少。
- 问：大表 JOIN 慢？答：被驱动表 Join 列必须有索引。
题目 7：count大表怎么优化
考点
- InnoDB count 原理；替代。
参考答案
1. InnoDB count(\*) 无 where 要扫可见行；
2. 带 where 走最小索引；
3. 精确总数用计数表：增删时维护；
4. 近似值：SHOW TABLE STATUS 估算；
5. 大数放 Redis。
追问
- 问：count(1) 和 count(*)？答：InnoDB 里基本一样。
- 问：count(列)？答：不计 NULL，更慢。
题目 8：Buffer Pool怎么调优
考点
- 大小；命中率。
参考答案
1. 大小：物理内存 50%~70%；
2. 目标：热数据全放内存，命中率高；
3. 观察：SHOW ENGINE INNODB STATUS 看命中率；
4. 多实例：大内存可配多 instance；
5. 预热：启动后把热点页加载。
追问
- 问：命中率低？答：数据量超内存，或大量全表扫污染。
- 问：设 100% 内存？答：留给 OS/其他进程，50%~70%。
题目 9：磁盘IO高怎么排查
考点
- IO 瓶颈；刷脏；慢查询。
参考答案
1. 看 iostat：%util、await；
2. 慢查询：大量随机 IO 来自回表/全表扫；
3. 刷脏页：checkpoint 刷盘突增；
4. undo/binlog：大事务产生大量日志；
5. 优化：加索引减少回表、调刷脏、上 SSD。
追问
- 问：刷脏怎么顺？答：调
innodb_io_capacity，平滑刷盘。 - 问：随机 IO 多？答：覆盖索引减少回表。
题目 10：CPU高怎么排查
考点
- CPU 瓶颈点。
参考答案
1. 慢查询全表扫：CPU 耗在逐行比对；
2. 高 QPS 简单查询：连接数多，上下文切换；
3. 排序/聚合：filesort、临时表耗 CPU；
4. 定位：top 找 MySQL 进程，show processlist 找 SQL；
5. 优化：加索引、减少扫描、降 QPS。
追问
- 问：CPU 高但没慢查询？答：可能高 QPS 小查询累计，看 QPS 和连接数。
- 问：临时表落磁盘？答：tmp_table_size 调大，先内存临时表。
题目 11：锁等待慢怎么优化
考点
- 长事务；热点行。
参考答案
1. 找长事务：information_schema.innodb_trx；
2. 缩短事务：移出 RPC/重逻辑；
3. 热点行分桶：打散行锁；
4. 乐观锁：冲突少避免排队；
5. kill 阻塞源：临时止血。
-- 查看当前锁等待，本示例基于 MySQL 8.0
SELECT * FROM information_schema.innodb_trx ORDER BY trx_started; -- 找最老事务
SELECT * FROM performance_schema.data_lock_waits; -- 锁等待关系
追问
- 问：怎么避免死锁？答：固定访问顺序、短事务。
- 问：锁等待超时？答：
innodb_lock_wait_timeout。
题目 12：大事务怎么拆小
考点
- 长事务危害；分批。
参考答案
1. 危害：undo 膨胀、锁久、主从延迟；
2. 拆分：大事务拆多个小事务；
3. 异步化：非核心移事务外；
4. 批量小批提交：每百行一批；
5. 监控长事务。
追问
- 问：拆了中间不一致？答：接受最终一致 + 补偿。
- 问：批量更新怎么分批？答：按主键区间 LIMIT 循环。
题目 13：批量insert怎么优化
考点
- 多行 values；事务合并。
参考答案
1. 多行 insert：一条 SQL 多组 values；
2. 控制批次：每批几百~一千；
3. 本地事务合并：一批一事务；
4. 顺序自增主键；
5. 更快：LOAD DATA INFILE。
-- 批量insert，本示例基于 MySQL 8.0
INSERT INTO t_log(uid, action, created_at) VALUES
(1,'a',NOW()),(2,'b',NOW()),(3,'c',NOW()); -- 一批多行，比逐条快很多
追问
- 问：批太大？答：binlog 巨大、主从延迟。
- 问：还慢？答：LOAD DATA INFILE。
题目 14：如何避免回表
考点
- 覆盖索引。
参考答案
1. 覆盖索引：查询列都进索引；
2. Extra 显示 Using index；
3. 设计：把高频查询列加进联合索引；
4. 别 SELECT *；
5. 权衡：索引变宽写放大。
追问
- 问：怎么设计覆盖索引？答：where 列 + select 列一起建联合索引。
- 问：覆盖索引能含大字段？答：宽索引代价大，权衡。
题目 15：filesort排序慢怎么优化
考点
- 索引顺序排序；避免额外排序。
参考答案
1. 问题：Extra 出现 Using filesort；
2. 让 ORDER BY 走索引：联合索引顺序匹配排序；
3. 索引列含排序列：(where列, order by列)；
4. 8.0 降序索引：混合方向用降序索引；
5. 小结果集：filesort 也无所谓。
追问
- 问：为什么 filesort 慢？答：额外排序，大数据量要磁盘排序。
- 问：怎么免 filesort？答：索引顺序与 ORDER BY 完全一致。
题目 16：连接数过多怎么优化
考点
- 连接池；短事务。
参考答案
1. 止血：kill 空闲/慢连接，临时调大 max_connections；
2. 根因：多为慢 SQL/长事务占连接；
3. 连接池合理：不是越大越好；
4. 短事务：缩短连接占用；
5. 中间件复用连接。
追问
- 问：池设多大？答：核数*2+磁盘数，通常几十~几百。
- 问：为什么不是越大越好？答：上下文切换、锁竞争。
题目 17：主从延迟怎么优化
考点
- 并行复制；大事务。
参考答案
1. 大事务拆分：减少单事务重放量；
2. 并行复制：8.0 基于组提交并行回放；
3. 从库性能：从库配置跟上；
4. binlog 格式：ROW 量大，权衡；
5. 延迟监控：超阈值切流。
追问
- 问：为什么单线程回放慢？答：传统 SQL 线程单线程重放。
- 问：半同步？答：写延迟换一致。
题目 18：热点行更新慢怎么优化
考点
- 行锁竞争；分桶。
参考答案
1. 缩短事务：锁内别做重逻辑；
2. 分桶打散：热点行拆多行；
3. 前置 Redis：高频写先 Redis；
4. 乐观锁：冲突少避免排队；
5. 批量合并：计数类合并刷回。
追问
- 问：分桶后怎么汇总？答：SUM 或 Redis 维护总数。
- 问：还不够？答：热点数据迁 Redis，DB 只快照。
题目 19：分库分表前SQL优化清单
考点
- 拆分前先优化。
参考答案
1. 先优化 SQL：别拿烂 SQL 去分库分表；
2. 消除全表扫：where 都有索引；
3. 消除深分页；
4. 消除大事务；
5. 跨库 Join 提前规避；
6. 再考虑分片键。
追问
- 问：为什么先优化再拆？答：拆了只是分摊，烂 SQL 依然慢。
- 问：拆分后注意？答：分布式 ID、跨库查询。
题目 20：怎么给数据库做压测
考点
- 压测方法；容量。
参考答案
1. 工具：sysbench / 业务流量回放；
2. 真实混合：读写比例按线上；
3. 找拐点：TPS/QPS 随并发上升到下降的点；
4. 容量规划：峰值 × 安全系数；
5. 观察资源：CPU/IO/连接水位。
追问
- 问：压测数据量？答：按生产数据量，别空表压。
- 问：压什么？答：核心 SQL 混合负载，别只 select。
速查速记
| 问题 | 定位 | 优化手段 |
|---|---|---|
| 慢查询 | 慢日志+EXPLAIN | 加索引/改写法 |
| 深分页 | LIMIT大偏移 | 游标/延迟关联 |
| 选错索引 | key为NULL/不对 | ANALYZE/FORCE |
| count慢 | 扫可见行 | 计数表/估算 |
| 锁等待 | innodb_trx | 短事务/分桶 |
| 主从延迟 | Seconds_Behind | 并行复制/拆事务 |
| IO高 | iostat | 减少回表/刷脏 |
| CPU高 | processlist | 加索引降QPS |
常见坑与注意事项
- 坑 1：一上来就调参数——先 EXPLAIN 定位 SQL，多数慢是索引问题；
- 坑 2：深分页加大缓冲——
LIMIT 100000,20还是扫，要游标/延迟关联； - 坑 3：连接池盲目调大——反而 CPU/连接数恶化；
- 坑 4：大表直接 ALTER——锁表，用 gh-ost/INPLACE；
- 坑 5：分库分表前不优化 SQL——拆了分摊，烂 SQL 依然慢；
- 注意：所有命令按 MySQL 8.0，压测前先备份、别在生产直接压。
复习清单
| 题目 | 核心 | 易错点 |
|---|---|---|
| 慢查询 | 慢日志+EXPLAIN | 别瞎调参数 |
| 深分页 | 游标/延迟关联 | 别加缓冲 |
| 加字段 | INPLACE/gh-ost | 大表别裸ALTER |
| 选错索引 | ANALYZE/FORCE | FORCE是临时 |
| 回表 | 覆盖索引 | 别SELECT * |
| filesort | 索引顺序排序 | 排序列进索引 |
| 锁等待 | 短事务/分桶 | 别事务里RPC |
| 主从延迟 | 并行复制/拆事务 | 大事务拖 |
练习题
练 1：一条 SELECT * FROM t_order ORDER BY id LIMIT 100000, 20 很慢，给出优化方案。
<details>
<summary>参考答案</summary>
方案一：游标分页 WHERE id < 上页最后id ORDER BY id DESC LIMIT 20；方案二：延迟关联，先 SELECT id FROM t_order ORDER BY id LIMIT 100000,20（走主键覆盖索引），再 JOIN 回原表取整行。两者都避免扫 10 万行再回表。
</details>
练 2：EXPLAIN 一条 SQL 的 type=ALL、rows 显示扫描 500 万行，Extra 有 Using filesort，你怎么优化？
<details>
<summary>参考答案</summary>
type=ALL 是全表扫，先看 where 条件列有没有索引，补上合适索引让 type 升到 ref/range；ORDER BY 列加进联合索引让排序走索引、消除 Using filesort；避免 SELECT * 争取覆盖索引。优化后再 EXPLAIN 验证 rows 大幅下降、type 改善。
</details>
下一篇预告：第 40 章 面试官追问链TOP20精讲
文章回复
0 条公开回复