[+/KM]MySQL에서 공간 데이터 최적화하기

📝배경

 

이번에 개인 프로젝트를 진행하면서 공공데이터 포탈의 의료 데이터를 사용하게 되었습니다.

그래서 의료 데이터 중 병원, 약국의 정보를 저장하여 그 데이터 중에서 좌표를 활용해서 주변 약국, 병원을 조회하는 서비스를 만들기로 하였습니다. 

 

데이터 저장에 성공하고 임의로 좌표 조회를 한 순간 매우 높은 응답 시간이 나와 최적화하기로 하였습니다. 

 

 

📝MySQL 공간 데이터베이스 

지금 사용하는 MySQL에서는 공간 데이터베이스를 제공합니다.

MySQL :: MySQL 8.0 Reference Manual :: 13.4.1 Spatial Data Types

MySQL :: MySQL 8.0 Reference Manual :: 13.4.1 Spatial Data Types

 

MySQL에서 공간 정보를 활용하기 위해 3가지의 요소가 필요합니다.

• 공간 데이터 타입
• 공간 함수
• 공간 관계 함수

 MySQL 공간 데이터 타입

 

공간 데이터 타입은 1~7까지 점점 커지는 의미로 1이 2를 구성한다 보면 됩니다. 우리는 좌표를 활용할 예정이므로 Point를 사용할 것입니다. 

MySQL 공간 함수 

이제 MySQL의 공간함수에 대해 알아봅시다. 우리는 좌표 평면에서 그 평면 기준으로 반경 ?KM를 조사할 것이기에 이에 적합한 함수를 결정해야 합니다.

 

1	ST_Intersection (g1 Geometry, g2 Geometry) : Geometry	g1과 g2의 교집합인 공간 객체를 반환
2	ST_Union (g1 Geometry, g2 Geometry) : Geometry	g1과 g2의 합집합인 공간 객체를 반환
3	ST_Difference (g1 Geometry, g2 Geometry) : Geometry	g1과 g2의 차집합인 공간 객체를 반환
4	ST_Buffer (g1 Geometry, d Double ) : Geometry	g1에서 d 거리만큼 확장된 공간 객체를 반환
5	ST_Envelope (g1 Geometry) : Polygon	g1을 포함하는 최소 MBR인 Polygon을 반환
6	ST_StartPoint (l1 LineString) : Point	l1의 첫 번째 Point를 반환
7	ST_EndPoint (l1 LineString) : Point	l1의 마지막 Point를 반환
8	ST_PointN (l1 LineString) : Point	l1의 n 번째 Point를 반환

 

이 표는 가장 통상적인 공간 함수를 나타냅니다. 여기서 제가 생각한 가장 적합한 함수는 ST_Buffer입니다.

 

ST_Buffer는 임의의 좌표를 기준으로 특정 거리만큼 확장된 영역을 생성하여 다른 조건과 비교할 수 있습니다. 이 특성은 KM 기반 거리 검색을 구현하는 저의 프로젝트에 매우 효과적일 것으로 예상됩니다.

 

공간 관계 함수

공간 관계 함수는 지리 정보 시스템(GIS)에서 공간 데이터를 처리하고, 분석하기 위해 사용됩니다.

이를 통해 두 개 이상의 공간 객체가 어떻게 상호 작용하는지 (예: 겹치는지, 포함되는지, 교차하는지 등)를 확인할 수 있습니다.

1	ST_Equals (g1 Geometry, g2 Geometry) : Boolean	g1과 g2가 동일하면 True를 반환하고 상이하다면 False를 반환
2	ST_Disjoint (g1 Geometry, g2 Geometry) : Boolean	g1과 g2가 겹치는 곳 없다면 True를 반환하고, 겹치는 곳이 있으면 False를 반환
3	ST_Within (g1 Geometry, g2 Geometry) : Boolean	g1이 g2 영역 안에 포함된 경우 True를 반환하고 그렇지 않은 경우 False를 반환
4	ST_Overlaps (g1 Geometry, g2 Geometry) : Boolean	g1과 g2 영역 중 교집합 영역이 존재하는 경우 True를 반환하고 존재하지 않는 경우 False를 반환
5	ST_Intersects (g1 Geometry, g2 Geometry) : Boolean	g1과 g2 영역 간에 교집합이 존재하는 경우 True를 반환하고 그렇지 않은 경우 False를 반환
6	ST_Contains (g1 Geometry, g2 Geometry) : Boolean	g2가 g1 영역 안에 포함된 경우 True를 반환하고 그렇지 않은 경우 False를 반환
7	ST_Touches (g1 Geometry, g2 Geometry) : Boolean	g1과 g2가 경계 영역에서만 겹치는 경우 결과 값으로 True를 반환하며 경계 영역 외에서 겹치거나 겹치는 곳이 없다면 False를 반환
8	ST_Distance (g1 Geometry, g2 Geometry)	Double g1과 g2간의 거리를 반환

이 프로젝트는 범위 안에 포함되어있는 포함 관계를 확인하기에 ST_Within를 사용하기로 했습니다. 

 

 

 

