책너두 (Real MySQL 8.0 1권) 33일차 (~362p)

요약

• 세미조인의 최적화 나머지 부분을 이해하게 됨.
  • 컨디션 팬아웃
    • 드라이빙 테이블, 드리븐 테이블을 최적 조건을 찾아 선정함.
    • 옵티마이저가 레코드 건수를 예측하여 성능을 향상시킴
  • 파생 테이블 머지
    • FROM 절에 사용된 서브 쿼리를 임시 테이블로 만들어 외부 쿼리와 병합하여 최적화함.
  • 인비저블 인덱스
    • 인덱스가 설정되어 있더라도 무시할 수있도록 사용할 수 있음.
  • 스킵 스캔
    • 인덱스 값이 정렬되어 있어서 가능함.
    • 인덱스의 선행 칼럼이 조건절에 없어도 후행 칼럼만으로 인덱스를 이용한 쿼리 성능 가능 → 인덱스 스킵했기에 가능
    • 선행 칼럼이 다양한 값을 가지면 스킵 스캔은 비효율 적일 수 있음.
  • 해시 조인
    • 첫번째 레코드 찾는데는 오래 걸리지만, 실행 속도가 그만큼 빠름.
    • 실제로 레코드 건수가 적은 경우에만 사용하고는 있음.
    • 빌드 단계와 프로브 단계로 나눔.
      • 빌드 : 해시 메모리 만듦
      • 프로브 : 해시 메모리에서 값을 찾아냄
    • 해시 1,2차 처리
      • 빌드, 프로브 테이블을 청크 단위로 디스크에 저장하여 분리하여 해시 처리를 수행함.

메모

컨디션 팬아웃(condition_fanout_filter)

• 조인 실행할 때, 테이블 순서는 쿼리 성능에 매우 큰 영향을 미침.
• A 테이블과 B 테이블 조인 시, A 테이블이 조건에 일치하는 레코드가 1만 건이고, B 테이블이 조건에 일치하는 레코드가 10건이라고 가정하자.
  • A 테이블을 조인의 드라이빙 테이블로 결정하면 B 테이블을 1만번 읽어야함.
  • 이때, B 테이블의 인덱스를 이용하여 조인을 실행하더라도 레코드를 읽을 때마다 B 테이블의 인덱스를 구성하는 B-Tree의 루트 노드부터 검색해야 함.
    • 따라서, MySQL 옵티마이저는 여러 테이블이 조인되는 경우, 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인을 실행함.

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

-- // condition_fandout_filter=off
+----+-------+------+--------------+------+----------+-------------+
| id | table | type | key          | rows | filtered | Extra       |
+----+-------+------+--------------+------+----------+-------------+
| 1  | e     | ref  | ix_firstname |  233 |   100.00 | Using where |
| 1  | s     | ref  | PRIMARY      |   10 |   100.00 | NULL        |
+----+-------+------+--------------+------+----------+-------------+

-- // condition_fandout_filter=on
+----+-------+------+--------------+------+----------+-------------+
| id | table | type | key          | rows | filtered | Extra       |
+----+-------+------+--------------+------+----------+-------------+
| 1  | e     | ref  | ix_firstname |  233 |    23.20 | Using where |
| 1  | s     | ref  | PRIMARY      |   10 |   100.00 | NULL        |
+----+-------+------+--------------+------+----------+-------------+

• 컨디션 팬 아웃이 off 일때, 실행 계획을 보면, 쿼리는 대략 다음의 절차를 거쳐 처리됨.
  1. employees 테이블에서 ix_firstname 인덱스를 이용하여 first_name=’Matt’ 조건에 일치하는 233건의 레코드를 검색한다.
  2. 검색된 233건 레코드 중, hire_date가 1985-11-21 ~ 1986-11-21 사이인 레코드만 걸러냄. 이 때, 실행 계획에서 filtered 칼럼의 값이 100인 것은 옵티마이저가 233건 모두 hire_date 칼럼의 조건을 만족할 것으로 예측했다는 것을 의미함.
  3. employees 테이블을 읽은 결과, 233건에 대해 salaries 테이블의 프라이머리 키를 이용해 salaries 테이블의 레코드를 읽음.
