MySQL 8.0.12 이후의 Spatial Index

 

❐ Description

---

과거 회사에서 MySQL 5.7을 사용해서 Explain Analyze를 사용할 수 없다.

그래서 docker mysql 버전을 8.4.3으로 업그레이드 해줬는데, 여기서 예기치 않은 오류를 만나게됐다.

 

 

 

 

 

❐ 문제

---

쿼리 분석을 Explain Analyze 사용을 위해 도커 MySQL 버전을 5.7 ➡️ 8.4로 변경해주었다.

기존에는 문제없던 인덱스 생성에서 다음과 같이, Too many key parts specified; max 1 parts allowed 라는

경고가 발생했으며, 결과적으로 인덱스 생성이 정상적으로 이루어지지 않았다.

 

 

 

 

 

❐ 변경사항

---

MySQL 8.0.12 이후에는 Spatial Indexes 관해서 아래와 같은 조건이 추가되었다.

 

1. SPATIAL 키워드는 MySQL 8.0.12부터 선택적(Optional)이다.

MySQL 8.0.12 버전부터 공간 컬럼에 인덱스를 생성할 때 SPATIAL 키워드를 명시하지 않아도 된다.

공간 데이터 타입(GEOMETRY, POINT, LINESTRING 등)에 대해 자동으로 SPATIAL 인덱스를 생성하도록 처리한다. 

-- MySQL 8.0.12 이전
CREATE TABLE geom_table (
    id INT PRIMARY KEY,
    geom GEOMETRY NOT NULL,
    SPATIAL INDEX(geom) -- SPATIAL 키워드 명시
);

-- MySQL 8.0.12 이후
CREATE TABLE geom_table (
    id INT PRIMARY KEY,
    geom GEOMETRY NOT NULL,
    INDEX(geom) -- SPATIAL 키워드 생략 가능
);

 

 

 

2. 단일(Single) 공간 컬럼에만 SPATIAL 인덱스 적용 가능

하나의 공간 데이터 타입 컬럼에만 인덱스를 생성할 수 있다. 따라서 여러 공간 컬럼을 조합하여

하나의 복합 SPATIAL 인덱스를 생성할 수는 없다.

CREATE TABLE single_geom (
    id INT PRIMARY KEY,
    geom GEOMETRY NOT NULL,
    SPATIAL INDEX(geom) -- 단일 공간 컬럼
);

CREATE TABLE multi_geom (
    id INT PRIMARY KEY,
    geom1 GEOMETRY NOT NULL,
    geom2 GEOMETRY NOT NULL,
    SPATIAL INDEX(geom1, geom2) -- 🔥다중 공간 컬럼은 불가
);

 

💡 참고
Spatial Index를 사용할 컬럼은 NOT NULL을 만족해야 한다.

 

 

 

 

 

❐ 의문점

---

1. MySQL 5.7에서는 복합 인덱스가 생성됐었던 기억이 있는데...

MySQL 5.7을 쓸 때는 아래와 같이 [기본 타입 + 공간 타입] 복합인덱스를 생성할 수 있었다.

ALTER TABLE zone_road_address
ADD INDEX IDX_ZONE_ID_LOCATION(zone_id, location);


show index from zone_road_address;
+-----------------+----------+--------------------+------------+-----------+---+----------+
|Table            |Non_unique|Key_name            |Seq_in_index|Column_name|...|Index_type|
+-----------------+----------+--------------------+------------+-----------+---+----------+
|zone_road_address|1         |IDX_ZONE_ID_LOCATION|1           |zone_id    |...|BTREE     |
|zone_road_address|1         |IDX_ZONE_ID_LOCATION|2           |location   |...|BTREE     |
+-----------------+----------+--------------------+------------+-----------+---+----------+

 

(과거에는 생각하지 못했지만) 다시 확인해보니 `공간 index`와 `일반 index`를 바탕으로 복합 인덱스를

생성할 경우 B-TREE를 사용하는 인덱스를 생성하게 된다.

 

그리고 공식문서에는 다음과 같이 작성되어 있다. 

SPATIAL INDEX creates an R-tree index.

 

보통의 인덱스는 B-Tree 인덱스를 만드는데 [공간 인덱스]는 R-Tree 인덱스를 만든다고 한다. 

그렇다면 이 부분에서 차이가 있을 것이라고 생각한다. 어떤 차이가 있는지 검색해봤다.

 

 

2. R-Tree와 B-Tree의 차이

자세한 내용은 [자료구조] 카테고리에서 작성하는 것이 맞는 것 같아서 여기서는 간단하게 정리했다.

 

 

 

 

 

❐ SRID (Spatial Reference System Identifier)

---

1. SRID란?

공간 데이터를 다룰 때 사용되는 좌표 체계(Spatial Reference System)를 식별하기 위한 고유 식별자이다.

SRID는 데이터의 좌표 값이 어떤 기준으로 정의되었는지 알려주며, 공간 연산 및 변환 작업에서 중요한 역할을 한다.

 

대표적인 SRID 값은 다음과 같다.

• SRID 4326 - WGS 84 (전 세계적으로 가장 널리 사용되는 좌표 체계)
• SRID - Google Maps 및 OpenStreetMap에서 사용하는 Web Mercator 투영법
• SRID 5179 - 한국에서 사용하는 TM(Korea 2000 Transverse Mercator) 좌표 체계

 

 

2. 필수로 정의해줘야 할까?

MySQL에서는 기본적으로 [SRID 0]을 사용한다. 다음 문서에서 관련된 정보를 확인할 수 있다.

검색해보니 문맥(context)에 따라서 결정하면 되는 것으로 보인다. 하지만 SRID를 설정하기로 결정했다면, 

공간 연산(ST_Distance, ST_Contains, ST_Within 등)을 수행할 때, 서로 다른 SRID를 가진 데이터는

비교하거나 연산할 수 없기 때문에 이 부분만 주의하면 되는 것 같다.

 

 

 

 

 

❐ 참고 문헌

---

• MySQL 5.7 documents - Spatial Indexes
• MySQL 8.0 documents - Spatial Indexes
• MySQL 8.0 documents - Spatial Data Types
• MySQL 8.0 documents - Rtree 관련
• MySQL 8.0 documents - SRID

 

 

 

 

 

---