Cải thiện hiệu suất khi sắp xếp theo một cột của bảng đã nối

Aug 17 2020

Tôi có một bảng mẹ chứa khóa ngoại cho một bảng tra cứu (ví dụ đơn giản):

CREATE TABLE [dbo].[Parent] (
    [Id] [uniqueidentifier] NOT NULL,
    [LookupId] [uniqueidentifier] NULL
)

CREATE TABLE [dbo].[Lookup] (
    [Id] [uniqueidentifier] NOT NULL,
    [Name] [nvarchar](64) NOT NULL
)

Trong trường hợp này, Parentbảng có hơn 10 triệu hàng và Lookupbảng có khoảng 5.000. Việc Parenttriển khai thực có một số tham chiếu khóa ngoại như vậy đến các bảng khác và mỗi cột trong số đó có thể chứa NULL.

Cả hai bảng ví dụ đều có chỉ mục được phân cụm duy nhất cho các Idcột của chúng , Parentcó chỉ mục không được phân cụm cho LookupIdvà Lookupcó chỉ mục không được phân cụm cho Name.

Tôi đang chạy một truy vấn phân trang trong đó tôi muốn bao gồm giá trị tra cứu trong kết quả: -

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

Điều này chạy nhanh chóng, cũng như đặt hàng trước P.LookupId.

Tuy nhiên, nếu tôi cố gắng sắp xếp theo Name(hoặc thậm chí L.Id), truy vấn chạy chậm hơn đáng kể:

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

Kế hoạch truy vấn cho truy vấn thứ hai ở đây: https://www.brentozar.com/pastetheplan/?id=Sk3SIOvMD

Các câu hỏi có vẻ liên quan khác dường như liên quan đến việc sắp xếp theo các cột trong bảng đầu tiên có thể được giải quyết bằng cách sử dụng một chỉ mục thích hợp.

Tôi đã thử tạo một dạng xem được lập chỉ mục cho truy vấn này, tuy nhiên, SQL Server sẽ không cho phép tôi lập chỉ mục dạng xem vì nó chứa LEFT JOIN mà tôi yêu cầu vì LookupIdcó thể là NULL và nếu tôi sử dụng INNER JOIN thì các bản ghi đó sẽ bị loại trừ.

Có cách nào để tối ưu hóa tình trạng này không?

BIÊN TẬP

Câu trả lời của Rob Farley (cảm ơn!) Rất hay và hoạt động hoàn hảo cho câu hỏi như tôi đã hỏi ban đầu, trong đó tôi ngụ ý rằng tôi đang tham gia một bàn duy nhất.

Hiện tại, tôi có nhiều bảng như vậy và tôi không thể điều chỉnh tất cả bằng cách sử dụng INNER JOIN để sử dụng giải pháp đó.

Hiện tại, tôi đã giải quyết vấn đề này bằng cách thêm hàng "NULL" vào bảng tra cứu để tôi có thể sử dụng INNER JOIN mà không mất bất kỳ hàng nào ở bên trái.

Trong trường hợp của tôi, tôi sử dụng uniqueidentifierdanh tính, vì vậy tôi tạo một dạng xem được lập chỉ mục như sau:

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

Sau đó, tôi thêm một hàng vào Lookupbảng với giá trị 00000000-0000-0000-0000-000000000000cho Idđể luôn có một kết quả khớp ở bên phải của phép nối.

Sau đó, tôi có thể tạo các chỉ mục trên chế độ xem đó nếu cần.

Ngoài ra, vì tôi không sử dụng Enterprise, tôi thấy mình cần sử dụng NOEXPANDgợi ý để đảm bảo các chỉ mục đó được sử dụng:

SELECT *
FROM [ParentView]
WITH (NOEXPAND)
ORDER BY Name
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

Trả lời

10 RobFarley Aug 17 2020 at 11:12

Hãy bắt đầu bằng cách nghĩ về truy vấn đầu tiên đó.

Bạn đang tham gia giữa Cha mẹ và Tra cứu, nhưng là tham gia bên ngoài, vì vậy Cha mẹ sẽ không bao giờ bị xóa khỏi kết quả. Tôi đoán rằng Lookup.Id là duy nhất, do đó, không có Parent nào có nhiều Lookup mà nó tham gia.

Do đó, hàng thứ 50000 trong Parent (theo thứ tự của Parent.Id) sẽ là hàng thứ 50000 trong kết quả nếu chúng ta không có mệnh đề OFFSET.

Do đó, truy vấn có thể di chuyển qua 50000 hàng cho phần bù, nhìn vào 50 hàng tiếp theo và sử dụng điều này để tham gia vào bảng Tra cứu. Sẽ không quan trọng nếu phép nối không tìm thấy gì, đó là phép nối bên ngoài bên trái và nó sẽ chỉ trả về NULL.

Nếu bạn sắp xếp theo một cột khác trong Gốc và cột đó được lập chỉ mục, nó có thể di chuyển qua 50000 hàng đó nhanh chóng.

Bây giờ chúng ta hãy xem xét truy vấn thứ hai.

Bạn muốn 50000 hàng mà bạn bỏ qua (theo hiệu số) là 50000 đầu tiên dựa trên kết quả của phép nối. 50000 hàng đó có thể bao gồm một số hàng NULL, trong đó giá trị Parent.LookupId không tồn tại trong bảng Tra cứu. Ngay cả khi bạn có một chỉ mục đẹp trên Parent.LookupId, có thể bạn sẽ cần liên quan đến hầu hết các hàng, vì trừ khi bạn tìm thấy 50050 hàng không tham gia thành công, bạn sẽ cần phải tiếp tục. Thậm chí 50050 còn nhiều hơn 50 hàng mà bạn tham gia trong truy vấn đầu tiên.

Bây giờ, nếu bạn có một khóa ngoại tại chỗ thì mọi thứ có thể sẽ khác một chút. Sau đó, công cụ SQL phải biết rằng nếu nó có một giá trị nào đó, thì Lookup.Name sẽ không có giá trị nào. Vì vậy, về mặt lý thuyết, nó có thể bắt đầu bằng cách tìm những giá trị rỗng, để xem có 50000 trong số chúng hay không. Nhưng điều đó vẫn còn hơi dài và công cụ SQL không có khả năng tạo ra một kế hoạch như thế này.

Nhưng bạn có thể.

Vì vậy, để giải quyết hiệu suất của truy vấn thứ hai, tôi sẽ làm một số điều.

Bắt đầu bằng cách xem xét những cái không rỗng. Điều đó có nghĩa là các hàng là một phần của phép nối bên trong. Bạn có thể tạo một chế độ xem được lập chỉ mục về điều này, để bạn có thể có một chỉ mục theo thứ tự bạn muốn.

Nhưng bạn cũng sẽ cần những cái mà Parent.LookupID là null - ngoại trừ những cái này, bạn không cần tham gia chút nào.

Nếu bạn thực hiện UNION ALL trên hai tập hợp này (và có thể bao gồm một cột không đổi trong cả hai, để đảm bảo rằng các hàng NULL xuất hiện trước các hàng KHÔNG ĐỦ theo thứ tự của bạn trước), bạn sẽ có thể thấy một số cải tiến.

Một cái gì đó như thế này:

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;

Hy vọng rằng kế hoạch của bạn sẽ bao gồm một toán tử Kết hợp Hợp nhất (Kết hợp), để nó chỉ kéo các hàng mà nó cần từ Quét chỉ mục trên chế độ xem được lập chỉ mục (theo thứ tự Tên) và Tìm kiếm chỉ mục trên Gốc (đối với LookupID).