mysql - 如何优化这个 MySQL 查询？ (交叉连接、子查询)

Question

我向 MySQL 专家提出了一个具有挑战性的问题。

我有一个包含 4 个表的用户权限系统:

1. 用户(id | 电子邮件 | 创建位置)
2. 权限(id | Responsible_id | key | 权重)
3. permission_user(id |permission_id | user_id)
4. 职责(id | key | 权重)

用户可以分配任意数量的权限，并且任何权限都可以授予任意数量的用户(多对多)。职责就像权限组，每个权限都属于一个职责。例如，一项权限称为“更新”，由“客户”负责。另一种是删除并负责订单。

我需要获取每个用户的完整权限图，但仅限于那些至少已授予一项权限的用户。结果应按以下顺序排序:

1. 用户的权限数量从大到小
2. 用户的 created_at 列，最早的在前
3. 责任的权重
4. 权限的权重

结果集示例:

user_id | responsibility | permission | granted
-----------------------------------------------
      5 | customers      | create     |       1
      5 | customers      | update     |       1
      5 | orders         | create     |       1
      5 | orders         | update     |       1
      2 | customers      | create     |       0
      2 | customers      | delete     |       0
      2 | orders         | create     |       1
      2 | orders         | update     |       0

假设我的数据库中有 10 个用户，但其中只有两个用户被授予了任何权限。总共有4个权限:

1. 创建客户责任
2. 客户责任的更新
3. 创建订单职责
4. 订单责任的更新。

这就是为什么我们的结果中有 8 条记录(2 个具有任意权限的用户 × 4 个权限)。首先显示 id = 5 的用户，因为他拥有更多权限。如果有抽签，created_at 日期较旧的抽签将先进行。权限始终按其职责的权重排序，然后按其自身的权重排序。

我的问题是，如何针对这种情况编写最佳查询？我自己已经做了一个，效果很好:

SELECT `users`.`id` AS `user_id`,
       `responsibilities`.`key` AS `responsibility`,
       `permissions`.`key` AS `permission`,
       !ISNULL(`permission_user`.`id`) AS `granted`
FROM `users`
CROSS JOIN `permissions`
JOIN `responsibilities`
  ON `responsibilities`.`id` = `permissions`.`responsibility_id`
LEFT JOIN `permission_user`
       ON `permission_user`.`user_id` = `users`.`id`
      AND `permission_user`.`permission_id` = `permissions`.`id`
WHERE (
    SELECT COUNT(*)
    FROM `permission_user`
    WHERE `user_id` = `users`.`id`
) > 0
ORDER BY (
             SELECT COUNT(*)
             FROM `permission_user`
             WHERE `user_id` = `users`.`id`
         ) DESC,
         `users`.`created_at` ASC,
         `responsibilities`.`weight` ASC,
         `permissions`.`weight` ASC

问题是我两次使用同一个子查询。

我可以做得更好吗？我信赖你们，MySQL 专家!

--- 编辑 ---

感谢 Gordon Linoff 的评论，我使用了 HAVING 子句:

SELECT `users`.`email`,
       `responsibilities`.`key`,
       `permissions`.`key`,
       !ISNULL(`permission_user`.`id`) as `granted`,
       (
           SELECT COUNT(*)
           FROM `permission_user`
           WHERE `user_id` = `users`.`id`
       ) AS `total_permissions`
FROM `users`
CROSS JOIN `permissions`
JOIN `responsibilities`
  ON `responsibilities`.`id` = `permissions`.`responsibility_id`
LEFT JOIN `permission_user`
       ON `permission_user`.`user_id` = `users`.`id`
      AND `permission_user`.`permission_id` = `permissions`.`id`
HAVING `total_permissions` > 0
ORDER BY `total_permissions` DESC,
         `users`.`created_at` ASC,
         `responsibilities`.`weight` ASC,
         `permissions`.`weight` ASC

我惊讶地发现 HAVING 可以单独使用而无需 GROUP BY。

现在可以改进以获得更好的性能吗？

Answer 1

最有效的方法可能是:

SELECT u.email, r.`key`, r.`key`,
       !ISNULL(pu.id) as `granted`
FROM (SELECT u.*,
             (SELECT COUNT(*) FROM `permission_user` pu WHERE pu.user_id = u.id
       ) AS `total_permissions`
      FROM `users` u
     ) u CROSS JOIN
     permissions p JOIN 
     responsibilities r
     ON r.id = p.responsibility_id LEFT JOIN 
     permission_user pu
     ON pu.user_id = u.id AND
        pu.permission_id = p.id
WHERE u.total_permissions > 0
ORDER BY `total_permissions` DESC,
         `users`.`created_at` ASC,
         `responsibilities`.`weight` ASC,
         `permissions`.`weight` ASC;

这将为每个用户运行一次子查询，而不是每个用户/权限组合运行一次(正如修改后的查询和原始查询所做的那样)。这有两个成本。首先是子查询的具体化，所以users表中的数据要重新读写。考虑到查询中的其他所有内容，可能没什么大不了的。第二个是users 表上的索引丢失。再次，使用交叉连接，索引(可能)不会被使用，所以这也是次要的。