05.1.3 SQL 优化与执行计划
所属:全栈开发面试题库 / 05 数据库与存储 / 05.1 关系型数据库通用能力
这个知识域解决什么问题
SQL 优化不能靠背结论,核心是看得懂执行计划、判断访问路径与代价、定位慢查询根因并改写。本域从执行计划与代价模型出发,串联索引设计与失效场景、慢查询定位与改写、统计信息对优化器的影响,以及批量与大规模数据操作;MySQL 与 PostgreSQL 的计划格式、统计机制差异在各题中对照说明。具体引擎实现分别见 05.2 MySQL 与 05.3 PostgreSQL。
知识地图
SQL 文本
→ 解析/绑定 → 优化器(基于统计信息估算代价)→ 执行计划 → 执行
执行计划:访问路径(全表/索引扫描/索引查找/回表)、连接方式、排序/聚合/临时表
优化主线:
计划解读(1)→ 索引是否被使用(2)→ 慢查询定位与改写(3)
→ 统计信息与优化器选型(4)→ 批量与大规模数据操作(5)
子主题
| 编号 | 子主题 | 核心内容 | 题目数 |
|---|---|---|---|
| 05.1.3.1 | 执行计划与代价模型 | 计划形态、访问路径、JOIN 算法、代价估算、MySQL EXPLAIN 与 PG EXPLAIN ANALYZE 解读 | 18 |
| 05.1.3.2 | 索引设计与失效场景 | 索引选择、最左前缀、覆盖索引、函数/隐式转换/前导模糊等失效场景与排查 | 18 |
| 05.1.3.3 | 慢查询定位与改写 | 慢日志、定位链路、深分页、OR/子查询/排序聚合改写、反模式清单 | 18 |
| 05.1.3.4 | 统计信息与优化器 | 直方图/密度、选择率估算、统计更新、选错索引、参数嗅探与计划稳定性 | 18 |
| 05.1.3.5 | 批量与大规模数据操作 | 批量写入/更新/删除、分页批处理、大事务、DDL 与锁、数据归档与批量回填 | 18 |
合计 90 题。 每题按「一句话结论 → 概念解释 → 原理流程 → 代码/命令 → 常见误区 → 追问(带答案)」组织;SQL 片段为可核对语句,MySQL 与 PostgreSQL 差异分别标注,版本行为不确定处标「待确认」。
学习路径
执行计划与代价模型(1)
→ 索引设计与失效场景(2)
→ 慢查询定位与改写(3)
→ 统计信息与优化器(4)
→ 批量与大规模数据操作(5)
自检清单
- [ ] 能读懂 MySQL
EXPLAIN的type/key/rows/Extra与 PGEXPLAIN (ANALYZE, BUFFERS)输出 - [ ] 能区分全表扫描、索引扫描、索引查找与回表,说明各自代价
- [ ] 能列出 JOIN 算法(嵌套循环/哈希/归并)的适用条件
- [ ] 能判断索引失效原因:函数运算、隐式转换、前导模糊、最左前缀断裂等
- [ ] 能从慢日志与计划定位根因,并给出对应改写方案
- [ ] 能解释统计信息如何影响行数估算与索引选择,知道何时更新统计
- [ ] 能设计批量写入/更新/删除方案,控制大事务与锁影响