refactor: Blueprint Search 쿼리 최적화

[mete0rfish]

🔥 구현 기능

1. N+1

현재 Blueprint Searching 관련 JPQL에서 N+1 문제가 발생하고 있다. 이는 Blueprint의 oneToMany 관계인 orderBlueprints, cartBlueprints으로 인해 발생한다. 현재 지연로딩이 설정되어 있지만, 연관 관계의 두 테이블을 추가로 조회하는 쿼리가 발생한다. 따라서, 간단하게 Fetch Join을 이용하여 모든 쿼리를 한 번에 수행하도록 변경하려고 한다.

2. 읽기 전용 트랜잭션

Search 서비스는 읽기 전용이기 때문에, 읽기 전용 트랜잭션을 통해 스냅샷 생성을 막아 성능 향상이 가능할 것으로 보인다. 따라서, 읽기 전용 트랜잭션 적용을 통해 성능 향상을 목표로 한다.

3. 인덱싱

Search의 경우 이름과 작가, 카테고리에 의해 검색된다. 따라서 해당 칼럼들에 대한 인덱싱을 통한 성능향상을 기대해볼 수 있을 것 같다.

🚧 작업목록

• N+1 문제 해결
• 읽기 전용 트랜잭션 도입
• 인덱싱 도입

⏰ 추정 시간

3시간

🔗 관련 링크

N+1 문제
[JPA] DB Index 개념과 JPA에서 Index 설정하기
JPA 페이징 Performance 향상 방법

[mete0rfish]

인덱스 적용

findByNameOrCreator는 아래 쿼리를 통해 OR 연산을 진행한다. OR 연산의 경우 모든 행을 확인해야 하기 때문에 쿼리 수행 시간이 매우 느릴 것으로 판단하였다.

SELECT b
FROM Blueprint b
WHERE (b.blueprintName LIKE %:keyword% OR b.creatorName LIKE %:keyword%)
AND b.inspectionStatus = :status
AND b.isDeleted = false

그래서 인덱스 적용 여부에 따른 쿼리 실행 속도를 EXPLAIN ANALYZE를 통해 분석해보았다.

0. 인덱스 도입 전

-> Filter: ((b.is_deleted = false) and (b.inspection_status = 'PASSED') and ((b.blueprint_name like '%골프장%') or (b.creator_name like '%골프장%')))  (cost=10367 rows=5124) (actual time=0.164..134 rows=10005 loops=1)
    -> Table scan on b  (cost=10367 rows=97662) (actual time=0.158..106 rows=100009 loops=1)

1. blueprint_name, creator_name 각각에 인덱싱

-> Filter: ((b.is_deleted = false) and (b.inspection_status = 'PASSED') and ((b.blueprint_name like '%골프장%') or (b.creator_name like '%골프장%')))  (cost=10363 rows=5130) (actual time=2.76..128 rows=10005 loops=1)
    -> Table scan on b  (cost=10363 rows=97782) (actual time=2.75..101 rows=100009 loops=1)

2. blueprint_name, creator_name, inspection_status, is_delete 각각에 인덱싱

-> Filter: ((b.is_deleted = false) and (b.inspection_status = 'PASSED') and ((b.blueprint_name like '%골프장%') or (b.creator_name like '%골프장%')))  (cost=4685 rows=5124) (actual time=0.161..271 rows=10005 loops=1)
    -> Intersect rows sorted by row ID  (cost=4685 rows=24416) (actual time=0.153..245 rows=100007 loops=1)
        -> Index range scan on b using blueprint_ix_is_deleted over (is_deleted = 0)  (cost=280e-6..13.7 rows=48831) (actual time=0.076..28.1 rows=100009 loops=1)
        -> Index range scan on b using blueprint_ix_inspection_status over (inspection_status = 'PASSED')  (cost=310e-6..15.1 rows=48831) (actual time=0.0279..27.5 rows=100007 loops=1)

246로 더 떨어진 것을 확인할 수 있다. inspection_Status와 is_delete의 경우, 인덱스가 아닌 커버링 인덱스를 통해 디스크 IO를 줄일 수 있을 것이라 판단하였다.

3. {is_deleted, inspection_status, blueprint_name}, {is_deleted, inspection_status, creator_name}

-> Filter: ((b.blueprint_name like '%골프장%') or (b.creator_name like '%골프장%'))  (cost=2734 rows=10264) (actual time=73..286 rows=10005 loops=1)
    -> Index lookup on b using blueprint_ix_blueprint_name (is_deleted=false, inspection_status='PASSED'), with index condition: (b.inspection_status = 'PASSED')  (cost=2734 rows=48911) (actual time=0.382..273 rows=100007 loops=1)

옵티마이저가 is_deleted, inspection_status, blueprint_name 순서대로 조건을 필터링하기 때문에 순서를 정했다.
오히려 is_deleted와 inspection_status를 커버링 인덱스 하니 성능이 더 떨어졌다.

쿼리 분리를 통한 최적화

OR 연산 대신 BlueprintName과 CreatorName에 대한 각각의 조회 쿼리를 통해 데이터를 머지하는 방식의 효율도 테스트를 진행했다.

-> Filter: ((b.is_deleted = false) and (b.inspection_status = 'PASSED') and ((b.blueprint_name like '%골프장%') or (b.creator_name like '%골프장%')))  (cost=10339 rows=5134) (actual time=0.826..126 rows=10005 loops=1)
    -> Table scan on b  (cost=10339 rows=97855) (actual time=0.818..98.2 rows=100009 loops=1)

결론

사실상 인덱스와 쿼리 분리의 의미가 없다.