쿼리 속도 개선기

ㅎㅎ·2023년 10월 2일
sql
0

문제점

  • "아파트명으로 검색" 기능은 검색어로 입력한 아파트 이름을 가지고 있는 전국 아파트 거래 데이터를 조회하여, 가장 최근 거래 데이터 정보를 반환하여 지도 상에 마커로 표시해주는 기능임

  • 기존 API는 Mybatis 환경에서 다음의 쿼리를 수행함

    <select id="getAptByName" parameterType="HouseParameterDto" resultType="houseInfoDto">
        select hi.aptCode, hi.aptName, hi.buildYear, hi.dongName, hi.roadName, trim('0' from hi.roadNameBonbun) roadNameBonbun, 
        trim('0' from hi.roadNameBubun) roadNameBubun,
                 hi.jibun, hi.lng, hi.lat, hi.dongCode,
                hd.dealAmount, hd.dealYear, hd.dealMonth, hd.dealDay, hd.area, hd.floor, max(hd.dealDate) dealDate,
                d.sidoName, d.gugunName
        from housedeal hd, houseinfo hi, dongcode d
        where hi.aptName like concat ('%', #{aptName}, '%')
        and hd.aptCode = hi.aptCode
        and hi.dongCode = d.dongCode
        group by hd.aptCode
    </select>

  • 문제점은 가장 최근 거래 정보만 가져오는 것이 아니라 해당 아파트의 거래목록, 주소정보를 모두 조회하는 것임

    • 기능의 문제는 없지만, 아주 흔한 아파트 이름의 경우(ex "주공") 전국에 거래 데이터가 많기 때문에, 약 440만 건의 데이터를 가지고 있는 테이블을 group 함수와 join을 이용해 조작하니 10초 이상의 실행시간이 나타났음.
    • 따라서 사용자 경험을 개선하기 위해 실행 속도를 줄여야 했음

  • 추가로 이 쿼리문 자체에 오류가 있었음

    • 최근 거래 정보를 불러오기 위해 max 함수를 이용했는데, 단순히 최종적으로 group화 할 때 dealDate(거래일)에 대해서 max를 구했으므로, 거래일은 최근 거래일이지만, 나머지 정보들은 최근 거래일에 해당하는 거래 정보가 아니게 됨. 따라서 표시되는 거래 금액에 오류가 발생함.

쿼리 수정

  • 우선 최근 거래 정보를 정확히 가져올 수 있도록 쿼리문을 다음과 같이 수정함.
select hi.aptCode, hi.aptName, hi.buildYear, hi.dongName, hi.roadName, trim('0' from hi.roadNameBonbun) roadNameBonbun,
	   trim('0' from hi.roadNameBubun) roadNameBubun,
	   hi.jibun, hi.lng, hi.lat, hi.dongCode,
	   hd.dealAmount, hd.dealYear, hd.dealMonth, hd.dealDay, hd.area, hd.floor, hd.dealDate,
	   d.sidoName, d.gugunName
from housedeal hd, houseinfo hi, dongcode d
where hi.aptName like concat ('%', "아파트명", '%')
and hd.no in (select no
				from ( select max(dealDate) dealDate, max(no) no
						from housedeal hd, houseinfo hi
						where hi.aptName like concat ('%', "아파트명", '%')
						and hi.aptCode = hd.aptCode 
                		group by hi.aptCode)
and hd.aptCode = hi.aptCode
and hi.dongCode = d.dongCode;
  • 이렇게 쿼리를 수정하여, 최근 거래에 해당하는 거래 정보의 pk 값인 no를 가지고 와서 no에 해당하는 거래정보를 가져올 수 있도록 함.
    • 여전히 10초 이상의 시간이 소요됨.

여러 방안 고려

  1. 쿼리 자체의 속도를 개선: 원래 작성된 쿼리문을 효율적으로 바꿔 실행속도를 개선할 수 있도록 한다.
  1. API 분리: 마커에 표시할 정보만 가져오고, 아파트 상세 정보는 마커를 클릭할 때 가져온다.
  • 2번 방안이 현실적으로 생각되나, 우선 쿼리 실행에 대한 이해를 높일 겸 1번부터 시도해본다.

1번 방안

  • 현재 주공이라는 검색어를 입력하면 851개의 쿼리가 RETURN, 15초가 소요됨

쿼리분석

  • 쿼리를 쪼개서 어떤 곳에서 실행 시간이 느려지는지 확인해보니 아래에서 병목 현상이 나타남.
select max(no) no, max(dealDate) dealDate 
	from housedeal hd, houseinfo hi 
	where hi.aptName like concat ('%', "주공", '%') 
	and hi.aptCode = hd.aptCode 
    group by hi.aptCode;
  • 실행계획 및 profile을 확인해 보았음(쿼리문 앞에 Explain을 붙여 실행)
  • 아직 잘 해석을 할 수가 없었음. 다음 글들을 참고해 추가 공부 필요.
  • 위 쿼리를 다시 쪼개서서 like 연산을 통한 검색, housedeal과 hoseinfo의 조인, group by 세가지를 실행해보니, group by에서 실행속도가 오래 걸림을 알 수 있었음.

#참고: 쿼리 실행 계획 분석

MySQL 공식문서 group by 최적화

  • MySQL의 Optimization에 관한 공식 문서를 읽어보았고, loose index scan과 tight index scan라는 방법이 있었음.

  • 일반적으로 group by는 각 그룹별로 해당 그룹에 속하는 데이터를 찾아 임시테이블에 넣고, 여기서 집계함수를 실행하는 방식으로 동작함.

  • 하지만 최적화 방법을 이용하면 임시테이블을 따로 만들지 않고, 이미 인덱싱을 통해 순서대로 되어 있는 데이터들을 이용해 빠르게 그룹화와 집계함수를 실행함.

  • 이 방법은, group by에 지정하는 각 column이 모두 index로 지정되어 있고, 인덱싱한 순서대로 group by에 지정하거나, 혹은 지정되지 않은 칼럼의 값들을 상수 조건으로 할당해줄 때만 동작함. 나의 쿼리에서는 이 조건을 충족하지 않아서 사용할 수가 없었음.

파티셔닝?

  • GPT에게 대용량 데이터를 group by 하는 연산의 속도 개선 방법을 물으니 파니셔닝에 대해 알려줌.
  • 파티셔닝은 한 테이블을 기준 키를 가지고 적절히 영역을 나눠 같은 스키마 여러 개를 생성해 분배하는 것.
  • 나의 경우 hosedeal 테이블의 440만건 데이터의 pk는 no이기 때문에 그룹화에 이용하는 aptCode는 순서대로 정렬되어 있지 않음. 그래서 aptCode를 기준 키로 해서 나눌 수가 없다고 생각함. 아파트이름으로 검색 시에 aptCode는 매우 다양하게 분포되어 있을텐데 그러면 모든 스키마를 다 탐색하며 데이터를 골라야 하는 등 문제가 발생할 것이고 불가능한 방법이라 생각함.

그렇다면 group by 속도 개선은 원시적불능인가..?

  • 개발자 커뮤니티 OKKY에 질문도 남기고, 같이 공부하는 사람들과 고민을 해보면서 두 가지 방법을 알게 됐음.

  • 1번: dealDate의 max값을 구할 필요가 없다. 왜냐하면 housedeal 테이블의 pk인 no가 이미 최근 거래일 수록 나중에 삽입되어 큰 값을 가지기 때문에, max(no)을 뽑으면 그게 최근 거래 데이터가 된다. 따라서 pk인 no가 indexing되어 있으므로 이 칼럼만 max값을 뽑으면 된다. 실제로 해보니 매우 빠르게 실행됨.

select max(no)
from housedeal hd, houseinfo hi
where hi.aptName like concat ('%', "주공", '%')
and hi.aptCode = hd.aptCode 
group by hi.aptCode;

  • 2번: 만약 dealDate를 이용하고 싶다면 그룹화하는 칼럼과 dealDate를 함께 index로 묶어주면 빠르게 max(dealDate)를 수행할 수 있다. 묶어서 indexing을 처리하면 aptCode - dealDate가 순서대로 key로 정렬되어 있는 자료구조를 가질 것이다. 그러므로 빠르게 max(dealDate)를 수행할 수 있다. 기존에는 aptCode에 대한 index만 있고, (aptCode, dealDate)로 된 index는 없었다. 그래서 실행속도가 느릴 수밖에 없었다. index에 대한 이해가 부족했고, 이번 리팩토링을 통해 좀 더 알게 됐다.
ALTER TABLE housedeal 
ADD INDEX housedeal_aptCode_dealDate_idx (aptCode, dealDate);
  • 위 명령어로 인덱스롤 만들고 실행을 하니 11초에서 0.1초로 실행 속도가 줄었다.
  • index를 잘 사용하는 것이 얼마나 중요한지 알게 됐고, 또 OKKY 커뮤니티에 질문글을 올렸을 때 많은 고수들이 빈약한 질문글에서 추축을 통해 위 2가지 방안을 정확히 짚어주었다. 같이 공부하는 팀원들도 아이디어를 같이 떠올려주었고, 문제를 공유하는 것이 얼마나 중요한지 또 한 번 느꼈다.
profile
ㅎㅎ
Hello World
이전 포스트

CORS, 쿠키 세션, JWT

다음 포스트

쿼리 속도 개선기

0개의 댓글