为什么这个MySQL查询非常缓慢？

Question

给出一个名为“orders_products”的MySQL表格，其中包含以下相关字段：

• products_id（产品ID）
• orders_id（订单ID）

这两个字段都建有索引。

我正在运行以下查询：

SELECT products_id, count( products_id ) AS counter
FROM orders_products
WHERE orders_id
IN (
  SELECT DISTINCT orders_id
  FROM orders_products
  WHERE products_id = 85094
)
AND products_id != 85094
GROUP BY products_id
ORDER BY counter DESC
LIMIT 4

这个查询非常耗时，大约需要20秒。除此之外，数据库并不是很忙碌，并且在其他查询上表现良好。

我想知道，是什么导致查询如此缓慢？

这个表相当大（大约有150万行，大小约为210 mb），这可能是内存问题吗？

有没有办法精确地告诉我SQL为何如此缓慢？

Explain的输出：

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     orders_products     range   products_id     products_id     4   NULL    1577863     Using where; Using temporary; Using filesort
2   DEPENDENT SUBQUERY  orders_products     ref     orders_id,products_id   products_id     4   const   2   Using where; Using temporary

- Majiy

---

EXPLAIN是什么？你能发布explain的输出吗？ - Rup

你在orders_id和products_id上有索引吗？ - bizzehdee

提供表结构，包括索引和查询目的或预期输出。 - Minesh

orders_id和products_id都有索引。在问题中添加了explain的输出。 - Majiy

啊，我的老朋友“Using filesort”。尝试移除“ORDER”语句看是否能帮助解决问题，至少可以用作问题隔离的一种方式。 - Rup

Answer 1

使用 WHERE ID IN (子查询) 的查询在 mysql 中性能通常很差。

然而，在大多数这样的查询中，可以将它们重写为 JOIN，这个也不例外：

SELECT
    t2.products_id,
    count(t2.products_id) AS counter
FROM orders_products t1
JOIN orders_products t2
    ON t2.orders_id = t1.orders_id
    AND t2.products_id != 85094 
WHERE t1.products_id = 85094
GROUP BY t2.products_id
ORDER BY counter DESC
LIMIT 4

如果您想返回没有其他产品的行（并显示零计数），请将连接更改为“LEFT JOIN”。

请注意，表的第一个实例具有“WHERE products_id = X”，这允许索引查找并立即减少行数，而表的第二个实例具有目标数据，但它在id字段上进行查找（再次很快），但在连接条件中进行了过滤以计算其他产品的数量。

- Bohemian

---

运行得非常好，现在查询几乎不需要时间。 - Majiy

没关系。顺便说一下，你对“group by”和“order by”的编辑是不必要的——无论哪种方式都可以正确执行：SQL标准为这些子句提供了对列位置（而不是列表达式）的引用。 - Bohemian

我同意在实际所有MySQL数据库中，*WHERE ID IN (子查询)*的性能都是臭名昭著的。 - ypercubeᵀᴹ

@ypercube 在您看来，WHERE IN()在哪些数据库中表现良好？ - Bohemian

SQL-Server 处理 IN 子查询没有问题。关于 Postgres 和 Oracle，我不确定，但我没有看到任何表明性能不佳的东西，“声名狼藉的糟糕”听起来真的很糟糕。 - ypercubeᵀᴹ

@ypercube 已经适当编辑，不再用 mysql 刷子涂抹其他数据库。 - Bohemian

Answer 2

请尝试以下内容：

1. MySQL不会优化带有子查询的IN语句 - 请将表连接在一起。
2. 您的查询包含!=条件，这非常难处理 - 您能否缩小产品范围并使用多个查找而不是不等式比较？

- user972946