조회 방식 개선 및 인덱스 수정

데이터 수집 및 가게 타입 지정

서울시 공공 데이터를 통해 가게 정보를 수집하고, 각 가게에 타입(store_type)을 지정하여 저장했습니다. 타입의 종류는 다음과 같습니다.

 

기존 조회 방식과 문제점

기존 조회 방식은 다음 두 가지 조건으로 이루어져 있습니다.

1. 햄버거, 피자와 같은 카테고리(category)로 조회
2. 타입(store_type)으로 조회

하지만, 이 방식에는 문제가 있습니다. 아래 데이터를 보시면, 가게 타입이 CAFE가 아니면서도 카테고리에는 카페를 포함하고 있는 가게들이 있기 때문입니다.

 

노래방과 당구장도 마찬가지였습니다.

 

“그러면 모든 가게를 카테고리로만 조회하면 되지 않나?”라고 생각하실 수도 있지만, 현재 데이터베이스에는 아래와 같이, 카테고리 정보가 비어 있는 가게들이 존재합니다. 이는 데이터 제공처에서 카테고리 정보가 제대로 관리되지 않아서 발생한 문제입니다. 서울시 공공데이터에는 가게의 상세 카테고리가 기재되어 있지 않으며, 카카오맵 데이터에는 카테고리 정보가 포함되어 있긴 하지만, 해당 정보가 제대로 관리되지 않아, 비어 있거나 잘못된 카테고리를 가진 가게들이 다수 존재합니다.

데이터베이스 상태

서울시 공공데이터

 

조회 방식 개선

앞서 언급한 문제를 해결하여 정확한 조회 결과를 제공하기 위해 조회 조건을 개선했습니다. 기존에는 store_type = 'CAFE' 조건만 사용했지만, category LIKE '%카페%' OR store_type = 'CAFE' 조건으로 변경함으로써 의도한 대로 모든 행을 정확하게 조회할 수 있게 되었습니다. => 이후에 데이터를 모두 수작업으로 수정하여 카테고리 조건만으로 조회할 수 있도록 변경했습니다. (불필요한 인덱스도 삭제했습니다.) 

// 기존 방식
WHERE store_type = 'CAFE'

// 개선한 방식
WHERE (category LIKE '%카페%' OR store_type = 'CAFE')

 

 

최종 쿼리문

이전 방식에서는 (카카오 평점, 카테고리) 또는 (카카오 평점, 가게 타입)의 조합으로만 조건을 지정했지만, 변경 후에는 (카카오 평점, 카테고리, 가게 타입)의 조합을 사용하도록 했습니다.

SELECT
    store.id,
    store.name,
    ST_Y(store.location) AS latitude,
    ST_X(store.location) AS longitude,
    store.kakao_average_grade,
    store.kakao_average_grade,
    store.nolgoat_average_grade
FROM store
WHERE (category LIKE '%카페%' OR store_type = 'CAFE')
AND (
    6371 * ACOS(
        COS(RADIANS(37.492219089015705)) 
        * COS(RADIANS(ST_Y(store.location))) 
        * COS(RADIANS(ST_X(store.location)) - RADIANS(127.03116426578977)) 
        + SIN(RADIANS(37.492219089015705)) 
        * SIN(RADIANS(ST_Y(store.location)))
        )
    ) <= 1.0
AND store.kakao_average_grade >= 5.0 
ORDER BY store.kakao_average_grade DESC;

 

성능 개선을 위한 인덱스 최적화

기존 인덱스(카카오 평점, 카테고리)를 통해서도 조회 성능에 개선이 있었습니다.

 

[Explain Analyze]

 

그러나 성능을 더욱 향상시키기 위해 새로운 멀티 컬럼 인덱스(카카오 평점, 카테고리, 가게 타입)를 생성했고, 이를 통해 조회 성능을 더욱 개선할 수 있었습니다.

 

[Explain Analyze]

 

새로 생성한 인덱스는 기존 인덱스에 store_type 컬럼을 추가한 형태이기 때문에, (카카오 평점, 카테고리) 조건에도 적용이 가능합니다. 이에 따라 기존 인덱스는 불필요해져 삭제하였습니다.

 

⚡결과

인덱스를 수정하여 요청 처리 속도를 109ms에서 63ms로 단축, 성능을 약 42.2% 개선했습니다.