위치기반 서비스 데이터베이스 선택 [MySQL]

들어가기

현재 진행중인 프로젝트인 'AmorGakCo(아모르각코)'는 사용자들이 모각코 모임을 개설하고 사용자의 위치를 기반으로 모각코 모임을 조회하는 서비스입니다.

서비스 기능의 핵심에는 사용자 현 위치를 기반으로 주변 모각코 모임을 조회하는 일입니다.

어떤 데이터베이스를 선택하고 어떤 설계를 가져가야할 지 고민한 내용을 정리했습니다.

 

데이터 특성

모각코 모임 데이터 특징은 아래와 같이 가정했습니다.

• 데이터 생명주기는 짧게는 반나절 길게는 하루~이틀 예상
• 종료된 모각코는 위치조회 대상에서 제외(비영구적)
• 가장 활발히 모각코가 진행될 때 최대 5만건 정도로 가정 (한국 개발자 현황 약 80만명)

한 모임에 4-5명인 모각코 모임을 최대 5만건으로 예상하면 참여자는 20만명이 넘기 때문에 충분한 가정이라 생각합니다.

또한 모각코는 지속적으로 생성되겠지만 그만큼 지속적으로 삭제됩니다.

데이터 준비

소상공인시장진흥공단_상가(상권)정보_20240331

 

데이터는 서울지역 상권 위치정보를 파이썬을 이용해 가공하고 우선은 Local MySQL 서버에 저장했습니다.

 

import pandas as pd
seoul = pd.read_csv('./소상공인시장진흥공단_상가(상권)정보_서울_202403.csv');

seoul.상권업종중분류명.value_counts()

 

 

 

서울지역 상권중 가장 많은 비중을 차지하는 4만6천개 가량의 한식 관련 상권을 선택했습니다. (데이터 특성에서 예상한 규모와 가장 근접합니다.)

 

koreanFood = seoul[seoul['상권업종중분류명']== '한식']
koreanFood = koreanFood[['상호명','도로명주소','위도','경도']]
koreanFood = koreanFood.rename(columns={'상호명': 'name', '도로명주소': 'address', '위도': 'latitude','경도':'longitude'})

 

가장 중요한 가게이름, 주소, 위도, 경도 데이터만 뽑아서 저장하였습니다.

 

MySQL GIS

create table korean_food(
    id int auto_increment primary key,
    name varchar(30),
    address varchar(100),
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX sx_location(location)
);

 

위도 경도를 저장하기 위해서는 POINT 타입을 지정해 저장해야합니다. POINT외에도 선,다각형 등을 저장할 수는 있지만 서비스 요구사항에 존재하지 않아 테스트하지 않겠습니다.

 

SRID 4326의 의미는 GCS(지리좌표계)를 사용하기 위함입니다. 지리좌표계의 종류는 400가지가 넘지만 요즘 모바일 장치가 수신하는 대부분의 위도 경도 좌표가 WGS84 4326(SRID)을 사용하기 때문에 선택했습니다.

 

GCS를 사용하지 않는다면 PCS(투영좌표계)를 사용할 수는 있지만 MySQL서버에서 제공하는 거리계산 함수를 이용할 수 없습니다. PCS는 지구가 구체라는 사실을 고려하지 않는 X,Y 좌표평면이라 생각하면 됩니다.

 

마지막으로 MySQL의 공간인덱스를 생성했습니다.

 

우선 파이썬으로 저장한 데이터를 DataGrip에서 간단히 확인해보니 잘 저장돼 있는걸 볼 수 있습니다.

 

 

location 컬럼의 데이터 타입이 POINT 타입인데 보여지는 값이 조금 이상합니다.

 

데이터를 저장할 때는

ST_PointFromText('POINT(37.5162149 127.0195806)',4326)

 

위와 같이 저장하게 되는데, 사람이 알아 볼수 있는 위도,경도인 WKT(Well-Known Text format)으로 표현됩니다.

 

하지만 서버에 저장을 할땐 컴퓨터가 해석할 수 있는 바이너리 포맷인 WKB(Well-Known Binary format)으로 저장됩니다.

 

