mysql - 丑陋且缓慢的 mysql 查询

Question

我现在正在尝试优化这个有一段时间了。这是一个巨大查询的一小部分摘录，但这就是查询最终导致速度变慢的原因。我想你可以从中得到简单的表结构。有人知道如何做到既优雅又 slim 吗？

SELECT gref.gid AS gid, COUNT(o.id) AS cnt
FROM objects AS o 
RIGHT JOIN group_xref AS gx ON o.id = gx.oid 
LEFT JOIN group_xref AS gref ON o.id = gref.oid 
LEFT JOIN groups AS groups ON gref.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gref.gid

好的，这是解释: http://htmlpaste.com/ff849935d884a36076f74bd934b0212bac7658e9

Answer 1

如果不了解表结构和关系，就很难判断查询在做什么。我想知道以下查询是否产生相同的结果以及它是否运行得更快:

select gref.gid AS gid, COUNT(*) 
from group_xref gref LEFT JOIN
     groups
     ON gref.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gref.gid;

至少，我认为对group_xref的双重连接是不必要的。这是您的查询，没有这个:

SELECT gx.gid AS gid, COUNT(o.id) AS cnt
FROM group_xref gx left join
     objects 0
     ON o.id = gx.oid LEFT JOIN
     groups groups
     ON gx.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gx.gid;

如果我假设对象的链接是 1-many 并且所有对象 id 都是有效的，那么这可以简化为:

SELECT gx.gid AS gid, COUNT(gx.id) AS cnt
FROM group_xref gx left join
     groups groups
     ON gx.gid = groups.id 
WHERE groups.att = 1 
GROUP BY gx.gid;

(我注意到这是我开始的地方)。