[Real MySQL 8.0 1권] 09. 옵티마이저 (3)

유혁·2026년 5월 26일
CSDatabaseReal MySQL 8.0

Real MySQL 8.0

목록 보기
18/22

본 포스트는 Real MySQL 8.0 1권을 읽은 뒤 정리하는 글입니다.

9.3 고급 최적화

9.3.1 옵티마이저 스위치 옵션

루스 스캔 (loosescan)

LooseScan은 인덱스를 사용하는 GROUP BY 최적화 방법의 루스 인덱스 스캔과 비슷한 읽기 방식을 사용한다.

mysql> EXPLAIN
		SELECT * FROM departments d WHERE d.dept_no IN (
        	SELECT de.dept_no FROM dept_emp de );

위 쿼리에서 dept_emp 테이블에는 (dept_no + emp_no) 칼럼의 조합으로 프라이머리 키 인덱스가 만들어져 있으며, 전체 레코드 수는 33만 건 정도 존재하지만 dept_no 만으로 그루핑하면 9건만 남게 된다.

따라서 dept_mep 테이블의 프라이머리 키를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 중복된 레코드를 제거하며 아주 효율적으로 서브쿼리 부분을 실행할 수 있다.

해당 쿼리의 실행 계획은 아래와 같으며, Extra 칼럼에 "LooseScan" 문구를 확인할 수 있다. 또한 각 테이블에 할당된 id 칼럼 값이 동일하게 "1"이라는 것을 통해 내부적으로는 조인처럼 처리됐다는 것을 말해준다.

해당 쿼리의 실행 계획을 실제로 확인했을 때는 루스 스캔이 아닌 Firstmatch 최적화 방식을 사용되었다. 이는 서브쿼리 최적화의 성숙도 문제로, 옵티마이저 스위치를 변경 후 다시 테스트했다.

LooseScan 최적화는 다음과 같은 특성을 가진다.

  • 루스 인덱스 스캔으로 서브쿼리 테이블을 읽은 이후 아우터 에이블을 드리븐으로 사용해서 조인을 수행한다. 그래서 서브쿼리 부분이 루스 인덱스 스캔을 사용할 수 있는 조건이 갖추져야 사용할 수 있는 최적화이며, 다음과 같은 형태의 서브쿼리에서 사용할 수 있다.
SELECT .. FROM .. WHERE expr IN (SELECT keypart1 FROM tab WHERE ...)

SELECT .. FROM .. WHERE expr IN (
	SELECT keypart2 FROM tab WHERE keypart1='상수' ...
)

구체화 (Materialization)

Materialization 최적화는 세미 조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리를 최적화한다는 의미다. 여기서 구체화(Materialization)은 쉽게 표현하면 내부 임시 테이블을 생성한다는 것을 의미한다.

mysql> 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');

위 쿼리는 서브쿼리 구체화(Subquery Materialization)을 사용하는 예제이며, 아래는 해당 쿼리의 실행 계획이다.

select_type 칼럼에서 "MATERIALIZED" 문구를 확인할 수 있으며, 쿼리의 테이블은 2개인데 실행 계획은 3개 라인이 생성된 것을 보아 임시 테이블이 생성된 것을 짐작할 수 있다.

dept_emp 테이블을 읽는 서브쿼리가 먼저 실행되어 그 결과로 임시 테이블(\<subquery2>)이 만들어 졌고, 최종적으로 employees 테이블을 조인해 결과를 반환한다.

Materialization 최적화가 사용될 수 있는 형태의 쿼리에 몇 가지 제한 사항 및 특성이 존재한다.

  • IN(subquery)에서 서브쿼리는 상관 서브쿼리가 아니어야 하낟.
  • 서브쿼리는 GROUP BY나 집함 함수들이 사용돼도 구체화를 사용할 수 있다.
  • 구체화가 사용된 경우에는 내부 임시 테이블이 사용된다.

중복 제거 (Duplicated Weed-Out)

Duplicate Weedout은 세미 조인 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다.

mysql> EXPLAIN
		SELECT * FROM employees e
        WHERE e.emp_no IN (
        	SELECT s.emp_no FROM salaries s WHERE s.salary > 1500000);

salaries 테이블의 프라이머리 키는 (emp_no + from_date)로,salary가 150000 이상인 레코드를 조회하면 결과에는 중복된 emp_no가 발생할 수 있다.

Duplicate Weedout 최적화는 아래와 같이 원본 쿼리를 INNER JOIN + GROUP BY 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리를 처리한다.

mysql> SELECT e.*
		FROM employees e, salaries s
        WHERE e.emp_no=s.emp_no AND s.salary>150000
        GROUP BY e.emp_no;

