[MyBatis] Loop vs Subquery vs Inner Join
요즘 회사 프로젝트 때문에 엄청 바쁜 하루를 보내고 있다. 지금 하는 프로젝트를 간략하게 설명하자면, PHP로 되어있는 AS-IS를 Java / Spring Boot + Vue typescript 로 마이그레이션 하는 작업을 하고 있다.
애니웨이, 이전 코드(PHP)를 그대로 옮기다 이상한 점들을 발견하게 되었고, 이를 어떻게 개선하면 좋을지 고민했던 내용과 어떻게 수정했는지 이번 게시물에 담고자 한다.
문제 상황
1. 특정 조건에 의하여 product_id를 Select 해온다.
SELECT product_id
FROM shipping_product
WHERE owner_id IN (1,2,3,4 ...)2. 1번의 결과 값을 List에 담는다.
3. List의 길이가 만 이상이면 500개 씩 n개의 array를 만든다.
4. 아래에 해당하는 sql 구문을 20번 돌면서 수행한다.
SELECT relesae_id
, release_code
, release_name
, expired_date
...
FROM release_product
WHERE product_id IN (1, ... , 500) // 501 ~ 1000, 1001 ~ 1500 ...
AND ...
GROUP BY ...
해당 flow를 보면서 왜 500개 씩 끊어서 for문을 돌까? 충분히 하나의 쿼리에서 작성 가능한데 왜 두 개의 쿼리로 나누어서 수행했을까?
라는 의문이 들었다. 그래서 이전 php 개발자와 이야기를 나누어 보았다.
의문 1) 왜 500개씩 끊어서 for문을 도는 것인가?
https://stackoverflow.com/questions/16335011/what-is-maximum-query-size-for-mysql
What is maximum query size for mysql?
What is maximum query size for mysql? Some times the server stops working when I fire a too long query.
stackoverflow.com
MySQL의 경우 쿼리 사이즈의 제한이 있다는 답변을 받았다. 그래서 쿼리 사이즈를 확인할 수 있는 명령어를 수행해보니
정말 limit가 있었다 ㅎ..
즉, 백만건의 데이터를 위 구문의 IN절 안에 포함시키면
WHERE product_id IN (1, 2, 3, ... ... ... 1000000) 이러면 쿼리 사이즈가 너무 커져 정상적인 결과를 도출할 수 없을 수도 있다.
의문 2) 왜 두개의 쿼리로 나누어서 설계하였는가?
쿼리 재사용을 위해 즉, 쿼리 개별 조회 후, 조합하는 방식으로 설계를 했다라는 답변을 받았다. 사실 요 답변은 이해가 가진 않았지만, SI를 하는 입장에서 개발 기간이 타이트하고 위에서 압박이 들어온다면 어쩔 수 없이 이런 구조를 가져갔어야 될 것 같다.
Loop vs Subquery 속도 테스트
우선, 500개씩 chunk list로 나눠서 n번 반복하는 쿼리 구문과 IN 절 다음에 바로 Subquery를 사용하는 쿼리 구문 중, 뭐가 더 속도가 빠른지 테스트를 진행해보려고 한다.
테스트 환경은 아래와 같다.
chunk list 테스트 환경
AS-IS의 코드와 동일하게 한 쿼리문에서 결과 값을 가져오면 이걸 500개 씩 짤라서 for문 도는 방식이다. 그리고 springframwork에 내장되어 있는 StopWatch를 사용하여 시간을 측정했다.
subquery 테스트 환경
SELECT relesae_id
, release_code
, release_name
, expired_date
...
FROM release_product
WHERE product_id IN (SELECT product_id
FROM shipping_product
WHERE owner_id IN (1, 2, 3, 4 ...))
AND ...
GROUP BY ...위 쿼리문 처럼 서브쿼리를 IN 절 안에 사용하여 하나의 쿼리로만 수행할 수 있도록 설계했다.
팀에서 현기증 난다고 빨리 결과를 알려달라할 정도로 관심이 많았다 ㅎㅎ.. 실험 결과는?!!!
10번씩 테스트를 해보면서 평균 값을 계산해본 결과 만개의 데이터까지는 속도 차이가 무의미했으나, 3만 부터는 확실한 차이를 보였다. 시간적으로는 압도적인 성능 개선을 얻을 수 있었지만... Subquery를 도입하기에는 아직 리스크가 있었다.
어떤 risk가..?
1. 쿼리의 복잡도
서브 쿼리로 인해, 쿼리의 볼륨이 많아져 가독성이 떨어지는 단점이 있다.
2. 최적화가 불가능하다.
Subquery는 어떻게 보면 가상의 데이터를 하나 만들어 사용하는 것이다. 그러므로 실제 table과 비교했을 때, 메타 정보를 담고 있지 않다.
즉, 명시적인 제약이나 인덱스가 작성되지 않았다. 그러므로 최적화가 힘들고 성능에서 문제가 발생할 수 있다.
3. 재사용이 불가능하다.
서브 쿼리에 있는 Select 문의 경우 다른 곳에서 많이 필요로하는 구문이다. 그렇기 때문에 쿼리 재사용이 불가능하다는 단점이 있다.
(사실 MyBatis에서는 <sql></sql>로 재사용 문제는 해결 가능함 ㅎ)
그래서 개선 한다는거여~? 만다는거여~?
여기서 포기할 킹명주가 아니다.. 이것 때문에 야근을 했는데, 내 의견이 받아지지 않는다면 너무 슬프다. 그래서 엄청나게 유능하다고 소문난 DBA 대리님을 찾아가서 조언을 얻었다.
요런 요런 문제가 있어 찾아왔따~ 어떻게 하면 좋을까요? 라고 질문하자.. 속도 측면에서는 subquery가 훨씬 빠를 순 있어도 메모리 측면에서 500개씩 끊어서 연산하는게 효율적이라고 하셨다. 그리고 직접 full query를 보아야 감이 오실 것 같다고 하셨다.
그래서 full query 를 전달드렸는데, 의외의 답변이 왔다.
"명주님.. 근데 이거 그냥 inner join 사용하면 되는건데?! "
뭔가 잘못되었다.. 대뇌 전두엽이 시려온다.. 생각해보니 왜 굳이 서브쿼리, chunk를 고민했을까??
대리님께서 제안하신 쿼리는 아래와 같다.
SELECT relesae_id
, release_code
, release_name
, expired_date
...
FROM release_product as release
INNER JOIN shipping_product as shipping
ON shipping.product_id = release.product_id
WHERE shipping.owner_id IN (1, 2, 3, 4 ...)
AND ...
GROUP BY ...
그런데, 신기하게 서브쿼리와 성능 차이는 거의 없었다.
굳이 시간 성능으로 따지자면 JOIN >= Subquery >>> Loop (chunk list) 였다. 그치만 메모리, 최적화, 속도 측면 모두를 고려한다면 JOIN 방식을 선택하는게 맞다!!
단순 마이그레이션 작업이다보니, 이전 쿼리를 최대한 보존하고 싶었던 마음(타의적인 마음^^)에 이런 호러블한 결과를 가져왔다.. 생각이 매우 편협해졌던 것 같다. Inner Join 한방이면 해결 가능한 것을!!!
그렇지만, 이 실험은 꽤나 많은 영향을 주었다. 속도 테스트를 진행할 수 있는 개발자가 되었고 성능을 고려하여 개발할 수 있는 개발자로 성장했다.
오늘의 결론
일단, 회사에서는 도입하지 못했다.. 일단 AS-IS를 그대로 따라하고 이후, 성능 개선을 고려해보자는 답변을 받았다. 이러한 고민을 똑같이 하고 있다면 조인 방식이 제일 좋기 때문에, JOIN을 활용했으면 좋겠다.
그렇지만 어쩔 수 없이 IN절 내에서 loop chunk list vs subquery 고민하고 있다면 아래와 같이 상황에 맞춰 사용하면 된다.
성능 >> 메모리
- subquery!!
메모리 >> 성능
- loop