Unikaj używania OFFSET i LIMIT w zapytaniach podzielonych na strony

Dawno minęły czasy, kiedy nie trzeba było martwić się optymalizacją wydajności bazy danych. Czas nie stoi w miejscu. Każdy nowy przedsiębiorca technologiczny chce stworzyć kolejnego Facebooka, jednocześnie próbując zebrać wszystkie dane, jakie tylko wpadnie mu w ręce. Firmy potrzebują tych danych, aby ulepszyć modele, które pomogą im zarabiać pieniądze. W takich warunkach programiści muszą tworzyć API, które pozwolą im szybko i niezawodnie pracować z ogromną ilością informacji.

Jeśli projektujesz backendy aplikacji lub baz danych przez dłuższy czas, prawdopodobnie napisałeś kod umożliwiający uruchamianie zapytań podzielonych na strony. Na przykład tak:

SELECT * FROM table_name LIMIT 10 OFFSET 40

Jak to jest?

Ale jeśli tak zrobiłeś paginację, to z przykrością muszę stwierdzić, że nie zrobiłeś tego w najbardziej efektywny sposób.

Chcesz mi się sprzeciwić? Czy możesz nie spędzić czas. Slack, Shopify и mixmax Już korzystają z technik, o których chcę dzisiaj porozmawiać.

Wymień przynajmniej jednego programistę backendowego, który nigdy nie korzystał OFFSET и LIMIT do wykonywania zapytań podzielonych na strony. W MVP (Minimum Viable Product) oraz w projektach, w których wykorzystywane są małe ilości danych, to podejście ma całkiem spore zastosowanie. To „po prostu działa”, że tak powiem.

Jeżeli jednak zachodzi potrzeba stworzenia od podstaw niezawodnych i wydajnych systemów, należy zawczasu zadbać o efektywność odpytywania baz danych wykorzystywanych w takich systemach.

Dzisiaj porozmawiamy o problemach z powszechnie używanymi (szkoda) implementacjami silników zapytań paginowanych i o tym, jak osiągnąć wysoką wydajność podczas wykonywania takich zapytań.

Co jest nie tak z OFFSET i LIMIT?

Jak już powiedziałem, OFFSET и LIMIT Świetnie sprawdzają się w projektach, które nie wymagają pracy z dużą ilością danych.

Problem pojawia się, gdy baza danych rozrośnie się do takiego rozmiaru, że nie mieści się już w pamięci serwera. Jednak podczas pracy z tą bazą danych należy używać zapytań podzielonych na strony.

Aby ten problem się ujawnił, musi zaistnieć sytuacja, w której DBMS ucieka się do nieefektywnej operacji Pełnego Skanowania Tabeli przy każdym zapytaniu podzielonym na strony (choć mogą wystąpić operacje wstawiania i usuwania, a my nie potrzebujemy nieaktualnych danych!).

Co to jest „skanowanie pełne tabeli” (lub „skanowanie sekwencyjne tabeli”, skanowanie sekwencyjne)? Jest to operacja, podczas której SZBD kolejno odczytuje każdy wiersz tabeli, czyli dane w niej zawarte, i sprawdza je pod kątem zgodności z zadanym warunkiem. Ten typ skanowania tabeli jest uważany za najwolniejszy. Faktem jest, że podczas jego wykonywania wykonywanych jest wiele operacji wejścia/wyjścia, które angażują podsystem dyskowy serwera. Sytuację pogarszają opóźnienia związane z pracą z danymi przechowywanymi na dyskach oraz fakt, że przesyłanie danych z dysku do pamięci jest operacją zasobochłonną.

Na przykład masz rekordy 100000000 XNUMX XNUMX użytkowników i uruchamiasz zapytanie z konstrukcją OFFSET 50000000. Oznacza to, że DBMS będzie musiał załadować wszystkie te rekordy (a nawet ich nie potrzebujemy!), umieścić je w pamięci, a następnie pobrać, powiedzmy, 20 wyników zgłoszonych w LIMIT.

Powiedzmy, że mogłoby to wyglądać tak: „wybierz wiersze od 50000 do 50020 z 100000”. Oznacza to, że system będzie musiał najpierw załadować 50000 XNUMX wierszy, aby zakończyć zapytanie. Czy widzisz, ile niepotrzebnej pracy będzie musiała wykonać?

Jeśli mi nie wierzysz, spójrz na przykład, który stworzyłem przy użyciu funkcji db-fiddle.com. 

Przykład na db-fiddle.com

