SQL嵌套查询性能基准测试_不同引擎下的表现差异

MySQL 8.0 的 IN 嵌套子查询默认走嵌套循环，而 PostgreSQL 15 默认用 hash semi-join，故性能差约3倍；需 MySQL 显式提示 MATERIALIZE 或改写为 JOIN，PG 则需防 LIMIT 导致退化为 nestloop。MySQL 8.0 vs PostgreSQL 15 的 IN 嵌套子查询执行速度为什么差 3 倍？因为 MySQL 在多数情况下无法对 IN (SELECT ...) 子查询做物化（materialization），默认走嵌套循环（NLJ），而 PostgreSQL 15 默认启用 hash semi-join，直接把子查询结果哈希化后一次匹配。尤其当外层表大、子查询结果小（比如 SELECT id FROM users WHERE status = 'active'）时，PostgreSQL 能跳过大量扫描。MySQL 8.0 需显式加 /*+ MATERIALIZE */ 提示（仅限优化器提示支持的版本），或改写为 JOIN + GROUP BYPostgreSQL 中若子查询含 LIMIT 或不可下推的表达式，hash semi-join 可能退化为 nestloop semi-join，用 EXPLAIN (ANALYZE) 看实际计划里的 Join Filter 是否出现测试时务必关掉查询缓存（MySQL 的 query_cache_type=0；PG 无全局缓存，但注意 shared_buffers 预热状态）SQLite 的 EXISTS 嵌套比 IN 快，但只在单表子查询成立SQLite 的查询规划器对 EXISTS 更激进地尝试使用索引查找（index lookup），而 IN 在子查询返回多列或含表达式时容易触发全表扫描。不过这个优势只在子查询本身不带 JOIN 或聚合的前提下稳定存在。如果子查询是 SELECT user_id FROM orders WHERE amount > 100，EXISTS 大概率走 user_id 索引；换成 IN 就可能变成先执行子查询再哈希匹配，而 SQLite 的哈希表实现较轻量，但内存受限时反而更慢一旦子查询含 GROUP BY 或窗口函数，SQLite 会强制走临时表，此时 EXISTS 和 IN 性能趋同，甚至更差（因多一次 EXISTS 判定开销）实测中，PRAGMA temp_store = MEMORY 对嵌套查询提速明显，但超过 temp_store_directory 限制后会静默切回磁盘，导致性能陡降ClickHouse 的 GLOBAL IN 网络放大问题怎么避免？GLOBAL IN 会把右子查询结果广播到所有分片节点，如果子查询返回 10 万行，集群有 20 个分片，就产生 200 万行网络传输 —— 这不是延迟问题，是带宽和内存双重瓶颈。普通 IN（本地）则只在当前节点执行子查询，但要求数据分布键与子查询字段一致。 There’s An AI For That 全球领先的 AI 聚合器，收集10,225个AI工具，可用于超过2,548个任务。