사내 백오피스 거래내역조회 페이지 응답속도 개선 후기

문제상황

사내 백오피스 애플리케이션에서 거래내역을 조회하는 페이지에 첫 진입할 때 5.48초가 걸리는 문제를 발견하였습니다

 

원인 분석

현재 거래내역 페이지는 offset 기반의 페이지네이션을 사용하고 있었고

리스트의 전체 행 수를 구하는 부분(이하 totalCount)에서 시간이 많이 소요되고 있음을 발견하였습니다.

페이지 첫 진입 시 데이터 조회 범위가 정해져있지 않았기 때문에 서비스 오픈부터 쌓여온 10만여건의 데이터를 전부 조회하고 있었습니다.

또한 실행계획을 조회해보니 거래 테이블에서 테이블 풀 스캔이 발생하고 있었습니다.

 

페이지 첫 진입 시 최근 한달 간의 거래내역만 조회하고 옵션에서 범위를 늘릴 수 있게 변경하고자 하였으나

운영팀에서는 조금 느리더라도 전체 범위 검색을 디폴트값으로 해달라는 요구사항이 있었기 때문에 다른 개선방법을 찾아보았습니다

 

시도 1. FORCE INDEX 사용 (실패)

PK가 있음에도 PK를 사용하지 않고 테이블 풀 스캔이 발생한 점에 의문이 들어 FORCE INDEX를 지정해봤지만 여전히 테이블 풀 스캔이 발생했습니다

그 이유는 아래에서 말씀드리겠습니다

 

시도 2. 검색 조건에 따라 동적으로 테이블 join (조금 아쉽)

거래내역 조회페이지에서는 거래정보 외에도 회원, 배송정보 등 다양한 정보를 포함하고 있어 JOIN이 많이 걸려있습니다

totalCount쪽에서도 목록조회와 동일하게 JOIN이 많았는데요

조건이 동일한 상태에서 JOIN을 할때 하지 않을때 모두 동일한 count값이 나오는 걸 확인하고 검색 옵션에 따라 동적으로 테이블을 JOIN하도록 코드를 수정하였습니다.

 

현재 회사에서는 ORM을 사용하지 않고 raw query를 string으로 담아서 PDO를 이용해 DB를 조회하는 방식을 사용하고 있는데요.

쿼리의 가독성을 높이기 위해 JOIN문과 WHERE절은 템플릿스트링을 이용하였습니다.

 

public function getCount($productName, $userName)
{
    $joinProduct = '';
    $joinUser = '';
    
    $whereProductName = '';
    $whereUserName = '';
    
    if(검색옵션) {
        $joinProduct = " JOIN product AS P ON O.product_id = P.id ";
        $whereProductName = " AND P.name = '{$productName}' ";
        // .... 생략
    }
    
    $sql = "
        SELECT COUNT(O.id) AS count
        FROM orders AS O
            {$joinProduct}
            {$joinUser}
        WHERE 1=1
            {$whereProductName}
            {$whereUserName}
    ";
    
    return DB::connection()->selectOne($sql);
}

(코드 예시. 재구성한 것으로 실제 코드와는 다릅니다. 기술스택 : PHP, Laravel, PDO)

 

어느정도 개선효과는 있었지만 여러 검색조건을 넣고 테스트를 진행하다 유독 느려지는 검색옵션을 발견하였습니다.

 

시도 3. OR절 제거 (성공)

응답속도가 느린 검색옵션을 확인해보니 2개의 JOIN 테이블을 OR절을 이용하여 전부 스캔하는 문제가 있었습니다.

이 테이블은 각각 5만건 이상의 데이터가 적재되어있는 상황이었고, 5만+5만 총 10만건의 데이터를 조회하면서 테이블 풀 스캔이 발생하였던 것입니다.

 

OR절을 사용하지 않고 검색옵션을 2가지로 분리하여 한번에 하나의 테이블만 조회하도록 개선하였습니다.

 

결과

5.48s -> 0.18s로 응답 속도를 개선할 수 있었습니다 (검색옵션 적용 케이스 포함)

 

추가로...

마지막 페이지로 갈 경우 6.17s 걸리는 문제를 발견하였습니다.

이는 offset 기반 페이지네이션이 가진 문제로,

6개의 데이터가 쌓인 테이블을 offset 4 limit 2으로 조회할 경우 4번 offset부터 시작하기 위해 0~3번 offset까지의 데이터를 다시 읽고 4번 offset부터 size만큼 반환하기 때문에 0~3번까지의 불필요한 데이터 조회가 발생한다고 합니다.

 

참고 : https://jaehoney.tistory.com/234

Real MySQL - Limit, Offset 절의 동작 원리! (+ No Offset 성능 비교)