Użycie OFFSET do pomijania wierszy i budowania paginacji

Ej, mam pytanie: co robisz, gdy na stronie sklepu internetowego widzisz za dużo produktów na jednej stronie? No jasne, klikasz na następną stronę. A co się dzieje w tle? Tam działa magia SQL — używana jest komenda OFFSET do pomijania wierszy. Dzisiaj dowiesz się, czym jest OFFSET, po co go używać, jak go stosować i dlaczego to podstawa paginacji danych.

OFFSET — to słowo kluczowe w SQL, które pozwala pominąć określoną liczbę wierszy w wynikach zapytania. To jak przewracanie stron w książce: możesz "pominąć" pierwsze 10 wierszy i zacząć oglądać od 11-tego.

Składnia

SELECT kolumna1, kolumna2
FROM tabela
OFFSET liczba_wierszy;

• OFFSET — słowo kluczowe do pomijania wierszy.
• liczba_wierszy — ile wierszy chcesz pominąć.

Prosty przykład użycia OFFSET

Załóżmy, że mamy tabelę students z takimi danymi:

Chcemy wyświetlić wszystkich studentów zaczynając od trzeciego. Do tego możemy użyć zapytania:

SELECT *
FROM students
OFFSET 2;

Wynik:

SQL "pominął" pierwsze dwa wiersze (Alicja i Bob) i zwrócił wynik zaczynając od trzeciego wiersza. Trochę jak z zakładkami: "tę stronę już czytaliśmy, otwieramy następną".

Łączenie OFFSET z LIMIT

OFFSET zwykle używa się razem z LIMIT. Dzięki temu możesz jednocześnie pominąć wiersze i ograniczyć wynik do określonej liczby rekordów. To szczególnie przydatne do budowania paginacji (page — strona) — wyświetlania danych porcjami, po kilka wierszy na raz.

Załóżmy, że chcemy wyświetlać po 2 rekordy na raz i zacząć od trzeciego wiersza. Zapytanie będzie wyglądać tak:

SELECT *
FROM students
LIMIT 2 
OFFSET 2;

Wynik:

Jak to działa:

1. OFFSET 2 pomija pierwsze dwa wiersze: Alicja i Bob.
2. LIMIT 2 bierze tylko dwa kolejne wiersze z pozostałych.

Budowanie paginacji

No to teraz najciekawsze — budowanie paginacji. Wyobraź sobie, że robisz web-apkę z listą studentów. Na jednej stronie chcesz pokazywać 2 rekordy. Jak to ogarnąć?

Podstawowa zasada:

• Pierwsza strona pomija 0 wierszy: OFFSET 0.
• Druga strona pomija 2 wiersze: OFFSET 2.
• Trzecia strona pomija 4 wiersze: OFFSET 4.
• I tak dalej...

Przykład: wyświetl drugą stronę

Na pierwszej stronie są rekordy 1 i 2 (Alicja i Bob). Na drugiej stronie — rekordy 3 i 4 (Klara i Dan).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 2;

Wynik:

Przykład: wyświetl trzecią stronę

Na trzeciej stronie — rekordy 5 i 6 (jeśli istnieją).

SELECT *
FROM students
ORDER BY id
LIMIT 2 
OFFSET 4;

Wynik:

Wzór na wyliczenie OFFSET

Budując systemy paginacji możesz użyć takiego wzoru, żeby to zautomatyzować:

OFFSET = (numer_strony - 1) * liczba_rekordów_na_stronie

Na przykład:

• Dla pierwszej strony: (1 - 1) * 2 = 0.
• Dla drugiej strony: (2 - 1) * 2 = 2.
• Dla trzeciej strony: (3 - 1) * 2 = 4.

Ważne uwagi dotyczące wydajności

Gdy pracujesz z dużymi tabelami, użycie OFFSET może być mało wydajne, zwłaszcza na późniejszych stronach. Powód? PostgreSQL i tak przegląda wiersze, które pomijasz. Na przykład zapytanie z OFFSET 10000 zmusza DBMS do przejścia przez pierwsze 10 000 wierszy zanim zwróci wynik. W takich przypadkach warto rozważyć alternatywy, np. użycie unikalnego identyfikatora jako markera paginacji.

Alternatywa: paginacja za pomocą kursora

Dla optymalizacji możesz użyć podejścia z "kursorem". Zamiast pomijać wiersze przez OFFSET, możesz zapamiętać id ostatniego wiersza z poprzedniej strony i użyć go do budowy kolejnego zapytania:

SELECT *
FROM students
WHERE id > ostatni_wyswietlony_id
ORDER BY id
LIMIT 2;

To podejście może mocno przyspieszyć pracę z dużymi tabelami.

Praktyczne zastosowanie paginacji

Paginacja jest używana w większości web-aplikacji: sklepy internetowe, blogi, panele admina. Na przykład:

• Wyświetlanie listy produktów w sklepie internetowym;
• Pokazywanie listy użytkowników w systemie CRM;
• Stronicowanie newsów na feedzie.

Paginacja przydaje się też przy analizie dużych ilości danych, żeby pracować na małych porcjach.

Typowe błędy przy użyciu OFFSET

Praca z OFFSET czasem sprawia problemy, szczególnie na początku nauki. Oto kilka typowych błędów:

Brak sortowania. Jeśli nie dodasz ORDER BY, kolejność wierszy w wynikach OFFSET może być losowa.

Zły przykład zapytania:

SELECT * FROM students
OFFSET 5;

Poprawny przykład zapytania:

SELECT * FROM students
ORDER BY id
OFFSET 5;

Zła wartość OFFSET. Jeśli podasz za dużą wartość, wynik będzie pusty.

Problemy z wydajnością. Jak już mówiliśmy, duże OFFSET na późnych stronach mogą być nieefektywne.

Brak filtrów. Jeśli używasz OFFSET i LIMIT bez WHERE, możesz dostać niepotrzebne dane, co pogorszy wydajność.

Kolejność operatorów. Kolejność operatorów w SQL jest sztywna: najpierw LIMIT, potem OFFSET. Możesz pominąć operator, ale nie możesz zmieniać kolejności.