📌정한 쿼리 실험하기 

 

이제 한번 정리해봅시다. 

• 공간 데이터 : Point
• 공간 함수 : ST_Buffer
• 공간 관계 함수 : ST_Within

explain analyze
SELECT *
FROM place
WHERE 
    ST_Within(
        coordinate, 
        ST_Buffer(
            ST_GeomFromText('POINT(37.5665 126.9780)', 4326), 
            10000 
        )
    );

이 쿼리는 서울 중구 기준으로 1km 반경으로 ST_Buffer 연산을 한 것입니다. 

또한 ST_Within는 한 기준점 범위 내에 포함되는지를 검색하는 함수입니다. 

실행 결과 Extra가 Using where이 나왔습니다. 인덱스를 사용하고 있긴 하지만 부분적으로 사용하고 있습니다.

그 이유는 ST_Within에서 인덱스를 사용하지만 ST_Buffer는 인덱스를 사용하지 않는 것입니다.

 

📝공간 조회 쿼리 최적화하기 

앞의 결과로 보면 ST_Buffer는 성능 문제를 안고 있습니다.

• ST_Buffer는 반경을 지정하여 원을 그리는 함수로, 이 원을 정확하게 계산하기 위해 내부적으로 많은 계산을 필요로 합니다.
• ST_Buffer로 생성된 원형 다각형은 실행 시에 동적으로 만들어지므로, 공간 인덱스 활용이 어렵습니다. 

 

그렇기에 ST_Buffer 대신에 직접 범위를 만드는 것을 선택했습니다.

 

그 이유는 미리 정의한 범위에서 인덱스의 데이터를 조회하므로 공간 인덱스를 활용할 수 있기 때문입니다. 

 

이제 테스트를 진행해봅시다. 테스트는 서비스 최대 탐색 범위인 10KM 기준으로 작성했습니다.

 

explain analyze
SELECT *
FROM place 
WHERE ST_Within(coordinate, 
    ST_GeomFromText('POLYGON((37.5396 126.9511, 37.5934 126.9511, 37.5934 127.0049, 37.5396 127.0049, 37.5396 126.9511))', 4326));

'-> Filter: st_within(place.coordinate,<cache>(st_geomfromtext(\'POLYGON((37.4775 126.8890,
37.4775 127.0670, 37.6555 127.0670, 37.6555 126.8890, 37.4775 126.8890))\',4326)))  (cost=112
rows=248) (actual time=0.243..757 rows=14997 loops=1)\n
	-> Index range scan on place using idx_place_coordinate over 
	(coordinate unprintable_geometry_value)  (cost=112 rows=248)
    (actual time=0.153..93.8 rows=15002 loops=1)\n'

위의 결과를 보시면 총 757ms가 걸린 것을 알 수 있습니다.

 

그렇다면 전의 쿼리는 얼마나 걸렸을까요?

explain analyze
SELECT *
FROM place
WHERE 
    ST_Within(
        coordinate, 
        ST_Buffer(
            ST_GeomFromText('POINT(37.5665 126.9780)', 4326), 
            10000 
        )
    );

'-> Filter: st_within(place.coordinate,<cache>(st_buffer(st_geomfromtext(\'POINT(37.5665
126.9780)\',4326),10000)))  (cost=144 rows=319) (actual time=0.736..1681 rows=14414 loops=1)
\n    -> Index range scan on place using idx_place_coordinate over 
	(coordinate unprintable_geometry_value)  (cost=144 rows=319)
	(actual time=0.208..149 rows=17988 loops=1)\n'

해당 쿼리는 1681ms가 걸린 것을 알 수 있습니다. 

그 이유는 인덱스 범위 스캔을 사용하였지만 ST_Buffer에 의한 원형 다각형 계산이 인덱스를 최적화하지 못해 성능이 저하되었습니다.

개선 전	개선 후
1681ms	757ms

 

 

📝결론

이번 쿼리 최적화 작업을 통해, ST_Buffer 함수를 사용하는 기존 방식에서 직접 정의된 POLYGON을 사용하는 방식으로 전환함으로써 성능을 크게 개선할 수 있었습니다.

기존의 ST_Buffer는 원형 다각형을 계산하는 데 많은 리소스를 소모하여 인덱스 최적화를 제대로 활용하지 못했습니다. 이로 인해 쿼리 실행 시간이 1681ms로 매우 길었죠.

반면 직접 정의된 POLYGON을 사용하는 방법은 인덱스를 잘 활용할 수 있어, 쿼리 실행 시간이 757ms로 크게 단축되었습니다. 이로 인해 시스템의 성능이 크게 개선되었으며, 대규모 데이터 처리에 있어서도 응답 속도를 크게 향상시킬 수 있었습니다.

최적화 전: 1681ms
최적화 후: 757ms

따라서 ST_Buffer 대신 POLYGON을 사용한 방법이 더 효율적이며, 공간 데이터를 다룰 때 성능 향상을 위해 이러한 접근 방식을 채택하는 것이 매우 중요하다는 결론을 내릴 수 있습니다.