sql-server - 使用 LIKE 和 EXISTS 子句时优化数据库架构/索引以获得更快的查询结果

Question

在 SQL 2005 服务器数据库上实现树结构时，当使用 LIKE 子句 与 EXISTS 结合使用时，查询响应时间过长(下面的查询超过 5 秒)条款。

慢速查询涉及两个表 - [SitePath_T] 和 [UserSiteRight_T]:

CREATE TABLE [dbo].[UserSiteRight_T](
      [UserID_i] [int] NOT NULL
    , [SiteID_i] [int] NOT NULL
    , CONSTRAINT [PKC_UserSiteRight_UserIDSiteID] PRIMARY KEY CLUSTERED ( [UserID_i] ASC, [SiteID_i] ASC )
    , CONSTRAINT [FK_UserSiteRight_UserID] FOREIGN KEY( [UserID_i] ) REFERENCES [dbo].[User_T] ( [ID_i] )
    , CONSTRAINT [FK_UserSiteRight_SiteID] FOREIGN KEY( [SiteID_i] ) REFERENCES [dbo].[Site_T] ( [ID_i] )
) 

[UserSiteRight_T] 表中 UserID_i = 2484 的行数 (rights) 非常小:545
( UserID_i = 2484 是随机选择的)

此外，数据库相对较小 - [SitePath_T] 表中只有 23000 行:

CREATE TABLE [dbo].[SitePath_T] (
    [SiteID_i] INT NOT NULL,
    [Path_v] VARCHAR(255) NOT NULL,
    CONSTRAINT [PK_SitePath_PathSiteID] PRIMARY KEY CLUSTERED ( [Path_v] ASC, [SiteID_i] ASC ),
    CONSTRAINT [AK_SitePath_Path] UNIQUE NONCLUSTERED ( [Path_v] ASC ),
    CONSTRAINT [FK_SitePath_SiteID] FOREIGN KEY( [SiteID_i] ) REFERENCES [Site_T] ( [ID_i] )

)

我试图仅获取具有可由特定UserID(由[UserSiteRight_T]表给出)访问的子网站的SiteID，如下:

SELECT sp.SiteID_i
  FROM SitePath_t sp
 WHERE EXISTS ( SELECT *
              FROM [dbo].[SitePath_T] usp
                 , [dbo].[UserSiteRight_T] uusr
             WHERE uusr.SiteID_i = usp.SiteID_i
               AND uusr.UserID_i = 2484
               AND usp.Path_v LIKE sp.Path_v+'%' )

下面您可以找到结果的一部分，其中只需要/返回列 sp.SiteID_i - 我还添加了相关的相应 Path_v、UserSiteRight_T.SiteID_i WHERE UserID = 2484 以及与 LIKE 匹配的相应 SitePath_T SiteID_i 和 Path_v 条件:

sp.SiteID_i  sp.Path_v      [UserSiteRight_T].SiteID_i      usp.SiteID_i        usp.Path_v
1           '1.'                        NULL                10054               '1.10054.'
10054       '1.10054.'                  10054               10054               '1.10054.'
10275       '1.10275.'                  10275               10275               '1.10275.'
1533        '1.1533.'                   NULL                2697                '1.1533.2689.2693.2697.'
2689        '1.1533.2689.'              NULL                2697                '1.1533.2689.2693.2697.'
2693        '1.1533.2689.2693.'         NULL                2697                '1.1533.2689.2693.2697.'
2697        '1.1533.2689.2693.2697.'    2697                2697                '1.1533.2689.2693.2697.'
1580        '1.1580.'                   NULL                1581                '1.1580.1581.'
1581        '1.1580.1581.'              1581                1581                '1.1580.1581.'
1585        '1.1580.1581.1585.'         1585                1585                '1.1580.1581.1585.'
222         '1.222.'                    222                 222                 '1.222.'
223         '1.222.223.'                223                 223                 '1.222.223.'
224         '1.222.223.224.'            224                 224                 '1.222.223.224.'
3103        '1.3103.'                   NULL                3537                '1.3103.3529.3533.3537.'
3529        '1.3103.3529.'              NULL                3537                '1.3103.3529.3533.3537.'
3533        '1.3103.3529.3533.'         NULL                3537                '1.3103.3529.3533.3537.'
3537        '1.3103.3529.3533.3537.'    3537                3537                '1.3103.3529.3533.3537.'

