수천만 건 공공데이터 반경 조회를 밀리초 단위로 단축하는 MySQL 8.0 공간 인덱스(Spatial Index R-Tree)와 MBR 최적화

전국 병의원·약국 현황, 상가 상권 정보, 부동산 실거래가 등 위치 기반 공공데이터를 서비스 백엔드에 통합할 때 가장 빈번하게 발생하는 병목은 ‘사용자 현재 위치 기준 반경 N km 이내 장소 검색’ 쿼리입니다.

수천만 건의 좌표 데이터에 대해 ST_Distance_Sphere(POINT(lon, lat), POINT(user_lon, user_lat)) <= 3000 조건을 WHERE 절에 단순 적용하면, MySQL 옵티마이저는 모든 행에 대해 삼각함수 연산을 반복 수행하는 풀 테이블 스캔(Full Table Scan)을 발생시킵니다. 데이터가 수백만 건만 넘어도 응답 시간이 수 초대로 급증하여 데이터베이스 CPU가 고갈됩니다. 본 글에서는 2차원 공간 계층 구조를 색인하는 MySQL 8.0 R-Tree 공간 인덱스(Spatial Index)의 동작 원리와 최소 경계 사각형(MBR, Minimum Bounding Rectangle)을 활용한 2단계 반경 필터링 아키텍처를 분석합니다.

1. B-Tree 인덱스의 한계와 R-Tree의 공간 분할 원리

• MBR(Minimum Bounding Rectangle) 계층화: R-Tree(Rectangle Tree)는 공간 객체들을 감싸는 최소 경계 사각형들을 계층적 트리 구조로 그룹화합니다.
• 공간 배제(Spatial Pruning): 쿼리 검색 영역과 교차하지 않는 상위 MBR 노드는 하위 리프 노드를 전혀 탐색하지 않고 한 번에 배제하므로 $O(\\log N)$의 고속 탐색이 가능합니다.
• InnoDB SRID 4326 지원: MySQL 8.0부터 InnoDB 스토리지 엔진에서 WGS84 좌표계(SRID 4326)를 지원하여 지구 곡률을 반영한 지리적 공간 인덱스를 직접 생성할 수 있습니다.

2. 공간 테이블 DDL 및 R-Tree 공간 인덱스 설계

공간 인덱스를 활용하기 위해서는 위경도를 DECIMAL이나 FLOAT가 아닌 POINT NOT NULL SRID 4326 타입 컬럼으로 선언하고 SPATIAL INDEX를 생성해야 합니다.

-- 1. 요양기관/공공데이터 공간 테이블 DDL
CREATE TABLE public_facilities (
    facility_id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    category VARCHAR(50) NOT NULL,
    latitude DECIMAL(10, 7) NOT NULL,
    longitude DECIMAL(11, 7) NOT NULL,
    -- POINT(경도, 위도) 공간 지오메트리 컬럼 (SRID 4326: WGS84 표준)
    location POINT NOT NULL SRID 4326,
    PRIMARY KEY (facility_id),
    -- R-Tree 공간 인덱스 생성
    SPATIAL INDEX idx_spatial_location (location)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. 위경도 데이터를 POINT 지오메트리로 변환 삽입
-- 주의: MySQL 8.0 SRID 4326에서 POINT 인자는 (경도, 위도) 순서입니다.
INSERT INTO public_facilities (name, category, latitude, longitude, location)
VALUES (
    '인천중앙병원',
    '종합병원',
    37.4562557,
    126.7052062,
    ST_SRID(POINT(126.7052062, 37.4562557), 4326)
);

3. 2단계 반경 검색 최적화: MBRContains + ST_Distance_Sphere

ST_Distance_Sphere 함수는 공간 인덱스를 직접 타지 못합니다. 따라서 1단계에서 MBR 사각형으로 후보군을 인덱스 스캔(Spatial Range Scan)으로 99% 걸러낸 뒤, 2단계에서 원형 정밀 거리(Spherical Distance)를 계산하는 파이프라인을 구축해야 합니다.

-- 사용자 위치(126.7052, 37.4562) 기준 반경 3km(3,000m) 이내 고속 조회
-- 위도 1도 ≈ 111.32km, 경도 1도 ≈ 88.80km (위도 37도 기준)
SET @user_lon = 126.7052062;
SET @user_lat = 37.4562557;
SET @radius_m = 3000;

-- 1단계: 반경을 사각형 MBR 경계(Delta)로 변환
SET @d_lat = @radius_m / 111320.0;
SET @d_lon = @radius_m / (111320.0 * COS(RADIANS(@user_lat)));

-- MBR 바운딩 박스 생성 (Polygon)
SET @mbr_box = ST_SRID(ST_GeomFromText(CONCAT(
    'POLYGON((',
    @user_lon - @d_lon, ' ', @user_lat - @d_lat, ',',
    @user_lon + @d_lon, ' ', @user_lat - @d_lat, ',',
    @user_lon + @d_lon, ' ', @user_lat + @d_lat, ',',
    @user_lon - @d_lon, ' ', @user_lat + @d_lat, ',',
    @user_lon - @d_lon, ' ', @user_lat - @d_lat,
    '))'
)), 4326);

-- 2단계: MBR 인덱스 필터링 + 정밀 구면 거리 계산 복합 쿼리
SELECT 
    facility_id,
    name,
    category,
    ST_Distance_Sphere(location, ST_SRID(POINT(@user_lon, @user_lat), 4326)) AS distance_meter
FROM public_facilities
WHERE 
    -- 1차 MBR 필터링: R-Tree 공간 인덱스를 활용해 후보군을 즉시 압축 (Index Scan)
    MBRContains(@mbr_box, location)
    -- 2차 원형 정밀 필터링: 사각형 모서리의 외곽 오차 제거
    AND ST_Distance_Sphere(location, ST_SRID(POINT(@user_lon, @user_lat), 4326)) <= @radius_m
ORDER BY distance_meter ASC
LIMIT 50;

4. 결론 및 도입 성과

수천만 건의 위치 기반 공공데이터 조회 환경에서 단순 거리 계산 함수에 의존하면 데이터베이스 전역 잠금과 CPU 병목을 피할 수 없습니다. POINT SRID 4326 타입과 SPATIAL INDEX를 선언하고 MBR 바운딩 박스를 활용한 2단계 필터링 구조를 적용하면, 쿼리 응답 시간을 수 초에서 10ms 미만으로 단축하여 대규모 트래픽 속에서도 안정적인 위치 기반 서비스를 제공할 수 있습니다.

출처 및 1차 공식 기술 문서

• MySQL 8.0 Reference Manual — Spatial Index Optimization
• MySQL 8.0 Reference Manual — Creating Spatial Indexes in InnoDB
• MySQL 8.0 Reference Manual — Spatial Relation Functions That Use Minimum Bounding Rectangles (MBR)