帮我优化这个查询

Question

我对我正在设计的应用程序有这样的查询。有一个参考文献表、一个作者表和一个reference_authors 表。有一个子查询返回给定参考文献的所有作者，然后我将其显示为 php 格式。子查询和查询单独运行既漂亮又快速。然而，一旦将子查询放入主查询中，整个过程就需要 120 秒以上才能运行。我希望能有一些新的眼光来看待这个。 谢谢。

SELECT
rf.reference_id,
rf.reference_type_id,
rf.article_title,
rf.publication,
rf.annotation,
rf.publication_year,
(SELECT GROUP_CONCAT(a.author_name)
FROM authors_final AS a
INNER JOIN reference_authors AS ra2 ON ra2.author_id = a.author_id
WHERE ra2.reference_id = rf.reference_id
GROUP BY ra2.reference_id) AS authors
FROM
references_final AS rf
INNER JOIN reference_authors AS ra ON rf.reference_id = ra.reference_id
LEFT JOIN reference_institutes AS ri ON rf.reference_id = ri.reference_id;

这是固定查询。谢谢大家的建议。

SELECT
rf.reference_id,
rf.reference_type_id,
rf.article_title,
rf.publication,
rf.annotation,
rf.publication_year,
GROUP_CONCAT(a.author_name) AS authors
FROM
references_final as rf
INNER JOIN (reference_authors AS ra INNER JOIN authors_final AS a ON ra.author_id = a.author_id)
ON rf.reference_id = ra.reference_id
LEFT JOIN reference_institutes AS ri ON rf.reference_id = ri.reference_id
GROUP BY rf.reference_id

Answer 1

虽然不是每个子查询都可以重写为内部联接，但我认为您可以。

从 120 秒缩短到 78 毫秒，这是一个不错的改进，大约提高了三个数量级。这天剩下的时间请假。

当您明天回来时，开始在源代码中查找其他子查询。

Answer 2

您说子查询在隔离中很好而且速度很快，但它现在显然是针对每一行运行的 - 100 行 = 100 个子查询。

假设您的所有外键都有索引，这与子查询一样好。

一种选择是左连接作者并创建笛卡尔积 - 您将返回更多行，并且需要一些代码才能获得相同的最终结果，但它会给数据库带来更少的压力并且运行速度更快。

如果您已开启分页并返回 10 行，那么发出 10 次单独调用来隔离作者也会非常快。