[MySQL] 공식문서의 공간 데이터 파트 정리와 공간 인덱스 성능 테스트 기록

이번 글에서는 MySQL에서 공간 데이터를 다루는 법을 알아보고 실습을 통해 성능을 비교해 본 것을 기록합니다. 특히 가장 많이 사용되는 특정 좌표로부터 특정 거리 내의 좌표를 찾는 연산을 위주로 실습하고 인덱스와 공간 연산의 성능을 테스트합니다.
 
들어가기 앞서 제가 이 글에서 사용한 MySQL 버전은 v8.3.0입니다.
 

MySQL의 공간 데이터

MySQL의 `MyISAM`, `InnoDB`, `NDB`, `ARCHIVE` 스토리지 엔진은 공간 데이터 타입과 관련 함수를 지원합니다. 이 중 `MyISAM`과 `InnoDB`는 공간 데이터 타입 컬럼에 대해 공간 인덱스와 비공간 인덱스를 지원하고 `NDB`와 `ARCHIVE`는 비공간 인덱스만 지원합니다. `InnoDB`는 데카르트 SRS와 geographic SRS를 지원하는 반면 `MyISAM`은 데카르트 SRS만 지원합니다.
참고로 MySQL에서 공간 인덱스를 사용하기 위해서는 SRID를 설정해야 합니다. (10.3.3. SPATIAL Index Optimization)
SRS(Spatial Reference System)와 SRID에 대해서는 아래에서 다루도록 하겠습니다.
 

공간 데이터 타입

MySQL의 공간 데이터 타입은 OpenGIS 모델을 따라 계층형 구조를 갖습니다.

• Geometry: 모든 공간 데이터 타입의 조상으로 인스턴스를 생성할 수 없지만 모든 타입을 저장할 수 있습니다.
  • `Point`: 0차원
  • Curve: 1차원
    • `LineString`
      • Line
      • LinearRing
  • Serface: 2차원
    • `Polygon`
  • `GeometryCollection`
    • `MultiPoint`
    • MultiCurve
      • `MultiLineString`
    • MultiSerface
      • `MultiPolygon`

인라인 코드로 하이라이트 된 클래스는 인스턴스화가 가능하고 그렇지 않은 클래스는 인스턴스를 생성하지 못하는 인터페이스라고 생각하시면 이해하기 쉽습니다.
`line`과 `linearRing`은 `LineString`이 폐곡선이면 `LinearRing`, 그렇지 않다면 `Line`으로 구분됩니다.
`GeometryCollection`의 경우 모든 요소가 동일한 SRID를 가져야 합니다.
 

공간 데이터 형식

MySQL은 공간 데이터를 두 가지 형식으로 표현합니다. 

• WKT(Well-Known Text)
• WKB(Well-Known Binary)

실제 저장되는 형태는 위 두 형태와는 다릅니다. 위 두 형식은 사용자가 읽을 수 있는 형태로 표현하는 형식입니다. 공식 문서에 따르면 실제 저장되는 내부 형식은 WKB와 비슷하지만 앞 4바이트에 SRID가 있다고 합니다.
 
WKT의 예시를 보면 다음과 같습니다.

POINT(15 20)
LINESTRING(0 0, 10 10, 20 25, 50 60) -- 4개의 점으로 이루어진 LineString
POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5)) -- 외부 링과 내부 링
MULTIPOINT(0 0, 20 20, 60 60)
GEOMETRYCOLLECTION(POINT(10 10), POINT(30 30), LINESTRING(15 15, 20 20))

 
주의할 점은 WKT로 표현된 Point에는 쉼표가 쓰이지 않는다는 점입니다.

SELECT ST_X(Point(15, 20)); -- 15
SELECT ST_X(ST_GeomFromText('POINT(15 20)')); -- 15

위 두 구문은 같은 결과를 냅니다.
첫 번째 구문은 `Point(x, y)`함수를 이용해 Point 객체를 생성한 후 `ST_X()` 함수로 x값을 출력한 구문이고
두 번째 구문은 WKT로 표현된 좌표를 `ST_GeomFromText()` 함수를 이용해 Point로 변환한 뒤 x값을 출력한 구문입니다.
함수에는 쉼표로 두 좌표 값을 구분하지만 WKT에는 값 사이에 쉼표를 사용하지 않습니다.
WKB에 대한 설명은 바로 위 공식문서에서 확인하실 수 있습니다.
 

Spatial Reference System

