请教 SQL 子查询的性能问题

[zcm3579]

我这里有个列表查询如下，两张表是 1 对多的，实际上字段比较多例子上做了些简化，数据量应该就 10 到 20 万左右 ,要分页和做筛选，

现在有个问题，如果在 where 里写条件会影响 GROUP_CONCAT 的结果 我能想到的就是把 select 或者 where 之一改为子查询， 但是不确认会不会产生性能的问题，想请大佬确认看看有没有更好的方法

SELECT
    u.id,
    u.name,#小王
    GROUP_CONCAT(i.email) AS email,#[email protected],[email protected]
    GROUP_CONCAT(i.phone) AS phone #[phone]
FROM user u
     LEFT JOIN contact i ON u.id = i.ref_id
GROUP BY u.id;

方式 1
SELECT
    u.id,
    u.name,
    GROUP_CONCAT(i.email) AS email,
    GROUP_CONCAT(i.phone) AS phone
FROM user u
     LEFT JOIN contact i ON u.id = i.ref_id
WHERE u.id IN (SELECT ref_id FROM contact WHERE phone LIKE '%137%')
  AND u.id IN (SELECT ref_id FROM contact WHERE email LIKE '%abc.com%')
GROUP BY u.id;

方式 2
SELECT
    u.id,
    u.name,
    (SELECT GROUP_CONCAT(email) FROM contact WHERE u.id = ref_id) AS email,
    (SELECT GROUP_CONCAT(phone) FROM contact WHERE u.id = ref_id) AS phone
FROM user u
     LEFT JOIN contact i ON u.id = i.ref_id
WHERE i.phone LIKE '%137%'
  AND i.email LIKE '%qq.com%'
GROUP BY u.id;

[celaraze]

子查询直接丢 JOIN 不行吗？还是条件更复杂？

SELECT u.id,
u.name,
GROUP_CONCAT(i.email) AS email,
GROUP_CONCAT(i.phone) AS phone
FROM user u
LEFT JOIN (SELECT * FROM contact where conditions) i ON u.id = i.ref_id
GROUP BY u.id;

[wtfedc]

chagpt4 针对方式 2 给的优化方案：
```
SELECT
u.id,
u.name,
GROUP_CONCAT(DISTINCT c1.email) AS email,
GROUP_CONCAT(DISTINCT c2.phone) AS phone
FROM user u
LEFT JOIN contact c1 ON u.id = c1.ref_id AND c1.email LIKE '%qq.com%'
LEFT JOIN contact c2 ON u.id = c2.ref_id AND c2.phone LIKE '%137%'
WHERE c1.email IS NOT NULL OR c2.phone IS NOT NULL
GROUP BY u.id, u.name;
```

[Habyss]

方式 1 和方式 2 的筛选逻辑不太一样
方式 1 email 和 phone 可以存在于不同记录中。
方式 2 phone 和 email 必须在同一条记录中同时满足条件。

方式 2 会更好(问的 chatgpt)

[zcm3579]

@Habyss 抱歉 才发现 1 有点问题 两个筛选应该都写到子查询里去。

但是 leftjoin 后又再子查询是不是有问题？



@celaraze 这样会影响 groupconcat 的结果吧？

[LiaoMatt]

直接 inner join + where 就好, "方式 1"还得全表扫描 contact 两次, "方式 2" 感觉也不行

[isora]

在处理这种问题时，我们需要考虑到性能和查询结果的准确性。在你的例子中，方式 1 和方式 2 都有可能产生性能问题。

方式 1 中，你使用了子查询在 WHERE 子句中，这可能会导致查询性能下降，因为 MySQL 需要为每个外部查询的行执行子查询。如果你的表中有大量的数据，这可能会导致性能问题。

方式 2 中，你在 SELECT 子句中使用了子查询，这也可能会导致性能问题，因为 MySQL 需要为每个外部查询的行执行子查询。此外，这种方法可能会导致查询结果不准确，因为你在子查询中使用了 GROUP_CONCAT 函数，但没有 GROUP BY 子句。

一个可能的解决方案是使用 JOIN 而不是子查询。你可以尝试以下查询：

```sql
SELECT
u.id,
u.name,
GROUP_CONCAT(i.email) AS email,
GROUP_CONCAT(i.phone) AS phone
FROM user u
LEFT JOIN contact i ON u.id = i.ref_id
WHERE i.phone LIKE '%137%'
AND i.email LIKE '%abc.com%'
GROUP BY u.id;
```

这个查询将`contact`表连接到`user`表，并在`WHERE`子句中应用筛选条件。然后，它使用`GROUP BY`子句和`GROUP_CONCAT`函数来聚合结果。这种方法应该比使用子查询更有效率，因为 MySQL 只需要执行一次 JOIN 操作，而不是为每个外部查询的行执行子查询。

然而，这种方法可能会导致查询结果不准确，因为`WHERE`子句中的条件可能会过滤掉一些你想要在结果中看到的行。为了解决这个问题，你可以考虑使用`HAVING`子句来替代`WHERE`子句，如下所示：

```sql
SELECT
u.id,
u.name,
GROUP_CONCAT(i.email) AS email,
GROUP_CONCAT(i.phone) AS phone
FROM user u
LEFT JOIN contact i ON u.id = i.ref_id
GROUP BY u.id
HAVING email LIKE '%abc.com%'
AND phone LIKE '%137%';
```

这个查询首先执行 JOIN 操作和 GROUP BY 子句，然后在聚合结果上应用 HAVING 子句中的条件。这种方法应该能够提供你想要的结果，而且性能也比使用子查询更好。

[wenxueywx]

“在 where 里写条件会影响 GROUP_CONCAT 的结果”
是什么意思？