具有子查询优化的 MySQL 查询

Question

我构建了一个具有相当典型的用户/组/权限系统的 CMS 系统，其中用户可以是组的成员，权限可以直接应用于用户，也可以应用于用户可以属于的组。

权限也可以是“通配符”(例如适用于所有对象)或适用于由模块名称和行 ID 指定的特定对象。权限可以是授予访问权限的“允许”，也可以是“拒绝”，它专门阻止访问并覆盖他们在其他地方授予的任何“允许”权限。拒绝存储在 userpermission/grouppermission 表中，方法是创建一个“允许”列设置为 0 的行。

以下查询当前用于(并且有效)列出所有已被授予特定“通配符”权限(permissionid 123)的用户。

    SELECT
        `user`.*
    FROM
        (
            SELECT
                `user`.*,
                `userpermission`.`allow` AS `user_allow`,
                `userpermission`.`permissionid` AS `user_permissionid`,
                `grouppermission`.`allow` AS `group_allow`,
                `grouppermission`.`permissionid` AS `group_permissionid`

            FROM
                `user`

                LEFT JOIN `userpermission` ON
                    `user`.`userid` = `userpermission`.`userid`
                    AND `userpermission`.`module` = '*'
                    AND `userpermission`.`rowid` = '*'
                    AND `userpermission`.`permissionid` = 18

                LEFT JOIN `usergroup` ON 
                  `user`.`userid` = `usergroup`.`userid`

                LEFT JOIN `grouppermission` ON
                    `usergroup`.`groupid` = `grouppermission`.`groupid`
                    AND `grouppermission`.`module` = '*'
                    AND `grouppermission`.`rowid` = '*'
                    AND `grouppermission`.`permissionid` = 18

                WHERE
                    (
                        `grouppermission`.`allow` = 1
                        OR
                        `userpermission`.`allow` = 1
                    )

        ) AS `user` 

        LEFT JOIN `userpermission` ON
            `user`.`userid` = `userpermission`.`userid`
            AND `userpermission`.`permissionid` = `user`.`user_permissionid`
            AND `userpermission`.`allow` = 0
            AND `userpermission`.`module` = '*'
            AND `userpermission`.`rowid` = '*'

        LEFT JOIN `usergroup` ON 
          `user`.`userid` = `usergroup`.`userid`

        LEFT JOIN `grouppermission` ON
            `usergroup`.`groupid` = `grouppermission`.`groupid`
            AND `grouppermission`.`permissionid` = `user`.`group_permissionid`
            AND `grouppermission`.`allow` = 0
            AND `grouppermission`.`module` = '*'
            AND `grouppermission`.`rowid` = '*'

      GROUP BY `user`.`userid`

      HAVING
        COUNT(`userpermission`.`userpermissionid`) + COUNT(`grouppermission`.`grouppermissionid`) = 0

但是它非常慢(约 0.5 秒，约 3000 个用户，约 250 个组，约 10000 个用户组加入，约 30 个权限，约 150 个组权限和约 30 个用户权限)。

根据上面的示例，permissionid 只是一个权限。可能还需要检查多个权限，例如IN(18,19,20) 而不是 = 18

Explain 提供以下输出 - 我认为我已经为正确的列编制了索引，但是我不确定如何(或如果可能的话)为派生表编制索引:

+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+
| id | select_type | table           | type | possible_keys              | key          | key_len | ref                            | rows | Extra                           |
+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+
|  1 | PRIMARY     | [derived2]      | ALL  | NULL                       | NULL         | NULL    | NULL                           |   62 | Using temporary; Using filesort |
|  1 | PRIMARY     | userpermission  | ref  | USERID,PERMISSIONID,ALLOW  | USERID       | 4       | user.userid                    |    2 |                                 |
|  1 | PRIMARY     | usergroup       | ref  | USERID                     | USERID       | 4       | user.userid                    |    4 |                                 |
|  1 | PRIMARY     | grouppermission | ref  | GROUPID,PERMISSIONID,ALLOW | PERMISSIONID | 4       | user.group_permissionid        |    3 |                                 |
|  2 | DERIVED     | user            | ALL  | NULL                       | NULL         | NULL    | NULL                           | 2985 |                                 |
|  2 | DERIVED     | userpermission  | ref  | USERID,PERMISSIONID        | PERMISSIONID | 4       |                                |    1 |                                 |
|  2 | DERIVED     | usergroup       | ref  | USERID                     | USERID       | 4       | [database].user.userid         |    4 |                                 |
|  2 | DERIVED     | grouppermission | ref  | GROUPID,PERMISSIONID       | PERMISSIONID | 4       |                                |    3 | Using where                     |
+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+

