我用 EXISTS 把一条 SQL 从 18 秒优化到 6 秒，同事以为我改了索引

shengjk1
0 阅读9分钟

你好，我是 shengjk1，多年大厂经验，努力构建 通俗易懂的、好玩的编程语言教程。 欢迎关注！你会有如下收益：

  1. 了解大厂经验
  2. 拥有和大厂相匹配的技术等

希望看什么，评论或者私信告诉我！

故事从一条"跑得很慢"的 SQL 开始

线上报警，日志查询超时。我接手一看，是这段 SQL：

SELECT message
FROM   ods_log_sby_biz_log_di_realtime
WHERE  tid IN (
    SELECT DISTINCT tid
    FROM   ods_log_sby_biz_log_di_realtime
    WHERE  timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
    AND    container_name = 'lijianjin'
    AND    message LIKE '%发起商家转账-start%'
)
AND timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
AND container_name = 'lijianjin'

需求本身很简单： 找出所有包含"发起商家转账-start"关键字的线程 ID（tid），把该 tid 下的全部日志都捞出来，方便链路追踪。

逻辑没问题，结果也正确，但在千万级数据下跑了 18 秒。

我没有加任何索引，只改了一种写法，直接降到 6 秒。

同事问我改了什么，我说：把 IN 换成了 EXISTS。

他沉默了三秒，然后说："这东西有这么大区别？"

有。而且原理不复杂，看完这篇你就懂了。

先说结论，再讲原理

对比维度IN + DISTINCTJOIN + DISTINCTEXISTS
子查询是否全量执行✅ 必须全量✅ 必须全量❌ 找到即停（短路）
是否需要去重✅ 需要 DISTINCT✅ 需要 DISTINCT❌ 不需要
匹配方式逐个比较Hash O(1) 碰撞短路匹配
内存占用高中低
数据量越大时越慢较快最快
综合推荐⭐⭐⭐⭐⭐⭐⭐⭐⭐⭐

测试数据

为了把执行过程讲清楚，先构造一张简化的日志表：

tidcontainer_namemessagetimestamp备注
T1lijianjin发起商家转账-start2026-03-18 10:00关键入口日志
T1lijianjin转账处理中2026-03-18 10:01中间过程
T1lijianjin转账完成2026-03-18 10:02结束日志
T2lijianjin发起商家转账-start2026-03-18 11:00关键入口日志
T2lijianjin转账失败2026-03-18 11:01结束日志
T3lijianjin其他业务日志2026-03-18 12:00无关记录

预期结果： T1（3条）+ T2（2条），共 5 条。T3 无关键字，过滤掉。

方式一：IN + DISTINCT

SQL 写法

SELECT message
FROM   ods_log_sby_biz_log_di_realtime
WHERE  tid IN (
    SELECT DISTINCT tid
    FROM   ods_log_sby_biz_log_di_realtime
    WHERE  timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
    AND    container_name = 'lijianjin'
    AND    message LIKE '%发起商家转账-start%'
)
AND timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
AND container_name = 'lijianjin'

执行步骤

第一步：执行子查询，全量扫描

数据库把整张表（或分区）从头到尾扫一遍，对每一行检查 WHERE 条件。满足条件的行收集其 tid，此时可能包含重复值：

扫描结果（含重复）：[T1, T2, T1, T2, ...]

注意：子查询必须完整跑完，不能提前停止。

第二步：DISTINCT 去重

对第一步结果执行去重，得到：

去重后：[T1, T2]

这是一次额外的排序或哈希去重计算，tid 基数越大，开销越显著。

第三步：外层逐行匹配 IN 列表

外层扫描主表，对每一行的 tid 逐个与 [T1, T2] 比对：

T1 - 发起商家转账-start  → tid=T1，在列表里 ✅ 保留
T1 - 转账处理中          → tid=T1，在列表里 ✅ 保留
T1 - 转账完成            → tid=T1，在列表里 ✅ 保留
T2 - 发起商家转账-start  → tid=T2，在列表里 ✅ 保留
T2 - 转账失败            → tid=T2，在列表里 ✅ 保留
T3 - 其他业务日志        → tid=T3，不在列表 ❌ 过滤

第四步：返回 5 条结果。

性能瓶颈在哪

  • 瓶颈一：子查询全量执行。 不管外层最终用多少行，内层都必须跑完。
  • 瓶颈二：DISTINCT 额外开销。 本质是一次 GROUP BY，数据量大时消耗大量 CPU 和内存。
  • 瓶颈三：IN 列表膨胀。 当子查询返回数万个不同 tid 时，IN 列表本身就是个负担。

方式二：JOIN + DISTINCT

SQL 写法

SELECT a.message
FROM   ods_log_sby_biz_log_di_realtime a
INNER JOIN (
    SELECT DISTINCT tid
    FROM   ods_log_sby_biz_log_di_realtime
    WHERE  timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
    AND    container_name = 'lijianjin'
    AND    message LIKE '%发起商家转账-start%'
) b ON a.tid = b.tid
WHERE  a.timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
AND    a.container_name = 'lijianjin'

执行步骤

第一步：执行子查询，同样全量扫描，DISTINCT 去重

与 IN 方式相同，得到小表 b：

b 表：[{tid: T1}, {tid: T2}]

第二步：构建 Hash 表（Build Phase）

优化器通常选择 Hash Join，将小表 b 装入内存 Hash 表：

Hash 表：{ T1: true, T2: true }

Hash 表查找复杂度 O(1)，这是相比 IN 更快的关键。

第三步：外层扫描，Hash 探测（Probe Phase）

扫描主表 a，对每行的 tid 在 Hash 表中碰撞：

T1 → H[T1] 命中 ✅ 保留
T3 → H[T3] 不存在 ❌ 过滤

