MySQL의 공간인덱스를 적용해보자!

예상 독자

• MySQL의 공간 타입(sptial type)을 사용하고 있는데 공간 인덱스를 적용해보고 싶은 분
• 공간인덱스가 적용이 안 되는 분

---

핵심 요약

• MySQL의 공간인덱스를 적용하기 위해 SRID는 하나로 통일되어 있나요? (ex. 4326)
• 공간 인덱스가 존재하더라도 옵티마이저의 선택을 받지 못하다면 풀스캔을 할 수 있습니다.

---

문제 상황

CareerBee에선 사용자의 현재 위치(위도, 경도) 기반으로 5KM 이내의 기업들을 조회하고 정보를 제공하고 있습니다.

 

이를 구현하기 위해 MySQL의 공감함수를 사용하고 있습니다. (아래 코드 참고)

private BooleanExpression inDistance(String point, Integer radius) {
    return radius != null
        ? Expressions.booleanTemplate(
        "ST_Distance_Sphere(ST_GeomFromText({0}, 4326), {1}) <= {2}",
        point, company.geoPoint, radius)
        : null;
}

이 쿼리도 잘 동작하지만, Full Table Scan이 발생한다는 점이 참 아쉽습니다.

 

따라서 조금더 나은 조회 성능을 위해 220개의 기업데이터와 함께 공간인덱스 테스트를 해보겠습니다.

SELECT *
FROM company
WHERE ST_Distance_Sphere(
        ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326),
        geo_point
      ) <= 1000;

위 쿼리를 3번 실행했을 때 약 350ms의 쿼리실행 속도를 보이고 있습니다.

 

EXPLAIN을 통해 실행계획 또한 파악해 보겠습니다.

EXPLAIN
SELECT *
FROM company
WHERE ST_Distance_Sphere(
        ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326),
        geo_point
      ) <= 1000;

실행 계획 결과를 정리해 보면 아래와 같습니다.

• type : ALL
  • Full Table Scan이 발생함을 알 수 있음
• possible_keys: null
  • 사용할 수 있는 인덱스 없음
• key: null
  • 실제 사용된 인덱스 없음

즉, 현재 인덱스가 사용되고 있지 않고 있으며, 평균 350ms의 쿼리속도를 가지고 있음을 알 수 있습니다.

---

공간 인덱스를 적용해 보자.

아래는 현재 특정 반경 내에 존재하는 기업을 조회하는 공간 쿼리(QueryDSL)입니다.

private BooleanExpression inDistance(String point, Integer radius) {
    return radius != null
        ? Expressions.booleanTemplate(
        "ST_Distance_Sphere(ST_GeomFromText({0}, 4326), {1}) <= {2}",
        point, company.geoPoint, radius)
        : null;
}

ST_Distance_Sphere() 함수는 Table Full Scan과 함께 두 지역 간의 거리 차이를 계산하게 되며, 공간 인덱스를 기대할 수 없습니다.

왜냐, MySQL은 MBR이라는 개념을 사용하여 인덱싱을 수행하게 되는데, ST_Distance()는 MBR의 포함관계를 이용하는 게 아닌, 단지 두 지점 사이의 거리를 계산해 내기 때문입니다.

*MBR(Minimum Bounding Rectangle) : 공간을 나타내는 공간의 가장 작은 크기의 사각형

 

아래는 공간인덱스를 적용하는 방식입니다. 다른 인덱스를 적용할 때와 같이 단순하게 적용할 수 있습니다.

ALTER TABLE company ADD SPATIAL INDEX idx_geo_point (geo_point);

인덱스가 적용됐는지 확인해볼까요?

SHOW INDEX FROM company;

잘 적용이 됐네요!

 

위에서 ST_Distance()를 사용하는 방식은 공간 인덱스를 활용할 수 없다고 했습니다. 따라서 반경 내 기업을 조회하는 쿼리를 바꿀 필요가 있습니다.

