PostgreSQL与MySQL在复杂查询下的性能对决

如果你正在设计一个需要频繁执行多表JOIN、子查询、窗口函数或者递归查询的系统，选错数据库的代价不是慢一两秒，而是可能直接导致查询超时、CPU飙满。PostgreSQL和MySQL在简单CRUD场景下差距不大，一旦进入复杂查询领域，两者的架构差异会被急剧放大。我们直接看它们到底差在哪里，以及各自适合什么场景。

复杂查询的性能首先取决于优化器能不能找到高效的执行路径。PostgreSQL使用基于成本的优化器，并且支持遗传查询优化算法来处理极多表JOIN的情况。当你写一个涉及七八张表的查询时，PostgreSQL会动态调整优化策略，不会因为穷举所有可能的JOIN顺序而耗尽规划时间。MySQL在5.7及之前版本对多表JOIN的优化深度明显不足，8.0版本虽有改进，但面对超过一定数量表的JOIN时，优化器仍然倾向于采用相对固定的启发式规则，容易选错索引或JOIN顺序。

一个实际的表现是：在PostgreSQL中你可以放心使用公共表表达式，优化器会把CTE当作优化边界，也可以从12版本开始通过MATERIALIZED或NOT MATERIALIZED显式控制。MySQL在8.0才引入CTE，但优化器对CTE的处理更倾向于总是物化，这在某些需要将CTE结果集下推到外层查询条件时反而成为性能瓶颈。这意味着同样的CTE写法，在PostgreSQL中可能被内联优化，在MySQL中却可能产生巨大的临时表开销。

复杂查询通常涉及大量数据的扫描和聚合，并行查询能力直接决定响应时间。PostgreSQL从9.6版本开始支持并行顺序扫描、并行聚合、并行JOIN，到15、16版本已经支持并行全外连接和并行哈希连接。更重要的是，PostgreSQL的并行查询可以同时利用多个CPU核心处理单个查询，并且并行工作者之间的数据交换通过动态共享内存段高效完成。

MySQL的并行查询直到8.0才以有限形式出现，且仅对SELECT COUNT(*)或全表扫描类操作生效，对复杂JOIN和子查询的并行支持非常有限。InnoDB引擎的架构决定了它在单个查询内部的多线程协作上存在天然瓶颈，其并行执行模型更依赖连接级别的并发而非查询内部的并行。当你面对一个需要对千万级数据进行多表关联和聚合的分析型查询时，PostgreSQL能把查询时间从分钟级压缩到秒级，MySQL则可能让你不得不考虑分库分表或外部计算引擎。

复杂查询往往依赖多种索引类型的组合。PostgreSQL支持B-tree、Hash、GiST、GIN、SP-GiST、BRIN等多种索引，其中GIN索引对JSONB字段的复杂嵌套查询至关重要，BRIN索引对时序数据的范围查询极其高效。更关键的是，PostgreSQL支持部分索引、表达式索引和覆盖索引，你可以为WHERE条件中特定取值创建部分索引，也可以为函数计算结果创建索引，这些在复杂查询中能产生数量级的性能提升。

MySQL的InnoDB主要依赖B+树索引，8.0引入了函数索引但功能仍不如PostgreSQL的表达式索引灵活。MySQL的索引条件下推和MRR特性在特定场景下有效，但当查询涉及多个复杂条件组合、需要对JSON内部字段进行过滤时，PostgreSQL的GIN索引配合jsonb_path_ops操作符类能实现亚毫秒级的查找，MySQL则需要通过虚拟列加索引的方式间接实现，增加了存储和维护成本。

NOT EXISTS、NOT IN这类反连接在复杂查询中非常常见。PostgreSQL的优化器能将NOT EXISTS自动转换为反连接，并且支持哈希反连接和合并反连接两种算法。当子查询返回大量数据时，哈希反连接的性能远优于嵌套循环。MySQL 8.0也开始支持反连接优化，但触发条件较为严格，查询写法稍有变化就可能退化为相关子查询的逐行执行。