速度比 IN 的逐个比较快很多。

第四步：返回 5 条结果。

相比 IN 的优势

  • Hash Join 替代线性查找，大数据集下优势明显
  • 优化器可以根据统计信息调整连接顺序，在 Flink/Spark SQL 中效果尤为显著

仍然存在的问题

仍然需要 DISTINCT。 子查询还是得去重，这一步无法省略，是 JOIN 方式最大的短板。

方式三：EXISTS（推荐）

SQL 写法

SELECT a.message
FROM   ods_log_sby_biz_log_di_realtime a
WHERE  a.timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
AND    a.container_name = 'lijianjin'
AND    EXISTS (
    SELECT 1
    FROM   ods_log_sby_biz_log_di_realtime b
    WHERE  b.tid            = a.tid
    AND    b.timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
    AND    b.container_name = 'lijianjin'
    AND    b.message LIKE '%发起商家转账-start%'
)

SELECT 1 是什么意思？

1 只是一个占位常量，写 SELECT * 或 SELECT tid 效果完全一样。EXISTS 只关心子查询「有没有返回行」，根本不在乎返回什么内容，所以写 SELECT 1 是惯用写法，语义清晰，性能也最轻。

执行步骤

这里是 EXISTS 和前两种方式最本质的不同。

第一步：外层取出第一行（T1 - 发起商家转账-start）

拿 tid = T1 带入子查询执行：

扫描 b 表第 1 行：tid=T1，message 含关键字 ✅
→ 立即停止！不再继续扫描 b 表剩余行
EXISTS = TRUE，保留这行

第二步：外层取出第二行（T1 - 转账处理中）

tid 仍为 T1，再次带入子查询：

扫描 b 表第 1 行：命中 ✅ → 立即停止
EXISTS = TRUE，保留

第三步：T1 的第三行同理，保留。

第四步：外层取出第五行（T2 - 转账失败）

tid = T2 带入子查询：

扫描 b 表直到找到 tid=T2 且含关键字的行 → 命中 ✅ → 立即停止
EXISTS = TRUE，保留

第五步：外层取出第六行（T3 - 其他日志）

tid = T3 带入子查询：

扫描 b 表所有行，没有 tid=T3 且含关键字的行
→ 扫完了也没找到
EXISTS = FALSE，过滤掉

第六步：返回 5 条结果。

EXISTS 为什么更快？核心是两个字：短路

短路执行（Short-circuit Evaluation）。 EXISTS 一旦在子查询中找到第一条匹配行，立即返回 TRUE，不再扫描剩余行。

举个极端的例子：tid = T1 的日志有 10 万条，b 表里第 1 行就是 T1 的关键字日志。那么：

  • IN / JOIN： 子查询要把 10 万行全扫完，去重，然后再返回
  • EXISTS： 扫到第 1 行就停了，剩下 99999 行直接跳过

数据量越大，这个差距越夸张。

无需 DISTINCT。 EXISTS 的本质是"存在性检查"，只要找到一条就行，天然不需要去重，省去了排序/哈希的全部开销。

性能基准参考

以下数据基于 1000 万行日志表（tid 基数约 10 万）的模拟测试：

指标IN + DISTINCTJOIN + DISTINCTEXISTS
执行时间18.3 s11.7 s6.2 s
内存峰值1240 MB820 MB310 MB
全表扫描次数2 次（均全量）2 次（均全量）2 次（内层短路）
是否需要去重排序是是否

⚠️ 上述数据为模拟基准，不同引擎（MySQL、Flink SQL、Spark SQL、Hive）因执行计划差异结果会有所不同。建议在实际环境中用 EXPLAIN 验证。

适用场景怎么选？

方式推荐用它当...不推荐用它当...
IN + DISTINCT子查询返回少量固定枚举值；数据量小对性能不敏感子查询返回大量 distinct 值；高并发实时查询
JOIN + DISTINCT需要同时获取关联表的其他字段；优化器 Hash Join 支持好仅需存在性判断（此时 EXISTS 更优）
EXISTS ⭐存在性检查；日志链路追踪；大数据量高频查询；子查询关联字段有索引需要返回关联表字段（用 JOIN 更合适）

选型口诀：

只判断「有没有」→ EXISTS 需要「关联字段」→ JOIN 「枚举少量固定值」→ IN

最终优化 SQL

-- 推荐写法：EXISTS 短路优化
SELECT a.message
FROM   ods_log_sby_biz_log_di_realtime a
WHERE  a.timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
AND    a.container_name = 'lijianjin'
AND    EXISTS (
    SELECT 1
    FROM   ods_log_sby_biz_log_di_realtime b
    WHERE  b.tid            = a.tid
    AND    b.timestamp BETWEEN '2026-03-18 00:00:00' AND '2026-03-19 00:00:00'
    AND    b.container_name = 'lijianjin'
    AND    b.message LIKE '%发起商家转账-start%'
)
ORDER BY a.tid, a.timestamp

总结

三种写法的本质差异只有一句话：

  • IN + DISTINCT： 先把所有符合条件的 tid 全捞出来去重，再去主表匹配。两步都很重。
  • JOIN + DISTINCT： Hash Join 加速了匹配，但去重这步还是省不掉。
  • EXISTS： 以外层驱动，内层找到一条就停，不去重，不全量扫，是纯粹存在性判断的最优解。

下次写 SQL 遇到"某字段在不在另一张表"的场景，先想想能不能换成 EXISTS，可能只是一个关键字的距离，但性能的差距会让你惊喜。

如果这篇文章对你有帮助，欢迎点赞收藏，也欢迎在评论区分享你遇到过的 SQL 慢查询场景。

avatar
文章
阅读
粉丝