logo标签列表

使用子查询优化的MySQL查询

mysqloptimization
4
我已经建立了一个CMS系统，其中有一个相当典型的用户/组/权限系统，用户可以成为组的成员，并且权限可以直接应用于用户，也可以应用于用户可以成为成员的组。权限还可以是“通配符”（例如应用于所有对象），或者应用于由模块名称和行ID指定的特定对象。权限可以是“允许”（即授权访问）或“拒绝”，后者专门阻止访问并覆盖他们在其他地方被授予的任何“允许”权限。拒绝通过创建带有“allow”列设置为0的行来存储在userpermission/grouppermission表中。
以下查询目前被使用（并有效）来列出所有已被授予特定“通配符”权限（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。
解释提供以下输出-我认为我已经正确索引了列，但我不确定如何（或是否可能）索引派生表： 
+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+
| 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                     |
+----+-------------+-----------------+------+----------------------------+--------------+---------+--------------------------------+------+---------------------------------+
是否可以重写查询而不使用子查询，以便进行优化，或者按原样进行优化？
如果需要更改数据结构，则并不是一个巨大的问题。
抱歉，我复制查询时出错了 - 它还有一个“Having”子句，我认为需要使用Group By？ - Chris Wheeler
1
啊，那改变了一切！！ - Strawberry
你能设置一个sqlfiddle或其他什么吗？那个查询看起来很疯狂。只要摆脱子查询，性能就可能得到改善，因为子查询结果将不被索引。 - mainstreetmark
是的，摆脱子查询可能是最好的解决方案，但我无法理解如何做到这一点。明天我应该能够设置一个SQL fiddle。感谢您的反馈。 - Chris Wheeler
2个回答
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;

试一试哪一个对你更好。

嗨，感谢您抽出时间回答这个问题。我尝试了您的SQL语句，但是它返回零结果。我认为这是因为用户可以从组权限或用户权限中获得授权。使用INNER连接意味着他们必须在userpermission和grouppermission中拥有相同的权限... - Chris Wheeler
好的，那只是一种假设。我重新撰写了答案，使用了左连接。如果它产生了正确的结果并具有更好的性能，请再次检查。 - fancyPants
好的 - 我已经更新了您的查询，以便它产生预期的结果（在 user.* 后面有一个逗号会导致问题，我添加了 'permissionid' = 18 检查到 NOT EXISTS 检查中...但是它需要大约0.6秒才能执行，这与我的原始查询大致相同。我将尝试添加一些索引来优化它... - Chris Wheeler
@ChrisWheeler，最近怎么样？如果你想让我看一下执行计划和表结构，随时可以分享给我。 - fancyPants
@ChrisWheeler 我编辑了我的答案，加入了另一种编写查询的可能性。如果它对你更有效，请尝试一下。 - fancyPants
fancyPants - 你的第二个例子快了很多（约0.3秒），所以我会接受你的答案 - 感谢你的所有帮助。我也想出了一种不同的方法，运行时间大约相同，所以我会将其作为单独的答案添加。由于权限不经常更改，当用户/组/权限更新时，我现在也会预先检查某些权限并将它们缓存到用户表的列中（对于需要频繁生成具有该权限的所有用户列表的权限）。 - Chris Wheeler
0

另一种不需要任何子查询的方法是：

SELECT
    `user`.*,
     SUM(CASE WHEN `userpermission`.`allow` = 1 THEN 1 ELSE 0 END) as user_allow,
     SUM(CASE WHEN `grouppermission`.`allow` = 1 THEN 1 ELSE 0 END) as group_allow,
     SUM(CASE WHEN `userpermission`.`allow` = 0 THEN 1 ELSE 0 END) as user_deny,
     SUM(CASE WHEN `grouppermission`.`allow` = 0 THEN 1 ELSE 0 END) as group_deny
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
    GROUP BY 
        `user`.`userid`
    HAVING
        (
            `user_allow` > 0
            OR
            `group_allow` > 0
        ) 
        AND 
            `user_deny` = 0
        AND
            `group_deny` = 0
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接