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