SQL优化与优化器
MySQL 优化器会根据成本模型选择执行计划。SQL 优化不是把语句改短，而是让优化器拥有更好的访问路径、更准确的统计信息和更小的数据处理范围。
# 1. 学习定位
| 维度 | 内容 |
|---|---|
| 难度层级 | 性能进阶 |
| 核心目标 | 先会用，再理解机制，最后能做工程取舍和故障分析 |
| 学习方法 | 带着业务场景看命令、SQL、数据结构、日志和运行时指标 |
# 2. 核心地图
SQL优化与优化器
├─ 成本模型
├─ 统计信息
├─ 连接顺序
├─ 索引选择
├─ 谓词下推
├─ 覆盖索引
├─ 临时表
└─ 排序优化
# 3. 小章节深度讲解
下面把核心地图中的每个点拆开讲。每个小节都同时面向开发和运维：开发侧关注怎么设计、怎么写代码、怎么避免错误；运维侧关注怎么监控、怎么定位、怎么恢复。
# 3.1. 成本模型
# 是什么
成本模型 属于 MySQL 性能分析与优化证据。它帮助判断问题发生在 SQL 写法、优化器估算、索引路径、内存缓存、CPU、IO 还是锁等待。
# 开发人员怎么用
开发人员要在改 SQL 前保留基线：原 SQL、参数、执行计划、耗时、扫描行数和返回行数。没有基线，优化很容易变成猜测。
# 运维人员怎么看
运维人员要把慢日志、Performance Schema、sys schema、系统 IO/CPU 和应用 trace 对齐到同一时间线。性能问题很少只靠一个指标就能定案。
# 常见风险
常见风险是只看平均耗时或只看是否走索引，忽略统计信息失真、数据倾斜、缓存污染和高并发下的资源争用。
# 3.2. 统计信息
# 是什么
统计信息 属于 MySQL 性能分析与优化证据。它帮助判断问题发生在 SQL 写法、优化器估算、索引路径、内存缓存、CPU、IO 还是锁等待。
# 开发人员怎么用
开发人员要在改 SQL 前保留基线：原 SQL、参数、执行计划、耗时、扫描行数和返回行数。没有基线，优化很容易变成猜测。
# 运维人员怎么看
运维人员要把慢日志、Performance Schema、sys schema、系统 IO/CPU 和应用 trace 对齐到同一时间线。性能问题很少只靠一个指标就能定案。
# 常见风险
常见风险是只看平均耗时或只看是否走索引，忽略统计信息失真、数据倾斜、缓存污染和高并发下的资源争用。
# 3.3. 连接顺序
# 是什么
连接顺序 属于 MySQL 性能分析与优化证据。它帮助判断问题发生在 SQL 写法、优化器估算、索引路径、内存缓存、CPU、IO 还是锁等待。
# 开发人员怎么用
开发人员要在改 SQL 前保留基线：原 SQL、参数、执行计划、耗时、扫描行数和返回行数。没有基线，优化很容易变成猜测。
# 运维人员怎么看
运维人员要把慢日志、Performance Schema、sys schema、系统 IO/CPU 和应用 trace 对齐到同一时间线。性能问题很少只靠一个指标就能定案。
# 常见风险
常见风险是只看平均耗时或只看是否走索引，忽略统计信息失真、数据倾斜、缓存污染和高并发下的资源争用。
# 3.4. 索引选择
# 是什么
索引选择 影响 MySQL 的物理访问路径。InnoDB 数据和索引以页为单位组织，主键索引保存整行，二级索引保存主键并可能触发回表。
# 开发人员怎么用
开发人员要从查询模式设计索引：等值、范围、排序、分组和返回字段要一起考虑。主键要短、稳定、尽量有序，联合索引要遵循最左前缀和选择性原则。
# 运维人员怎么看
运维人员要看执行计划、索引大小、Buffer Pool 命中、写入延迟和未使用索引。索引治理既包括新增，也包括合并和下线。
# 常见风险
常见风险是给每个字段都建索引，读查询短期改善，写入、空间、缓存和优化器选择长期恶化。
# 3.5. 谓词下推
# 是什么
谓词下推 是 SQL 语言和优化器执行模型的一部分。SQL 是声明式语言，写法会影响优化器能否下推过滤、利用索引、减少排序和临时表。
# 开发人员怎么用
开发人员要写可优化的 SQL：字段类型匹配，避免在索引列上包函数，JOIN 条件完整，分页和排序有索引支撑。复杂 SQL 要先拆解数据流。
# 运维人员怎么看
运维人员要通过慢日志、执行计划、临时表指标、排序指标和 CPU/IO 观察 SQL 成本。报表查询应与在线交易隔离。
# 常见风险
常见风险是把所有计算塞进一条 SQL，测试数据没问题，生产数据一大就拖垮主库。
# 3.6. 覆盖索引
# 是什么
覆盖索引 影响 MySQL 的物理访问路径。InnoDB 数据和索引以页为单位组织，主键索引保存整行，二级索引保存主键并可能触发回表。
# 开发人员怎么用
开发人员要从查询模式设计索引：等值、范围、排序、分组和返回字段要一起考虑。主键要短、稳定、尽量有序，联合索引要遵循最左前缀和选择性原则。
# 运维人员怎么看
运维人员要看执行计划、索引大小、Buffer Pool 命中、写入延迟和未使用索引。索引治理既包括新增，也包括合并和下线。
# 常见风险
常见风险是给每个字段都建索引，读查询短期改善，写入、空间、缓存和优化器选择长期恶化。
# 3.7. 临时表
# 是什么
临时表 是 SQL 语言和优化器执行模型的一部分。SQL 是声明式语言，写法会影响优化器能否下推过滤、利用索引、减少排序和临时表。
# 开发人员怎么用
开发人员要写可优化的 SQL：字段类型匹配，避免在索引列上包函数，JOIN 条件完整，分页和排序有索引支撑。复杂 SQL 要先拆解数据流。
# 运维人员怎么看
运维人员要通过慢日志、执行计划、临时表指标、排序指标和 CPU/IO 观察 SQL 成本。报表查询应与在线交易隔离。
# 常见风险
常见风险是把所有计算塞进一条 SQL，测试数据没问题，生产数据一大就拖垮主库。
# 3.8. 排序优化
# 是什么
排序优化 是 SQL 语言和优化器执行模型的一部分。SQL 是声明式语言，写法会影响优化器能否下推过滤、利用索引、减少排序和临时表。
# 开发人员怎么用
开发人员要写可优化的 SQL：字段类型匹配，避免在索引列上包函数，JOIN 条件完整，分页和排序有索引支撑。复杂 SQL 要先拆解数据流。
# 运维人员怎么看
运维人员要通过慢日志、执行计划、临时表指标、排序指标和 CPU/IO 观察 SQL 成本。报表查询应与在线交易隔离。
# 常见风险
常见风险是把所有计算塞进一条 SQL，测试数据没问题，生产数据一大就拖垮主库。
# 3.9. 本篇学习实验
建议准备一个至少百万行级别的测试表，分别观察正常查询、缺失索引、错误索引、长事务、锁等待和慢查询日志。每次实验都记录 SQL、执行计划、耗时、扫描行数和 MySQL 状态变量变化。
开发侧实验重点是理解 SQL 与事务边界，运维侧实验重点是理解指标如何变化。真正掌握本篇内容，应该能从现象反推 SQL、索引、锁、日志或存储引擎问题。
# 4. 核心机制
- 优化器通过估算 IO、CPU、扫描行数和选择性来比较不同计划。
- 统计信息不准会导致错误索引选择，数据倾斜场景尤其常见。
- 连接顺序决定中间结果大小，小驱动表和高选择性条件通常更有利。
- 谓词下推、索引下推和派生表合并可以减少无效数据进入上层执行。
# 5. 工程实践
- 优化 SQL 先减少数据范围，再减少回表，再减少排序和临时表。
- 高频查询建立合适联合索引，低频报表考虑异步化或数仓化。
- 定期分析表统计信息，重大数据变化后关注计划漂移。
- 必要时可以用 hint 稳定计划，但要记录原因和退出机制。
# 6. 常见坑
- 把一个复杂 SQL 拆成很多小 SQL 后造成 N+1 查询。
- 强制索引后数据分布变化，原本的优化变成负担。
- 为了一个低频查询增加宽索引，拖慢大量写入。
- 忽略应用侧连接池和网络往返，导致数据库优化收益被抵消。
# 7. 专家视角
- 优化器研究要关注计划稳定性，而不仅是单次执行速度。
- 专家会建立 SQL 基线和回归测试，避免版本升级或统计变化引起性能回退。
- 有些问题不该由 SQL 优化解决，而应调整业务流程、缓存、异步化或数据产品形态。
# 8. Tips 快问快答
Q：SQL 优化第一步是什么？
A：明确慢在哪里：扫描多、回表多、排序多、锁等待，还是连接资源不足。
Q：hint 能不能用？
A：能，但要谨慎。hint 会绕过部分优化器判断，后续数据变化可能变成风险。
Q：优化器为什么会选错索引？
A：成本估算依赖统计信息和模型，数据倾斜或统计陈旧时可能误判。
# 9. 阶段小结
SQL优化与优化器 的学习重点不是记住零散概念，而是把它放回真实系统：数据如何进入、如何存储、如何被查询、如何在并发下保持正确、如何在故障后恢复。掌握这些连接关系，才能从“会用”走向“能设计、能优化、能排障”。