代码之家 › 专栏 › 技术社区 › Robert Fraser

查询/数据库优化:如何优化?(我应该使用物化视图吗?)

indexed-view materialized-views query-optimization database sql-server

Robert Fraser · 技术社区 · 16 年前

我有一个关于如何优化查询的问题。实际上,由于我将经常运行查询,所以我考虑使用物化视图或索引视图(这是个好主意吗?)或非规范化。

考虑以下四个表(省略了不相关的字段):

用户(int userid)
组(int groupid)
组成员身份(int userid、int groupid、bool issharing)
计算机(int userid)

关系是用户可以拥有0..n台计算机(一个用户对多台计算机),并且可以是0..n组的成员。一个组可以有0..n个用户(多个用户到多个组)。“is sharing”表示用户是共享到该组还是该组的“只读”成员(即,可以看到共享成员的计算机,但不共享自己的计算机)。

查询是为给定用户查找用户可以看到的计算机。用户可以看到自己的所有计算机。她还可以看到其他用户的任何计算机,这些计算机属于她所属的组,并且正在与该组共享。好吧,这没什么意义,所以这是O(n^3)psudocode的目标:

List<Computer> l
foreach(Computer c in Computers)
    if(c.userId == current_user_id)
        add c to l
    else
        foreach(GroupMembership m where m.userId == current_user_id)
            foreach(GroupMembership m2 where c.userId == m2.userId && m.groupId == m2.groupId)
                if(m2.isSharing)
                    add c to l

现在我使用的是ORM映射器,基本上是在做上面的工作(我对整个SQL工作不是很在行),但这显然不是一个理想的解决方案。我在这里列出的每个字段上都有索引(isshared除外),在groupmembership的(userid,groupid)元组上还有一个额外的索引。但是,任何一个数据库向导都能想到一个更好的解决方案吗?

这个项目还没有上线,但是我猜每个用户平均会有1.2台电脑(每个人都会有一台,一些人可能会有更多),每个用户可能有0.75个组成员身份(许多用户不会使用组功能,但是那些使用组功能的人很可能是多个组的成员)。此外,所有这些关联的表都将频繁地进行添加,这可能会使物化视图成为一个不太实际的解决方案。我正在使用SQL Server 2008。

谢谢, 一切顺利, 罗伯特

2 回复 | 直到 16 年前

Jay 16 年前

我认为这样做没有任何子查询。免责声明:这是我的头上,没有测试。

select distinct computerId
from groupMemberships m1
join groupMemberships m2 on m2.groupId=m1.groupId
  and (m2.isSharing or m2.userId=m1.userId)
join computers c on c.userId=m2.userId
where m1.userId=?

不需要读取用户表组,除非有来自那些表的其他数据要包括在未提到的select中。

“issharing或userid”应该让您拥有自己的计算机和任何共享的计算机。这可能是不必要的聪明:一个简单的联合可能更有效。

Adriaan Stander 16 年前

好的,我认为您需要上述规范的表和查询吗?

我从一台计算机被“分配”给一个给定的用户的规范中得到,但是可以共享吗?

计算机(int userid)

看看这个,如果你想改变任何规格请告诉我。

DECLARE @Users TABLE(
        UserID INT
)

DECLARE @Computers TABLE(
        ComputerID INT,
        UserID INT
)

DECLARE @Groups TABLE(
        GroupID INT
)

DECLARE @GroupMemberships TABLE(
        UserID INT,
        GroupID INT,
        IsSharing INT
)

INSERT INTO @Users (UserID) SELECT 1
INSERT INTO @Users (UserID) SELECT 2

INSERT INTO @Computers (ComputerID, UserID) SELECT 1, 1
INSERT INTO @Computers (ComputerID, UserID) SELECT 2, 1
INSERT INTO @Computers (ComputerID, UserID) SELECT 3, 1
INSERT INTO @Computers (ComputerID, UserID) SELECT 4, 2
INSERT INTO @Computers (ComputerID, UserID) SELECT 5, 2

INSERT INTO @Groups (GroupID) SELECT 1
INSERT INTO @Groups (GroupID) SELECT 2
INSERT INTO @Groups (GroupID) SELECT 3

INSERT INTO @GroupMemberships (UserID,GroupID,IsSharing) SELECT 1, 1, 0
INSERT INTO @GroupMemberships (UserID,GroupID,IsSharing) SELECT 1, 2, 1
INSERT INTO @GroupMemberships (UserID,GroupID,IsSharing) SELECT 2, 2, 0
INSERT INTO @GroupMemberships (UserID,GroupID,IsSharing) SELECT 2, 3, 0

DECLARE @UserID INT
--SELECT @UserID = 1
SELECT @UserID = 2

SELECT  DISTINCT 
        ComputerID
FROM    @Computers
WHERE   UserID = @UserID
UNION
SELECT  DISTINCT 
        ComputerID
FROM    @Computers c INNER JOIN
        (
            SELECT  DISTINCT 
                    gm.UserID
            FROM    @GroupMemberships gm INNER JOIN
                    @GroupMemberships ThisUserGroups    ON  gm.GroupID = ThisUserGroups.GroupID
                                                        AND ThisUserGroups.UserID = @UserID
            WHERE   gm.UserID != @UserID
            AND             gm.IsSharing = 1
    ) OtherUsersInSharedGroups ON c.UserID = OtherUsersInSharedGroups.UserID