지피티가 만들어준 블로그 썸네일
이전글
개요
저번 글에서 작성했던 쿼리의 성능의 개선이 필요했기 때문에 쿼리의 latency와 실행계획을 살펴보고 이를 공간 인덱스를 통해 개선 해보자.
기존 쿼리 성능
기존의 쿼리는 다음과 같다.
SET @point = ST_GeomFromText(CONCAT('LINESTRING(', 37.51233279170618, ' ', 127.04926180642089, ',', 37.49047500861753, ' ', 127.03020739357909, ')'));
SELECT * FROM store as s
where MBRContains(@point, POINT(s.lat,s.lon));이 쿼리에 대해 부하테스트를 통해 성능을 측정해봤었는데
MySQL의 내장된 프로파일러를 통해 쿼리 자체의 성능에 대해 살펴보자.
MySQL 내장 프로파일러를 통한 성능 분석
MySQL에서는 쿼리 실행에 대해 세부적인 성능 분석을 제공하는 프로파일러가 내장되어있다.
- 프로파일링을 활성화 한다.
SET profiling = 1;- 쿼리를 실행한다.
SELECT * FROM store as s
WHERE MBRContains(@point, POINT(s.lat,s.lon));- 프로파일링 결과를 조회한다.
SHOW PROFILES
그러면 다음과 같이 쿼리의 실행시간에 대해 보여준다.
이 결과에서 해당 쿼리의 ID를 통해 조금 더 상세한 성능 분석을 조회할 수 있다.
SHOW PROFILE FOR QUERY [쿼리 ID]
- 10번의 테스트
| Count | Duration (s) |
|---|---|
| 1 | 0.1972 |
| 2 | 0.2033 |
| 3 | 0.1977 |
| 4 | 0.1981 |
| 5 | 0.1967 |
| 6 | 0.1972 |
| 7 | 0.1974 |
| 8 | 0.2001 |
| 9 | 0.1977 |
| 10 | 0.1976 |
- average : 0.1983s
DB 쿼리만으로 대략 0.2초가 소요되고 있다는 것을 확인할 수 있다.
쿼리 실행 계획
실행계획을 통해서 인덱스가 적용되었는지 확인해봐야 할 것 같다.
expain키워드를 붙여서 실행계획을 확인할 수 있다.
SET @point = ST_GeomFromText(CONCAT('LINESTRING(', 37.51233279170618, ' ', 127.04926180642089, ',', 37.49047500861753, ' ', 127.03020739357909, ')'));
EXPLAIN
SELECT * FROM store as s
where MBRContains(@point, POINT(s.lat,s.lon));쿼리를 실행하면 다음과 같은 결과를 받을 수 있다.
핵심적인 컬럼만 살펴보자.
- select_type: 쿼리의 종류
여기서는 단순 SELECT 쿼리를 사용하여 SIMPLE 로 표시되고 있다. - type: JOIN 또는 검색타입 (ref, range, index, ALL ...)
여기서는 풀 테이블 스캔을 하여 ALL 로 표시되고 있다. - key: 실제로 사용된 인덱스
사용된 인덱스가 없어서 NULL 로 표시된다. - rows: 쿼리 실행에 의해 검사되거나 읽혀진 행의 수
여기서 주목할 부분은 type 컬럼이다.
실행계획이 인덱스를 참조하고 있는지 가장 핵심인 컬럼이다.
우리의 쿼리는 인덱스를 타고있지 않기 때문에 공간인덱스를 설정해서 인덱스를 적용해보자.
공간 인덱스
이전 글에서 공간 데이터에 대해 간단하게 알아봤는데 MYSQL에서는 추가적으로 공간 데이터 검색을 위한 공간 인덱스도 제공한다.
공간 인덱스에서의 핵심은 R-Tree 라고 불리는 자료구조이다.
R-Tree
공간 데이터의 객체들을 Tree 형태로 계층적으로 조직화하고, 이들의 공간적 위치와 경계를 표현하는데에 사용한다.
출처: https://itwiki.kr/w/R_%ED%8A%B8%EB%A6%AC
R-Tree에 대해 이해하기 쉽게 표현한 그림이라서 가지고 왔다.
특징
- R-Tree는 각 노드가 공간 데이터 요소들의 MBR을 포함한다.
- 이 구조는 데이터가 공간에 어떻게 분포되어 있는지에 따라 동적으로 조정된다.
- R-Tree는 범위 검색, 근처 검색 같은 공간 쿼리를 빠르게 수행할 수 있게 해준다.
- Search 에서 O(logn)의 시간복잡도를 가진다.
여기서 MBR은 이전 글 에서도 등장 했었는데 공간 객체를 완전히 포함하는 가장 작은 직사각형 이다.
이러한 개념을 바탕으로 공간 인덱스를 이제 적용해보자.
공간 데이터 생성
공간 인덱스를 적용하기 위해서는 공간 데이터를 저장하는 필드가 필요하다.
기존의 컬럼은 다음과 같았다.
위도와 경도를 저장하고 있는 lat, lon 이라는 컬럼이 있었고 이를 이용해서 공간 데이터를 저장하는 필드를 추가해보자. 우리는 POINT 타입의 필드를 추가할 것 이다.
1. 공간 데이터 타입 생성
location 이라는 이름의
POINT타입 필드를 생성하는데 SRID 라는 것을 적용해야 한다.
Spatial Reference Identifier의 약자로 쉽게 말해서 좌표 체계 라고 생각하면 된다.
대표적으로 위경도 방식(WGS84) 의 SRID는 4326, 한국식 좌표계(UTM-K) 는 5179, 단순 직교 좌표계는 0이다.
SRID를 적용하지 않고 생성하였을 때 Default 값은 0 (단순 직교 좌표계) 이고 이때는 R-Tree의 인덱스 혜택을 받을 수 없다.
ALTER TABLE store ADD location POINT NOT NULL SRID 4326;2. 기존 컬럼을 이용하여 location 값 변경
UPDATE store SET location = ST_SRID(Point(lon, lat), 4326);3.location 필드 값 확인
1,2 과정을 진행하면 location 필드가 추가 된다.
공간데이터는 다음과 같이 BLOB 타입으로 표현되고 이 값을 확인하기 위해서 다음 SQL을 수행해보자.
SELECT ST_AsText(location) FROM store;
데이터가 아주 잘 들어가 있는 것을 볼 수 있다.
공간 인덱스 생성
R-Tree 인덱스 구조를 기반으로 동작하는 Spartial Index를 적용해보자.
ALTER TABLE store ADD SPATIAL INDEX(location);🚫 에러
대용량 데이터를 처리할 때는 다음과 같이 타임아웃이 발생할 수 있다.
Error Code: 2013. Lost connection to MySQL server during query
이런 경우에는 Workbench 서버의 설정을 늘려서 해결 할 수 있다.
이걸로도 해결이 안된다면 다음 쿼리를 실행하여 MySQL 서버의 설정을 변경해보자.
SET GLOBAL wait_timeout = 28800; -- 초 단위
SET GLOBAL interactive_timeout = 28800; -- 초 단위인덱스 확인
위 과정으로 쿼리가 잘 실행되었다면 이제 인덱스가 잘 적용되었는지 확인해보자.
인덱스 목록 확인
SHOW INDEXES FROM store;
location에 인덱스가 잘 걸려있고 이제 특정 쿼리에 대해 인덱스가 어떻게 사용되는지 실행계획을 통해 확인해보자.
실행 계획 확인
위에서 공간 인덱스를 적용하기 전에 살펴보았던 실행계획을 똑같이 확인해보자
SET @point = ST_GeomFromText(CONCAT('LINESTRING(', 37.51233279170618, ' ', 127.04926180642089, ',', 37.49047500861753, ' ', 127.03020739357909, ')'), 4326);
EXPLAIN
SELECT * FROM store AS s
WHERE MBRContains(@point, s.location);아까전과 바뀐점은 location 필드가 추가되어 location 으로 변경해준 것 뿐이다.
실행계획이 다음과 같이 나온다.
아래의 인덱스 적용전과 비교하면 확연한 차이가 느껴진다.
기존의 FULL SCAN 방식에서 4개의 rows(쿼리를 실행할 때 검색해야 할 행의 개수)로 줄어든 것을 볼 수 있다.
성능 분석
- 10번의 테스트
| Count | Duration (s) |
|---|---|
| 1 | 0.0012 |
| 2 | 0.0017 |
| 3 | 0.0011 |
| 4 | 0.0018 |
| 5 | 0.0018 |
| 6 | 0.0015 |
| 7 | 0.0012 |
| 8 | 0.0010 |
| 9 | 0.0015 |
| 10 | 0.0014 |
- average : 0.00142s
내장 프로파일러를 통하여 성능 분석을 해보았다.
기존의 인덱스를 적용하지 않았을 때랑 비교하면 0.1983s ->0.00142s
무려 약 140배 빨라졌다.
결과
위치 기반 검색에 있어서 SQL 쿼리에서 크게 병목현상이 발생하고 있음을 알고 공간 인덱스를 통하여 성능을 개선하고 측정하는 방법을 알아보았다.
공간 인덱스를 잘 사용만 해도 검색 엔진 없이 이대로 사용해도 무방할 것 같다.
하지만 우리 서비스에서는 단순히 위치 기반 검색뿐만 아니라 형태소 단위의 키워드 기반 검색 또한 동시에 진행해야 한다.
따라서 다음 글에서는 Elasticsearch 를 적용하여 성능 개선을 하는 방법에 대해 다뤄볼 것이다.