위치 조회 : ST_Distance_Sphere() : 원형 검색

3호선 신사역 위도 경도인 37.5162149 127.0195806를 기준으로 반경 1키로미터 내의 한식가게를 조회해 보겠습니다.

 

select name, address, ST_AsText(location) as location,
       ROUND(ST_Distance_Sphere(location,ST_PointFromText('POINT(37.5162149 127.0195806)',4326))) as distance_meters
from korean_food
where ST_Distance_Sphere(location,ST_PointFromText('POINT(37.5162149 127.0195806)',4326)) < 1000;

 

 

1KM 반경 검색 : 평균 150ms

 

1KM : 4만6천건 중 564건의 위치데이터를 조회하는데 10회 평균 150ms 소요됐습니다.

 

3KM 반경 검색 : 평균 173ms

 

3KM : 4만6천건 중3763건 데이터를 조회하는데 10회 평균 173ms 소요됐습니다.

10KM 반경 검색 : 평균 360ms

10KM로 범위를 늘리면 평균 360ms 소요됩니다.

 

ST_Distance_Sphere의 큰 단점

위에서 실행한 쿼리의 실행계획을 보면 어떤 단점이 있는지 알 수 있습니다.

 

 

앞서 공간인덱스를 걸어주었는데도 type:ALL로 테이블 전체를 스캔했습니다.

 

rows와 filtered 컬럼을 보더라도 모든 행을 읽고 필터링을 전혀 진행해지 못했다는 것을 알 수 있습니다.

 

MySQL의 공간인덱스(R-Tree)를 사용하기 위해서는 ST_Contains() 혹은 ST_Within() 함수를 이용해야합니다.

 

공간인덱스는 B-Tree인덱스와 인덱스 페이지 자체를 단계별로 나누어 인덱싱 한다는 점에서 개념적으론 같습니다.

 

다만 인덱스에 저장하는 값이 1차원 값이 아닌 2차원 도형인 점만 다릅니다.

 

Real MySQL8.0

 

공간 검색을 위해서는 점 P를 중심으로 사각형을 만들어 내야합니다.

 

아래와 같이 직사각형MBR 객체를 반환하는 함수를 작성해줍니다.(Real MySQL8.0 p.246)

 

DELIMITER ;;

CREATE DEFINER='root'@'localhost'
    FUNCTION getDistanceMBR(p_origin POINT, p_distanceKm DOUBLE) RETURNS POLYGON DETERMINISTIC
    SQL SECURITY INVOKER
BEGIN
    DECLARE v_originLat DOUBLE DEFAULT 0.0;
    DECLARE v_originLon DOUBLE DEFAULT 0.0;
    
    DECLARE v_deltaLon DOUBLE DEFAULT 0.0;
    DECLARE v_Lat1 DOUBLE DEFAULT 0.0;
    DECLARE v_Lon1 DOUBLE DEFAULT 0.0;
    DECLARE v_Lat2 DOUBLE DEFAULT 0.0;
    DECLARE v_Lon2 DOUBLE DEFAULT 0.0;

    SET v_originLat = ST_X(p_origin); /* = ST_Latitude(p_origin) for SRID=4326*/
    SET v_originLon = ST_Y(p_origin); /* = ST_Longitude(p_origin) for SRID=4326 */

    SET v_deltaLon = p_distanceKm / ABS(COS(RADIANS(v_originLat))*111.32);
    SET v_Lon1 = v_originLon - v_deltaLon;
    SET v_Lon2 = v_originLon + v_deltaLon;
    SET v_Lat1 = v_originLat - (p_distanceKm / 111.32); 
    SET v_Lat2 = v_originLat + (p_distanceKm / 111.32);

    SET @mbr = ST_AsText(ST_Envelope(ST_GeomFromText(CONCAT("LINESTRING(", v_Lat1, " ", v_Lon1,", ", v_Lat2, " ", v_Lon2,")"))));
    RETURN ST_PolygonFromText(@mbr, ST_SRID(p_origin));
END ;;

DELIMITER ;

 