// 기존 코드
SELECT *
FROM company
WHERE ST_Distance_Sphere(
          ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326),
          geo_point) <= 1000;

// 변경 코드
SELECT *
FROM company
WHERE ST_CONTAINS(ST_BUFFER(ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326), 2000), geo_point);

쿼리 실행 전에 실행계획을 통해 인덱스가 적용됐는지 확인해 보겠습니다.

EXPLAIN
SELECT *
FROM company
WHERE ST_CONTAINS(ST_BUFFER(ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326), 2000), geo_point);

음? 인덱스가 적용이 안 됐습니다. 큰일입니다.

---

공간인덱스를 적용해 보자.

구글링을 통해 찾아보니 공간인덱스를 설정했음에도 Table Full Scan이 발생하는 요인이 몇 개 있다고 해서 점검해봤습니다.

1. SRID가 동일하지 않은 경우

EXPLAIN
SELECT *
FROM company
WHERE ST_CONTAINS(ST_BUFFER(ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326), 2000), geo_point);

SRID가 동일하지 않거나 적용되지 않으면 인덱스가 적용되지 않는다고 합니다.

 

따라서 아래 쿼리를 통해 확인해 보도록 하겠습니다.
(ST_GeomFromText()의 SRID 파라미터 값을 4326으로 설정했으니, geo_point의 SRID가 4326인지 확인해 보면 됩니다.)

SELECT distinct ST_SRID(geo_point)
FROM company;

결과는 보시다시피 4326으로 잘 설정되어 있는 것을 보아하니, SRID의 불일치 문제는 아닌 것으로 확인되었습니다.

2. 공간함수 오용

이는 ST_Distance()와 같이 공간인덱스 적용이 안 되는 함수를 사용했을 때, 발생할 수 있는 문제입니다.

 

이런 문제를 처음부터 알고, ST_Distance()를 사용하지 않고 ST_Contains + ST_Buffer 조합을 사용했으니 위의 문제도 아닙니다.

3. 공식문서대로 해보자.

많은 글들을 참고했을 때, ST_Contains + ST_Buffer를 사용해서 공간 인덱스를 적용하는 것을 배웠습니다.

하지만 계속 시도해도 안되니,, 공식 문서에 나오는 방식인 MBRContains()를 사용해 봤습니다.

The optimizer investigates whether available spatial indexes can be involved in the search for queries that use a function such as MBRContains() or MBRWithin() in the WHERE clause

EXPLAIN
SELECT *
FROM company
WHERE MBRContains(
  ST_GeomFromText(
    'POLYGON((
      37.39124430415324 127.09798761648364,
      37.39124430415324 127.11598761648364,
      37.40924430415324 127.11598761648364,
      37.40924430415324 127.09798761648364,
      37.39124430415324 127.09798761648364
    ))', 4326
  ),
  geo_point
);

MBRContains()는 POLYGON으로 다각형을 생성하고 그 안에 속하는지를 통해 데이터를 찾아내게 됩니다.

 

따라서, 예시로 1km 정사각형을 통해 테스트해봤습니다.

하지만 결과는 ,,,, 공간인덱스 적용이 안됩니다…

---

DDL의 문제?

시도한 방법이 모두 통하지 않아서 결국 다시 본질로 돌아갔습니다.

 

그리고 문뜩 떠올랐습니다. “과연 SRID가 잘 적용된 게 맞나?”

이를 확인하기 위해 DDL과 확인용 쿼리를 통해 확인해 봤습니다.

DDL

-- auto-generated definition
create table company
(   
        ...
    geo_point         point                                                                         not null,
        ...
)

create spatial index idx_geo_point
    on company (geo_point);

DDL을 보고 뭔가 기분이 싸했습니다. 기대했던 4326은 대체 어디 간 거죠?

 

저는 현재 SpringBoot + JPA를 사용하고 있으며, 엔티티에 다음과 같이 설정해 두고 자동으로 테이블을 생성하고 사용하고 있습니다.

