Dlaczego MYSQL higher LIMIT offset spowalnia zapytanie?

Scenariusz w skrócie: tabela z ponad 16 milionami rekordów [rozmiar 2GB]. Im wyższy limit przesunięcia za pomocą SELECT, tym wolniejsze staje się zapytanie przy użyciu ORDER BY * primary_key *

Więc 

SELECT * FROM large ORDER BY `id`  LIMIT 0, 30

Zajmuje znacznie mniej niż 

SELECT * FROM large ORDER BY `id` LIMIT 10000, 30

Że zamawia tylko 30 płyt i to samo. Więc nie chodzi o koszty z zamówienia.
Teraz podczas pobierania ostatnich 30 wierszy zajmuje to około 180 sekund. Jak mogę zoptymalizować to proste zapytanie?

Author: Brendan Long, 2010-12-19
Source

5 answers

To normalne, że wyższe przesunięcia spowalniają zapytanie, ponieważ zapytanie musi odliczać pierwsze rekordy OFFSET + LIMIT (i pobierać tylko LIMIT z nich). Im wyższa jest ta wartość, tym dłużej zapytanie jest uruchamiane.

Zapytanie nie może przejść bezpośrednio do OFFSET, ponieważ po pierwsze, rekordy mogą mieć różną długość, a po drugie, mogą występować luki w usuniętych rekordach. Musi sprawdzić i policzyć każdy rekord w drodze.

Zakładając, że id jest PRIMARY KEY z MyISAM tabeli, możesz ją przyspieszyć używając ta sztuczka: 

SELECT  t.*
FROM    (
        SELECT  id
        FROM    mytable
        ORDER BY
                id
        LIMIT 10000, 30
        ) q
JOIN    mytable t
ON      t.id = q.id

Zobacz ten artykuł:

 142
Author: Quassnoi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-12-21 18:06:02

Sam miałem dokładnie ten sam problem. Biorąc pod uwagę fakt, że chcesz zebrać dużą ilość tych danych, a nie określony zestaw 30, prawdopodobnie uruchomisz pętlę i zwiększysz offset o 30.

Więc to, co możesz zrobić zamiast tego, to:

  1. przechowuj ostatni identyfikator zbioru danych (30) (np. lastId = 530)
  2. Dodaj warunek WHERE id > lastId limit 0,30

Więc zawsze możesz mieć zerowe przesunięcie. Będziesz zaskoczony poprawą wydajności.

 144
Author: Nikos Kyr,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-30 07:59:27

MySQL nie może przejść bezpośrednio do 10000-tego rekordu (lub 80000-tego bajtu, jak sugerujesz), ponieważ nie może założyć, że jest spakowany/uporządkowany w ten sposób(lub że ma ciągłe wartości od 1 do 10000). Chociaż w rzeczywistości może tak być, MySQL nie może zakładać, że nie ma dziur/luk/usuniętych identyfikatorów.

Tak więc, jak zauważył bobs, MySQL będzie musiał pobrać 10000 wierszy (lub przejść przez 10000 wpisów indeksu na id) przed znalezieniem 30 do powrotu.

Edytuj : Aby zilustrować mój punkt

Zauważ, że chociaż 

SELECT * FROM large ORDER BY id LIMIT 10000, 30

Would be slow(er), 

SELECT * FROM large WHERE id >  10000 ORDER BY id LIMIT 30

Będzie szybkie (er) i zwróci te same wyniki pod warunkiem, że nie ma brakujących id s (tj. luk).

 15
Author: Riedsio,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-12-27 18:14:08

Czasochłonną częścią obu zapytań jest pobieranie wierszy z tabeli. Logicznie rzecz biorąc, w wersji LIMIT 0, 30 należy pobrać tylko 30 wierszy. W wersji LIMIT 10000, 30 obliczane jest 10000 wierszy i zwracane jest 30 wierszy. Nie może być pewne optymalizacji można zrobić mój proces odczytu danych, ale należy wziąć pod uwagę następujące:

A gdybyś miał klauzulę WHERE w zapytaniach? Silnik musi zwrócić wszystkie wiersze, które się kwalifikują, a następnie posortować dane, a na koniec uzyskać 30 rzędy.

Rozważ również przypadek, w którym wiersze nie są przetwarzane w kolejności po kolejności. Wszystkie kwalifikujące się wiersze muszą zostać posortowane, aby określić, które wiersze mają zostać zwrócone.

 5
Author: bobs,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-12-19 03:28:53

Znalazłem ciekawy przykład, aby zoptymalizować kolejność zapytań SELECT według limitu id X, Y. Mam 35 milionów wierszy, więc znalezienie zakresu wierszy zajęło jakieś 2 minuty.

Oto sztuczka: 

select id, name, address, phone
FROM customers
WHERE id > 990
ORDER BY id LIMIT 1000;

Po prostu umieść gdzie z ostatnim id masz zwiększyć dużo wydajność. Dla mnie to było z 2minut do 1 sekundy:)

Inne ciekawe triki tutaj: http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/

Działa również z strings

 3
Author: sym,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-01 17:04:18