다음은 Dulicate Weedout 최적화 알고리즘을 처리하는 과정이며, 이를 이미지로 나타냈다.

  1. salaries 테이블의 ix_salary 인덱스를 스캔해서 salary가 150000보다 큰 사원을 검색해 employees 테이블 조인을 실행
  2. 조인된 결과를 임시 테이블에 저장
  3. 임시 테이블에 저장된 결과에서 emp_no 기준으로 중복 제거
  4. 중복을 제거하고 남은 레코드를 최종적으로 반환

실행 계획은 다음과 같으며, Extra 칼럼에서 "Start templorary", "End temporary" 문구를 확인할 수 있다.

Duplicate Weedout 최적화는 다음과 같은 장점과 제약 사항이 있다.

  • 서브쿼리가 상관 서브쿼리여도 사용할 수 있다.
  • 서브쿼리가 GROUP BY나 집합 함수가 사용된 경우에는 사용될 수 없다.
  • 서브쿼리의 테이블을 조인으로 처리하기 때문에 최적화할 수 있는 방법이 많다.

컨디션 팬아웃 (condition_fanout_filter)

조인을 실행할 때 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미치므로, MySQL 옵티마이저는 여러 테이블이 조인되는 경우 가능하면 일치하는 레코드 건수가 적은 순서대로 조인을 실행한다.

다음은 예제 쿼리 및 condition_fanout_filter 옵티마이저 옵션을 비활성화한 상태의 실행 계획이다.

mysql> SET optimizer_switch='condition_fanout_filter=off';

mysql> SELECT *
		FROM employees e
        	INNER JOIN salaries s ON s.emp_no=e.emp_no
      	WHERE e.first_name='Matt'
        	AND e.hire_date BETWEEN '1985-11-21' AND '1986-11-21';

실행 계획에 의한 쿼리의 처리 절차는 대략 다음과 같다.

  1. employees 테이블에서 ix_firstname 인덱스를 이용해 first_name='Matt' 조건에 일치하는 233건의 레코드를 검색한다.
  2. 검색된 레코드 중에서 hire_date 조건에 해당하는 레코드만 걸러내는데, filtered 칼럼의 값이 100인 것은 옵티마이저가 233건 모두 조건을 만족할 것으로 예측했다는 것을 의미한다.
  3. employees 테이블을 읽은 결과 233건에 대해 salaries 테이블의 프라이머리 키를 이용해 salaries 테이블의 레코드를 읽는다. 이때 MySQL 옵티마이저는 employees 테이블 레코드 한 건당 salaries 테이블 레코드 9건이 일치할 것으로 예상했다.

다음은 옵션을 활성화 한 뒤 실행 계획이다.

여기서 중요한 차이점은 rows 칼럼의 값은 233으로 동일하지만, filtered 칼럼의 값이 100%에서 26.07%로 변경됐다는 점으로, condition_fandout_filter 최적화가 활성화되며 MySQL 옵티마이저는 인덱스를 사용할 수 있는 first_name 칼럼 조건 이외의 나머지 조건(hire_date)에 대해서도 얼마나 조건을 충족할지를 고려했다는 의미다.

condition_fanout_filter 최적화는 다음과 같은 조건을 만족하는 칼럼의 조건들에 대해 조건을 만족하는 레코드의 비율을 계산할 수 있다.

  1. WHERE 조건절에 사용된 칼럼에 대해 인덱스가 있는 경우
  2. WHERE 조건절에 사용된 칼럼에 대해 히스토그램이 존재하는 경우

파생 테이블 머지 (derived_merge)

MySQL 서버에서 FROM 절에 사용된 서브쿼리를 임시 테이블로 만들었는데, 이를 파생 테이블(Derived table)이라고 한다.

이전 버전에서는 MySQL 서버 내부적으로 임시 테이블을 생성한 다음 외부 쿼리 부분을 처리했는데, MySQL 5.7 버전부터는 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 파생 테이블 머지(derived_merge) 최적화가 도입되었다.

다음과 같은 조건에서는 옵티마이저가 자동으로 서브쿼리를 외부 쿼리로 병합할 수 없게 되어, 수동으로 서브쿼리를 외부 쿼리로 병합해서 작성해야 한다.

  • SUM(), MIN(), MAX() 같은 집계 함수와 윈도우 함수가 사용된 서브쿼리
  • DISTINCT가 사용된 서브쿼리
  • GROUP BY나 HAVING이 사용된 서브쿼리
  • LIMIT이 사용된 서브쿼리
  • UNION 또는 UNION ALL을 포함하는 서브쿼리
  • SELECT 절에 사용된 서브쿼리
  • 값이 변경되는 사용자 변수가 사용된 서브쿼리

인비저블 인덱스 (use_invisible_indexes)

