[Key Word 개발기] 친구 검색 쿼리 개선기

최근 인덱스에 대해서 공부하다가 프로젝트를 생각해보니 검색 쿼리 속도 개선을 하면 좋을 거같아서 테스트하는 도중에 좋은 개선사항이 있어 적어본다.

---

쿼리 수정기

 

해당 프로젝트에서 친구관련 리스트를 가져올때 총 3가지의 경우가 존재한다.

 

1. 내가 친구한테 친구를 요청한 경우

2. 친구가 나에게 요청한 경우

3. 이미 친구인 경우

 

1,3번의 경우 밑에 코드를 이용한다.

  @Query("SELECT f.friend.memberId FROM Friend f JOIN f.friend friend JOIN f.memberRequest memberRequest "
      + "WHERE memberRequest.memberId = :memberRequestId AND f.status = :friendStatus ORDER BY f.friend.name")
  Page<Long> findFriendListByMemberId(@Param("memberRequestId") Long memberRequestId,
      @Param("friendStatus") FriendStatus friendStatus,
      Pageable pageable);

Hibernate: 
    select
        friend0_.member_id as col_0_0_ 
    from
        friend friend0_ 
    inner join
        member member1_ 
            on friend0_.member_id=member1_.member_id 
    inner join
        member member2_ 
            on friend0_.member_req_id=member2_.member_id 
    where
        member2_.member_id=? 
        and friend0_.status=? 
    order by
        member1_.name limit ?

 

현재 이코드와 쿼리는 불필요한 join을 발생시키고 있다.

그 이유는 첫번째

• where member2.member_id는 결국 friend.member_req_id이다. 그렇다면 friend.member_req_id로 바꾸면 된다
• 만약 그렇게 된다면 member2와의 join은 불필요하다

 

  @Query("SELECT f.friend.memberId FROM Friend f JOIN f.friend friend "
      + "WHERE f.memberRequest.memberId = :memberRequestId AND f.status = :friendStatus ORDER BY f.friend.name")
  Page<Long> findFriendListByMemberId(@Param("memberRequestId") Long memberRequestId,
      @Param("friendStatus") FriendStatus friendStatus,
      Pageable pageable);

 그래서 f.memberRequest에 대한 join을 없애고 where 절에 id값만 비교할 수 있게 수정하였다.

그랬더니 쿼리가 군더더기 없이 원하는데로 join을 한번만 진행하였다.

    select
        friend0_.member_id as col_0_0_ 
    from
        friend friend0_ 
    inner join
        member member1_ 
            on friend0_.member_id=member1_.member_id 
    where
        friend0_.member_req_id=? 
        and friend0_.status=? 
    order by
        member1_.name limit ?

 

 

 

2번의 경우

  @Query("SELECT f.memberRequest.memberId FROM Friend f JOIN f.friend friend JOIN f.memberRequest memberRequest "
      + "WHERE friend.memberId = :friendMemberId AND f.status = :friendStatus ORDER BY f.memberRequest.name")
  Page<Long> findFriendListByFriendId(@Param("friendMemberId") Long friendMemberId,
      @Param("friendStatus") FriendStatus friendStatus,
      Pageable pageable);

    select
        friend0_.member_req_id as col_0_0_ 
    from
        friend friend0_ 
    inner join
        member member1_ 
            on friend0_.member_id=member1_.member_id 
    inner join
        member member2_ 
            on friend0_.member_req_id=member2_.member_id 
    where
        member1_.member_id=? 
        and friend0_.status=? 
    order by
        member2_.name limit ?

 

결국 이 쿼리도  불필요한 join을 발생시키고 있다.

• where member1.member_id는 friend.member_id와 동일하다
• 따라서 member1과의 join은 불필요하다.

  @Query("SELECT f.memberRequest.memberId FROM Friend f JOIN f.memberRequest memberRequest "
      + "WHERE f.friend.memberId = :friendMemberId AND f.status = :friendStatus ORDER BY f.memberRequest.name")
  Page<Long> findFriendListByFriendId(@Param("friendMemberId") Long friendMemberId,
      @Param("friendStatus") FriendStatus friendStatus,
      Pageable pageable);

 

f.friend에 대한 join을 없애고 where절에는 f.friend.memberId로 값을 비교하게 수정하였다.

그랬더니 이번에도 join한번만 진행하게 수정되었다.

Hibernate: 
    select
        friend0_.member_req_id as col_0_0_ 
    from
        friend friend0_ 
    inner join
        member member1_ 
            on friend0_.member_req_id=member1_.member_id 
    where
        friend0_.member_id=? 
        and friend0_.status=? 
    order by
        member1_.name limit ?

 

 

 

쿼리 개선기

 

해당 쿼리 속도를 측정하기 위해 약 20만개의 데이터를 삽입하여 진행하였다.

 

 

일단 앞서 쿼리를 수정하기전 쿼리 속도는??

SELECT friend.member_id FROM Friend f JOIN member friend on f.member_id = friend.member_id
    JOIN member memberRequest on f.member_id = memberRequest.member_id
      WHERE f.member_req_id = 6 AND f.status = 'FRIEND_ACCEPTED' ORDER BY friend.name limit 180000 ,10;

 

첫째줄에 type이 All이다. 즉 아무 인덱스도 사용하고 있지 않다는 말이다.

여기서 앞서 쿼리를 수정한 걸로 쿼리 속도를 측정하면??

 

SELECT friend.member_id
FROM Friend f
         JOIN member friend ON f.member_id = friend.member_id
WHERE f.member_req_id = 6 AND f.status = 'FRIEND_ACCEPTED'
ORDER BY friend.name DESC limit 180000 ,10;

 

여기도 첫번째 type이 All이다. 아무 인덱스도 사용하고 있지않다.

다만 join두번이었던 것을 한번으로 줄였더니 약 35% 쿼리 속도가 개선되었다.

 

 

여기서 인덱스를 생성해서 쿼리 속도를 개선해보겠다.

 

첫번째로 Friend테이블과 관련된 칼럼 member_id, member_req_id, status 복합 인덱스를 생성해겠다

create index idx_test4 on friend (member_id,member_req_id, status);

SELECT friend.member_id
FROM Friend f
         JOIN member friend ON f.member_id = friend.member_id
WHERE f.member_req_id = 6 AND f.status = 'FRIEND_ACCEPTED'
ORDER BY friend.name DESC limit 180000 ,10;

 

약 230ms정도 나온다. 8%정도 개선된 것인데 획기적이지는 않다.

이유를 생각해보면 where절에서는 index를 사용하지만 orderBy절에서  filesort방식으로 임시 테이블을 사용해 정렬 작업을 수행하고 있다.

그럼 Order By에 있는 name절에 인덱스를 생성해볼까?

 

create index idx_test2  on member (name  DESC );

 

idx_test2를 사용한다는 것을 key칼럼과 Extra칼럼을 보면 알 수 있다.

그랬더니 230ms -> 197ms 15%개선되었다.

 

 

음 그럼 member에서 member_id랑 name Desc 복합 인덱스 만들어보는거 어때??

create index idx_test6  on member (member_id,name  DESC );

230ms로 생각보다 개선이 안되었다.

 

 

그래서 보니 rows를 보면 모든 데이터를 끌고온다.  idx_test6인덱스가 더 성능이 안좋았다.

 

 

---

결과적으로 join 불필요한거 줄이고, Friend 인덱스와 Member order 인덱스를 생성하였더니

388ms에서 197ms로 약 50%개선되었다.

 

아 테스트까지 완료했으니 이제 프로젝트 코드 수정해봐야징