在Firebird中使用IN做复杂过滤时语句挂起该如何解决？

核心原因 #

你遇到的是典型的数据库查询优化器退化问题：绝大多数关系型数据库（MySQL、PostgreSQL、Oracle等）在处理同表IN嵌套子查询时，很容易将独立非相关子查询错误优化为逐行匹配的相关子查询——即外层查询每扫描TABLE1的一行数据，就会重复执行一次IN里的子查询，外层TABLE1数据量越大，执行耗时就会指数级上涨。你单独执行子查询速度快，就是因为此时子查询是独立执行，仅跑一次即可返回结果。

可行解决方案 #

1. 改为JOIN直接操作（优先推荐） #

放弃IN子查询写法，直接用多表关联执行更新/查询，从语法层面避免优化器错误优化，性能最高。
更新语句示例（不同数据库语法差异极小，以下为通用兼容写法）：

UPDATE TABLE1 t1
LEFT JOIN TABLE2 t2 ON t1.ID = t2.IDFIRST
INNER JOIN TABLE3 t3 ON t1.ID = t3.IDTABLE1
SET t1.MYFIELD = 'hello'
WHERE t2.IDTABLE1 IS NULL 
AND t2.SOMEFIELD = 0 
AND t3.OTHERFIELD IS NULL;

查询语句同理直接关联查询即可，无需嵌套。

2. 强制子查询物化 #

如果必须保留IN写法，可以在子查询外再套一层临时派生表，强制数据库先执行完子查询把结果集缓存为临时表，再和外层TABLE1做匹配：

UPDATE TABLE1 SET MYFIELD='hello' 
WHERE ID IN (
  SELECT id FROM (
    SELECT TABLE1.ID 
    FROM TABLE1 
    LEFT JOIN TABLE2 ON TABLE1.ID=TABLE2.IDFIRST 
    INNER JOIN TABLE3 ON TABLE1.ID=TABLE3.IDTABLE1 
    WHERE TABLE2.IDTABLE1 IS NULL 
    AND TABLE2.SOMEFIELD=0 
    AND TABLE3.OTHERFIELD IS NULL
  ) AS temp_result
)

3. 补全关联、过滤字段索引 #

确认以下索引已创建，进一步提升查询/更新性能：

• TABLE1的ID字段（主键默认自带索引，如未设主键需单独创建）
• TABLE2创建联合索引：INDEX idx_t2 (IDFIRST, SOMEFIELD, IDTABLE1)，覆盖关联和过滤条件
• TABLE3创建联合索引：INDEX idx_t3 (IDTABLE1, OTHERFIELD)，覆盖关联和过滤条件

4. 排查锁等待问题 #

如果以上优化做完仍然挂起，可检查当前数据库是否存在未提交的写事务锁定了TABLE1的相关行，导致更新/查询一直等待锁释放。

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