MySQL 8.0 버전부터는 인덱스가 존재하더라도 옵티마이저가 해당 인덱스를 사용하지 못하게 제어하는 기능을 제공한다.

-- // 옵티마이저가 ix_hiredate 인덱스를 사용하지 못하게 변경
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate INVISIBLE;

-- // 옵티마이저가 ix_hiredate 인덱스를 사용할 수 있게 변경
mysql> ALTER TABLE employees ALTER INDEX ix_hiredate VISIBLE;

스킵 스캔 (skip_scan)

인덱스 스킵 스캔 최적화는 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로도 인덱스를 이용한 쿼리 성능 개선이 가능하다. 

mysql> ALTER TABLE employees ADD INDEX ix_gender_birthdate (gender, birth_date);

-- // ix_gender_birthdate 인덱스를 사용하지 못하는 쿼리
mysql> SELECT * FROM employees WHERE bitrh_date>='1965-02-01';

-- // ix_gender_birthdate 인덱스를 사용할 수 있는 쿼리
mysql> SELECT * FROM employees WHERE gender='M' AND birth_date>='1965-02-01';

위처럼 gender 칼럼에 대한 조건이 존재하지 않아 인덱스를 사용할 수 없는 경우, MySQL 8.0 버전의 옵티마이저는 테이블에 존재하는 모든 gender 칼럼의 값을 가져와 두 번째 쿼리와 같이 gender 칼럼의 조건이 있는 것처럼 쿼리를 최적화한다.

인덱스의 선행 칼럼이 다양한 값을 가지는 경우 인덱스 스킵 스캔 최적화가 비효율적일 수 있어, 옵티마이저는 인덱스의 선행 칼럼이 소수의 유니크한 값을 가질 때만 인덱스 스킵 스캔 최적화를 사용한다.

해시 조인 (hash_join)

대부분 해시 조인이 기존 네스티드 루프 조인보다 빠르다고 생각하지만, 이는 항상 옳지는 않다. 다음은 네스티드 루프 조인과 해시 조인의 처리 성능을 비교해 보여주는 것으로, 화살표의 길이는 전체 쿼리의 실행 시간을 의미한다.

네스티드 루프 조인과 해시 조인은 똑같은 시점에 시작했지만 해시 조인이 먼저 끝난 것을 확인할 수 있다. A 지점은 쿼리가 실행되며 MySQL 서버가 첫 번째 레코드를 찾아낸 시점, B 지점은 마지막 레코드를 찾아낸 시점을 의미한다.

해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많이 걸리지만 최종 레코드를 찾는 데까지 시간이 많이 걸리지만, 네스티드 루프 조인은 반대로 첫 번째 레코드를 찾는 것은 빠르나 마지막 레코드를 찾는 데까지 시간이 많이 걸리는 것을 알 수 있다.

즉, 해시 조인은 최고 스루풋 전략에, 네스티드 루프 조인은 최고 응답 속도 전략에 적합하다.

MySQL서버는 온라인 트랜잭션 처리를 위한 범용 RDBMS로, 기본적으로 응답 속도에 집중해 최적화한다. 따라서 MySQL 서버의 해시 조인 최적화는 네스티드 루프 조인이 사용되기 적합하지 않은 경우를 위한 차선책 정도로 생각하면 되며, MySQL 8.0.20 버전부터는 네스티드 루프 조인을 사용할 수 없는 경우에는 항상 해시 조인이 사용되도록 바뀌었다.

일반적으로 해시 조인은 빌드 단계(Build-phase)와 프로브 단계(Probe-phase)로 나뉘어 처리된다.

빌드 단계에서는 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라서 메모리에 해시 테이블을 생성(빌드)하는 작업을 수행한다.

프로브 단계는 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정을 진행한다.

빌드 단계에서 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블, 프로브 단계에서 읽는 나머지 테이블을 프로브 테이블이라고도 한다.

다음은 해시 조인 최적화 방식을 살펴보기 위한 예제로, IGNORE INDEX 힌트는 네스티드 루프 조인이 사용되지 못하게 하기 위해 사용했다.

mysql> EXPLAIN
		SELECT *
        FROM employees e IGNORE INDEX(PRIMARY, ix_hiredate)
        	INNER JOIN dept_emp de IGNORE INDEX(
            	ix_empno_fromdate, ix_fromdate
            ) ON de.emp_no=e.emp_no AND de.from_date=e.hire_date;

MySQL 옵티마이저는 해시 조인을 위해 빌드 테이블인 dept_emp 테이블의 레코드를 읽어서 메모리에 해시 테이블을 생성하고, 프로브 테이블 employees 테이블을 스캔하면서 메모리에 생성된 해시 테이블에서 레코드를 찾아서 결과를 사용자에게 반환하였다.

