为何添加NOT IN过滤子句后查询时长暴涨?如何优化排除指定ID的查询
SQL添加NOT IN后性能暴跌原因及优化方案
性能暴跌核心原因
- 非确定性CTE无法复用
people CTE用到rand()非确定性函数,BigQuery优化器不能将两次引用的people CTE合并为一次执行(合并会导致两次随机抽样结果不一致,破坏逻辑正确性)。原查询仅引用一次people CTE,添加NOT IN后相当于额外全量执行一遍「全表扫描、随机排序、取前53370条」的完整流程,原本的并行度和广播join优化路径直接被打乱。 - 过滤逻辑完全冗余
主查询已经通过t1 LEFT JOIN foo ON foo.id = t1.id完成了id匹配,左连接后foo.id IS NULL的行本身就是t1中不在people集合内的记录。额外添加的NOT IN子查询相当于拿t1的每一行去匹配另一次独立抽样生成的id列表,不仅重复计算,还会让优化器退化成低效的嵌套循环匹配。 - 冗余数组结构放大开销
你将单行字段包裹为单元素结构体数组[STRUCT(...)],两次UNNEST解析数组的额外开销,会被非确定性CTE的并行度问题进一步放大,最终导致执行时间从秒级飙升至分钟级。
可落地的优化方案
去掉冗余子查询,直接利用现有左连接结果过滤
不需要额外写NOT IN子查询,直接通过左连接的空值判断实现排除逻辑,同时去掉不必要的数组包裹,省掉UNNEST的解析开销,参考代码:WITH people AS( SELECT rand() AS prob, -- 直接用结构体输出字段,无需包裹成单元素数组 STRUCT( name, address, id, [CAST(FLOOR(19*RAND()) AS INT64), CAST(FLOOR(19*RAND()) AS INT64), CAST(FLOOR(19*RAND()) AS INT64)] AS answers ) AS person FROM `table_id` ORDER BY prob DESC LIMIT 53370 ) SELECT t1.* EXCEPT(col_1, col_2), people.prob, people.person FROM `table_id` t1 -- 直接关联结构体内部的id,无需UNNEST LEFT JOIN people ON people.person.id = t1.id -- 直接过滤掉匹配到people的行,完全实现排除逻辑 WHERE people.person.id IS NULL这个修改完全复用了原有左连接的计算结果,没有额外全量计算,执行时间基本和原查询的3.4秒持平。
若需保留数组结构,用ANTI JOIN替代NOT IN
如果业务必须保留数组的嵌套结构,把NOT IN子查询换成ANTI JOIN,让优化器自动走高效的广播anti join路径,参考代码:WITH people AS( -- 原有people CTE逻辑不变 ), -- 单独抽出id列表的小CTE,优化器会自动识别为小表广播 people_ids AS ( SELECT p.id FROM people, UNNEST(people.person) p ) SELECT t1.* EXCEPT(col_1, col_2), prob, foo.person FROM `table_id` t1 LEFT JOIN ( SELECT * FROM people, UNNEST(people.person) p ) foo ON foo.id = t1.id -- 用ANTI JOIN替代NOT IN,性能远高于NOT IN ANTI JOIN people_ids ON t1.id = people_ids.id强制物化CTE避免重复抽样
如果业务必须两次独立引用people CTE(既要抽样的person信息,又要单独的id列表),给CTE添加强化物化注解,让优化器把第一次执行的结果存为临时表,后续引用直接复用,不会重复执行随机抽样的全流程:WITH people AS /*+ materialize */ ( -- 原有people CTE逻辑不变 )
连接类型调整说明
- 不需要替换现有左连接类型,你的业务逻辑本身需要左连接获取匹配的person信息,仅需通过
WHERE foo.id IS NULL或ANTI JOIN实现排除逻辑即可。 - 如果原表id存在重复导致左连接后数据膨胀,可在关联前对foo的id做去重,避免冗余匹配。
内容的提问来源于stack exchange,提问作者Pedro Pablo Severin Honorato
相关产品推荐
相关产品推荐

