SQL优化器是数据库将用户编写的查询语句转换为可执行步骤的核心组件。不同数据库在优化器的设计目标、统计信息使用和代价计算方式上有明显分歧，这直接导致同样的SQL在不同引擎中产生完全不同的执行计划。理解这些差异，是进行跨数据库开发和查询性能调优的前提。
一、优化器的基本分类与工作原理
从宏观上看，SQL优化器可分为基于规则（RBO）和基于代价（CBO）两大类。早期数据库多使用RBO，它按照预定优先级重写查询，例如总是优先使用索引而不是全表扫描。这种方式简单可预测，但面对复杂查询时容易选出糟糕计划。现代主流数据库如MySQL和PostgreSQL均以CBO为主，通过估算每一步操作的CPU和IO开销，挑选总代价最小的执行路径。
CBO依赖统计信息来估算结果集行数和数据分布。以PostgreSQL为例，它通过analyze命令收集每一列的最频值、直方图和空值比例，优化器利用这些元数据推算过滤条件后的行数。如果统计信息过期，代价估算就会失真，进而选错索引或连接顺序。因此，优化器差异不仅体现在算法上，也体现在统计信息的维护策略中。
1.1 火山模型与执行算子
多数关系型数据库采用火山模型（Volcano Model），即每个执行算子以迭代器方式向上层吐出元组。优化器生成的计划树由扫描、连接、聚合等算子组成。MySQL的优化器在生成树后，会将其转换为JOIN对象链表；PostgreSQL则保留更完整的计划节点结构。这种内部表示的差异，影响了后续对子查询和公共表表达式的处理能力。
下面是一段伪代码，描述火山模型中一次简单扫描算子的逻辑：
class SeqScan:
def __init__(self, table):
self.table = table
self.cursor = 0
def next(self):
# 每次调用返回一行，没有更多数据时返回None
if self.cursor < len(self.table.rows):
row = self.table.rows[self.cursor]
self.cursor += 1
return row
return None
二、MySQL与PostgreSQL优化器差异对比
虽然两者都是CBO，但MySQL的优化器在连接顺序搜索上较为保守，默认只做有限排列组合，且对子查询倾向于改写为半连接或物化。PostgreSQL则提供遗传查询优化（GEQO）来处理多表连接，当表数量超过阈值时采用概率搜索避免组合爆炸。这使得PostgreSQL在十几张表关联时仍可找到较优顺序，而MySQL可能因搜索空间限制而固定使用左深树。
另一个关键区别是索引使用策略。MySQL的InnoDB二级索引叶子节点存主键，回表代价高，优化器在覆盖索引可用时会强烈偏好它。PostgreSQL的索引类型更丰富，支持BRIN、GIN等，且允许索引-only扫描通过可见性映射避免堆访问。因此在相同查询下，两者对是否走索引的判断阈值不同。
| 对比维度 | MySQL | PostgreSQL |
|---|---|---|
| 连接顺序搜索 | 左深树有限枚举 | 动态规划+遗传算法 |
| 子查询处理 | 半连接/物化改写 | 子计划上拉为主 |
| 统计信息 | 采样估算，默认持久化 | 可配置采样率，直方图细 |
| 并行执行 | 仅部分算子并行 | 计划树广泛并行 |
2.1 代价模型参数差异
MySQL使用基于页的IO估算，顺序读和随机读成本比例由配置参数控制，例如顺序读成本常设为1，随机读为4。PostgreSQL则用顺序页成本、随机页成本、CPU元组处理成本等独立参数，默认随机页成本为4，顺序为1，但还引入CPU开销让计算密集型过滤更被重视。调优时若照搬参数经验，往往会误判。
以下SQL用于在PostgreSQL中查看某表当前统计与估算行数差异：
-- 查看优化器对表行数的估算 EXPLAIN SELECT * FROM orders WHERE create_time > '2023-01-01'; -- 对比真实行数 SELECT count(*) FROM orders WHERE create_time > '2023-01-01';
三、查询策略在业务中的实际影响
当应用从MySQL迁移到PostgreSQL，原本靠联合索引提速的分页查询可能变慢，因为两者对ORDER BY加LIMIT的代价权重不同。PostgreSQL可能选择先全表排序再截取，而MySQL利用索引有序性避免排序。此时需要重建索引或改写查询，例如使用游标式分页代替偏移量分页。
在报表类查询中，PostgreSQL的并行顺序扫描能显著缩短大表聚合时间；MySQL若未配置并行查询，则可能单线程拖慢响应。开发团队应在设计阶段就针对目标库的优化器特性编写查询，而不是事后调参。
3.1 子查询优化实例
MySQL对IN子查询容易生成依赖子查询，导致外层每行都执行一次内层。改写为JOIN后性能飞跃。PostgreSQL通常能自动上拉子查询，但存在相关子查询时仍需人工干预。示例改写如下：
-- MySQL中较慢的写法 SELECT * FROM users WHERE id IN (SELECT user_id FROM logs WHERE log_type = 2); -- 改写为JOIN提升性能 SELECT u.* FROM users u JOIN logs l ON u.id = l.user_id WHERE l.log_type = 2 GROUP BY u.id;
四、如何针对优化器差异做调优
首要动作是收集并阅读执行计划。MySQL用EXPLAIN FORMAT=JSON可看代价细节；PostgreSQL用EXPLAIN (ANALYZE, BUFFERS)能获得真实执行时间与缓存命中。对比估算行数和实际行数，若偏差大，先更新统计信息再重测。
其次，利用数据库专属提示或配置。MySQL可通过optimizer_switch关闭某些启发式；PostgreSQL可用SET enable_seqscan=off做测试，但生产环境应依靠统计而非硬hint。最后，保持SQL简洁，避免多层嵌套视图，让优化器有更大空间重排。
优化器不是黑盒魔法，它的每个选择都能从代价公式和统计分布中找到依据。跨库开发时，把执行计划当作接口文档来读，才能少走弯路。
4.1 统计信息维护建议
对频繁更新的表，MySQL可配置innodb_stats_auto_recalc，PostgreSQL则应安排每日analyze或开启autovacuum主动分析。对数据倾斜严重的列，PostgreSQL可加大统计目标，例如ALTER TABLE t ALTER COLUMN status SET STATISTICS 1000，让直方图更精细。
只有把优化器机制纳入日常设计，才能在复杂业务下维持查询性能稳定。不同数据库的差异不是缺陷，而是各自在工程取舍上的体现，理解它比恐惧它更有价值。