MySQL의 최적화 조인들에 대해 알아보자

세미 조인 (Semi Join)

다른 테이블과 실제 조인을 수행하지는 않고, 단지 다른 테이블에서 조건이 일치하는 레코드가 있는지 없는지만

체크하는 형태의 쿼리를 세미 조인이라고 한다.

일반 조인과의 다른 점은 EXISTS나 IN 연산자를 서브쿼리와 함께 사용해

서브쿼리의 조건을 만족하는 레코드는 가져오지 않고 만족하는지만 확인하는 것이다.

-- IN을 사용한 세미 조인
SELECT * FROM orders 
WHERE customer_id IN (
    SELECT customer_id 
    FROM customers 
    WHERE country = 'KR'
);

-- EXISTS를 사용한 세미 조인
SELECT * FROM orders o 
WHERE EXISTS (
    SELECT 1 
    FROM customers c 
    WHERE c.customer_id = o.customer_id 
    AND c.country = 'KR'
);

 

MySQL 8.0 이전에는 이 세미 조인 형태의 쿼리를 처리하는데 최적화가 되지 않았다.

현재와 어떤 차이가 있는지 다음 쿼리를 통해 한번 알아보자.

SET SESSION optimizer_switch='semijoin=off'; -- 세미 조인 비활성화

EXPLAIN SELECT * FROM employees e
WHERE e.emp_no IN (
	SELECT de.emp_no 
	FROM dept_emp de 
	WHERE de.from_date = '1995-01-01'
);

세미 조인을 비활성화하고 위 쿼리 실행 계획을 보면 다음과 같다.

서브쿼리에서 ix_fromdate 인덱스를 사용해 57개의 레코드를 스캔했고

메인쿼리에서는 e 테이블의 300,252개의 레코드를 스캔하면서 서브 쿼리의 결과 레코드 57개와 비교한다.

 

세미 조인을 활성화하고 다시 위 쿼리를 실행하면 어떻게 처리될까 ?

SET SESSION optimizer_switch='semijoin=on';

1. 서브쿼리가 MATERIALIZED로 처리되어 한 번만 실행되고 ix_fromdate 인덱스를 사용해 57개의 행을 가져온다.
2. 이 결과를 임시 테이블 subquery2로 만든다.
3. 임시 테이블의 각 행에 대해 e 테이블을 조회한다.

 

이전 실행 계획과 비교하면 세미 조인을 사용하고 난 후 서브쿼리를 실행하여 임시 테이블을 만들고

이 57건의 레코드만 있는 임시 테이블과 메인 테이블을 효율적으로 조인해

불필요한 스캔과 비교 연산이 대폭 감소했음을 알 수 있다.

 

세미 조인은 긍정문을 사용하는 세미 조인과 부정문을 사용하는 안티 세미 조인이 있다.

이 세미 조인들은 다음과 같은 최적화 전략으로 최적화 된다.

• IN-to-EXISTS
• MATERIALIZATION
• Table Pull-out
• Duplicate Weed-out
• First Match
• Loose Scan

쿼리에 사용되는 테이블과 조인 조건의 특성에 따라 옵티마이저가 위 전략 중 하나를 선별해 사용한다.

궁금하다면 추가로 알아보자.

---

해쉬 조인 (Hash Join)

기존에는 Nested Loop Join(중첩 루프 조인)만 지원됐지만

MySQL 8.0 버전부터 해쉬 조인이 추가로 지원됐다.

 

중첩 루프 조인은 드라이빙 테이블의 각 행에 대해 드리븐 테이블을 순차적으로 검색하는 방식이며

해쉬 조인은 해쉬 테이블을 이용해 바로 매칭되는 값을 찾는 방식이다.

이 때문에 해쉬 조인은 기존의 Nested Loop Join에 비해 성능이 크게 향상될 수 있는 장점을 가지며

대용량 데이터를 처리할 때 매우 효율적인 방식이다.

MySQL 서버는 주로 조인 조건의 컬럼이 인덱스가 없거나 조인 대상 테이블 중

일부의 레코드 건수가 매우 적은 경우 등에 대해 중첩 루프 조인 대신 해쉬 조인을 사용한다.

 

