第四章：查询执行与优化器 — 数据库的大脑

1. 定义

查询优化器 (Query Optimizer) 是数据库内核中最复杂的部分。它的任务是将用户的声明式 SQL（告诉数据库要什么）转换为物理执行计划（告诉数据库怎么做）。

现代数据库大多使用 CBO (Cost-Based Optimization)，即计算所有可能的执行路径（索引扫描、全表扫描、嵌套循环连接等）的“成本”，并选择成本最低的一条。成本通常以 I/O 次数和 CPU 指令数为单位。

2. 技术深度：选择性与基数

优化器如何知道哪个索引更快？

• 选择性 (Selectivity)：列中唯一值占总行数的比例。选择性越高（唯一值越多），索引效率通常越高。
• 基数 (Cardinality)：列中不重复值的估算数量。

如果 status 列只有 ‘active’ 和 ‘inactive’ 两个值（选择性低 / 基数=2），在千万级表中，使用索引查找 ‘active’ 行可能需要回表 500 万次。此时，CBO 会判断全表扫描 (Sequential Scan) 比索引扫描更高效。

3. 可视化：SQL 执行管道

flowchart TD
    SQL[SQL 语句] --> Parser[解析器 (Parser)]
    Parser --> AST[抽象语法树]
    AST --> Rewriter[重写器 (视图展开/常量折叠)]
    Rewriter --> Optimizer[优化器 (CBO)]
    
    Optimizer --"统计信息 (Statistics)"--> PlanA[计划 A: 索引扫描]
    Optimizer --"成本计算"--> PlanB[计划 B: 全表扫描]
    
    Optimizer --> Executor[执行器]
    Executor --> Engine[存储引擎 (InnoDB)]
    Engine --> Data[数据返回]

4. 真实案例：GitLab 生产环境慢查询事故

背景：GitLab.com 曾遭遇严重的数据库性能降级，某些简单的查询耗时超过 30 秒。 原因：PostgreSQL 的统计信息 (Statistics) 过时。

深度解析：

1. 某张大表通过 DELETE 删除了大量数据，但 Auto-analyze（自动统计进程）尚未触发。
2. Postgres 的统计直方图仍然认为该表有数百万行数据，且目标列的分布非常均匀。
3. 优化器基于错误的统计信息，错误地选择了 Nested Loop Join 而不是 Hash Join。
4. 这导致了数亿次的循环查询，瞬间耗尽了 CPU 资源。

教训：SQL 性能不只取决于 SQL 写法，更取决于数据库对数据的“认知”。定期 ANALYZE 是运维的生命线。

5. 深度优化与纵深防御

A. 理解执行计划 (EXPLAIN)

不要猜测，使用 EXPLAIN (ANALYZE) 查看真实的执行路径。

• 访问类型 (Access Type)：关注 type 字段。ALL (全表) 最差，const / eq_ref 最好。
• Rows Examined：扫描行数 vs 返回行数。如果扫描 100 万行只返回 1 行，说明索引无效。

B. 强制索引 (Index Hint)

作为最后的手段（慎用）。

• 在 MySQL 中使用 FORCE INDEX (idx_name) 告诉优化器：“我相信这个索引更好”。
• 风险：数据分布变化后，强制索引可能变成最差选择。

C. 复合索引的最左前缀原则

优化器只能利用复合索引的最左边部分。

• 索引 (a, b, c) 可以支持 a=? 和 a=? AND b=?。
• 但不支持 b=? 或 c=?。这就像电话簿：你不能在不知道姓氏的情况下直接查名字。

6. 参考资料

• PostgreSQL Documentation: Planner/Optimizer
• MySQL Explain Output Format
• GitLab Post-Mortem: Database Incident