MySQL-选择关系数据以避免左连接的最快方法 [英] MySQL - Fastest way to select relational data avoiding left join

问题描述

我目前有一个查询，该查询从两个表中选择指标数据，同时让项目从另外两个表中进行查询(一个是拥有的项目，另一个是用户有权访问的项目).

I've currently got a query that selects metrics data from two tables whilst getting the projects to query from two other tables (one is owned projects, the other is projects to which the user has access).

SELECT v.`projectID`,
(SELECT COUNT(m.`session`) 
    FROM `metricData` m
    WHERE m.`projectID` = v.`projectID`) AS `sessions`,
(SELECT COUNT(pb.`interact`)
    FROM `interactionData` pb WHERE pb.`projectID` = v.`projectID` GROUP BY pb.`projectID`) AS `interactions`
FROM `medias` v
LEFT JOIN `projectsExt` pa ON v.`projectsExtID` = pa.`projectsExtID`
WHERE (pa.`user` = '1' OR v.`ownerUser` = '1')
GROUP BY v.`projectID`

它花费的时间太长了1-2秒.显然，这是多重左联接方案.但是，我有一些想法可以提高速度，并且想知道这些想法在原理上是什么.我是否:-

It takes too long, 1-2seconds. This is obviously the multi left-join scenario. But, I've got a couple of ideas to improve speed and wondered what the thoughts were in principle. Do I:-

• 尝试在查询中选择列表，然后获取数据，而不是进行联接.不确定如何运作.
• 在单独的查询中进行选择以获得项目ID，然后在每个项目ID上运行查询.这可能会导致数百个潜在的数千个请求，但对于处理可能更好呢?
• 其他想法?

推荐答案

这里有两个问题:

1. 如何在不到2秒的时间内得到结果
2. 如何避免左联接.

要正确回答#1，必须有更多信息.技术信息(例如此特定查询的解释计划)是一个很好的开始.如果我们拥有您访问的所有表的SHOW CREATE TABLE以及它们包含的行数，那就更好了.

To answer #1 properly there has to be more information. Technical information, such as the explain plan for this particular query is a good start. Even better if we'd have the SHOW CREATE TABLE of all tables that you access, as well as the number of rows they contain.

但是我也希望获得更多功能信息:您要回答的问题到底是什么?现在，您似乎正在看两种不同的媒体:

But I'd also appreciate more functional information: what exactly is the question you're trying to answer? Right now, it seems you're looking at two different sets of medias:

1. projectsExt中没有匹配的行，在这种情况下，medias.ownerUser必须等于"1"("1"应该是字符串btw吗?)
2. 或者在projectsExt中恰好有一个数学行，对于这些行，projectsExt.user必须等于'1'(是'1'应该是字符串btw吗?)

由于缺乏足够的信息来回答#1，我可以回答#2-如何避免左联接".答案是:写出两个集合的一个UNION，一个在哪里有一个匹配项，另一个在没有一个匹配项.

By lack of enough information to answer #1, I can answer #2 - "how to avoid a left join". Answer is: write a UNION of the two sets, one where there is a match and one where there isn't a match.

SELECT v.`projectID`
,      (
       SELECT COUNT(m.`session`) 
       FROM `metricData` m
       WHERE m.`projectID` = v.`projectID`
       ) AS `sessions`
,      (
       SELECT COUNT(pb.`interact`)
       FROM `interactionData` pb
       WHERE pb.`projectID` = v.`projectID`
       GROUP BY pb.`projectID`
) AS `interactions`
FROM ( 
    SELECT      v.projectID
    FROM medias 
    WHERE ownerUser = '1'
    GROUP BY projectID
    UNION ALL
    SELECT      v.projectID
    FROM medias v
    INNER JOIN projectsExt pa
    ON         v.projectsExtID = pa.projectsExtID
    WHERE      v.ownerUser != '1'
    AND        pa.user = '1'
    GROUP BY v.`projectID
) v

这篇关于MySQL-选择关系数据以避免左连接的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！