• 위에서 중요한 것은 employees 테이블의 rows 칼럼 값이 233이고, filtered 칼럼 값이 100% 라는 것임.
• 컨디션 팬 아웃이 on 이더라도 rows 칼럼 값은 233으로 동일함.
  • 하지만 filtered 칼럼 값이 100% 가아니라 23.2%로 변경됨.
  • 즉, 컨디션 팬 아웃 최적화가 활성화되면 MySQL 옵티마이저는 인덱스를 사용할 수 있는 first_name 칼럼 조건 이외의 나머지 조건(hire_date 칼럼의 조건)에 대해서도 얼마나 조건을 충족할지를 고려했다는 뜻임.
  • MySQL 옵티마이저가 조건을 만족하는 레코드 건수를 정확하게 예측할 수 있다면 더 빠른 실행 계획을 만들어 낼 수 있음
• 어떻게 filtered 칼럼 값을 예측해 내는 가?
  • 아래 조건을 만족하는 레코드의 비율을 계산할 수 있다.
    • WHERE 조건절에 사용된 칼럼에 대해 인덱스가 있는 경우
    • WHERE 조건절에 사용된 칼럼에 대해 히스토그램이 존재하는 경우
  • 위 예제에서, first_name=’Matt’ 조건을 위한 ix_firstname 인덱스를 사용함.
  • 실행 계획을 수립하는 경우에는 first_name 칼럼의 인덱스를 이용해 first_name=’Matt’ 조건에 일치하는 레코드 건수가 대략 233건 정도라는 것을 알아냄
    • hire_date 칼럼의 조건을 만족하는 레코드 비율이 대략 23.2% 일 것으로 예측함.
  • employees 테이블의 hire_date 칼럼의 인덱스가 없었다면 MySQL 옵티마이저는 first_name 칼럼의 인덱스를 이용하여 hire_date 칼럼값의 분포도를 살펴보고 filtered 칼럼 값을 예측함.

MySQL 옵티마이저가 실행 계획 수립할 때, 테이블이나 인덱스의 통계 정보만 사용하는 것이 아니라, 다음의 순서대로 사용 가능한 방식을 선택함.

1.  레인지 옵티마이저(Range optimizer)를 이용한 예측

2. 히스토그램을 이용한 예측

3. 인덱스 통계를 이용한 예측
4. 추측에 기반한 예측(Guesstimates = Guess + Estimate)

여기서 가장 우선순위가 높은 레인지 옵티마이저는 실제 인덱스의 데이터를 살펴보고 레코드 건수를 예측하는 방식임 → 실제 쿼리가 실행되기도 전에 실행 계획을 수립 단계에서 빠르게 소량의 데이터를 읽어보는 것임.
레인지 옵티마이저에 의한 예측은 인덱스를 이용해서 쿼리가 실행될 수 있을 떄만 사용됨.

• 컨디션 팬아웃 필터 최적화 기능을 활성화하면 MySQL 옵티마이저는 더 정교한 계산을 거쳐 실행 계획을 수립함.
  • 그만큼 더 많은 컴퓨팅 자원을 사용하게 됨.

파생 테이블 머지(derived_merge)

• 예전 버전의 MySQL 서버에서, 아래와 같은 FROM 절에 사용된 서브쿼리는, 먼저 실행해서 그 결과를 임시 테이블로 만든 다음, 외부 쿼리 부분을 처리했음.

mysql> EXPLAIN
             SELECT * FROM (
                 SELECT * FROM employees WHERE first_name='Matt'
           ) derived_table
             WHERE derived_table.hire_date='1986-04-03';

+----+-------------+------------+------+--------------+
| id | select_type | table      | type | key          |
+----+-------------+------------+------+--------------+
| 1  | PRIMARY     | <derived2> | ref  | <auto_key0>  |
| 2  | DERIVED     | employees  | ref  | ix_firstname |
+----+-------------+------------+------+--------------+

• 쿼리 실행 계획을 보면, employees 테이블을 읽는 라인의 select_type 칼럼 값이 DERIVED 라고 표시되어 있음.
  • 이는, employees 테이블에서 first_name 칼럼 값이 ‘Matt’인 레코드만 읽어서 임시 테이블을 생성하고, 이 임시 테이블을 다시 읽어서 hire_date 칼럼 값이 1986-04-03 인 레코드만 걸러내어 반환한 것임.
  • MySQL 서버에서는 이렇게 FROM 절에 사용된 서브쿼리를 파생 테이블(Derived Table) 이라고 부름
  • 위 실행 계획의 경우, MySQL 서버는 내부적으로 임시 테이블을 생성하고 first_name=’Matt’인 레코드를 employees 테이블에서 읽어서 임시 테이블로 INSERT 함.
  • 그리고 다시 임시 테이블을 읽으므로 MySQL 서버는 레코드를 복사하고 읽는 오버헤드가 더 추가됨.
