지도 요청이 쌓이자 커넥션 풀이 말랐다 — lat/lng B-tree에서 MySQL SPATIAL 인덱스로

지도 기반 서비스에서 지도를 빠르게 휘저으면(줌·이동을 연속으로 하면) 간헐적으로 "네트워크 오류" 팝업이 뜬다는 QA 리포트를 받았다. 한두 번 요청할 땐 멀쩡했다 — 문제는 짧은 시간에 요청이 쌓일 때였다. 표면은 프론트의 네트워크 오류였지만, 끝까지 따라가 보니 원인은 공간 쿼리를 lat/lng B-tree로 풀던 것 + 요청이 동시에 누적될 때의 커넥션 풀 고갈이었다. 최종 해결은 MySQL POINT + SPATIAL INDEX. 그 과정에서 만난 함정들을 정리한다.

1. 증상 — 느린 쿼리가 네트워크 오류로 둔갑하는 과정

중요한 건 한두 번 요청할 땐 멀쩡했다는 점이다. 느린 쿼리 하나가 곧장 장애를 내는 게 아니다. 지도는 보이는 영역(bounding box)이 바뀔 때마다 조회 API를 호출하는데, 빠르게 줌/이동하면 이 호출이 짧은 시간에 누적된다. 각 요청이 느린 쿼리(최대 6초)로 커넥션을 오래 잡으니, 요청이 쌓이는 속도가 처리 속도를 넘어서는 순간 동시 점유 커넥션 수가 풀 크기를 초과한다. 그래서 벌어진 일:

HikariPool-x - Connection is not available, request timed out after 30000ms
(total=4, active=4, idle=0, waiting=N)

느린 쿼리(최대 6초)가 커넥션을 오래 점유
   + 빠른 줌/이동으로 짧은 시간에 요청이 누적
   → 동시 점유 커넥션 수 > 풀 크기(4)
   → 풀 고갈, 대기 요청이 30초 타임아웃
   → 프론트 "네트워크 오류" 팝업

교훈 0: 단일 요청 지연이 아니라 "느린 요청 × 동시성" 이 풀을 말린다. 한두 개는 버텨도 짧은 시간에 쌓이면 무너진다. 프론트의 "네트워크 오류"는 그 말단 증상인 경우가 많다 — 표면 메시지를 믿지 말고 캐스케이드를 거꾸로 따라가야 한다.

2. 1차 대응 — HikariCP 설정 버그 (증상 완화)

먼저 풀 쪽을 들여다봤더니 설정 버그가 있었다. maxLifetime/keepaliveTime을 선언만 하고 HikariConfig에 적용하지 않고 있었고, 기본값도 30초로 너무 짧았다.

// ❌ 선언은 있는데 HikariConfig에 set 호출이 빠져 있었다
private long maxLifeTime = 30000; // 게다가 30초로 너무 짧음

// ✅ 수정
hikariConfig.setMaxLifetime(property.getMaxLifeTime());     // 30분
hikariConfig.setKeepaliveTime(property.getKeepaliveTime()); // 5분

이걸 고치니 증상은 완화됐다. 하지만 근본 원인(느린 쿼리가 커넥션을 오래 잡는 것)은 그대로였다. 풀 설정은 진통제다.

3. 근본 원인 — 2D 범위 쿼리를 B-tree로 풀면 안 된다

문제의 쿼리는 이랬다.

SELECT ...
FROM places p
LEFT JOIN place_stats s ON p.id = s.place_id
WHERE p.center_lat BETWEEN :latSW AND :latNE   -- 위도 범위
  AND p.center_lng BETWEEN :lngSW AND :lngNE   -- 경도 범위
  AND p.type IN (:types)

(center_lat, center_lng) 복합 B-tree로는 2차원 영역 검색이 효율적이지 않다. 위도로 먼저 좁혀도 그 위도 범위에 속한 모든 경도 데이터를 스캔해야 하기 때문이다. 영역에 따라 수만~수십만 row를 훑었고, 쿼리가 6초까지 늘어졌다. 데이터가 수백만 건(약 540만 행) 규모면 이건 시한폭탄이다.

