如何优化这个MySQL查询？

Question

请问为什么添加group by子查询会导致这个查询需要花费如此长的时间（30秒）：

SELECT *
FROM aggregate_songlist AS a
INNER JOIN musical_works AS m 
ON a.musical_work_id = m.id
WHERE m.genre='rock' AND m.id NOT IN 
(SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING COUNT(sources.musical_work_id) > 8)

如果我去掉“group by”（并增加子查询的结果），那么它只需要0.07秒：

SELECT *
FROM aggregate_songlist AS a
INNER JOIN musical_works AS m 
ON a.musical_work_id = m.id
WHERE m.genre='rock' AND m.id NOT IN 
(SELECT sources.musical_work_id FROM sources)

子查询中没有外部引用，所以它应该只被执行一次，对吗？ 单独执行它：

SELECT sources.musical_work_id FROM sources GROUP BY sources.musical_work_id HAVING COUNT(sources.musical_work_id) > 8

只需要0.01秒。

有什么解释吗？有没有建议如何改变它？

- user447736

---

“SOURCES”表的表引擎是什么？我最近读到过，MyISAM在使用GROUP BY时性能不佳，而InnoDB则相反。 - OMG Ponies

哪些列上有索引？ - supakeen

将这个按组分组-按计数大于8的查询放入一个临时表中，看看超级查询的行为如何。 - Denis Valeev

Answer 1

在子查询中没有外部引用，所以它应该只被执行一次，对吗？

你可能会这样想，但实际上不是这样的。如果你查看 EXPLAIN，你会发现子查询被称为“DEPENDENT SUBQUERY”，而不是“SUBQUERY”。这意味着它每次都会重新执行。这是 MySQL 5.0 中已知的一个 bug，在 MySQL 6.0 中已修复。

为了解决这个问题，你可以使用其他方法来检查另一个表中是否不存在行。常见的三种方法是 NOT IN、NOT EXISTS 和 LEFT JOIN ... WHERE ... IS NULL，所以你还有两个选项。

- Mark Byers

Answer 2

NOT IN 可能是您的问题所在。尝试改为连接（必须反转 HAVING 子句）：

SELECT *
FROM aggregate_songlist AS a
INNER JOIN musical_works AS m 
ON a.musical_work_id = m.id
LEFT JOIN (
      SELECT sources.musical_work_id FROM sources 
      GROUP BY sources.musical_work_id   
      HAVING COUNT(sources.musical_work_id) <= 8) AS t
ON m.id = t.musical_work_id
WHERE m.genre='rock' AND t IS NULL

[更新以反映@Mark Byers的评论，感谢！]

- Scott Stafford

---

这将不会返回在 a JOIN m 中存在但在 source 中完全不存在的行。使用 LEFT JOIN ... WHERE ... IS NULL 将会纠正这个问题。 - Mark Byers

@Mark Byers：谢谢，我已经更新了我的答案以反映您的建议。 - Scott Stafford

Answer 3

SELECT *
FROM 
aggregate_songlist AS a
INNER JOIN musical_works AS m 
ON a.musical_work_id = m.id
LEFT JOIN (
      SELECT sources.musical_work_id FROM sources 
      GROUP BY sources.musical_work_id   
      HAVING COUNT(sources.musical_work_id) <= 8)
AS t
ON m.id = t.musical_work_id
WHERE
m.genre='rock' AND
t.musical_work_id IS NULL

- Gajendra Bang