MySQL 重写不带 IN 子句的查询

Question

我有一个由一些java代码生成的大型sql查询。我负责修改这个查询的一小部分。原始查询如下所示:

SELECT
    fields
FROM
    table1, table2, table3
WHERE
    table1.key = table2.key
AND
    table2.key2 = table3.key2
AND
    (field1 = 'x')
AND
    (field2 = 'y')

我的代码区域负责向现有查询附加一个额外的“AND”查询。理想情况下，其他开发人员根本不希望我调整“FROM”子句。

对于 field2 = 'y' 部分，现在有一个新表，其中包含他们想要检查的覆盖值。所以我最初尝试写:

AND
    (field2 = 'y' OR table1.key IN (SELECT key from override_table))

当我运行上述查询时，它运行了超过 12 个小时，必须由 DBA 手动终止。有趣的是， override_table 完全是空的，所以我认为这不是索引问题。我们的数据库服务器是 MySQL 5.0，我们的 IT 部门拒绝将其升级到较新的版本(已多次请求升级但被忽略)。

我确实尝试对查询运行解释，但没有出现任何异常情况。如果我删除“OR table1.key IN”子句，查询将立即返回运行。一位同事告诉我，MySQL 5 很难处理子选择。在 MySQL 5 中，有什么好的方法可以重写 IN 子句来避免子选择吗？或者关于如何优化此查询的任何其他建议？

谢谢。

Answer 1

如果您只能更改 where 子句，请使用:

SELECT
    fields
FROM
    table1, table2, table3
WHERE
    table1.key = table2.key
AND
    table2.key2 = table3.key2
AND
    (field1 = 'x')
AND
    (
        field2 = 'y' 
        OR exists (select 1 from override_table where key = table1.key)
    )

只需确保 override_table.key 已建立索引，table1.key 也已建立索引。当 override_table 中有多个键时，EXISTS 会更快。

如果您可以选择测试查询，请尝试提供 index hints以及explain 到查询，以查看提示和无提示、exists + 提示、in + 提示等之间的性能差异。