SRS는 쉽게 생각해 좌표계로 이해할 수 있습니다. 공간 데이터를 다룰때 SRS는 아주 중요합니다.
공간 데이터를 다루려면 해당 데이터가 2차원인지 3차원인지 알아야하고 2차원이라면 3차원 지구를 어떻게 2차원에 투영한 것인지, 3차원 이라면 지구를 어떤 타원체로 모델링 하여 표한한 것인지, 지구의 중심은 어디에 위치하는 지 등 공간 데이터를 정의하는 좌표계가 필요합니다. 
이렇게 데이터를 실제 지구와 매핑 시켜주는 시스템이 필요하고 이를 SRS로 생각할 수 있습니다. SRS에는 정말 많은 종류가 있고 이곳에서 13419개의 SRS를 확인하실 수 있습니다. EPSG(European Petroleum Survey Group)에서 SRS들에 부여한 ID를 `EPSG 코드`라고 소프트웨어에서는 SRID로 많이 사용됩니다.
이번 실습에서 저희가 사용할 세계측지계 WGS 84의 EPSG 코드는 4326입니다.
 
글 초반에 `InnoDB`는 데카르트 SRS와 geographic SRS를 지원하고 `MyISAM`은 데카르트 SRS만 지원한다고 설명드렸습니다. `데카르트 SRS`는 2차원에 투영된 좌표계, `geographic SRS`는 3차원 좌표계로 이해할 수 있습니다.
즉 `MyISAM`은 3차원 공간 데이터를 지원하지 않는 스토리지 엔진입니다.
 
MySQL에 새로운 SRS를 등록할 수도 있습니다. SRS를 등록하는 방법과 MySQL이 기본으로 지원하는 SRID는 15.1.19 CREATE SPATIAL REFERENCE SYSTEM Statement에서 확인 하실 수 있습니다.
 

일정 범위 내 점 데이터 조회 테스트

MySQ에서 공간데이터를 어떻게 저장하고 표현하는지 살펴봤으니 이제 실습을 통해 함수와 문법을 살펴보겠습니다.
공간 함수는 이곳에서 확인 하실 수 있습니다. 14.16.1 Spatial Function Reference
 

테이블 생성

공간 인덱스의 사용에 대한 성능 비교를 위해 `InnoDB`를 사용했고 공간 인덱싱을 위해 SRID를 설정했습니다.
이왕이면 현실 세계를 반영하자는 생각으로 SRID는 4326으로 설정했습니다.
비교를 위해 인덱스가 없는 경우(test 테이블), 비공간 인덱스가 있는 경우(test_indexed 테이블), 공간 인덱스가 있는 경우(test_spatial_indexed 테이블)을 생성합니다.
 
참고로 MySQL에서 공간 인덱스는 R-Tree로, 비공간 인덱스는 B-Tree로 관리됩니다.

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  location POINT NOT NULL SRID 4326
) ENGINE=InnoDB;

CREATE TABLE test_indexed (
  id INT AUTO_INCREMENT PRIMARY KEY,
  location POINT NOT NULL SRID 4326,
  INDEX(location)
) ENGINE=InnoDB;

CREATE TABLE test_spatial_indexed (
  id INT AUTO_INCREMENT PRIMARY KEY,
  location POINT NOT NULL SRID 4326,
  SPATIAL INDEX(location)
) ENGINE=InnoDB;

 

더미 데이터 생성

파이썬을 이용해 우리나라의 위경도 범위(위도 33~38, 경도 126~129) 내에서 20만개의 무작위 좌표를 생성하고 세 개의 테이블에 같은 좌표를 2만개씩 배치로 삽입했습니다.

import mysql.connector
from mysql.connector import Error
import random

host='host'
database='database'
user='user'
password='password'

tables = ['test', 'test_indexed', 'test_spatial_indexed']
total_size = 400_000
batch_size = 20_000

def insert_dummy_data(tables, total_size, batch_size):
    try:
        connection = mysql.connector.connect(host=host, database=database, user=user, password=password)
        if connection.is_connected():
            cursor = connection.cursor()
            batch_num = total_size // batch_size
            for batch in range(batch_num):
                for _ in range(batch_size):
                    lat = random.uniform(33, 38)
                    lng = random.uniform(126, 129)
                    
                    for table_name in tables:
                        query = f"INSERT INTO {table_name} (location) VALUES (ST_GeomFromText('POINT({lat} {lng})', 4326));"
                        cursor.execute(query)
                    
                connection.commit()
                print(f"Batch {batch + 1}/{batch_num}: {batch_size} records inserted successfully into tables")
                
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")

insert_dummy_data(tables, total_size, batch_size)

 

용량 확인

SELECT 
    TABLE_NAME AS 'Table',
    TABLE_ROWS AS 'Rows',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS 'Total Size (MB)',
    ROUND((DATA_LENGTH) / 1024 / 1024, 2) AS 'Data Size (MB)',
    ROUND((INDEX_LENGTH) / 1024 / 1024, 2) AS 'Index Size (MB)'
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_SCHEMA = 'test_db' AND 
    TABLE_NAME IN ('test', 'test_indexed', 'test_spatial_indexed');

200 개정도 차이나긴 하지만 그냥 진행하겠습니다.
 
이상한 점은 index size가 0이라는 점입니다.

