책너두 (Real MySQL 8.0 1권) 30일차 (~329p)

요약

• MySQL 의 옵티마이저의 고급 최적화에 대한 내용을 알게됨.
  • 통계 정보, 옵티마이저 옵션을 결합하여 실행계획을 수립함.
  • 옵티마이저 옵션을 이용 → 조인이 많이 사용되는 서비스에 사용
  • 스위치 → 고급 최적화 기능을 제어하는 용도로 사용
• 옵티마이저 스위치 옵션을 이해하게 됨.
  • 글로벌, 세션별 설정이 가능함.
  • MRR & 배치 키 액세스
  • 블록 네스티드 루프 조인
  • 인덱스 컨디션 푸시다운
  • 인덱스 확장
    • 에 대한 내용을 통해 실행 계획의 결정을 예시를 통해 잘 이해할 수 있었음.

메모

고급 최적화

• MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립함.
  • 옵티마이저 옵션은 크게 아래와 같이 구분할 수 있다.
    • 조인 관련된 옵티마이저 옵션
      • MySQL 서버 초기 버전부터 제공되던 옵션임.
      • 조인이 많이 사용되는 서비스에서는 알아야 하는 부분임.
    • 스위치
      • MySQL 5.5 버전부터 지원되기 시작함.
      • MySQL 서버의 고급 최적화 기능들을 제어하는 용도로 사용됨.

옵티마이저 스위치 옵션

• 옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어한다.
  • 이 시스템 변수에는 여러 개의 옵션을 세트로 묶어서 설정하는 방식을 사용함.
  • (p318~219에 옵티마이저 스위치들에 대한 내용이 잘 나와있음.)
  • 각 옵티마이저 스위치 옵션은 “default”, “on”, “off” 중에 하나를 설정할 수 있음.
  • 옵티마이저 스위치 옵션은 글로벌과 세션별 모두 설정할 수 있는 시스템 변수임.
    • MySQL 서버 전체적으로, 또는 현재 커넥션에 대해서만 다음과 같이 설정할 수 있음.

-- // MySQL 서버 전체적으로 옵티마이저 스위치 설정
mysql> SET GLOBAL optimizer_switch='index_merge=on, index_merge_union=on, ...';

-- // 현재 커넥션의 옵티마이저 스위치 설정
mysql> SET SESSION optimizer_switch='index_merge=on, index_merge_union=on, ...';

• “SET_VAR” 옵티마이저 힌트를 이용해서 현재 쿼리에만 설정할 수도 있음.

mysql> SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */
             ...
             FROM ...

MRR과 배치 키 액세스(mrr & batched_key_access)

• MRR은 “Multi-Range Read”를 줄여서 부르는 이름임.
  • 매뉴얼에서는 DS-MRR(Disk Sweep Multi-Range Read) 라고도 함.
• MySQL 서버에서 지금까지 지원한 조인 방식은 드라이빙 테이블(조인에서 제일 먼저 읽는 테이블)의 레코드를 한 건 읽어서, 드리븐 테이블(조인되는 테이블에서 드라이빙이 아닌 테이블들)의 일치하는 레코드를 찾아서 조인을 수행헀음.
  • 이를, 네스티드 루프 조인(Nested Loop Join) 이라고 함.
  • MySQL 서버의 내부 구조상, 조인 처리는 MySQL 엔진이 처리하지만, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당함.
    • 이때, 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면, 레코드를 찾고 읽는 스토리지 엔진에서는 아무런 최적화를 수행할 수 없음.
      • 이 단점을 보완하기 위해, MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링한다.
      • 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고, 조인 대상을 버퍼링 하는 것임.
      • 조인 버퍼에 레코드가 가득 차면, 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청한다.
        • 이렇게 함으로써 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화할 수 있다.
        • 물론, 데이터 페이지가 메모리(InnoDB 버퍼 풀)에 있더라도 버퍼 풀의 접근을 최소화할 수 있다.
        • 이 같은 읽기 방식을 MRR(Multi-Range Read) 라고함.
        • MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access) 조인이라고 함.
        • BKA 조인 최적화는 기본이 비활성화임. → BKA 조인의 단점이 있기 떄문임.
          • 쿼리 특성에 따라 BKA 조인이 큰 도움이 되는 경우도 있지만, BKA 조인을 사용하게 되면 부가적인 정렬 작업이 필요해지면서 오히려 성능에 안 좋은 영향을 미치는 겨우도 있음.

블록 네스티드 루프 조인(block_nested_loop)

• MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인(Nested Loop Join)임.
• 블록 네스티드 루프 조인은 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용되는 조인 방식임.

