LIMIT를 사용하면 성능이 향상되고 눈에 띄나요? 개의 테이블을 조인한다고

다음을 이해하고 싶습니다.
복잡한 쿼리가 있다고 가정하고 합계 및 순서별로 5 개의 테이블을 조인한다고 가정 해 봅시다. 쿼리 자체에 대한 최적화 (예 : 인덱스)를
제외하고
사용하면 성능에 큰 이점이 LIMIT있습니까? LIMIT를 적용 하기 전에 모든 쿼리 (및 결과)를 처리해야한다고 가정 하므로 LIMIT를 사용하여 결과의 ​​서브 세트를 검색하면 크게 개선 될 수 있습니까?



답변

LIMIT성능 향상 을 활용 하려면 다음이 필요합니다.

  • 당신이 검색하는 데이터를 이해
  • 올바른 열 순서를 올바르게 색인화
  • 쿼리 리팩토링 책임
  • LIMIT전에 사용JOIN

이러한 원칙을 조정하면 먼 길을 갈 수 있습니다.

이 YouTube 비디오보면서 이러한 개념을 배웠습니다 (프랑스어 악센트를 통해주의 깊게 듣기)

이 개념을 사용하여 일부 테이블에서 상위 40 개 기사를 얻는 방법에 대한 매우 까다로운 StackOverflow 질문에 대답했습니다. 2011 년 5 월 12 일 : 조인 테이블에서 단일 행 가져 오기 .

에서 그 질문에 대한 내 대답 (5 월 (16), 2011) , 나는 다음과 같은 쿼리를 작성하고 철저하게 테스트 :

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;

검색어와 함께 줄을 확인하십시오 LIMIT

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

이 하위 쿼리는 3 단계 깊이에 묻혀 있습니다. 이것으로을 사용하여 마지막 40 기사를 얻을 수있었습니다 LIMIT. 그런 다음 필요한 JOIN을 수행했습니다.

교훈

  • LIMIT색인의 카디널리티, 데이터 컨텐츠 및의 결과 세트 크기로 인해 서브 쿼리 내부를 수행하는 것이 항상 답이되는 것은 아닙니다 LIMIT. “행에 멍청이”가 있다면 (질문에 네 가지 원칙을 염두에 두어야 함) 놀랍게도 좋은 결과를 얻을 수 있습니다.
  • LIMIT키만 수집 하여 가능한 한 간단하게 쿼리를 작성하십시오 .

답변

쿼리가 실행되면 먼저 여러 연산자로 구성된 계획으로 변환됩니다. 두 가지 기본 유형의 연산자가 있습니다 : 차단 및 비 차단. 비 차단 연산자는 요청 된 각 행에 대해 해당 자식 또는 자식에서 행 (또는 몇 개의 행)을 검색합니다. 반면에 차단 연산자는 출력을 생성하기 전에 모든 하위의 전체 행 세트를 읽고 처리해야합니다.

정렬은 일반적인 차단 연산자입니다. 따라서 order by를 사용하는 선택은 한계에서 큰 이점을 얻지 못합니다. 그러나 메모리가 덜 필요하고 limit 절이 제공 될 때 더 빠른 정렬 알고리즘을 사용할 수있는 RDBMS가 있습니다. 이 경우 현재 첫 번째 n 행을 저장하고 이전 행이 나올 때 메모리에서 옮기는 것으로 충분합니다. 이는 상당한 성능 향상이 될 수 있습니다. 그러나 MySQL이 그 능력을 가지고 있는지 100 % 확신하지 못합니다.

어느 쪽이든, 제한 정렬조차도 첫 번째 출력 행을 생성하기 전에 전체 입력 행 세트를 처리해야합니다. 이 알고리즘을 구현하면 정렬 속도를 높일 수 있지만 나머지 쿼리가 가장 비싼 경우 전체 실행 시간은 제공된 제한으로 인해 크게 향상되지 않습니다.


답변

제 경우에는 (아직도) 왜 이해가 안 되더라도 라고 말할 수 있습니다 .

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**)

시간은 18 초입니다. 큰 LIMIT와 동일한 요청 :

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**)

10 배 이상 빠르다 !!!

EXPLAIN은 두 요청 모두에 대해 동일한 결과를 제공합니다.

+----+-------------+-------+------------+--------+---------------------------------------------------+---------+---------+------------------------------+--------+----------+----------------------------------------------+
| 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는 결과 세트를 제한하기 위해서만 간섭해야합니다 (즉, LIMIT 4를 수행하는 경우 위 결과 세트의 처음 4 행만 얻습니다).


답변