책너두 6기 31일차

백은빈, 이성욱의 Real MySQL8.0 1권 p.318 ~ p.328

내용정리

09 고급 최적화

9.3 고급 최적화

MySQL 서버의 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 계획을 수립한다. 옵션은 크게 조인 관련된 옵션과 스위치로 구분할 수 있다.

9.3.1 옵티마이저 스위치 옵션

옵티마이저 스위치 옵션은 optimizer_switch시스템 변수를 이용해 제어한다.

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

MMR은 “Multi-Range Read”를 줄여서 부르는 이름인데, 메뉴얼에서는 DS-MRR(Disk Sweep Multi-Range Read)이라고도 한다. 네스티드 루프 조인(Nested Loop Join)은 드라이빙 테이블(조인에서 제일 먼저 읽는 테이블들)의 일치하는 레코드를 찾아서 조인을 수행하는 것이다. 이와 달리 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링한다. 조인 버퍼에 레코드가 가득 차면 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청한다. 이 읽기 방식을 MRR이라고 하고, 응용하여 실행되는 조인 방식을 BKA(Batched Key Access) 조인이라고 한다.

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

MySQL 서버에서 사용되는 대부분의 조인은 네스티드 루프 조인이다. 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용된다.

네스티드 루프 조인과 블록 네스티드 루프 조인의 가장 큰 차이는 조인 버퍼가 사용되는지 여부와 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐다. 조인 알고리즘에서 블록이라는 단어는 조인용으로 별도의 버퍼가 사용됐다는 것을 의미한다. 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리하는데, 이 메모리의 캐시를 조인 버퍼라고 한다.

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

인덱스를 범위 제한 조건으로 사용하지 못한다고 하더라도 인덱스에 포함된 칼럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있게 되었는데, 이를 인덱스 컨디션 푸시다운 옵션이다.

9.3.1.4 인덱스 확장(use_index_extensions)

InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션