다음을 이해하고 싶습니다.
복잡한 쿼리가 있다고 가정하고 합계 및 순서별로 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 행만 얻습니다).