标签： IN 子查询

发布于

在 PostgreSQL 中优化含 ‘IN’ 子查询的 SQL 语句有哪些方法 ？

以下是我近期博客中的一条 SQL 语句。有读者询问在子查询的选择列表添加 +0（例如 b.emp_grade+0）对性能的影响。我很欣喜地得知，这个 +0 解决方案大约在 20 年前首次由我提出，用于解决特定子查询问题。不过该方法现已被 COALESCE(b.emp_grade, b.emp_grade) 替代——此举既能规避数据类型检测，又可预防意外错误。

今天，我想进一步探讨利用 IN 子查询优化 SQL 的其他解决方案。

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000)

以下截图展示了由 Tosska SQL Tuning Expert Pro 通过’仅重写’选项和 ‘SQL调优智能=2’ 生成的替代SQL方案。我将分享更多SQL重写方法，全面展示各种可能方案，期待以SQL调优技术的精妙艺术激发读者的探索热情。

以下虽不深入探讨具体SQL重写语句细节，但我很乐意分享其余查询重写方案及其对应的执行计划：

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

发布于

如何对 PostgreSQL 中的“IN 子查询”进行 SQL 调优 ?

以下是一个使用 IN 子查询的 SQL 语句示例。该查询从 grade 表中检索所有 grd_id 匹配 employee 表中 emp_id 大于 3,000,000 的记录的 emp_grade 的数据：

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000)

查询的执行计划显示，该语句耗时 12.6 秒。

该查询计划显示，优化器采用了 嵌套循环（Nested Loop） 连接方式：先对 grade 表进行全表顺序扫描（Sequential Scan），再对 employee 表进行索引扫描（Index Scan）。然而，这种执行逻辑会导致 每个 grade 表的记录都会触发一次针对 employee 表中 emp_id > 3,000,000 条件的索引扫描。虽然 employee 表总记录约 400 万条，但满足 emp_id > 3,000,000 的记录不足 100 万条。

为解决此问题，我通过在子查询中增加 GROUP BY 子句重写了 SQL：

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000
                   group by b.emp_grade)

修改后的查询计划发生以下关键变化：

  • GROUP BY 子句强制子查询优先执行.
  • 通过索引扫描快速筛选出 employee 表中 emp_id > 3,000,000 的记录.
  • 对 emp_grade 列进行分组操作，消除重复值。
  • 将 grade 表与已分组的 emp_grade 子集通过哈希表高效关联。

优化后的 SQL 执行时间降至 0.54 秒，性能显著提升。

优化后的 SQL 语句比原始版本 性能提升超过 23 倍。此类优化也可以通过 Tosska SQL Tuning Expert Pro for PostgreSQL 工具实现。在下方的截图中，虽然展示了其他性能更优的 SQL 改写方案，但受限于篇幅无法在此详述——我们后续可以进一步探讨这一主题。

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited