Оптимизация схемы / индексов базы данных для более быстрого результата запроса при использовании предложений LIKE и EXISTS

При реализации древовидной структуры в базе данных сервера SQL 2005 ответ на запрос занимает слишком много времени (запросы ниже говорят более 5 секунд) при использовании предложения LIKE в сочетании с предложением EXISTS .

Медленные запросы включают две таблицы - [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] )
)

Количество строк (прав) для UserID_i = 2484 в таблице [UserSiteRight_T] довольно мало: 545
(UserID_i = 2484 был выбран случайным образом)

Кроме того, база данных относительно небольшая - всего 23000 строк в таблице [SitePath_T]:

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] )

)


Схема БД

Я пытаюсь получить только идентификаторы сайтов, у которых есть дочерние сайты, доступные для определенного идентификатора пользователя (указанного в таблице [UserSiteRight_T]) как:

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 и соответствующие SitePath_T SiteID_i и Path_v, соответствующие LIKE состояние:

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 - ощущается (и остается) очень медленным. при обновлении дерева навигации

Есть ли предложения по оптимизации / изменению схемы / индексов / запросов базы данных, чтобы получить более быстрый ответ?

Спасибо


sql-server query-optimization sql-server-2005
person Adrian S.    schedule 03.01.2010    source источник
comment
Интересно, будет ли лучше рекурсивный CTE для сайтов, но я не могу сказать по вашим данным, как узнать, каковы корневые значения SITEPATH_T.   -  person OMG Ponies    schedule 03.01.2010

Ответы (3)


arrow_upward
3
arrow_downward

По материалам:

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+'%' )

(что вполне нормально, учитывая тот факт, что вы выполняете Semi Join).

Сначала он фокусируется (правильно) на таблице uusr, чтобы найти записи для этого пользователя. Он уже занимается поиском CIX по этому поводу, и это хорошо. Оттуда он находит соответствующие записи в usp в соответствии с полями SiteID_i.

Итак, теперь рассмотрим тот факт, что он хочет найти сайты по SiteID_i, и какое соединение вы хотите, чтобы это было.

Как насчет объединения слиянием? Это было бы неплохо, но требует сортировки данных с обеих сторон. Это нормально, если индексы в правильном порядке ...

... и после этого вы захотите найти что-то на основе Path. Так как насчет:

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.

person Rob Farley    schedule 07.01.2010
comment
Итак ... подумайте о том, что вы хотите, чтобы строки, выходящие из uusp, располагались в порядке SiteID_i, и что строки, выходящие из usp, будут в том же порядке, что очень помогает соединению. Тогда у вас есть свои пути, и там все еще может быть некоторая боль, но потенциально не слишком сильная. - person Rob Farley; 07.01.2010
comment
Я также собирался сказать, бросьте покрывающий индекс на SitePath_T (Path_v), но у него уже есть индекс с некластеризованным уникальным ограничением. Он по-прежнему выполняет сканирование индекса! Таким образом, остается вопрос: как уменьшить объем данных как можно раньше в плане выполнения? - person Peter Radocchia; 08.01.2010
comment
Привет, Роб, мне любопытно узнать, как это повлияет, если вместо двух индексов на SitePath_t у нас будет только один ...... CREATE INDEX ix_sp на [dbo]. [SitePath_T] (Path_v, SiteID_i); Учитывая, что Path_v может иметь больше фильтрации, поэтому мы сначала отфильтруем, а затем выполним соединение слиянием. Пожалуйста, поправьте меня, если я ошибаюсь. На мой взгляд, если мы можем сохранить традиционный поиск с помощью составного индекса, то мы должны это сделать. - person Nitin Midha; 11.01.2010
comment
Один отсортирован по path_v, а другой по siteid_i. Если оптимизатор начинает с фильтрации пользователя, а затем получает отсортированный список сайтов, то индекс, который начинается с siteid, будет более полезным. Оба они потенциально полезны, но могут быть полезны в разных ситуациях (например, для разных наборов статистики). - person Rob Farley; 11.01.2010

arrow_upward
0
arrow_downward

Я бы попытался добавить индекс внешних ключей в вашу UserSiteRight_T таблицу - они еще не проиндексированы, и индекс по этим полям должен ускорить поиск:

CREATE NONCLUSTERED INDEX IX01_UserSiteRight
  ON UserSiteRight_T(UserID_i)

CREATE NONCLUSTERED INDEX IX02_UserSiteRight
  ON UserSiteRight_T(SiteID_i)

а также в вашей таблице SitePath_T:

CREATE NONCLUSTERED INDEX IX01_SitePath
  ON dbo.SitePath_T(SiteID_i)

Попробуйте установить их, затем снова запустите запросы и сравните время выполнения и планы выполнения - видите ли вы какие-либо улучшения?

Это распространенное заблуждение, но SQL Server не автоматически помещает индекс в столбец внешнего ключа (например, SiteID_i на SitePath_T), хотя общее мнение таково, что внешний ключ полезен и потенциально ускоряет оба обеспечение ссылочной целостности, а также JOINs по этим внешним ключам.

person marc_s    schedule 03.01.2010

arrow_upward
0
arrow_downward

Самостоятельное присоединение к SitePath_T для поиска родителей убивает вас. Возможно, вам стоит добавить столбец для ParentSiteID_i и использовать обычный рекурсивный CTE?

Тогда это становится:

WITH Recurse_CTE AS (
  SELECT 
    us.SiteID_i
  , us.ParentSiteID_i
  , 0 AS RecurseDepth_i
  FROM dbo.SitePath_T us
  JOIN dbo.UserSiteRight_T uusr ON us.SiteID_i = uusr.SiteID_i
  WHERE uusr.UserID_i = 2484
  UNION ALL
  SELECT 
    us.SiteID_i
  , us.ParentSiteID_i
  , rcs.RecurseDepth_i+1 AS RecurseDepth_i
  FROM dbo.SitePath_T us
  JOIN Recurse_CTE rcs ON us.SiteID_i = rcs.ParentSiteID_i
  )
SELECT * FROM Recurse_CTE

Добавьте индекс на SitePath_T (ParentSiteID_i), и производительность должна быть быстрой.

person Peter Radocchia    schedule 08.01.2010