조인 된 테이블의 열을 기준으로 정렬 할 때 성능 향상
조회 테이블에 대한 외래 키가 포함 된 부모 테이블이 있습니다 (간단한 예).
CREATE TABLE [dbo].[Parent] (
[Id] [uniqueidentifier] NOT NULL,
[LookupId] [uniqueidentifier] NULL
)
CREATE TABLE [dbo].[Lookup] (
[Id] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](64) NOT NULL
)
이 경우 Parent테이블에는 1,000 만 개 이상의 행이 있고 Lookup테이블에는 약 5,000 개의 행 이 있습니다. 실제 Parent구현에는 다른 테이블에 대한 이러한 외래 키 참조가 여러 개 있으며 각 열에는 NULL이 포함될 수 있습니다.
두 예제 테이블 모두 해당 Id열에 대해 고유 한 클러스터형 인덱스 Parent가 있고에 대한 비 클러스터형 인덱스가 LookupId있으며 Lookup에 대한 비 클러스터형 인덱스가 Name있습니다.
결과에 조회 값을 포함하려는 페이지 된 쿼리를 실행하고 있습니다.
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY P.Id
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
이것은에서 주문하는 것처럼 빠르게 실행됩니다 P.LookupId.
그러나, 나는에 의해 주문하려고하면 Name(또는 L.Id), 쿼리가 상당히 느리게 실행 :
SELECT
P.Id,
L.Name
FROM Parent P
LEFT JOIN Lookup L ON P.LookupId = L.Id
ORDER BY L.Name
OFFSET 500000 ROWS FETCH NEXT 50 ROWS ONLY
두 번째 쿼리에 대한 쿼리 계획은 다음과 같습니다. https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD
다른 겉보기 관련 질문은 적절한 색인을 사용하여 해결할 수있는 첫 번째 테이블의 열 순서를 포함하는 것 같습니다.
이 쿼리에 대해 인덱싱 된 뷰를 만들려고 시도했지만 SQL Server는 LookupIdNULL 일 수 있기 때문에 필요한 LEFT JOIN이 포함되어 있고 INNER JOIN을 사용하면 해당 레코드가 제외 되기 때문에 뷰를 인덱싱 할 수 없습니다.
이 상황을 최적화하는 방법이 있습니까?
편집하다
Rob Farley의 답변 (감사합니다!)은 제가 원래 질문했던 질문에 완벽하게 작동합니다. 여기에서 제가 단일 테이블에 참여하고 있음을 암시했습니다.
현재로서는 이러한 테이블이 여러 개 있으며 해당 솔루션을 사용하기 위해 INNER JOIN을 사용하여 모두 조정할 수 없었습니다.
지금까지는 조회 테이블에 "NULL"행을 추가하여이 문제를 해결하여 왼쪽의 행을 잃지 않고 INNER JOIN을 사용할 수 있습니다.
제 경우에는 uniqueidentifierID를 사용 하므로 다음과 같이 인덱싱 된 뷰를 만듭니다.
CREATE VIEW [dbo].[ParentView]
WITH SCHEMABINDING
AS
SELECT
P.Id,
L.Name
FROM [dbo].Parent P
INNER JOIN [dbo].Lookup L ON ISNULL(P.LookupId, '00000000-0000-0000-0000-000000000000') = L.Id
그런 다음 for Lookup값이 있는 테이블에 행을 추가 하여 조인 오른쪽에 항상 일치 항목이 있도록합니다.00000000-0000-0000-0000-000000000000Id
그런 다음 필요에 따라 해당 뷰에 인덱스를 만들 수 있습니다.
또한 Enterprise를 사용하지 않기 때문에 NOEXPAND해당 인덱스 를 사용하려면 힌트를 사용해야합니다 .
SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY
답변
첫 번째 쿼리에 대해 생각해 보겠습니다.
부모와 조회간에 조인하지만 외부 조인이므로 부모는 결과에서 제거되지 않습니다. 나는 Lookup.Id가 고유하다고 추측 할 것이므로 어떤 부모도 조인하는 여러 조회를 가지지 않을 것입니다.
따라서 OFFSET 절이 없으면 Parent의 50000 번째 행 (Parent.Id로 정렬 됨)이 결과에서 50000 번째 행이됩니다.
따라서 쿼리는 오프셋에 대해 50000 개 행을지나 이동하고 다음 50 개 행을보고이를 사용하여 조회 테이블에 조인 할 수 있습니다. 조인이 아무것도 찾지 못하는 경우에는 중요하지 않습니다. 왼쪽 외부 조인이고 NULL을 반환합니다.
부모의 다른 열을 기준으로 정렬하고 색인이 생성 된 경우 해당 50000 행을 빠르게 지나갈 수 있습니다.
이제 두 번째 쿼리를 살펴 보겠습니다.
(오프셋으로) 무시하는 50000 개 행이 조인 결과에 따라 처음 50000 개가되기를 원합니다. 이러한 50000 행에는 NULL 인 일부가 포함될 수 있으며, 여기서 Parent.LookupId 값은 조회 테이블에 존재하지 않습니다. Parent.LookupId에 좋은 인덱스가 있더라도 성공적으로 조인되지 않는 50050 개의 행을 찾지 않는 한 계속 진행해야하기 때문에 대부분의 행을 포함해야 할 것입니다. 50050조차도 첫 번째 쿼리에서 조인하는 50 개 행보다 많습니다.
이제 외래 키가 있으면 상황이 약간 다를 수 있습니다. 그런 다음 SQL 엔진은 값이 전혀 있으면 Lookup.Name이 null이 아님을 알아야합니다. 따라서 이론적으로는 null 인 것을 찾아서 50000 개가 있는지 확인하는 것으로 시작할 수 있습니다. 그러나 그것은 여전히 약간의 확장이며 SQL 엔진은 이와 같은 계획을 생성하지 않을 것입니다.
하지만 할 수 있습니다.
따라서 두 번째 쿼리의 성능을 해결하기 위해 몇 가지 작업을 수행합니다.
null이 아닌 것을 고려하여 시작하십시오. 이는 내부 조인의 일부인 행을 의미합니다. 이것에 대해 인덱싱 된 뷰를 만들 수 있으므로 원하는 순서대로 인덱스를 가질 수 있습니다.
그러나 Parent.LookupID가 null 인 항목도 필요합니다. 단, 이러한 항목의 경우 조인이 전혀 필요하지 않습니다.
이 두 세트에 대해 UNION ALL을 수행하면 (그리고 두 세트 모두에 상수 열을 포함하여 NULL 행이 주문에서 NOT NULL 행 앞에 나타나는지 확인) 약간의 개선을 볼 수 있습니다.
이 같은:
SELECT ID, Name
FROM
(
SELECT i.ID, i.Name, 2 as SetNumber
FROM dbo.MyIndexedView i
UNION ALL
SELECT p.ID, NULL, 1 as SetNumber
FROM dbo.Parent p
WHERE p.LookupID IS NULL
) u
ORDER BY u.SetNumber, u.Name
OFFSET 50000 ROWS FETCH NEXT 50 ROWS ONLY;
계획에 병합 조인 (연결) 연산자가 포함되어 인덱싱 된 뷰의 인덱스 스캔 (이름 순서) 및 부모의 인덱스 검색 (LookupID 용)에서만 필요한 행을 가져 오길 바랍니다.