쿼리 성능 개선 (2) ‐ Index 설정 - YJGwon/connectruck GitHub Wiki
dummy data: 각각 다른 username, phone을 가진 row 100만개
username을 통한 조회는 로그인 할 때 마다 실행된다. 쓰기(회원 가입)와 비교할 수 없을 정도로 높은 빈도수를 가진다. 따라서 index를 추가해주었다.
설정 전) index가 없어 풀 테이블 스캔 (0.76 sec)
설정 후) index 사용해 1개의 row만 조회 (0.00 sec)
username을 통한 조회는 회원 가입 시 중복 체크를 위해 실행된다. 휴대폰 중복 검사에 1초가 넘게 소요되는 서비스라면 회원 가입의 의지가 뚝 떨어질 것이다 😱
설정 전) index가 없어 풀 테이블 스캔 (1.52 sec)
설정 후) index 사용해 1개의 row만 조회 (0.00 sec)
dummy data: truck_id 1인 row 1개, truck_id 25인 row 100만개
push_subscription에 대해 가장 빈번한 조회는 주문 생성 시 알림 발송을 위해 실행되는 findByTruckId이다. 그러나 findByTokenAndTruckId도 무시할 수는 없다. 알림 구독 및 해제 시에도 실행되고 주문 알림 발송 후 유효하지 않은 token을 삭제할 때도 실행된다.
findByTokenAndTruckId의 성능 개선만 생각하면 cardinality가 더 높은 token을 첫 column으로 사용하는 것이 좋을 것이다. 그러나 훨씬 빈번히 실행되는 findbyTruckId는 해당 index를 활용하지 못한다. findByTruckId는 FK를 갖고 있어 이미 index를 사용하지만, truck_id, token 두 컬럼을 사용한 index는 PushSubscription 객체를 조회할 때 covering index로 사용될 수 있어 FK로만 조회하는 것 보다 빠르다. 따라서 truck_id, token 순으로 indexing해 findbyTruckId에서도 covering index의 성능 이점을 누릴 수 있게 설정했다.
findByTokenAndTruckId의 성능을 생각해봐도 반대 순서로 indexing 하는 경우에 비교해 차이가 크지 않을 것이라 예상한다. 같은 푸드트럭에 대해 동시에 알림을 구독하는 기기가 엄청 많아질 경우는 거의 없을 것이기 때문이다.
설정 전) truck_id 사용, 같은 truck_id 가진 row 수 만큼 scan (1번 트럭 0.00 sec, 25번 트럭 6.04 sec)
한 푸드트럭을 구독하는 token이 많을 수록 성능이 급격히 저하된다.
설정 후) index 사용해 1개의 row만 조회, covering index (25번 트럭 0.00 sec)
dummy data: truck_id 25인 row 각 주문 상태(5가지)별 20만개(총 100만개), createdAt은 insert 될 때의 시간 사용
findByTruckIdAndStatus (order by createdAt)를 기준으로 설정했다. index의 정렬 순서와 조회 시 정렬 순서는 연관이 없다고 생각할 수도 있다. B+Tree에서는 leaf node끼리 양방향으로 연결되어 있으니 말이다. 그러나 node 내의 index record끼리는 단방향으로 연결되어 있어 순방향 scan이 더 빠르다. 따라서 조회 시 정렬하는 순서대로 index를 생성해주면 좋다.
indexing하는 순서는 equal 조건으로 조회하는 두 컬럼을 앞에 두고 둘 중 cardinality가 더 높은 truck_id를 맨 앞에 두었다.
설정 전) truck_id만 사용, filesort 사용 (5.68 sec)
filesort를 사용한다는 것은 후보 row들을 모두 읽은 뒤 다시 정렬했음을 의미한다. 이는 많은 부하를 일으킨다.
설정 후) index 사용, index condition pushdown 사용 (1.27 sec)
번외) index condition pushdown?
index condition pushdown은 index만으로 탐색 범위를 줄이기 힘들 경우(ex like %00) table에서 record를 실제로 읽기 전에 최대한 index 값으로 범위를 줄이는 최적화 기능이다. 내가 알기론 order by까지 index를 쓸 수 있으면 limit 범위에 해당하는 record만 읽는 걸로 아는데 index condition pushdown이 여기서 어떤 역할인지는 잘 모르겠다 😿 그래서 index condition pushdown을 끄고 다시 실행 계획을 확인했다.
using where가 나온다. 이는 storage engine에서 읽은 data를 MySQL engine에서 한 번 더 가공했다는 이야기다. index condition pushdown을 끈 상태에서는 where 조건에 해당하는 record를 모두 읽은 후 limit 범위 처리를 MySQL engine에서 처리하게 되는걸까? 그렇다기엔 실행 시간에 큰 차이가 없다. 뭐지…?
결국 index를 사용해서 order by + limit 조회 했을 때 index condition pushdown은 어떤 최적화를 해준다는건지는 미스테리로 남았다. 공식 문서에서 아래 글들도 찾아봤지만 여전히 모르겠다 😿
실행 시간이 많이 줄긴 했지만 여전히 많은 row를 scan하고 있다. 이는 offset paging 자체의 문제점이다. 현재 행사별 푸드트럭 목록 조회, 푸드트럭별 상태별 주문 목록 조회 기능에서 offset paging 조회가 실행되고 있다. 이에 대한 최적화는 다음편에서 계속…!