B-tree는 1차원 정렬 자료구조라 "사각형 영역 안에 있는 점"을 찾는 데 본질적으로 불리하다. 이건 R-tree(공간 인덱스) 의 영역이다.

4. 해결 — MySQL SPATIAL 인덱스

POINT 컬럼 + SPATIAL INDEX(R-tree) + MBRWithin/MBRContains로 전환했다.

-- 영역 안의 점을 R-tree로 검색
WHERE MBRContains(
  ST_GeomFromText('POLYGON((...bbox...))', 0),
  p.location
)

결과는 극적이었다. EXPLAIN 기준:

type=range  key=idx_location  rows≈52,000   -- 540만 → 5만 row로 축소

쿼리가 6초 → 수십 ms로 떨어졌다. 커넥션 점유 시간이 짧아지니 풀 고갈도 근본적으로 사라졌다.

5. 3번의 삽질 — SPATIAL 인덱스가 안 타던 이유들

여기서 진짜 이야기가 시작된다. SPATIAL INDEX를 만들었는데도 옵티마이저가 계속 풀스캔(type: ALL) 으로 돌았다. 세 번 헛발질했다.

① SRID 4326 + 축(axis) 순서 불일치

처음엔 SRID 4326(WGS84)으로 만들었다. 그런데 4326은 위도-우선(lat-first) 규약이라, POINT(lng, lat)로 넣은 데이터와 축이 안 맞아 인덱스가 무시됐다(풀스캔 540만 row, 응답 90초). 4326에 맞게 lat-first로 다시 넣으려니 latitude 126.97 out of range(경도값이 위도 자리에) 에러.

잠깐 — 좌표계 선택. 4326의 lat-first 축순서가 위 함정을 만든다. 하지만 더 본질적으로, 우리 연산은 측지 거리가 아니라 위경도 bbox 포함 판정이라 4326의 타원체 정밀도가 결과를 바꾸지 않는다 → 비용만 든다. 그래서 더 단순하고 함정 적은 평면 SRID 0 이 합리적 선택이다. (거리/반경이 필요해지면 그때 4326/투영 CRS로 — 근거는 §6 "좌표계 선택")

② 더티 데이터

lat 컬럼에 경도값(126.x)이 섞여 들어간 row가 있었다. SRID 0 평면 좌표계로 전환. 그런데도 여전히 type: ALL.

③ `/!80003 SRID 0 /` 주석 누락 ← 진짜 원인

이게 핵심이었다. 컬럼 정의에 SRID 속성이 명시돼 있지 않으면, 옵티마이저는 SPATIAL 인덱스를 못 쓴다. FORCE INDEX조차 무력화된다.

-- ❌ SRID 속성 없음 → 인덱스 무용지물
location POINT GENERATED ALWAYS AS (...) STORED NOT NULL

-- ✅ /*!80003 SRID 0 */ 가 있어야 옵티마이저가 인덱스를 인식
location POINT GENERATED ALWAYS AS (...) STORED NOT NULL /*!80003 SRID 0 */

MySQL 8.0 매뉴얼: "The optimizer can use SPATIAL indexes on SRID-restricted columns." — 컬럼에 SRID 제약이 박혀 있어야 한다.

사실 이 제약 자체는 알고 있었다. 다만 SRID 설정은 1년에 한 번 있을까 말까 한 작업이라(예전 환경에선 이미 다 세팅돼 있어 직접 만질 일이 없었다), 정작 내 손으로 하려니 너무 오랜만이라 머릿속에서 휘발돼 있었다. "아는 것"과 "그 순간 떠올리는 것"은 다르다 — 자주 안 쓰는 제약일수록 더 그렇다. 드물게 하는 셋업은 체크리스트나 스니펫으로 외부화해두는 편이 안전하다.

