이번 포스트에서 성능을 개선해볼 쿼리는 1:N관계에 대한 두개의 필드를 가지고 있기 때문에 이들을 모두 메모리에서 조립해주어야하는 부담이 있었고, 이 점이 서버의 과부하의 원인이 되는 것으로 추측되었기 때문에 기존의 100ms정도의 쿼리 성능을 조금 더 개선해보려 합니다.
기존 쿼리
기존의 쿼리는 store와 1:n관계에 있는 image와 keyword에 대한 정보를 불러와야했습니다. 쿼리의 결과로 중첩 리스트 구조를 만들 수 없었기 때문에 메인쿼리, count쿼리, 1:n관계에있는 entity를 불러오기 위한 IN쿼리 로 총 세번의 쿼리가 필요했습니다.
메인 쿼리
//메인 쿼리
select
store0_.store_id as col_0_0_,
store0_.name as col_1_0_,
store0_.address as col_2_0_,
store0_.avg_review_rating as col_3_0_,
store0_.like_count as col_4_0_
from
store store0_
inner join
category category1_
on store0_.category_id=category1_.category_id
left outer join
store_keyword storekeywo2_
on store0_.store_id=storekeywo2_.store_id
left outer join
keyword keyword3_
on storekeywo2_.keyword_id=keyword3_.keyword_id
where
store0_.status=?
and ?=?
and keyword3_.name=?
and ?=?
and (
store0_.name like ? escape '!'
)
order by
store0_.avg_review_rating desc limit ?
-> Limit: 10 row(s) (cost=548 rows=1.11) (actual time=0.0398..0.195 rows=10 loops=1)
-> Nested loop inner join (cost=548 rows=1.11) (actual time=0.0387..0.193 rows=10 loops=1)
-> Nested loop inner join (cost=274 rows=1.11) (actual time=0.0295..0.144 rows=10 loops=1)
-> Filter: ((store0_.`status` = 'APPROVED') and (store0_.`name` like '가게%') and (store0_.category_id is not null)) (cost=0.971 rows=1.11) (actual time=0.0242..0.134 rows=10 loops=1)
-> Index scan on store0_ using avg_review_rating (reverse) (cost=0.971 rows=10) (actual time=0.0208..0.126 rows=10 loops=1)
-> Single-row covering index lookup on category1_ using PRIMARY (category_id=store0_.category_id) (cost=0.25 rows=1) (actual time=588e-6..619e-6 rows=1 loops=10)
-> Covering index lookup on storekeywo2_ using store_keyword_idx_store_id_keyword_id (store_id=store0_.store_id, keyword_id='1') (cost=0.25 rows=1) (actual time=0.00397..0.00461 rows=1 loops=10)
sort 생략: 실행계획을분석해보면 sort하는 과정이 빠져있습니다. 이는 정렬에 쓰인 필드가 인덱스로 사용되어 불필요한 연산이 없어졌음을 의미합니다.
10개의 row반환: 인덱스를 사용하게 되면 Single block I/O로 디스크 블록을 읽는데, 이 과정 때문에 소량의 데이터를 읽을 때 인덱스 사용이 적합합니다. 현재 쿼리에선 인덱스를 사용하고 있고, 대량의 데이터를 읽지 않기 때문에 적합하게 사용하고 있습니다.
커버링 인덱스 사용: 커버링 인덱스를 사용하면 쿼리 실행에 필요한 모든 데이터를 인덱스 자체에서 제공할 수 있기 때문에 빠릅니다.
일반적인 인덱스 사용 시에는 인덱스를 검색하여 원하는 데이터의 위치를 찾고 나서 해당 위치에 있는 실제 데이터를 가져오기 위해 별도로 테이블을 조회하는 과정을 거칩니다
반면 커버링 인덱스를 사용하면 인덱스를 검색하고 인덱스 내에 이미 쿼리에 필요한 모든 데이터가 있기 때문에 별도의 테이블 조회 없이 바로 원하는 정보를 가져올 수 있습니다.
인덱스로 설정된 항목들의 제약조건을 반영하여 키 길이 줄이기: 인덱스로 설정된 항목들은 길이에 대한 제한을 가지고 있습니다. 그 제약조건을 디비단에도 반영하여 키의 길이를 줄이려고 합니다.
데이터베이스 인덱스는 주로 B-tree 구조로 구현됩니다. B-tree는 여러 레벨로 구성되며, 각 레벨은 여러 페이지(데이터를 읽고 쓰기 위한 기본 단위)로 나뉩니다. 인덱스의 길이가 길면, B-tree의 레벨 수가 증가할 수 있고, 그 결과로 특정 데이터에 액세스하기 위해 더 많은 페이지를 읽게 됩니다.
인덱스의 길이가 길다는 것은 인덱스의 키 값이 길다는 것을 의미합니다. 인덱스의 키 값이 길어지면, 각 페이지(또는 블록)에 저장할 수 있는 키의 개수가 줄어듭니다.
(페이지에는 실제 데이터와 인덱스 키 값이 들어있습니다)
B-tree는 페이지의 최대 용량에 도달하면 분할되어 새로운 페이지를 생성하게 됩니다. 인덱스 키의 길이가 길면, 페이지당 포함될 수 있는 키의 수가 줄어들기 때문에, 동일한 데이터 양에 대해서도 더 많은 페이지가 필요하게 됩니다
이렇게 페이지의 수가 늘어나면, B-tree의 깊이(레벨)도 자연스럽게 증가하게 됩니다.
Count 쿼리
//count쿼리
Hibernate:
select
store1_.store_id as col_0_0_
from
store_keyword storekeywo0_
inner join
store store1_
on storekeywo0_.store_id=store1_.store_id
inner join
keyword keyword2_
on storekeywo0_.keyword_id=keyword2_.keyword_id
where
store1_.status=?
and ?=?
and keyword2_.name=?
and ?=?
and (
store1_.name like ? escape '!'
)
-> Nested loop inner join (cost=1449 rows=1093) (actual time=0.132..29.5 rows=9700 loops=1)
-> Filter: ((store1_.`status` = 'APPROVED') and (store1_.`name` like '가게%')) (cost=1067 rows=1093) (actual time=0.0495..6.5 rows=9701 loops=1)
-> Covering index scan on store1_ using avg_review_rating (cost=1067 rows=9864) (actual time=0.00932..3.55 rows=9864 loops=1)
-> Covering index lookup on storekeywo0_ using store_keyword_idx_store_id_keyword_id (store_id=store1_.store_id, keyword_id='1') (cost=0.25 rows=1) (actual time=0.00205..0.00223 rows=1 loops=9701)
메인 쿼리보다도 cost가 많이 드는것을 확인할 수 있습니다. store_id에 인덱스가 걸려 있더라 store_id 값을 빠르게 찾는 데 유용하고, count 연산에 사용되는 유일한 값만을 카운팅하는 데에서는 인덱스의 효용성이 떨어지기 때문입니다.
많은 row를 loop하는 것이 필연적이기 때문에 쿼리에 손을 대기보단 다른 방법을 선택하였습니다.
검색 버튼 클릭유무를 백엔드 서버로 전달하여 그 유무에 따라 count쿼리의 실행 유무를 결정짓는 방법입니다. 다음과 같은 상황이 있다고 가정합니다.
데이터베이스에 총 95개의 게시물이 있습니다. 한 페이지에 10개의 게시물이 보여집니다. 사용자가 페이지 번호를 선택하여 페이지를 조회할 수 있습니다.
사용자가 처음 게시판을 열었을 때 (검색 버튼 클릭 포함):
대략적인 페이지 수를 보여주기 위해 일단 10개의 페이지 번호를 보여줍니다 (즉, 10 x 10 = 100개의 게시물이 있다고 가정)
사용자가 9번 페이지를 클릭:
9번 페이지는 실제로 데이터베이스에 있는 게시물을 표시할 수 있습니다. 그러므로 정상적으로 9번 페이지의 게시물들이 보여집니다.
이때 전체 row의 개수를 세는 count쿼리는 직접 쿼리를 날리는 것이 아닌 10x10=100개라고 가정하여 응답으로 보냅니다
사용자가 10번 페이지를 클릭:
실제로는 95개의 게시물만 있기 때문에 10번 페이지는 없습니다. 이때 총 게시물 수(95)와 요청된 페이지 번호(10)를 확인하여 10번 페이지 클릭 시 마지막 페이지를 보여줍니다.
1:N 쿼리
//1:n관계 쿼리
Hibernate:
select
store0_.store_id as col_0_0_,
storeimage1_.url as col_1_0_,
keyword3_.name as col_2_0_
from
store store0_
left outer join
image storeimage1_
on store0_.store_id=storeimage1_.store_id
left outer join
store_keyword storekeywo2_
on store0_.store_id=storekeywo2_.store_id
inner join
keyword keyword3_
on storekeywo2_.keyword_id=keyword3_.keyword_id
where
store0_.store_id in (
? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?
)
-> Nested loop left join (cost=31.6 rows=65.3) (actual time=0.0412..0.223 rows=29 loops=1)
-> Nested loop inner join (cost=10.2 rows=11) (actual time=0.033..0.124 rows=23 loops=1)
-> Nested loop inner join (cost=6.38 rows=11) (actual time=0.0182..0.0465 rows=23 loops=1)
-> Filter: (store0_.store_id in (10,11,12,13,14,15,16,17,18,19,20)) (cost=2.53 rows=11) (actual time=0.0128..0.0217 rows=9 loops=1)
-> Covering index range scan on store0_ using PRIMARY over (store_id = 10) OR (store_id = 11) OR (9 more) (cost=2.53 rows=11) (actual time=0.011..0.0187 rows=9 loops=1)
-> Filter: (storekeywo2_.keyword_id is not null) (cost=0.259 rows=1) (actual time=0.00182..0.0024 rows=2.56 loops=9)
-> Covering index lookup on storekeywo2_ using store_keyword_idx_store_id_keyword_id (store_id=store0_.store_id) (cost=0.259 rows=1) (actual time=0.00164..0.00206 rows=2.56 loops=9)
-> Single-row index lookup on keyword3_ using PRIMARY (keyword_id=storekeywo2_.keyword_id) (cost=0.259 rows=1) (actual time=0.00314..0.00317 rows=1 loops=23)
-> Index lookup on storeimage1_ using FK10wms0kbcv5yy3cmrjdbdretf (store_id=store0_.store_id) (cost=1.4 rows=5.94) (actual time=0.00331..0.00406 rows=1.26 loops=23)
in 쿼리를 통해 n+1문제를 예방하고 한번에 데이터를 가져오고 있습니다
storeKeyword 테이블에 store_id와 keyword_id의 인덱스가 설정됨으로써 둘을 기반으로 하는 조인이 효과적으로 수행 될 수 있습니다.
결론
커버링 인덱스 및 count쿼리 개선 등으로 인해 130 ms -> 30ms 로 4배 가량 응답속도가 개선되었습니다. (useSearchBtn값이 false인 경우엔 60ms정도)
속도에 대해선 개선이 되었고, 서버의 자원을 많이 사용할것으로 예상되는 count쿼리의 경우도 특정 순간에만 실행하기 때문에 기존의 서버의 높은 getConnection()지연시간의 문제를 해결할 수 있다고 생각하였습니다.
