优化MySQL查询：移除子查询

Question

有以下这些表：

customers
---------------------
`id` smallint(5) unsigned NOT NULL auto_increment,
`name` varchar(100) collate utf8_unicode_ci default NOT NULL,
....

customers_subaccounts
-------------------------
`companies_id` mediumint(8) unsigned NOT NULL,
`customers_id` mediumint(8) unsigned NOT NULL,
`subaccount` int(10) unsigned NOT NULL

我需要获取为同一公司分配了多个子账户的所有客户。

这是我已有的：

SELECT * FROM customers 
WHERE id IN 
    (SELECT customers_id 
     FROM customers_subaccounts
     GROUP BY customers_id, companies_id 
     HAVING COUNT(subaccount) > 1)

这个查询太慢了。如果在子查询的SELECT中添加DISTINCT修饰符到customers_id，速度甚至更慢，最终为整个查询检索相同的客户列表。也许有一种更好的方法可以避免子查询，任何更快的方式都会有所帮助，我不确定它是否会检索出准确正确的列表。

需要帮助吗？

- luis.ap.uyen

---

我认为是你的 COUNT 占用了太多时间。WHERE .. IN 也相当慢，但你可以通过使用 LEFT JOIN 来改善它。 - Hearner

@Hearner 感谢您的建议。是的，我知道这个问题。实际上是由于子查询引起的。 - luis.ap.uyen

Answer 1

您可以使用 INNER JOIN 替换子查询：

SELECT t1.id
FROM customers t1
INNER JOIN
(
    SELECT DISTINCT customers_id 
    FROM customers_subaccounts
    GROUP BY customers_id, companies_id 
    HAVING COUNT(*) > 1
) t2
    ON t1.id = t2.customers_id

- Tim Biegeleisen

---

你的建议是更快的一个（约0.023秒，不包括缓存）。非常感谢你的帮助。 - luis.ap.uyen

Answer 2

你也可以尝试使用EXISTS()，它可能比连接更快：

SELECT * FROM customers t
WHERE EXISTS(SELECT 1 FROM customers_subaccounts s
             WHERE s.customers_id = t.id
             GROUP BY s.customers_id, s.companies_id 
             HAVING COUNT(subaccount) > 1)

您还应考虑添加以下索引（如果尚不存在）：

customers_subaccounts (customers_id,companies_id,subaccount)
customers (id)

- sagi

---

你的查询也太慢了。不管怎样，谢谢你的帮助。 - luis.ap.uyen

Answer 3

假设您想为公司创建不同的子账户(或者它们已经保证是不同的)，那么在某些情况下，以下方法可能更快：

select c.*
from (select distinct cs.customers_id
      from customers_subaccounts cs join
           customers_subaccounts cs2
           on cs.customers_id = cs2.customers_id and
              cs.companies_id = cs2.companies_id and
              cs.subaccount < cs2.subaccount
     ) cc join
     customers c
     on c.customers_id = cc.customers_id;

具体而言，这可以利用在customers_subaccounts(customers_id, companies_id, subaccount)上的索引。

注意：这假设你想要的行中subaccounts是不同的。真正需要的是定义customers_subaccounts表中唯一行的方法。

- Gordon Linoff

---

我已经尝试过了，它绝对比我的快得多。但是你必须更正最后一行，我认为应该是 on c.id = cc.customers_id。它检索到与我的相同的结果，并且速度更快。 - luis.ap.uyen

我已经检查过了，它不像Tim Biegeleisen的建议那样快。你的持续时间大约为0.043秒。 - luis.ap.uyen

@user3514092……我假设你已经有了性能比较所需的索引。Tim的回答也非常好（在回答之前我已经点赞了）。在某些情况下，这可能会更快，因为它不需要两个级别的聚合。 - Gordon Linoff

Answer 4

尝试以下内容;)

SELECT DISTINCT t1.*
FROM customers t1
INNER JOIN customers_subaccounts t2 ON t1.id = t2.customers_id
GROUP BY t1.id, t1.name, t2.companies_id
HAVING COUNT(t2.subaccount) > 1

还可以在customers_id上添加index。

- Blank

---

我收到了这个错误：在having子句中，列'subaccount'是不明确的。 - luis.ap.uyen

已更新，请再次确认。 - Blank

嗯，它很慢（花了我42.74秒），并且检索到了重复的客户。 - luis.ap.uyen

如果使用像我的更新答案中的 INNER JOIN，会怎样呢？@user3514092 - Blank

Answer 5

通过使用缓存子查询结果，可以加快查询的速度。您的查询只需要进行简单的更改，就能让 mysql 意识到可以缓存子查询结果：

SELECT * FROM customers 
WHERE id IN 
  (select * from
    (SELECT distinct customers_id 
     FROM customers_subaccounts
     GROUP BY customers_id, companies_id 
     HAVING COUNT(subaccount) > 1) t1);

我很久以前用过它，它帮助了我很多。

- Mostafa Vatanpour

---

通过将第三级子查询作为别名封装，您的意思是它会缓存结果？这很有趣，因为它仍然非常类似于我的查询。我已经尝试过了，速度非常快（约0.027秒）。 - luis.ap.uyen

不用谢。是的。第一次对我来说也很有趣。 - Mostafa Vatanpour