그리고 이건 나만의 실수가 아니다. MySQL 8.0이 SRID 제약 없는 컬럼의 SPATIAL 인덱스를 invalid로 마크하고 옵티마이저가 절대 안 쓰게 한 건 WL#10439로 의도된 설계다 — 인덱스가 평면(SRID 0)인지 지리(4326)인지 알아야 bounding box 계산이 맞기 때문이다. 그래서 FORCE INDEX조차 무력하다(인덱스가 invalid라서). 다만 DX는 정당하게 비판받는다. 5.7→8.0 업그레이드 때 기존 인덱스가 경고 없이 조용히 무효화돼 "잘 되던 게 갑자기 느려지는" 사고가 워낙 흔해, MySQL이 업그레이드 안내 블로그를 따로 낼 정도였다. (정밀히는, SRID 미지정 컬럼에 새 인덱스를 만들 땐 "안 쓰일 것"이라는 경고가 뜬다. 그러나 업그레이드·쿼리 시점엔 침묵하고 FORCE INDEX도 무력한 비일관성이 사람을 잡는다.)

ALTER는 3단계로 분리

한 ALTER에 SRID 변경 + 인덱스를 묶으면 "인덱스가 살아있는 채로 SRID를 못 바꾼다"는 에러가 난다. DROP → MODIFY → ADD 3단계로 쪼개야 한다.

ALTER TABLE places DROP INDEX idx_location;
ALTER TABLE places
  MODIFY COLUMN location POINT
    GENERATED ALWAYS AS (ST_SRID(POINT(COALESCE(center_lng,0), COALESCE(center_lat,0)), 0))
    STORED NOT NULL /*!80003 SRID 0 */;
ALTER TABLE places ADD SPATIAL INDEX idx_location (location);
ANALYZE TABLE places;

6. 좌표계·좌표 순서 — 실전 레퍼런스

축 순서: SRID 0 vs 4326 (제일 많이 헷갈리는 부분)

같은 POINT(a b) 라도 SRID에 따라 a·b가 경도냐 위도냐가 뒤바뀐다. 공간 데이터에서 가장 많이 밟는 지뢰다.

	SRID 0 (평면/Cartesian)	SRID 4326 (WGS84 지리)
의미	단순 X·Y 평면 (지리 의미 없음)	실제 지구 타원체상의 위경도
축 순서	X, Y — 관례상 X=경도, Y=위도	위도 먼저, 경도 나중 (EPSG 4326 공식 축순서 = MySQL 기본)
한국 좌표 입력	`POINT(경도, 위도)` → `POINT(127.0, 37.5)`	`POINT(위도, 경도)` → `POINT(37.5, 127.0)`
범위 검증	없음	위도 −90~~90 / 경도 −180~~180, 벗어나면 에러
`ST_Distance`	데카르트 거리(단위 무의미)	미터(실거리)

핵심 함정: SRID 4326은 위도가 먼저다. 한국 좌표를 경도 먼저로 POINT(127.0 37.5)처럼 넣으면 → 첫 값 127을 위도로 해석 → Latitude out of range 에러. (위 삽질 ①이 바로 이것)

MySQL 실전 메모

POINT(x, y) 함수는 SRID 0 지오메트리를 만든다. 4326으로 쓰려면 ST_SRID(POINT(...), 4326) 또는 ST_GeomFromText('POINT(...)', 4326).
4326에서 굳이 경도-먼저로 쓰고 싶으면 ST_GeomFromText(wkt, 4326, 'axis-order=long-lat') 옵션으로 축순서를 뒤집을 수 있다.
헷갈리면 컴포넌트를 명시적으로: 지리 점은 ST_Latitude(g) / ST_Longitude(g)로 안전하게 꺼낸다.

선택의 핵심은 "데이터가 WGS84냐"가 아니라 "이 쿼리가 측지 거리를 쓰냐"다:

뷰포트 bbox·영역 포함만 → SRID 0 + POINT(경도, 위도) (충분·단순·빠름, 축순서 함정 없음). MBR* 함수는 bounding-box 비교라 평면/지리 결과가 같다.
진짜 거리·반경("500m 내")·면적 → 4326(미터) 또는 투영 평면 CRS.

