Вопросы Теги

Оптимизируйте схему базы данных / индексы для более быстрого результата запроса при использовании КАК, и СУЩЕСТВУЕТ пункты

При реализации древовидной структуры по базе данных сервера SQL 2005 ответ запроса занимает слишком много времени (ниже запросов, говорят больше чем 5 секунд), когда использование оператора LIKE, объединенного с, СУЩЕСТВУЕТ пункт.

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

Кроме того, база данных является относительно маленькой - только 23 000 строк в таблице [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] )

)


DB Schema

Я пытаюсь получить только SiteIDs, которые имеют подсайты, доступные определенным UserID (данный таблицей [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 ГДЕ UserID = 2484 и соответствующий 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 reques - чувства (и), очень медленный при обновлении дерева навигации

Какие-либо предложения для оптимизации / изменяют схему базы данных / индексы / запросы для получения более быстрого ответа?

Спасибо

6
задан Glorfindel 29 May 2019 в 04:03
поделиться

4 ответа

Основываясь на:

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 Seek на этом, что хорошо. Оттуда он находит соответствующие записи в usp по полям SiteID_i.

Так что далее рассмотрим тот факт, что он хочет найти Сайты по 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, который находит нужные вам идентификаторы сайтов:

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

Может быть, на этом последнем используется вложенный цикл, но это, надеюсь, не так уж и плохо. Вещь, которая повлияет на вашу систему, будет первые два индекса, которые должны позволить вам увидеть Слияние между двумя таблицами в вашем пункте EXISTS.

.
3
ответ дан 17 December 2019 в 18:16
поделиться

Попробую добавить индекс по посторонним ключам в таблицу 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), хотя общее мнение заключается в том, что посторонний ключ полезен и потенциально ускоряет как обеспечение целостности ссылки, так и JOIN по этим посторонним ключам.

0
ответ дан 17 December 2019 в 18:16
поделиться

Самостоятельное присоединение к 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), и производительность должна быть быстрой.

0
ответ дан 17 December 2019 в 18:16
поделиться

Мэтт Геммелл имеет очень хорошую запись в блоге об этом: http://mattgemmell.com/2008/10/29/favorites-ui-design

-121--4667824-

Вот решение - используйте поставщика комет SaaS, таких как WebSync On-Demand . Нет серверных ресурсов для беспокойства, общего хостинга или нет, так как все это выгружено, и вы можете вытолкнуть информацию по мере необходимости.

Поскольку это SaaS, он будет работать с любым языком сервера. Для PHP уже есть издатель, написанный и готовый идти.

-121--2907580-

Я также хотел бы отдать должное Робу Фарли за понимание схемы/алгоритма и предоставление схемы индексирования.

Но проблема не в схеме индексирования.

Это оптимизатор запросов!

Подробнее об этом в следующей публикации: Проблема SQL Server 2005 T-SQL: можете ли вы доверять оптимизатору запросов? Я знаю, что не могу!

0
ответ дан 17 December 2019 в 18:16
поделиться
Другие вопросы по тегам:

Похожие вопросы: