공간 인덱스를 이용해 조회 속도 개선하기

"다른 DB에 대한 특별한 언급이 있지 않으면 대부분 MySQL을 기준으로 설명합니다."

기능 소개

우리 서비스의 메인 기능은 사용자 주변에 있는 수거함들을 조회하는 것이다.

중심점으로부터 반지름이 600m인 원을 그리고, 원 안에 포함되는 수거함들을 다음과 같이 마커로 표시한다.

이 프로젝트에서는 JPA를 사용하고 있으며, MySQL에서 지원하는 공간 데이터를 다루기 위해 Hibernate Spatial과 QueryDSL Spatial 라이브러리를 사용했다.

문제 상황

기능을 구현한 상태에서 ngrinder로 성능 테스트를 진행해봤는데, 결과는 충격적이었다..

Vusers를 99로 설정했을 때 TPS는 3.8이 나왔고, Latency(Mean Test Time)는 20560ms(200sec)가 나왔다😇

아직 성능 테스트 지표에 대한 지식은 부족하지만, 확실히 문제가 있는 것은 알 수 있었다.

그래서 나는 쿼리 쪽에서 성능을 개선할 수 있지 않을까 하여 MySQL 공식 문서를 다시 한 번 확인했고, 다음과 같은 문장을 발견할 수 있었다.

The optimizer can use SPATIAL indexes on the column.

즉, MySQL의 옵티마이저는 공간 인덱스를 이용해 공간 연산 성능을 최적화할 수 있다고 한다.

공간 인덱스

공간 인덱스(Spatial Index)는 말 그대로 공간 데이터 타입에 대한 검색 작업을 최적화할 수 있도록 인덱싱하는 데 사용되는 형식이다.

여기서 말하는 '공간 데이터 타입에 대한 검색 작업'은 대표적으로 다음과 같다.

• 주어진 좌표(Point)를 포함하는 모든 객체를 검색하는 쿼리
• 주어진 영역(Region)과 겹치는 모든 객체를 검색하는 쿼리

쉽게 말해서 조회 속도를 향상시키기 위해 일반 인덱스를 사용하는 것처럼 공간 데이터도 똑같이 인덱스를 사용할 수 있는데, 이 경우 특별한 인덱스를 사용하는 것 뿐이다.

많은 데이터베이스에서 이런 공간 인덱스를 지원하는데, MySQL의 경우 MyISAM과 InnoDB 엔진에서만 지원한다.

MySQL에서 지원하는 공간 인덱스는 quadratic split 방식을 이용한 R-Tree로 구현되어 있다.

R-Tree가 뭔지, quadratic split 방식은 또 뭔지 가볍게 살펴보자.

MBR

R-Tree는 공간 객체를 MBR로 근사화하여 생성되는 트리 구조이므로, 먼저 MBR에 대한 이해가 필요하다.

MBR은 Minimum Bounding Rectangle의 약어로 최소 경계 사각형이라는 의미를 가지고 있다. 쉽게 말해 어떤 공간 객체를 포함하는 최소 크기의 사각형을 의미한다.

위와 같이 어떤 지점의 좌표(붉은색 점), 직선 거리(보라색 선), 면적(파란색 다각형)의 공간 객체들이 있을 때, 각각을 포함하는 최소 크기의 사각형을 그리면 다음과 같다.

이처럼 어떤 도형을 포함하는 최소 경계 사각형을 MBR이라고 한다.

R-Tree란?

R-Tree는 BST, AVL-TRee와 같은 검색 트리의 한 종류로 B-Tree에서 다차원 검색이 가능하게끔 확장시켜 놓은 형태의 자료구조를 의미한다.

R-Tree는 여러 MBR들의 포함 관계를 트리 구조로 나타내는데, 이는 아래 그림을 통해 이해할 수 있다.

여러 개의 MBR들을 포함하는 MBR을 계속해서 그리다보면 위와 같은 형태가 나오는데, 이들을 R-Tree로 표현할 수 있게 된다.

R-Tree 특징

• 각 노드에 저장할 수 있는 도형의 개수는 ⎡M/2⎤~ M이다.
  (M = 각 노드의 최대 자식 노드 개수)

• root 노드는 최소 2개의 자식 노드를 가진다.

• 부모 노드의 MBR은 자식 노드의 MBR을 모두 포함한다.

• 같은 depth의 노드에 속한 MBR은 최대한 겹치지 않도록 해야 한다.

• MySQL의 R-Tree는 여러 분할 알고리즘 중 quadratic split 방식을 사용한다.

알아두면 좋은 내용

• 공간 데이터 타입의 컬럼에는 공간 인덱스 뿐만 아니라, B-Tree 기반의 일반 인덱스도 사용할 수 있다.
  이 경우 정확한 값을 검색하는 데에는 유용하지만, 범위 검색에는 적합하지 않다.

• 옵티마이저가 공간 인덱스를 사용하기 위해서는 해당 컬럼에 SRID(Spatial Reference ID)가 지정되어 있어야 한다.

• 공간 인덱스는 각 MBR의 포함 관계를 이용해 만들어진 인덱스이므로, ST_Contains() 또는 ST_Within() 같은 포함 관계를 비교하는 함수로 검색하는 경우에만 사용 가능하다.

사용 방법

일반적인 인덱스와 마찬가지로 다음과 같이 간단하게 한 줄만 입력하면 공간 인덱스를 생성 및 컬럼에 적용할 수 있다.

