SQL查询性能问题(多个子查询) [英] SQL query performance question (multiple sub-queries)

问题描述

我有这个查询:

SELECT p.id, r.status, r.title
FROM page AS p
    INNER JOIN page_revision as r ON r.pageId = p.id AND (
        r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId AND r2.status = 'active')
        OR r.id = (SELECT MAX(r2.id) from page_revision as r2 WHERE r2.pageId = r.pageId)
    )

这将返回每个页面以及每个页面的最新活动修订，除非没有活动修订可用，在这种情况下，它只会返回最新修订.

Which returns each page and the latest active revision for each, unless no active revision is available, in which case it simply returns the latest revision.

是否可以通过任何方式对其进行优化以提高性能，或者仅仅是提高可读性?我现在没有任何问题，但是我担心的是，当它进入生产环境(可能有很多页面)时，它将表现不佳.

Is there any way this can be optimised to improve performance or just general readability? I'm not having any issues right now, but my worry is that when this gets into a production environment (where there could be a lot of pages) it's going to perform badly.

还有，我应该注意任何明显的问题吗?子查询的使用总是让我感到烦恼，但据我所知，没有子查询是无法完成的.

Also, are there any obvious problems I should be aware of? The use of sub-queries always bugs me, but to the best of my knowledge this cant be done without them.

注意:
条件位于JOIN而不是WHERE子句中的原因是，在其他查询(使用相同逻辑)中，我是从站点"表到页面"表的左连接，如果没有页面存在，我仍然希望网站返回.

Note:
The reason the conditions are in the JOIN rather than a WHERE clause is that in other queries (where this same logic is used) I'm LEFT JOINing from the "site" table to the "page" table, and If no pages exist I still want the site returned.

杰克

编辑:我正在使用MySQL

I'm using MySQL

推荐答案

如果"active"是字母顺序中的第一个，则您可以将子查询减少为:

If "active" is the first in alphabetical order you migt be able to reduce subqueries to:

SELECT p.id, r.status, r.title
FROM page AS p
    INNER JOIN page_revision as r ON r.pageId = p.id AND 
        r.id = (SELECT r2.id 
                FROM page_revision as r2 
                WHERE r2.pageId = r.pageId 
                ORDER BY r2.status, r2.id DESC
                LIMIT 1)

否则，您可以用

ORDER BY CASE r2.status WHEN 'active' THEN 0 ELSE 1 END, r2.id DESC

所有这些都是基于我对SQL Server的假设，您对MySQL的追求可能会有所不同.

These all come from my assumptions on SQL Server, your mileage with MySQL may vary.

这篇关于SQL查询性能问题(多个子查询)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！