上述查询的执行计划:

  |--Nested Loops(Left Semi Join, WHERE:([MyTestDB].[dbo].[SitePath_T].[Path_v] as [usp].[Path_v] like [Expr1007]))
       |--Compute Scalar(DEFINE:([Expr1007]=[MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%', [Expr1008]=LikeRangeStart([MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%'), [Expr1009]=LikeRangeEnd([MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%'), [Expr1010]=LikeRangeInfo([MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%')))
       |    |--Index Scan(OBJECT:([MyTestDB].[dbo].[SitePath_T].[AK_SitePath_Path] AS [sp]))
       |--Table Spool
            |--Hash Match(Inner Join, HASH:([uusr].[SiteID_i])=([usp].[SiteID_i]))
                 |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[UserSiteRight_T].[PKC_UserSiteRight_UserIDSiteID] AS [uusr]), SEEK:([uusr].[UserID_i]=(2484)) ORDERED FORWARD)
                 |--Index Scan(OBJECT:([MyTestDB].[dbo].[SitePath_T].[AK_SitePath_Path] AS [usp]))

以及重写的查询:

SELECT DISTINCT 
       sp.SiteID_i
  FROM [dbo].[SitePath_t] sp
     , [dbo].[SitePath_T] usp
     , [dbo].[UserSiteRight_T] uusr
 WHERE ( uusr.SiteID_i = usp.SiteID_i
   AND uusr.UserID_i = 2484
   AND usp.Path_v LIKE sp.Path_v+'%' )
 ORDER BY SiteID_i ASC

执行计划:

  |--Hash Match(Aggregate, HASH:([sp].[SiteID_i]))
       |--Nested Loops(Inner Join, WHERE:([MyTestDB].[dbo].[SitePath_T].[Path_v] as [usp].[Path_v] like [Expr1006]))
            |--Hash Match(Inner Join, HASH:([uusr].[SiteID_i])=([usp].[SiteID_i]))
            |    |--Clustered Index Seek(OBJECT:([MyTestDB].[dbo].[UserSiteRight_T].[PKC_UserSiteRight_UserIDSiteID] AS [uusr]), SEEK:([uusr].[UserID_i]=(2484)) ORDERED FORWARD)
            |    |--Index Scan(OBJECT:([MyTestDB].[dbo].[SitePath_T].[AK_SitePath_Path] AS [usp]))
            |--Table Spool
                 |--Compute Scalar(DEFINE:([Expr1006]=[MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%', [Expr1007]=LikeRangeStart([MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%'), [Expr1008]=LikeRangeEnd([MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%'), [Expr1009]=LikeRangeInfo([MyTestDB].[dbo].[SitePath_T].[Path_v] as [sp].[Path_v]+'%')))
                      |--Index Scan(OBJECT:([MyTestDB].[dbo].[SitePath_T].[AK_SitePath_Path] AS [sp]))

所有索引均已就位 - 数据库引擎优化顾问不建议进行新的架构修改 - 但两个查询都在 5 秒以上返回正确的结果 - 并且，因为它是 Ajax 请求的响应 - 感觉(并且是)更新导航树时非常慢

有什么建议可以优化/修改数据库架构/索引/查询以获得更快的响应吗？

谢谢

Answer 1

基于:

SELECT sp.SiteID_i 
  FROM SitePath_t sp 
 WHERE EXISTS ( SELECT * 
              FROM [dbo].[SitePath_T] usp 
                 , [dbo].[UserSiteRight_T] uusr 
             WHERE uusr.SiteID_i = usp.SiteID_i 
               AND uusr.UserID_i = 2484 
               AND usp.Path_v LIKE sp.Path_v+'%' ) 

(基于您正在进行半连接的事实，这很好)。

它首先(正确地)关注 uusr 表，以查找该用户的记录。它已经对此进行了 CIX Seek，这很好。从那里，它根据 SiteID_i 字段在 usp 中查找相应的记录。

接下来考虑它想要通过 SiteID_i 查找站点的事实，以及您希望采用哪种类型的加入。

合并连接怎么样？那就太好了，但需要双方对数据进行排序。如果索引的顺序正确就可以了...

...之后，您希望根据路径查找内容。那么怎么样:

CREATE INDEX ix_UUSR on [dbo].[UserSiteRight_T] (UserID_i, SiteID_i);
CREATE INDEX ix_usp on [dbo].[SitePath_T] (SiteID_i) INCLUDE (Path_v);

然后 SitePath_T 上的另一个索引可以找到您想要的 SiteID:

CREATE INDEX ix_sp on [dbo].[SitePath_T] (Path_v) INCLUDE (SiteID_i);

最后一个循环可能使用了嵌套循环，但希望这不会太糟糕。将影响您的系统的是前两个索引，这应该让您在 EXISTS 子句中看到两个表之间的合并联接。