중첩 루프 조인은 첫 번째 레코드를 찾는 것은 빠르지만 마지막 레코드를 찾는 것은 느리다.

해쉬 조인은 해쉬 테이블을 만드는 시간이 필요해 첫 번째 레코드를 찾는데 시간이 많이 걸리지만

이후에 레코드를 찾는 것은 상대적으로 빠르다.

 

이로 인해 중첩 루프 조인은 빠른 응답 속도, 해쉬 조인은 빠른 처리량의 전략에 적합하다는 것을 알 수 있다.

하지만 일반적인 OLTP 환경은 빠른 응답 속도가 중요하다.

주로 OLTP 환경에 사용되는 MySQL은 응답 속도에 많은 비중을 두어

중첩 루프 조인을 사용할 수 없는 경우에는 항상 해쉬 조인이 사용되도록 되어 있다.

즉, 해쉬 조인은 중첩 루프 조인의 차선책 같은 느낌이기 때문에 해쉬 조인이 빠르다고 강제로 유도시키는 것은 좋지 않다고 한다.

 

해쉬 조인 작동 방식은 다음과 같이 2가지로 나뉘어 처리된다.

1. 빌드 단계 (Build Phase)

• 조인할 두 테이블 중 더 작은(레코드 건수가 작은) 테이블을 선택
• 선택된 테이블의 조인 키를 기준으로 해시 테이블을 메모리에 생성
• 이때 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블이라고 함.

2. 프로브 단계(Probe Phase)

• 나머지 테이블을 스캔하면서 해쉬 테이블의 일치 레코드를 찾음.
• 이때 읽는 나머지 테이블을 프로브 테이블이라고 함.

 

해쉬 조인은 당연하게도 해쉬 테이블을 저장하기 위한 메모리 공간이 필요하다.

이 메모리 공간은 다음 시스템 변수로 크기를 제어할 수 있는데 기본 값은 256KB이다.

join_buffer_size

만약 해시 테이블이 버퍼 크기보다 작으면 Grace Hash Join으로 전환되는데

해쉬 조인보다 더 복잡한 과정을 거치므로 해쉬 조인의 성능이 필요하다면

작업하는 데이터 크기에 맞게 크기를 조절해야할 수도 있을 것이다.

---

지연된 조인

조인은 대체로 실행될 수록 레코드가 늘어난다.

그래서 조인의 결과를 GROUP BY, ORDER BY하면 조인을 실행하기 전 보다 많은 레코드를 처리해야 한다.

인덱스가 적절히 사용된다면 이미 최적으로 처리되고 있겠지만 아닌 경우에는

지연된 조인을 이용하면 조인이 실행되기 이전에 GROUP BY, ORDER BY를 먼저 처리하는 방식을 의미한다.

지연된 조인은 주로 LIMIT이 함께 사용된 쿼리에서 더 큰 효과를 얻을 수 있다.

 

지연된 조인을 적용 전

아래 쿼리는 인덱스를 사용하지 못하는 GROUP BY, ORDER BY 쿼리이다.

SELECT e.*
FROM employees e
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE s.emp_no BETWEEN 10001 AND 13000
GROUP BY s.emp_no
ORDER BY SUM(s.salary) DESC
LIMIT 10;

이 쿼리의 실행 계획을 보면 다음과 같다.

이 실행 계획을 간단히 분석하면

• employees 테이블에서 조건에 맞는 약 3,000개의 행을 먼저 읽음
• 3,000개의 employee 레코드와 각 행 별로 조인 조건에 일치하는 9개의 salaries 레코드와 조인
• 약 27,000개의 salaries 레코드를 그룹화
• 그룹화된 레코드를 내림차순 정렬
• 최종적으로 상위 10개 행만 선택

조인은 3,000(드라이빙 테이블 rows) * 9(드리븐 테이블의 평균 매칭 레코드) = 27,000번

즉, 수 많은 조인이 이뤄지고 이 많은 데이터를 그룹화하고, 그룹화된 레코드를 정렬하고 있는 것을 볼 수 있다.

 

지연된 조인 적용 후

아래는 이전 쿼리를 지연된 조인으로 처리하는 경우이다.

SELECT e.*
FROM 
   (SELECT s.emp_no
    FROM salaries s
    WHERE s.emp_no BETWEEN 10001 AND 13000
    GROUP BY s.emp_no
    ORDER BY SUM(s.salary) DESC
    LIMIT 10) x,
   employees e
