Cześć wszystkim! Jestem deweloperem backendu, piszę mikroserwisy w Javie z użyciem Springa. Pracuję w jednym z zespołów zajmujących się rozwojem wewnętrznych produktów w firmie Tinkoff.
W naszym zespole często poruszamy kwestię optymalizacji zapytań w bazach danych. Zawsze chcemy, żeby było jeszcze trochę szybciej, ale nie zawsze możemy obejść się dobrze skonstruowanymi indeksami — czasem trzeba szukać alternatywnych rozwiązań. Podczas jednego z takich poszukiwań w sieci w poszukiwaniu sensownych optymalizacji w pracy z bazami danych znalazłem , autora książki SQL Performance Explained. To ten rzadki rodzaj bloga, w którym można czytać wszystkie artykuły z rzędu.
Chcę przetłumaczyć dla was mały artykuł Markusa. Można go nazwać pewnego rodzaju manifestem, który ma na celu zwrócenie uwagi na stary, ale wciąż aktualny problem wydajności operacji offset w standardzie SQL.
W niektórych miejscach dodam od autora wyjaśnienia i uwagi. Wszystkie takie miejsca oznaczę jako „przyk.” dla większej jasności.
Krótkie wprowadzenie
Myślę, że wielu z was wie, jak problematyczne i spowolnione może być działanie z paginacyjnymi selektami przez offset. A czy wiecie, że można to dość łatwo zastąpić bardziej wydajną konstrukcją?
A zatem kluczowe słowo offset wskazuje bazie, aby pominęła pierwsze n rekordów w zapytaniu. Niemniej jednak, база nadal musi odczytać te pierwsze n rekordów z dysku, w określonej kolejności (przyk.: zastosować sortowanie, jeśli jest wymagane), a dopiero potem możliwe będzie zwrócenie rekordów zaczynających się od n+1 i dalej. Najciekawsze jest to, że problem nie leży w konkretnej implementacji w bazie danych, lecz w pierwotnym określeniu w standardzie:
…the rows are first sorted according to the and then limited by dropping the number of rows specified in the from the beginning…
-SQL:2016, Part 2, 4.15.3 Derived tables (przyk.: obecnie najbardziej używany standard)
Kluczowy punkt tutaj to fakt, że offset przyjmuje jeden parametr — liczbę rekordów, które należy pominąć, i tyle. Zgodnie z takim określeniem, baza danych może tylko pobrać wszystkie rekordy, a następnie odrzucić niepotrzebne. Oczywiście takie określenie offsetu wymusza wykonanie zbędnej pracy. I tu nawet nie ma znaczenia, czy to SQL, czy NoSQL.
Jeszcze trochę bólu
Problemy z offsetem na tym się nie kończą, i oto dlaczego. Jeśli między odczytem dwóch stron danych z dysku inna operacja wstawi nowy rekord, co się wtedy wydarzy?
Kiedy używa się offsetu do pomijania rekordów z poprzednich stron, w sytuacji dodania nowego rekordu między operacjami odczytu różnych stron, prawdopodobnie otrzymasz duplikaty (uwaga: może się to zdarzyć, gdy odczytujemy stronami z użyciem konstrukcji order by, wtedy nowy rekord może wpaść w środek naszej listy wyników).
Rysunek obrazowo przedstawia taką sytuację. Baza odczytuje pierwsze 10 rekordów, po czym wstawiany jest nowy rekord, który przesuwa wszystkie odczytane rekordy o 1. Następnie baza bierze nową stronę z kolejnych 10 rekordów i zaczyna nie od 11, jak powinna, ale od 10, duplikując ten rekord. Istnieją też inne anomalie związane z użyciem tego wyrażenia, ale ta jest najczęstsza.
Jak już ustaliliśmy, nie są to problemy konkretnego SGBD ani ich implementacji. Problem leży w definiowaniu paginacji według standardu SQL. Mówimy SGBD, którą stronę ma pobrać lub ile rekordów ma pominąć. Baza po prostu nie jest w stanie zoptymalizować takiego zapytania, ponieważ ma za mało informacji.
Warto również zauważyć, że to nie jest problem konkretnego słowa kluczowego, a raczej semantyki zapytania. Istnieje jeszcze kilka identycznych syntaktycznych problemów:
- Słowo kluczowe offset, jak już wcześniej wspomniano.
- Konstrukcja złożona z dwóch słów kluczowych limit [offset] (chociaż limit sam w sobie nie jest taki zły).
- Filtrowanie według dolnych granic, oparte na numeracji wierszy (np. row_number(), rownum itd.).
Wszystkie te wyrażenia po prostu mówią, ile wierszy należy pominąć, bez dodatkowych informacji czy kontekstu.
Dalej w tym artykule słowo kluczowe offset jest używane jako ogólne określenie wszystkich tych wariantów.
Życie bez OFFSET
A teraz wyobraźmy sobie, jak wyglądałby nasz świat bez wszystkich tych problemów. Okazuje się, że życie bez offsetu nie jest takie trudne: za pomocą select można wybierać tylko te wiersze, których jeszcze nie widzieliśmy (uwaga: to znaczy te, których nie było na poprzedniej stronie), używając warunku w where.
W tym przypadku opieramy się na tym, że zapytania wykonują się na uporządkowanym zbiorze (stary, dobry order by). Ponieważ mamy uporządkowany zbiór, możemy zastosować stosunkowo prosty filtr, aby uzyskać tylko te dane, które znajdują się za ostatnim zapisem poprzedniej strony:
SELECT ...
FROM ...
WHERE ...
AND id < ?last_seen_id
ORDER BY id DESC
FETCH FIRST 10 ROWS ONLYI to cały zasad działania tego podejścia. Oczywiście, przy sortowaniu po wielu kolumnach wszystko staje się ciekawsze, ale idea jest wciąż ta sama. Ważne jest, aby zauważyć, że ta konstrukcja jest zastosowalna w wielu -rozwiązaniach.
To podejście nazywa się metodą seek lub paginacją opartą na kluczu. Rozwiązuje problem unoszącego się wyniku (przykład: sytuacja z zapisem między odczytami stron, opisana wcześniej) i, co wszyscy kochamy, działa szybciej i stabilniej niż klasyczny offset. Stabilność polega na tym, że czas przetwarzania zapytania nie wzrasta proporcjonalnie do numeru żądanej tabeli (przykład: jeśli chcesz dowiedzieć się więcej o różnych podejściach do paginacji, możesz . Tam także można znaleźć porównawcze benchmarki różnych metod).
Jedna ze slajdów , że paginacja oparta na kluczach, oczywiście, nie jest wszechmogąca — ma swoje ograniczenia. Najważniejsze — nie ma możliwości odczytu losowych stron (przykład: nienastępczo). Jednak w erze nieskończonego przewijania (przykład: z przodu) nie jest to taki wielki problem. Podawanie numeru strony do kliknięcia — w każdym razie złe rozwiązanie przy projektowaniu UI (przykład: zdanie autora artykułu).
A co z narzędziami?
Paginacja oparta na kluczach często nie nadaje się z powodu braku wsparcia narzędziowego dla tej metody. Większość narzędzi deweloperskich, w tym różne frameworki, nie daje wyboru, w jaki sposób będzie wykonywana paginacja.
Sytuację pogarsza to, że opisana metoda wymaga wszechstronnej obsługi w wykorzystywanych technologiach — od bazy danych po wykonanie zapytania AJAX w przeglądarce przy nieskończonym przewijaniu. Zamiast po prostu podawać numer strony, teraz trzeba będzie podawać zestaw kluczy dla wszystkich stron jednocześnie.
Jednak liczba frameworków wspierających paginację na kluczach stopniowo rośnie. Oto, co mamy na chwilę obecną:
- dla Javy;
- dla Ruby;
- i dla Django;
- dla Pythona;
- — API kryteriów dla implementacji JPA;
- dla Perla;
- , мапер для Node.js .
(Przyp.: niektóre linki zostały usunięte, ponieważ w momencie tłumaczenia niektóre biblioteki nie były aktualizowane od 2017—2018 roku. Jeśli chcesz, możesz zajrzeć do źródła.)
Dokładnie w tym momencie potrzebna jest Twoja pomoc. Jeśli rozwijasz lub wspierasz framework, który w jakikolwiek sposób korzysta z paginacji, to proszę, apeluję, błagam Cię o zrobienie natywnego wsparcia dla paginacji na kluczach. Jeśli masz pytania lub potrzebujesz pomocy, chętnie pomogę (, , ) (przyp.: z mojego doświadczenia w rozmowach z Markusem mogę powiedzieć, że naprawdę entuzjastycznie podchodzi do rozpowszechniania tego tematu).
Jeśli korzystasz z gotowych rozwiązań, które Twoim zdaniem zasługują na wsparcie paginacji po kluczach, — stwórz zgłoszenie lub zaproponuj gotowe rozwiązanie, jeśli to możliwe. Można również wskazać w linku ten artykuł.
Podsumowanie
Powód, dla którego tak proste i użyteczne podejście, jak paginacja po kluczach, jest mało rozpowszechnione, nie leży w technicznej realizacji ani nie wymaga jakichkolwiek dużych wysiłków. Główna przyczyna leży w tym, że wielu przyzwyczaiło się widzieć i pracować z offsetem — takie podejście narzucane jest przez sam standard.
W rezultacie niewielu myśli o zmianie podejścia do paginacji, a przez to wsparcie narzędziowe ze strony frameworków i bibliotek rozwija się słabo. Dlatego, jeśli bliska jest Ci idea i cel paginacji bezoffsetowej, — pomóż ją rozpowszechnić!
Źródło:
Autor: Markus Winand
Źródło: habr.com
