공간 인덱스로 조회속도 32배 개선하기(요즘 카페 지도 기능 개발)

우리 서비스에 지도 기능을 추가하기로 하였다.
계획은 다음과 같다.

회색선이 교차되는 부분이 지도의 중심점이다.

지도의 중심점을 기준으로 원을 그리고 원 안에 포함되는 카페들을 핀으로 보여주기로 하였다.

 

현재 우리서비스에서 사용하는 MySql에는 공간에 대한 정보를 담을 수 있는 공간 데이터와
이 공간데이터를 효율적으로 이용할 수 있는 공간 함수,
그리고 검색의 성능을 올릴 수 있는 공간 인덱스가 존재 한다.

 

이것들을 이용해보려 한다.

1. MySql 공간 데이터 다루기

MySql에서 제공하는 공간데이터의 종류는 위와 같다.

 

단일 타입으로는 Point, LineString, Polygon 이 세가지가 있고,
나머지들은 이 세가지 타입의 조합이다.

 

현재 개발하려는 기능은 각 카페마다 좌표를 갖고 있어야 하기에, 필요한 데이터타입은 Point 이다.

Point를 이용해서 각 카페의 공간데이터를 담을 새로운 테이블을 만들어 주었다.

 

CREATE TABLE cafe_coordinates  
(  
    id         BIGINT NOT NULL AUTO_INCREMENT,  
    coordinate POINT  NOT NULL,
    cafe_id    BIGINT NOT NULL,  
    PRIMARY KEY (id),  
    FOREIGN KEY (cafe_id) REFERENCES cafe (id)  
);

CREATE SPATIAL INDEX idx_coordinates ON cafe_coordinates (coordinate);

 

테이블을 만들고, coordinate column에 인덱스를 걸어주었다.
공간 데이터에 인덱스를 걸 때는 공간 인덱스(Spatial Index)를 사용한다.

1-1. R Tree

인덱스가 B Tree로 관리 되듯이, 공간 인덱스는 R Tree라는 자료구조를 통해 관리된다.

 

RTree를 이해하기 위해선 MBR을 알아야 한다.
별건 아니고, 사각형을 만드는 방법이다.

 

MBR은 Minimum Bounding Rectangle 의 약어로 최소 경계 사각형이란 뜻이다.

어떠한 꼭지점(?)도 벗어나지 않는 가장 작은 사각형 정도라고 설명하면 되려나
뭔가 말로 설명하기 어렵다..

그림으로 보면 단박에 이해된다.

각각 Point, LineString, Polygon 으로 생각했을때 이 세가지 공간데이터의 MBR은 다음과 같다.

 

무튼 Rtree는 이 MBR들을 만들고 이 MBR 들을 노드로 관리하는 트리이다.

예를 들어 하나의 테이블에 여러가지 공간데이터가 담겨있고 그걸 지도에 찍어보니 다음그림과 같다고 가정해보자.

 

지도를 걷어내고 각각의 데이터들의 MBR을 그려보면 다음과 같다.

이렇게 만들어진 MBR들을 또 묶어서 조금 더 큰 MBR을 만들고, 또 묶어서 만들고..
하다보면 최종적으로 만들어지는 그림은 아래와 같다.

그리고 이걸 트리형태로 표현하면 아래와 같다.

만약 H라는 공간데이터를 조회 한다고 하면,
루트에서 부터 어떤 노드에 포함되어있는지 확인 후, 포함하고있는 노드를 따라 하향하여 검색하는 방식이다.

 

이제 공간데이터랑 좀 친해진 거 같으니.. 마저 기능을 구현해보자.

2. 쿼리 실행계획

인텔리J를 키기전에, 쿼리 실행계획을 통해 걸어둔 인덱스가 잘 타지는지 확인을 해보자.

내가 작성한 최초의 쿼리는 다음과 같다.

 

SELECT c.id, c.name, co.coordinates
FROM cafe_coordinate AS co
INNER JOIN cafe AS c
ON co.cafe_id = c.id
WHERE ST_DISTANCE_SPHERE(ST_PointFromText('POINT({LATITUDE} {LONGTIDUE})', 4326), co.coordinate) <= {RADIUS};

