不带IN子句的MySQL重写查询

Question

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

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

我的代码区域负责在现有查询中附加一个额外的“和”查询。理想情况下，其他开发人员根本不希望我调整"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很难与筛选者做斗争。有什么好的方法来重写IN子句以避免MySQL 5中的子选择？或关于如何优化此查询的其他建议？

谢谢。

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也是如此。当EXISTS中有几个键时，override_table会更快。

如果您可以选择测试查询，请尝试向查询提供索引提示以及explain，查看提示与不提示、exists +暗示、in +提示等之间的性能差异。