mysql - 哪个子查询更快？

Question

(可能重复，但我只能通过 JOIN [3] 找到问题和解决方案，这不是一个选项。)

我有两张 table 。非常细(几列)和很长(多行)。一种是数据表(articles)，一种是ACL表(acl)。

我只想显示我可以通过 acl.some_id 访问的文章。哪个子查询更快？

[1]
SELECT a.title
FROM articles a
WHERE 0 < (
  SELECT COUNT(1)
  FROM acl
  WHERE article_id = a.id AND some_id IN (1, 2, 3)
)

或

[2]
SELECT a.title
FROM articles a
WHERE a.id IN (
  SELECT article_id
  FROM acl WHERE some_id IN (1, 2, 3)
)

我的想法是第二个，因为那个子查询可以被所有可能匹配的行重用，所以只会执行一次(虽然结果集会非常大)，而第一个子查询必须检查对于每个可能匹配的行。

还有第三种方法，但这不是一个选项，因为它会重复行(并且 GROUP BY 不是解决方案，因为我以后需要一个 COUNT 来处理其他事情(而 DISTINCT 永远不是解决方案!)):

[3]
SELECT a.title
FROM articles a
JOIN acl
  ON acl.article_id = a.id
WHERE acl.some_id IN (1, 2, 3)

由于 article_id X 在 acl 中存在 N 次，它会返回该行 0 - N 次而不是 0 - 1 次。

还有第四种方式:EXISTS。感谢 ypercube。

相关:

• Subqueries vs Inner joins - Which one executes faster?

Answer 1

我会说 [2] ，也是，但是MySQL在优化方面有一些盲点IN子查询，至少达到 5.5。在(新发布的)5.6 版本中，查询优化器有几项改进。您可以在 MySQL 文档中阅读有关(半连接和 IN 子查询)的信息: MySQL 5.6: Optimizing Subqueries with Semi-Join Transformations .

MariaDB(5.3 和 5.5 版本)中的优化器也有一些改进，其中一些与此类查询有关。您可以在他们的文档中阅读: MariaDB 5.3: Semi-join subquery optimizations 。

您也可以尝试 EXISTS版本，特别是如果您使用的是 5.5 或更旧版本:

-- [4]
SELECT id
FROM articles AS a
WHERE EXISTS (
  SELECT *
  FROM acl 
  WHERE acl.some_id IN (1, 2, 3)
    AND acl.article_id = a.id 
) ;

我认为 (article_id, some_id) 上有一个索引在这里会有用 - 或者可能相反，两者都尝试一下也没什么坏处。

---

如果有来自acl (article_id) REFERENCES article (id)的外键可以信任的，只需要文章ID，也可以单表取数据:

SELECT DISTINCT article_id
FROM acl 
WHERE acl.some_id IN (1, 2, 3) ;

---

当然，您应该测试服务器中的多个版本、您拥有(或计划使用)的 MySQL 版本、数据分布，当然还有足够大的表。测试几百行不会告诉你太多。