스칼라 서브쿼리는 최악이다

서브쿼리의 결과가 정확히 1행 1열이라면, 이를 스칼라 서브쿼리(Scalar Subquery) 라고 부른다. 스칼라 서브쿼리는 이러한 단일 값을 SELECT 절, WHERE 절, HAVING 절 등에서 필드처럼 사용할 수 있는 서브쿼리를 의미한다.

 

A scalar subquery is a simple operand, and you can use it almost anywhere a single column value or literal is legal, and you can expect it to have those characteristics that all operands have: a data type, a length, an indication that it can be NULL, and so on.   - https://dev.mysql.com/doc/refman/8.4/en/scalar-subqueries.html

아래의 쿼리는 스칼라 서브 쿼리 결과가 필드 max_score 에 담겨 있다.

SELECT name, 
       (SELECT MAX(score) FROM exam WHERE exam.student_id = tb_student.id) AS max_score
FROM tb_student;

스칼라 서브쿼리의 가장 큰 문제점은 기준이 되는 SELECT 결과의 레코드 마다 서브쿼리가 매번 실행된다는 점이다. 예를 들어, tb_student 테이블에 10만 건의 레코드가 있다면, 해당 스칼라 서브쿼리는 총 10만 번 실행되는 셈이다. 결과 데이터 양이 많아질수록 성능 저하가 발생할 수 있다.

 

이는 상황에 따라서 문제의 소지가 된다. 서브 쿼리 결과가 동일하다면 굳이 레코드 마다 실행할 필요가 없다. MYSQL 5.6 버전 부터는 이에 대한 개선이 있엇다. 아래의 조건을 모두 만족하면 레코드 갯수만큼 스칼라 쿼리가 실행되지 않고 1번만 동작하여 상수처럼 취급할수 있다.

• 서브쿼리가 외부 테이블 컬럼(또는 외부 쿼리 참조)을 참조하지 않는 경우
• limit 1, max, min, count 처럼 단일 값 반환이 명확할 것

위 쿼리 예제에서는 WHERE 절 필터링으로 외부 쿼리 결과를 비교하는 구문이 있어서 상수처럼 쓸수 없어 매번 쿼리가 실행된다. 위 예시의 스칼라 서브쿼리가 실제로는 결과가 상수이더라도, 이는 쿼리를 실행해보아야만 알수있기 때문에 mysql 실행계획은 상수로 취급할 수가 없다. 그럼으로 서브쿼리를 매번 실행할수 밖에 없다.

위 예시의 경우라면 서브 쿼리를 사용하지 않고, join 절을 통해서 최적화를 시도하는 것이 정답이 될 수 있다.

SELECT s.name, MAX(e.score) AS max_score
FROM tb_student s
LEFT JOIN exam e ON e.student_id = s.id
GROUP BY s.id;

비록 결과는 동일하더라도, 이 JOIN 구문이 훨씬 더 효율적으로 동작한다. MAX와 같은 집계 함수는 GROUP BY 절로 묶인 각 그룹에 대해 한 번씩만 실행된다. 예를 들어 GROUP BY s.id를 기준으로 그룹핑한 결과가 10개라면, MAX(e.score)는 단 10번만 수행된다. 반면 앞선 스칼라 서브쿼리는 tb_student의 전체 레코드 수만큼, 즉 10만 건에 대해 서브쿼리가 각각 실행되므로 처리 비용이 훨씬 크다.

이와 동일한 상황이라면, 스칼라 서브쿼리보다는 JOIN과 GROUP BY를 활용한 방식이 성능과 확장성 측면에서 더 유리하다. 결과는 같더라도 그 과정이 어떻게 처리되는지가 MySQL 성능 최적화의 핵심이다. 오늘 다룬 내용과 유사한 또 다른 사례로는 EXISTS와 LIMIT 1의 차이가 있다. 다음 글에서는 이 둘의 차이와 성능상 고려사항에 대해 알아볼까 한다.