인덱스에 따른 검색 조회 성능을 테스트해보자

Doyeon·2023년 4월 2일

현재 진행하는 프로젝트에서는 전국에 있는 주차장을 검색할 수 있다.
‘서울특별시청’과 같은 키워드를 입력했을 때, 키워드에 해당하는 목적지 좌표 주변 모든 주차장을 지도에서 보여주고 있다.
키워드를 입력했을 때 반환되는 목적지의 좌표는 다음과 같이 구하고 있다.

카카오맵 API로 키워드를 검색했을 때 반환되는 장소 리스트를 구한다.
주차장 DB에 있는 데이터 중 키워드를 포함한 주차장 리스트를 구한다.
1번과 2번 리스트 중 키워드와 유사도를 판별하여 유사도가 가장 높은 장소를 반환한다.
3번에서 반환된 장소의 반경 2km 이내 주차장을 보여준다.

여기서, 주차장 검색 조회 성능을 향상시키기 위해 2번, 4번에 해당하는 작업을 수행할 때 인덱스를 적용하고자 한다.
인덱스를 적용할 때 성능 차이가 어떻게 나는지 테스트해보자!

테스트 정의

테스트 항목

키워드 검색
주변 주차장 검색

테스트 방법

: 쿼리 실행 속도를 측정한다.

키워드 검색

검색어 : 서울특별시청, 사려니숲길, 서울역, 강남역, 스타벅스 울산, 울산 스타벅스

**select * from park_info a where a.name like '%서울특별시청%';
select * from park_info a where a.name like '%스타벅스 울산%';
select * from park_info a where a.name like '%사려니숲길%';**

주변 주차장 검색

서울특별시청 좌표를 기준으로 주변 주차장을 검색한다.(lo: 126.978179, la: 37.5665734)

주차장 유형 : 공영, 민영

**select * from park_info a, park_oper_info b
where a.id = b.park_info_id
and ST_DISTANCE_SPHERE(Point(126.978179, 37.5665734), Point(a.lo, a.la)) < 2000
and b.park_ctgy = '공영'
order by ST_Distance_Sphere(Point(126.978179, 37.5665734), Point(a.lo, a.la)) asc;**

인덱스 적용

park_info 의 name

BTREE 인덱스 생성

CREATE INDEX idx_park_info_name ON park_info(name);

FULLTEXT 인덱스 생성

ALTER TABLE park_info ADD FULLTEXT idx_park_info_name (name);

park_info의 la, lo

BTREE 인덱스 생성

ALTER TABLE park_info ADD INDEX idx_park_info_lo_la (lo, la);

park_oper_info 의 park_ctgy

BTREE 인덱스 생성

CREATE INDEX idx_park_ctgy ON park_oper_info (park_ctgy);

키워드 검색 테스트

0. FULLSCAN

주차장 정보(약 78,000건) 검색 시 성능 측정
테스트 결과
두 단어 이상의 키워드 검색
- 스타벅스 울산 검색결과 → 12건
- 울산 스타벅스 검색결과 → 0건
  
  → 주차장 이름에 포함된 키워드 그대로 검색한 경우에만 검색 결과로 추출된다. 울산 스타벅스 로 검색시 결과값이 나오지 않는다.

1. MySQL INDEX(BTREE)

속도 개선을 위해 기본 INDEX 기능 적용
테스트 결과
서울역 검색결과
- FULL SCAN 검색 → 검색결과 9건
- BTREE 검색 → 검색결과 5건
강남역 검색결과
- FULL SCAN 검색 → 검색결과 7건
- BTREE 검색 → 검색결과 5건
  
  → B-TREE 기반 인덱스 방식은 첫글자를 기준으로 인덱싱 처리를 하기 때문에 like '검색어%' 형태의 쿼리에서만 인덱스가 적용된다. %검색어 , %검색어% 쿼리를 인덱스로 검색할 수 없다.
두 단어 이상의 키워드 검색 → FULL SCAN 방식과 동일한 문제가 발생한다. 단어의 순서가 바뀔 경우 원하는 검색 결과를 얻지 못한다.

2. FULLTEXT INDEX

검색 성능과 특정 단어, 구 검색 정확성 향상을 위해 FULLTEXT INDEX 생성
테스트 결과
서울역 검색 → 검색결과 7건 - 검색 키워드가 단어 중간에 위치하면 검색하지 못한다.
강남역 검색 → 검색결과 7건 - 검색 키워드가 단어 시작 부분에 위치한 경우 검색결과에 포함된다.
울산 스타벅스 검색 → 검색결과 12건 - 두 단어 이상 검색시 단어의 순서가 바뀌어도 검색결과에 포함된다.

주변 주차장 검색 테스트

주차장 유형(park_ctgy) 인덱스 생성

실행 쿼리

select * from park_info a, park_oper_info b
where a.id = b.park_info_id
  and (6731 * acos(cos(radians(37.5665734)) * cos(radians(a.la)) * cos(radians(a.lo) - radians(126.978179)) + sin(radians(37.5665734)) * sin(radians(a.la)))) < 2
  and b.park_ctgy = '민영'
order by (6731 * acos(cos(radians(37.5665734)) * cos(radians(a.la)) * cos(radians(a.lo) - radians(126.978179)) + sin(radians(37.5665734)) * sin(radians(a.la)))) asc;

EXPLAIN 쿼리 실행 결과
park_oper_info 테이블에서 park_ctgy 에 인덱스를 생성했으나, 쿼리 실행계획 결과를 보면, idx_park_ctgy 인덱스가 실제로 사용되지는 않았다.
옵티마이저가 Join 컬럼인 park_info_id 에 생성된 인덱스를 선택하므로, 주차장 유형 컬럼에 만든 인덱스는 사용되지 않아 따로 성능 측정을 하지 않았다.

테스트 결과

현재 DB에 들어있는 주차장 데이터는 약 78,000건이다. 데이터가 많은 것은 아니어서 인덱스를 적용하지 않아도 조회 시간이 오래 걸리지는 않는다. 하지만 주차장을 조회할 때, DB에 해당 키워드가 있는 데이터를 찾는 것 외에 들어가 있는 로직이 더 있기 때문에, 각 로직에서 조금씩이라도 조회 시간을 줄일 수 있다면 최종 조회 성능에 유의미한 영향을 줄 수 있다고 판단했다.
MySQL 기본 인덱스인 BTREE 인덱스와 FULLTEXT 인덱스의 경우 속도 차이가 크게 나지는 않지만, 그래도 FULLTEXT 인덱스를 걸었을 때 조회 속도가 조금은 더 빠른 것을 확인할 수 있다. 또한, FULLTEXT 인덱스의 경우 두 단어 이상 키워드를 검색했을 때, 단어의 순서를 바꿔 검색을 해도 원하는 결과를 리턴해주기 때문에 조회 성능과 검색 효용성 면에서 더 우위에 있다고 볼 수 있다.