MySql에서는 공간 함수를 제공해 주는데 몇개의 공간함수를 써서 Where절을 만들었다.
WHERE 절을 확인해보면 생소한 친구들이 보이는데,
하나씩 알아보자.

 

공간 함수 관련 공식문서

2-1. ST_PointFromText

공간데이터는 실제로 DB에 저장될때 저런 값으로 저장이 되는데,
이걸 당연히 직접입력하는게 아니라,
함수를 이용해 값을 생성한다.

 

생성할 공간 데이터를 담고 있는 텍스트, 즉 WKT와 SRID 값을 이용하여 생성해준다.

WKT는 간단하게 말하면 지리적인 개체들의 위치와 형태를 설명하는 문자열이다.
MySql에서 다루는 모든 공간데이터를 WKT 형식으로 표현할 수 있다.

 

• Point: "POINT (x y)"
• LineString: "LINESTRING (x1 y1, x2 y2, ...)"
• Polygon: "POLYGON ((x1 y1, x2 y2, ..., xn yn))"
• MultiPoint: "MULTIPOINT ((x1 y1), (x2 y2), ...)"
• MultiLineString: "MULTILINESTRING ((x11 y11, x12 y12, ...), (x21 y21, x22 y22,...), ...)"
• MultiPolygon: "MULTIPOLYGON (((x11 y11, x12 y12,...)), ((x21y21,x22y22,...)),...)"

 

만약 컬럼의 데이터타입이 GEOM(모든 타입의 공간데이터 입력이 가능하다.)이라면
ST_Point~ 대신 ST_GeomFrom을 사용하면 된다.

 

SRID는 Spatial Reference System Identifier의 약자이다.

음 쉽게말해 좌표 시스템이다.

가장 흔하게 사용되는 값이 4326(WGS84) 이며,

이는 우리가 흔히 아는 위도 경도를 이용한 좌표 시스템이다.

다른 값들에 대해선 굳이 알아보지 않았다.

2-2. ST_DISTANCE_SPHERE

네이밍에서도 느껴지듯, 두 공간데이터를 인자로 받아 두 데이터 사이의 거리를 계산해주는 함수이다.

 

이 두가지 함수를 이용해,
입력받은 위도 경도를 통해 포인트를 만들어주고, 내 DB에 저장된 카페의 데이터와 거리를 계산하여,
그 값이 RADIUS 보다 작거나 같은 row를 반환한다.

 

위 쿼리의 실행계획을 확인해보자.

type이 all 로 되어있다...?

index가 안걸렸나 싶어서 호다닥 확인을 해봤는데,

인덱스는 잘 걸려있다.. 뭐지??

3. 인덱스 안타는 이유(들)

음.
결론부터 말하면 상당히 여러 문제점들이 있었다.
하나씩 알아보자.

3-1. SRID

위에서 간략하게 설명한 SRID라는 값이 있는데, 이 친구를 모두 일치시켜줘야 한다.

CREATE TABLE cafe_coordinates  
(  
    id         BIGINT NOT NULL AUTO_INCREMENT,  
    coordinate POINT  NOT NULL,
    cafe_id    BIGINT NOT NULL,  
    PRIMARY KEY (id),  
    FOREIGN KEY (cafe_id) REFERENCES cafe (id)  
);

1. 테이블의 컬럼에 SRID 값
아까 테이블을 생성했던 쿼리이다.
테이블을 생성할 때 공간데이터가 들어가는 column에 SRID 값을 적용 시킬 수 있다.

 

그런데 난 안했었다.. ㅎ
이렇게 따로 값을 설정해주지 않으면 default로 0으로 들어간다.

 

ALTER문을 이용해 수정할 수 있으니, 수정해주자.

ALTER TABLE cafe_coordinates MODIFY COLUMN coordinate POINT NOT NULL SRID 4326;

물론 테이블을 만들 때부터 값을 설정해줄 수도 있다.

CREATE TABLE cafe_coordinates  
(  
    id         BIGINT NOT NULL AUTO_INCREMENT,  
    coordinate POINT  NOT NULL SRID 4326,
    cafe_id    BIGINT NOT NULL,  
    PRIMARY KEY (id),  
    FOREIGN KEY (cafe_id) REFERENCES cafe (id)  
);

2. 데이터의 SRID 값