비공간 인덱스로 설정했지만 공간 인덱스로 생성된 모습

`test_indexed`와 `test_spatial_indexed`를 보면 모두 공간 인덱스가 생성된 것을 확인 할 수 있습니다. InnoDB가 자동으로 `test_inexed`의 비공간 인덱스를 공간 인덱스로 생성한 것 같습니다.
 

공간 인덱스의 성능 비교

성능 비교는 Performance Schema를 이용해 진행했습니다. 이전 Perforamce Schema를 이용하기 위해 node에서 MySQL 쿼리 실행 시간을 측정하는 npm 패키지를 만들었을 때 정리한 방법과 EXPLAIN으로 진행했습니다.
 
성능 비교는 글 처음에서 언급 했듯 특정 좌표로 부터 일정 거리 안에 있는 점들을 조회하는 쿼리로 진행하겠습니다.
세 개의 테이블에 각각 2개의 쿼리를 실행 시키고 총 6개의 결과에 대해 실행 시간과 인덱스 사용 여부를 확인해 보겠습니다.
쿼리에 사용될 함수는 ST_Buffer와 ST_Distance_Sphere입니다.
아래는 중심 좌표로 부터 10km 이내의 점의 수를 세는 쿼리입니다.

-- ST_Buffer + ST_Contains
SELECT count(*) FROM test 
WHERE ST_Contains((ST_Buffer(ST_PointFromText('POINT(35.5 127.5)',4326), 10000)), location);

SELECT count(*) FROM test_indexed  
WHERE ST_Contains((ST_Buffer(ST_PointFromText('POINT(35.5 127.5)',4326), 10000)), location);

SELECT count(*) FROM test_spatial_indexed  
WHERE ST_Contains((ST_Buffer(ST_PointFromText('POINT(35.5 127.5)',4326), 10000)), location);


-- ST_Distance_Sphere
SELECT count(*) FROM test 
WHERE ST_Distance_Sphere(location, ST_PointFromText('POINT(35.5 127.5)',4326)) < 10000;

SELECT count(*) FROM test_indexed 
WHERE ST_Distance_Sphere(location, ST_PointFromText('POINT(35.5 127.5)',4326)) < 10000;

SELECT count(*) FROM test_spatial_indexed 
WHERE ST_Distance_Sphere(location, ST_PointFromText('POINT(35.5 127.5)',4326)) < 10000;

실행 결과

Explain

결과

SELECT EVENT_ID, TRUNCATE(TIMER_WAIT/1000000000000,6) as Duration, SQL_TEXT, NO_INDEX_USED
FROM performance_schema.events_statements_history_long
WHERE SQL_TEXT like '%FROM test%';

성능 결과

인덱스를 사용한 테이블에서 `ST_Buffer()`를 사용한 쿼리가 0.06 ~ 0.08 초 대로 가장 빨랐습니다.
`ST_Distance_Sphere()`는 인덱스의 영향을 받지 않고 모두 0.4 ~ 0.5초 대이며 인덱스를 사용하지 않은 테이블에서 `ST_Buffer()`가 2초 대로 가장 느렸습니다.

간단한 테스트지만 대략 30배 정도 차이가 납니다.
 
이유를 생각해 보면 `ST_Distance_Sphere()`는 구면에서의 거리를 계산한 뒤 결과를 대소비교를 통해 결과를 도출합니다. 모든 좌표에 대해 거리를 계산해야하니 인덱스를 사용할 수 없는 것 같습니다.
반면 `ST_Buffer`는 해당 점을 기준으로 버퍼를 생성하고 버퍼에 속하는 점을 찾습니다. 이때 공간 인덱스를 사용해 버퍼에 속하는 점을 빠르게 찾을 수 있습니다.
 
여기서 이상한 점이 있습니다. 위 6개의 쿼리 결과를 보면 `ST_Distance_Sphere()`는 440개, `ST_Buffer()`는 435개로 결과가 다릅니다.
`ST_Distance_Sphere()`가 더 많은 이유는 동작 방식의 차이 때문입니다. `ST_Buffer()`는 점을 기준으로 반경 10km 짜리 원에 가까운 폴리곤 버퍼를 생성합니다. 버퍼가 생성된 뒤`ST_Contains()`는 이 버퍼 안에 완전히 포함되는 점에 대해서만 true를 반환합니다. 즉 버퍼의 경계와 조금이라도 걸친 점들은 결과에 포함되지 않습니다. 따라서 `ST_Distance_Sphere()`보다 적은 수의 결과가 출력된 것 입니다. 경계에 걸치는 점이 5개 라고 예상해 볼 수 있겠네요.
 
`ST_Distance_Sphere()`가 더 정확한 결과를 보이지만 거리의 정확도가 크게 중요하지 않다면 공간 인덱스를 사용할 수 있는 `ST_Buffer()`와 `ST_Contains()`를 이용해 성능을 향상시킬 수 있을 것 같습니다.