게시판 조회 쿼리 개선
게시판 조회 쿼리
게시판조회는 페이징으로 특정 글 목록을 표시합니다.
조건1 : 목록의 글마다 글에 달린 댓글 수가 있어야합니다.
조건2 : 목록의 글마다 글이 받은 추천 수가 있어야합니다.
조건3 : 차단되거나 차단한 회원의 글은 보이지 않습니다.
결과 요약
10000개의 글이 있을 때 페이징으로 20개 글을 조회하는 테스트를 하였습니다.
| 쿼리 | 첫 페이지 | 중간 페이지 | 마지막 페이지 |
| 최초 쿼리 | 54ms | 78ms | 130ms |
| 리팩토링 후 문제 발생 | 778ms | 770ms | 744ms |
| 최종 개선 | 23ms | 44ms | 66ms |
1. 최초 쿼리
쿼리dsl 사용 시 발생하는 sql입니다.
SELECT
p1_0.post_id,
p1_0.title,
p1_0.views,
(SELECT CAST(COUNT(pl1_0.post_like_id) AS SIGNED)
FROM post_like pl1_0
WHERE pl1_0.post_id = p1_0.post_id),
p1_0.created_at,
m1_0.member_id,
COALESCE(m1_0.member_name, '탈퇴한 사용자'),
(SELECT CAST(COUNT(c1_0.comment_id) AS SIGNED)
FROM comment c1_0
WHERE c1_0.post_id = p1_0.post_id
AND c1_0.deleted = 0)
FROM
post p1_0
LEFT JOIN
member m1_0 ON m1_0.member_id = p1_0.member_id
WHERE
p1_0.is_notice = 0
AND (
m1_0.member_id NOT IN (
SELECT mb1_0.black_member_id
FROM member_blacklist mb1_0
WHERE mb1_0.request_member_id = 1
)
OR m1_0.member_id IS NULL
)
ORDER BY
p1_0.created_at DESC
LIMIT
0, 20;
SELECT
COUNT(DISTINCT p1_0.post_id)
FROM
post p1_0
LEFT JOIN
member m1_0 ON m1_0.member_id = p1_0.member_id
WHERE
p1_0.is_notice = 0
AND (
m1_0.member_id NOT IN (
SELECT mb1_0.black_member_id
FROM member_blacklist mb1_0
WHERE mb1_0.request_member_id = 1
)
OR m1_0.member_id IS NULL
);두개의 서브쿼리가 존재함에 따라, 20개의 게시글을 가져오면 좋아요 수와 댓글 수를 계산하기 위해 각각 20번씩 총 40번의 추가루프가 발생합니다. 이는 N+1은 아닙니다. N+1은 추가로 커넥션을 생성하고 쿼리를 발생시키며 RTT도 증가합니다.
이러한 서브쿼리는 한번의 쿼리에 수행되고 DB내부에서 루프를 돌게됩니다. N+1보다는 비교할 수 없이 빠릅니다.
하지만 루프를 없애기위해 서브쿼리에서 조인 방식으로 쿼리를 변경해보았습니다.
2. 리팩토링 후 쿼리
서브쿼리를 제거하고 전부 조인으로 해결한 버전입니다.
select
p1_0.post_id,
p1_0.title,
p1_0.views,
cast(count(distinct pl1_0.post_like_id) as signed) as like_count,
p1_0.created_at,
m1_0.member_id,
coalesce(m1_0.member_name, '탈퇴한 사용자'),
cast(count(distinct c1_0.comment_id) as signed) as comment_count
from
post p1_0
left join
member m1_0
on m1_0.member_id=p1_0.member_id
left join
post_like pl1_0
on pl1_0.post_id=p1_0.post_id
left join
comment c1_0
on c1_0.post_id=p1_0.post_id
and c1_0.deleted = 0
left join
member_blacklist mb1_0
on p1_0.member_id=mb1_0.black_member_id
and mb1_0.request_member_id = 1
where
p1_0.is_notice = 0
and mb1_0.member_black_list_id is null
group by
p1_0.post_id,
p1_0.title,
p1_0.views,
p1_0.created_at,
m1_0.member_id,
m1_0.member_name
order by
p1_0.created_at desc
limit
0, 20;
select
count(distinct p1_0.post_id)
from
post p1_0
left join
member_blacklist mb1_0
on p1_0.member_id=mb1_0.black_member_id
and mb1_0.request_member_id = 1
where
p1_0.is_notice = 0
and mb1_0.member_black_list_id is null;그러나 이 방식에서 쿼리가 약 10배 느려졌습니다.
리팩토링 후 쿼리 실행계획
실행계획은 아래에서 위로, 안쪽에서 바깥으로 읽으면 됩니다. 코드에 읽는 순서를 숫자로 적어놨습니다.
13. -> Limit: 20 row(s) (actual time=495..495 rows=20 loops=1)
12. -> Sort: p1_0.created_at DESC, limit input to 20 row(s) per chunk (actual time=495..495 rows=20 loops=1)
11. -> Stream results (actual time=402..493 rows=10057 loops=1)
10. -> Group aggregate: count(distinct `comment`.comment_id), count(distinct post_like.post_like_id) (actual time=402..484 rows=10057 loops=1)
9. -> Sort: p1_0.post_id, p1_0.title, p1_0.views, p1_0.created_at, m1_0.member_id, m1_0.member_name (actual time=402..415 rows=101776 loops=1)
8. -> Stream results (cost=104e+6 rows=91.9e+6) (actual time=0.87..256 rows=101776 loops=1)
7. -> Filter: (mb1_0.member_black_list_id is null) (cost=104e+6 rows=91.9e+6) (actual time=0.855..175 rows=101776 loops=1)
6. -> Nested loop antijoin (cost=104e+6 rows=91.9e+6) (actual time=0.854..169 rows=101776 loops=1)
4. -> Nested loop left join (cost=12.5e+6 rows=91.9e+6) (actual time=0.848..130 rows=101830 loops=1)
3. -> Nested loop left join (cost=931673 rows=9.23e+6) (actual time=0.834..49.8 rows=11830 loops=1)
2. -> Nested loop left join (cost=2391 rows=5045) (actual time=0.826..36.7 rows=10060 loops=1)
1. -> Index lookup on p1_0 using idx_post_notice_created (is_notice=0) (cost=625 rows=5045) (actual time=0.816..17.1 rows=10060 loops=1)
1. -> Single-row index lookup on m1_0 using PRIMARY (member_id=p1_0.member_id) (cost=0.25 rows=1) (actual time=0.00179..0.00182 rows=1 loops=10060)
3. -> Covering index lookup on pl1_0 using FKj7iy0k7n3d0vkh8o7ibjna884 (post_id=p1_0.post_id) (cost=1.23 rows=1830) (actual time=0.00113..0.00117 rows=0.182 loops=10060)
4. -> Covering index lookup on c1_0 using idx_comment_post_deleted (post_id=p1_0.post_id, deleted=0) (cost=0.255 rows=9.95) (actual time=0.00425..0.00627 rows=8.45 loops=11830)
5. -> Single-row covering index lookup on mb1_0 using uk_member_blacklist_request_black (request_member_id=1, black_member_id=p1_0.member_id) (cost=13.7e-6 rows=1) (actual time=273e-6..273e-6 rows=530e-6 loops=101830)1. 인덱스를 사용해서 공지글을 제외한 post를 1번 스캔했고 1만행을 스캔했습니다. 이 과정에서 17ms가 소요됐습니다. 그리고 멤버 PK로 한 행씩 10060번 스캔했습니다. 이 과정에서 18ms가 소요됐습니다.
2. 1번을 레프트 조인합니다.
3. 커버링 인덱스로 글추천테이블을 레프트 조인했습니다.약 11ms가 소모되었습니다.
4. 커버링 인덱스로 글과 삭제되지 않은 댓글을 레프트 조인했습니다. 여기서 70ms가 소요되었고 행 폭발이 일어났습니다. 평균적으로 글당 10개의 행이 늘어나 10만개의 행이 생성됐습니다.
5 - 6. 블랙리스트 테이블을 커버링인덱스로 찾아 안티 조인했습니다. not exists를 활용한 형태고 시간은 39ms 소요되었습니다.
7. 이후 블랙리스트가 null이 아닌지 검사를했습니다
8. 10만행을 대상으로 결과를 버퍼에 담고 스트림 방식으로 흘려보냅니다. 여기서 80ms가 소요됐고 120ms동안 작업이 멈췄습니다. 이것은 불어난 10만개의 행 때문이고 그로인해 Group aggregate가 초기화하는데 시간이 크게 소요되었습니다.
9. 정렬을하는데 15ms가 소비되었습니다.
10. 그리고 집계를 하는데 80ms가 소비되었고 그제서야 불어난 행이 1만개가 되었습니다.
11 - 12. 다시 스트림과 정렬을 하였고 이 과정에서 90ms가 소비되었습니다..
카운트 실행계획
-> Aggregate: count(distinct p1_0.post_id) (cost=7436 rows=1) (actual time=32.4..32.4 rows=1 loops=1)
-> Filter: (mb1_0.member_black_list_id is null) (cost=6931 rows=5045) (actual time=0.749..29.8 rows=10057 loops=1)
-> Nested loop antijoin (cost=6931 rows=5045) (actual time=0.748..29.1 rows=10057 loops=1)
-> Index lookup on p1_0 using idx_post_notice_created (is_notice=0) (cost=625 rows=5045) (actual time=0.739..14.8 rows=10060 loops=1)
-> Single-row covering index lookup on mb1_0 using uk_member_blacklist_request_black (request_member_id=1, black_member_id=p1_0.member_id) (cost=0.25 rows=1) (actual time=0.00131..0.00131 rows=298e-6 loops=10060)인덱스를 적절하게 활용했지만 이전 쿼리에서 너무 많은 행을 전달하여 32ms걸렸습니다.
서브쿼리방식의 루프를 방지하기 위해 조인을 도입했지만 10만개로의 행 폭발때문에 오히려 더 느려졌습니다.
행이 폭발한 이유는 1:N의 LEFT JOIN때문입니다.
이것을 방지하는 방법은 여러가지가 있습니다.
1. 메인쿼리를 시행하고 가져온 ID에 대해서만 IN절로 추가쿼리를 시행하고 애플리케이션에에서 합치기.
2. 다시 원래대로 돌아가서 필요한 부분만 서브쿼리
3. 반정규화
3번은 프로젝트 전체에 미치는 영향이 너무 컸고 2번은 이미 해보았기 때문에 1번을 선택하기로 했습니다
3. 최종 개선 쿼리
SELECT
p1_0.post_id,
p1_0.title,
p1_0.views,
(SELECT CAST(COUNT(pl1_0.post_like_id) AS SIGNED)
FROM post_like pl1_0
WHERE pl1_0.post_id = p1_0.post_id),
p1_0.created_at,
m1_0.member_id,
COALESCE(m1_0.member_name, '탈퇴한 사용자')
FROM
post p1_0
LEFT JOIN
member m1_0 ON m1_0.member_id = p1_0.member_id
WHERE
p1_0.is_notice = 0
ORDER BY
p1_0.created_at DESC
LIMIT
0, 20;
SELECT
COUNT(DISTINCT p1_0.post_id)
FROM
post p1_0
WHERE
p1_0.is_notice = 0;
SELECT
DISTINCT CASE
WHEN (mb1_0.request_member_id = 1) THEN mb1_0.black_member_id
ELSE mb1_0.request_member_id
END
FROM
member_blacklist mb1_0
WHERE
mb1_0.request_member_id = 1
OR mb1_0.black_member_id = 1;
SELECT
c1_0.post_id,
COUNT(c1_0.comment_id)
FROM
comment c1_0
WHERE
c1_0.post_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
GROUP BY
c1_0.post_id;추천 수는 글과 도메인이 같아 서브쿼리로 두었습니다. 멤버는 도메인은 다르지만 개발편의를 위해 메인쿼리에 두었습니다. 글과 멤버는 1:1관계라 조인을 선택했습니다.
댓글 수와 블랙리스트는 도메인도 다르고 1:N 관계라 다른 도메인에서 배치로 조회하여 결과값을 가져와 애플리케이션에서 조합했습니다.
글 실행계획 - 0.97ms
"-> Limit: 20 row(s) (cost=2333 rows=20) (actual time=0.907..0.97 rows=20 loops=1)
-> Nested loop left join (cost=2333 rows=4916) (actual time=0.906..0.968 rows=20 loops=1)
-> Index lookup on p1_0 using idx_post_notice_created (is_notice=0) (cost=612 rows=4916) (actual time=0.841..0.847 rows=20 loops=1)
-> Single-row index lookup on m1_0 using PRIMARY (member_id=p1_0.member_id) (cost=0.25 rows=1) (actual time=0.0058..0.00582 rows=1 loops=20)
-> Select #2 (subquery in projection; dependent)
-> Aggregate: count(pl1_0.post_like_id) (cost=6.37 rows=1) (actual time=0.00284..0.00287 rows=1 loops=20)
-> Covering index lookup on pl1_0 using FKj7iy0k7n3d0vkh8o7ibjna884 (post_id=p1_0.post_id) (cost=3.32 rows=30.5) (actual time=0.00258..0.00258 rows=0 loops=20)
"
멤버 ID를 PK로 조회하고 인덱스를 사용해 post를 조회했습니다. 조인을하고 바로 행을 20개로 줄였습니다.
서브쿼리는 20번의 루프를 돌았고 커버링인덱스를 사용해 0.0006초만에 조합했습니다.
블랙리스트 실행계획 - 0.1ms
-> Table scan on <temporary> (cost=2.92..4.18 rows=2) (actual time=0.112..0.112 rows=1 loops=1)
-> Temporary table with deduplication (cost=1.66..1.66 rows=2) (actual time=0.111..0.111 rows=1 loops=1)
-> Filter: ((mb1_0.request_member_id = 1) or (mb1_0.black_member_id = 1)) (cost=1.46 rows=2) (actual time=0.092..0.0921 rows=1 loops=1)
-> Deduplicate rows sorted by row ID (cost=1.46 rows=2) (actual time=0.09..0.09 rows=1 loops=1)
-> Index range scan on mb1_0 using idx_member_blacklist_request over (request_member_id = 1) (cost=0.36 rows=1) (actual time=0.0395..0.044 rows=1 loops=1)
-> Index range scan on mb1_0 using idx_member_blacklist_black over (black_member_id = 1) (cost=0.36 rows=1) (actual time=0.0371..0.0371 rows=0 loops=1)
인덱스 range 스캔이 발생했고 행이 하나만있어 아주 빨리 끝났습니다.
댓글 수 실행계획 - 0.2ms
-> Group aggregate: count(c1_0.comment_id) (cost=60.7 rows=199) (actual time=0.123..0.305 rows=20 loops=1)
-> Filter: (c1_0.post_id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)) (cost=40.8 rows=199) (actual time=0.103..0.286 rows=199 loops=1)
-> Covering index range scan on c1_0 using idx_comment_post_created over (post_id = 1) OR (post_id = 2) OR (18 more) (cost=40.8 rows=199) (actual time=0.1..0.266 rows=199 loops=1)커버링 인덱스로 댓글 200개를 스캔했고 바로 필터링하여 행을 20개로 줄였습니다.
카운트 실행계획 - 5ms
"-> Aggregate: count(distinct p1_0.post_id) (cost=985 rows=1) (actual time=4.91..4.91 rows=1 loops=1)
-> Covering index lookup on p1_0 using idx_post_notice_created (is_notice=0) (cost=494 rows=4916) (actual time=0.0904..2.83 rows=10060 loops=1)
"
행이 처음에 5천개 들어와서 시간 소모가 2ms정도 들었습니다. 그리고 집계가 동작하기 이전까지 약 2ms가 정지되었는데 예전 10만개의 행이 있을때는 정지시간이 120ms였습니다. 많이 개선되었지만 이부분도 조금 더 개선해야할듯합니다.
이렇게 쿼리를 분리시켜 애플리케이션에서 조합했고
애플리케이션에서의 조합은 일반적으로 DB보다 더 느리지만 페이징 ID는 개수가 많을 수가 없기에 전체적으로 성능이 상승했습니다.
결과
| 쿼리 | 첫 페이지 | 중간 페이지 | 마지막 페이지 |
| 최초 | 54ms | 78ms | 130ms |
| 개선 후 문제 발생 | 778ms | 770ms | 744ms |
| 최종 개선 후 | 23ms | 44ms | 66ms |
모든 속도가 증가하였습니다. 다만 눈에 띄는점은 뒷 페이지로 갈수록 성능이 하락하는 부분이있습니다.
이부분은 페이징 전용 인덱스 쿼리를 먼저 실행하거나 UI를 더보기 방식으로 바꿔 Cursor방식으로 바꾸는 것을 고안중입니다.
아직 제 프로젝트의 게시글은 1페이지도 채우지 못하여 더 우선순위가 높은 다른 개선사항들을 개선하려합니다.