그리고 데이터를 삽입할 때 역시 이 SRID 값을 이용해줘야 한다.

INSERT INTO cafe_coordinates (coordinate, cafe_id) VALUES (ST_PointFromText('POINT(10 20)', 4326), 1);

만약에 데이터를 입력할 때 SRID를 입력을 안했다면 마찬가지로 default 값이 0으로 들어가는데,
이거는 MySql에서는 수정할 방법이 없다.
데이터 다 날리고 다시 입력해줘야 한다.
그러니 잘 확인하자..

 

3. 조회할 때의 SRID 값

이건 위의 인덱스 안타던 쿼리의 WHERE 절에 사용했다.

이 세가지 경우에 사용하는 모든 SRID값이 일치해야 인덱스를 탄다.
(관련 공식 문서)

 

나같은 경우는 1번, 테이블의 컬럼에 SRID 값을 설정해주지 않았었기에, default 값(0)이 들어가 인덱스를 안탄 듯 하다.

컬럼은 수정해주고, 쿼리 실행계획을 확인해 봤으나...
마찬가지로 인덱스를 안탄다..

3-2. ST_DISTANCE_SPHERE는 인덱스를 사용안하는 함수

다시 구글링을 해보니.. 쿼리 자체가 문제였다.
말그대로 ST_DISTANCE~(이하 디스턴스함수) 이 함수는 인덱스를 사용하지 않는다.

디스턴스함수는 각각의 값의 공간데이터를 가져와 값을 비교하는 함수기에,

인덱스를 사용하지 않고, 풀스캔을 돌린다.

 

변경한 쿼리는 다음과 같다.

SELECT c.id, c.name, co.coordinate
FROM cafe_coordinates AS co
INNER JOIN cafe AS c
ON co.cafe_id = c.id
WHERE ST_CONTAINS((ST_Buffer(ST_PointFromText('POINT(37.5665 126.9780)',4326), 100)),co.coordinate);

디스턴스 함수 대신 contains 와 buffer를 사용했다.

 

buffer는 공간데이터와, radius를 받아 공간데이터의 중심으로 부터 radius를 반지름으로 갖는 가상의 원을 그려주는 함수이다.

 

contains는 첫번째 공간데이터에 두번째 공간 데이터가 포함되어있는지 확인하는 함수이다.

 

이렇게 변경한 쿼리의 실행계획을 확인해보면,

type이 range로 아주 이쁘게 인덱스를 타는 것을 확인 할 수 있다.

👆 인덱스 O

👇 인덱스 X

인덱스 유무에 따라 약 32배 정도의 속도차이를 보여준다.
아 인덱스 너무 맛도리다...

4. 코드에 적용할 때 주의사항

전체 코드를 보여주기 보단, 몇가지 주의해야 될 점들에 대해 작성해보려한다.

4-1. build.gradle

implementation group: 'org.hibernate', name: 'hibernate-spatial', version: '6.2.5.Final'

hibernate5 부터 공식적으로 공간데이터를 지원한다.
사용하기 위해선 위의 의존성을 추가해줘야 한다.

version 은 현재 프로젝트에 적용된 hibernate 버전과 동일하게 입력해주어야 한다.

4-2. Type

맨 밑의 coordinate 의 타입이 Point인데,
org.locationtech.jts.geom.Point 를 import 해와야 정상적으로 사용할 수 있다.

(다른 데이터 타입도 마찬가지로 jts.geom 에서 가져와야한다.)

4-3 new Coordinate()

public static Point generateWithCoordinate(final double latitude, final double longitude) {  
    final Point point = GEOMETRY_FACTORY.createPoint(new Coordinate(longitude, latitude));  
    point.setSRID(SRID);  
    return point;  
}

 

Point를 생성할 때 사용하는 메서드인데,
저기저저 new Coordinate() 가 아주 악마같은 녀석이다.

 

인자로 두개의 값을 받는데 파라미터 명이 x, y 이다.

당연히 위도, 경도 순인줄 알고 값을 집어 넣었는데, 알고보니 경도 위도 순이다...

 

다시 생각해보니 경도가 x, 위도가 y가 맞긴하다..

그.. SRID 값에 따라 x, y 가 의미하는게 달려저서 x, y로 네이밍을 한 듯 하다.
고작 이거때문에 3시간을 날렸다....

 

