为什么SQL子查询中的DISTINCT会导致巨大的临时表开销-SQL-PHP中文网

distinct在子查询中并非单纯去重，而是强制触发物化：数据库必须先全量计算、排序或哈希去重，生成临时表，无法下推谓词或limit，导致性能劣化。

为什么sql子查询中的distinct会导致巨大的临时表开销

DISTINCT在子查询里不是去重，是主动触发物化

子查询中加 DISTINCT，数据库不会“聪明地跳过重复”，而是立刻决定：必须先把全部结果拉出来、建哈希表或排序、再筛一遍——哪怕外层只取 1 行。它直接绕过谓词下推，强制把整个中间集物化进临时表。

MySQL 中 SELECT DISTINCT id FROM t WHERE status = 'active'，若 status 无索引，就会全表扫描 + 内存哈希；一旦结果超 tmp_table_size，立刻落盘
PostgreSQL 对子查询里的 DISTINCT 几乎不优化，EXPLAIN ANALYZE 显示 Plan Rows 常是原始表总行数的 80% 以上
SQL Server 把这类子查询标记为 DERIVED，且执行计划里必带 Hash Match (Aggregate) 或 Sort 节点，SpillLevel=1 是常态

为什么外层JOIN也救不了这个子查询

子查询被物化后，就变成一张“静态表”，外层 JOIN 只能拿这张临时表去连，无法把 WHERE 条件下推进去。比如：

SELECT u.* FROM users u
JOIN (SELECT DISTINCT user_id FROM orders WHERE created_at > '2024-01-01') o
  ON u.id = o.user_id

这里子查询先生成几十万行 user_id，哪怕最终 JOIN 只匹配出 300 个用户，也得先造完那张大临时表。

真正高效的写法是把过滤提前：SELECT DISTINCT user_id FROM orders WHERE created_at > '2024-01-01' AND user_id IN (SELECT id FROM users)，让优化器有机会用 users.id 索引反向驱动
如果 orders.user_id 有索引，直接去掉子查询里的 DISTINCT，改用 INNER JOIN ... GROUP BY user_id，让索引覆盖分组过程
Oracle 下可加提示 /*+ PUSH_PRED(o) */ 强制下推，但前提是子查询没用聚合或窗口函数

别信“DISTINCT比GROUP BY轻”这种经验

在子查询上下文中，DISTINCT 和 GROUP BY 底层几乎等价：都需全量数据参与哈希/排序。但 DISTINCT 更危险，因为它不显式暴露分组键，容易掩盖字段组合膨胀问题。

比如 SELECT DISTINCT a, b, c FROM t，若三列联合基数极高（如日志表），哈希表内存占用可能是单列的 10 倍以上
GROUP BY a, b, c 至少能让你意识到要建联合索引 (a,b,c)；而 DISTINCT 容易让人忽略这点，继续用 SELECT * 或乱序字段
MySQL 8.0+ 对单表 DISTINCT 启用哈希聚合，但一旦子查询里出现 JOIN 或函数（如 DATE(created_at)），立刻退化回磁盘排序路径