안녕하세요, @tmkor 입니다. 오늘은 비상업 데이터베이스의 공간 질의 성능을 비교해보도록 하겠습니다.
MySQL
MySQL은 국내에서 인기가 좋은 관계형 데이터베이스 입니다. 오라클에 인수된 이후로 MariaDB로 이름을 바꾸어 개발이 계속되고 있습니다. 여전히 CLI 명령어 이름은 mysql 이지요. ^^;
MySQL은 Geometry 데이터타입과 간단한 공간 인덱스를 지원하고 있습니다. 자세한 내용은 https://dev.mysql.com/doc/refman/5.7/en/spatial-types.html 페이지에 설명되어 있습니다.
PostGIS (PostgreSQL)
PostgreSQL은 데이터베이스의 거장인 마이클 스톤브레이커의 주도로 학술적으로 개발이 진행된 객체지향 데이터베이스 입니다. 뭐, 저희가 쓸 때에는 대부분 관계형 DB처럼 사용합니다. 오픈소스로 되어있고, 확장이 간편합니다. 대신 문서화가 좀 약한게 단점입니다.
PostGIS는 postgreSQL를 기반으로 공간 분석을 위한 Geometry, Geography 데이터타입, 그리고 다양한 공간 함수를 지원하고 있습니다.
(마이클 스톤브레이커는 Ingres에서 관계형 DB를 다지고, Postgres에서 객체지향 DB를 다지고, 스트리밍 DB, 컬럼형 DB, 분산 DB, 인메모리 DB 등 대부분의 데이터베이스를 설계/개발한 사람입니다. 그냥 이 사람의 여정을 따라가면 DB의 역사가 나옵니다. ㄷㄷㄷ)
PostGIS 정보는 http://postgis.net 에서 확인하실 수 있습니다.
실험 재료
1. 범위 질의
범위 질의(range query)를 이용하여 실험을 진행합니다. 범위 질의는 사각형의 영역안에 존재하는 데이터를 찾는 질의 입니다. 우리가 지도 앱을 키면 우리가 볼 수 있는 영역에 대한 정보가 필요합니다. 그 외의 정보는 사실 필요하지 않죠. 예를 들어, 반포동 지도를 보고 있을 때, 부산의 아파트에 대한 정보까지 필요할까요? 아니죠, 딱 필요한 영역의 정보만 필요합니다. 그 때 범위 질의를 사용합니다.
[지도에 표시할 아파트를 그리기 위해서는 보이는 영역에 대한 범위 질의를 수행합니다.]
2. 서울시 아파트
범위 질의를 사용할 데이터 집합은 서울시의 아파트입니다. 범위는 37.456795 127.0027725 에서 37.4897942 127.059656 까지이고 대략 2000여개의 아파트가 잡힙니다.
3. 인덱스
인덱스는 자료 검색을 빠르게 해주는 마법의 도구입니다. 알라의 마법봉처럼 엄청 빠르게 문제를 해결하게 도와줍니다. 초창기 구글의 핵심 원료는 바로 이 인덱스였지요.
MySQL은 R-Tree 기반의 공간 인덱스를 지원합니다.
PostGIS는 GIST 인덱스를 지원합니다.
결과
query 1 : 개수 질의
- mysql query
SELECT count(*) FROMfilterTableWHERE MBRContains( GeomFromText( 'LINESTRING(37.456795 127.0027725,37.4897942 127.059656)' ), WGS84)
- postgis query
SELECT count(*) from filterTable where 'LINESTRING(37.456795 127.0027725,37.4897942 127.059656)'::geometry ~ WGS84
결과
mysql : 10회 평균 18.798ms
postgis : 10회 평균 3ms
query 2 : selection 질의
- mysql query
SELECT * FROMfilterTableWHERE MBRContains( GeomFromText( 'LINESTRING(37.456795 127.0027725,37.4897942 127.059656)' ), WGS84)
- postgis query
SELECT * from filterTable where 'LINESTRING(37.456795 127.0027725,37.4897942 127.059656)'::geometry ~ WGS84
결과
mysql : 10회 평균 136.16 ms
postgis : 10회 평균 27.82 ms
query 3 : natural join 질의
- mysql query
SELECT * from filtertable natural join nametable WHERE MBRContains( GeomFromText( 'LINESTRING(37.456795 127.0027725,37.4897942 127.059656)' ), WGS84)
- postgis query
SELECT * from filterTable natural join nametable where 'LINESTRING(37.456795 127.0027725,37.4897942 127.059656)'::geometry ~ WGS84
결과
mysql : 10회 평균 9699.16 ms
postgis : 10회 평균 267.29 ms
결론
- PostGIS의 gist index의 경우 MySQL의 spatial index에 비해 10x 이상의 성능 향상을 가져다 줍니다.
- 다만, PostGIS의 경우 지원 문서 및 사용자 수가 적으므로, 상황에 맞추어 선택을 하는 것이 좋습니다.