Chcę zrozumieć następujące.
Załóżmy, że mam skomplikowane zapytanie z, powiedzmy, złączeniem 5 tabel w grupie według sumowań i sortowania według.
Pomijając wszelkie optymalizacje samego zapytania, np. Indeksy itp.
Czy przynoszą jakieś znaczące korzyści w zakresie wydajności LIMIT? Zakładam, że wszystkie zapytania (i wyniki) muszą zostać przetworzone przed zastosowaniem LIMIT, więc używając LIMIT do odzyskania podzbioru wyników, czy oferuje to jakąkolwiek znaczącą / zauważalną poprawę?

Jeśli chcesz skorzystać z LIMITpoprawy wydajności, potrzebujesz

• zrozumieć dane, które pobierasz
• poprawne indeksowanie poprawnej sekwencji kolumn
• wziąć odpowiedzialność za refaktoryzację zapytania
• używając LIMITwcześniejJOIN

Zasady te mogą przejść długą drogę, jeśli potrafisz je zharmonizować.

Nauczyłem się tych koncepcji, oglądając ten film na YouTube (uważnie słuchaj przez francuski akcent)

Użyłem tych koncepcji, aby odpowiedzieć na bardzo trudne pytanie StackOverflow dotyczące uzyskiwania 40 najlepszych artykułów z niektórych tabel: 12 maja 2011 r .: Pobieranie pojedynczego wiersza z tabeli łączenia .

W mojej odpowiedzi na to pytanie (16 maja 2011 r.) Napisałem następujące zapytanie i dokładnie je przetestowałem:

SELECT
  AAA.author_id,
  AAA.date_created,
  IFNULL(BBB.title,'<NO_TITLE>') title,
  IFNULL(CCC.filename,'<NO-IMAGE>') filename,
  IFNULL(CCC.date_added,'<NO-IMAGE-DATE>') image_date
FROM
(
  SELECT
    AA.id,
    AA.date_added,
    BB.author_id,
    BB.date_created
  FROM
  (
    SELECT
      A.id,IFNULL(MAX(B.date_added),'1900-01-01 00:00:00') date_added
      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A
      LEFT JOIN article_images B ON A.id = B.article_id
      GROUP BY A.id
  ) AA
  INNER JOIN articles BB USING (id)
) AAA
LEFT JOIN article_contents BBB ON AAA.id=BBB.article_id
LEFT JOIN article_images CCC
ON (AAA.id=CCC.article_id AND AAA.date_added=CCC.date_added)
ORDER BY AAA.date_created DESC;

Proszę zwrócić uwagę na wiersz w zapytaniu za pomocą LIMIT

      FROM (SELECT id FROM articles ORDER BY date_created DESC LIMIT 40) A

To podzapytanie jest zakopane na trzech poziomach. To pozwoliło mi pobrać ostatnie 40 artykułów LIMIT. Następnie wykonałem niezbędne połączenia.

ZDOBYTA WIEDZA

• Wykonywanie LIMITwewnętrznych podkwerend nie zawsze może być odpowiedzią ze względu na liczność indeksów, zawartość danych i rozmiar zestawu wyników z LIMIT. Jeśli masz wszystkie „kaczki z rzędu” (pamiętaj o czterech zasadach dotyczących zapytania), możesz uzyskać zaskakująco dobre wyniki.
• Spraw, aby Twoje zapytania były maksymalnie uproszczone, LIMITzbierając tylko klucze.

Kiedy zapytanie jest wykonywane, najpierw jest tłumaczone na plan, który składa się z kilku operatorów. Istnieją dwa podstawowe typy operatorów: Blokowanie i Nieblokowanie. Operator nieblokujący pobiera wiersz (lub kilka wierszy) od swojego potomka lub potomków dla każdego żądanego od niego wiersza. Z drugiej strony operator blokujący musi wczytać i przetworzyć cały zestaw wierszy wszystkich swoich elementów potomnych, zanim będzie w stanie wygenerować jakikolwiek wynik.

Sortowanie jest typowym operatorem blokującym. Zatem wybór z zamówieniem według nie korzysta z limitu. Istnieją jednak RDBMS, które mogą wykorzystywać algorytm sortowania, który wymaga mniej pamięci i jest szybszy, gdy podano klauzulę limitu. Wystarczy w tym przypadku po prostu zapisać obecnie pierwsze n wierszy i przenieść je z pamięci w miarę pojawiania się wcześniejszych wierszy. Może to być znaczący wzrost wydajności. Nie jestem jednak w 100% pewien, że MySQL ma tę zdolność.

Tak czy inaczej, nawet sortowanie według limitu musi przetworzyć cały zestaw wierszy wejściowych, zanim będzie mógł wygenerować pierwszy wiersz wyjściowy. Chociaż ten algorytm, jeśli zostanie zaimplementowany, może przyspieszyć sortowanie, jeśli reszta zapytania jest najdroższą częścią, całkowity czas wykonania nie poprawi się znacząco z powodu podanego limitu.

W moim przypadku mogę powiedzieć Tak , nawet jeśli (nadal) nie rozumiem dlaczego.

SELECT g0_.id AS id_0, COUNT(a1_.id_tarifs) AS sclr_1
FROM groupe_jardinerie g0_
INNER JOIN articles_tarifs a1_
  ON (a1_.groupe_jardinerie_id = g0_.id)
WHERE g0_.centrale_id = 511
  AND a1_.date_fin_tarif >= '2018-01-29 10:46:35'
GROUP BY g0_.id;

(result set)

8 rows in set (**18.14 sec**)

Zanotuj czas: 18 sekund. Ta sama prośba z dużym LIMITEM:

SELECT g0_.id AS id_0, COUNT(a1_.id_tarifs) AS sclr_1 
FROM groupe_jardinerie g0_
INNER JOIN articles_tarifs a1_
  ON (a1_.groupe_jardinerie_id = g0_.id)
WHERE g0_.centrale_id = 511 
  AND a1_.date_fin_tarif >= '2018-01-29 10:46:35'
GROUP BY g0_.id
LIMIT 100000000000;

(exact same result set)

8 rows in set (**1.32 sec**)

Ponad dziesięć razy szybciej !!!

WYJAŚNIJ daje ten sam wynik dla obu żądań.

+----+-------------+-------+------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+----------+----------------------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                     | key     | key_len | ref                          | rows   | filtered | Extra                                        |
+----+-------------+-------+------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+----------+----------------------------------------------+
|  1 | SIMPLE      | a1_   | NULL       | ALL    | IDX_438010BBC10784EF                              | NULL    | NULL    | NULL                         | 795135 |    33.33 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | g0_   | NULL       | eq_ref | PRIMARY,IDX_9CA5CF6758A1D71F,IDX_9CA5CF67670C757F | PRIMARY | 4       | phs.a1_.groupe_jardinerie_id |      1 |    50.00 | Using where                                  |
+----+-------------+-------+------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+----------+----------------------------------------------+

LIMIT powinien ingerować tylko w celu ograniczenia zestawu wyników (tzn. Jeśli wykonam LIMIT 4, mam tylko pierwsze 4 wiersze powyższego zestawu wyników).