CREATE SPATIAL INDEX 인덱스명 ON 테이블명 (컬럼명);
CREATE SPATIAL INDEX geom_idx ON location (geom);

반대로 인덱스를 제거하고 싶은 경우에도 다음과 같이 한 줄만 입력하면 된다.

alter table 테이블명 drop index 인덱스명;
alter table location drop index geom_idx;

인덱스 적용 후 조회 속도

공간 인덱스를 적용했을 때 어느 정도의 차이가 나올지 비교하기 위해, 적용 전후 모두 응답 속도를 평균내봤다.

그 결과 적용 전 조회 속도는 평균 586ms, 적용 후 조회 속도는 평균 20ms가 나왔다.
대략 29배 정도 빨라진 것으로 확연한 차이를 보였다.

PostGIS (PostgreSQL)

사실 공간 데이터 조회 성능을 개선하기 위해 PostGIS라는 방법도 있다는 것을 발견했지만, 결과적으로는 사용하지 않았다.

PostGIS란?

PostGIS는 PostgreSQL에서 공간 데이터를 처리할 수 있는 다양한 기능을 제공하는 확장 모듈이다.

여기서 말하는 기능은 다음과 같다.

• 점, 선, 다각형, 도형, 다중 도형과 같은 공간 데이터 저장
• 공간 인덱싱을 통해 위치 기반의 공간 데이터 빠른 검색 및 조회
• 공간 데이터 필터링 및 분석, 거리와 면적 계산, 버퍼링 등 광범위한 공간 연산 제공
• 단순화, 변환, 일반화 등 geometry 타입의 데이터를 처리하고 조작할 수 있는 도구 제공
• 고도, 날씨 데이터와 같은 raster 데이터 저장 및 처리
• geocoding과 reverse geocoding을 위한 함수 제공

MySQL과 어떤 점이 다르지?

MySQL과 달리 PostGIS는 R-Tree-over-GIST scheme를 이용해 공간 인덱스를 제공한다. 이는 GIST 인덱스 위에 R-Tree 인덱스를 구현한 방식으로 다양한 데이터 유형에 적용할 수 있으며, 더 복합적으로 구현되어 복잡한 공간 질의에 대해 높은 성능을 보인다.

실제로 여기에서는 PostGIS가 MySQL보다 10배 이상의 높은 성능을 보여준다고 설명하고 있다.

추가로 스택오버플로우의 한 게시글에서도 PostGIS가 다른 DB에 비해 높은 안정성을 보장하고, 복잡한 연산을 가능하게 해준다고 설명한다.

직접 비교해보자

많은 곳에서 PostGIS가 더 좋은 성능을 보여준다고 설명하지만, 그들의 상황과 내 상황은 다르기 때문에 직접 비교해보는 것이 좋겠다고 판단했다.

create index geom on location using gist(point)

우선 위와 같이 공간 인덱스를 생성 및 적용한 뒤 아래처럼 PostGIS에 맞춰 쿼리를 약간 수정했다.

select l.point
from collecting_box c join location l on c.location_id = l.id
where 
	st_contains(st_buffer(st_geomfromtext('point(126.887423153328 37.4906534376757)', 4326)::geography, 600)::geometry, l.point) and
	c.tag in ('CLOTHES', 'LAMP_BATTERY', 'MEDICINE', 'TRASH');

데이터를 MySQL 실행 환경과 동일하게 저장해놓고 쿼리를 돌려봤고, 평균 조회 속도는 57ms가 나왔다.

이는 내 예상과 달리 MySQL에 비해 많은 차이를 보이지 않는 결과였고, 오히려 더 느린 속도였다.

왜 PostGIS가 더 느릴까?

PostgreSQL은 자체적으로 쿼리 최적화 기술이 구현되어 있기 때문에, 복잡한 쿼리를 요구하거나 대규모 서비스인 경우에 특화되어 있다. 하지만 단순 CRUD 시에는 MySQL에 비해 성능이 조금 떨어진다고 한다.

이러한 PostgreSQL의 특성을 봤을 때, 데이터 규모가 크지 않고 공간 쿼리의 복잡도가 낮은 우리 서비스에서는 MySQL과 차이를 거의 보이지 않거나, 오히려 더 느릴 수도 있겠다고 판단했다.

이는 제가 가진 얕은 지식을 통해 내린 판단일 뿐이며, 잘못된 판단일 수 있습니다.

결론

DB를 마이그레이션하는 과정에서 생기는 여러 비용을 감당하면서 별로 차이가 나지 않는 PostgreSQL을 사용하는 것은 적절하지 않은 선택이라 생각한다.
따라서 기존에 사용하고 있던 MySQL을 이어서 사용하되 공간 인덱스를 적용하여 성능을 개선하였다.

Ref.

https://dad-rock.tistory.com/594
https://tecoble.techcourse.co.kr/post/2023-10-04-spatial-data/
https://steemit.com/kr-dev/@tmkor/db-2-mysql-vs-postgis-postgresql
https://velog.io/@sdsd0908/%EC%8A%A4%ED%94%84%EB%A7%81-%EB%B0%98%EA%B2%BD-%EA%B2%80%EC%83%89-%EA%B8%B0%EB%8A%A5-DB-%EB%B3%80%EA%B2%BD%EC%97%90-%EB%8C%80%ED%95%9C-%EA%B3%A0%EB%AF%BC
https://dev.mysql.com/doc/refman/8.4/en/spatial-types.html