具有子查询和“HAVING”的SQL查询非常缓慢

Question

我有一个查询，想要计算“用户”中有多个“用户名”条目的数量。

SELECT usernames.NAME,
    COUNT(user.id)
FROM user
INNER JOIN user_username ON user.id = user_username.user_id
INNER JOIN usernames ON user_username.user_username_id = usernames.id
WHERE 
    user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'
    AND user.id IN 
        (
            SELECT user_id
            FROM user_username
            GROUP BY user_id
            HAVING COUNT(*) > 1
        )
GROUP BY usernames.NAME
ORDER BY user.id DESC

这个查询可以正常工作，但速度太慢了，你有什么想法可以加快这个查询的速度吗？

- flaggalagga

---

你应该查看你的语句的“解释计划（EXPLAIN PLAN）”。 - David Isla

我会做的第一件事是确保你的Fk字段被索引了。 - HLGEM

包括实际执行计划（Ctrl + M）并执行查询。然后查看是否没有建议。我怀疑查询中的某个表可能存在索引问题。 - January Mmako

user.id、usernames.id 和 user_username.id 是主键，user_username.user_id 和 user_username.user_username_id 是外键。 - flaggalagga

为什么要按用户ID排序，即使它不在选择列表中，也没有进行分组？这在ISO SQL中是不允许的。 - trincot

Answer 1

问题有点模糊，但是试试这个：

SELECT  usernames.name, COUNT(user.id) 
FROM user 
INNER JOIN user_username ON user.id = user_username.user_id
INNER JOIN usernames     ON user_username.user_username_id = usernames.id
WHERE 
    user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'
    AND (SELECT COUNT(*) where user_id = user.id from user_username) > 1

GROUP BY usernames.name
ORDER BY user.id DESC

我认为你实际上不需要按user_id分组来获取具有更多用户名的用户。

编辑：我认为这样做会更好。进行左外连接（您将获得多个带有重复用户ID的行），然后按它们进行分组。

SELECT  user.id, COUNT(usernames.name) -- to show the number of usernames a user has 
FROM user 
LEFT JOIN user_username ON user.id = user_username.user_id
LEFT JOIN usernames     ON user_username.user_username_id = usernames.id
WHERE user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'
GROUP BY user.id
HAVING COUNT(usernames.name) > 1
ORDER BY user.id DESC

顺便说一下...user，usernames.name，user_username.user_id，user.id，user_username.user_username_id，usernames.id，user_id=user.id 让我头晕...

- dburner

---

很抱歉这个例子不太好。原始查询并没有那些名称，但我不能展示真实的表格名称等等... - flaggalagga

Answer 2

像这样分解查询是否有帮助？

CREATE TEMPORARY TABLE QualifiedUserIDs AS (
SELECT user_id
FROM user_username
GROUP BY user_id
HAVING COUNT(*) > 1);

SELECT usernames.NAME,
    COUNT(user.id)
FROM user
INNER JOIN QualifiedUserIDs quids ON quids.user_id = user.id 
INNER JOIN user_username ON user.id = user_username.user_id
INNER JOIN usernames ON user_username.user_username_id = usernames.id
WHERE 
    user.datecreated BETWEEN '2016-01-01' AND '2016-09-01'

GROUP BY usernames.NAME
ORDER BY user.id DESC

- Langosta

---

我选择了这个解决方案。非常好用，谢谢你。 - flaggalagga