mysql> EXPLAIN
             SELECT *
             FROM employees e
             INNNER JOIN salaries s ON s.emp_no=e.emp_no
                                                          AND s.from_date<=NOW()
                                                            AND s.toDate>=NOW()
             WHERE e.first_name='Amor';

+----+-------------+-------+------+--------------+-------------+
| id | select_type | table | type | key          | Extra       |
+----+-------------+-------+------+--------------+-------------+
| 1  | SIMPLE      | e     | ref  | ix_firstname | NULL        |
| 1  | SIMPLE      | s     | ref  | PRIMARY      | Using where |
+----+-------------+-------+------+--------------+-------------+

• 위 형태의 조인은 프로그래밍 언어에서 마치 중첩된 반복 명령을 사용하는 것처럼 작용하여 네스티드 루프 조인이라고 함.
• 네스티드 루프 조인과 블록 네스티드 루프 조인의 가장 큰 차이는
  • 조인 버퍼(Join_buffer_size 시스템 설정으로 조정되는 조인을 위한 버퍼)가 사용되는지 여부와, 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐다.
• 조인 알고리즘에서 “Block” 이라는 단어가 사용되면, 조인용으로 별도의 버퍼가 사용됐다는 것을 의미함.
• 조인 쿼리 실행 계획에 Extra 칼럼에서 “Using Join buffer” 문구가 표시되면, 그 실행 계획은 조인 버퍼를 사용한다는 것을 의미함.
• 보통, 조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리됨.
  • 예를 들어, 드라이빙 테이블에서 일치하는 레코드가 1000건이고, 드리븐 테이블의 조인 조건이 인덱스를 이용할수 없다면, 드리븐 테이블에서 연결되는 레코드를 찾기 위해 1000번의 풀 테이블 스캔을 해야 함.
    • 따라서 쿼리가 상당히 느려지며 옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립함.
      • 그런데, 어떤 방식으로든 드리븐 테이블이 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면..?
      • 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리함.
      • 이때 사용하는 메모리의 캐시를 조인 버퍼(Join buffer)라고 함.
      • 조인 버퍼는 join_buffer_size 시스템 변수로 크기를 제한할 수 있음.
      • 조인이 완료되면 조인 버퍼는 바로 해제됨.

mysql> SELECT *
             FROM dept_emp de, employees e
             WHERE de.from_date>'1995-01-01' AND e.emp_no<109004;

-- 실행 계획
+----+-------------+-------+-------+-------------+---------------------------------------+
| id | select_type | table | type  | key         | Extra                                 |
+----+-------------+-------+-------+-------------+---------------------------------------+
| 1  | SIMPLE      | de    | range | ix_fromdate | Using index condition                 |
| 1  | SIMPLE      | e     | range | PRIMARY     | Using join buffer (block nested loop) |
+----+-------------+-------+-------+-------------+---------------------------------------+

• 위 쿼리에서, 두 테이블이 조인되는데, 각 테이블에 대한 조건은 WHERE 절에 있지만, 두 테이블 간의 연결 고리 역할을 하는 조인 조건이 없다.
• 그래서 dept_emp 테이블에서 from_date>’2000-01-01’ 인 레코드와 employees 테이블에서 emp_no < 109004 조건을 만족하는 레코드는 카테시안 조인을 수행함.
• 실행 계획을 보면, dept_emp 테이블이 드라이빙 테이블임.
  • employees 테이블을 읽을 때는 조인 버퍼(Join buffer)를 이용해서 블록 네스티드 루프 조인을 한다는 것을 Extra 칼럼의 내용으로 알 수 있음.

• 이 그림은 위 쿼리의 실행 계획에서 조인 버퍼가 어떻게 사용되는지 보여줌.
  1. dept_emp 테이블의 ix_fromdate 인덱스를 이용해 (from_date>’1995-01-01’) 조건을 만족하는 레코드를 검색한다.
  2. 조인에 필요한 나머지 칼럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장한다.
  3. employees 테이블의 프라이머리 키를 이용해 (emp_no < 109004) 조건을 만족하는 레코드를 검색한다.
  4. 3번에서 검색된 결과(employees)에 2번의 캐시된 조인 버퍼의 레코드(dept_emp)를 결합해서 반환한다.
• 위 그림을 통해 알 수 있는 중요한 점은, 조인 버퍼가 사용되는 쿼리에서는 조인의 순서가 거꾸로인 것처럼 실행된다는 점임.
  • 쿼리의 실행 계획상으로는 dept_emp 테이블이 드라이빙 테이블, employees 테이블이 드리븐 테이블임.
  • 하지만, 실제 드라이빙 테이블의 결과는 조인 버퍼에 담아두고, 드리븐 테이블을 먼저 읽고 조인 버퍼에서 일치하는 레코드를 찾는 방식으로 처리됨.
  • 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있음을 기억해야 함.

MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입됨. MySQL 8.0.20 버전부터는 블록 네스티드 루프 조인은 더이상 사용되지 않고, 해시 조인 알고리즘이 대체되어 사용됨.

인덱스 컨디션 푸시다운(index_condition_pushdown)

• MySQL 5.6 버전부터 인덱스 컨디션 푸시다운(Index Condition Pushdown) 기능이 도입됨.
• 인덱스 컨디션 푸시다운은 너무 비효율적이어서 훨씬 오래 전부터 개선됐어야 했는데, 이제서야 보완됨.
• p324 부터 예시가 잘 설명되어 있음.

SELECT * FROM employees WHERE last_name='Acton' AND first_name LIKE '%sal';

• 위 그림은, 인덱스 컨디션 푸시다운이 작동하지 않을 때의 그림임.
• 위 쿼리의 실행계획은 “Using where” 인데, 이는 InnoDB 스토리지 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사하는 과정을 의미함.
• ix_last_name_firstname의 first_name 칼럼을 이용하지 않고, employees 테이블의 first_name을 읽음.
  • 불필요한 2건의 레코드를 읽게 됨.
• MySQL 5.6 버전부터는 위 WHERE 조건절에서 인덱스 범위 제한 조건을 사용하지 못하더라도, 인덱스에 포함된 칼럼 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선됨.

• 위 그림은 인덱스 컨디션 푸시다운이 활성화될 때 그림임.
• 인덱스를 이용하여 최대한 필터링까지 완료하여 꼭 필요한 레코드 1건의 대해서만 테이블 읽기를 수행할 수 있게 됨.
• 쿼리의 실행 계획도 “Using where” → “Using index condtion” 으로 출력됨.
• 인덱스 컨디션 푸시다운 기능은, 고도의 기술력이 필요한 기능은 아님.
  • 하지만 쿼리의 성능이 몇 배에서 몇십 배로 향상될 수도 있는 중요한 기능임.

index_condtion_pushdown과 이름이 비슷한 engine_condition_pushdown 옵티마이저 스위치는 내부 작동 방식은 거의 흡사하지만, engine_condition_pushdown은 NDB(MySQL Cluster)에서만 사용가능한 옵션임.

인덱스 확장(use_index_extensions)

• use_index_extensions 옵티마이저 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수있게 할지를 결정하는 옵션임.
• 8.8절의 ‘클러스터링 인덱스’ 에서, InnoDB 스토리지 엔진은 프라이머리 키를 클러스터링 키로 생성한다.
  • 그래서, 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가진다.

mysql> CREATE TABLE dept_emp (
                 emp_no INT NOT NULL,
                 dept_no CHAR(4) NOT NULL,
                 from_date DATE NOT NULL,
                 to_date DATE NOT NULL,
                 PRIMARY KEY (dept_no, emp_no),
                 KEY ix_fromdate (from_date)
             ) ENGINE=InnoDB;

• 위 dept_emp 테이블에서, 프라이머리 키는 (dept_no, empo_no) 이고, 세컨더리 인덱스는 ix_fromdate 는 from_date 칼럼만 포함한다.
  • 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해, 프라이머리 키인 dept_no와 emp_no 칼럼을 순서대로 (프라이머리 키에 명시된 순서) 포함한다.
  • 즉, 최종적으로 ix_fromdate 인덱스는 (from_date, dept_no, emp_no) 조합으로 인덱스를 생성한 것과 흡사하게 작동할 수 있게 됨.
    • 예전 MySQL 버전에서는 위 쿼리와 같이 세컨더리 인덱스의 마지막에 자동으로 추가되는 프라이머리 키를 제대로 활용하지 못했음.
    • MySQL 서버가 업그레이드되면서 옵티마이저는 ix_fromdate 인덱스의 마지막에 (dept_no, emp_no) 칼럼이 숨어있다는 것을 인지하고 실행 계획을 수립하도록 개선됨. ****
• p328 에서 key_len이 19바이트이고, 각 칼럼이 3, 16바이트로 나뉜다고 하는 부분이 잘 이해 안감…
• InnoDB의 프라이머리 키가 세컨더리 인덱스에 포함되어 있으므로, 정렬 작업도 인덱스를 활용해서 처리되는 장점도 있음.

mysql> SELECT * FROM dept_emp WHERE from_date='1987-07-25' ORDER BY dept_no;

• 위 쿼리의 실행 계획의 Extra 칼럼은 “Using Filesort” 가 표시되지 않음.
  • 즉, MySQL 서버가 별도의 정렬 작업 없이, 인덱스 순서대로 레코드를 읽기만 함으로써 “ORDER BY dept_no” 를 만족했음을 의미함.