[MySQL] 가까운 순 조회시 공간 인덱스 사용

배달 서비스에 대한 클론코딩 프로젝트를 진행하면서, 음식점을 가까운 순으로 조회하는 기능을 개발하였습니다. 해당 과정에서 공간 인덱스를 사용하기 전후의 부하 테스트 결과를 정리하였습니다.

배경

프로젝트에서, 가게 목록 조회 기능을 개발하면서 나와 가까운 순으로 정렬하는 기능을 제공하였습니다. 나와 가게의 거리는 요청하는 회원에 따라 달라지기에 캐싱에 어려움이 있었습니다. 따라서 부하테스트의 대상으로 결정하고 테스트를 진행하였습니다.

실제 AWS 배포한 서버의 경우 4만 개 정도의 가게를 등록하였는데, 부하테스트 중에는 공공 데이터인 전국음식점일반데이터를 이용하여 약 66만 행으로 데이터를 늘려 테스트를 진행하였습니다. 검색으로 나오기론 배달의 민족에 등록된 가맹점 수가 30만 개 이상이라는데 그 두 배 정도라 볼 수 있습니다.

아래에서 공간 인덱스를 사용하는 방법 외에도, 광역시/도나 시/군/구를 하나의 칼럼으로 저장해놓고 인덱스로 활용하는 방법도 생각하였으나, 사용하지는 않았습니다.

인덱스 사용 이전

실행 쿼리

가까운 순 가게 목록 조회에서 사용하는 쿼리로 ST_Distance_Sphere 함수를 통해 두 좌표 사이 거리를 계산합니다.

SELECT * FROM store
WHERE store_category_id = 1
AND is_deleted = 0
AND ST_Distance_Sphere(location, ST_GeomFromText('POINT(36.633042651860364 127.45836290082639)', 4326)) < 5000
ORDER BY ST_Distance_Sphere(location, ST_GeomFromText('POINT(36.633042651860364 127.45836290082639)', 4326))
LIMIT 20

실행 계획

실행 계획을 보면 store_category_id 와 is_deleted 에 대한 인덱스만 사용되고, 그 결과 66만개 행 중 16만 3천개의 행이 조회됩니다.

index_merge 타입, 163395 rows 스캔

부하테스트 (300vu, 60s)

부하테스트의 경우 K6를 이용하여 진행하였습니다.

300개의 virtual user로 60초 간 부하테스트 결과, 581개 요청 중 120개로, 20.65%의 요청만 성공하고 80% 가 실패하였습니다.

█ TOTAL RESULTS

  checks_total.......................: 581    6.188773/s
  checks_succeeded...................: 20.65% 120 out of 581
  checks_failed......................: 79.34% 461 out of 581

  ✗ status is 200
    ↳  20% — ✓ 120 / ✗ 461

  HTTP
  http_req_duration..................: avg=40.45s min=7.52s med=31.61s max=1m2s   p(90)=1m1s   p(95)=1m1s
    { expected_response:true }.......: avg=36.75s min=7.52s med=38.01s max=59.82s p(90)=55.01s p(95)=59.7s
  http_req_failed....................: 79.34% 461 out of 581
  http_reqs..........................: 581    6.188773/s

  EXECUTION
  iteration_duration.................: avg=39.97s min=8.57s med=32.03s max=1m1s   p(90)=59.62s p(95)=59.66s
  iterations.........................: 571    6.082254/s
  vus................................: 45     min=45   max=300
  vus_max............................: 300    min=300  max=300

  NETWORK
  data_received......................: 440 kB 4.7 kB/s
  data_sent..........................: 80 kB  846 B/s

실패 원인을 분석한 결과, HikariCP 커넥션 풀이 고갈되어 타임아웃이 발생한 것으로 확인되었습니다. 이는 쿼리 실행 시간이 과도하게 길어진 데에 기인한 것으로 추정됩니다.

Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30008ms (total=10, active=10, idle=0, waiting=25)