해시 테이블의 레코드 건수가 많아서 조인 버퍼의 공간이 부족한 경우, MySQL 서버는 빌드 테이블과 프로브 테이블을 적당한 크기(하나의 청크가 조인 버퍼보다 작도록)의 청크로 분리한 다음, 청크별로 위 이미지와 동일한 방식을 사용해 해시 조인을 처리한다.

다음 이미지는 조인 버퍼보다 해시 테이블이 큰 경우 해시 조인이 실행되는 방법으로, 해시 테이블이 설정된 메모리 크기(join_buffer_size)보다 큰 지를 알 수 없어 조금 복잡하다.

위와 같은 1차 조인 결과를 생성한 뒤에는 MySQL 서버는 디스크에 저장된 빌드 테이블 청크에서 첫 번째 청크를 읽어 다시 메모리 해시 테이블을 구축하고, 프로브 테이블 청크에서 첫 번째 청크를 읽어 조인을 수행해 2차 결과를 가져오며, 해당 과정을 디스크에 저장된 청크 개수만큼 반복하여 완성된 조인 결과를 만들어낸다.

MySQL 옵티마이저는 빌드 테이블의 크기에 따라 메모리에서 모두 처리 가능한 경우에는 클래식 해시 조인 알고리즘을, 해시 테이블이 조인 버퍼 메모리보다 큰 경우에는 그레이시 해시 조인 알고리즘을 활용하도록 구현돼있다.

인덱스 정렬 선호 (prefer_ordering_index)

MySQL 옵티마이저는 ORDER BY 또는 GROUP BY를 인덱스를 사용해 처리 가능한 경우 쿼리의 실행계획에서 이 인덱스의 가중치를 높이 설정해서 실행된다.

MySQL 8.0.20 버전까지는 옵티마이저가 체크해야 하는 레코드 건수가 많음에도 불구하고 정렬된 인덱스를 활용하여 실행 계획을 선택하는 실수가 가끔 발생하여, 직접 IGNORE INDEX 힌트를 사용하여 방지하였다.

MySQL 8.0.21 버전부터는 MySQL 서버 옵티마이저가 ORDER BY를 위한 인덱스에 너무 가중치를 부여하지 않도록 prefer_ordering_index 옵티마이저 옵션이 추가되었다.

9.3.2 조인 최적화 알고리즘

Exhaustive 검색 알고리즘

Exhaustive 검색 알고리즘은 FROM절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합 1개를 찾는 방법이다.

다음과 같이 간단한 4개의 테이블을 조인하는 쿼리 문장이 존재할 때, 이를 Exhaustive 검색 알고리즘으로 처리하는 과정을 살펴보자.

mysql> SELECT *
		FROM t1, t2, t3, t4
        WHERE ...

이미지는 Exhaustive 검색 알고리즘으로 4개의 테이블의 최적 조인 순서를 찾는 방법을 표현한 것이다. 만약 테이블이 더 많아지는 경우 모든 테이블의 조합에 대한 실행 계획 비용을 계산하기 때문에 시간 소모적인 문제가 발생한다.

Greedy 검색 알고리즘

Greedy 검색 알고리즘은 Exhaustive검색 알고리즘의 시간 소모적인 문제점을 해결하기 위해 MySQL 5.0부터 도입된 최적화 기법이다.

이전과 동일하게 4개의 테이블 조인 쿼리를 Greedy 검색 알고리즘을 통해 처리할 때의 과정은 다음과 같다. (optimizer_search_depth 값 2로 가정)

  1. 전체 N개의 테이블 중에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합 생성
  2. 생성된 조인 조합 중에서 최소 비용의 실행 계획 하나 선정
  3. 선정된 실행 계획의 첫 번째 테이블을 "부분 실행 계획"의 첫 번째 테이블로 선정
  4. 전체 N-1개의 테이블 중(3번에서 선택된 테이블 제외)에서 optimizer_search_depth 시스템 설정 변수에 정의된 개수의 테이블로 가능한 조인 조합 생성
  5. 생성된 조인 조합들을 하나씩 "부분 실행 계획"에 대입해 실행 비용 계산
  6. 비용 계산 결과, 최적의 실행 계획에서 두 번째 테이블을 "부분 실행 계획"의 두 번째 테이블로 선정
  7. 남은 테이블이 모두 없어질 때까지 4~6번 과정을 반복하며 "부분 실행 계획"의 테이블 조인 순서 기록
  8. 최종적으로 "부분 실행 계획"이 테이블의 조인 순서로 결정됨

profile
유혁
백엔드 개발자
이전 포스트

[Real MySQL 8.0 1권] 09. 옵티마이저와 힌트 (2)

다음 포스트

[Real MySQL 8.0 1권] 09. 옵티마이저 (4)

0개의 댓글