Dane MySQL - najlepszy sposób na wdrożenie stronicowania?

Moja aplikacja na iPhone'a łączy się z moją usługą PHP w celu pobrania danych z bazy danych MySQL. Żądanie może zwrócić 500 wyników.

Jaki jest najlepszy sposób wdrażania stronicowania i pobierania 20 elementów jednocześnie?

Załóżmy, że otrzymuję pierwsze 20 reklam z mojej bazy danych. Jak mogę teraz poprosić o następne 20 reklam?

Z dokumentacji MySQL :

Klauzula LIMIT może służyć do ograniczenia liczby wierszy zwracanych przez instrukcję SELECT. LIMIT przyjmuje jeden lub dwa argumenty liczbowe, które muszą być nieujemnymi stałymi liczbami całkowitymi (z wyjątkiem sytuacji, gdy używane są przygotowane instrukcje).

W przypadku dwóch argumentów pierwszy argument określa przesunięcie pierwszego wiersza do zwrócenia, a drugi określa maksymalną liczbę wierszy do zwrócenia. Przesunięcie początkowego rzędu wynosi 0 (nie 1):

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15

Aby pobrać wszystkie wiersze od określonego przesunięcia do końca zestawu wyników, możesz użyć dużej liczby dla drugiego parametru. Ta instrukcja pobiera wszystkie wiersze od 96-tego wiersza do ostatniego:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

Za pomocą jednego argumentu wartość określa liczbę wierszy do zwrócenia od początku zestawu wyników:

SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

Innymi słowy, LIMIT liczba_wierszy jest równoważna LIMIT 0, liczba_wierszy.

Dla 500 rekordów wydajność prawdopodobnie nie jest problemem, ale jeśli masz miliony rekordów, może być korzystne użycie klauzuli WHERE, aby wybrać następną stronę:

SELECT *
FROM yourtable
WHERE id > 234374
ORDER BY id
LIMIT 20

„234374” tutaj jest identyfikatorem ostatniego rekordu z poprzedniej strony, którą przeglądałeś.

Umożliwi to użycie indeksu na id do znalezienia pierwszego rekordu. Jeśli go użyjesz, LIMIT offset, 20możesz zauważyć, że staje się on coraz wolniejszy, gdy przewijasz stronę do końca. Jak powiedziałem, prawdopodobnie nie będzie to miało znaczenia, jeśli masz tylko 200 rekordów, ale może to zmienić przy większych zestawach wyników.

Kolejną zaletą tego podejścia jest to, że jeśli dane zmienią się między połączeniami, nie przegapisz ani nie uzyskasz powtarzającego się rekordu. Jest tak, ponieważ dodanie lub usunięcie wiersza oznacza przesunięcie wszystkich wierszy po jego zmianie. W twoim przypadku prawdopodobnie nie jest to ważne - myślę, że twoja pula reklam nie zmienia się zbyt często, a zresztą nikt nie zauważy, że otrzymają tę samą reklamę dwa razy z rzędu - ale jeśli szukasz „najlepszego sposobu” to kolejna rzecz, o której należy pamiętać przy wyborze podejścia.

Jeśli chcesz używać LIMIT z przesunięciem (i jest to konieczne, jeśli użytkownik przechodzi bezpośrednio do strony 10000 zamiast stronicowania stron jeden po drugim), możesz przeczytać ten artykuł na temat wyszukiwania w późnych wierszach, aby poprawić wydajność LIMIT z dużym offsetowy.

Zdefiniuj PRZESUNIĘCIE dla zapytania. Na przykład

strona 1 - (rekordy 01-10): offset = 0, limit = 10;

strona 2 - (rekordy 11-20) offset = 10, limit = 10;

i użyj następującego zapytania:

SELECT column FROM table LIMIT {someLimit} OFFSET {someOffset};

przykład dla strony 2:

SELECT column FROM table
LIMIT 10 OFFSET 10;

Jest na ten temat literatura:

• Zoptymalizowane paginowanie za pomocą MySQL , dzięki czemu różnica między zliczaniem całkowitej liczby wierszy, a paginacją.

• Efektywna paginacja za pomocą MySQL , Yahoo Inc. na konferencji Percona Performance Conference 2009. Zespół Percona MySQL udostępnia ją również jako film na Youtube: Wydajna paginacja za pomocą MySQL (wideo) ,

Główny problem zdarza się przy użyciu dużych OFFSETs. Unikają korzystania OFFSETz różnych technik, od idwyboru zakresu w WHEREklauzuli, do pewnego rodzaju stron buforowania lub wstępnego obliczania.

Sugerowane rozwiązania można znaleźć na stronie Use the INDEX, Luke :

• „ Sprawdzanie wyników ”.

• „ Paginacja zrobiona we właściwy sposób ”.

Ten samouczek pokazuje doskonały sposób podziału na strony. Wydajna paginacja za pomocą MySQL

Krótko mówiąc, unikaj używania opcji OFFSET lub dużego LIMITU

możesz też zrobić

SELECT SQL_CALC_FOUND_ROWS * FROM tbl limit 0, 20

Liczba wierszy instrukcji select (bez limitu) jest przechwytywana w tej samej instrukcji select, dzięki czemu nie trzeba ponownie sprawdzać wielkości tabeli. Liczba wierszy jest uzyskiwana za pomocą SELECT FOUND_ROWS ();

Zapytanie 1: SELECT * FROM yourtable WHERE id > 0 ORDER BY id LIMIT 500

Zapytanie 2: SELECT * FROM tbl LIMIT 0,500;

Zapytanie 1 działa szybciej z małymi lub średnimi rekordami, jeśli liczba rekordów wynosi 5000 lub więcej, wynik jest podobny.

Wynik dla 500 rekordów:

Kwerenda 1 zajmuje 9,9999904632568 milisekund

Query2 zajmuje 19,999980926514 milisekund

Wynik dla 8000 rekordów:

Kwerenda 1 zajmuje 129,99987602234 milisekund

Kwerenda 2 zajmuje 160,00008583069 milisekund

Stronicowanie jest proste, gdy pobiera dane z jednej tabeli, ale jest skomplikowane, gdy pobiera dane łączące wiele tabel. Oto dobry przykład z MySql i Spring:
https://www.easycodeforall.com/zpagination1.jsp