Paginacja w SQL Server

Mam bardzo dużą bazę danych, około 100 GB. Wykonuję zapytanie:

select * from <table_name>;

i chcę pokazać tylko wiersze od 100 do 200.

Chcę zrozumieć, jak to się dzieje wewnętrznie. Czy baza danych pobiera wszystkie rekordy z dysku do pamięci i odsyła od 100 do 400 wierszy do klienta wysyłającego zapytania? Czy istnieje jakiś mechanizm, więc tylko te rekordy (od 100 do 200) są pobierane z bazy danych - za pomocą mechanizmu indeksowania, takiego jak B-drzewa itp.?

Odkryłem, że jest to związane z koncepcją paginacji, ale nie mogłem dokładnie ustalić, jak to się dzieje na poziomie bazy danych.

W wysłanym zapytaniu:

select * from <table_name>;

Nie ma czegoś takiego jak wiersze od 100 do 200, ponieważ nie określasz ORDER BY. Zamówienie nie jest gwarantowane, chyba że podasz ORDER BY z wielu interesujących powodów, ale nie o to tutaj chodzi.

Aby zilustrować twój punkt, użyjmy tabeli - skorzystam z tabeli Użytkownicy ze zrzutu zrzutu danych przepełnienia stosu i uruchommy to zapytanie:

SELECT * FROM dbo.Users ORDER BY DisplayName;

Domyślnie w polu DisplayName nie ma indeksu, więc SQL Server musi przeskanować całą tabelę, a następnie posortować ją według DisplayName. Oto plan wykonania :

To nie jest ładne - to dużo pracy, przy szacowanym koszcie około 30 000 drzew. (Możesz to zobaczyć, umieszczając kursor myszy nad operatorem wyboru w PasteThePlan.) Co się stanie, jeśli chcemy tylko wiersze 100-200? Możemy użyć tej składni w SQL Server 2012+:

SELECT * FROM dbo.Users ORDER BY DisplayName OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY;

Również plan wykonania jest dość brzydki:

SQL Server wciąż skanuje całą tabelę, aby zbudować posortowaną listę, aby uzyskać wiersze 100-200, a koszt nadal wynosi około 30 tys. Co gorsza, cała lista będzie przebudowywana za każdym razem, gdy zostanie uruchomione zapytanie (ponieważ w końcu ktoś mógł zmienić swoją nazwę wyświetlaną).

Aby przyspieszyć, możemy utworzyć indeks nieklastrowany na DisplayName, który jest kopią naszej tabeli, posortowaną według tego konkretnego pola:

CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);

Z tym indeksem plan wykonania naszego zapytania wyszukuje teraz indeks:

Kwerenda kończy się natychmiast i ma szacunkowy koszt poddrzewa zaledwie 0,66 (w przeciwieństwie do 30 tys.).

Podsumowując, jeśli uporządkujesz dane w sposób, który obsługuje często uruchamiane zapytania, to tak, SQL Server może przyjąć skróty, aby przyspieszyć twoje zapytania. Z drugiej strony, jeśli wszystko, co masz, to stosy lub indeksy klastrowe, to jesteś zepsuty.

Jako dodatek do odpowiedzi Brenta, gdy używa się niekryjącego indeksu, aby uniknąć sortowania, istnieje potencjalny problem z późniejszymi numerami stron, które można zobaczyć po uruchomieniu poniższych

SELECT * 
FROM dbo.Users 
ORDER BY DisplayName 
OFFSET 100000 ROWS 
FETCH NEXT 100 ROWS ONLY;

Plan wykonania pokazuje, że wyszukiwanie zostało wykonane 100 100 razy, mimo że wszystkie operacje oprócz 100 wierszy są następnie filtrowane przez operatora TOP.

Można to złagodzić, korzystając z poniższego wzoru

WITH T
     AS (SELECT Id,
                DisplayName
         FROM   dbo.Users
         ORDER  BY DisplayName
        OFFSET 100000 ROWS 
        FETCH NEXT 100 ROWS ONLY
        )
SELECT U.*
FROM   dbo.Users U
       JOIN T
         ON U.Id = T.Id
ORDER  BY T.DisplayName 

Spowoduje to odfiltrowanie wszystkich oprócz ostatnich 100 wierszy przed rozpoczęciem wyszukiwania, które mogą mieć znaczący wpływ na szybkość przy dużych wartościach przesunięcia.

To naprawdę zależy od tego, jak zaimplementujesz paginację w zapytaniu, charakter danych i sposób skonfigurowania systemu. Można śmiało powiedzieć, że SQL Server spróbuje zwrócić twoje dane przy użyciu tego, co uważa za najmniejszy możliwy wysiłek. Jeśli nie masz wyraźnej kolejności sortowania, filtrowania, grupowania lub okienkowania, wówczas SQL Server może zoptymalizować plan zapytań, tak aby zwracał tylko strony z dysku zawierającego dane wymagane przez zapytanie - lub jeszcze lepiej bezpośrednio z pula buforów. Gdy tylko zaczniesz zmieniać zapytanie w celu włączenia sortowania, grupowania, okienkowania i filtrowania, zaczyna się komplikować.

Jest tutaj bardzo dobry artykuł na temat wydajności SQL , który szczegółowo opisuje różne metody paginacji i ich wpływ na plan zapytań. Gorąco polecam przeczytanie go, a następnie wypróbowanie kilku różnych metod, na które wskazują, i sprawdzenie, jaki plan zapytań jest wybrany w twoim systemie.