优化Mysql中的group by子查询

Question

这是一个非常简单的查询。

如果我分别运行这些查询，它们并不会太慢，但当我将它们组合在一起时，速度就变得很慢了。

我不知道该如何进行优化。我只想显示多个退款信息，即faultid出现多次的情况。

SELECT 
    r.* 
FROM 
    faultrefunds_v2 r
WHERE 
    r.id IN (SELECT r1.id 
             FROM faultrefunds_v2 r1 
             GROUP BY faultid
             HAVING count(r1.faultid) > 1);

解释的结果已经作为图片附加在下方。

- Robbo_UK

---

r.id是主键吗？ - Mosty Mostacho

是的，faultid 是外键。 - Robbo_UK

我认为你写的查询并没有回答这个问题。这是因为一旦你按faultid分组，你只会返回一个r.id来代表该faultid。 - Mosty Mostacho

Answer 1

您使用的IN子句会非常缓慢，请改用JOIN：

SELECT r.* FROM ( 
  SELECT r1.id AS id
  FROM faultrefunds_v2 r1 
  GROUP BY faultid
  HAVING count(r1.faultid) > 1
) AS ids
LEFT JOIN faultrefunds_v2 AS r
ON( ids.id = r.id )

- nobody

---

这个程序可行，速度非常快。您能否解释一下为什么我使用的 IN 子句很慢，以便我避免再次这样做？ - Robbo_UK

MySql通常不会对IN子句中的元素进行索引，因此它应该将r表中每一行的ID与子查询中找到的所有ID进行比较。在我的方法中，MySql首先找到所有适当的ID，然后使用索引非常快地检索每个ID的完整行，在外部查询中。 - nobody

这个答案是正确的答案，与Andiry M的回答并列。我不能接受两个答案（尽管我认为对于同一个问题可能有两个正确但不同的答案）。 - Robbo_UK

Answer 2

我认为你的查询并没有回答问题。据我理解，你应该首先获取所有具有多个关联ID（表中的行）的faultid。然后获取所有这些行（而不仅仅是faultId）。

试试这个：

select * from faultrefunds_v2
where faultId in (
    select faultId from faultrefunds_v2
    group by faultId
    having count(*) > 1
)

- Mosty Mostacho

---

我认为这是我的查询改进，更加合乎逻辑和易于理解。但它仍然相当慢。给它+1。 - Robbo_UK

Answer 3

我想这更像是一种重写而不是优化，但无论如何，这是我会尝试的方法：

SELECT 
  r.* 
FROM faultrefunds_v2 r
WHERE EXISTS (
  SELECT *
  FROM faultrefunds_v2 r1 
  WHERE r1.faultid = r.faultid
    AND r1.id <> r.id
);

- Andriy M

---

为什么这个查询比我写的要快得多？它们看起来非常相似？ - Robbo_UK

您的查询使用了分组，这是一个相对昂贵的操作。此外，那种子查询被认为需要扫描整个表来构建要匹配的行集。而我的查询则使用了半连接（以EXISTS谓词的形式）。在半连接中，一旦找到至少一个匹配项，就不再继续搜索该行的匹配项，您可以猜想，在某些/大多数行存在许多匹配项的情况下，这可能会极大地加快查询速度。 - Andriy M

使用exist时，它只能通过使用相同的表（但具有不同的别名）来工作吗？它如何知道要将哪个ID与表链接起来。 - Robbo_UK

@Robbo_UK：在EXISTS子查询中，您可以使用任何（数量的）表，就像在几乎任何其他子查询中一样，只要记住EXISTS（至少根据SQL标准）忽略您正在尝试检索的任何列（因此您经常可以看到SELECT * FROM … 在EXISTS子查询中，尽管有些人发现使用SELECT NULL FROM …或SELECT 1 FROM …或适合他们的任何内容更清晰）。唯一重要的是EXISTS谓词是否返回任何行（至少一行）：如果是，则谓词计算为true，否则为false。 - Andriy M