是否可以在没有子查询的情况下重写查询以便对其进行优化，或者按原样进行优化？

如果数据结构需要改变那不是什么大问题。

Answer 1

您离开加入，之后您计算非空行并检查是否不存在。我希望你从我的措辞中意识到，这比它需要的更复杂。您可以像这样简单地重写整个查询:

SELECT
    `user`.*
FROM
    `user`
    LEFT JOIN `userpermission` ON
        `user`.`userid` = `userpermission`.`userid`
        AND `userpermission`.`module` = '*'
        AND `userpermission`.`rowid` = '*'
        AND `userpermission`.`permissionid` = 18
    LEFT JOIN `usergroup` ON `user`.`userid` = `usergroup`.`userid`
    LEFT JOIN `grouppermission` ON
        `usergroup`.`groupid` = `grouppermission`.`groupid`
        AND `grouppermission`.`module` = '*'
        AND `grouppermission`.`rowid` = '*'
        AND `grouppermission`.`permissionid` = 18
    WHERE
        (`grouppermission`.`allow` = 1 OR `userpermission`.`allow` = 1)
        AND NOT EXISTS (SELECT 1 FROM `userpermission` WHERE `user`.`userid` = `userpermission`.`userid`
                        AND `userpermission`.`allow` = 0
                        AND `userpermission`.`module` = '*'
                        AND `userpermission`.`rowid` = '*'
                        AND `userpermission`.`permissionid` = 18
                       )
        AND NOT EXISTS (SELECT 1 FROM `grouppermission` WHERE `usergroup`.`groupid` = `grouppermission`.`groupid`
                        AND `grouppermission`.`allow` = 0
                        AND `grouppermission`.`module` = '*'
                        AND `grouppermission`.`rowid` = '*'
                        AND `grouppermission`.`permissionid` = 18
                       );

EXISTS() 的另一个优点是，它会在找到条目后立即停止。您不必获取所有行然后检查是否没有行。

另一种编写查询的方法是这样的:

SELECT
    `user`.*
FROM
    `user`
    LEFT JOIN `userpermission` ON
        `user`.`userid` = `userpermission`.`userid`
        AND `userpermission`.`module` = '*'
        AND `userpermission`.`rowid` = '*'
        AND `userpermission`.`permissionid` = 18
    LEFT JOIN `usergroup` ON `user`.`userid` = `usergroup`.`userid`
    LEFT JOIN `grouppermission` ON
        `usergroup`.`groupid` = `grouppermission`.`groupid`
        AND `grouppermission`.`module` = '*'
        AND `grouppermission`.`rowid` = '*'
        AND `grouppermission`.`permissionid` = 18
    LEFT JOIN `userpermission` up ON `user`.`userid` = `up`.`userid`
                                AND up.`allow` = 0
                                AND up.`module` = '*'
                                AND up.`rowid` = '*'
                                AND up.`permissionid` = 18
    LEFT JOIN grouppermission gp ON `usergroup`.`groupid` = `gp`.`groupid`
                                AND gp.`allow` = 0
                                AND gp.`module` = '*'
                                AND gp.`rowid` = '*'
                                AND gp.`permissionid` = 18
    WHERE
        (`grouppermission`.`allow` = 1 OR `userpermission`.`allow` = 1)
        AND up.userid IS NULL
        AND gp.groupid IS NULL;

试试哪一个更适合您。