한 가지 더 — "평면이면 거리계산 틀리지 않냐"는 반박엔: 맞다, raw 위경도를 평면 거리계산하면 왜곡된다(한국 37.5°N에서 경도 1°≈88km vs 위도 1°≈111km). 거리까지 정확하게 계산하려면 raw lat/lng가 아니라 투영 좌표계(EPSG:5179/5186 Korea TM, 또는 Web Mercator 3857)를 써야 한다. 거리계산을 안 하면(YAGNI) SRID 0로 충분하고, 거리 기능이 생기면 그때 전환한다. ("익숙해서"가 아니라 연산 성격에 맞춘 선택이다.)

🐘 여담 — "PostGIS가 공간처리에 더 낫다"는 건 4326 때문이 아니다. PostGIS의 강점은 구면좌표계를 표준으로 강제해서가 아니라, geometry(평면)·geography(측지) 타입 이원화 + 임의 CRS 재투영(ST_Transform) + 풍부한 함수 + GiST 인덱스다. PostGIS도 bbox 조회는 평면 geometry로 한다 — 즉 위 SRID 0 선택과 같은 철학이고, 측지(geography)는 "진짜 거리"가 필요할 때 꺼내 쓴다. MySQL은 타입이 하나라 SRID로 동작을 추론하고, 측지·재투영 지원이 제한적인 게 차이.

외부 지도 API는 거의 WGS84(4326) — 단 순서가 제각각

지오코딩·POI·길찾기로 받은 좌표를 POINT 컬럼에 적재할 때 이 함정이 실전에서 터진다. 응답은 대부분 WGS84인데, lat/lng 순서가 서비스마다 다르다:

서비스	좌표계	순서 (대표 API)
Google (Geocoding)	WGS84	위도,경도 (`latlng=lat,lng`)
Naver (NCP Geocoding)	WGS84	경도,위도 (`lng,lat`)
Kakao (Local REST)	WGS84 기본(+TM/WTM 등)	경도,위도 (`x=경도, y=위도`)
Tmap (SK Open API)	WGS84GEO	경도,위도 (`startX=경도, startY=위도`)

즉 Google만 위도 먼저, 국산 3사(Naver·Kakao·Tmap)는 경도 먼저(x/startX=경도). "WGS84니까 다 똑같겠지"가 아니라 API마다 순서를 확인해서 우리 POINT(경도, 위도) 적재 규약에 맞춰야 한다. 안 맞추면 위경도가 뒤집혀 엉뚱한 위치에 찍히거나(SRID 0) Latitude out of range로 터진다(4326).

7. 대용량 테이블 마이그레이션 팁

수백만 행 테이블에 POINT 컬럼 + SPATIAL INDEX를 안전하게 얹는 두 가지 전략:

일반 컬럼 방식 (앱에서 직접 수정/INSERT가 필요할 때): Nullable로 컬럼 추가 → PK 범위 청크 단위로 분할 UPDATE(540만 행 단일 UPDATE는 binlog/undo 누적 + 긴 락 위험) → NOT NULL + 인덱스. POINT(x, y)는 POINT(경도, 위도) 순서임에 주의.
STORED 생성 컬럼 방식 (lat/lng에 NULL 없고 읽기 전용 유도 컬럼이면): 단일 ALTER로 끝. 단 STORED 필수(VIRTUAL엔 공간 인덱스 못 만듦), 컬럼 SRID와 표현식 SRID 일치 필요.

8. 인덱스 하나가 끝이 아니었다 — 여러 축으로 막기

근본적으로 문제는 여러 축이 겹친 것이라, 각각 따로 막았다.

쿼리 효율 — SPATIAL 인덱스(위에서 다룸): geometry 필터를 R-tree로 싸게.
요청 빈도 — debounce(있었지만 prod엔 빠져 있었다): 사실 프론트엔 줌/이동 연속 요청을 묶는 debounce가 이미 구현돼 있었다. 그런데 배선을 깜빡했는지, 다른 버그 수정 중 롤백됐는지 — 어느 순간 로직에서 빠진 채 prod는 debounce 없이 돌고 있었다. 연속 요청이 그대로 쏟아지니 §1의 '누적/동시성'을 키운 원인 중 하나였고, 이 debounce를 다시 제대로 거는 것도 대응에 포함됐다.