• 5.7버전 부터 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 최적화가 도입됨.
  • derived_merge 최적화 옵션은 이러한 임시 테이블 최적화를 활성화할지 여부를 결정함.
• 위 쿼리에서 임시 테이블이 외부 쿼리로 병합된 경우의 실행 계획은 아래와 같다.

+----+-------------+-----------+-------------+--------------------------+
| id | select_type | table     | type        | key                      |
+----+-------------+-----------+-------------+--------------------------+
| 1  |  SIMPLE     | employees | index_merge | ix_hiredate,ix_firstname |
+----+-------------+-----------+-------------+--------------------------+

• 이전 실행 계획의 select_type에 있었던 DERIVED 라인이 없어지고 서브쿼리 없이 employees 테이블을 조회하는 형태의 단순 실행 계획으로 바뀜.
• 예전 버전의 MySQL 서버에서는 이러한 서브쿼리로 작성된 쿼리를 외부 쿼리로 병합하는 작업을 DBA가 수작업으로 많이 처리했음.
  • 하지만 이제는 MySQL 옵티마이저가 처리할 수 있어서 굳이 쿼리를 새로 작성할 필요는 없어짐.
  • 하지만, 모든 쿼리에 대해 옵티마이저가 서브쿼리를 외부 쿼리로 병합할 수 있는 건 아님.
  • 아래와 같은 조건에서는 옵티마이저가 자동으로 서브쿼리를 외부 쿼리로 병합할 수 없게 됨.
  • 따라서, 다음의 경우, 가능하면 서브쿼리는 외부 쿼리로 수동으로 병합해서 작성하는 것이 쿼리 성능 향상에 도움됨.
    • SUM(), MIN(), MAX() 같은 집계 함수와 윈도우 함수(Window Function)가 사용된 서브쿼리
    • DISTINCT 가 사용된 서브쿼리
    • GROUP BY나 HAVING이 사용된 서브쿼리
    • LIMIT이 사용된 서브쿼리
    • UNION 또는 UNION ALL을 포함하는 서브쿼리
    • SELECT 절에 사용된 서브쿼리
    • 값이 변경되는 사용자 변수가 사용된 서브쿼리

인비저블 인덱스(use_invisible_indexes)

• MySQL 8.0 버전부터 인덱스의 가용 상태를 제어할 수 있는 기능이 추가됨.
• 8.0 이전 버전까지는, 인덱스가 존재하면 옵티마이저가 항상 실행 계획을 수립할 때, 해당 인덱스를 검토하고 사용함.
• 8.0부터 인덱스를 삭제하지 않고, 해당 인덱스를 사용하지 못하게 제어하는 기능을 제공함.

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

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

• use_invisible_indexes 옵티마이저 옵션을 이용하면 INVISIBLE로 설정된 인덱스라 하더라도 옵티마이저가 사용하게 제어할 수 있음.

스킵 스캔(skip_scan)

• 인덱스의 핵심은 값이 정렬되어 있다는 것임.
  • 이로 인해, 인덱스를 구성하는 칼럼 순서가 매우 중요함.
• (A, B, C) 칼럼으로 구성된 인덱스가 있을 때, WHERE B,C 칼럼에 대한 조건을 가지고 있다면, 이 쿼리는 인덱스를 활용할 수 없음.
  • 인덱스 스킵 스캔은 제한적이긴 하지만, 인덱스의 이런 제약 사항을 뛰어넘을 수 있는 최적화 기법임.
• MySQL 8.0 버전부터 인덱스 스킵 스캔 최적화가 도입됨.
  • 이 기능은 인덱스의 선행 칼럼이 조건절에 사용되지 않더라도 후행 칼럼의 조건만으로 인덱스를 이용한 쿼리 성능 개선이 가능함.

mysql> SELECT * FROM employees birth_date>='1965-02-01';

