개요
이번 글에서는 우테코에서 진행한 투룻 프로젝트를 통해 쿼리 개선을 경험한 내용을 공유하고자 합니다. 프로젝트에서 쿼리 성능을 개선하기 위해 GROUP BY와 JOIN의 순서를 재설계하고, NL(Nested Loop) JOIN의 드라이빙 테이블 크기를 줄이는 작업을 수행했습니다. 이 과정을 통해 쿼리 성능을 최대 264배 개선할 수 있었습니다.
이 글에서는 정렬 시 인덱스를 활용하는 것이 중요하지만, 드라이빙 테이블의 크기가 쿼리 성능에 미치는 영향이 더 클 수 있다는 사실을 다룹니다. 또한 커버링 인덱스를 활용하여 추가적인 성능 개선을 이룬 경험도 함께 다룰 예정입니다.
투룻 서비스 소개
투룻은 여행기 서비스로, 여행기에 달려있는 태그 기반으로 여행기 목록을 조회하는 기능이 있었습니다. 이 때 여행기 목록은 좋아요 순으로 정렬되어 표시되었습니다.
위의 화면은 태그 기반으로 좋아요 순으로 정렬한 여행기 목록을 보여줍니다.
간략한 테이블 구조 설명
쿼리를 살피기 전에 테이블 구조를 간략하게 설명하겠습니다. 기본적으로 여행기 정보를 저장하는 travelogue 테이블과 태그 정보를 저장하는 tag 테이블이 있습니다. 여행기에는 여러 개의 태그가 존재할 수 있기 때문에 다대다 관계로 중간 테이블인 travelogue_tag 테이블이 존재합니다.
또한 travelogue 테이블에는 like_count라는 좋아요 숫자에 대한 컬럼이 반정규화되어 존재했습니다. 이는 좋아요 순 조회 성능을 높이기 위해 추가된 컬럼입니다.
기존 쿼리
이 때 태그로 필터링 후 좋아요 순으로 정렬하는 쿼리를 다음과 같이 작성하였습니다.
SELECT
t1_0.id,
t1_0.author_id,
t1_0.created_at,
t1_0.deleted_at,
t1_0.like_count,
t1_0.modified_at,
t1_0.thumbnail,
t1_0.title
FROM
travelogue t1_0
JOIN
travelogue_tag tt1_0
ON tt1_0.travelogue_id = t1_0.id
WHERE
tt1_0.tag_id IN (?, ?)
GROUP BY
t1_0.id
HAVING
COUNT(tt1_0.id) = ?
ORDER BY
t1_0.like_count DESC
LIMIT ? OFFSET ?;
여기서 travelogue와 travelogue_tag 테이블을 JOIN한 후, GROUP BY와 HAVING을 사용하여 선택한 태그를 전부 가지고 있는 여행기를 필터링하였습니다. 이후 like_count로 내림차순 정렬하고, LIMIT과 OFFSET을 사용하여 반환하였습니다. 이 때 LIMIT과 OFFSET은 무한 스크롤을 구현하기 위해 페이지 개수만큼 데이터를 자르는 방식으로 사용되었습니다.
해당 쿼리의 실행 계획을 살펴보았더니 다음과 같이 Using temporary; Using filesort가 있는 것을 확인할 수 있었습니다.
+--+-----------+-----+----------+------+---------------------------------------------------------+-------+-------+--------------------------------+-------+--------+--------------------------------------------+
|id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows |filtered|Extra |
+--+-----------+-----+----------+------+---------------------------------------------------------+-------+-------+--------------------------------+-------+--------+--------------------------------------------+
|1 |SIMPLE |tt1_0|null |ALL |fk_travelogue_tag_tag_id,fk_travelogue_tag_travelogue_id |null |null |null |1495116|20.61 |Using where; Using temporary; Using filesort|
|1 |SIMPLE |t1_0 |null |eq_ref|PRIMARY,fk_travelogue_author_id,idx_travelogue_like_count|PRIMARY|8 |touroot_test.tt1_0.travelogue_id|1 |100 |null |
+--+-----------+-----+----------+------+---------------------------------------------------------+-------+-------+--------------------------------+-------+--------+--------------------------------------------+like_count 컬럼에 대한 인덱스가 있었지만, 옵티마이저는 이 인덱스를 활용하지 않고 직접 정렬하는 계획을 선택하였습니다. 인덱스를 활용하지 못했기 때문에 성능 저하가 우려되었고, 이에 따라 테스트 데이터를 넣고 성능을 측정하였습니다.
테스트 데이터 정보
테스트를 위해 travelogue 데이터 100만 건, 태그 17개, 여행기 당 평균 태그 1.5개, 여행기 당 좋아요 수 평균 50개로 설정하고 성능을 측정하였습니다. 실제 환경과의 차이를 조금이라도 줄이기 위해 단순 랜덤으로 데이터를 생성하지 않고, 정규 분포 형태로 데이터를 생성하여 측정하였습니다.
테스트 결과
그 결과 평균 1.853s가 걸렸습니다. 이는 단순히 슬로우 쿼리일 뿐만 아니라 인덱스를 제대로 활용하지 못하는 쿼리였기 때문에 개선이 필요하다고 판단하였습니다. 따라서 100ms까지 개선을 목표로 쿼리 튜닝을 시작하였습니다.
처음에는 옵티마이저가 왜 인덱스를 활용하여 정렬하지 않는 것인지 이해가 되지 않았습니다. NL JOIN에서 드라이빙 테이블을 like_count 인덱스로 삼는다면 추가적인 정렬 작업이 필요 없을 텐데, 이해가 가지 않는 실행 계획이었습니다.
더욱이 데이터가 10만 건인 경우에는 옵티마이저가 like_count 인덱스를 드라이빙 테이블로 삼아 추가적인 정렬 작업이 없었습니다. 즉, 데이터 개수에 따라 옵티마이저의 실행 계획이 바뀌니 더 혼란스러웠습니다.
like_count를 강제로 드라이빙 테이블로 삼아보자
따라서 STRAIGHT_JOIN을 사용하여 like_count 인덱스를 강제적으로 드라이빙 테이블로 삼고 쿼리를 실행했으나, 실행 시간이 8s가 넘어가는 결과를 초래했습니다. 인덱스를 활용하도록 쿼리를 수정했음에도 불구하고 성능이 오히려 더 나빠지는 결과가 나왔습니다.
EXPLAIN ANALYZE 실행 계획을 분석해보니 드라이빙 테이블의 row 수가 100만 개나 되는 것을 확인할 수 있었습니다. (지금 다시 돌려보니, 너무 느린 쿼리라 옵티마이저가 travelogue의 PK 인덱스를 드라이빙 테이블로 삼고 직접 정렬하는 계획을 선택하네요. 그 때 캡쳐라도 미리 해둘 걸 아쉬움이 남습니다.)
반면 travelogue_tag를 드라이빙 테이블로 삼는 실행 계획을 확인해보니, 드라이빙 테이블의 크기가 약 17만 개임을 알 수 있었습니다.
-> Limit: 5 row(s) (actual time=1713..1713 rows=5 loops=1)
-> Sort: t1_0.like_count DESC (actual time=1713..1713 rows=5 loops=1)
-> Filter: (`count(tt1_0.id)` = 2) (actual time=1647..1707 rows=7255 loops=1)
-> Table scan on <temporary> (actual time=1647..1699 rows=169368 loops=1)
-> Aggregate using temporary table (actual time=1647..1647 rows=169367 loops=1)
-> Nested loop inner join (cost=416899 rows=315298) (actual time=1.58..1105 rows=176623 loops=1)
-> Filter: (tt1_0.tag_id in (2,5)) (cost=152507 rows=315298) (actual time=0.872..495 rows=176623 loops=1)
-> Table scan on tt1_0 (cost=152507 rows=1.5e+6) (actual time=0.792..415 rows=1.5e+6 loops=1)
-> Single-row index lookup on t1_0 using PRIMARY (id=tt1_0.travelogue_id) (cost=0.739 rows=1) (actual time=0.00327..0.0033 rows=1 loops=176623)
해당 실행 계획을 보고 예전 데이터베이스 수업 시간에 NL JOIN에서 드라이빙 테이블의 크기가 쿼리 성능에 많은 영향을 끼친다는 것이 기억났습니다. 이에 따라 확인해보니 드라이빙 테이블의 크기가 커질수록 디스크 I/O에서 랜덤 I/O가 많이 발생해 성능이 저하된다는 것을 확인할 수 있었습니다.
(드라이빙 테이블의 크기가 커질수록 seek time과 rotational latency가 증가하게 되기 때문인데, 자세한 내용은 따로 공부해봐도 좋을 듯합니다!)
즉 travelogue_tag를 드라이빙 테이블로 삼는 경우에는 태그에 따라 필터링을 먼저 거쳐 row 개수를 줄인 후 JOIN을 수행하기 때문에 드라이빙 테이블의 크기가 상대적으로 적습니다. 반면 like_count 인덱스를 활용하는 경우에는 JOIN을 한 뒤 GROUP BY와 HAVING 절을 수행해야 했기 때문에 travelogue 테이블 전체를 대상으로 JOIN을 할 수밖에 없었습니다. 이는 GROUP BY와 HAVING 절을 거친 후 LIMIT가 적용되므로 불가피한 상황이었습니다.
처음에는 like_count 인덱스를 활용하여 정렬하는 것이 당연히 빠를 줄 알았지만, 드라이빙 테이블의 크기가 커짐에 따라 NL JOIN의 성능 저하가 훨씬 더 큰 영향을 미쳤습니다. 이로 인해 쿼리 성능이 저하된 것이었습니다.
첫번째 쿼리 개선 - GROUP BY와 JOIN의 순서를 바꿔보자
드라이빙 테이블의 크기를 줄이기 위해 GROUP BY 후 JOIN을 시도하면 성능 개선이 가능할 것이라고 생각하였습니다. OFFSET과 LIMIT 절이 있기 때문에 travelogue의 모든 row를 살피지 않아도 되며, 좋아요 순으로 정렬된 travelogue의 행을 하나씩 가져와 LIMIT 개수가 나올 때까지만 JOIN을 수행하면 되기 때문입니다.
실제로 다음과 같이 쿼리를 개선하고 실행 계획을 분석하였습니다. GROUP BY와 HAVING 절로 태그를 먼저 필터링하고, travelogue와 JOIN을 하도록 변경하였습니다.
SELECT t.id,
t.author_id,
t.created_at,
t.deleted_at,
t.like_count,
t.modified_at,
t.thumbnail,
t.title
FROM travelogue t
JOIN
(SELECT tt.travelogue_id
FROM travelogue_tag AS tt
WHERE tt.tag_id IN (?, ?)
GROUP BY tt.travelogue_id
HAVING COUNT(tt.id) = ?) ttt
ON t.id = ttt.travelogue_id
ORDER BY t.like_count DESC
LIMIT ? OFFSET ?;
실행 계획은 다음과 같았습니다.
-> Limit: 5 row(s) (cost=2.48e+6 rows=0) (actual time=325..326 rows=5 loops=1)
-> Nested loop inner join (cost=2.48e+6 rows=0) (actual time=325..326 rows=5 loops=1)
-> Index scan on t using idx_travelogue_like_count (cost=0.0108 rows=1) (actual time=0.0201..1.12 rows=446 loops=1)
-> Covering index lookup on ttt using <auto_key0> (travelogue_id=t.id) (cost=0.25..2.5 rows=10) (actual time=0.729..0.729 rows=0.0112 loops=446)
-> Materialize (cost=0..0 rows=0) (actual time=325..325 rows=7255 loops=1)
-> Filter: (`count(tt.id)` = 2) (actual time=299..322 rows=7255 loops=1)
-> Table scan on <temporary> (actual time=299..314 rows=169368 loops=1)
-> Aggregate using temporary table (actual time=299..299 rows=169368 loops=1)
-> Index range scan on tt using fk_travelogue_tag_tag_id over (tag_id = 2) OR (tag_id = 5), with index condition: (tt.tag_id in (2,5)) (cost=141885 rows=315298) (actual time=0.0183..218 rows=176623 loops=1)
실제 row 개수가 446개로 이전보다 훨씬 줄어든 것을 확인할 수 있으며, 실행 시간도 약 300ms로 이전보다 6배 정도 개선된 것을 확인하였습니다. 이는 인덱스를 통해 정렬된 travelogue를 하나씩 가져와 LIMIT으로 5개가 나올 때까지만 JOIN을 수행하기 때문에 드라이빙 테이블의 개수가 크게 줄어들어 속도가 많이 개선된 것입니다.
하지만 OFFSET을 1000까지 늘리면 쿼리는 다시 1.4초로 많이 느려졌습니다. 실행 계획을 보니 OFFSET이 증가하게 되면서 travelogue에서 많은 row를 가져와 JOIN을 수행해야 했기 때문에 느려진 것이었습니다. 확인해보니 약 14만 개 정도의 row를 가져오는 것을 확인하였습니다.
그리고 OFFSET이 1000인 경우에는 travelogue_tag를 드라이빙 테이블로 삼은 실행 계획이 약 500ms 정도로 더 빠른 것을 확인할 수 있었습니다. 다음은 실행 계획입니다.
-> Limit/Offset: 5/1000 row(s) (actual time=577..577 rows=5 loops=1)
-> Sort: t.like_count DESC, limit input to 1005 row(s) per chunk (actual time=577..577 rows=1005 loops=1)
-> Stream results (cost=226260 rows=0) (actual time=396..568 rows=7255 loops=1)
-> Nested loop inner join (cost=226260 rows=0) (actual time=396..561 rows=7255 loops=1)
-> Table scan on ttt (cost=2.5..2.5 rows=0) (actual time=396..397 rows=7255 loops=1)
-> Materialize (cost=0..0 rows=0) (actual time=396..396 rows=7255 loops=1)
-> Filter: (`count(tt.id)` = 2) (actual time=373..395 rows=7255 loops=1)
-> Table scan on <temporary> (actual time=373..388 rows=169368 loops=1)
-> Aggregate using temporary table (actual time=373..373 rows=169368 loops=1)
-> Index range scan on tt using fk_travelogue_tag_tag_id over (tag_id = 2) OR (tag_id = 5), with index condition: (tt.tag_id in (2,5)) (cost=166165 rows=315298) (actual time=0.373..279 rows=176623 loops=1)
-> Single-row index lookup on t using PRIMARY (id=ttt.travelogue_id) (cost=0.718 rows=1) (actual time=0.0223..0.0224 rows=1 loops=7255)
인덱스를 활용하지 않고 직접 정렬을 하지만, JOIN에서 드라이빙 테이블의 크기를 보면 row 수가 7255인 것을 확인할 수 있습니다. 즉 드라이빙 테이블의 크기가 훨씬 작기 때문에 직접 정렬하는 것이 인덱스를 활용하는 것보다 오히려 더 빠른 결과를 낳았습니다.
이를 통해 드라이빙 테이블의 크기가 쿼리에 얼마나 큰 영향을 미치는지를 알 수 있었습니다.
두번째 쿼리 개선 - 서브 쿼리 개선(커버링 인덱스)
이번에는 서브 쿼리를 개선하였습니다. 현재는 GROUP BY와 HAVING 절로 태그를 필터링하고 있었으며, 해당 서브쿼리가 동작하는 방식을 간단히 살펴보면 다음과 같습니다.
먼저 실행 계획은 다음과 같습니다.
-> Filter: (`count(tt.id)` = 2) (actual time=391..414 rows=7255 loops=1)
-> Table scan on <temporary> (actual time=391..406 rows=169368 loops=1)
-> Aggregate using temporary table (actual time=391..391 rows=169368 loops=1)
-> Index range scan on tt using fk_travelogue_tag_tag_id over (tag_id = 2) OR (tag_id = 5), with index condition: (tt.tag_id in (2,5)) (cost=166165 rows=315298) (actual time=0.485..295 rows=176623 loops=1)- tag_id FK 인덱스에서 tag_id에 따라 필터링합니다.
- 그 후 travelogue_id로 집계를 합니다.
- 같은 travelogue_id에 대해 row 개수를
COUNT합니다.
여기서 FK 인덱스에 접근할 때 tag_id로 먼저 필터링하는 것은 좋았지만, 문제는 같은 tag_id에 대해 travelogue_id가 정렬되어 있지 않아 직접 하나하나 찾는 수밖에 없었습니다. 따라서 (tag_id, travelogue_id)로 인덱스를 설정하면 GROUP BY의 성능이 더 좋아질 것이라고 생각하였습니다.
인덱스를 추가한 후 확인해보니 다음과 같이 커버링 인덱스로 변경된 것을 확인할 수 있었습니다.
-> Filter: (`count(tt.id)` = 2) (actual time=148..170 rows=7255 loops=1)
-> Table scan on <temporary> (actual time=148..162 rows=169368 loops=1)
-> Aggregate using temporary table (actual time=148..148 rows=169368 loops=1)
-> Filter: (tt.tag_id in (2,5)) (cost=68320 rows=336672) (actual time=0.074..63.6 rows=176623 loops=1)
-> Covering index range scan on tt using idx_travelogue_tag_tag_travelogue over (tag_id = 2) OR (tag_id = 5) (cost=68320 rows=336672) (actual time=0.0723..50.4 rows=176623 loops=1)
실제로 이전에 살펴본 쿼리를 다시 측정해보니 약 300ms 정도의 시간이 측정되었습니다. 즉 커버링 인덱스를 통해서 성능을 개선하였습니다.
세번째 쿼리 개선 - 서브 쿼리 개선(GROUP BY 없애기)
하지만 서브 쿼리 개선은 여기서 멈추지 않았습니다. (tag_id, travelogue_id) 인덱스를 활용하더라도 travelogue_id가 tag_id마다 정렬은 되어있지만 분산되어 있기 때문에 인덱스를 제대로 활용하기 어려워질 것이라고 생각하였습니다.
따라서 위의 쿼리에서 이제는 GROUP BY를 아예 없애고 여러 번 JOIN하는 방식으로 개선하였습니다.
SELECT tt1.travelogue_id
FROM travelogue_tag tt1 JOIN travelogue_tag tt2
ON tt1.travelogue_id = tt2.travelogue_id
WHERE tt1.tag_id = 2 AND tt2.tag_id = 5;
실행 계획을 확인해보니 다음과 같았습니다.
-> Nested loop inner join (cost=199667 rows=165984) (actual time=0.255..125 rows=7255 loops=1)
-> Covering index lookup on tt1 using idx_travelogue_tag_tag_travelogue (tag_id=2) (cost=17085 rows=165984) (actual time=0.121..19.1 rows=88238 loops=1)
-> Covering index lookup on tt2 using idx_travelogue_tag_tag_travelogue (tag_id=5, travelogue_id=tt1.travelogue_id) (cost=1 rows=1) (actual time=0.00107..0.00109 rows=0.0822 loops=88238)
여기서 오직 커버링 인덱스와 JOIN만 있는 것을 확인할 수 있었습니다.
서브 쿼리를 개선했기 때문에 기존 쿼리에서 서브쿼리를 다음과 같이 바꾸고 측정해보았습니다.
SELECT t.id,
t.author_id,
t.created_at,
t.deleted_at,
t.like_count,
t.modified_at,
t.thumbnail,
t.title
FROM
(SELECT tt1.travelogue_id
FROM travelogue_tag tt1 JOIN travelogue_tag tt2
ON tt1.travelogue_id = tt2.travelogue_id
WHERE tt1.tag_id = 2 AND tt2.tag_id = 5) ttt
JOIN
travelogue t
ON t.id = ttt.travelogue_id
WHERE deleted_at IS NULL
ORDER BY t.like_count DESC
LIMIT 5 OFFSET 1000;
실행 계획은 다음과 같았습니다.
-> Limit/Offset: 5/1000 row(s) (actual time=142..142 rows=5 loops=1)
-> Sort: t.like_count DESC, limit input to 1005 row(s) per chunk (actual time=142..142 rows=1005 loops=1)
-> Stream results (cost=317463 rows=165984) (actual time=0.0931..139 rows=7255 loops=1)
-> Nested loop inner join (cost=317463 rows=165984) (actual time=0.0848..135 rows=7255 loops=1)
-> Nested loop inner join (cost=199667 rows=165984) (actual time=0.0714..116 rows=7255 loops=1)
-> Covering index lookup on tt1 using idx_travelogue_tag_tag_travelogue (tag_id=2) (cost=17085 rows=165984) (actual time=0.0442..16.8 rows=88238 loops=1)
-> Covering index lookup on tt2 using idx_travelogue_tag_tag_travelogue (tag_id=5, travelogue_id=tt1.travelogue_id) (cost=1 rows=1) (actual time=0.001..0.00103 rows=0.0822 loops=88238)
-> Single-row index lookup on t using PRIMARY (id=tt1.travelogue_id) (cost=0.61 rows=1) (actual time=0.00243..0.00246 rows=1 loops=7255)
측정해보니 150ms 정도로 이전보다 더 개선된 결과를 얻었습니다.
이 방식은 다음과 같은 추가적인 장점도 가지고 있습니다.
- 투룻 서비스에서 여행기는 최대 3개의 태그만 가질 수 있기 때문에
JOIN도 최대 3번만 일어나며, 즉 무한히JOIN이 발생하지 않습니다. - 인덱스를 추가하게 되는 경우 쓰기 작업에 대해서는 손해가 발생하지만, 위의 경우에는 기존 tag_id FK를
(tag_id, travelogue_id)인덱스로 대체하면 되기 때문에 인덱스를 새로 추가하는 것이 아닙니다.
따라서 GROUP BY를 JOIN으로 변경하여 개선하였습니다.
최종 개선한 쿼리
최종적으로는 서브쿼리로 JOIN하는 것이 아니라 travelogue_tag와 직접 JOIN하는 것으로 결정하였습니다.
SELECT
t.id,
t.author_id,
t.created_at,
t.deleted_at,
t.like_count,
t.modified_at,
t.thumbnail,
t.title
FROM
travelogue t
JOIN
travelogue_tag tt1 ON tt1.travelogue_id = t.id AND tt1.tag_id = 2
JOIN
travelogue_tag tt2 ON tt2.travelogue_id = t.id AND tt2.tag_id = 5
ORDER BY
t.like_count DESC
LIMIT 5 OFFSET 1000;
이유는 위의 쿼리가 가독성이 더 좋고 성능도 가장 좋았기 때문입니다.
최종 성능 측정
최종적으로 실행 시간을 정리하면 다음과 같습니다.
1. like_count 인덱스를 드라이빙 테이블로 삼는 실행 계획 (OFFSET이 0인 경우)
약 7ms 소요
-> Limit: 5 row(s) (cost=1.98e+6 rows=5) (actual time=4.19..7.45 rows=5 loops=1)
-> Nested loop inner join (cost=1.98e+6 rows=5) (actual time=4.17..7.44 rows=5 loops=1)
-> Nested loop inner join (cost=992441 rows=5) (actual time=0.568..7.07 rows=43 loops=1)
-> Index scan on t using idx_travelogue_like_count (cost=0.0266 rows=5) (actual time=0.501..5.2 rows=446 loops=1)
-> Covering index lookup on tt1 using idx_travelogue_tag_tag_travelogue (tag_id=2, travelogue_id=t.id) (cost=1 rows=1) (actual time=0.004..0.00404 rows=0.0964 loops=446)
-> Covering index lookup on tt2 using idx_travelogue_tag_tag_travelogue (tag_id=5, travelogue_id=t.id) (cost=1 rows=1) (actual time=0.00806..0.00809 rows=0.116 loops=43)2. like_count 인덱스를 드라이빙 테이블로 삼는 실행 계획 (OFFSET이 1000인 경우)
약 1s 소요
-> Limit/Offset: 5/1000 row(s) (cost=1.99e+6 rows=5) (actual time=961..962 rows=5 loops=1)
-> Nested loop inner join (cost=1.99e+6 rows=1005) (actual time=4.88..961 rows=1005 loops=1)
-> Nested loop inner join (cost=992546 rows=1005) (actual time=2.3..936 rows=12395 loops=1)
-> Index scan on t using idx_travelogue_like_count (cost=5.45 rows=1005) (actual time=2.24..748 rows=140425 loops=1)
-> Covering index lookup on tt1 using idx_travelogue_tag_tag_travelogue (tag_id=2, travelogue_id=t.id) (cost=1 rows=1) (actual time=0.00122..0.00124 rows=0.0883 loops=140425)
-> Covering index lookup on tt2 using idx_travelogue_tag_tag_travelogue (tag_id=5, travelogue_id=t.id) (cost=1 rows=1) (actual time=0.00187..0.00189 rows=0.0811 loops=12395)3. travelogue_tag를 드라이빙 테이블로 삼는 실행 계획
OFFSET과 상관없이 약 200ms 소요
-> Limit: 5 row(s) (actual time=225..225 rows=5 loops=1)
-> Sort: t.like_count DESC, limit input to 5 row(s) per chunk (actual time=225..225 rows=5 loops=1)
-> Stream results (cost=294116 rows=165984) (actual time=0.341..223 rows=7255 loops=1)
-> Nested loop inner join (cost=294116 rows=165984) (actual time=0.271..217 rows=7255 loops=1)
-> Nested loop inner join (cost=199667 rows=165984) (actual time=0.183..121 rows=7255 loops=1)
-> Covering index lookup on tt1 using idx_travelogue_tag_tag_travelogue (tag_id=2) (cost=17085 rows=165984) (actual time=0.144..18.6 rows=88238 loops=1)
-> Covering index lookup on tt2 using idx_travelogue_tag_tag_travelogue (tag_id=5, travelogue_id=tt1.travelogue_id) (cost=1 rows=1) (actual time=0.00103..0.00107 rows=0.0822 loops=88238)
-> Single-row index lookup on t using PRIMARY (id=tt1.travelogue_id) (cost=0.469 rows=1) (actual time=0.013..0.0131 rows=1 loops=7255)
최종 성능 개선 결과, 최대 1853ms에서 7ms로 개선하여 약 264배 개선하였습니다.
결론
마지막으로 드라이빙 테이블을 무엇으로 삼을지에 대한 결정이 필요했습니다. OFFSET에 따라 더 빠른 실행 계획이 달라질 수 있기 때문에 특정 임계값을 기준으로 STRAIGHT_JOIN을 통해 직접 실행 계획을 지정할까 고민하였습니다. 하지만 결론적으로는 옵티마이저에게 직접 실행 계획 선정을 맡기기로 결정하였습니다.
그 이유는 다음과 같습니다.
- 테스트 데이터와 실제 데이터의 양상 차이: 예를 들어 실제 환경에서 특정 태그가 인기가 많아 해당 태그에 데이터가 몰리는 경우, travelogue_tag를 드라이빙 테이블로 삼더라도 row 개수가 많아져 성능이 어떻게 나올지 예측하기 어렵습니다.
- 임계값 계산의 복잡성: 임계값을 찾는 것도 복잡한 로직을 통해 계산해야 하며, 이는 유지보수에 부담을 줄 수 있습니다.
OFFSET이 큰 쿼리 발생 확률: 무엇보다OFFSET이 큰 쿼리는 자주 발생하지 않습니다. 현재 페이지의 크기가 5개인데,OFFSET이 1000이라는 것은 여행기를 5000개 조회했을 때 나오는 쿼리입니다. 즉 실제 환경에서는OFFSET이 큰 쿼리가 발생할 확률이 매우 낮습니다.
따라서 옵티마이저에게 드라이빙 테이블 선정을 맡기기로 결정하였습니다.
만약 나중에 OFFSET이 큰 쿼리가 자주 발생하고, 옵티마이저가 비효율적으로 드라이빙 테이블을 선정한다면 그때 가서 STRAIGHT_JOIN을 사용하기로 결정하였습니다.
'우테코 > 팀 프로젝트 - 투룻' 카테고리의 다른 글
| [MySQL] INSERT IGNORE 는 데이터 중복 시 왜 새로운 데이터의 삽입을 막을까? (0) | 2024.11.24 |
|---|---|
| 동시성 문제(데드락) 해결기 - X 락인데 왜 공유가 가능하지?????? (2) | 2024.10.30 |