지구는 둥글기 때문에 위도에 따라서 경도 1도의 변화량이 다릅니다.

 

위 코드의 v_deltaLon 파라미터 코드가 해당 변화를 고려한 계산식입니다. (상당히 복잡합니다.)

 

 

이렇게 3호선 신사역 주변 1KM 반경으로 사각형을 생성을 확인할 수 있습니다.

 

1KM 사각형은 원모양으로 위치를 검색하지 않기 때문에 MBR과 ST_Distance_Sphere를 and조건으로 같이 사용해 주어야합니다.

 

쿼리는 이미 MBR로 공간검색을 마친뒤에 원형으로 필터링 하기때문에 ST_Distance_Sphere 만 사용했을 때 보다 성능은 더 우수합니다.

 

ST_Within()+ST_Distance_Sphere() : 원형 검색

select name,address
from korean_food
where ST_Within(location,getDistanceMBR(ST_PointFromText('POINT(37.5162149 127.0195806)',4326),1))
and ST_Distance_Sphere(location,ST_PointFromText('POINT(37.5162149 127.0195806)',4326)) < 1000;

 

위에서와 똑같이 신사역을 기준으로 1KM 반경을 검색합니다.

 

1KM 반경 검색 : 평균 42ms [인덱스O]

 

인덱스를 타지 않을 때에 비해 3.5배 가량 속도가 개선됩니다.

 

검색 결과도 마찬가지로 564건으로 조회됩니다.

 

3KM 반경 검색 : 평균 70ms [인덱스O]

 

마찬가지로 2.4배 개선됐습니다.

 

10KM 반경 검색 : 평균 300ms [인덱스X]

 

평균 300ms로 ST_Distance_Sphere()만을 사용했을 때 보다 60ms 정도 빨라지긴 했습니다.

 

위 쿼리의 실행계획을 보면

 

type이 ALL로 테이블 풀스캔을 진행한 것을 볼 수 있습니다.

 

가져온 결과를 보니 2만7천건 가량으로 테이블 전체 크기에 절반 이상이 조회대상이였습니다. MySQL엔진이 판단하기에 range type 스캔이 더 비효율적이라 생각한 것 같습니다.

 

인덱스 힌트

 

Force Index Hint를 주어 range 스캔을 시켜봐도 그다지 성능은 개선되지 않았음을 확인했습니다.

 

explain select name,address
from korean_food force index (sx_location)
where ST_Within(location,getDistanceMBR(ST_PointFromText('POINT(37.5162149 127.0195806)',4326),10))
and ST_Distance_Sphere(location,ST_PointFromText('POINT(37.5162149 127.0195806)',4326)) < 10000;

 

 

평균 290ms로 미미하게 개선이 되긴했지만 의미가 크진 않습니다.

 

Hibernate-Spatial + JPQL

저희 서비스는 JPA(Hibernate) 기술을 이용하기 때문에 JPA와의 궁합도 고려했습니다.

 

Hibernate는 데이터베이스 공간 타입들을 추상화 시킨 Hibernate-Spatial를 제공합니다.

 

Hibernate ORM User Guide

 

위 문서에 각 데이터베이스 별로 지원되는 JPQL 함수들이 정리돼 있습니다.

 

MySQL의 경우 저희 서비스에서 압도적으로 많이 사용해야할 ST_dwithin() ST_distance() 함수를 지원하지 않습니다.

 

어쩔 수 없이 네이티브 쿼리를 이용해야 합니다.

 

Spatial Native Query

쿼리는 DataGrip에서 작성한 것과 동일합니다.

 

간단한 컨트롤러, 레포지토리를 만들어서 이번엔 포스트맨 환경에서 테스트해봤습니다.

 

GitHub - songhaechan/amorgakco-gis-performance-test: 아모르각코 GIS 반경 검색 성능 비교

 

 

ST_Within 의 4가지 테스트 케이스 중에서 10Km 는 인덱스 활용하지 못한 케이스입니다.

 

다음은 PostgreSQL GIS를 이용해 테스트를 진행해보겠습니다.