교훈: 존재하는 코드 ≠ 동작하는 코드. "구현됨"이 아니라 "지금 prod에서 켜져 있음"을 확인해야 한다.
데이터 볼륨 — 넓은 줌은 프론트가 호출을 분기: 넓게 볼수록 bbox 안 건수가 급증한다 — 줌 레벨 14·대도시(서울) 기준 한 화면에 약 1만 건. 여기에 통계 테이블 LEFT JOIN까지 겹치면 무겁다. 그래서 프론트가 줌에 따라 호출을 분기한다: 줌14에선 개별 단지를 일정 개수로 cap(중심 반경 우선 노출), 더 줌아웃하면 개별 조회 대신 집계 마커(count) API를 부른다. (※ 백엔드 조회 API엔 줌 분기 로직이 없다 — 분기는 프론트의 API 라우팅이다.)

빠른 쿼리(인덱스) + 적은 요청(디바운스) + 줌별 호출 분기(프론트) 의 다축 방어다. 다만 솔직히 말하면, SPATIAL 인덱스는 geometry 필터만 싸게 했을 뿐 LEFT JOIN 볼륨의 구조적 정리(통계 통합)는 후속 과제로 남아 있다. "인덱스 하나로 끝"이 아니라는 게 핵심.

9. 기술 요약

느린 쿼리 → 커넥션 풀 고갈 → 네트워크 오류 캐스케이드. 풀 설정은 진통제, 쿼리 최적화가 근본.
2D 영역 검색은 B-tree가 아니라 SPATIAL 인덱스(R-tree) 의 일.
/*!80003 SRID 0 */ 없으면 FORCE INDEX도 무력 — 컬럼 SRID 속성 명시 필수.
4326은 lat-first 축순서가 함정이고, bbox 조회엔 측지 정밀도가 애초에 불필요 — SRID 0로 충분. (진짜 거리/반경/면적엔 4326·투영 CRS)
ALTER는 DROP → MODIFY → ADD 3단계.
해결은 한 축이 아니다 — 쿼리 효율(SPATIAL) + 요청 빈도(debounce) + 넓은 줌 볼륨(프론트가 집계 마커 API로 분기)을 함께 막았다. 단 LEFT JOIN 볼륨의 구조적 정리는 후속 과제(인덱스는 geometry 필터만 해결).

번외. 회고 — 전문성은 "데이터로 미리 보여줘야" 채택된다

이 글의 기술적 결론은 깔끔하지만, 개인적으로 더 크게 남은 건 따로 있다.

지도는 원래 lat/lng B-tree로 돌고 있던 레거시였다 — 내가 설계한 구조가 아니다. 위치 데이터는 내 전문 영역이라(이전 직장에서 4년간 위치 기반 기술을 다뤘다) 이 방식이 규모가 커지면 한계에 부딪힌다는 건 알고 있었지만, "지금은 충분하다"는 우선순위에 밀려 손대지 못한 채 운영됐다. 트래픽이 늘면서 정확히 그 지점에서 인시던트로 터졌다.

배운 건 "내가 맞았다"가 아니다. 도메인 전문성이 있어도, 그것만으로는 레거시의 우선순위를 바꾸지 못한다는 것이다. 우선순위를 움직이는 건 말이 아니라 수치다 — "지금은 괜찮지만 데이터가 N배가 되면 스캔이 이만큼 늘고 풀이 이렇게 고갈된다"를 인시던트가 터지기 전에 작은 벤치마크로 보여줬다면, 그 비용을 미리 막을 수 있었다. 다음엔 내 전문 영역일수록, 주장 대신 벤치마크 한 장을 먼저 들고 가려 한다.

누구의 잘잘못을 따지는 글이 아니다. 내가 더 잘할 수 있었던 부분에 대한 기록이고, 이번에 가장 또렷이 배운 건 "옳음"보다 "수치로 미리 보여주기"가 결정을 바꾼다는 것이다.