请教 SQL 子查询的性能问题

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

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

SELECT
    u.id,
    u.name,#小王
    GROUP_CONCAT(i.email) AS email,#test@abc.com,xyz@123.com
    GROUP_CONCAT(i.phone) AS phone #13711112222,13966665555
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;

isora

20 天前

在处理这种问题时，我们需要考虑到性能和查询结果的准确性。在你的例子中，方式 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 子句中的条件。这种方法应该能够提供你想要的结果，而且性能也比使用子查询更好。