举个例子，当你要找出在订单表中没有退货记录的所有用户时，下面这条查询在PostgreSQL中会被优化为高效的哈希反连接：

SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM returns r WHERE r.user_id = u.id
);

在MySQL中，如果returns表数据量大且缺乏合适索引，这条查询可能执行得非常慢。实际测试中，在千万级数据规模下，PostgreSQL的哈希反连接比MySQL的相关子查询执行方式快5到10倍。

窗口函数是复杂分析查询的核心工具。PostgreSQL对窗口函数的支持非常完整，支持RANGE和ROWS两种窗口框架，支持多个窗口定义在同一个查询中复用，并且窗口函数的执行优化得相当成熟。你可以写嵌套窗口函数，可以在窗口定义中使用FILTER子句，这些特性让复杂的数据分析查询可以直接在数据库层完成。

MySQL从8.0开始支持窗口函数，但实现上仍有差距。MySQL的窗口函数目前不支持RANGE模式的完全灵活定义，不支持GROUPS窗口框架，也不支持在同一个查询中多个窗口函数共享窗口定义时的某些优化。在大数据量下，PostgreSQL的窗口聚合通常比MySQL消耗更少的内存和临时表空间，因为PostgreSQL可以利用索引排序结果避免额外的排序操作。

处理树形结构、组织架构、物料清单等递归查询场景时，PostgreSQL的递归CTE实现非常稳定，支持递归深度控制、循环检测，并且递归部分的优化器能有效利用索引。MySQL的递归CTE在8.0引入，但默认递归深度限制为1000，且对递归查询中的索引利用不够智能，容易出现全表扫描导致的性能骤降。

实际场景中，一个五层的组织架构树遍历，在PostgreSQL中即使数据量达到百万级也能在百毫秒内完成，而MySQL在同样数据量下可能因为临时表写入和反复扫描导致秒级的响应时间。如果你的业务涉及复杂的层级数据查询，PostgreSQL的递归查询能力是一个决定性的优势。

复杂查询经常需要超出work_mem的排序或哈希操作，这时数据库会将溢出数据写入临时文件。PostgreSQL允许你为每个会话单独设置work_mem，并且对于大型查询的并行工作者也有独立的内存分配参数。你可以根据查询复杂度动态调整这些参数，让复杂查询获得足够的内存而不会影响其他简单查询。

MySQL的sort_buffer_size和join_buffer_size是会话级别的分配，但MySQL的内存管理在复杂查询场景下更容易出现膨胀，因为join_buffer_size是按每个JOIN分配的，一个多表JOIN查询可能同时分配多个join buffer，导致实际内存消耗远超预期。在并发复杂查询场景下，MySQL更容易触发OOM，而PostgreSQL的内存控制更加精细和可预测。

如果你的业务以复杂查询为主，比如BI报表、数据分析、实时数仓、GIS应用、全文搜索与结构化查询混合等场景，PostgreSQL的综合表现明显优于MySQL。它的优化器更智能、并行能力更强、索引类型更丰富、对复杂SQL标准的支持更完整。MySQL的优势在于简单查询的高并发吞吐和成熟的主从复制生态，一旦查询复杂度上升，其架构层面的限制就会暴露出来。

对于已经在使用MySQL但面临复杂查询瓶颈的团队，可以考虑将分析型查询迁移到PostgreSQL只读副本，或者通过逻辑复制将部分数据同步到PostgreSQL专门处理复杂查询。这种异构数据库组合在业界已经相当普遍，能兼顾MySQL的事务处理优势和PostgreSQL的分析查询能力。

最后需要强调的是，没有任何数据库能在所有场景下胜出。PostgreSQL在复杂查询下的优势建立在正确的索引设计、合理的参数配置和良好的查询编写习惯之上。即使选择了PostgreSQL，糟糕的表结构设计和缺失索引同样会让复杂查询变得不可接受地慢。数据库的选择只是起点，持续的优化和监控才是保证性能的关键。