...
@Entity
public class Company {
        ...

    @Column(columnDefinition = "POINT SRID 4326")
    private Point geoPoint;

        ...
    }
}

엔티티에서 columnDefinition을 통해 POINT 타입의 SRID를 4326으로 설정도 해줬고,
파이썬을 통한 기업 데이터 삽입에서도 아래와 같이 공간 데이터의 SRID를 4326으로 설정해서 삽입해서 넣어줬기 때문에 당연 데이터의 타입문제는 없다고 생각했습니다.

insert_query = """
            INSERT INTO company (
                ... geo_point ...
            )
            VALUES (
                ... ST_PointFromText(%s, 4326) ...
            )
            """

하지만 DDL에 4326이 없는 건 뭔가 크게 싸함을 느꼈고.. 쿼리를 통해 테이블 내의 칼럼 형식을 더 자세히 살펴봤습니다.

• 아래의 쿼리는 ST_GEOMETRY_COLUMNS 뷰를 조회해서 company 테이블의 geo_point 칼럼이 어떤 SRID를 사용하는지 확인합니다.

SELECT COLUMN_NAME, SRS_ID
FROM information_schema.ST_GEOMETRY_COLUMNS
WHERE TABLE_SCHEMA = 'careerbee'
  AND TABLE_NAME = 'company'
  AND COLUMN_NAME = 'geo_point';

결과는 다음과 같습니다.

왜 SRS_ID(Spatial Reference System Identifier)가 null이죠????

 

이게 문제라는 게 와닿는 순간이었습니다. 마음을 차분히 하고 SRID 재적용을 위해 아래 쿼리를 실행해 줍니다.

-- 1. 기존 인덱스 제거
DROP INDEX idx_geo_point ON company;

-- 2. 컬럼에 SRID 지정
ALTER TABLE company
    MODIFY geo_point POINT SRID 4326 NOT NULL;

-- 3. 공간 인덱스 다시 생성
CREATE SPATIAL INDEX idx_geo_point ON company (geo_point);

실행 후 다시 SRS_ID를 확인해 봅시다.

적용은 잘 됐습니다. 과연 문제가 해결 됐을까요?

 

실행계획을 통해 파악해 봅시다.

EXPLAIN
SELECT *
FROM company
WHERE ST_CONTAINS(ST_BUFFER(ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326), 1000), geo_point);

아직 TABLE FULL SCAN을 하고 있지만 possible_keys에 idx_geo_point가 들어갔습니다… 이론상 공간 인덱스가 적용될 수 있단 거죠!!

 

그리고 다시 한번 공식문서를 따라 MBRContains()를 활용해서 인덱스 적용을 기대해 봅시다.

EXPLAIN
SELECT id, name
FROM company
WHERE MBRContains(
        ST_Buffer(
                ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326),
                500
        ),
        geo_point
      )
  AND ST_Distance_Sphere(
              geo_point,
              ST_GeomFromText('POINT(37.40024430415324 127.10698761648364)', 4326)
      ) <= 3000;

아쉽게도 인덱스 적용이 안 됐습니다.

 

실행계획의 possible_keys에는 공간 인덱스(idx_geo_point)가 존재하지만, 옵티마이저는 인덱스를 사용하지 않고 Full Table Scan을 수행하고 있습니다.

 

이때, 옵티마이저가 인덱스를 선택하지 않는 이유를 고민하다가
”데이터 개수가 너무 적어서, 인덱스를 쓰는 것보단 Full Scan이 더 효율적이라고 판단한 건 아닐까?
하는 생각이 들었습니다.

 

이를 테스트해 보기 위해 3,000개의 임시 기업 데이터를 추가해 테스트를 진행했습니다.

아래는 같은 쿼리를 다시 실행한 결과입니다.

드디어 type이 range입니다. 공간인덱스가 드디어 적용 됐습니다.

 