• employees 테이블의 인덱스는 (gender, birth_date) 로 이루어져 있음.
• 위 쿼리 실행 시, 8.0 버전의 옵티마이저는 테이블에 존재하는 모든 gender 칼럼 값을 가져와서 두 번쨰 쿼리와 같이 gender 칼럼의 조건이 있는 것처럼 쿼리를 최적화 함.
  • 그런데, 인덱스의 선행 칼럼이 매우 다양한 값을 가지는 경우, 인덱스 스킵 스캔 최저고하가 비효율 적일 수 있음.
  • 그래서 MySQL 8.0 옵티마이저는 인덱스의 선행 칼럼이 소수의 유니크한 값을 가질 때만 인덱스 스킵 스캔 최적화를 사용한다. (8.3.4.4 인덱스 스킵 스캔 참고)

해시 조인(hash_join)

• MySQL 8.0.18 부터, 해시 조인이 추가로 지원됨.

• 많은 사용자가 해시 조인 기능을 기대하는 이유를 기존의 네스티드 루프 조인(Nested Loop Join)보다 해시 조인이 빠르다고 생각하기 때문임. 
  • 이는 항상 옳은 이야기가 아님.
  • 위 그림은 네스티드 루프 조인과 해시 조인의 처리 성능을 비교한 것임. (화살표 길이는 전체 쿼리 실행 시간을 의미함.)
  • 예상대로, 네스티드 루프 조인과 해시 조인은 똑같은 시점에 시작했지만, 해시 조인이 먼저 끝난 것을 확인할 수 있음.
    • 그림의 A 지점은 쿼리가 실행되면서 MySQL 서버가 첫 번째 레코드를 찾아낸 시점임.
    • B 지점은 MySQL 서버가 마지막 레코드를 찾아낸 시점임.
  • 해시 조인은 첫 번째 레코드를 찾는 데 시간이 많이 걸리지만, 최종 레코드를 찾는 데 까지는 시간이 많이 걸리지 않음을 알 수 있음.
  • 네스티드 루프 조인은 마지막 레코드를 찾는데 시간이 많이 걸리지만, 첫 번쨰 레코드 찾는데는 상대적으로 훨씬 빠르다는 것을 알 수 있음.
    • 즉, 해시 조인 쿼리는 최고 스루풋(Best Throughput) 전략에 적합함.
    • 네스티드 루프 조인은 최고 응답 속도(Best Response-time) 전략에 적합함.
  • 일반적인 웹 서비스는 온라인 트랜잭션(OLTP) 서비스이므로 스루풋도 중요하지만 응답 속도가 더 중요함.
  • 분석과 같은 서비스는 사용자 응답 시간보다, 전체적으로 처리 소요 시간이 중요하므로 응답 속보도나는 전체 스루풋이 중요함.
• MySQL 서버는 범용 RDBMS 임. → 온라인 트랜잭션 처리를 위한 데이터베이스 서버를 지칭함.
  • 즉, 대용량 데이터 분석에선 MySQL 서버를 사용하지 않을 것임.
  • 이 관점으로 보면 MySQL 서버가 응답 속도 와 스루풋 중 어디에 집중해서 최적화할 것인지 명확해짐.
    • 이 이유로 MySQL 서버는 주로 조인 조건의 칼럼이 인덱스가 없다거나 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우 등에 대해서만 해시 조인 알고리즘을 사용하도록 설계되어 있음.
    • 즉, MySQL 서버의 해시 조인 최적화는 네스티드 루프 조인이 사용되기에 적합하지 않은 경우를 위한 차선책(Fallback strategy) 같은 기능으로 생각하는 것이 좋다
      • 해시 조인이 빠르다고, 옵티마이저 힌트를 사용해서 강제로 쿼리 실행 계획을 해시 조인으로 유도하는 건 좋지 않음
• 일반적으로 해시 조인은 빌드 단계(Build-phsae)와 프로브 단계(Probe-phsae)로 나뉘어 처리됨.
  • 빌드 단계에서는 조인 대상 테이블 중, 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라 메모리에 해시 테이블을 생성(빌드) 하는 작업을 수행함.
    • 빌드 단계에서 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블 이라고도 함.
  • 프로브 단계는 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정을 의미함.
    • 이때 읽는 나머지 테이블을 프로브 테이블이라고도 함.

