移除带不等连接的相关标量子查询以优化SQL性能

首先明确原SQL的逻辑：对TestTable中的每一行，计算表内所有Test_type_id与当前行不同的记录总数。原SQL的核心问题是关联标量子查询会逐行执行，数据量较大时会触发大量重复查询，导致性能低下。

原SQL代码：

SELECT
  (SELECT COUNT(0)
   FROM TestTable s_t
   WHERE s_t.Test_type_id <> t.Test_type_id
  ) AS count
FROM TestTable t

优化思路：用预统计替代逐行子查询 #

核心逻辑是：不同类型的记录数 = 表总记录数 - 当前行对应类型的记录数，通过一次全局统计+分组统计，就能批量计算所有行的结果，彻底消除不等连接和逐行子查询。

方案1：使用窗口函数（推荐，简洁高效）

利用窗口函数一次性计算总条数和各类型的分组条数，直接通过算术运算得到结果：

WITH agg_stats AS (
  SELECT
    COUNT(*) OVER () AS total_records,
    Test_type_id,
    COUNT(*) OVER (PARTITION BY Test_type_id) AS type_records
  FROM TestTable
)
SELECT
  total_records - type_records AS count
FROM agg_stats

方案2：独立统计后关联（兼容不支持窗口函数的数据库）

先单独统计总条数和各类型的记录数，再与原表关联计算：

SELECT
  total.total_records - type_stats.type_records AS count
FROM TestTable t
-- 全局统计总条数
CROSS JOIN (SELECT COUNT(*) AS total_records FROM TestTable) total
-- 关联各类型的统计结果
LEFT JOIN (
  SELECT Test_type_id, COUNT(*) AS type_records
  FROM TestTable
  GROUP BY Test_type_id
) type_stats ON t.Test_type_id = type_stats.Test_type_id

性能提升的关键 #

• 消除逐行子查询：原查询是O(n²)的时间复杂度，优化后仅需两次扫描（全局统计+分组统计），时间复杂度降至O(n)
• 移除不等连接：避免了<>条件带来的大量数据匹配和过滤开销
• 提升索引利用率：Test_type_id上的索引会被分组统计直接利用，快速完成类型聚合

内容的提问来源于stack exchange，提问作者VRK