Tam, po lewej stronie, na polu Schema SQL, znajduje się kod, który wstawia do bazy 100000 XNUMX wierszy, a po prawej stronie w polu Query SQL, pokazane zostaną dwa zapytania. Pierwsza, powolna, wygląda tak:

SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;

A drugie, które jest skutecznym rozwiązaniem tego samego problemu, wygląda następująco:

SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;

Aby spełnić te żądania, wystarczy kliknąć przycisk Run na górze strony. Po wykonaniu tej czynności porównujemy informacje o czasie wykonania zapytania. Okazuje się, że wykonanie niewydajnego zapytania trwa co najmniej 30 razy dłużej niż wykonanie drugiego (czas ten różni się w zależności od uruchomienia; na przykład system może zgłosić, że wykonanie pierwszego zapytania zajęło 37 ms, ale wykonanie sekunda - 1 ms).

A jeśli danych będzie więcej, to wszystko będzie wyglądało jeszcze gorzej (aby się o tym przekonać, zerknij na mój przykład z 10 milionami wierszy).

To, co właśnie omówiliśmy, powinno dać ci pewien wgląd w to, jak faktycznie przetwarzane są zapytania do bazy danych.

Należy pamiętać, że im wyższa wartość OFFSET — tym dłużej będzie trwała realizacja żądania.

Czego powinienem użyć zamiast kombinacji OFFSET i LIMIT?

Zamiast kombinacji OFFSET и LIMIT Warto skorzystać z konstrukcji zbudowanej według następującego schematu:

SELECT * FROM table_name WHERE id > 10 LIMIT 20

Jest to wykonanie zapytania z paginacją opartą na kursorze.

Zamiast przechowywać bieżące lokalnie OFFSET и LIMIT i przesyłaj je przy każdym żądaniu, musisz zapisać ostatnio otrzymany klucz podstawowy (zwykle jest to ID) I LIMITw efekcie otrzymane zostaną zapytania podobne do powyższych.

Dlaczego? Chodzi o to, że jawnie podając identyfikator ostatniego odczytanego wiersza, informujesz swój system DBMS, od czego ma rozpocząć wyszukiwanie niezbędnych danych. Co więcej, wyszukiwanie dzięki zastosowaniu klucza będzie przebiegać sprawnie, a system nie będzie musiał być rozpraszany liniami spoza określonego zakresu.

Przyjrzyjmy się poniższemu porównaniu wydajności różnych zapytań. Oto nieskuteczne zapytanie.

Powolne żądanie

A oto zoptymalizowana wersja tego żądania.

Szybka prośba

Obydwa zapytania zwracają dokładnie tę samą ilość danych. Ale ukończenie pierwszego zajmuje 12,80 sekundy, a drugiego 0,01 sekundy. Czy czujesz różnicę?

Możliwe problemy

Aby proponowana metoda zapytania działała efektywnie, tabela musi zawierać kolumnę (lub kolumny) zawierające unikalne, sekwencyjne indeksy, takie jak identyfikator całkowity. W niektórych specyficznych przypadkach może to przesądzić o powodzeniu wykorzystania takich zapytań w celu zwiększenia szybkości pracy z bazą danych.

Naturalnie konstruując zapytania należy wziąć pod uwagę specyficzną architekturę tabel i wybrać te mechanizmy, które najlepiej sprawdzą się na istniejących tabelach. Na przykład, jeśli musisz pracować z zapytaniami zawierającymi duże ilości powiązanych danych, może to być interesujące to artykuł.

Jeśli mamy do czynienia z problemem braku klucza podstawowego, na przykład jeśli mamy tabelę z relacją wiele do wielu, wówczas tradycyjne podejście polegające na użyciu OFFSET и LIMIT, z pewnością nam odpowiada. Jednak jego użycie może skutkować potencjalnie wolnymi zapytaniami. W takich przypadkach zalecałbym użycie klucza podstawowego z automatyczną inkrementacją, nawet jeśli jest on potrzebny tylko do obsługi zapytań podzielonych na strony.

Jeśli interesuje Cię ten temat - tutaj, tutaj и tutaj - kilka przydatnych materiałów.

Wyniki

Główny wniosek, jaki możemy wyciągnąć, jest taki, że niezależnie od tego, o jakiej wielkości baz danych mówimy, zawsze konieczne jest przeanalizowanie szybkości wykonywania zapytań. W dzisiejszych czasach skalowalność rozwiązań jest niezwykle istotna i jeśli od samego początku pracy na danym systemie wszystko zostanie poprawnie zaprojektowane, może to w przyszłości uchronić programistę przed wieloma problemami.

Jak analizować i optymalizować zapytania do bazy danych?

Źródło: www.habr.com