이에 대한 해결 방법은 되지 않았지만, 커넥션 풀의 최대 커넥션의 수를 늘려보면 결과가 달라질까 해보았는데요? 코어 수의 2~4배로 설정하는 걸 추천한다 하여 24개와 36개로 늘려 테스트해본 결과, 재미있게도 오히려 성공률이 각각 17%, 14%로 감소하였습니다.

공간 인덱스

MySQL은 좌표 같은 공간 검색을 위해서 SPATIAL INDEX 를 제공합니다. MySQL에서 옛날에는 R-tree를 사용하여 공간 데이터를 저장했는데, 5.7버전 이후 부터는 공간 데이터를 정렬 가능한 값(MBR)으로 변환하여 B-tree에 저장한다고 합니다. 정렬된 값에 대해서 범위 조건 검색에 대해 인덱스 기능을 제공합니다.

ALTER TABLE store ADD SPATIAL INDEX idx_store_location (location);

새 쿼리

인덱스 추가 이후 마법처럼 조회가 빨라질 줄 알았지만, 이전에 사용했던 ST_Distance_Sphere 함수는 타원 위에서 점과 점 사이 거리를 계산하는 함수로 공간 인덱스가 사용되지 않는다 합니다.

특정 모양의 Polygon을 만들고, 그 안에 좌표가 포함되는가 확인할 때 공간 인덱스를 활용할 수 있었습니다.

요청을 보낸 사용자로 부터 5km 정사각형 내부의 가게만 거리를 계산한다. 타원 위의 좌표이기에 엄밀히 정사각형은 아닐 것이다.

따라서 기존 쿼리의 WHERE 조건절에 한쪽 변이 약 5km의 정사각형을 만들어 필터링한 후 남은 좌표에 대해 거리를 계산 및 정렬하도록 변경하였습니다. 가까운 순 조회의 경우 근처의 가게만 계산하면 되기에 효율적이었습니다.

SELECT *,
  ST_Distance_Sphere(location, ST_GeomFromText('POINT(36.633 127.458)', 4326)) AS distance
FROM store
WHERE
  MBRContains(
    ST_SRID(
      ST_GeomFromText('POLYGON((
        127.413 36.588,
        127.503 36.588,
        127.503 36.678,
        127.413 36.678,
        127.413 36.588
      ))'),
      4326
    ),
    location
  )
  AND store_category_id = 1
  AND is_deleted = 0
HAVING distance < 5000
ORDER BY location <=> ST_GeomFromText('POINT(36.633 127.458)', 4326)
LIMIT 20;

실행 계획

그 결과 실행 계획에서 공간 인덱스(idx_store_location)가 사용되는 걸 확인할 수 있었습니다.

idx_store_location 사용됨, range 타입, 376 rows 스캔

부하테스트 (300vu, 60s)

동일한 조건으로 부하테스트 결과 100% 성공하였습니다.

█ TOTAL RESULTS

  checks_total.......................: 3401   49.668701/s
  checks_succeeded...................: 100.00% 3401 out of 3401
  checks_failed......................: 0.00%   0 out of 3401

  ✓ status is 200

  HTTP
  http_req_duration..................: avg=4.72s min=854.6ms med=4.66s max=9.85s p(90)=5.74s p(95)=6.13s
    { expected_response:true }.......: avg=4.72s min=854.6ms med=4.66s max=9.85s p(90)=5.74s p(95)=6.13s
  http_req_failed....................: 0.00% 0 out of 3401
  http_reqs..........................: 3401   49.668701/s

  EXECUTION
  iteration_duration.................: avg=5.54s min=1.85s med=5.6s max=9.78s p(90)=6.01s p(95)=6.09s
  iterations.........................: 3401   49.668701/s
  vus................................: 49     min=49   max=300
  vus_max............................: 300    min=300 max=300

  NETWORK
  data_received......................: 5.6 MB 82 kB/s
  data_sent..........................: 439 kB 6.4 kB/s

 

다음 글

[MySQL] 공간 인덱스는 충분히 빠를까?