WHERE e.emp_no = x.emp_no;

 

이 쿼리의 실행 계획은 다음과 같다.

(실행 계획은 같은 id 내에서는 위에서 아래로 실행, id 컬럼이 큰 순서대로 실행된다.)

FROM 절에 서브 쿼리가 사용됐기 때문에 서브 쿼리의 결과는 3번째 줄에 DERIVED(파생) 처리됐다.

먼저 서브 쿼리가 실행되고 서브 쿼리의 결과를 derived2 임시 테이블에 저장한다.

이 때 LIMIT 갯수만큼 10건만 저장이 되었고 employees 테이블과 JOIN을 수행하는데

이전 쿼리에 비해 트라이빙 테이블의 rows가 적어 훨씬 적은 조인 횟수가 이뤄진다.

즉, 최종 10건만 employees 테이블과 조인한다.

 

전과 비교하면 다음과 같은 최적화를 얻을 수 있다.

• 조인 횟수 감소
• 임시 테이블에 저장될 레코드 감소 (메모리 사용량 감소)
• 정렬 부하 감소

 

지연 조인은 경우에 따라 조인 횟수를 줄여 상당한 성능 향상을 가져다 줄 수 있지만

모든 쿼리를 지연된 조인 형태로 개선할 수 있는 것은 아니다.

OUTER JOIN과 INNER JOIN에 대해 다음과 같은 조건이 갖춰져야지만 지연된 조인 쿼리로 개선할 수 있다.

• LEFT JOIN은 드라이빙 - 드리븐 테이블 간 1:1 또는 M:1 관계여야 함
• INNER JOIN은 위 조건과 동시에 드라이빙 테이블에 있는 레코드는 드리븐 테이블에 모두 존재해야한다.

---

래터럴 조인 (Lateral Join)

MySQL 8.0 버전부터는 레터럴 조인이라는 기능을 제공한다.

레터럴 조인이란 “FROM 절의 서브쿼리가 외부 쿼리의 컬럼을 참조할 수 있는 조인”이다.

 

아래 쿼리는 employees 테이블에서 이름이 ‘john’인 사원에 대해

사원별 가장 최근 급여 변경 내역을 최대 2건씩만 반환하는 쿼리이다.

SELECT *
FROM employees e
LEFT JOIN LATERAL (
    SELECT *
    FROM salaries s
    WHERE s.emp_no=e.emp_no
    ORDER BY s.from_date DESC LIMIT 2
) s2 ON s2.emp_no=e.emp_no
WHERE e.first_name='john';

가장 중요한 부분은 salaries 테이블을 읽는 서브쿼리에서 외부 쿼리의 FROM 절의 employees 테이블의 emp_no를 참조한다.

이렇게 서브쿼리가 외부 쿼리의 컬럼을 참조하기 위해서는 ‘LATERAL’ 키워드가 명시돼야 한다.

 

서브쿼리를 조인하는 경우와 래터럴 조인의 차이는 다음과 같다.

서브쿼리를 조인 래터럴 조인

	일반적인 서브쿼리 조인	래터럴 조인
실행 순서	서브쿼리가 먼저 실행되어 하나의 결과 집합을 만들고 이 결과 집합을 외부 쿼리와 JOIN	외부 쿼리의 결과 레코드 단위로 임시 테이블을 생성
외부 쿼리 컬럼 참조	외부 테이블의 컬럼을 서브쿼리 내부에서 참조할 수 없음	서브쿼리 내에서 외부 테이블의 컬럼 참조 가능

즉, 위 쿼리에서 employees 테이블에 'john'라는 이름을 가진 직원이 100명이 있다고 가정해보면

각 Matt마다 최근 급여 2건을 찾기 위해 LATERAL 서브쿼리는 100번 실행되고 각각 임시 테이블을 만든다.

 

따라서 LATERAL은 각 행마다 개별적인 처리가 필요할 때 유용하지만

그만큼 임시 테이블 생성에 대한 리소스를 많이 사용하기 떄문에 꼭 필요한 경우에만 사용하도록 하자.

---

참고자료

• Real MySQL 8.0 1권, 2권