이로써, 옵티마이저가 단순히 인덱스 존재 여부가 아니라 데이터 개수와 쿼리 조건을 분석해 인덱스 사용 여부를 결정한다는 점을 알게 됐습니다.

 

이를 검증하기 위해 이번에는 모든 기업이 포함될 정도의 넓은 범위로 쿼리를 재실행해보겠습니다.

인덱스가 적용이 안 됐습니다.

 

즉, 인덱스 사용 여부는 옵티마이저의 결정에 따라 달라질 수 있다는 사실을 알 수 있었습니다.

 

최종적으로 QueryDSL 코드는 아래와 같이 변했습니다.

private BooleanExpression inDistance(String pointWkt, Integer radiusMeters) {
        if (radiusMeters == null) return null;

        // 1) 1차 필터링 (데이터 프루닝): MBRContains(...) = 1  (반환 타입을 Integer로 고정)
        BooleanExpression inMbr =
            Expressions.numberTemplate(Integer.class,
                "MBRContains(" +
                    "  ST_Buffer(ST_GeomFromText({0}, 4326), {1}), " +
                    "  {2}" +
                    ")",
                pointWkt, radiusMeters, company.geoPoint
            ).eq(1);

        // 2) 2차 필터링 (정밀 필터): ST_Distance_Sphere(...) <= radius  (반환 타입을 Double로 고정)
        BooleanExpression inSphere =
            Expressions.numberTemplate(Double.class,
                "ST_Distance_Sphere({0}, ST_GeomFromText({1}, 4326))",
                company.geoPoint, pointWkt
            ).loe(radiusMeters.doubleValue());

        return inMbr.and(inSphere);
    }

---

결론

이 글의 목적대로 공간인덱스 적용은 성공했습니다.

 

하지만, 충분히 많은 데이터가 존재할 때만 옵티마이저에서 인덱스를 사용하고 있습니다. (현재 데이터 수에선 Table Full Scan 적용)

그래도 2차 필터링 방식으로 쿼리 로직이 변함에 따라 유의미한 개선은 이뤄졌습니다.

 

BEFORE - 평균 350ms

1. 모든 데이터를 대상으로 반경 안에 있는지 판단

AFTER - 평균 180ms

1. MBR_Contains()를 통해 러프하게 1차 필터링
2. 1차 필터링에서 나온 후보 기업들 대상으로 정확히 반경안에 있는지 2차 필터링

평균 응답 속도가 48.6% (350ms → 180ms) 개선되었습니다.

---

회고

이 글을 작성하기 위해 꽤 많은 시간이 소요됐습니다.

 

인덱스가 존재하더라도 옵티마이저가 인덱스를 사용하지 않을 거란 생각을 하지 못했거든요.

 

하지만, 혹시 나로 시작한 물음이 곧 정답이었고, 현재 데이터 수로는 인덱스를 사용할 수 없지만 데이터가 확장됐을 때는 공간 인덱스가 적용될 거란 확신이 생겼습니다.

 

개발에는 실버 불렛이 없듯이, 트러블슈팅 과정에서도 당연하다고 생각되는 것을 다시 한번 생각해 보면 좋을 거 같습니다.

 

이런 선택덕에 MySQL의 공간 타입부터 공간 인덱스 그리고 옵티마이저의 인덱스 적용여부 판단 조건까지 배울 수 있었습니다.

 

이 글의 마무리를 할 수 있을지 큰 걱정이 있었지만 그래도 해피엔딩(?)으로 마무리할 수 있어 다행입니다.

 

 

 

다음 글로는 MySQL의 옵티마이저가 어떤 조건을 통해 인덱스 여부를 판단하는지 살펴보도록 하겠습니다.

 

긴 글 읽어주셔서 감사합니다.

틀린 내용이 있거나 덧붙일 내용이 있다면 언제든 댓글을 통해 알려주시면 감사하겠습니다 :)