MySQL 性能 : Single query using GROUP_CONCAT, 还是两个单独的查询？

Question

我有一个MySQL数据库，每个用户都有一个账号，每个账号可以有多个权限。

我的最终目标是以帐户的用户名和逗号分隔的权限 ID 列表结束。我可以通过两种方式完成此操作:

SELECT a.username, GROUP_CONCAT(rp.permission_id) as permission_ids
FROM account AS a
JOIN role_permission AS rp
ON rp.role_id = a.role_id
WHERE a.id = 1902

……或者……

SELECT username
FROM account
WHERE id = 1902;

SELECT permission_id
FROM account_permission
WHERE account_id = 1902

通过单一查询，我得到了我想要的结果。对于两个查询，我必须使用第二个结果集在应用程序 (PHP) 中创建以逗号分隔的列表。

不选择第一个选项是否有任何性能原因？我以前从未使用过 GROUP_CONCAT，所以我不知道它在性能方面的含义。

Answer 1

性能应该还可以——比两个查询要好。您需要注意 length is limited虽然:

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;