OFFSET w SQL: pomijanie wierszy i pułapki paginacji

Gdy potrzebuję drugiej strony listy, muszę określić zarówno jej rozmiar, jak i kolejność. OFFSET mówi, ile początkowych wierszy wyniku pominąć. Nie wyszukuje numeru rekordu i nie usuwa danych z tabeli. Pokazuję tutaj odczyt małego, syntetycznego zbioru, bez obietnic dotyczących wydajności konkretnej aplikacji.

Składnia zależy od silnika

Dla PostgreSQL 18, MySQL 8.4 i SQLite zapisuję drugą stronę po dwa wiersze tak:

SELECT id, name
FROM users
ORDER BY id
LIMIT 2 OFFSET 2;

Taką kolejność klauzul opisują dokumentacje PostgreSQL, MySQL oraz SQLite. PostgreSQL akceptuje także odwrotną kolejność tych dwóch klauzul, ale nie przenoszę jej jako wspólnej składni do pozostałych silników. Tutaj konsekwentnie używam LIMIT ... OFFSET ....

W SQL Server 2012 i nowszych odpowiednik wygląda inaczej:

SELECT id, name
FROM users
ORDER BY id
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

Dokumentacja Microsoftu umieszcza OFFSET i FETCH w składni ORDER BY. Nie dopisuję do tego zapytania LIMIT. Podobnie wyglądające fragmenty w różnych bazach nie są dowodem, że całe zapytanie da się przenieść bez zmiany.

Dwa pominięte wiersze, a nie identyfikator

Przyjmijmy, że tabela users ma następującą zawartość. Kolumna id jest unikalna i nie zawiera NULL. W czasie odczytu dane się nie zmieniają.

Po ORDER BY id pomijamy Adę i Bena, czyli pierwsze dwa wiersze. Następne dwa to Celina i Dawid. Oba zapytania z poprzedniej sekcji mają więc w tym przykładzie wynik:

Ewa nie trafia na tę stronę, bo limit wynosi dwa. Luki w identyfikatorach niczego tu nie zmieniają. OFFSET 2 nie oznacza id > 2 ani początku od id = 2. To pozycja w wyniku po zastosowaniu pozostałej logiki zapytania, a nie wartość klucza.

Obliczanie stron i przypadki graniczne

Dla numeracji stron od jedynki pominięcie to (numer_strony - 1) * rozmiar_strony. Przy rozmiarze dwa dostajemy kolejno offsety zero, dwa i cztery. Pierwsza strona zawiera Adę i Bena, druga Celinę i Dawida, a trzecia samą Ewę. Limit określa maksymalną liczbę zwróconych wierszy.

Jeśli pominiemy więcej wierszy, niż ma wynik, strona będzie pusta. Ja w aplikacji ograniczyłbym numer strony i jej rozmiar do uzgodnionych dodatnich wartości oraz przekazywał parametry mechanizmem obsługiwanym przez sterownik. Składnię parametrów trzeba dobrać do używanego silnika i biblioteki; liczby w tym haśle są stałymi do przeczytania przykładu.

MySQL i SQLite mają też wariant z przecinkiem, w którym kolejność wartości może mylić: LIMIT 2, 5 oznacza pominięcie dwóch i pobranie najwyżej pięciu wierszy. Dla czytelności wybrałbym zapis ze słowem OFFSET. Nie polegałbym na domyślnym zachowaniu wartości ujemnych, bo różni się między silnikami.

Kolejność musi rozstrzygać remisy

Jeżeli sortujesz tylko po nazwisku, dwie osoby z tym samym nazwiskiem mogą zamieniać się miejscami na granicy stron. Dodałbym unikalny klucz do sortowania, na przykład ORDER BY surname, id. W naszym małym zbiorze wystarcza ORDER BY id, bo jawnie przyjęliśmy unikalność identyfikatora.

To nadal nie zamraża tabeli. Wyobraź sobie, że pobrałeś pierwszą stronę, czyli ID 11 i 14. Przed następnym zapytaniem ktoś usuwa ID 11. Druga strona z offsetem dwa pomija teraz ID 14 i 18, więc zwraca 23 i 31. Rekord 18 został pominięty podczas przeglądania, mimo jednoznacznego sortowania.

Microsoft opisuje osobno wymagania stabilnego stronicowania: unikalną kolejność oraz niezmienny zbiór między odczytami lub odpowiednio zapewniony spójny obraz danych. Dobór sposobu pracy z transakcjami zależy od aplikacji. Nie utrzymywałbym długiej transakcji przez całą sesję użytkownika bez świadomego zaprojektowania tego mechanizmu.

Kiedy rozważyłbym paginację po kluczu

Dla przewijania dalej po rosnącym, unikalnym id mogę zapamiętać ostatnio pokazany klucz. Po pierwszej stronie byłoby to 14. W PostgreSQL, MySQL i SQLite następny odczyt miałby postać:

SELECT id, name
FROM users
WHERE id > 14
ORDER BY id
LIMIT 2;

Na danych z przykładu nadal otrzymuję 18 i 23, również po usunięciu 11. Ten sposób wiąże następną stronę z wartością klucza. Nie daje automatycznie skoku do dowolnego numeru strony ani migawki wszystkich danych. Zmiana warunku filtrowania lub wartości używanych do sortowania wymaga dalszego przemyślenia.

Przy sortowaniu po kilku kolumnach trzeba uwzględnić pełny porządek, w tym remisy i kierunek sortowania. Nie kopiowałbym samego warunku id > ostatni_id do listy sortowanej według daty. To byłby już inny porządek niż ten, na którym opiera się nasz prosty przykład.

Dokumentacja PostgreSQL ostrzega, że pomijane wiersze nadal wymagają pracy serwera, więc duży offset może być nieefektywny. Nie wyprowadzam z tego uniwersalnego progu liczby stron ani gwarancji przyspieszenia po dodaniu indeksu. Porównałbym plan i zachowanie na docelowym zapytaniu, zanim wybrałbym rozwiązanie dla konkretnego systemu.

Ćwiczenie

Zapisz wynik dla LIMIT 2 OFFSET 4, a następnie dla LIMIT 2 OFFSET 5. W pierwszym przypadku zostanie Ewa, w drugim wynik będzie pusty. Potem wyjaśnij własnymi słowami, dlaczego usunięcie pierwszego wiersza może wpłynąć na drugą stronę. Jeśli potrzebujesz przypomnienia o maksymalnej liczbie wierszy, zajrzyj do hasła LIMIT.