mysql> SELECT *
             FROM employees e IGNORE INDEX(PRIMARYT, 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;

+----+-------------+-------+------+---------------------------------------------+
| id | select_type | table | type | Extra                                       |
+----+-------------+-------+------+---------------------------------------------+
| 1  | SIMPLE      | de    | ALL  |  NULL                                       |
| 1  | SIMPLE      | e     | ALL  |  Using where; Using join buffer (hash join) |
+----+-------------+-------+------+---------------------------------------------+

• 위 쿼리의 실행 계획을 EXPLAIN 만으로 어느 테이블이 빌드 테이블이고, 프로브 테이블인지 식별하기 어려움.
  • 이럴 때, EXPLAIN FORMAT=TREE 명령, 또는 EXPLAIN ANALYZE 명령을 사용하면 좀 더 쉽게 구분 할 수 있음. (빌드 테이블이 dept_emp 이고, 프로브 테이블이 employees 임)

• MySQL 옵티마이저는 해시 조인을 위해 빌드 테이블인 dept_emp 테이블의 레코드를 읽어서 메모리에 해시 테이블을 생성함.
• 그리고, 프로브 테이블로 선택된 employees 테이블을 스캔하면서 메모리에 생성된 해시 테이블에서 레코드를 찾아서 결과를 사용자에게 반환하는 과정을 위 그림이 표현함.
  • 위 그림은 메모리에서 모두 처리가 가능한 경우임.
• 해시 테이블을 메모리에 저장할 때, MySQL 서버는 join_buffer_size 시스템 변수로 크기를 제어할 수 있는 조인 버퍼를 사용함.
  • 조인 버퍼 기본 크기는 256KB임
  • 해시 테이블의 레코드 건수가 많아서 조인 버퍼의 공간이 부족할 수 있음.
  • 이 경우, MySQL 서버는 빌드 테이블과 프로브 테이블을 적당한 크기(하나의 청크가 조인 버퍼보다 작도록)의 청크로 분리한 다음, 아래 그림과 같이 청크별로 메모리 해시 테이블에서 레코드를 찾아서 반환함
  • 아래 그림의 경우, 해시 조인 1차 처리라고 함.

• 해시조인 1차 처리는 조인 버퍼보다 해시 테이블이 큰 경우, 해시 조인이 실행되는 방법을 보여줌.

• 위 그림은 해시 조인 처리 방법이 좀 더 복잡해짐.
  • 해시 테이블이 설정된 메모리 크기(join_buffer_size) 보다 큰지 알 수 없기 때문임.
  • MySQL 서버는 dept_emp 테이블을 읽으면서 메모리의 해시 테이블을 준비하다, 지정된 메모리 크기(join_buffer_size)를 넘어서면 dept_emp 테이블의 나머지 레코드를 디스크에 청크로 구분해서 저장함. (해시조인 1차 처리에 해당하는 그림의 1) 2) 과정임)
  • MySQL 서버는 employees 테이블의 emp_no 값을 이용하여 메모리의 해시 테이블을 검색해서 1차 조인 결과를 생성함.
    • 동시에, employees 테이블에서 읽은 레코드를 디스크에 청크로 구분해서 저장함.
• 디스크에 저장된 청크 개수만큼 이 과정을 반복 처리해서 완성된 조인 결과를 만들어냄.
  • 이렇게 청크 단위로 조인을 수행하기 위해, MySQL 서버는 2차 해시 함수를 이용하여 빌드 테이블과 프로브 테이블을 동일 개수의 청크로 쪼개어 디스크로 저장함.
  • 여기서 2차 해시 함수라는 건 특정 해시 알고리즘을 지칭한 것이 아니라 해시 조인을 위해 해시 키 생성용 해시 함수와는 다른, 해시 함수를 사용해서 청크를 분리한다는 의미임. (실제 청크를 구분하기 위해서 사용하는 해시 함수 자체는 크게 중요하지 않음)
• MySQL 옵티마이저는 빌드 테이블 크기에 따라 클래식 해시 조인(Classic hash join) 알고리즘을 사용함.
• 해시 조인 1차 처리의 경우, 그레이스 해시 조인(Grace hash join) 알고리즘을 하이브리드하게 활용하도록 구현되어 있음.
• MySQL 서버의 해시 조인에서 해시 키를 만들 때, xxHash64 해시 함수를 사용함.
  • xxHash64 해시 함수는 매우 빠르고 해시된 값의 분포도 훌륭한 해시 알고리즘임.