1. 들어가기전
현재 좌표를 기준으로 주변 데이터들을 조회하는 기능을 개발하고자 한다.
다음 그림처럼 원의 중간점을 현재 좌표로 설정하였다면 줌 레벨에 따라 반경 (X) Km의 원 내에 존재하는 데이터들을 조회하는 기능을 구현하고자 한다. 어렴풋이 공간 데이터, 공간 함수에 대해서 들어본적이 있었기에 현재 사용중인 데이터베이스인 MySQL의 공간 데이터, 공간 함수, 그리고 공간 인덱스를 통한 성능 개선을 해보고자 한다.
2. 공간 데이터
공간 데이터란 말 그대로 공간 정보를 저장할 수 있는 데이터베이스를 의미한다.
다음 그림은 MySQL에서 제공하는 공간 데이터 타입이다.
| Point | 좌표 공간의 한 지점 | POINT(10 10) |
|---|---|---|
| LineString | 다수의 Point를 연결해주는 선분 | LINESTRING(10 10, 20 20, 30 30) |
| Polygon | 다수의 선분들이 연결되어 닫혀있는 상태 | POLYGON((10 10, 10 20, 20 10, 10 10)) |
| Multi-Point | 다수의 Point 집합 | MULTIPOINT(10 10, 20 20) |
| Multi-LineString | 다수의 LineString 집합 | MULTILINESTRING((10 10, 20 20), (15 15, 25 25)) |
| Multi-Polygon | 다수의 Polygon 집합 | MULTIPOLYGON(((10 10, 10 20, 20 10, 10 10)), ((40 40, 30 30, 40 40))) |
| GeomCollection | 모든 공간 데이터들의 집합 | GEOMETRYCOLLECTION(POINT(10 10), LINESTRING(20 20, 30 30)) |
현재 개발중인 기능은 각 데이터마다 좌표를 가지고 있어야 했다.
CREATE TABLE place
(
id BIGINT NOT NULL AUTO_INCREMENT,
coordinate POINT NOT NULL SRID 4326,
PRIMARY KEY (id),
);
CREATE SPATIAL INDEX idx_coordinates ON place (coordinate);따라서 Mysql의 Point함수를 를 이용하여 각 데이터의 공간 데이터를 만들어주었다.
2.1 SRID를 설정한 이유
column에 SRID값을 적용 시킬 수 있는데, SRID란 Spatial Reference System Identifier의 약자로 쉽게 말해 좌표 시스템이다.
그럼 이 SRID를 설정한 이유는 무엇일까? 결론부터 말하자면 공간 인덱스를 적용하기 위해서이다.
MySQL의 공식 문서를 참고하면 공간 인덱스를 적용하기 위해서는 SRID를 반드시 설정해야 한다고 서술되어 있다.
SRID 4326은 우리가 흔히 아는 위도, 경도 시스템을 사용하기 위해서 설정한 값으로 WSG84 좌표계를 사용하기 위해서 설정해주었다. 좌표계에는 다양한 것이 있는걸로 알고있는데, 현재 개발중인 기능은 거리 계산을 이용하여 특정 범위 내의 데이터들만 보여주어야 하므로 WSG84 좌표계를 사용하였다.
3. 공간 함수
MySQL에는 공간 데이터 뿐만 아니라 공간 함수도 제공하고 있다. (Mysql 공간 함수)
[MySQL :: MySQL 5.7 Reference Manual :: 12.16.1 Spatial Function Reference
MySQL 5.7 Reference Manual / ... / Functions and Operators / Spatial Analysis Functions / Spatial Function Reference 12.16.1 Spatial Function Reference The following table lists each spatial function and provides a short description of eac
dev.mysql.com](https://dev.mysql.com/doc/refman/5.7/en/spatial-function-reference.html)
| ST_Buffer (g1 Geometry, d Double ) : Geometry | g1에서 d 거리만큼 확장된 공간 객체를 반환한다 |
|---|---|
| ST_PointN (l1 LineString) : Point | l1의 n번째 Point를 반환한다 |
| ST_Equals (g1 Geometry, g2 Geometry): Boolean | g1과 g2가 동일하면 True, 다르면 False를 반환한다 |
| ST_Contains (g1 Geometry, g2 Geometry): Boolean | g2가 g1 영역 안에 포함되는 경우 True, 그렇지 않으면 False를 반환한다 |
| ST_Distance (g1 Geometry, g2 Geometry): Double | g1과 g2간의 거리를 반환한다 |
매우 다양한 함수를 제공하고 있는데, 궁금하다면 해당 링크를 참고하길 바란다.
4. 공간 인덱스
인덱스가 B-Tree로 관리되는 반면 공간 인덱스는 R-Tree로 관리된다. R-Tree인덱스를 설명하기 이전에 MBR에 대해서 알고 있어야 하는데, MBR은 최소 경계 상자로 공간 데이터(Point, Line, Polygon)들을 감싸는 최소 크기의 사각형을 의미한다.
최상위 레벨: R1 ~ R
차상위 레벨: R3 ~ R7
최하위 레벨: R8~R19
MBR을 그림으로 표현하면 다음과 같은 형태가 되고, 이 그룹들을 B-Tree 형식으로 표현한것을 R-Tree 인덱스라고 한다.
5. 트러블 슈팅
ST_Distance_Sphere 함수가 인덱스를 타지 않음
- 초기 쿼리문
SELECT *
FROM place
WHERE ST_Distance_Sphere(
coordinate, ST_PointFromText('POINT(35.8413 128.7601)', 4326)
) <= 3000;
초기 버전에는 공간 함수인 ST_DISTANCE_SPHERE를 이용하여 좌표들 사이의 거리를 계산하는 쿼리문을 작성했다. 해당 쿼리로 A좌표와 B좌표의 거리를 계산하여 반경 3KM안이라면 해당 좌표에 있는 데이터를 보여주고자 하였다.
해당 쿼리의 실행 계획을 살펴보자.
분명 CREATE SPATIAL INDEX idx_coordinates ON place (coordinate) 를 이용하여 공간 인덱스를 설정하였으나 실행 계획에서 type이 ALL로 공간 인덱스를 타지않고, Full-Table Scan 방식을 이용하고 있는것을 볼 수 있다.
구글링을 통해 알게된 점은 ST_Distance_Sphere 함수의 동작 방식에 그 이유를 알게되었다. 해당 함수는 모든 좌표와의 거리를 계산한 뒤 대소 비교를 통해 데이터를 조회하므로 전체 데이터를 조회할 수 밖에 없으므로 Full-Table-Scan 방식으로 동작하는 것이었다.
- 변경 쿼리문
SELECT *
FROM location
WHERE ST_CONTAINS(
ST_BUFFER(
ST_PointFromText('POINT(35.8601 128.6200)', 4326), 3000), coordinate);
ST_Buffer()는 좌표를 기준으로 반경 3KM 원에 가까운 Polygon Buffer를 생성한다. 그리고 Buffer가 생성되면 ST_Contains()로 Buffer 안에 완전히 포함되는 점에 대해서만 true를 반환하도록 하였다.
수정된 쿼리의 실행 계획을 살펴보자.
type이 range로 공간 인덱스가 성공적으로 적용된 것을 알 수 있다.
5. 성능 분석
1. 데이터 5만개
- 인덱스 X
type이 ALL로 인덱스를 타지않는 것을 볼 수 있으면 쿼리 실행시간은 580ms다. 네트워크를 탄것도 아닌 오직 쿼리문의 실행 속도만으로 580ms라면 실제 운영 서버에 적용하기에는 다소 문제가 있다.
- 인데스 O
type은 range로 인덱스가 적용된것을 볼 수 있으며 쿼리 실행시간은 10ms로 매우 빠른 속도로 성능이 향상된것을 확인할 수 있다. 이정도 수치는 실제 서버에 적용하더라도 큰 문제가 없다.
-> 조회 성능이 약 58배 증가하였다.
2. 데이터 10만개
- 인덱스 X
데이터수가 늘어날수록 성능은 더욱 떨어지는데, 역시 공간 인덱스를 적용하지 않을시 데이터는 총 10만개이지만 조건에 일치하는 데이터는 오직 128개만 존재한다. 하지만 쿼리의 실행 속도는 무려 1.18 sec가 걸린걸 알 수 있다.
이는 역시 실제 서버에 적용하기에는 아주 큰 문제가 있다.
- 인덱스 O
똑같은 조건으로 공간 인덱스를 적용시킬 경우 10만개의 데이터 중 128개의 데이터를 조회하는데 단 30ms 밖에 걸리지 않았다.
-> 조회 성능이 약 40배 향상된 것을 확인할 수 있다.
이러한 성능 분석의 결과 공간 데이터를 사용할 경우 공간 인덱스를 반드시 사용하는것이 성능 향상에 큰 도움이 될 것이다.