[이슈 #6] 주소 데이터의 빠른 조회를 위해 인덱스 설정하기

얼마 전 주소데이터를 DB에서 관리하도록 변경하였는데, 데이터가 100,000,00건정도 되어서 검색이 정말 느렸습니다. 인덱스를 걸지 않은 상태로 조회를 하면 검색에만 10초가 넘게 걸리는 무시무시한 상황이었습니다.

 

다행히도 주소 DB는 DML문이 거의 진행되지 않습니다. 가끔 공공데이터에서 업데이트 정보가 올라온다면 그 부분만 수정해주면 되는 것이죠. 조회는 많고 수정 삽입 삭제가 거의 없는 테이블이니 인덱스를 잡기에 부담은 없을 것 같습니다.

 

우선 해당 주소 DB를 구축하는 과정은 이전 포스팅에 적혀있습니다. 필요하시다면 참고해주세요.

https://deveric.tistory.com/66?category=350852

[이슈 #4] 주소를 외래키로 관리하도록 변경(공공데이터 사용)

 

처음 다뤄보는 1000만건의 데이터

 

 

 

검색 성능 측정

 

성능 측정 - 0, 기본 성능으로 검색하기

프로젝트 작업 테스트로 Postman을 사용하고 있습니다. 주소 검색 로직을 제작한 후에 한번 request를 날려봤는데 한참동안 응답이 오지 않아서 확인해보니 DB에서 응답이 10초가 넘게 오지 않고 있었습니다.

 

해당 프로젝트에서 공백 값으로 넘어온 부분은 검색 조건에 포함시키지 않도록 되어있습니다. 검색한 부분은 건물 이름 하나입니다.

 

건물 이름이 '네이버'로 시작하는 주소 가져오기

검색 하나 하는데 12.34초가 걸렸다

 

해당 쿼리는 다음과 같습니다.

SELECT town_code townCode, city_name cityName, ....중략
FROM ADDRESS 
WHERE 1 = 1
AND building_name_for_city 
LIKE CONCAT('네이버', '%') 
ORDER BY building_management_number LIMIT 10

 

해당 쿼리를 DB로 가져가서 인덱스 검사를 진행해 보았습니다. 인덱스를 검사하는 방법은 간단합니다. EXPLAIN을 앞에 붙여주기만 하면 됩니다.

 

 

PHPMyAdmin에서 확인한 결과

 

인덱스를 타고 있기는 합니다. PRIMARY Key 인덱스를 타고 있다고 하는데, 페이징 처리를 위하여 Order by절에 적어놓은 building_management_number(PK)만 인덱스를 타는 것 같습니다. Extra를 보면 어느 부분에서 인덱스를 타는지 알 수 있습니다. 해당 인덱스를 타긴 하지만 별 도움이 되는것 같지는 않습니다. 조회하는데 13초 정도 걸리니까요.

 

검색 한 번 하는데 13000ms라는 응답 시간이 걸린다는 것은 서비스를 제공할 수 없다는 뜻입니다. 그 응답시간동안 다른 사용자는 DB 사용을 할 수 없기 때문입니다. 그렇기 때문에 쿼리 튜닝과 DB 성능 개선이 필수적인 상황입니다.

 

 

개선사항 - 1, 검색 조건을 정하기

조회 성능을 높이는 것에 필수적인 것 중 하나가 인덱스 처리입니다. 인덱스 컬럼을 지정하기 위해서는 어떤 컬럼을 인덱스로 지정해 주어야 성능이 빠르게 나오냐는 것입니다. 주소 테이블에 있는 모든 컬럼을 인덱스로 지정하는 것은 자원의 낭비가 심하므로 최소한의 인덱스로 최대한의 성능을 끌어내야합니다.

 

주소 DB의 구조는 다음과 같습니다.

 

ADDRESS Table schema

 

그러므로 주소 검색을 진행할 때 어떤 조건으로 검색이 일어나는지 먼저 고민해보았습니다. 제가 주소 검색을 할 때는 다음 상황으로 검색하는 경우가 많습니다.

 

1. xx로 xx번길 xx-x 2. xx동 xxx-x 3. xx동 '건물이름'

 

크게 두가지 경우로 나눌 수 있었습니다.

1. 지번 주소로 검색

SELECT * FROM `ADDRESS` 
WHERE town_name LIKE CONCAT('야탑', '%')
AND	  building_number LIKE CONCAT('', '%')
AND	  building_side_number LIKE CONCAT('', '%')
AND   building_management_number > '' /* 페이징 */
ORDER BY building_management_number
LIMIT 10;

2. 도로명 주소로 검색

SELECT * FROM `ADDRESS` 
WHERE road_name LIKE CONCAT('야탑', '%')
AND	  building_number LIKE CONCAT('', '%')
AND	  building_side_number LIKE CONCAT('', '%')
AND   building_management_number > '' /* 페이징 */
ORDER BY building_management_number
LIMIT 10;

3. 자신의 건물 이름으로 검색

SELECT * FROM `ADDRESS` 
WHERE building_name_for_city LIKE CONCAT('야탑', '%')
AND   building_management_number > '' /* 페이징 */
ORDER BY building_management_number
LIMIT 10;

 

성능 측정 - 1, 아무 설정 없이 검색

우선 위 세가지 조건으로 쿼리를 구성한 후 질의해보았습니다.

 

1. 지번 주소로 검색 - 2503ms

 

 

2. 도로명 주소로 검색 - 2585ms

 

3. 건물 이름으로 검색 - 2439ms

 

 

 

개선사항 - 2, 인덱스 지정하기

 

자신의 주소를 검색한다면 우선 특정할 수 있는 건물 번호와 건물 부번호가 필요할 것입니다.(부가적으로 건물 이름이 있다면 건물 이름 포함)

또한 어느 동, 어느 도로인지도 적어줘야 자신이 찾는 정보가 나올 것입니다.

인덱스 후보는 다음과 같습니다.

 

1. 읍면동명

2. 도로명

3. 건물 번호

4. 건물 부번호

5. 건물 명

 

 

인덱스 생성

1. 기본키 인덱스(자동 생성)

2. 지번 주소 검색 인덱스 (읍면동명, 건물 본번, 건물 부번)

3. 도로명 주소 검색 인덱스 (도로명, 건물 본번, 건물 부번)

4. 건물 이름 검색 인덱스 (건물 이름)

 

 

성능측정 - 2, 인덱스 적용 후

1. 지번 주소로 검색 - 1ms 미만

 

2. 도로명 주소로 검색 - 1ms 미만

 

3. 건물 이름으로 검색 - 1ms 미만

실제 서비스 상에서 검색 성능 측정

프로젝트 DelFood에 실제 적용하여, Postman을 통해 성능을 측정해보았습니다.

최초 인덱스 없이 검색하였을 때는 약 13000ms의 응답 시간이 걸렸습니다.

 

1. 지번 주소로 검색 - 94ms

 

 

2. 도로명 주소로 검색 - 106ms

 

 

3. 건물 이름으로 검색 - 167 ms

 

 

 

 

결과

DB - 2500ms -> 1ms

Service - 12340ms -> 120ms

 

13초 가량 소요되는 검색 시간을 0.2초 미만으로 속도를 단축하였습니다. 해당 속도는 서버를 통해서 가져올 때 까지의 속도라서 DB에 직접적으로 조회한 속도와는 다소 차이가 있지만, 약 60배 이상의 속도 증가 효과를 보았습니다. 데이터 조회 성능을 위하여 인덱스는 꼭 설정해 주는 것이 좋을 것입니다.