저 세가지 정도만 주의하고 나머지 부분은 문서를 확인하며 작업하면, 필요한 기능을 개발하는 데는 문제가 없을 것이다.

 

완전새로운 개념을 학습하고 적용하느라 쬐끔 고생을 하긴 했는데,

하는내내 재밌게 해서, 기억에 오래 남을 듯 하다.

 

5. 비즈니스 요구사항 변경 및 구현(23.10.09 추가)

원래는 중심점으로 부터 원을 그려서 그 원안에 포함되는 카페를 반환하기로 했었는데, 현재화면에 보여지는 지도에 포함된 카페들을 반환하는 것으로 변경되었다.

비즈니스 요구사항이 변경된 김에 포스팅 내용을 추가하고, 어떤식으로 구현했는지 작성하려한다.

지도내에 포함 된 카페들을 반환하는 것이므로, 지도모양의 Polygon을 만들고 ST_Contains 함수를 사용하면 뚝-딱 할 듯 하다.

5-1. 지도모양 폴리곤 생성

클라이언트로 부터 네가지 값을 받는다..

latitude (지도 중심 위도)longitude (지도 중심 경도)latitudeDelta (지도 내 최대 위도 - latitude)longitude (지도 내 최대 경도 - longitude)

이 네가지 값이면 지도 내의 네 꼭짓점의 위도 경도를 구할 수 있다.

public static Polygon generatePolygon(final CafeLocationRequest cafeLocationRequest) {
    final double latitude = cafeLocationRequest.latitude();
    final double longitude = cafeLocationRequest.longitude();
    final double latitudeDelta = cafeLocationRequest.latitudeDelta();
    final double longitudeDelta = cafeLocationRequest.longitudeDelta();

    final double minLatitude = latitude - latitudeDelta;
    final double maxLatitude = latitude + latitudeDelta;
    final double minLongitude = longitude - longitudeDelta;
    final double maxLongitude = longitude + longitudeDelta;

    final Coordinate[] vertexes = new Coordinate[]{
            new Coordinate(maxLongitude, minLatitude),
            new Coordinate(maxLongitude, maxLatitude),
            new Coordinate(minLongitude, maxLatitude),
            new Coordinate(minLongitude, minLatitude),
            new Coordinate(maxLongitude, minLatitude)
    };

    final Polygon polygon = GEOMETRY_FACTORY.createPolygon(vertexes);
    polygon.setSRID(SRID);
    return polygon;
}

vertexes 를 보면, Coordinate 가 5개이다. Polygon 을 생성할 때는 마지막 좌표는 꼭 첫번째 좌표여야한다. 안그러면 예외가 발생한다.

첫번째 좌표로 끝나야 모든 선이 닫힌 Polygon 형태가 나온다.

Point 와 마찬가지로 SRID 값을 빼먹지 말자..!

5-2 . JPQL

hibernate 5 이후 공간데이터를 공식적으로 지원하기에 JPQL로 공간함수를 이용할 수 있다. nativeQuery 안써도 된다!

hibernate 공식문서에 표가 하나 나와있는데, 이게 DB 별 지원하는 공간함수를 표기한 내용인데, 이게 MySql 5.6 이전 버전에 대해 표기가 되어있다. MySql5.6 이상의 버전을 사용한다면, 앤간한 공간함수를 모두 사용할 수 있다. MySql 공간함수 공식문서랑 비교해가며 사용하자.

무튼 이제 JPQL로 카페 데이터 가져오면 된다.

@Query("""
        SELECT
	        co.cafe.id AS id,
	        co.cafe.name AS name,
			co.cafe.address AS address,
			ST_X(co.coordinate) AS latitude,
			ST_Y(co.coordinate) AS longitude
        FROM CafeCoordinate co
        WHERE ST_CONTAINS(:area, co.coordinate)
        """)
List<CafePinDto> findCafePinsFromCoordinate(@Param("area") final Polygon area);

진짜 너무 간단하다. 위 코드에선 Projection mapping을 이용했는데, 당연히 entity를 바로 반환 받을 수도 있다.

 

음 이제 더는 수정할 일이 없으면 좋겠다..ㅎ