Minęły czasy, kiedy nie trzeba było martwić się o optymalizację wydajności baz danych. Czas nie stoi w miejscu. Każdy nowy przedsiębiorca z branży technologii chce stworzyć kolejnego Facebooka, a jednocześnie dąży do gromadzenia wszystkich danych, do których ma dostęp. Te dane są potrzebne firmom do lepszego uczenia modeli, które pomagają generować zyski. W takich warunkach programiści muszą tworzyć takie API, które umożliwiają szybkie i niezawodne przetwarzanie ogromnych ilości informacji.
Jeśli od jakiegoś czasu zajmujesz się projektowaniem części serwerowych aplikacji lub baz danych, prawdopodobnie pisałeś kod do wykonywania zapytań z paginacją. Na przykład taki:
SELECT * FROM table_name LIMIT 10 OFFSET 40
Czy tak jest?
Ale jeśli paginację wykonywałeś w ten sposób, z przykrością muszę stwierdzić, że z pewnością nie był to najefektywniejszy sposób.
Życzysz sobie sprzeciwu? . , i już stosuje techniki, o których chcę dzisiaj opowiedzieć.
Podaj przynajmniej jednego programistę backendowego, który nigdy nie korzystał OFFSET i LIMIT do wykonywania zapytań z paginacją. W MVP (Minimal Viable Product, minimalny produkt możliwy do wprowadzenia na rynek) oraz w projektach, w których używane są małe zbiory danych, podejście to jest jak najbardziej zasadne. Tak, powiedzmy, "po prostu działa".
Ale jeśli musisz od podstaw stworzyć niezawodne i wydajne systemy, warto wcześniej zadbać o wydajność zapytań do baz danych używanych w takich systemach.
Dziś porozmawiamy o problemach towarzyszących powszechnie stosowanym (niestety) implementacjom mechanizmów wykonywania zapytań z paginacją oraz o tym, jak osiągnąć wysoką wydajność podczas wykonywania takich zapytań.
Co jest nie tak z OFFSET i LIMIT?
Jak już wspomniano, OFFSET i LIMIT sprawdzają się świetnie w projektach, w których nie trzeba pracować z dużymi zbiorami danych.
Problem pojawia się, gdy baza danych rozrasta się do takich rozmiarów, że przestaje mieścić się w pamięci serwera. Ale w trakcie pracy z tą bazą danych nadal trzeba korzystać z zapytań z paginacją.
Aby ten problem się ujawnił, musi wystąpić sytuacja, w której system zarządzania bazą danych (DBMS) wykonuje nieefektywną operację pełnego skanowania tabeli (Full Table Scan) przy każdym zapytaniu z paginacją (w tym samym czasie mogą zachodzić operacje wstawiania i usuwania danych, a przestarzałe dane nie są nam potrzebne!).
Czym jest „pełne skanowanie tabeli” (lub „sekwencyjny przegląd tabeli”, Sequential Scan)? Jest to operacja, w której DBMS sekwencyjnie odczytuje każdy wiersz tabeli, czyli dane w niej zawarte, i sprawdza je pod kątem zgodności z podanym warunkiem. Znane jest, że ten typ skanowania tabel jest najwolniejszy. Dzieje się tak, ponieważ przy jego wykonaniu realizowanych jest wiele operacji wejścia/wyjścia, angażujących system dysków serwera. Sytuację pogarszają opóźnienia związane z pracą z danymi przechowywanymi na dyskach, a także to, że transfer danych z dysku do pamięci to operacja wymagająca wielu zasobów.
Na przykład, jeśli masz zapisy o 100000000 użytkowników i wykonujesz zapytanie z konstrukcją OFFSET 50000000, oznacza to, że DBMS będzie musiał załadować wszystkie te zapisy (a przecież nie są nam nawet potrzebne!), umieścić je w pamięci, a dopiero potem wziąć, powiedzmy, 20 wyników, które są wskazane w LIMIT.
Powiedzmy, że może to wyglądać tak: „wybierz wiersze od 50000 do 50020 z 100000”. To znaczy, że system, aby wykonać zapytanie, najpierw musi załadować 50000 wierszy. Widzisz, ile niepotrzebnej pracy będzie musiał wykonać?
Jeśli nie wierzysz – spójrz na przykład, który stworzyłem, korzystając z możliwości .
Przykład na db-fiddle.com
Tam, z lewej strony, w polu Schema SQL, znajduje się kod, który wykonuje wstawianie do bazy danych 100000 wierszy, a z prawej strony, w polu Query SQL, pokazane są dwa zapytania. Pierwsze, wolne, wygląda tak:
SELECT *
FROM `docs`
LIMIT 10 OFFSET 85000;
A drugie, które stanowi efektywne rozwiązanie tego samego zadania, wygląda tak:
SELECT *
FROM `docs`
WHERE id > 85000
LIMIT 10;
Aby wykonać te zapytania, wystarczy nacisnąć przycisk Run na górze strony. Dzięki temu porównamy dane dotyczące czasu wykonywania zapytań. Okazuje się, że wykonanie nieefektywnego zapytania zajmuje przynajmniej 30 razy więcej czasu niż wykonanie drugiego (od uruchomienia do uruchomienia ten czas różni się, na przykład system może poinformować, że wykonanie pierwszego zapytania zajęło 37 ms, a wykonanie drugiego — 1 ms).
A jeśli danych będzie więcej, sytuacja będzie wyglądała jeszcze gorzej (aby się o tym przekonać — zerknij na mój z 10 milionami wierszy).
To, co właśnie omówiliśmy, powinno dać ci pewne pojęcie o tym, jak tak naprawdę przetwarzane są zapytania do baz danych.
Pamiętaj, że im większa wartość OFFSET — tym dłużej będzie trwało wykonanie zapytania.
Co warto użyć zamiast kombinacji OFFSET i LIMIT?
Zamiast kombinacji OFFSET i LIMIT warto użyć konstrukcji, która opiera się na schemacie:
SELECT * FROM table_name WHERE id > 10 LIMIT 20
To jest wykonanie zapytania z paginacją opartą na kursorze (Cursor based pagination).
Zamiast lokalnie przechowywać bieżące OFFSET i LIMIT i przesyłać je z każdym zapytaniem, należy przechowywać ostatni otrzymany klucz główny (zazwyczaj jest to ID) i LIMIT, w wyniku czego masz zapytania przypominające wyżej podane.
Dlaczego? Chodzi o to, że wyraźnie wskazując identyfikator ostatniego odczytanego wiersza, informujesz swoją SGBD, od którego miejsca powinna rozpocząć wyszukiwanie potrzebnych danych. Zaletą tego rozwiązania jest to, że dzięki wykorzystaniu klucza, wyszukiwanie będzie prowadzone efektywnie, system nie będzie musiał zajmować się wierszami znajdującymi się poza wskazanym zakresem.
Przyjrzyjmy się następującemu porównaniu wydajności różnych zapytań. Oto nieefektywne zapytanie.
Wolne zapytanie
A oto — zoptymalizowana wersja tego zapytania.
Szybkie zapytanie
Oba zapytania zwracają dokładnie tę samą ilość danych. Ale wykonanie pierwszego trwa 12,80 sekundy, a drugiego — 0,01 sekundy. Czujesz różnicę?
Potencjalne problemy
Aby zapewnić skuteczne działanie proponowanej metody wykonywania zapytań, w tabeli powinny znajdować się kolumny zawierające unikalne, uporządkowane indeksy, takie jak identyfikator całkowity. W niektórych specyficznych przypadkach może to decydować o sukcesie zastosowania takich zapytań w celu zwiększenia wydajności pracy z bazą danych.
Oczywiście, tworząc zapytania, należy uwzględnić szczegóły architektury tabel i wybierać te mechanizmy, które najlepiej sprawdzą się na dostępnych tabelach. Na przykład, jeśli musisz pracować w zapytaniach z dużymi ilościami powiązanych danych, może być interesujący artykuł.
Jeśli napotykamy problem braku klucza podstawowego, na przykład, gdy mamy tabelę z relacją "wiele-do-wielu", to tradycyjne podejście przewidujące zastosowanie OFFSET i LIMIT, będzie nam z pewnością odpowiadać. Niemniej jednak, jego zastosowanie może prowadzić do potencjalnie wolnych zapytań. W takich przypadkach polecałbym użycie klucza podstawowego z autoinkrementacją, nawet jeśli jest potrzebny tylko do organizacji zapytań z paginacją.
Jeśli interesuje Cię ten temat — , i — kilka przydatnych materiałów.
Podsumowanie
Główna konkluzja, jaką możemy wyciągnąć, jest taka, że niezależnie od rozmiarów baz danych, zawsze należy analizować szybkość wykonywania zapytań. W dzisiejszych czasach ekstremalnie ważna jest skalowalność rozwiązań, a jeśli od samego początku pracy nad systemem zaprojektuje się wszystko poprawnie, w przyszłości można to ułatwić programiście życie z wieloma problemami.
Jak analizujesz i optymalizujesz zapytania do baz danych?
Źródło: habr.com
