Cześć wszystkim! Jestem programistą backendowym piszącym mikroserwisy w Java + Spring. Pracuję w jednym z wewnętrznych zespołów rozwoju produktów w Tinkoff.
W naszym zespole często pojawia się kwestia optymalizacji zapytań w DBMS. Zawsze chcesz działać trochę szybciej, ale nie zawsze możesz sobie poradzić z przemyślanie skonstruowanymi indeksami — musisz szukać obejść. Podczas jednej z takich wędrówek po sieci w poszukiwaniu rozsądnych optymalizacji przy pracy z bazami danych, znalazłem , autor książki SQL Performance Poradnik. To ten rzadki typ bloga, na którym można przeczytać wszystkie artykuły z rzędu.
Chciałbym przetłumaczyć dla Państwa krótki artykuł Marcusa. Można to nazwać w pewnym sensie manifestem mającym na celu zwrócenie uwagi na stary, ale wciąż aktualny problem wykonania operacji offsetowej według standardu SQL.
W niektórych miejscach uzupełnię autora wyjaśnieniami i komentarzami. Wszystkie takie miejsca będę nazywać „około”. dla większej przejrzystości
Małe wprowadzenie
Myślę, że wiele osób wie, jak problematyczna i powolna jest praca z wyborem stron za pomocą przesunięcia. Czy wiesz, że można go dość łatwo zastąpić wydajniejszą konstrukcją?
Zatem słowo kluczowe offset nakazuje bazie danych pominięcie pierwszych n rekordów w żądaniu. Jednak baza danych nadal musi odczytać pierwsze n rekordów z dysku, w podanej kolejności (uwaga: zastosuj sortowanie, jeśli jest określone) i dopiero wtedy będzie możliwe zwrócenie rekordów począwszy od n+1. Najciekawsze jest to, że problem nie leży w konkretnej implementacji w DBMS, ale w oryginalnej definicji zgodnie ze standardem:
…wiersze są najpierw sortowane według a następnie ograniczone przez usunięcie liczby wierszy określonej w pliku od początku…
-SQL:2016, część 2, 4.15.3 Tabele pochodne (uwaga: obecnie najczęściej używany standard)
Kluczową kwestią jest to, że offset przyjmuje pojedynczy parametr – liczbę rekordów do pominięcia i to wszystko. Zgodnie z tą definicją, DBMS może jedynie pobrać wszystkie rekordy, a następnie odrzucić niepotrzebne. Oczywiście taka definicja offsetu zmusza nas do wykonania dodatkowej pracy. I nie ma nawet znaczenia, czy jest to SQL, czy NoSQL.
Tylko trochę więcej bólu
Na tym nie kończą się problemy z offsetem. Oto dlaczego. Jeżeli pomiędzy odczytaniem dwóch stron danych z dysku inna operacja wstawi nowy rekord, co się stanie w tym przypadku?
Kiedy offset służy do pomijania rekordów z poprzednich stron, to w sytuacji dodania nowego rekordu pomiędzy odczytami różnych stron najprawdopodobniej otrzymamy duplikaty (uwaga: jest to możliwe, gdy czytamy strona po stronie stosując kolejność według konstrukcji, wówczas w środku naszego wyniku może pojawić się nowy wpis).
Rysunek wyraźnie przedstawia tę sytuację. Baza odczytuje pierwsze 10 rekordów, po czym wstawiany jest nowy rekord, który przesuwa wszystkie przeczytane rekordy o 1. Następnie baza pobiera nową stronę z kolejnych 10 rekordów i zaczyna nie od 11-tego jak powinna, ale od 10., powielając ten rekord. Istnieją inne anomalie związane z użyciem tego wyrażenia, ale jest to najczęstsze.
Jak już się przekonaliśmy, nie są to problemy konkretnego DBMS-u czy jego implementacji. Problem polega na zdefiniowaniu paginacji zgodnie ze standardem SQL. Mówimy systemowi DBMS, którą stronę pobrać lub ile rekordów ma pominąć. Baza danych po prostu nie jest w stanie zoptymalizować takiego żądania, ponieważ jest na to za mało informacji.
Warto też doprecyzować, że nie jest to problem konkretnego słowa kluczowego, a raczej semantyki zapytania. Istnieje kilka innych składni, które są identyczne pod względem problematycznym:
- Słowo kluczowe offset ma taką samą postać, jak wspomniano wcześniej.
- Konstrukcja dwóch słów kluczowych limit [offset] (chociaż limit sam w sobie nie jest taki zły).
- Filtrowanie według dolnych granic na podstawie numeracji wierszy (na przykład row_number(), rownum itp.).
Wszystkie te wyrażenia po prostu mówią, ile linii należy pominąć, bez dodatkowych informacji i kontekstu.
W dalszej części tego artykułu słowo kluczowe offset zostało użyte jako podsumowanie wszystkich tych opcji.
Życie bez OFFSETU
A teraz wyobraźmy sobie, jak wyglądałby nasz świat bez tych wszystkich problemów. Okazuje się, że życie bez offsetu nie jest takie trudne: za pomocą selekcji możesz zaznaczyć tylko te wiersze, których jeszcze nie widzieliśmy (uwaga: czyli te, których nie było na poprzedniej stronie), używając warunku gdzie.
W tym przypadku zaczynamy od tego, że selekcje są wykonywane na uporządkowanym zestawie (stara, dobra kolejność). Ponieważ mamy uporządkowany zbiór, możemy zastosować dość prosty filtr, aby uzyskać tylko te dane, które znajdują się za ostatnim rekordem poprzedniej strony:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYOto cała zasada tego podejścia. Oczywiście sortowanie według wielu kolumn staje się ciekawsze, ale idea jest wciąż taka sama. Należy zauważyć, że ten projekt ma zastosowanie dla wielu -decyzje.
To podejście nazywa się metodą wyszukiwania lub paginacją zestawu kluczy. Rozwiązuje problem pływającego wyniku (uwaga: opisana wcześniej sytuacja z zapisem pomiędzy odczytami stron) i oczywiście, co wszyscy kochamy, działa szybciej i stabilniej niż klasyczny offset. Stabilność polega na tym, że czas przetwarzania żądania nie rośnie proporcjonalnie do liczby żądanej tabeli (uwaga: jeśli chcesz dowiedzieć się więcej o działaniu różnych podejść do paginacji, możesz . Można tam również znaleźć punkty odniesienia porównawcze dla różnych metod).
Jeden ze slajdów że paginacja po kluczach nie jest oczywiście wszechmocna – ma swoje ograniczenia. Najważniejsze jest to, że nie ma możliwości czytania przypadkowych stron (uwaga: niekonsekwentnie). Jednak w dobie niekończącego się scrollowania (uwaga: na froncie) nie stanowi to takiego problemu. Określenie numeru strony do kliknięcia i tak jest złą decyzją przy projektowaniu interfejsu użytkownika (uwaga: opinia autora artykułu).
A co z narzędziami?
Paginacja na klawiszach często nie jest odpowiednia ze względu na brak instrumentalnego wsparcia dla tej metody. Większość narzędzi programistycznych, w tym różne frameworki, nie pozwala wybrać dokładnie, w jaki sposób będzie wykonywana paginacja.
Sytuację pogarsza fakt, że opisana metoda wymaga kompleksowego wsparcia w stosowanych technologiach - od DBMS po wykonanie żądania AJAX w przeglądarce z niekończącym się przewijaniem. Zamiast podawać tylko numer strony, musisz teraz określić zestaw kluczy dla wszystkich stron jednocześnie.
Jednak liczba frameworków obsługujących paginację na kluczach stopniowo rośnie. Oto co mamy w tej chwili:
- dla Javy;
- dla Ruby;
- и dla Django;
- dla Pythona;
- — API kryteriów dla wdrożeń JPA;
- dla Perla;
- , maper dla Node.js .
(Uwaga: niektóre linki zostały usunięte ze względu na fakt, że w momencie tłumaczenia niektóre biblioteki nie były aktualizowane od lat 2017-2018. Jeśli jesteś zainteresowany, możesz zajrzeć do oryginalnego źródła.)
To właśnie w tym momencie potrzebna jest Twoja pomoc. Jeśli opracowujesz lub wspierasz framework, który w jakikolwiek sposób wykorzystuje paginację, to proszę, nalegam, błagam cię o zapewnienie natywnej obsługi paginacji na klawiszach. Jeśli masz pytania lub potrzebujesz pomocy, chętnie pomogę (, , ) (uwaga: z mojego doświadczenia z Marcusem mogę powiedzieć, że jest on naprawdę entuzjastycznie nastawiony do szerzenia tego tematu).
Jeśli korzystasz z gotowych rozwiązań, które Twoim zdaniem zasługują na obsługę paginacji po kluczach, utwórz zapytanie lub nawet zaproponuj gotowe rozwiązanie, jeśli to możliwe. Możesz także podać link do tego artykułu.
wniosek
Powodem, dla którego tak proste i użyteczne podejście, jak paginacja według kluczy, nie jest powszechne, nie jest to, że jest ono trudne technicznie do wdrożenia lub wymaga dużego wysiłku. Głównym powodem jest to, że wielu jest przyzwyczajonych do oglądania i pracy z offsetem - takie podejście jest podyktowane samym standardem.
W rezultacie niewiele osób myśli o zmianie podejścia do paginacji, przez co wsparcie instrumentalne ze strony frameworków i bibliotek słabo się rozwija. Dlatego jeśli idea i cel paginacji bez offsetu jest Ci bliski, pomóż ją rozpowszechnić!
Źródło:
Autor: Markus Winand
Źródło: www.habr.com